Detailed explanation of MySQL master-slave replication and read-write separation

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 simple, which is to improve the concurrent performance of the database.
You can imagine that if it is a single machine, and both reading and writing are done on one MySQL, the performance will definitely not be high.
If there are three MySQL servers, one master is only responsible for write operations, and two slaves are only responsible for read operations, wouldn't the performance be greatly improved?
Therefore, master-slave replication and read-write separation are designed to enable the database to support greater concurrency.
As the business volume expands, if MySQL is deployed on a single machine, the I/O frequency will be too high.
The use of master-slave replication and read-write separation can improve the availability of the database.

The principle of master-slave replication

①When the Master node performs insert, update, or delete operations, they are written to the binlog in order.
②The salve slave database connects to the master main database, and the number of binlog dump threads created by the master is the same as the number of slaves.
③When the binlog of the Master node changes, the binlog dump thread will notify all salve 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 based on the content of the relay-log.

How to implement master-slave replication

** Environment display, I use two virtual machines for demonstration, IP is Master (135) and 136 (Slave)

For a more intuitive look, the following figure shows the effects of the three machines (data changes: such as inster, update, delete...)

Master Configuration

**Use the command line to enter mysql:

> mysql -uroot -p //Enter password> //192.168.190.136 is the IP address of the slave machine
> GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.190.136' identified by 'rootroot'; 
//Refresh the system permission table. If not, restart the system to make it effective. 
> flush privileges;

The user configured above is needed when configuring the slave machine.

Next, add the configuration to the mysql configuration file /etc/my.cnf:

vim /etc/my.cnf //Add #database ID number under [mysqld]. When it is 1, it means master. The master_id of the master and slave cannot be consistent.
server-id=1
# Enable binlog
log-bin=mysql-bin
#The database that needs to be synchronized. If not configured, all databases will be synchronized binlog-do-db=test
#The number of days that binlog logs are retained. Logs older than 10 days will be cleared.
#To prevent too many files from being too large, resulting in insufficient disk space.
expire-logs-days=10

After the configuration is complete, restart mysql

systemctl restart mariadb

Then enter mysql, check the current binlog log information and record it.

mysql -uroot -p

show master status\G //No need to write a semicolon here, because \G is functionally equivalent to a semicolon ";"

**

Slave Configuration

The Slave configuration is simpler. The slave machine can add server-id in /etc/my.cnf.

# Do not repeat with 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', // previously created user account MASTER_PASSWORD='rootroot', // previously created user password MASTER_LOG_FILE='mysql-bin.000002', // master host binlog log name MASTER_LOG_POS=245, // binlog log offset Position
master_port=3306;//Port

After successful operation, start the slave service

start slave;

Then verify whether the startup is successful.

show slave status\G

You can see the following information: (The output is long, only part of it is intercepted)

Next we can test the 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 is completed, let's refresh the library table and see

You can see that I am executing on the Master machine, and the slave will synchronize directly to it. At this point, the master-slave synchronization of mysql has been completed.

Regarding read-write separation, available solutions and monitoring will be written in the next chapter, and the link will be supplemented.

**Note: Part of the content of this chapter comes from @Alibaba Cloud Developer**

Summarize

This is the end of this article about MySQL master-slave replication and read-write separation with pictures and text. For more relevant MySQL master-slave replication and read-write separation content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!