The Gtid of new characteristics of MySQL5.6

Background:

  MySQL5.6在5.5的基础上增加了一些改进，本文章先对其中一个一个比较大的改进"GTID"进行说明。

Concept:

  GTID即全局事务ID（global transaction identifier），GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。下面是一个GTID的具体形式：

4e659069-3cd8-11e5-9a49-001c4270714e:1-77
See the official note for more specific instructions.

Gtid Meaning:

  引入GTID的意义是什么？  1)因为清楚了GTID的格式，所以通过UUID可以知道这个事务在哪个实例上提交的。  2)通过GTID可以极方便的进行复制结构上的故障转移，新主设置。很好的解决了下面这个图（图来自高性能MySQL第10章）的问题。

The above figure means: Server1 (master) crashes, Server2 (slave) has followed the master, based on the value obtained master_log_file/read_master_log_pos from the show slave status Server3 (Slave) did not follow the Lord. At this time if the Server2 ascension to the main, Server3 become Server2 from. At this time in the Server3 to perform a change on the need to do some calculations, here do not explain, the specific description see high-performance MySQL 10th chapter, relatively difficult.

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, the Gtid on the Server3 's current stop can be positioned to Gtid on Server2. Even due to the advent of master_auto_position function, we do not need to know the specific value of Gtid, direct use of change MASTER to master_host= ' xxx ', master_auto_ Position command can directly complete the work of failover.

Principle:

  从服务器连接到主服务器之后，把自己执行过的GTID(Executed_Gtid_Set)<SQL线程> 、获取到的GTID(Retrieved_Gtid_Set）<IO线程>发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主， 先change到同步最成功的那台从服务器， 等把GTID全部补全了，就可以把它提升为主了。

Test:

1) The construction of the replication environment: Specific replication steps can be found on the web

Because Gtid is supported, more than 5.6 parameters are available:

Copy Code
Mysql> Show variables like '%gtid% ';
+---------------------------------+-----------+
| variable_name | Value |
+---------------------------------+-----------+
| Binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | OFF |
| Gtid_deployment_step | OFF |
| gtid_executed | |
| Gtid_mode | OFF |
| Gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| Simplified_binlog_gtid_recovery | OFF |
+---------------------------------+-----------+
Copy Code
The construction of the master-slave environment and 5.5 no difference, the only thing to note is: Open Gtid need to enable these three parameters:

#GTID
Gtid_mode = On
Enforce_gtid_consistency = 1
Log_slave_updates = 1
Any parameter that does not open will be an error:

2015-08-09 02:33:57 6512 [ERROR]--gtid-mode=on or upgrade_step_1 or upgrade_step_2 requires--log-bin and--LOG-SLAVE-UPD Ates
2015-08-09 02:33:57 6512 [ERROR] Aborting

2015-08-09 02:39:58 9860 [ERROR]--gtid-mode=on or upgrade_step_1 requires--enforce-gtid-consistency
2015-08-09 02:39:58 9860 [ERROR] Aborting
Specific methods can refer to the official documentation.

After three instances are turned on (3306, 3307, 3308), you should also note that when you perform a change:

UUID for each instance:

Copy Code
3306:
Mysql> SELECT @ @server_uuid;
+--------------------------------------+
| @ @server_uuid |
+--------------------------------------+
| 4e659069-3cd8-11e5-9a49-001c4270714e |
+--------------------------------------+

3307:
Mysql> SELECT @ @server_uuid;
+--------------------------------------+
| @ @server_uuid |
+--------------------------------------+
| 041d0e65-3cde-11e5-9a6e-001c4270714e |
+--------------------------------------+

3308:
Mysql> SELECT @ @server_uuid;
+--------------------------------------+
| @ @server_uuid |
+--------------------------------------+
| 081ccacf-3ce4-11e5-9a95-001c4270714e |
+--------------------------------------+
Copy Code
Use the master-slave change before 5.6:

mysql> Change Master to master_host= ' 127.0.0.1 ', master_user= ' rep ', Master_password= ' rep ', Master_log_file= ' Mysql-bin3306.000001 ', master_log_pos=151,/master_auto_position=1/;
Error:

