Practice of new characteristics of MySQL5.6 Gtid

This article will briefly introduce the principle of master-slave replication based on 5.6 gtid. And through several experiments to introduce the related fault treatment

Gtid Profile What is Gtid

GTID (global Transaction ID) is the number of a committed transaction and is a globally unique number. Gtid is actually made up of Uuid+tid. Where the UUID is the unique identity of a MySQL instance. The TID represents the number of transactions that have been committed on the instance and is monotonically incremented as the transaction commits. Here is a specific form of a Gtid

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

A more detailed introduction can be found in: official documentation

The role of Gtid

So what is the purpose of the Gtid function? There are two main points in the specific induction:

• According to Gtid, you can tell which instance the transaction was originally committed on.
• Gtid's presence facilitates replication's failover.

Here is a detailed explanation of the 2nd. We can look at the operation of replication failover before the gtid of MySQL 5.6 appears. Suppose we have an environment like this at this time,Server AServer outage, you need to switch your business toServer BOn At the same time, we need toServer CThe copy source is changed toServer B。 The command syntax for copying source modifications is simpleCHANGE MASTER TO MASTER_HOST=‘xxx‘, MASTER_LOG_FILE=‘xxx‘, MASTER_LOG_POS=nnnn。 The difficulty is that, because the same transaction on each machine is located in the Binlog name and location are different, then how to findServer CCurrent synchronization stop point, correspondingServer BOfmaster_log_fileAndmaster_log_posWhen it is, it becomes a problem. This is why the M-s replication cluster needs to useMMM,MHAAn important reason for such an additional management tool. This problem is very simple after the appearance of Gtid in 5.6. Because the gtid of the same transaction is consistent across all nodes, theServer CThe Gtid of the current stop can be uniquely positioned toServer BOn the Gtid. Even becauseMASTER_AUTO_POSITIONfunction, we do not need to know the specific value of Gtid, directly useCHANGE MASTER TO MASTER_HOST=‘xxx‘, MASTER_AUTO_POSITIONcommand to complete failover's work directly. So easy isn't it?

Build

This build uses a mysql_sandbox script-based, first-in-one, three-from-a-position replication environment. The entire schema is then designed for Gtid-based replication through configuration modifications. If you are not yet familiar mysql_sandbox , you can read the blog before the article blog before the article step-by-step installation. Build recommendations based on the Gtid in the MySQL official documentation. You need to make configuration changes to the master and slave nodes once and restart the service. Such an operation is obviously unacceptable in the production environment when it is upgraded. , all of which have been Facebook Booking.com Percona optimized by patch and have achieved a more elegant upgrade. The specific mode of operation will be described in a later post. Here we have an experimental upgrade in accordance with the official documentation. The main upgrade steps are as follows:

• Ensure master-Slave synchronization
• Configure READ_ONLY on Master to ensure no new data is written
• Modify my.cnf and restart the service on master
• Modify the slave my.cnf and restart the service
• Execute on slave change master to and bring on master_auto_position=1 enable Gtid-based replication

Due to the experimental environment, READ_ONLY and service restarts are not a major hindrance. As long as the official Gtid to build the recommendations can be successfully completed upgrade, here will not repeat the detailed process. Here are some of the errors that are easily encountered during the upgrade process.

Common errors

gtid_mode=ON, log_slave_updates enforce_gtid_consistency These three parameters must be configured at the same time my.cnf . Otherwise mysql.err , the following error will appear in the

[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates

[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 requires --enforce-gtid-consistency

Change Master to after warnings

After following the documentation change master to , you will find two warnings. In fact, there are two security warnings that do not interfere with normal synchronization (interested readers can look at a specific introduction to the warning. The specific contents of warning are as follows:

slave1 [localhost] {msandbox} ((none)) > Stop slave; Query OK, 0 rows affected (0.03 sec) slave1 [localhost] {msandbox} ((none)) > Change master to master_host= ' 127.0.0.1 ', M Aster_port =21288,master_user= ' Rsandbox ', master_password= ' Rsandbox ', master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.04 sec) slave1 [localhost] {msandbox} ((none)) > Show warnings;+-------+------+ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------+| Level | Code |                                                                                                                                                                                                                                             Message |+-------+------+----------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------+| Note | 1759 |                                                                                                                                                                             Sending passwords in plain text without SSL/TLS is extremely insecure. || Note | 1760 | Storing MySQL user name or password information in the master Info repository are not secure and are therefore not recommend Ed. Consider using the USER and PASSWORD connection options for START SLAVE; See the ' START SLAVE Syntax ' on the MySQL Manual for more information. |+-------+------+---------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------+2 rows in Set (0.00 sec)

Experiment one: If the gtid required by slave has been purge on master

According to show global variables like ‘%gtid%‘ the command result we can see that there is one in the variable associated with Gtid gtid_purged . The literal meaning as well as the official document can be known that the variable is recorded in this machine has been executed, but has been purge binary logs to ordered to clean gtid_set . In this section we will experiment with what happens if Master Gtid event purge that some slave have not been fetch.

The following directives are executed on master

master [localhost] {msandbox} (Test) > show global variables like '%gtid% '; +---------------------------------+------ ----------------------------------+| variable_name | Value |+---------------------------------+----------------------------------------+| Binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | On | | gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1 | | Gtid_mode | On | |                                        gtid_owned | ||                                        gtid_purged | || Simplified_binlog_gtid_recovery |  OFF |+---------------------------------+----------------------------------------+7 rows In Set (0.01 sec) master [localhost] {msandbox} (Test) > Flush logs;create table gtid_test2 (ID int) Engine=innodb; Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.02 sec) Master [localhost] {msandbox} (Test) > Flush lo Gs;create table Gtid_test3 (ID int) Engine=innodb; Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.04 sec) master [localhost] {msandbox} (Test) > Show Mas ter status;+------------------+----------+--------------+------------------+----------------------------------- -------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+--------------- ---------------------------+|      mysql-bin.000005 |              359 |                  | | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 |+------------------+----------+--------------+------------------+---- --------------------------------------+1 Row in Set (0.00 sec) Master [localhost] {msandbox} (Test) > Purge binary logs To ' mysql-bin.000004 '; Query OK, 0Rows affected (0.03 sec) master [localhost] {msandbox} (Test) > show global variables like '%gtid% '; +------------------- --------------+------------------------------------------+| variable_name | Value |+---------------------------------+------------------------------------------+| Binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | On | | gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 | | Gtid_mode | On | |                                          gtid_owned | || gtid_purged | 24024e52-bd95-11e4-9c6d-926853670d0b:1 | | Simplified_binlog_gtid_recovery | OFF |+---------------------------------+------------------------------------------+7 Rows in Set (0.00 sec)

