MySQL replication advantages and principles

MySQL replication advantages and principles

Replication transfers the DDL and DML operations of the primary database to the slave database through binary logs, and rewrites the data from the slave database to ensure data synchronization between the slave database and the primary database. MySQL can replicate data from one master database to multiple slave databases at the same time. The slave database can also be used as the master database of other slave databases for chain replication.

Advantages of MySQL replication:

• If the master database fails, you can quickly switch to the slave database to provide services;
• Perform the query operation on the slave database to reduce the access pressure on the master database;
• Back up the slave database to avoid affecting the master database during the backup;

MySQL replication principles

1. MySQL master database records data changes in Binlog as Event Events During transaction commit. The sync_binlog parameter on the master database controls Binlog refresh to the disk;

2. The primary database pushes events in the Binlog to the slave database's Relay Log, and then rewrites the data from the slave database based on the Relay Log to achieve data consistency between the master and slave databases through logical replication;

MySQL completes data replication between master and slave databases through three threads: The Binlog Dump thread runs on the master database, and the I/O thread and SQL thread run on the slave database. When starting replication (Start Slave) from the Slave database, first create an I/O thread to connect to the master database, and then create a Binlog Dump thread to read database events and send them to the I/O thread, the I/O thread obtains the event data, updates it to the Relay Log of the slave database, and then reads and applies the database events updated in the Relay Log from the SQL thread on the database,

As shown in:

View the master database:

Mysql> show processlist \ G; ***************************** 1. row ************************** Id: 3 User: root Host: 10.24.33.187: 54194 db: NULL Command: Sleep Time: 176 State: Info: NULL **************************** 2. row ************************** Id: 4 User: root Host: 10.24.33.187: 54195 db: NULL Command: Sleep Time: 176 State: Info: NULL **************************** 3. row ************************** Id: 8 User: root Host: localhost db: test Command: Query Time: 0 State: starting Info: show processlist **************************** 4. row ************************* Id: 12 User: repl Host: dsz884.hcg.homecredit.net: 39731 db: NULL Command: Binlog Dump -- Binlog Dump thread Time: 87 State: Master has sent all binlog to slave; waiting for more updates -- this can be seen, synchronize Info: NULL 4 rows in set (0.00 sec) ERROR: No query specified in Push mode

View slave database:

mysql> show processlist\G; *************************** 1. row ***************************    Id: 1   User: system user   Host:     db: NULL Command: Connect   Time: 4427  State: Waiting for master to send event   Info: NULL *************************** 2. row ***************************    Id: 2   User: system user   Host:     db: NULL Command: Connect   Time: 2044  State: Slave has read all relay log; waiting for more updates   Info: NULL 

It can be seen that MySQL replication is asynchronous and there is a certain delay between the slave database and the master database.

Copy related logs

1. BinlogBinlog records all data modification operations in mysql. You can view the Binlog format in the following ways. Three types of data are available: Statement, Row, and Mixed:

mysql> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW  | +---------------+-------+ 1 row in set (0.00 sec) 

2. The file format and content of Relay LogRelay Log are the same as those of Binlog. The only difference is that after the SQL thread on the database executes the events in the current Relay Log, the SQL thread automatically deletes the Relay Log to release space. To ensure that the slave database's I/O and SQL threads can still know where to start copying after the Crash restart, by default, two log files master.info and relay-log.info are created from the slave database to save the replication progress, these two files respectively record the progress of reading the Binlog of the master database from the I/O thread of the slave database and the progress of applying Relay Log to the SQL thread.

Mysql> show slave status \ G; ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.24.33.186 -- master database IP Master_User: repl -- master database master _port: 3306 -- master database port Connect_Retry: 60 Master_Log_File: mysql-bin.000005 -- read the Binlog File Name of the master database from the library I/O thread Read_Master_Log_Pos: 4356 -- read the location of the Binlog of the master database from the library I/O thread Relay_Log_File: strong-relay-bin.000006 -- SQL thread applying Relay Log Relay_Log_Pos: 320 -- Relay Log location unknown: mysql-bin.000005 -- Relay Log corresponding Binlog Slave_IO_Running: Yes Slave_ SQL _Running: Yes unknown: Replicate_Do_Table: Unknown: failed: counters: 0 Last_Error: Skip_Counter: 0 counters: 4356 -- the SQL thread is applying the Relay Log to the Binlog location Relay_Log_Space: 1153 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 primary: No primary: Primary: Master_SSL_Cert: Primary: Master_SSL_Key: Primary: 0 primary: No primary: 0 Last_IO_Error: Primary: 0 Last_ SQL _Error: Primary: Master_Server_Id: 1 Master_UUID: Primary Master_Info_File: /usr/local/mysql-5.7.21-el7-x86_64/data/master.info SQL _Delay: 0 SQL _Remaining_Delay: NULL labels: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Keys: Master_SSL_Crl: master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql>

MySQL replication Mode

There are three Binlog formats, which correspond to three MySQL Replication technologies.

MySQL replication Architecture

Common MySQL replication architectures include Master-slave replication architecture, multi-level replication architecture, and Dual Master architecture.

1. The one-master-multiple-slave architecture achieves read/write splitting by configuring the One-master-multiple-slave replication architecture in the scenario where the read requests of the primary database are extremely high, distribute read requests that do not have particularly high real-time requirements to multiple slave databases through Server Load balancer to reduce the read Pressure on the master database,

2. Multi-level replication architecture the One-master-Multi-slave architecture can meet the needs of most scenarios with extremely high Read Request pressure, because MySQL replication pushes Binlog from the master database to the slave database, the I/O pressure and network pressure of the master database will increase with the increase of the slave database (each slave database will have an independent Binlog Dump thread on the master database to send Binlog events ), the multi-level replication architecture solves the extra I/O and network pressure on the master database in the scenario of one master node and multiple slave nodes,

3. Dual-Master replication/Dual Master architecture is particularly suitable for scenarios where DBA Requires Master-slave switchover for maintenance. This architecture avoids the trouble of repeated slave database construction,