gtid 1032同步异常处理

[root@dba_test_002 ~]# cat  3.sql

# 在从库中执行

set sql_log_bin=0;

insert into fudao_student_lable(uid,tid) values(1,11);

insert into fudao_student_lable(uid,tid) values(2,22);

insert into fudao_student_lable(uid,tid) values(3,33);

insert into fudao_student_lable(uid,tid) values(4,44);

set sql_log_bin=1;

mysql> select * from fudao_student_lable;

+----+-----+-----+

| id | uid | tid |

+----+-----+-----+

|  1 |   1 |  11 |

|  2 |   2 |  22 |

|  3 |   3 |  33 |

|  4 |   4 |  44 |

+----+-----+-----+

4 rows in set (0.00 sec)

# 在主库中执行

insert into fudao_student_lable(uid,tid) values(1,11);

insert into fudao_student_lable(uid,tid) values(2,22);

insert into fudao_student_lable(uid,tid) values(3,33);

insert into fudao_student_lable(uid,tid) values(4,44);

mysql> select * from fudao_student_lable;

+----+-----+-----+

| id | uid | tid |

+----+-----+-----+

|  1 |   1 |  11 |

|  2 |   2 |  22 |

|  3 |   3 |  33 |

|  4 |   4 |  44 |

+----+-----+-----+

4 rows in set (0.00 sec)

# 在从库中查看

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.10.50.60

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000021

          Read_Master_Log_Pos: 3272

               Relay_Log_File: relay-bin.000051

                Relay_Log_Pos: 2430

        Relay_Master_Log_File: mysql-bin.000021

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 1062

                   Last_Error: Could not execute Write_rows event on table test.fudao_student_lable; Duplicate entry '' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000021, end_log_pos 2452

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 2220

              Relay_Log_Space: 90228961

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 1062

               Last_SQL_Error: Could not execute Write_rows event on table test.fudao_student_lable; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000021, end_log_pos 2452

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1574144089

                  Master_UUID: 0501f340-0a94-11ea-ad2b-5254007dcbb3

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp: 191204 17:20:16

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:10-5440875

            Executed_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:1-5440871,

137347eb-0a94-11ea-ad2b-525400dd43f8:1-531071

                Auto_Position: 0

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> 

# 分析主库中的日志

#191204 17:20:16 server id 1574144089  end_log_pos 2268 CRC32 0xe50d7ecf        GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '0501f340-0a94-11ea-ad2b-5254007dcbb3:5440872'/*!*/;

# at 2268

#191204 17:20:16 server id 1574144089  end_log_pos 2340 CRC32 0xa0a551a6        Query   thread_id=3294  exec_time=0     error_code=0

SET TIMESTAMP=1575451216/*!*/;

BEGIN

/*!*/;

# at 2340

#191204 17:20:16 server id 1574144089  end_log_pos 2404 CRC32 0x01555dc8        Table_map: `test`.`fudao_student_lable` mapped to num

ber 70

# at 2404

#191204 17:20:16 server id 1574144089  end_log_pos 2452 CRC32 0x6cc26db4        Write_rows: table id 70 flags: STMT_END_F

### INSERT INTO `test`.`fudao_student_lable`

### SET

###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

###   @2=1 /* INT meta=0 nullable=0 is_null=0 */

###   @3=11 /* INT meta=0 nullable=0 is_null=0 */

# at 2452

SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440872';

BEGIN;COMMIT;

SET GTID_NEXT='AUTOMATIC';

START SLAVE;

show slave status\G;

SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440873';

BEGIN;COMMIT;

SET GTID_NEXT='AUTOMATIC';

START SLAVE;

show slave status\G;

SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440874';

BEGIN;COMMIT;

SET GTID_NEXT='AUTOMATIC';

START SLAVE;

show slave status\G;

SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440875';

BEGIN;COMMIT;

SET GTID_NEXT='AUTOMATIC';

START SLAVE;

show slave status\G;

#在从库中执行

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.10.50.60

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000021

          Read_Master_Log_Pos: 3272

               Relay_Log_File: relay-bin.000051

                Relay_Log_Pos: 3482

        Relay_Master_Log_File: mysql-bin.000021

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 3272

              Relay_Log_Space: 90228961

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1574144089

                  Master_UUID: 0501f340-0a94-11ea-ad2b-5254007dcbb3

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:10-5440875

            Executed_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:1-5440875,

137347eb-0a94-11ea-ad2b-525400dd43f8:1-531071

                Auto_Position: 0

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> 

[root@dba_test_002 ~]#
巴特西

gtid 1032同步异常处理

gtid 1032同步异常处理

最新文章

热门文章
