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

MySQL master and slave database inconsistency how to solve, many novices are not very clear, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Go to the Master library first:

Mysql > show processlist

Check to see if the process has too much Sleep. It turns out it's normal.

Show master status

It's 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)

Check it on Slave again.

Mysql > show slave status\ G Slave_IO_Running: Yes Slave_SQL_Running: No

It can be seen that the Slave is out of sync

Solution

Here are two solutions

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; # means skip one step error, followed by a variable number set global sql_slave_skip_counter = 1; start slave

Then use mysql > show slave status\ G to check.

Mysql > show slave status\ G Slave_IO_Running: Yes Slave_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

# backup data to mysql.bak.sql file

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

One thing to note here: database backups must be carried out on a regular basis, and you can use shell or python scripts, 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

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 Slave_IO_Running: Yes Slave_SQL_Running: Yes

10. Go back to the main library and execute the following command to unlock the table.

Is it helpful for UNLOCK TABLES; to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.