New Features of MySQL 5.6 GTID and new features of gtid

New Features of MySQL 5.6 GTID and new features of gtid

GTID Overview

What is GTID?

GTID (Global Transaction ID) is the ID of a committed Transaction and a globally unique ID.

GTID is actually composed of UUID + TID. UUID is the unique identifier of a MySQL instance. TID indicates the number of transactions committed on the instance, and increases monotonically as the transaction is committed. Below is a specific form of GTID

3e11fa47-71ca-11e1-9e33-c80aa9429133: 23

For more details, see the official documentation.

Role of GTID

So what is the purpose of the GTID function? There are two main points to be summarized:

According to GTID, you can know the existence of GTID committed on which instance the transaction was originally initiated, facilitating the Replication Failover. Here we will explain the second point in detail. We can see the previous replication failover operation process in GTID of MySQL 5.6. Suppose we have an environment like

In this case, the Server of Server A is down and the service 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 copying source modifications is simple: change master to MASTER_HOST = 'xxx', MASTER_LOG_FILE = 'xxx', MASTER_LOG_POS = nnnn. The difficulty lies in how to find the current synchronization stop point of Server C because the binlog names and locations of the same transaction on each machine are different, the master_log_file and master_log_pos corresponding to Server B become difficult. That's one of the important reasons why M-S replication clusters need additional administrative tools such as MMM And MHA.

This problem is very simple after 5.6 of GTID occurs. Since GTID of the same transaction has the same value on all nodes, the GTID at the current stop point of Server C can be uniquely located on Server B. Even because of the MASTER_AUTO_POSITION function, we do not need TO know the specific value of GTID. directly use the change master to MASTER_HOST = 'xxx' command TO directly complete failover. So easy, isn't it?

Introduction to master-slave Replication Based on GTID

Build

The mysql_sandbox script is used as the basis to create a location-based replication environment with one master node and three slaves. Then, through configuration modification, the entire architecture is designed for GTID-based replication.

Follow the GTID build suggestions provided in the MySQL official documentation. You need to modify the configuration of the master and slave nodes at a time and restart the service. This operation is obviously unacceptable when upgrading the production environment. Facebook, Booking.com, and Percona have all optimized the patch and made the upgrade more elegant. The specific operation method will be introduced in the following blog posts. Here we will perform an experimental upgrade according to the official documentation.

The main upgrade steps are as follows:

Ensure that the master-slave synchronization is configured with read_only on the master node, and no new data is written to the master node. cnf, and restart the service to modify my. cnf, and restart the service to execute change master to on slave and enable GTID-based replication with master_auto_position = 1. Because it is an experimental environment, read_only and service restart are not a major problem. You only need to follow the official GTID building suggestions to complete the upgrade smoothly. Here we will not repeat the detailed process. The following lists some errors that may be encountered during the upgrade process.

Common Errors

Gtid_mode = ON, log_slave_updates, and enforce_gtid_consistency must be configured in my. cnf at the same time. Otherwise, the following error occurs 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
20:13:53 32570 [ERROR] -- gtid-mode = ON or UPGRADE_STEP_1 requires -- enforce-gtid-consistency

Warnings after changing master

After changing the master to according to the operation in the document, two warnings are found. In fact, there are two security warnings that do not affect normal synchronization. (If you are interested, you can refer to the specific introduction to this warning. The specific content of 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 slave has been purged on the master

According to the command result of show global variables like '% gtid %', we can see that there is a gtid_purged variable related to GTID. From the literal meaning and official documentation, we can know that the variable records the gtid_set that has been executed on the local machine but has been cleared by the purge binary logs to command.
In this section, we will try to see what results will happen if some slave gtid event purge has not been fetch to on the master.

Run the following commands on the master node:

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)

Perform a new Master/Slave operation on slave2. Run the following command 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: NoSlave_SQL_Running: Yes......Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 0Relay_Log_Space: 151......Last_IO_Errno: 1236Last_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: 0Last_SQL_Error:......Auto_Position: 11 row in set (0.00 sec)

Experiment 2: Ignore the purged part and force Synchronization

In actual production applications, the DBA may occasionally encounter this situation: After a slave is recovered from the backup (or load data infile), the DBA can manually ensure that the slave data is consistent with the master; or, even if they are inconsistent, these differences will not cause future master-slave exceptions (for example, only insert and no update on all master nodes ). In this case, we want to make slave replicate data from the master through replication. In this case, we need to skip the portion of the master that has been purged. What should we do?

Let's take Experiment 1 as an example:

First confirm the purge part on the master. The following command result shows that the log of the transaction 24024e52-bd95-11e4-9c6d-926853670d0b: 1 is missing from 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-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)

Set global gtid_purged = 'xxxx' on the slave to skip the purge part.

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.000005Read_Master_Log_Pos: 359Relay_Log_File: mysql_sandbox21290-relay-bin.000004Relay_Log_Pos: 569Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YesSlave_SQL_Running: Yes......Exec_Master_Log_Pos: 359Relay_Log_Space: 873......Master_Server_Id: 1Master_UUID: 24024e52-bd95-11e4-9c6d-926853670d0bMaster_Info_File: /data/mysql/rsandbox_mysql-5_6_23/node2/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_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-3Executed_Gtid_Set: 24024e52-bd95-11e4-9c6d-926853670d0b:1-3Auto_Position: 11 row in set (0.00 sec)

We can see that the slave can be synchronized normally, and the binlog logs in the range of 24024e52-bd95-11e4-9c6d-926853670d0b: 2-3 have been completed.

The above is a new MySQL 5.6 GTID practice introduced by xiaobian. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!