MySQL master-slave replication and read-write separation

Time:2021-12-5

Article mind map

在这里插入图片描述

Why use master-slave replication and read-write separation

Master-slave replication and read-write separation are generally used together. The purpose is very simple. It is to improve the concurrency performance of the database.
If you think it’s a stand-alone machine, reading and writing are completed on one mysql, and the performance is certainly not high.
If there are three MySQL servers, one mate is only responsible for write operations and two save are only responsible for read operations, will the performance not be greatly improved?
Therefore, the separation of master-slave replication and read-write is to support greater concurrency in the database.
With the expansion of business volume, if MySQL is deployed on a single machine, the I / O frequency will be too high.
Using master-slave replication and separation of read and write can improve the availability of the database.

Principle of master-slave replication

① When the master node performs insert, update and delete operations, it will be written to the binlog in order.
② Save connects to the master master database from the database, and the number of slave threads created by the master will be as many as binlog dump threads.
③ When the binlog of the master node changes, the binlog dump thread will notify all save nodes,
And push the corresponding binlog content to the slave node.
④ After receiving the binlog content, the I / O thread writes the content to the local relay log.
⑤ The SQL thread reads the relay log written by the I / O thread and performs corresponding operations on the slave database according to the contents of the relay log.

在这里插入图片描述

How to implement master-slave replication

**The environment shows that I use two virtual machines for demonstration. The IP addresses are master (135) and 136 (slave)

In order to be more intuitive, the following figure shows the effect of the three machines (data change: such as inster, update, delete…)

Master configuration

**Use the command line to enter MySQL:

>MySQL - uroot - P // enter the password
>// 192.168.190.136 is the slave IP address
> GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.190.136' identified by 'rootroot'; 
//Refresh the system permission table. If it is not refreshed, restart it to take effect. 
> flush privileges;

The users configured above need it when configuring slave machines.

Next, add the configuration in the MySQL configuration file / etc / my.cnf:

VIM / etc / my. CNF // added under [mysqld]
#Database ID number. When it is 1, it means master. Master slave master_ ID cannot be consistent.
server-id=1
#Enable binlog
log-bin=mysql-bin
#Databases that need to be synchronized. If not configured, synchronize all databases
binlog-do-db=test
#Enter the number of days for binlog log retention, and clean up logs over 10 days.
#In case of too many and too large files, resulting in insufficient disk space.
expire-logs-days=10

After configuration, restart mysql

systemctl restart mariadb

Then enter Mysql to view and record the current binlog log information.

mysql -uroot -p

Show master status \ g / / there is no need to write a semicolon here because \ G is functionally equivalent to the semicolon “;”

在这里插入图片描述
**

Slave configuration

The slave configuration is simpler. Add server ID to / etc / my.cnf from the slave.

#Do not duplicate other MySQL service IDs
server-id=111

Then log in to the MySQL command line. Enter the following SQL

CHANGE MASTER TO 
MASTER_ Host ='192.168.190.135 ', // host IP
MASTER_ User ='root ', // user account created earlier
MASTER_ Password ='rootroot ', // user password created previously
MASTER_ LOG_ File ='mysql-bin. 00000 2 ', // binlog log name of master host
MASTER_ LOG_ POS = 245, // binlog log offset position
master_ port=3306;// port

Start the slave service after running successfully


start slave;

Then verify whether the startup is successful.


show slave status\G

You can see the following information: (the output is long and only part is intercepted)

在这里插入图片描述

Next, we can test master-slave replication

Execute SQL on the master host:

CREATE TABLE `goods_brand` (
	`id` BIGINT (20) UNSIGNED NOT NULL auto_increment,
	`brand_ Name ` varchar (50) not null comment 'brand name',
	`gmt_create` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`gmt_update` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY (`id`)
)Engine = InnoDB default charset = utf8 comment = 'brand table';

After the execution, let’s refresh the table of the library

在这里插入图片描述

You can see that I execute on the master machine, and the slave will synchronize directly. At this point, the master-slave synchronization of MySQL is completed.

For the separation of reading and writing, the available schemes and monitoring will be written in the next chapter, and the connection will be supplemented.

**Note: part of this chapter comes from @ alicloud developers**

summary

This is the end of this article about MySQL master-slave replication and read-write separation. For more information about MySQL master-slave replication and read-write separation, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

The real problem of Alibaba IOS algorithm can’t hang up this time

More and more IOS developers continue to enter the peak of job hopping in 2020 Three main trends of interview in 2020: IOS bottom layer, algorithm, data structure and audio and video development Occupied the main battlefield. Data structure and algorithm interview, especially figure, has become the main reason for the failure of most first-line […]