ERROR 1776 (HY000): Parameters master_log_file, Master_log_pos, Relay_log_file and Relay_log_pos cannot is set when MASTER _auto_position is active.
The Master_log_file,master_log_pos parameter cannot be used when the Master_auto_position parameter is used.

Use the master-slave change after 5.6:

mysql> Change Master to master_host= ' 127.0.0.1 ', master_user= ' rep ', Master_password= ' rep ', Master_port=3306,master _auto_position=1;
In the execution of the above command will be an error 2 warnings, the main reason is to copy the account security issues, the relevant information can be seen here.

In general, it is not necessary to manually determine the Master_log_file and Master_log_pos of the primary server because of the support for Gtid. If you do not need gtid, you need to specify file and Pos. Execute the above command on the top 2, and complete the master-slave environment. Gtid's master and slave finishes can be viewed via show processlist:

Copy Code
Mysql> show Processlist\g;
1. Row
id:38
User:rep
host:localhost:52321
Db:null
Command:binlog Dump GTID #通过GTID复制
time:48
State:master have sent all binlog to slave; Waiting for Binlog to be updated
Info:null
rows_sent:0
rows_examined:0
Copy Code
2) Testing replication failover

Server1 (3306) hang up, the server does not come up. One of these needs to be set from the primary and the other set to its slave library:

Server2 (3307):

          Master_Log_File: mysql-bin3306.000002      Read_Master_Log_Pos: 4156773      Exec_Master_Log_Pos: 4156773

Server3 (3308):

          Master_Log_File: mysql-bin3306.000001      Read_Master_Log_Pos: 83795320      Exec_Master_Log_Pos: 83795320

In contrast, Server2 completes a transaction that is closer to or equal to server1 than Server3, and now needs to set the Server3 to Server2 from the library.

Before MySQL5.6, it would be cumbersome to compute the log_pos of the main library and the Log_pos that are currently being set as the main library, most likely with errors. So there are some high availability tools such as mha,mmm to solve the problem.

After the MySQL5.6, it was easy to solve the problem. Because the gtid of the same transaction is consistent on all nodes, the Gtid on the Server3 current stop can be positioned to gtid on the Server2, so the change is performed directly on the Server3:

Copy Code
mysql> stop Slave;
Query OK, 0 rows affected (0.02 sec)

#千万不要执行 Reset Master, or it will start on the first Gtid.

mysql> Change Master to master_host= ' 127.0.0.1 ', master_user= ' rep ', Master_password= ' rep ', Master_port=3307,master _auto_position=1; #指定到另一个比较接近主的从上.
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave; #成功的切换到新主
Query OK, 0 rows affected (0.03 sec)
Copy Code
The master-slave structure has been changed, Server2 is Master,server3 is slave. Because you do not need to calculate the value of POS, it is easy to solve this problem by Gtid.

3) Skip replication error: Gtid_next, gtid_purged

① skipped a wrong transaction from the server:

Copy Code
Mysql> show Slave status\g;
1. Row
Slave_io_state:waiting for Master to send event
master_host:127.0.0.1
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysql-bin3306.000001
read_master_log_pos:38260944
relay_log_file:mysqld-relay-bin3307.000002
relay_log_pos:369
relay_master_log_file:mysql-bin3306.000001
Slave_io_running:yes
Slave_sql_running:no
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:1008
Last_error:error ' Can ' t drop database ' Mablevi '; Database doesn ' t exist ' on query. Default database: ' Mablevi '. Query: ' Drop database Mablevi '
skip_counter:0
exec_master_log_pos:151
relay_log_space:38261371
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
Seconds_behind_master:null
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:1008
Last_sql_error:error ' Can ' t drop database ' Mablevi '; Database doesn ' t exist ' on query. Default database: ' Mablevi '. Query: ' Drop database Mablevi '
Replicate_ignore_server_ids:
Master_server_id:1
master_uuid:4e659069-3cd8-11e5-9a49-001c4270714e
Master_Info_File:mysql.slave_master_info
Sql_delay:0 #通过在change的时候指定, such as: Change Master to master_delay=600, delayed 10 minutes synchronization.
Sql_remaining_delay:null
Slave_sql_running_state:
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
last_sql_error_timestamp:150810 23:38:39
MASTER_SSL_CRL:
Master_ssl_crlpath:
retrieved_gtid_set:4e659069-3cd8-11e5-9a49-001c4270714e:1-48
Executed_gtid_set:
Auto_position:1
Copy Code
Before MySQL5.6, you only need to perform:

