MySQL5.6 新特性之GTID

背景：概念：4e659069-3cd8-11e5-9a49-001c4270714e:1-77
更具体的说明见官方说明。GTID意义：上面图的意思是：Server1(Master)崩溃，根据从上show slave status获得Master_log_File/Read_Master_Log_Pos的值，Server2(Slave)已经跟上了主，Server3(Slave)没有跟上主。这时要是把Server2提升为主，Server3变成Server2的从。这时在Server3上执行change的时候需要做一些计算，这里就不做说明了，具体的说明见高性能MySQL第10章，相对来说是比较麻烦的。这个问题在5.6的GTID出现后，就显得非常的简单。由于同一事务的GTID在所有节点上的值一致，那么根据Server3当前停止点的GTID就能定位到Server2上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现，我们都不需要知道GTID的具体值，直接使用CHANGE MASTER TO MASTER_HOST=’xxx’， MASTER_AUTO_POSITION命令就可以直接完成failover的工作。原理：测试：1）复制环境的搭建：具体的复制搭建的步骤可以在网上搜索因为支持GTID，所以5.6多了几个参数：复制代码
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 |
+———————————+———–+
复制代码
主从环境的搭建和5.5没有什么区别，唯一需要注意的是：开启GTID需要启用这三个参数：#GTID
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
任意一个参数不开启则都会报错：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-updates
2015-08-09 02:33:57 6512 [ERROR] Aborting2015-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
具体的方法可以参考官方文档。三个实例开启之后(3306、3307、3308)，执行change的时候也要注意：各个实例的uuid：复制代码
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 |
+————————————–+
复制代码
使用5.6之前的主从change：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 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
当使用 MASTER_AUTO_POSITION 参数的时候，MASTER_LOG_FILE，MASTER_LOG_POS参数不能使用。使用5.6之后的主从change：mysql> change master to master_host=’127.0.0.1′,master_user=’rep’,master_password=’rep’,master_port=3306,master_auto_position=1;
在执行上面的命令的时候会报错2个warnings，主要的原因是复制账号安全的问题，相关的信息可以看这里。从总体上看来，由于要支持GTID，所以不需要手工确定主服务器的MASTER_LOG_FILE及MASTER_LOG_POS。要是不需要GTID则需要指定FILE和POS。在2个从上执行上面命令，到此主从环境搭建完成。GTID的主从完成之后可以通过show processlist查看：复制代码
mysql> show processlistG;
1. row
Id: 38
User: rep
Host: localhost:52321
db: NULL
Command: Binlog Dump GTID #通过GTID复制
Time: 48
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Rows_sent: 0
Rows_examined: 0
复制代码
2）测试复制的故障转移server1(3306)挂了，服务器起不来了。需要把其中的一个从设置为主，另一个设置为其的从库：server2(3307):server3(3308):相比之下server2完成的事务要比server3更接近或则等于server1，现在需要把server3设置为server2的从库。在MySQL5.6之前，这里的计算会很麻烦，要计算之前主库的log_pos和当前要设置成主库的log_pos，很有可能出错。所以出现了一些高可用性的工具如MHA，MMM等解决问题。在MySQL5.6之后，很简单的解决了这个难题。因为同一事务的GTID在所有节点上的值一致，那么根据server3当前停止点的GTID就能定位到server2上的GTID，所以直接在server3上执行change即可：复制代码
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)#千万不要执行 reset master，否则会从最先的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)
复制代码
主从结构已经变更，server2是Master，server3是Slave。因为不需要计算pos的值，所以通过GTID很简单的解决了这个问题。3）跳过复制错误：gtid_next、gtid_purged① 从服务器跳过一个错误的事务:复制代码
mysql> show slave statusG;
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的时候指定，如：change master to master_delay=600，延迟10分钟同步。
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
复制代码
在MySQL5.6之前，只需要执行：mysql> set global sql_slave_skip_counter=1;
跳过一个错误的事务，就可以继续进行复制了。但在MySQL5.6之后则不行：mysql> set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
分析：因为是通过GTID来进行复制的，也需要跳过这个事务从而继续复制，这个事务可以到主上的binlog里面查看：因为不知道找哪个GTID上出错，所以也不知道如何跳过哪个GTID。但在show slave status里的信息里可以找到在执行Master里的POS:151Exec_Master_Log_Pos: 151
的时候报错，所以通过mysqlbinlog找到了GTID：#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’/!/;
找到这个GTID之后执行：必须按照下面顺序执行复制代码
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，即跳过这个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)
复制代码
查看复制状态：复制代码
mysql> show slave statusG;
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 has 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
复制代码
在此成功跳过了错误，同步继续。可以通过这个办法来处理复制失败的问题，这里还有个例子，有兴趣的可以看一下（从服务器中跳过一条语句/事务）：View Code
注意：通过GTID的复制都是没有指定MASTER_LOG_FILE和MASTER_LOG_POS的，所以通过GTID复制都是从最先开始的事务开始，除非在自己的binlog里面有执行过之前的记录，才会继续后面的执行。② 要是事务日志被purge，再进行change：View Code
报错：这里需要解决的是：Slave如何跳过purge的部分，而不是在最先开始的事务执行。复制代码
在主上执行，查看被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)在从上执行，跳过这个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)要是出现：
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
则需要执行：
reset master;
复制代码
到这从的同步就正常了。 View 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; #误操作，把记录删除了
Query OK, 0 rows affected (0.02 sec)mysql> show slave statusG;
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 has 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(本身实例执行的事务)
4e659069-3cd8-11e5-9a49-001c4270714e:1-46
Auto_Position: 1数据被误删除之后，最好停止复制：stop slave;恢复数据从slave1（3307）上备份数据，并还原到slave2（3308）中。
备份：
mysqldump -uzjy -p123456 -h227.0.0.1 -P3307 –default-character-set=utf8 –set-gtid-purged=ON -B mmm > mmm1.sql在还原到slave2的时候需要在slave2上执行：reset master; 不然会报错：
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.还原：
root@zjy:~# mysql -uzjy -p123456 -h227.0.0.1 -P3308 –default-character-set=utf8
开启同步：
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)这时候你会发现误删除的数据已经被还原，并且复制也正常。因为根据GTID的原理，通过slave1的备份直接可以和Master进行同步。
复制代码
这里备份注意的一点是：在备份开启GTID的实例里，需要指定 –set-gtid-purged参数，否则会报warning：Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that 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
备份文件里面会出现：SET @@GLOBAL.GTID_PURGED=’4e659069-3cd8-11e5-9a49-001c4270714e:1-483′;
还原的时候会要求先在实例上reset master，不然会报错：Warning: Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
指定–set-gtid-purged=ON参数，出现GTID_PURGED，直接还原的时候执行，从库不需要其他操作就可以直接change到主。关于GTID更多的信息可以到官方文档开发云主机域名里查看。 总结：

相关推荐: 操作MySQL的基本命令有哪些

本篇文章给大家主要讲的是关于操作MySQL的基本命令有哪些的内容，感兴趣的话就一起来看看这篇文章吧，相信看完操作MySQL的基本命令有哪些对大家多少有点参考价值吧。操作系统：CentOS数据库系统：MySQL[root@sky9896~]# mysql -h …