Solve the problem of master-slave non-synchronization of MySQL
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
