What are the reasons why mysql master and slave are synchronized?

The reasons why mysql master and slave are synchronized: 1. Network delay; 2. The loads of the master and slave machines are inconsistent; 3. The "max_allowed_packet" setting is inconsistent; 4. The key value starting from the key increment is different from the The auto-increment step setting is inconsistent; 5. The database version is inconsistent, etc.

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

# Analysis of the reasons why mysql master is not synchronized

1. Network delay

Due to Mysql master-slave replication is an asynchronous replication based on binlog. Binlog files are transmitted through the network. Of course, network delay is the vast majority of reasons for master-slave synchronization. Especially for data synchronization across computer rooms, the probability of this happening is very high, so Separate reading and writing, and pay attention to the early design from the business layer.

2. The loads of the master and slave machines are inconsistent

Because mysql master-slave replication starts an io thread on the master database and starts 1 sql from above Threads and 1 io thread, any one of the machines has a high load and is too busy, resulting in insufficient resources for any one of the threads, and master-slave inconsistency will occur.

3. Max_allowed_packet settings are inconsistent

The max_allowed_packet set on the master database is larger than that of the slave database. When a large sql statement can be completed on the master database, the slave database The settings on the database are too small and cannot be executed, resulting in master-slave inconsistency.

4. Master-slave inconsistency caused by inconsistency between the key value starting from the key auto-increment key and the auto-increment step setting.

5. When mysql is abnormally down, if sync_binlog=1 or innodb_flush_log_at_trx_commit=1 is not set, it is very likely that the binlog or relaylog file will be damaged, resulting in master-slave inconsistency.

6. Master-slave synchronization caused by bugs in mysql itself.

7. Database versions are inconsistent, especially when the higher version is the master and the lower version is the slave, the functions supported by the master database are not supported by the slave database. .

Solution

Method 1: After ignoring the error, continue synchronization

This method is applicable When the master-slave database data is not very different, or the data is not required to be completely unified, or the data requirements are not strict,

Solution:

stop slave;

means skipping a step error, and the following numbers can be After changing

set global sql_slave_skip_counter =1;
start slave;

, use mysql> show slave status\G to check:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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

Method 2: Re-make the master-slave, complete synchronization

This method is suitable for situations where the data in the master-slave database are very different, or the data is required to be completely unified

The solution steps are as follows:

1. First enter the main database 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 state, statements are not case-sensitive

2. Back up data

Back up data to the mysql.bak.sql file

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

Note here: database backup must be To do it regularly, you can use shell scripts or python scripts, which are more convenient and ensure that the data is foolproof

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 and perform Data recovery

Use scp command

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

5. Stop the status of the slave library

mysql> stop slave;

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

mysql> source /tmp/mysql.bak.sql

7. Set up the synchronization from the slave library. Pay attention to the synchronization point there, which is the two items | File| Position in the show master status information of the master library

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. Restart slave synchronization

mysql> start slave;

9. Check synchronization status

mysql> show slave status\G

View:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

[Related recommendations:mysql video tutorial】

The above is the detailed content of What are the reasons why mysql master and slave are synchronized?. For more information, please follow other related articles on the PHP Chinese website!