mysql> set global sql_slave_skip_counter=1;
If you skip the wrong transaction, you can proceed with the replication. But not after the MySQL5.6:

mysql> set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): Sql_slave_skip_counter can Not was set when the server is running with @ @GLOBAL. Gtid_mode = on. Instead, for each transaction so want to skip, generate a empty transaction with the same GTID as the transaction
Analysis: Because it is through Gtid to replicate, also need to skip this transaction to continue copying, this transaction can go to the Lord's Binlog inside view: Because do not know which gtid to find error, so do not know how to skip which gtid. But in the show slave status information can be found in the implementation of Master pos:151

exec_master_log_pos:151
When the error, so through the Mysqlbinlog found Gtid:

At 151

#150810 22:57:45 Server ID 1 end_log_pos 199 CRC32 0x5e14d88f GTID [Commit=yes]
SET @ @SESSION. gtid_next= ' 4e659069-3cd8-11e5-9a49-001c4270714e:1 '/! /;
After finding this Gtid execution: must be executed in the following order

Copy Code
mysql> stop Slave;
Query OK, 0 rows affected (0.01 sec)

Mysql> set session gtid_next= ' 4e659069-3cd8-11e5-9a49-001c4270714e:1 '; #在session里设置gtid_next, skip this gtid.
Query OK, 0 rows affected (0.01 sec)

Mysql> begin; #开启一个事务
Query OK, 0 rows Affected (0.00 sec)

Mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> SET SESSION gtid_next = AUTOMATIC; #把gtid_next设置回来
Query OK, 0 rows Affected (0.00 sec)

mysql> start slave; #开启复制
Query OK, 0 rows affected (0.01 sec)
Copy Code
To view the replication status:

Copy Code
Mysql> show Slave status\g;
1. Row
Slave_io_state:waiting for Master to send event
master_host:127.0.0.1
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysql-bin3306.000001
read_master_log_pos:38260944
relay_log_file:mysqld-relay-bin3307.000003
relay_log_pos:716
relay_master_log_file:mysql-bin3306.000001
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:38260944
relay_log_space:38261936
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
master_uuid:4e659069-3cd8-11e5-9a49-001c4270714e
Master_Info_File:mysql.slave_master_info
sql_delay:0 #延迟同步
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
retrieved_gtid_set:4e659069-3cd8-11e5-9a49-001c4270714e:1-48
executed_gtid_set:4e659069-3cd8-11e5-9a49-001c4270714e:1-48
Auto_position:1
Copy Code
The error has been skipped successfully here and the synchronization continues. This can be used to deal with the problem of replication failure, here is an example, interested can see (skip a statement/transaction from the server):

View Code
Note: Replication through Gtid does not specify Master_log_file and Master_log_pos, so Gtid replication starts with the first transaction, unless the previous record has been executed in its own binlog to continue execution later.

② If the transaction log is purge, then change:

View Code
Error:

            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.‘

What needs to be addressed here is how slave skips the purge part rather than the first transaction execution.

Copy Code
In the Lord's execution, view the purge Gtid:
Mysql> show global variables like ' gtid_purged ';
+---------------+-------------------------------------------+
| variable_name | Value |
+---------------+-------------------------------------------+
| gtid_purged | 4e659069-3cd8-11e5-9a49-001c4270714e:1-50 |
+---------------+-------------------------------------------+
1 row in Set (0.00 sec)

At the top of the execution, skip this gtid:
mysql> stop Slave;
Query OK, 0 rows Affected (0.00 sec)

