tutorial on using Gtids Replication protocol and interrupt protocol in MySQL

MySQL5.6 has many new features, many of whom are interested in the global transaction ordinal function (Gtids). The reason why everyone is interested in this feature is that it's always a hassle to reconnect from the server and a new primary server, but it's easy to do after enabling the Gtids feature. However, the use of Gtids not only replaces the old binary log file/location with a separate identifier, it also uses a new replication protocol. If you don't quite understand this, you can learn something in this article.

Replication protocol: New VS old

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

The new protocol is slightly different: Slave first sends a range of gtid that it has already performed, and master sends every missing transaction. It also ensures that a given gtid can only be executed once in a particular slave.

In practice, does this change anything? So that it will change a lot of things. Imagine the following scenario: you want to start copying from Trx 4, but trx2 is lost for some reason on slave.

With the old protocol, TRX 2 will never be executed again, and with the new protocol, it will be executed automatically once more.

Here are two common scenarios where you can see the new protocol in practice.

Skip Transaction

It is well known that the old SET GLOBAL sql_slave_skip_counter = N no longer provides support when you want to skip a transaction, and Gtid can be enabled. Swap with Gtid xxx:n to skip transactions, you need to 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? Just because of the new replication protocol!

Imagine that we have three servers as shown in the following illustration:

Let's assume that sql_slave_skip_counter can be used and has been used on S2 to skip trx2. What would happen if you S2 a slave set to S1?

Two servers are exchanged for the Gtid range, and S1 will realize that they must send trx2 to S2. Then there are two possible things that can happen:

If Trx 2 is still in the S1 binary log, it will be sent to S2, and the transaction will not be skipped.

If TRX 2 no longer exists in the S1 binary log, you will get a copy error.

Obviously this is not safe, which is why Sql_slave_skip_counter is not available when using Gtid. The only safe option to skip a transaction is to perform a virtual transaction, rather than a real transaction.

The wrong transaction

What happens if you perform a transaction locally on a slave (referred to as an error transaction in a MySQL document), if you are pushed to the new master by this transaction?

With the old protocol, there's basically nothing (to be exact, the data between the new master and its slave will be inconsistent, but it may be repaired later).

With the new protocol, the wrong transactions will be recognized as being lost everywhere, and will automatically be executed on a fault-tolerant backup, which can cause disruption to replication.

For example, you have a master (M) and two slave (S1 and S2). Here are two scenarios where the slave will be attached to the new master (with a different replication error):

# scene 1

# S1

mysql> CREATE DATABASE mydb;

# M

mysql> CREATE DATABASE IF not EXISTS mydb;

# to ' IF not EXITS ', replication doesn ' t break on S1. Now move S2 to S1:

# S2

Mysql> STOP SLAVE; Change MASTER to master_host= ' S1 '; START SLAVE;

# This creates a conflict with existing data!

Mysql> Show SLAVE STATUSG

[...]

last_sql_errno:1007

Last_sql_error:error ' Can ' t create database ' mydb '; Database exists ' on query. Default database: ' MyDB '. Query: ' CREATE DATABASE mydb '

[...]

# scene 2

# S1

mysql> CREATE DATABASE mydb;

# Now, we'll remove this transaction from the binary logs

# S1

Mysql> FLUSH LOGS;

Mysql> PURGE BINARY LOGS to ' mysql-bin.000008 ';

# M

mysql> CREATE DATABASE IF not EXISTS mydb;

# S2

Mysql> 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 STATUSG

[...]

last_io_errno:1236

Last_io_error:got fatal Error 1236 from master then reading data from binary log: ' The slave was connecting using change M ASTER to Master_auto_position = 1, but the MASTER has purged binary logs containing gtids this slave requires. '

[...]

You can understand that the wrong transaction should be avoided with the help of Gtid based services. If you need to run a local transaction, the best option is to disable the binary log for that particular statement:

mysql> SET sql_log_bin = 0;

Mysql> # Run Local transaction

Conclusion

Gtids is a big step forward in making it easy for us to reconnect with other servers. However, we are faced with new difficulties and challenges in the field of operation and maintenance. If you're going to start using gtids, then you have to really understand the new replication protocol, or you'll end the replication process in an unexpected way.