Slave2 on the master and slave, the following command executes on Slave2

slave2 [localhost] {msandbox} ((none)) > Change master to master_host= ' 127.0.0.1 ', Master_port =21288,master_user= ' Rsandbox ', master_password= ' Rsandbox ', master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.04 sec) slave2 [localhost] {msandbox} ((none)) > Start slave; Query OK, 0 rows affected (0.01 sec) slave2 [localhost] {msandbox} ((none)) > show slave status\g*********************** 1.             Row *************************** ...                   Slave_io_running:no Slave_sql_running:yes ... last_errno:0 last_error:skip_counter:0 exec_master_log_pos:0 R                elay_log_space:151 ...  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. '                last_sql_errno:0 last_sql_error: ... Auto_position:11 row in Set (0.00 sec)

Experiment two: Ignoring the part of purged, forcing synchronization

In real-world production applications, the occasional scenario is when a slave is restored from a backup (or load data infile), and the DBA can artificially ensure that the slave data is consistent with master, or even if it is inconsistent, These differences also do not result in future master-slave exceptions (for example, only insert does not update on all master). On this premise, we want to make slave data copy from master through replication. At this point we need to skip the part where Master has been purge, so how do we actually do it? Let's take the case of experiment one, for example:

First confirm that the part that is already purge on master. From the following command result, you can know that the 24024e52-bd95-11e4-9c6d-926853670d0b:1 log of this transaction is missing on master

master [localhost] {msandbox} (Test) > show global variables like '%gtid% '; +---------------------------------+------ ------------------------------------+| variable_name | Value |+---------------------------------+------------------------------------------+| Binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | On | | gtid_executed | 24024e52-bd95-11e4-9c6d-926853670d0b:1-3 | | Gtid_mode | On | |                                          gtid_owned | || gtid_purged | 24024e52-bd95-11e4-9c6d-926853670d0b:1 | | Simplified_binlog_gtid_recovery | OFF |+---------------------------------+------------------------------------------+7 Rows in Set (0.00 sec)

Skips the >set section by on slave

slave2 [localhost] {msandbox} ((none)) > Stop slave; Query OK, 0 rows affected (0.04 sec) slave2 [localhost] {msandbox} ((none)) > Set global gtid_purged = ' 24024e52-bd95-11 E4-9c6d-926853670d0b:1 '; Query OK, 0 rows affected (0.05 sec) slave2 [localhost] {msandbox} ((none)) > Start slave; Query OK, 0 rows affected (0.01 sec) slave2 [localhost] {msandbox} ((none)) > show slave status\g ******* 1. Row *************************** slave_io_state:waiting for master to send event:              .... master_log_file:mysql-bin.000005 read_master_log_pos:359 Relay_log_file:mysql_sandbox21290-relay  -bin.000004 relay_log_pos:569 relay_master_log_file:mysql-bin.000005 slave_io_running:          Yes Slave_sql_running:yes ...       exec_master_log_pos:359 relay_log_space:873 ...      Master_server_id:1 master_uuid:24024e52-bd95-11e4-9c6d-926853670d0b Master_info_file: /data/mysql/rsandbox_mysql-5_6_23/node2/data/master.info sql_delay:0 Sql_remaining_delay:nu LL Slave_sql_running_state:slave have read all relay log;           Waiting for the slave I/O thread to update it ... Retrieved_gtid_set:24024e52-bd95-11e4-9c6d-926853670d0b:2-3 executed_gtid_set:24024e52-bd95-11e4-9c6d-9268536 70d0b:1-3 auto_position:11 Row in Set (0.00 sec)

You can see that at this point the slave is already in sync and the 24024e52-bd95-11e4-9c6d-926853670d0b:2-3 range of Binlog logs is padded.

Written byCenalulu (Lu Junyi)Share on Twitter Share on Facebook Share on Google + http://cenalulu.github.io/mysql/mysql-5-6-gtid-basic/

Practice of new characteristics of MySQL5.6 Gtid