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 made up of UUID+TID. UUID is the unique identifier of a MySQL instance. TID represents the number of transactions that have been committed on this instance and increases monotonously with transaction submission. Here is a specific form of GTID

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

For a more detailed description, see: Official documents

Role of GTID

So what is the purpose of the GTID function? Specific induction mainly includes the following two points:

• According to GTID, you can know which instance the transaction was originally committed on.
• The existence of GTID facilitates Failover of Replication

The second point is explained in detail here. We can see the operation of replication failure over before GTID in MySQL 5.6. Suppose we have an environment like the following

At this point, Server A server downtime, need to switch business to Server B. At the same time, we need to make Server C Replication Source Changed to Server B. The command syntax for copying source modifications is very simple, that is CHANGE. MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=nnnn. The difficulty is how to find Server because the binlog name and location of the same transaction on each machine are different. C Current Synchronization Stop Point, corresponding to Server When B's master_log_file and master_log_pos are, it becomes a problem. This is an important reason why M-S replication clusters need to use additional management tools such as MMM and MHA.
This problem appears very simple after GTID of 5.6. Because the GTID of the same transaction has the same value on all nodes, then according to Server C The GTID of the current stop point can be uniquely located at Server GTID on B. Even because of the appearance of MASTER_AUTO_POSITION function, we do not need to know the specific value of GTID and use CHANGE directly. MASTER TO MASTER_HOST='xxx', the MASTER_AUTO_POSITION command can directly complete the failover work. So easy, isn't it?

Introduction of master-slave replication based on GTID

build

Based on mysql_sandbox script, a location-based replication environment with one master and three slaves is created. Then through configuration modification, the whole architecture is dedicated to GTID-based replication.
According to MySQL official documents GTID Construction Suggestions . Configuration changes to master and slave nodes need to be made once, and services need to be restarted. Such an operation is obviously unacceptable when upgrading the production environment. Facebook, Booking.com and Percona have all optimized this through patch to achieve a more elegant upgrade. The specific operation mode will be introduced in the future blog. Here we will make an experimental upgrade according to the official documents.
The main upgrade steps are as follows:

• Ensure master-slave synchronization
• Configure read_only on master to ensure that no new data is written
• Modify my.cnf on master and restart the service
• Modify my.cnf on slave and restart the service
• Execute change on slave Masto and master_auto_position=1 enable GTID-based replication

Because of the experimental environment, read_only and service reboot are not a major obstacle. As long as it's official GTID Construction Suggestions You can complete the upgrade smoothly by doing so, so I won't go into details here. Below are some of the errors that can easily be encountered in the upgrade process.

Common mistakes

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

warnings after change master to

In accordance with the operation of the document change After master to, you will find two warnings. In fact, there are two security warnings that do not affect normal synchronization. (Interested readers can see about this warning.) Concrete introduction . The details of warning are as follows:

Experiment 1: If the GTID corresponding to the transaction slave needs has been purge d on master

According to show The command result of global variables like'% gtid%'shows that there is a gtid_purged variable associated with GTID. From the literal meaning as well as _____________ Official documents You can see that the record in this variable is that it has been executed locally, but it has been purge The gtid_set cleaned up by the binary logs to command.
In this section, we'll try to see what happens if the master purges gtid events that some slave s haven't fetch ed.

The following instructions 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 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)

Re-do master and slave on slave 2. The following command is executed on slave 2

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)

Experiment 2: Forcing synchronization by ignoring the purged part

In practical production applications, there are occasional situations where DBA can artificially ensure that the slave data is consistent with the master after a slave is restored from the backup (or load data infile); or even if the slave data is inconsistent, these differences will not lead to future master-slave anomalies (for example, only insert s on all masters do not update). On this premise, we want slave to replicate data from master through replication. At this point we need to skip the part where the master has been purge d, so how do we actually operate?
We also take the case of Experiment 1 as an example:

First confirm the purge section on the master. From the results of the following command, you can see that the master has lost 24024e52-bd95-11e4-9c6d-926853670d0b:1 log for this transaction.

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)

Pass set on slave The way global gtid_purged='xxxx'skips 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.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)

It can be seen that slave can be synchronized normally at this time, and the binlog logs in the range of 2424e52-bd95-11e4-9c6d-926853670d0b:2-3 have been completed.