Shulou(Shulou.com)06/01 Report--

Recently, the platform has to be migrated. Due to the requirements of the business migration process, it is hoped that slave can not only ensure that the data of master can be synchronized to slave, but also hope that slave can be update.

This requirement, as DBA, can be judged from the database level alone, is not feasible. However, the actual test is convincing. Speak with data / experimental results.

Start to build a test master and slave to test slave's update.

Set the read_only of slave to on so that slave can be changed. Note the parameter .read _ only here. For root or have (.) Users with full instance permissions are not valid. The main library creates a test table test2

On slave, you can see that test2 has been synchronized.

Query the number of table rows

Mysql > select count (*) from test2;+-+ | count (*) | +-+ | 23502 | +-+ 1 row in set (0.01sec)

Delete 10 lines

Mysql > delete from test2 where id select count (*) from test2;+-+ | count (*) | +-+ | 23493 | +-+ 1 row in set (sec) is in the main database. Modify table data mysql > select count (*) from test2 +-+ | count (*) | +-+ | 23502 | +-+ 1 row in set (0.01 sec) Delete 5 rows of data mysql > delete from test2 where id select count (*) from test2;+-+ | count (*) | +-+ | 23498 | +-+ 1 row in set (0.01 sec)

Then go to the standby library to check the synchronization status.

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 10.27.20.4Master_User: envisionMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000043Read_Master_Log_Pos: 57380204Relay_Log_File: Replay-bin.000003Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000043Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1032Last_Error: Could not execute Delete_rows event on table cnpmjs.test2 Can't find record in 'test2', Error_code: 1032; handler error HA_ERR_END_OF_FILE The event's master log mysql-bin.000043 End_log_pos 57380173Skip_Counter: 0Exec_Master_Log_Pos: 57375790Relay_Log_Space: 13539720Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1032Last_SQL_Error: Could not execute Delete_rows event on table cnpmjs.test2 Can't find record in 'test2', Error_code: 1032; handler error HA_ERR_END_OF_FILE The event's master log mysql-bin.000043 End_log_pos 57380173Replicate_Ignore_Server_Ids:Master_Server_Id: 102Master_UUID: b095e989-7dcf-11e8-83a2-0017fa032e39Master_Info_File: / var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State:Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp: 180702 14:54:00Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec)

The above error has indicated that the master-slave data is inconsistent and cannot be synchronized.

Fix the data on slave by inserting 10 missing lines mysql > insert into test2 select * from user where id stop slave;Query OK, 0 rows affected (0.00 sec) mysql > start slave Query OK, 0 rows affected (0.01 sec) query again, you can see Slave continues to synchronize. MySQL > show slave status\ Gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 10.27.20.4Master_User: envisionMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000043Read_Master_Log_ Pos: 57380204Relay_Log_File: replay-bin.000005Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000043Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 57380204Relay_Log_Space: 688Until_Condition: NoneUntil_Log_File:Until_ Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 102Master_UUID: b095e989-7dcf-11e8-83a2-0017fa032e39Master_Info_File: / var/ Lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log Waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec) query data, which is also the information after the main database deletes 5 entries. MySQL > select count (*) from test2 +-+ | count (*) | +-+ | 23498 | +-+ 1 row in set (sec) update master again, depending on the master / slave synchronization mysql > insert into test2 select * from test2;Query OK, 23498 rows affected (1.01 sec) Records: 23498 Duplicates: 0 Warnings: 0 query the number of rows of the master database table mysql > select count (*) from test2 +-+ | count (*) | +-+ | 46996 | +-+ 1 row in set (0.02 sec) slave database query. MySQL > select count (*) from test2;+-+ | count (*) | +-+ | 46996 | +-+ 1 row in set (0.02 sec)

The previous test is delete, now test whether update is feasible

