MySQL使用GTID跳过事务

  • A+
所属分类：Database

从库：

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100
                  Master_UUID: abc
             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: 170616 12:48:37
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: efg:16139485-18367652,
abc:1-4
            Executed_Gtid_Set: efg:1-18367652,
abc:1-4

主库：

0
1
2
3
4
5
6
7
8
9
10
11
12
mysql> show global variables like '%gtid%'\G
*************************** 3. row ***************************
Variable_name: gtid_executed
        Value: efg:1-18125360:18367653-18401437,
abc:1-6
*************************** 7. row ***************************
Variable_name: gtid_purged
        Value: abc:1-18125360,
efg:1-3
*************************** 8. row ***************************
Variable_name: session_track_gtids
        Value: OFF
8 rows in set (0.00 sec)

congku:

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                                     |
+----------------------------------+-------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                        |
| enforce_gtid_consistency         | ON                                                                                        |
| gtid_executed                    | efg:1-18367652,
abc:1-4 |
| gtid_executed_compression_period | 1000                                                                                      |
| gtid_mode                        | ON                                                                                        |
| gtid_owned                       |                                                                                           |
| gtid_purged                      | efg:1-17402840,
abc:1-3 |
| session_track_gtids              | OFF                                                                                       |
+----------------------------------+-------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

0
1
2
3
4
5
6
7
8
9
10
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> reset slave;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql> quit
/etc/init.d/mysqld stop
mysql> reset slave;
mysql> reset master;
mysql> set @@global.gtid_purged='78488ade-0dfc-11e7-ad71-00163e00dadd:1-18125360,b2723b81-468b-11e7-8636-00163e1ab1b4:1-3,78488ade-0dfc-11e7-ad71-00163e00dadd:1-18367652,b2723b81-468b-11e7-8636-00163e1ab1b4:1-4';
Query OK, 0 rows affected (0.00 sec)