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…)
**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 “;”
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
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**
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!