MySQL5.6 replication: Advantages and limitations of GTID (part 1) _ MySQL

MySQL5.6 replication: Advantages and limitations of GTID (part 1) bitsCN.com

The Global transaction Identifier (Global Transactions Identifier) is a new feature of MySQL 5.6 replication. It provides multiple possibilities for DBAs who maintain servers under a specific replication topology to significantly improve their working conditions. However, you should also understand the limitations of the current implementation. This blog post is the first part of a series of articles devoted to discussing what GTID enables in the production environment actually means.

This manual describes how to switch to GTID-based replication.

The basic steps are as follows:

• Let the host become read-only mode, so that the slave machine can execute all the time, so as to maintain the synchronization status with the host.
• Modify the configurations of all servers and restart them.
• Use change master to allow all servers TO use GTID
• Disable the read-only mode of the host.

This step will switch all your servers from normal replication to GTID replication. However, if you are running a production system, you may want to enable GTID at, which makes it easier to roll back once there are any other issues. Some entries in related documents are not clearly written.

For example:

• Do we really need to restart all servers at the same time? Downtime is something we can do to avoid!
• Is it necessary to change the host to read-only mode?
• Can we use GTID replication on some slave machines while using common replication?

To find the answer to these questions, let's first create a simple copy configuration. one host and two slave servers run MySQL 5.6 on all servers, and GTID is not enabled.

First try: configure only one of the servers to enable GTID

Let's stop the service on the slave machine 2 first, modify the configuration, and restart:

1 mysql> show slave status/G2 [...] 3Slave_IO_Running: No4Slave_ SQL _Running: Yes5 [...] the ERROR log shows why the IO thread is not started: 12013-05-17 13:21:26 3130 [ERROR] Slave I/O: the slave IO thread stops because the master has GTID_MODE OFF and this server has GTID_MODE ON, Error_code: 1593

Unfortunately, if you want to copy and run normally, gtid_mode must be ON or OFF all servers.

What if we reconfigure the host? This time, the replication on the slave Machine No. 1 will stop running:

12013-05-17 13:32:08 2563 [ERROR] Slave I/O: The slave IO thread stops because the master has GTID_MODE ON and this server has GTID_MODE OFF, Error_code: 1593

These two simple tests answered the first two questions: replication can run normally only when gtid_mode on all servers has the same value. Therefore, you should restart them at the same time, in addition, it is best to make the host read-only mode. However, "at the same time" means "at the same binlog location", so you can restart the server one by one.

Try again: enable GTID and use both common replication and GTID replication.

This time we enable GTID on the first slave instead of the second slave:

1 # slave #12 mysql> change master to master_auto_position = 1; 3 mysql> start slave; Next let's create a new table on the host:
1 mysql> create table test. t (id int not null auto_increment primary key );

Running show tables from test on both slave machines indicates that all servers have created this new table. Therefore, once GTID is enabled on all servers, you can enable some slaves to use file-based location and other slaves to use GTID-based location.

This answers the second question: we can make different servers have different replication modes, but all servers must set gtid_mode to ON. When gtid_mode is ON, file-based replication is also run. what does this mean? I have not found any use of this, so in practice, you may only use file-based replication (all servers are set to gtid_mode = off ), you can only use GTID-based replication (all servers are set to gtid_mode = on ).

Another question: how can I check the output of the SHOW SLAVE STATUS to see if a SLAVE is copied based on GTID? This can be distinguished by viewing the value of the last field Auto_Position:

1 # Slave #12 mysql> show slave status/G3 [...] 4Auto_Position: 1-> GTID-based positioning5 # Slave #26 mysql> show slave status/G7 [...] 8Auto_Position: 0-> File-based positioning conclusion

If your application cannot easily tolerate downtime or read-only mode, it will be very tricky to enable GTID-based replication, especially when you need to reconfigure a large number of servers, this is even more true. It would be nice to mix the gtid_mode ON server with the gtid_mode OFF server, because in this way, the process required for GTID-based replication can be simplified, if an error occurs, rollback is easier.

Address: http://www.mysqlperformanceblog.com/2013/05/21/replication-in-mysql-5-6-gtids-benefits-and-limitations-part-1/

BitsCN.com