The principle of MySQL master-slave synchronization

MySQL’s built-in replication capabilities are the foundation for building large, high-performance applications. To distribute MySQL data to multiple systems, this distribution mechanism is achieved by copying the data of one MySQL host to other hosts (slave) and executing it again.

One server acts as the master server and one or more other servers act as slave servers during replication. The master server writes updates to a binary log file and maintains an index of the file to keep track of log failures, which can record updates sent to the slave server. When a slave connects to the master, it informs the master of the last successful update read by the slave in the log. The slave server receives any updates that have occurred since then, then blocks and waits for updates notified by the master server.

Note that:

When mysql is replicated, all updates to the replicated tables must be made on the primary server. Care must be taken to avoid conflicts between user updates to tables on the master server and updates to tables on the slave server.

MySQL > select * from ‘MySQL’;

  • The first step in the Mysql replication process is for the master node to enable binary logging. Before each transaction update commits, the master records these changes in the binary log. MySQL writes transactions serially to the binary log, even though the statements in the transaction are executed across. After the event is written to the binary log, the master notifies the storage engine to commit the transaction.
  • Second, the slave copies the master’s binary log to its own relay log. First, slave starts a worker thread — the I/O thread. The I/O thread opens a normal connection on the master and begins the binlog dump process. The Binlog dump process reads events from the master’s binary logs. If it has followed the master, it sleeps and waits for the master to generate new events. The I/O thread writes these events to the relay log.
  • Finally, the SQL slave Thread (SQL slave thread) reads the events from the slave log and reproduces the transactions in the log to update the slave data to match the data in the master. As long as the thread is consistent with the I/O thread, the relay log is usually in the OS cache, so the overhead of the relay log is minimal.
  • In addition, there is a worker thread in the master: as with all MySQL connections, opening a connection in the master causes the master to start a thread. The replication process has one important limitation — replication is serialized on the slave, which means that parallel updates on the master cannot be performed in parallel on the slave.

MySQL primary/secondary replication configuration procedure

Note the following before configuring the master/slave database replication: The version of the slave must be greater than or equal to that of the master. Otherwise, some operations performed on the master may cause data inconsistency.

1. Prepare two servers (master and slave) and install the MySQL database.

2. Edit the MySQL database configuration file (Windows:my.ini, Linux:mysqld.cnf) on the master server and add the following configuration:

server-id=1The server id of the current database cannot be the same as the server ID of another database. Otherwise, the primary/secondary replication cannot be performedlog-bin=mysql-bin # enable log level 2 for primary/secondary data synchronizationbinlog_do_db= sbtest # set binlog_do_db=XXXbinlog_ignore_dbMysql > select * from database where primary/secondary replication is not required;expire_logs_days = 3The binary log expiration time is deleted3Log files from days ago.Copy the code

3. Restart the database and run the commandshow master status;Command to check the status of the master server. As follows:

Note: Record the values of the File and Position fields.

4. Edit the configuration file of the MySQL database on the slave server and add the following configuration:

server-id=2# As above, the configuration cannot be repeatedrelay_log=mysql-relay-binread_only=1Read only modeexpire_logs_days = 3The binary log expiration time is deleted3Log files from days ago.# If this slave needs to be the master of another mysql database, the following two lines need to be commented on.
# log-bin=mysql-bin
# log_slave_updates=1
Copy the code

5. Restart the database and run the following command on the slave server:

mysql> CHANGE MASTER TO
MASTER_HOST='172.60.20.34',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000059'.The value of the File field mentioned above
MASTER_LOG_POS=48113969;  # Value of the Position field mentioned above
mysql> start slave;
Copy the code

6. Run the following command to check the running status of the slave node

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.60.20.34
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000059
          Read_Master_Log_Pos: 53015005
               Relay_Log_File: sn_20_46-relay-bin.000181
                Relay_Log_Pos: 53015218
        Relay_Master_Log_File: mysql-bin.000059
             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: 53015005
              Relay_Log_Space: 53015475
              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: 222
                  Master_UUID: 372f0366-9924-11e8-84fc-000c29938ebe
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; 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)
Copy the code

Check whether the Slave_IO_Running and Slave_SQL_Running nodes are Yes. Otherwise, the slave node fails to be started. For the solution, see “Slave_SQL_Running:No”

Personal blog: aikiller.github. IO /