Shulou(Shulou.com)06/01 Report--

Solve mysql master-slave non-synchronization

Today, it was found that the master-slave database of Mysql was not synchronized.

Go to the Master library first:

Mysql > show processlist; to see if the process has too many Sleep. It turns out it's normal.

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

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

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 to skip a step error, 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: 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

[root@server01 mysql] # 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

[root@server01 mysql] # 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: Yes

Slave_SQL_Running: Yes

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.