Solve a small problem of MySQL master-slave Synchronization

Due to historical issues, the table structure of the MySQL Master/Slave database is inconsistent. A table tableA in the master database has a field less than tableA in the slave database. When you try to change the table structure in the master database, the alter statement will be synchronized to the slave database along with the binlog. If an error occurs when the slave database executes this statement, the master-slave synchronization thread will automatically stop, the error can only be handled manually.

Due to historical issues, the table structure of the MySQL Master/Slave database is inconsistent. A table tableA in the master database has a field less than tableA in the slave database. When you try to change the table structure in the master database, the alter statement will be synchronized to the slave database along with the binlog. If an error occurs when the slave database executes this statement, the master-slave synchronization thread will automatically stop, the error can only be handled manually.

Due to historical issues, the table structure of the MySQL Master/Slave database is inconsistent. A table tableA in the master database has a field less than tableA in the slave database.

When you try to change the table structure in the master database, the alter statement will be synchronized to the slave database along with the binlog. If an error occurs when the slave database executes this statement, the master-slave synchronization thread will automatically stop, in this way, you can only manually handle errors and then start the master-slave synchronization thread on the slave. The scenario is like the following:

1. Execute alter table aaa add column xxx int default 1 after yyy on the master database;

2. The slave database also executes such a statement, but the slave thread fails to change the table because the slave database already has the xxx field, when you use show slave status to view the Master/slave status, you will find statements similar to the following:

Slave_IO_Running: Yes Slave_ SQL _Running: No Replicate_Do_DB: Usage: Replicate_Do_Table: Usage: Last_Errno: 1060 Last_Error: Error 'duplicate column name 'xxx' on query. default database: 'dbxxx '. query: 'alter table aaa add column xxx int default 1 after yyy' // some statements Last_ SQL _Errno: 1060 Last_ SQL _Error: Error 'duplicate column name 'xxx' on query are omitted here. default database: 'dbxxx '. query: 'alter table aaa add column xxx int default 1 after yyy'

We can see that the salve_io thread for copying binlog from the master database is still busy, but the slave_ SQL statement for executing the update from the slave database has gone on strike, from Last_error: we can see that an error occurred while executing alter table aaa add column xxx int default 1 after yyy from the master database China. The specific reason is that the table already has the xxx column.

The manual solution is to tell the slave slave_ SQL thread to let him ignore this error and continue the execution:

mysql>set global sql_slave_skip_counter=1;mysql>start slave;

The preceding statement tells slave to skip the currently stuck event and then start working again.

The above method can be used when slave is relatively small, but when there are dozens of slave databases, it is time-consuming and error-prone to process one by one, how can we avoid it once and for all on the master database side?

Right, that is, do not write the statement such as alter to the binlog on the master database. MySQL is worthy of world-class software products and provides a session granularity option, you can disable this option to prevent the master database from writing SQL statements before enabling this option or closing the connection to binlog.

mysql>set sql_log_bin=off;mysql>alter table aaa add column xxx int default 1 after yyy;

Is it convenient?