In the slave database, modify the data mysql > update test2 set json=0 where id=5;Query OK, 2 rows affected (0.08 sec) Rows matched: 2 Changed: 2 Warnings: 0mysql > select * from test2 where id=5\ G * * 1. Row * * id: 5gmt_create: 2016-02-23 12:28:51gmt_modified: 2016-02-23 12:28:51name: m_golsalt: 0password_sha: 0ip: 0roles: [] rev: 2-379c3d7dfc06312105072ec0ccf84b4aemail: m.goleb@gmail.comjson: 0npm_user: 1 in the main library Modify data mysql > update test2 set json=1000 where id=5 Query OK, 0 rows affected (0.07 sec) Rows matched: 2 Changed: 0 Warnings: 0mysql > select * from test2 where id=5\ G * 1. Row * * id: 5gmt_create: 2016-02-23 12:28:51gmt_modified: 2016-02-23 12:28:51name: m_golsalt: 0password_sha: 0ip: 0roles: [] rev: 2-379c3d7dfc06312105072ec0ccf84b4aemail: m.goleb@gmail.comjson: 1000npm_user: 1 query slave synchronization status mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 10.27.20.4Master_User: envisionMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000043Read_Master in the slave database _ Log_Pos: 70913715Relay_Log_File: replay-bin.000005Relay_Log_Pos: 13529969Relay_Master_Log_File: mysql-bin.000043Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1032Last_Error: Could not execute Update_rows event on table cnpmjs.test2 Can't find record in 'test2', Error_code: 1032; handler error HA_ERR_END_OF_FILE The event's master log mysql-bin.000043 End_log_pos 70912987Skip_Counter: 0Exec_Master_Log_Pos: 70909853Relay_Log_Space: 13534935Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1032Last_SQL_Error: Could not execute Update_rows event on table cnpmjs.test2 Can't find record in 'test2', Error_code: 1032; handler error HA_ERR_END_OF_FILE The event's master log mysql-bin.000043 End_log_pos 70912987Replicate_Ignore_Server_Ids:Master_Server_Id: 102Master_UUID: b095e989-7dcf-11e8-83a2-0017fa032e39Master_Info_File: / var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State:Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp: 180702 15:23:42Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec)

To sum up, slave cannot be modified, especially in the same table, and in the same row, if the lookup row does not exist. It will cause synchronization to fail.

Even if it is not delete, just update, it is not allowed.

So slave still needs to exist in the form of read_only. In order to ensure that the group has been synchronized normally.

The reason is that in binglog, the context of the row information is recorded, and if the context does not match (modified on the slave side), the recover of the subsequent binglog cannot correctly find the information that needs to be restored, and the recover cannot be executed successfully, resulting in the failure of slave synchronization.

Refer to the following binlog interception information:

Mysqlbinlog mysql-bin.000005 > > test.log#181126 9:53:49 server id 101end_log_pos 8342676 CRC32 0x928e28de Anonymous_GTID last_committed=2436 sequence_number=2437 rbrachionlyzed keys / test.log#181126 9:53:49 server id 8342676 / 181126 server id 101 end_log_pos 8342758 CRC32 0x1068a385 Query thread_id=58717 exec_time=0 error_code=0SET timespacks 1543226029 # at 8342758#181126 9:53:49 server id 101 end_log_pos 8342849 CRC32 0x2ee04d35 Table_map: `test`.`app_purchases` mapped to number 290# at 8342849#181126 9:53:49 server id 101 end_log_pos 8343168 CRC32 0x598ec3d0 Update_rows: table id 290 flags: STMT_END_FBINLOG 'rcL7WxNlAAAAWwAAAEFNfwAAACIBAAAAAAEADmVvc19wb3J0YWxfd2ViAA1hcHBfcHVyY2hhc2VzAAsPDw8PAQ8SEhESAQ7AAMAAwADAAP0CAAAAAPgHNU3gLg==rcL7Wx9lAAAAPwEAAIBOfwAAACIBAAAAAAEAAgAL/////2D7JDBlM2FmNzJkLWIxNDctNGQ3My1hZjY5LTBhNDQxZmZmMTdiMSQzN2Q2ZDJjZS1hZGU2LTRhNzEtOTEzYS1lNDJhMTQ5NmQxMDAPbzE1NDI1MjM2NjI4NDcxJDM3ZDZkMmNlLWFkZTYtNGE3MWQ5ZDc1NWUyLWJmYTQtNDA3ZgKZoXSdawBg+CQwZTNhZjcyZC1iMTQ3LTRkNzMtYWY2OS0wYTQ0MWZmZjE3YjEkMzdkNmQyY2UtYWRlNi00YTcxLTkxM2EtZTQyYTE0OTZkMTAwD28xNTQyNTIzNjYyODQ3MSQzN2Q2ZDJjZS1hZGU2LTRhNzFkOWQ3NTVlMi1iZmE0LTQwN2YCmaF0nWtb+8KtmaF0nXEB0MOOWQ=='/*!*/;# at 8343168#181126 9:53:49 server id 101 end_log_pos 8343199 CRC32 0xcb9106dc Xid = 400668COMMIT/*!*/;# at 8343199

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.