Detailed explanation of MySQL master-slave inconsistency and solutions

1. MySQL master-slave asynchrony

1.1 Network Delay

Since MySQL master-slave replication is an asynchronous replication based on binlog

When transmitting binlog files over the network, network latency is of course the main reason for master-slave asynchrony. This is especially likely to happen when synchronizing data across computer rooms. Therefore, read-write separation is required, and attention should be paid to the preliminary design from the business layer.

1.2 The loads of the master and slave machines are inconsistent

Since MySQL master-slave replication starts one IO thread on the master database, and starts one SQL thread and one IO thread from it, if the load on any of the machines is very high and it is too busy, any of the threads will have insufficient resources, resulting in master-slave inconsistency.

1.3 Inconsistent max_allowed_packet settings

The max_allowed_packet set on the master database is larger than that on the slave database. When a large SQL statement can be executed on the master database, it is set too small on the slave database and cannot be executed, resulting in inconsistency between the master and the slave.

1.4 Inconsistent auto-increment keys

The master-slave inconsistency is caused by the inconsistency between the starting key value of the key auto-increment key and the auto-increment step setting.

1.5 Synchronization parameter setting issues

In the case of abnormal downtime of MySQL, if sync_binlog=1 or innodb_flush_log_at_trx_commit=1 is not set, it is very likely that the binlog or relaylog files will be damaged, resulting in inconsistency between the master and the slave.

1.6 Bugs

The master-slave asynchrony caused by the mysql bug

1.7 Version inconsistency

In particular, when the higher version is the master and the lower version is the slave, the functions supported on the master database are not supported on the slave database.

1.8 Master-slave inconsistency optimization configuration

Based on the above situation, first ensure that max_allowed_packet, the start point and the growth point of the auto-increment key are set to be consistent, and then sacrifice some performance to enable sync_binlog on the master. For libraries using innodb, the following configuration is recommended

innodb_flush_logs_at_trx_commit = 1
innodb-support_xa = 1 # MySQL 5.0 or above innodb_safe_binlog # MySQL 4.0

At the same time, add the following two parameters from the above recommendation

skip_slave_start
read_only

2. How to solve the master-slave asynchrony problem

2.1 Master-Slave Asynchronous Scenario Description

Today I found that the master and slave databases of Mysql are not synchronized

First go to the Master library:

mysql>show processlist;

Check whether the process sleeps too much. Found it normal.

show master status;

Check that the status of the main database is normal.

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

Then check on Slave

mysql> show slave status\G                        

Slave_IO_Running: Yes
Slave_SQL_Running: No

This shows that the Slave is out of sync

2.2 Solution 1: Ignore the error and continue syncing

This method is suitable for situations where the data of the master and slave databases are not much different, or the data can be not completely unified. It is suitable for situations where the data requirements are not strict:

stop slave;

Indicates skipping a step error, the number behind is variable

set global sql_slave_skip_counter = 1;
start slave;

Then use mysql> show slave status\G to view:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

OK, now the master-slave synchronization status is normal. . .

2.3 Method 2: Re-do master-slave, complete synchronization

This method is suitable for situations where the data of the master and slave databases differ greatly, or when the data needs to be completely unified.

The solution steps are as follows:

1. Enter the main database first and lock the table to prevent data from being written

Use command:

mysql> flush tables with read lock;

Note: This is locked as read-only, the statement is not case sensitive

2. Perform data backup

Back up the data to the mysql.bak.sql file

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

One thing to note here: Database backup must be performed regularly. You can use shell scripts or python scripts, which are more convenient to ensure that the data is safe.

3. Check the master status

mysql> show master status; 
+——————-+———-+————–+——————————-+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+——————-+———-+————–+——————————-+ 
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema | 
+——————-+———-+————–+——————————-+ 
1 row in set (0.00 sec)

4. Transfer the MySQL backup file to the slave machine for data recovery

Using scp command

[root@server01 mysql]# scp mysql.bak.sql [email protected]:/tmp/

5. Stop the slave status

mysql> stop slave;

6. Then execute the mysql command from the database to import the data backup

mysql> source /tmp/mysql.bak.sql

7. Set up slave database synchronization. Note that the synchronization point is the | File | Position item in the show master status information of the master database.

change master to master_host = '192.168.1.206', master_user = 'rsync', master_port=3306, master_password=”, master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

8. Restart the slave synchronization

mysql> start slave;

9. Check the synchronization status

mysql> show slave status\G View:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

OK, synchronization is complete.

3. How to monitor the delay between MySQL master and slave

3.1 Introduction:

In daily work, there are two aspects to check MYSQL master-slave replication:

