The new characteristic practice of MySQL 5.6 Gtid _mysql

Gtid Introduction

What is Gtid

The Gtid (Global Transaction ID) is the number for a committed transaction and is a globally unique number.

Gtid is actually made up of Uuid+tid. Where the UUID is the unique identifier of a MySQL instance. 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 gtid

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

A more detailed description can be found in the official document

The role of Gtid

So what is the purpose of the Gtid function? The specific induction mainly has the following two points:

According to Gtid, the existence of the Gtid that the transaction was originally submitted on is convenient for replication failover here to explain the 2nd in detail. We can look at the process of replication failover before the gtid of MySQL 5.6 appears. Let's say we have an environment with the following diagram

At this point, server A's servers are down and you need to switch the business to Server B. At the same time, we need to change the replication source for 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 is that because the Binlog name and location of the same transaction on each machine are different, then how to find the current sync stop in Server C, corresponding to Master_log_file and master_log_ of server B It becomes a problem when POS is what it is. This is also one of the important reasons why M-S replication clusters require the use of additional management tools such as MMM,MHA.

This problem appears very simple after the gtid of 5.6. Because the gtid of the same transaction is consistent on all nodes, the Gtid of the current stop point on Server C can uniquely navigate to Gtid on Server B. Even because of the appearance of master_auto_position function, we do not need to know the specific value of Gtid, directly using the change MASTER to master_host= ' xxx ', master_auto_ Position command to complete the failover work directly. So easy, right?

Introduction to Master-slave replication based on Gtid

Build

Built using the Mysql_sandbox script as the basis, first created a primary three from the location based replication environment. The entire schema is then designed to be gtid based on configuration modifications.

According to the MySQL official documentation given by the Gtid build recommendations. You need to make configuration changes to the master-slave node at once, and restart the service. Such an operation is obviously unacceptable when the production environment is upgraded. Facebook,booking.com,percona has been optimized for this through patch, and has done a more elegant upgrade. The specific mode of operation will be introduced in the future blog. Here we have an experimental upgrade according to the official documentation.

The main upgrade steps will have the following steps:

Ensure master-Slave synchronization configures read_only on Master, ensures no new data is written to modify MY.CNF on master, restarts service modification slave my.cnf, restarts service to execute change master on slave and takes master _auto_position=1 Enable Gtid replication because it is an experimental environment, READ_ONLY and service restart is not a major obstacle. As long as the official Gtid to build the recommendations can be completed successfully upgrade, here is not to repeat the detailed process. The following 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 in MY.CNF at the same time. Otherwise, the following error will appear in the Mysql.err

2016-10-08 20:11:08 32147 [ERROR]--gtid-mode=on or upgrade_step_1 or upgrade_step_2 requires and--log-bin Dates
2016-10-08 20:13:53 32570 [ERROR]--gtid-mode=on or upgrade_step_1 requires--enforce-gtid-consistency

Warnings after change master to

You will find two warnings after you change master to by the document's actions. In fact, there are two security warnings that do not affect 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 ',
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 are not secure and isn't therefore not recommend Ed. Please consider using the "USER" and PASSWORD connection options for START SLAVE; The ' START SLAVE Syntax ' in the MySQL Manual for more information.
| +-------+------+----------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------+ 2 rows in Set (0.00 sec)

Experiment one: If the slave requires a transaction corresponding to the Gtid has been purge in master

According to the command result of show global variables like '%gtid% ' we can see that there is a gtid_purged in the variables associated with Gtid. The literal meaning and official documentation can be known that the variable is recorded on the local computer that has been executed, but has been purge binary logs to command cleanup gtid_set.
In this section, we're going to experiment with what happens when Master Gtid event purge that some slave have not yet been fetch.

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 M Aster 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)

Redo the master on the Slave2, the following command executes on Slave2

 Slave2 [localhost] {msandbox} (None) > Change master to master_host= ' 127.0.0.1 ', mast
Er_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 Conne Cting using change MASTER to Master_auto_position = 1 but the master has purged binary logs containing gtids the SLA
ve requires. '
last_sql_errno:0 last_sql_error: ... Auto_position:1 1 row in Set (0.00 sec) 

Experiment two: Ignore the purged part, Force synchronization

In the case of actual production applications, there are occasional cases where the DBA can make sure that the slave data is consistent with Master after a slave is restored (or load data infile), or even if it is inconsistent, These differences also do not lead to future master and subordinate exceptions (e.g., all master inserts without update). In this context, we would like to enable slave to replicate data from master through replication. At this point we need to skip the part where Master has been purge, so what is the actual operation?

Let's take the case of experiment one:

First confirm the part that is already purge on master. From the following command result, you will know that the related log for 24024e52-bd95-11e4-9c6d-926853670d0b:1 is missing from 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)

Skip the already purge part by means of set global gtid_purged= ' xxxx ' 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-1
1e4-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)

You can see that slave has been able to sync properly, and has padded the 24024e52-bd95-11e4-9c6d-926853670d0b:2-3 range Binlog log.

The above is a small series to introduce the MySQL 5.6 gtid new characteristics of practice, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!