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

I read an article in which server id caused the loss of transactions during the recovery of mysql backups, so I would like to reproduce it.

GTID is enabled for master / slave, and the experimental process is as follows:

1. Main library execution: create database test1;create database test2;2. Master-slave backup without delay, using slave library backup, physical or logical can be: mysqldump-uroot-poracle-- single-transaction-- master-data=2-- all-databases > dump.sql3. Main library execution: create database test3;4. Take out the main library by 5. Upgrade from library to main library, and: create database test4;6. Use the backup of the slave library to restore the old master library and point to the new master. At this time, you will find that the restored slave library has lost a transaction test3:mysql > show databases. +-+ | Database | +-+ | information_schema | | ming | | mysql | | performance_schema | | sakila | | sys | | test1 | | test2 | | test4 | | tt | | world | +-+ 11 rows in set (0.00 sec) |

The article says it is because of server_id.

One of the great functions of server id is to avoid data loopback. So the sever id recorded in the transaction will be permanent, just like our ID cards.

It doesn't change anywhere.

Because the old master library is restored from the backup set of the slave library, the transaction executed is 6f5b02b9-1f08-11ea-9853-000c2970dcdf:1-4.

Then request the 6f5b02b9-1f08-11ea-9853-000c2970dcdf:5 transaction from the new master.

The transaction in the new master master-bin log file is as follows: server id is 1573854809. And the server id happens to be the old master's server id.

The record is filtered out at this point. It won't be passed on to the old man.

# at 836 / 200328 11:23:25 server id 1573854809 end_log_pos 901CRC32 0x23ffdc70 GTID last_committed=4 sequence_number=5 rbr_only=noSET @ @ SESSION.GTID_NEXT= '6f5b02b9-1f08-11ea-9853-000c2970dcdf / / at 901 / 200328 11:23:25 server id 1573854809 end_log_pos 998 CRC32 0x2f611a1d Query thread_id=2 exec_time=4290974348 error_code=0SET timestamp 1585365805

So why is test4 passed to the owner to be applied? Because the transaction is in the new master master-bin log as follows, server id 1051295 is the new master's

It will not be filtered by IO thread.

# at 998years 200211 6:19:19 server id 1051295 end_log_pos 1063 CRC32 0xec9c6a1e GTID last_committed=5 sequence_number=6 rbr_only=noSET @ @ SESSION.GTID_NEXT= '4c312339-ab38-11e9-86a8-000c29050245 purge 1 "at 1063" 200211 6:19:19 server id 1051295 end_log_pos 1160 CRC32 0xaccb28ab Query thread_id=2 exec_time=0 error_code=0SET timeset 1581373159" session "set @ @ session.sqlsubscription modebook 1151336480 lead to session. Session. Sqlhammer modebook 1151336480 lead to create database test4

So why are the two records inconsistent? This is because the test3 transaction is passed by the old master, so in relay log, in master-bin log

And when passed back to other slave libraries, the server id will be taken down all the time. The test4 transaction is the new master's own transaction.

Then it will be the new master's server id from his own master-bin log, as well as the relay log passed back from the library and the master-bin log generated by the application.

So test3 will be filtered and test4 will be applied.

The old main library is now:

Mysql > show master status\ gateway * 1. Row * * File: mysql-bin.000002 Position: 1443 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1508afe9-70a7-11ea-8d70-000c2970dcdf:1-3 -- transaction executed in your own library 4c312339-ab38-11e9-86a8-000c29050245 6f5b02b9 sec-transaction passed from master to slave 6f5b02b9-master-11ea-9853-000c2970dcdf:1-4ML-transaction executed by yourself as master (0.00 sec)

New main library:

Mysql > show master status\ Gateway * 1. Row * * File: mysql-bin.000002 Position: 1322 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4c312339-ab38-11e9-86a8-000c29050245:1-2 6f5b02b9-1f08- 11ea-9853-000c2970dcdf:1-51 row in set (0.00 sec)

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.