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

Error handling in replication

Traditional replication errors skip:

Stop slave sql_thread

Set global slq_slave_skip_counter=1

Start slave sql_thread

GTID replication error skips:

Stop slave sql_thread

Set gtid_next='uuid:N'

Begin;commit

Set gtid_next='automatic'

Start slave sql_thread

Note:

For binlog+pos replication, use:

Set global sql_salve_skip_counter=1

Instead of the following steps:

Root@localhost [testdb] > set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380purl 13'

Root@localhost [testdb] > begin;commit

Root@localhost [testdb] > set gtid_next='automatic'

Error classification and treatment of master-slave replication

(1) the master library create table. The slave library already exists. The master database shall prevail.

Slave:

Set sql_log_bin=0

Drop table t1

Set sql_log_bin=1

Start slave sql_thread

Example:

Slave:

Root@localhost [testdb] > create table T2 (C1 int,c2 varchar (20))

Master:

Root@localhost [testdb] > create table T2 (C1 int,c2 varchar (20))

Root@localhost [testdb] > show slave status\ G

.

Last_Error: Error 'Table 't2' already exists' on query. Default database: 'testdb'. Query: 'create table T2 (C1 int,c2 varchar (20))'

.

Solution:

Slave:

The # drop operation does not record the binlog of the slave library. The purpose of this step is to prevent the T2 table of the master library from being wiped out when the master-slave switch occurs later.

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > drop table T2

Root@localhost [testdb] > set sql_log_bin=1

Root@localhost [testdb] > start slave sql_thread

(2) error error1062 of insert primary key conflict

Solution: directly delete the conflict primary key from the slave library

Example:

Slave:

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > set sql_log_bin=1

Master:

Root@localhost [testdb] > insert into T1 values (2meme bbbbbbbbbb')

Slave:

Root@localhost [testdb] > show slave status\ G

Last_Errno: 1062

Last_Error: Could not execute Write_rows event on table testdb.t1; Duplicate entry'2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000029, end_log_pos 2796

Slave:

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > delete from T1 where c1x2

Root@localhost [testdb] > set sql_log_bin=1

Root@localhost [testdb] > start slave sql_thread

(3) update cannot find record error1032

The only way: to falsify qualified data

Example:

Master:

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > set sql_log_bin=1

Root@localhost [testdb] > update T1 set c2 Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Slave:

Root@localhost [testdb] > show slave status\ G

.

Last_Error: Could not execute Update_rows event on table testdb.t1; Can't find record in't 1 million, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000029, end_log_pos 2529

Skip_Counter: 0

Exec_Master_Log_Pos: 2283

Master:

[root@Darren1 logs] # mysqlbinlog-base64-output=decode-rows-verbose-start-position=2283-stop-position=2529 mysql-bin.000029

.

# UPDATE `testdb`.`t1`

# WHERE

# @ 1room1

# @ 2roomaaaa'

# SET

# @ 1room1

# @ 2 roomaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Slave:

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > set sql_log_bin=1

Root@localhost [testdb] > start slave sql_thread

(4) delete cannot find the wrong error1032

Method 1: falsify qualified data

Example:

Master:

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > set sql_log_bin=1

Root@localhost [testdb] > delete from T1 where C1

Slave:

Root@localhost [testdb] > show slave status\ G

.

Slave_IO_Running: Yes

Slave_SQL_Running: No

Exec_Master_Log_Pos: 905-the postion point to which the slave library has successfully executed the master library

Last_SQL_Error: Could not execute Delete_rows event on table testdb.t1; Can't find record in 't1clients, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000029, end_log_pos 1138-- execute the end point from the library

Maser:

[root@Darren1 logs] # mysqlbinlog-base64-output=decode-rows-verbose-start-position=905-stop-position=1138 mysql-bin.000029

.

# DELETE FROM `testdb`.`t1`

# WHERE

# @ 1room1

# @ 2roomaaaa'

Slave:

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > set sql_log_bin=1

Root@localhost [testdb] > start slave sql_thread

Method 2: skip the GTID corresponding to the row records that were not deleted successfully from the library

Master:

Root@localhost [testdb] > set sql_log_bin=0

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > set sql_log_bin=1

Root@localhost [testdb] > delete from T1 where C1 = 1

Root@localhost [testdb] > delete from T1 where C1 = 2

Root@localhost [testdb] > insert into T1 values (3meme ccc')

Slave:

Root@localhost [testdb] > show slave status\ G

.

Last_SQL_Error: Could not execute Delete_rows event on table testdb.t1; Can't find record in't 1 million, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000029, end_log_pos 1402

Retrieved_Gtid_Set: f0e27aec-b275-11e6-9c17-000c29565380:1-14-- from the GTID point where the library ends

Executed_Gtid_Set: ab6320bc-d158-11e6-88f8-000c29c1b8a9:1

F0e27aec-b275-11e6-9c17-000c29565380:10-11-GTID successfully executed from the library

Slave:

Root@localhost [testdb] > stop slave

Root@localhost [testdb] > set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380purl 12'

Root@localhost [testdb] > begin;commit

Root@localhost [testdb] > set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380purl 13'

Root@localhost [testdb] > begin;commit

Root@localhost [testdb] > set gtid_next='automatic'

Root@localhost [testdb] > start slave

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.