MySQL replication advantages and principles explained in detail

Replication is to transfer the DDL and DML operations of the master database to the slave database through binary logs, and then redo them on the slave database, so that the slave database and the master database keep data synchronized. MySQL can replicate from one master database to multiple slave databases at the same time, and a slave database can also serve as the master database for other slave databases to achieve chain replication.

Advantages of MySQL replication:

• If the master database fails, the service can be quickly switched to the slave database.
• Execute query operations on the slave database to reduce the access pressure on the master database;
• Perform backup on the slave database to avoid affecting the master database during the backup;

MySQL replication principle

1. When a transaction is committed, the MySQL master database records data changes as events in the Binlog. The sync_binlog parameter on the master database controls the flushing of the Binlog log to the disk.

2. The master database pushes events in the Binlog to the Relay Log of the slave database. The slave database then redoes the events based on the Relay Log, and achieves data consistency between the master and slave databases through logical replication.

MySQL uses three threads to complete data replication between the master and slave databases: 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 on the slave, an I/O thread is first created to connect to the master. The master then creates a Binlog Dump thread to read database events and send them to the I/O thread. After the I/O thread obtains the event data, it updates it to the slave's Relay Log. Then, the SQL thread on the slave reads the updated database events in the Relay Log and applies them.

As shown in the following figure:

View the main library:

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 -- From this, we can see that synchronization is done in a "push" manner. Info: NULL 
4 rows in set (0.00 sec) 
 
ERROR:  
No query specified

View the backup library:

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 logs; waiting for more updates 
  Info: NULL

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

Copy related logs

1. BinlogBinlog will record all data modification operations in MySQL. You can view the format of Binlog in the following ways. There are three types: Statement, Row and Mixed:

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

2. Relay Log The file format and content of Relay Log are the same as Binlog. The only difference is that after the SQL thread on the slave executes the events in the current Relay Log, the SQL thread will automatically delete the Relay Log to free up space. To ensure that the slave's I/O thread and SQL thread still know where to start replication after the slave crashes and restarts, the slave creates two log files, master.info and relay-log.info, by default to save the replication progress. These two files record the progress of the slave's I/O thread currently reading the master's Binlog and the progress of the SQL thread applying the Relay Log.

mysql> show slave status \G; 
*************************** 1. row *************************** 
        Slave_IO_State: Waiting for master to send event 
         Master_Host: 10.24.33.186 -- Main database IP 
         Master_User: repl -- User account of the master database used for master-slave replication Master_Port: 3306 -- Master database port Connect_Retry: 60  
       Master_Log_File: mysql-bin.000005 --The file name of the master library Binlog currently read by the slave library I/O thread Read_Master_Log_Pos: 4356 --The position of the master library Binlog read by the slave library I/O thread Relay_Log_File: strong-relay-bin.000006 --The Relay Log being applied by the SQL thread 
        Relay_Log_Pos: 320 --Relay Log location Relay_Master_Log_File: mysql-bin.000005 --Binlog corresponding to Relay Log 
       Slave_IO_Running: Yes 
      Slave_SQL_Running: Yes 
       Replicate_Do_DB:  
     Replicate_Ignore_DB:  
      Replicate_Do_Table:  
    Replicate_Ignore_Table:  
   Replicate_Wild_Do_Table:  
 Replicate_Wild_Ignore_Table:  
          Last_Errno: 0 
          Last_Error:  
         Skip_Counter: 0 
     Exec_Master_Log_Pos: 4356 --SQL thread is applying the location of the Relay Log corresponding to the location of the Binlog Relay_Log_Space: 1153 
       Until_Condition: None 
        Until_Log_File:  
        Until_Log_Pos: 0 
      Master_SSL_Allowed: No 
      Master_SSL_CA_File:  
      Master_SSL_CA_Path:  
       Master_SSL_Cert:  
      Master_SSL_Cipher:  
        Master_SSL_Key:  
    Seconds_Behind_Master: 0 
Master_SSL_Verify_Server_Cert: No 
        Last_IO_Errno: 0 
        Last_IO_Error:  
        Last_SQL_Errno: 0 
        Last_SQL_Error:  
 Replicate_Ignore_Server_Ids:  
       Master_Server_Id: 1 
         Master_UUID: 2a3e3fd9-0587-11e8-bdb8-0800272325a8 
       Master_Info_File: /usr/local/mysql-5.7.21-el7-x86_64/data/master.info 
          SQL_Delay: 0 
     SQL_Remaining_Delay: NULL 
   Slave_SQL_Running_State: Slave has read all relay logs; waiting for more updates 
      Master_Retry_Count: 86400 
         Master_Bind:  
   Last_IO_Error_Timestamp:  
   Last_SQL_Error_Timestamp:  
        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 method

There are three formats of Binlog, corresponding to the three technologies of MySQL replication.

MySQL replication architecture

Common architectures of MySQL replication include one-master-multiple-slave replication architecture, multi-level replication architecture, and dual-master replication (Dual Master) architecture.

1. One-master-multiple-slave architecture In scenarios where the read request pressure of the master database is very high, read-write separation is achieved by configuring a one-master-multiple-slave replication architecture. Read requests that do not require high real-time performance are distributed to multiple slave databases through load balancing, thereby reducing the read pressure of the master database, as shown in the figure:

2. Multi-level replication architecture The one-master-multiple-slave architecture can solve the needs of most scenarios with particularly high read request pressure. Since MySQL replication is the master database pushing Binlog to the slave database, the I/O pressure and network pressure of the master database will increase with the increase of slave databases (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 scenario of additional I/O and network pressure of the master database in the one-master-multiple-slave scenario, as shown in the figure:

3. Dual Master Replication/Dual Master Architecture The dual master replication/Dual Master architecture is particularly suitable for scenarios where DBA needs to switch between master and slave for maintenance. This architecture avoids the trouble of repeatedly building slave libraries, as shown in the figure: