MySQL 5.6 GTID new feature practice_MySQL

GTID Introduction

What is GTID

GTID (Global Transaction ID) is the number of a submitted transaction and is a globally unique number.

GTID is actually composed of UUID+TID. The UUID is the unique identifier of a MySQL instance. TID represents the number of transactions that have been committed on this instance, and increases monotonically as transactions are committed. The following is the specific form of a GTID

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

For a more detailed introduction, please see: official documentation

The role of GTID

So what is the purpose of the GTID function? The specific summary mainly includes the following two points:

According to the GTID, you can know on which instance the transaction was initially submitted. The existence of the GTID facilitates Replication's Failover. The second point is explained in detail here. We can take a look at the operation process of replication failover before the GTID of MySQL 5.6 appeared. Suppose we have an environment as shown below

At this time, the server of Server A is down and the business needs to be switched to Server B. At the same time, we need to change the replication source of Server C to Server B. The command syntax for copy source modification is very simple, namely CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=nnnn. The difficulty is that since the same transaction has different binlog names and locations on each machine, it becomes a problem to find the current synchronization stop point of Server C and what the corresponding master_log_file and master_log_pos of Server B are. This is an important reason why M-S replication clusters need to use additional management tools such as MMM and MHA.

This problem seems to be very simple after the GTID of 5.6 appeared. Since the GTID of the same transaction has the same value on all nodes, the GTID on Server B can be uniquely located based on the GTID of the current stop point of Server C. Even due to the emergence of the MASTER_AUTO_POSITION function, we do not need to know the specific value of GTID. We can directly use the CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION command to directly complete the failover work. So easy, isn’t it?

Introduction to GTID-based master-slave replication

Building

The construction is based on the mysql_sandbox script, and a location-based replication environment with one master and three slaves is first created. Then through configuration modifications, the entire architecture is designed for GTID-based replication.

Based on the GTID construction suggestions given in the official MySQL documentation. It is necessary to modify the configuration of the master and slave nodes once and restart the service. Such an operation is obviously unacceptable when upgrading in a production environment. Facebook, Booking.com, and Percona have all optimized this through patches and achieved more elegant upgrades. The specific operation methods will be introduced in future blog posts. Here we will conduct an experimental upgrade according to the official documentation.

The main upgrade steps are as follows:

Ensure master-slave synchronization. Configure read_only on the master to ensure that no new data is written to modify my.cnf on the master. Restart the service to modify my.cnf on the slave. Restart the service and execute change master to on the slave with master_auto_position. =1 Enabling GTID-based replication is an experimental environment, so restarting read_only and services will not cause any problems. As long as you follow the official GTID construction recommendations, you can successfully complete the upgrade. The detailed process will not be described here. Listed below are some common errors that may be encountered during the upgrade process.

Common mistakes

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

2016-10-08 20:11:08 32147 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates
2016-10-08 20:13:53 32570 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 requires --enforce-gtid-consistency

Warnings after changing master to

After changing master to according to the documentation, you will find two warnings. In fact, they are two security warnings, which do not affect normal synchronization (interested readers can read the detailed introduction of this warning. The specific content of the warning is 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',master_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 is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Experiment 1: If the GTID corresponding to the transaction required by the slave has been purged on the master

According to the command results of show global variables like '%gtid%', we can see that there is a gtid_purged among the variables related to GTID. From the literal meaning and the official documentation, we can know that what is recorded in this variable is the gtid_set that has been executed on the local machine but has been cleaned up by the purge binary logs to command.
In this section, we will test what will happen if the master purges some gtid events that have not been fetched by the slave.

The following instructions are executed on the 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 logs;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 master 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, 0 rows 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上重新做一次主从，以下命令在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
Relay_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: 1
1 row in set (0.00 sec)

实验二：忽略purged的部分，强行同步

那么实际生产应用当中，偶尔会遇到这样的情况：某个slave从备份恢复后（或者load data infile）后，DBA可以人为保证该slave数据和master一致；或者即使不一致，这些差异也不会导致今后的主从异常（例如：所有master上只有insert没有update）。这样的前提下，我们又想使slave通过replication从master进行数据复制。此时我们就需要跳过master已经被purge的部分，那么实际该如何操作呢？

我们还是以实验一的情况为例：

先确认master上已经purge的部分。从下面的命令结果可以知道master上已经缺失24024e52-bd95-11e4-9c6d-926853670d0b:1这一条事务的相关日志

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)

在slave上通过set global gtid_purged='xxxx'的方式，跳过已经purge的部分

slave2 [localhost] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.04 sec)
slave2 [localhost] {msandbox} ((none)) > set global gtid_purged = '24024e52-bd95-11e4-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: NULL
Slave_SQL_Running_State: Slave has 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-926853670d0b:1-3
Auto_Position: 1
1 row in set (0.00 sec)

可以看到此时slave已经可以正常同步，并补齐了24024e52-bd95-11e4-9c6d-926853670d0b:2-3范围的binlog日志。

以上所述是小编给大家介绍的MySQL 5.6 GTID新特性实践，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！