Two ways to solve the problem of master-slave synchronization in MySQL

Mysql Master/Slave databases are not synchronized today

First go to the Master database:

Mysql> show processlist; check whether there are too many Sleep processes. It is normal.

Show master status; also 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)

Go to Slave to view

Mysql> show slave statusG

Slave_IO_Running: Yes

Slave_ SQL _Running: No

Obviously, Slave is not synchronized.

The following describes two solutions:

Method 1: Ignore errors and continue Synchronization

This method is applicable when the data in the Master/Slave database is not much different or the data requirements are not strict.

Solution:

Stop slave;

# Indicates that the error is skipped. The subsequent number is variable.

Set global SQL _slave_skip_counter = 1;

Start slave;

Then use mysql> show slave statusG to view the information:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

OK. Now the master-slave synchronization status is normal...

Method 2: Re-master the slave, full synchronization

This method is applicable when the data in the Master/Slave database is significantly different or the data must be completely unified.

The solution is as follows:

1. First enter the master database and lock the table to prevent data writing.

Run the following command:

Mysql> flush tables with read lock;

Note: The lock is read-only and the statements are case insensitive.