MySQL's master-slave database does not have a synchronized solution

Today found MySQL's master-slave database is not synchronized

First on the Master library:

Mysql>show processlist; Check to see if the process is too much sleep. Found to be normal.

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

And then go to the slave to see

Mysql> Show Slave STATUSG

Slave_io_running:yes

Slave_sql_running:no

Visible is slave different step

Here are two ways to fix this:

Method one: After ignoring the error, continue synchronizing

This method is suitable for the situation that the data of master-slave library is not very different, or the data can not be completely unified, and the data requirements are not strict.

Solve:

Stop slave;

#表示跳过一步错误, the following number is variable

Set global sql_slave_skip_counter = 1;

Start slave;

Then use mysql> show slave STATUSG View:

Slave_io_running:yes

Slave_sql_running:yes

OK, now the master-slave sync State is normal ...

Mode two: Re-master from, full sync

This method is suitable for the case that the data of master-slave library is large or the data is completely unified.

The resolution steps are as follows:

1. Advanced Master Library, to lock the table, to prevent data writing

To use the command:

Mysql> flush tables with read lock;

Note: This is locked in a read-only state and statements are case-insensitive

2. Data backup

#把数据备份到mysql. bak.sql file

[Root@server01 MySQL] #mysqldump-uroot-p-hlocalhost > Mysql.bak.sql

Note here: Database backups must be done on a regular basis, you can use shell scripts or Python scripts, are easier 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. Upload MySQL backup files from the library machine for data recovery

#使用scp命令

[Root@server01 mysql]# SCP Mysql.bak.sql root@192.168.128.101:/tmp/

5. Stop the state from the library

mysql> stop Slave;

6. Then to execute MySQL command from library, import data backup

Mysql> Source/tmp/mysql.bak.sql

7. Set to sync from the library, note that the sync point of this place is the main library show Master status information | file| Position two items

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. Reboot from Sync

mysql> start slave;

9. View sync Status

Mysql> Show slave STATUSG View:

Slave_io_running:yes

Slave_sql_running:yes

All right, Sync's done.