Tutorial on using GTIDs replication protocol and interrupt protocol in MySQL, mysqlgtids

Tutorial on using GTIDs replication protocol and interrupt protocol in MySQL, mysqlgtids

MySQL5.6 has many new features, and many of them are interested in the global transaction sequence number function (GTIDs ). The reason why everyone is very interested in this feature is also very understandable, that is, it is always very troublesome to re-connect the slave server and a new master server, however, it becomes easy to enable the GTIDs function. However, GTIDs not only replaces the old binary log file/location with a separate identifier, but also adopts a new replication protocol. If you do not understand this yet, you can learn something in this article.
Replication protocol: New VS old

The old protocol is often simple: first, connect to a given binary log file from the server at a specific offset, and then the master server sends all transactions from there.

The new protocol is slightly different: slave first sends the range of GTID that has been executed, and then the master sends every lost transaction. it also ensures that a given GTID can be executed only once in a specific slave.

In practice, will this change anything? In this way, it will change many things. Imagine the following scenario: You want to copy from trx 4, but trx2 is lost on slave for some reason.

If the old protocol is used, trx 2 will no longer be executed once. If the new protocol is used, it will be automatically executed again.

The following are two general scenarios of the new protocol.

Skip transactions

As we all know, the old set global SQL _slave_skip_counter = N is no longer supported when you want to skip a transaction, and GTID can be enabled. replace gtid xxx: N to skip the transaction. You must inject an empty transaction:
 

mysql> SET gtid_next = 'XXX:N';mysql> BEGIN; COMMIT;mysql> SET gtid_next = 'AUTOMATIC';

Why can't we use SQL _slave_skip_counter? This is because of the new replication protocol!

Imagine we have three servers, as shown in figure:

Let's assume that SQL _slave_skip_counter can be used and has been used on S2 to skip trx2. what will happen if S2 is set to an slave of S1?

The two servers exchange the range of GTID executed, and S1 will realize that it must send trx2 to S2. then there are two possibilities for this:

1. If trx 2 is still in the binary log of S1, it will be sent to S2, and the transaction will not be skipped.
2. If trx 2 no longer exists in the binary log of S1, you will get a replication error.

Obviously this is not safe. This is why SQL _slave_skip_counter cannot be used when GTID is used. to skip a transaction, the only safe choice is to execute a virtual transaction instead of a real transaction.
 
Wrong transaction

If you execute a transaction locally on a slave (known as an error transaction in the MySQL document), what will happen if you are pushed to the new master by this transaction?

There is basically nothing to do with the old Protocol (to be accurate, the data between the new master and its slave will be inconsistent, but it may be repaired later ).

When the new protocol is used, wrong transactions will be identified as lost everywhere and will be automatically executed on Fault-Tolerant backups, which will lead to the risk of interrupting replication.

For example, you have one master (M) and two slave (S1 and S2 ). there are two scenarios where the Server Load balancer will fail to be reconnected to the new master (with different replication errors:

# Scenario 1
 

# S1mysql> CREATE DATABASE mydb;# Mmysql> CREATE DATABASE IF NOT EXISTS mydb;# Thanks to 'IF NOT EXITS', replication doesn't break on S1. Now move S2 to S1:# S2mysql> STOP SLAVE; CHANGE MASTER TO MASTER_HOST='S1'; START SLAVE;# This creates a conflict with existing data!mysql> SHOW SLAVE STATUS\G[...]Last_SQL_Errno: 1007        Last_SQL_Error: Error 'Can't create database 'mydb'; database exists' on query. Default database: 'mydb'. Query: 'CREATE DATABASE mydb'[...]

# Scenario 2
 

# S1mysql> CREATE DATABASE mydb;# Now, we'll remove this transaction from the binary logs# S1mysql> FLUSH LOGS;mysql> PURGE BINARY LOGS TO 'mysql-bin.000008';# Mmysql> CREATE DATABASE IF NOT EXISTS mydb;# S2mysql> STOP SLAVE; CHANGE MASTER TO MASTER_HOST='S1'; START SLAVE;# The missing transaction is no longer available in the master's binary logs!mysql> SHOW SLAVE STATUS\G[...]Last_IO_Errno: 1236        Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'[...]

As you can understand, you should avoid wrong transactions by using GTID-based services. If you need to run a local transaction, the best choice is to disable binary logs for that specific statement:
 

mysql> SET SQL_LOG_BIN = 0;mysql> # Run local transaction

Conclusion

GTIDs makes great progress in making it easier for us to reconnect to copies of other servers. However, we also face new difficulties and challenges in O & M. If you plan to start using GTIDs, you have to understand the new replication protocol. Otherwise, you will end the replication process in an unexpected way.