Master-slave failover case 1

Fault cause: the developer modified the structure of a table on the master database and added a field. The slave database failed to synchronize the table due to various reasons. Troubleshooting: 1. view the synchronization status on the slave Database: mys

Fault cause: the developer modified the structure of a table on the master database and added a field. The slave database failed to synchronize the table due to various reasons. Troubleshooting: 1. view the synchronization status on the slave Database: mys

Fault Cause:
The developer modified the structure of a table on the master database and added a field. The slave database failed to synchronize the table due to various reasons.

Troubleshooting:
1. view the synchronization status on the slave database:

Mysql> show slave status \ G ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 10.10.100.100Master _ User: Connector: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000009Read_Master_Log_Pos: Connector: mysqld-relay-bin.000017Relay_Log_Pos: Connector: mysql-bin.000009Slave_IO_Running: Connector: mysql, information_schema, cece_schema, test, mysql, information_schema, performance_schema, testReplicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: rule: Last_Errno: 1054Last_Error: Error 'unknown column 'js _ Code' in 'field list' on query. default database: 'web _ platform '. query: 'Update act_id set js_code = 'ensure that PV tracking on this page has been reported; __tj (node, snode, cid, w ,'''','''', '''); Note: remark; 'where act_id = 11' Skip _ Counter: Until_Log_Pos: Counter: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: master_SSL_Key: Seconds_Behind_Master: Unknown: 0Last_IO_Error: Last_ SQL _Errno: 1054Last_ SQL _Error: Error 'unknown column 'js _ Code' in 'field list' on query. default database: 'web _ platform '. query: 'Update act_id set js_code = 'ensure that PV tracking on this page has been reported; __tj (node, snode, cid, w ,'''','''', '''); Note: remark; 'where act_id = 11' Replicate _ Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec)

2. We can see from the above that the js_code field of the table act_id does not exist in the slave database. We can compare the structure of the table between the master database and the slave database:
View the master database:

Mysql> desc act_id; + keys + ------------------ + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + keys + ------------------ + ------ + ----- + --------- + ---------------- + | act_id | int (10) unsigned | NO | PRI | NULL | auto_increment | name | varchar (512) | NO | NULL | fields | varchar (512) | NO | NULL | js_code | text | NO | NULL | remark | varchar (512) | NO | NULL | create_user | varchar (256) | NO | NULL | duty_user_name | varchar (256) | NO | NULL | duty_user_email | varchar (256) | YES | NULL | duty_user_phone | varchar (256) | YES | NULL | create_time | bigint (20) | NO | NULL | update_time | bigint (20) | NO | NULL | + ----------------- + ------------------ + ------ + ----- + --------- + ------------------ + 11 rows in set (0.00 sec)

View slave database:

Mysql> desc act_id; + keys + ------------------ + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + keys + ------------------ + ------ + ----- + --------- + ---------------- + | act_id | int (10) unsigned | NO | PRI | NULL | auto_increment | name | varchar (512) | NO | NULL | fields | varchar (512) | NO | NULL | remark | varchar (512) | NO | NULL | create_user | varchar (256) | NO | NULL | duty_user_name | varchar (256) | NO | NULL | duty_user_email | varchar (256) | YES | NULL | duty_user_phone | varchar (256) | YES | NULL | create_time | bigint (20) | NO | NULL | update_time | bigint (20) | NO | NULL | + ----------------- + ------------------ + ------ + ----- + --------- + ------------------ + 10 rows in set (0.00 sec)

3. Stop the slave process of the master database and slave database before officially handling the fault (my environment is bidirectional synchronization, that is, the master synchronization)

Mysql> slave stop; Query OK, 0 rows affected (0.12 sec)

4. Add missing fields to the slave database:

Alter table act_id add js_code text not Null AFTER fields;

5. Start the slave process of the slave database and view the master/slave status:

Mysql> slave start; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \ G ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 10.10.100.100Master _ User: Connector: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000009Read_Master_Log_Pos: Connector: mysqld-relay-bin.000117Relay_Log_Pos: Connector: mysql-bin.000009Slave_IO_Running: Connector: mysql, information_schema, cece_schema, test, mysql, information_schema, performance_schema, failed: Last_Errno: 0Last_Error: Skip_Counter: Failed: Until_Log_Pos: Failed: Master_SSL_Cert: Failed: Master_SSL_Key: Failed: noLast_IO_Errno: 0Last_IO_Error: Last_ SQL _Errno: 0Last_ SQL _Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec)