Description:
We test MySQL failover by kill -9, after a long time test, when check the gtid, the gtid in mysql-bin.000004 all lost.
In mysql-bin.000004, we can see the gtid below, the previous_gtids is 3817c69d-0f19-11e9-9200-0242c0a8300f:1-1180, and next gtid is 3817c69d-0f19-11e9-9200-0242c0a8300f:1181
mysql> show binlog events in 'mysql-bin.000004' limit 12;
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 1 | 194 | 3817c69d-0f19-11e9-9200-0242c0a8300f:1-1180 |
| mysql-bin.000004 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= '3817c69d-0f19-11e9-9200-0242c0a8300f:1181' |
| mysql-bin.000004 | 259 | Query | 1 | 345 | BEGIN |
| mysql-bin.000004 | 345 | Table_map | 1 | 415 | table_id: 111 (flashback_test.6) |
| mysql-bin.000004 | 415 | Update_rows | 1 | 905 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000004 | 905 | Xid | 1 | 936 | COMMIT /* xid=8780 */ |
| mysql-bin.000004 | 936 | Gtid | 1 | 1001 | SET @@SESSION.GTID_NEXT= '3817c69d-0f19-11e9-9200-0242c0a8300f:1182' |
| mysql-bin.000004 | 1001 | Query | 1 | 1087 | BEGIN |
| mysql-bin.000004 | 1087 | Table_map | 1 | 1157 | table_id: 111 (flashback_test.6) |
| mysql-bin.000004 | 1157 | Update_rows | 1 | 2111 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000004 | 2111 | Xid | 1 | 2142 | COMMIT /* xid=8788 */ |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
12 rows in set (0.00 sec)
In mysql-bin.000005, there no previous_gtids event at all, maybe this time, while writing this event, MySQL is kill again. The file size is 123, equal the End_log_pos.
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)
At in mysql-bin.000006, the previous_gtids is empty, the next gtid is 3817c69d-0f19-11e9-9200-0242c0a8300f:1181 now, all gtids in mysql-bin.000004 is missing.
mysql> show binlog events in 'mysql-bin.000006' limit 10;
+------------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000006 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000006 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '3817c69d-0f19-11e9-9200-0242c0a8300f:1181' |
| mysql-bin.000006 | 219 | Query | 1 | 305 | BEGIN |
| mysql-bin.000006 | 305 | Table_map | 1 | 375 | table_id: 110 (flashback_test.6) |
| mysql-bin.000006 | 375 | Update_rows | 1 | 8445 | table_id: 110 |
| mysql-bin.000006 | 8445 | Update_rows | 1 | 15845 | table_id: 110 |
| mysql-bin.000006 | 15845 | Update_rows | 1 | 23991 | table_id: 110 |
| mysql-bin.000006 | 23991 | Update_rows | 1 | 31987 | table_id: 110 |
| mysql-bin.000006 | 31987 | Update_rows | 1 | 40175 | table_id: 110 |
+------------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------+
10 rows in set (0.00 sec)
The my.cnf is:
[mysqld]
binlog-format=row
binlog-ignore-db=dbscale_tmp
enforce-gtid-consistency
general_log=1
gtid-mode=on
innodb_buffer_pool_size=32M
innodb_lock_wait_timeout=31560000
log-slave-updates
log_bin=mysql-bin
log_error=mysql-error
log_timestamps=system
lower_case_table_names=1
max_allowed_packet=16M
max_connections=1000
port=3306
skip-slave-start
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
user=mysql
How to repeat:
none
