I was confused when I found that the Mysql master-slave database was not synchronized. So I searched and sorted it out. Next I will introduce the solution. If you are interested, please refer
I was confused when I found that the Mysql master-slave database was not synchronized. So I searched and sorted it out. Next I will introduce the solution. If you are interested, please refer
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 status \ G
Slave_IO_Running: Yes
Slave_ SQL _Running: No
Obviously, Slave is not synchronized.
The following two solutions are provided::
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 status \ G 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.
2. Back up data
# Back up data to the mysql. bak. SQL File
[Root @ server01 mysql] # mysqldump-uroot-p-hlocalhost> mysql. bak. SQL
Note: Database Backup must be performed on a regular basis. You can use shell scripts or python scripts to make sure that the data is safe.
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 the mysql backup file to the slave database machine for data recovery.
# Use the scp command
[Root @ server01 mysql] # scp mysql. bak. SQL root@192.168.128.101:/tmp/
5. Stop slave database status
Mysql> stop slave;
6. Run the mysql command in the slave database to import the data backup.
Mysql> source/tmp/mysql. bak. SQL
7. Set slave Database Synchronization. Note that the synchronization point here is | File | Position in the show master status information of the master database.
Change master to master_host = '192. 168.128.100 ', master_user = 'rsync', master_port = 192, master_password = '', master_log_file = 'mysqld-bin.000001', master_log_pos = 3306;
8. enable synchronization again
Mysql> stop slave;
9. View synchronization status
Mysql> show slave status \ G View:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Now, synchronization is complete.
