Shulou(Shulou.com)05/31 Report--

This article mainly shows you "why mysql masters and followers are out of sync". The content is simple and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn why mysql masters and followers are out of sync.

The reasons for mysql master-slave non-synchronization: 1, the delay of the network; 2, the load of the master-slave two machines is inconsistent; 3, the "max_allowed_packet" setting is inconsistent; 4, the key value of the key self-increment key is inconsistent with the self-increment step setting; 5, the database version is inconsistent, and so on.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Cause Analysis of Master-Slave non-synchronization in mysql

1. Network delay

Since mysql master-slave replication is an asynchronous replication based on binlog, and binlog files are transmitted through the network, it is only natural that network delay is the vast majority of reasons for master-slave non-synchronization, especially the probability of data synchronization across computer rooms is very high, so do read-write separation and pay attention to the pre-design from the business layer.

2. The load of the master and slave machines is not consistent.

Because mysql master-slave replication starts 1 io thread above the master database, and starts 1 SQL thread and 1 io thread from above, any one of the machines has a high load and is too busy, resulting in a shortage of resources in any of these threads, and there will be master inconsistency.

3. Inconsistent max_allowed_packet settings

The max_allowed_packet set above the master database is larger than that of the slave database. When a large sql statement can be executed on the master database, the setting from the master database is too small to be executed, resulting in the master never being consistent.

4. The key value of key from the beginning of the increment key is not consistent with the master caused by the inconsistency of the self-increment step size setting.

5. In the case of abnormal mysql downtime, if sync_binlog=1 or innodb_flush_log_at_trx_commit=1 is not set, it is likely that the binlog or relaylog file will be corrupted, resulting in inconsistency of the master.

6. The master-slave asynchrony caused by mysql's own bug.

7. The database version is inconsistent, especially when the high version is the master and the low version is the slave, the function supported on the master database is not supported from the database.

Solution

Method 1: continue to synchronize after ignoring the error

This method is suitable for situations where there is little difference between master and slave database data, or when the data is not completely unified, and the data requirements are not strict.

Resolve:

Stop slave

Indicates that one step is skipped and the following number is variable.

Set global sql_slave_skip_counter = 1 * * start slave

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

Slave_IO_Running: YesSlave_SQL_Running: Yes

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

Method 2: re-master and slave, complete synchronization

This method is suitable for situations where there is a large difference between master and slave database data, or when the data is required to be completely unified.

The resolution steps are as follows:

1. First enter the main library and lock the table to prevent data from being written

Use the command:

Mysql > flush tables with read lock

Note: this is where the lock is read-only and the statement is case-insensitive.

two。 Make a data backup

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

Mysqldump-uroot-p-hlocalhost > mysql.bak.sql

One thing to note here: database backup must be carried out regularly. You can use shell script or python script, both of which are convenient to ensure that the data is foolproof.

3. View 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 files to the slave machine for data recovery

Use the scp command

Scp mysql.bak.sql root@192.168.128.101:/tmp/

5. Stop the state of the slave library

Mysql > stop slave

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

Mysql > source / tmp/mysql.bak.sql

7. Set the slave database synchronization. Pay attention to the synchronization point there, that is, the | File | Position items in the show master status information of the master database.

Change master to master_host = '192.168.128.100, master_user =' rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260

8. Re-enable slave synchronization

Mysql > start slave

9. View synchronization status

Mysql > show slave status\ G

View:

Slave_IO_Running: YesSlave_SQL_Running: Yes above are all the contents of the article "Why are the mysql masters and followers out of sync?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.