mysql> Set Global gtid_purged = ' 4e659069-3cd8-11e5-9a49-001c4270714e:1-50 ';
Query OK, 0 rows affected (0.02 sec)

mysql> Reset Master;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

If it appears:
ERROR 1840 (HY000): @ @GLOBAL. Gtid_purged can only is set when @ @GLOBAL. Gtid_executed is empty.
You need to do this:
Reset Master;
Copy Code
The synchronization from here is normal.

View Code
③ recovering from library data from library through another

For example, one from the library misoperation, data loss, can be recovered from the library through another:

Copy Code
Slave2 (3308):
mysql> Use MMM
Database changed
Mysql> Show tables;
+---------------+
| tables_in_mmm |
+---------------+
| patent_family |
| T |
| tt |
+---------------+
3 Rows in Set (0.00 sec)

Mysql> TRUNCATE TABLE TT; #误操作, delete the records.
Query OK, 0 rows affected (0.02 sec)

Mysql> show Slave status\g;
1. Row
Slave_io_state:waiting for Master to send event
master_host:127.0.0.1
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysql-bin3306.000001
read_master_log_pos:38260553
relay_log_file:mysqld-relay-bin3308.000002
relay_log_pos:38260771
relay_master_log_file:mysql-bin3306.000001
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:38260553
relay_log_space:38260980
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
master_uuid:4e659069-3cd8-11e5-9a49-001c4270714e
Master_info_file:/var/lib/mysql3/master.info
sql_delay:0 #延迟同步
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
retrieved_gtid_set:4e659069-3cd8-11e5-9a49-001c4270714e:1-46
Executed_gtid_set:081ccacf-3ce4-11e5-9a95-001c4270714e:1, #多出了一个GTID (transactions performed by the instance itself)
4e659069-3cd8-11e5-9a49-001c4270714e:1-46
Auto_position:1

After the data has been mistakenly deleted, it is best to stop copying: stop slave;

The recovery data backs up data from slave1 (3307) and reverts to Slave2 (3308).
Backup:
mysqldump-uzjy-p123456-h127.0.0.1-p3307--default-character-set=utf8--set-gtid-purged=on-b mmm > Mmm1.sql

When reverting to slave2, it needs to be performed on slave2: RESET master; Otherwise the error will be:
ERROR 1840 (HY000) at line: @ @GLOBAL. Gtid_purged can only is set when @ @GLOBAL. Gtid_executed is empty.

Restores:
[Email protected]:~# mysql-uzjy-p123456-h127.0.0.1-p3308--default-character-set=utf8 < Mmm.sql

Turn on Sync:
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

At this point you will find that the deleted data has been restored and replication is normal. Because according to the principle of Gtid, the slave1 backup can be directly synchronized with master.
Copy Code
One thing to note here is that the--set-gtid-purged parameter needs to be specified in the backup Gtid instance, otherwise it will be reported warning:

Warning:a partial dump from A server which has gtids would by default include the Gtids of all transactions, even those tha T changed suppressed parts of the database. If you don ' t want to restore gtids, pass--set-gtid-purged=off. To make a complete dump, pass--all-databases--triggers--routines--events
The backup file will appear inside:

SET @ @GLOBAL. Gtid_purged= ' 4e659069-3cd8-11e5-9a49-001c4270714e:1-483 ';
Restore will require reset master on the instance first, or it will error:

Warning:using a password on the command line interface can is insecure.
ERROR 1840 (HY000) at line: @ @GLOBAL. Gtid_purged can only is set when @ @GLOBAL. Gtid_executed is empty.
Specify the--set-gtid-purged=on parameter, the occurrence of gtid_purged, direct restore execution, from the library does not need other operations can be directly change to the main. More information about Gtid can be found in the official documentation.

Summarize:

  GTID就是全局事务ID(global transaction identifier )，最初由google实现，官方MySQL在5.6才加入该功能。要是主从结构只有一台Master和一台Slave对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。  使用GTID需要注意的是：在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。即：通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。

The Gtid of new characteristics of MySQL5.6