MySQL Master-slave database error handling

Method one: After ignoring the error, continue syncing

This method is applicable to the case that the data of master-slave database is not very different, or the data can not be completely unified, the data requirements are not strict.

Solve:
Stop slave;

#表示跳过一步错误, the following numbers are 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 state is normal ...

Mode two: Re-master from, fully synchronized

This method is suitable for the case that the master-slave database data is large or the data is completely unified.
 
The steps to resolve are as follows:
 
1. Advanced Master Library, lock table, prevent data write
 
Use the command:
 
mysql> flush tables with read lock;
 
Note: This is locked to a read-only state, and the statement is case insensitive
 
2. Perform data backup
 
#把数据备份到mysql. bak.sql file
[[email protected] MySQL] #mysqldump-uroot-p-hlocalhost > Mysql.bak.sql
Note here: Database backups must be done on a regular basis, with shell scripts or Python scripts, to make sure 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 the MySQL backup file to the slave library for data recovery
 
#使用scp命令
[ Email protected] mysql]# SCP mysql.bak.sql [email protected]:/tmp/
 
5. Stop the state from the library
mysql> stop slave;
 
 
6. Then run the MySQL command from the library, import the data backup
 
mysql> Source/tmp/mysql.bak.sql
 
7. Set synchronization from the library, note that the synchronization point is in 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. Re-open from Sync
mysql> start slave;
 
9. View sync Status
mysql> Show slave status\g view:
 
Slave_io_running:yes
Slave_sql_running:yes
 
OK, the sync is done.

MySQL Master-slave database error handling