Ensure that the overall structure of the copy is complete;

Need to check if the data is consistent;

For the former, we can monitor whether the replication thread is working properly and whether the master-slave delay is within the tolerance range. For the latter, we can ensure data consistency by checking whether the md5 codes of the data in the master and slave tables are consistent. You can use the mk-table-checksum tool in the Maatkit toolkit to check.
This document describes how to check the master-slave delay.

There are usually two methods for master-slave delay judgment: Seconds_Behind_Master and mk-heartbeat

3.2 Method 1.

By monitoring the value of the Seconds_Behind_Master parameter in the show slave status\G command output, you can determine whether there is a master-slave delay.

mysql> show slave status\G;
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.1.205
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 30
       Master_Log_File: edu-mysql-bin.000008
     Read_Master_Log_Pos: 120
        Relay_Log_File: edu-mysql-relay-bin.000002
        Relay_Log_Pos: 287
    Relay_Master_Log_File: edu-mysql-bin.000008
       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: 120
       Relay_Log_Space: 464
       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: 205
         Master_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15
       Master_Info_File: /home/mysql/data/master.info
          SQL_Delay: 0
     SQL_Remaining_Delay: NULL
   Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
      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
1 row in set (0.00 sec)

The above is the output of show slave status\G. These structures provide many meaningful parameters for our monitoring.

Slave_IO_Running This parameter can be used as a monitoring item for io_thread. Yes means that io_thread is connected to the master database normally and can perform replication work. No means that the communication with the master database is abnormal, which is mostly caused by the network between the master and the slave.

Slave_SQL_Running This parameter indicates whether sql_thread is normal, specifically whether the statement is executed successfully. It is often encountered that the primary key is repeated or a table does not exist.

Seconds_Behind_Master is a difference value obtained by comparing the timestamp of the event executed by sql_thread with the timestamp of the event copied by io_thread (abbreviated as ts); NULL - indicates that either io_thread or sql_thread has failed, that is, the Running status of the thread is No, not Yes. 0 — This value is zero, which is what we are most eager to see. It means that the master-slave replication is good and lag does not exist. Positive value - indicates that there is a delay between the master and the slave. The larger the number, the more the slave lags behind the master. Negative value - It is rarely seen. I just heard some senior DBAs say they have seen it. In fact, this is a BUG value. This parameter does not support negative values, which means it should not appear.

Note: The calculation method of Seconds_Behind_Master may cause problems. As we all know, the contents of relay-log and bin-log of the master database are exactly the same. When recording SQL statements, the ts at that time will be recorded, so the reference value comes from binlog. In fact, the master and slave do not need to synchronize with NTP, that is, there is no need to ensure the consistency of the master and slave clocks. You will also find that the comparison actually occurs between io_thread and sql_thread, and io_thread is actually related to the main database, so the problem arises.

When the master database I/O load is heavy or the network is blocked, io_thread cannot copy binlog in time (it is not interrupted and is still copying), while sql_thread can always keep up with the io_thread script. In this case, the value of Seconds_Behind_Master is 0.

That is what we think is no delay, but actually it is not, you know. This is why people criticize the inaccuracy of using this parameter to monitor whether the database has delays, but this value is not always inaccurate.

This value is also very valuable if the io_thread and the master network are very good. ''Before, we mentioned that the parameter Seconds_Behind_Master will have a negative value. We already know that this value is the difference between the most recent ts of io_thread and the ts executed by sql_thread.

The former is always greater than the latter. The only possibility is that an error occurs in the ts of an event and it is smaller than the previous one. When this happens, negative values ​​may appear.

3.2 Method 2.

mk-heartbeat: A tool in the Maatkit universal toolkit, which is considered to be a method for accurately determining replication delay.

The implementation of mk-heartbeat is also achieved with the help of timestmp comparison. It first needs to ensure that the master and slave servers must be consistent by synchronizing the clock with the same NTP server. It needs to create a heartbeat table on the master database, which contains at least two fields, id and ts. The id is server_id, and ts is the current timestamp now(). The structure will also be copied to the slave database. After the table is built, a row of update operation commands will be executed on the master database in the background process mode to regularly insert data into the table. The default cycle is 1 second. At the same time, the slave database will also execute a monitoring command in the background to compare the ts value of the copied record with the same ts value on the master database. If the difference is 0, it means there is no delay. The larger the difference, the more seconds of delay. We all know that replication is asynchronous and ts are not completely consistent, so the tool allows a half-second gap, and the difference within this can be ignored and considered to be no delay. This tool uses real copying and cleverly uses timestamp to check the delay;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM. .