一、gtid特性介绍：GTID（全局事务标识符）是MySQL5.6的新特性，可以唯一标识一个事务，由UUID+TID组成：UUID是唯一一个用于MySQL实例的标识符TID是实例上提交的事务数。在主从复制中，GTID取代了经典的复制方式。不使用binlog+pos启动复制，而是使用master_auto_postion=1自动匹配GTID断点进行复制。启用GTID只需要在MySQL参数文件中添加如下参数：gtid-mode=ONenforce_gtid_consistency=12、数据恢复需求：需要将MySQL（以下简称A库）恢复到凌晨12:00左右的状态。一天前。前提条件如下：启用GTID，昨天凌晨12:00之前有A数据库的xtra备份文件，启用binlog日志（废话）。影响线上业务1、删除B库data目录，将A库备份文件复制到B库mvdatadata_20160716#删除B库数据mvA_back_20160714data#移入A库备份文件chown-Rmysql12300.mysqldata/2。打开B库，配置主从查看数据目录下xtrabackup_binlog_info文件中记录的GTID：[root@service-test1data]$catxtrabackup_binlog_infomysql-bin.000012464555548133046e-4282-11e6-848e-026ca51d284c:1-4920155设置@B库(slave)中的@global.gedtid_p跳过备份包含的GTID，执行changemaster指定数据库A为主库：mysql>SETGLOBALgtid_purged="8133046e-4282-11e6-848e-026ca51d284c:1-4920155";QueryOK,0rowsaffected(0.00sec)mysql>changemastertoMaster_Host='10.11.21.14',Master_Port=3306,Master_User='replica',Master_Password='XXXXXXXX',MASTER_AUTO_POSITION=1;QueryOK,0rowsaffected,2warnings(0.01sec)注意：有时xtrabackup_binlog_info中的GTID不止一个，当设置@@global.gtid_purged时可以指定多个，用逗号分隔。4.在A库binlog中找到恢复点，进行恢复。特别要注意的是，在上面的操作之后，不要直接启动slave，否则B库也会运行到A库的当前状态，将A库的binlog转换成sql语句：mysqlbinlog-vvmysql-bin.000011>mysql-bin.000011.sql找到前一天凌晨12:00左右的位置，记录下GTID：#at561467475#1605210:24:31serverid212177500end_log_pos561467523CRC320x216072caGTID[commit=yes].@GT_SSIONTEF62='154192a5-52-bc49-025d3d22c211:1348360'/*!*/;在B库打开slave，指定恢复位置：mysql>startslaveuntilSQL_BEFORE_GTIDS='542ef021-9a64-11e5-bc49-025d3d22c211:1348360';QueryOK,0rowsaffected(0.00sec)执行指定GTID时，SQL线程会停止，但是IO线程会继续复制：mysql>showslavestatus\G***************************1.row*****************************Slave_IO_State:WaitingformastertosendeventMaster_Host:10.30.21.11Master_User:replicaMaster_Port:7500Connect_Retry:60Master_Log_File:mysql-bin.000011Read_Master_Log_Pos:810203081Relay_Log_File:relay-bin.000002Relay_Log_Pos:5707357Relay_Master_Log_File:mysql-bin.000011Slave_IO_Running:YesSlave_SQL_Running:NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:561467475Relay_Log_Space:254443161Until_Condition:SQL_BEFORE_GTIDSUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:21117500Master_UUID:63f38fe7-9e3b-11e5-9554-02eeb2c1042fMaster_Info_File:/data1/mysql10070/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:542ef021-9a64-11e5-bc49-025d3d22c211:1341313-1368005Executed_Gtid_Set:44226252-9e38-11e5-9540-02212401d46f:1,542ef021-9a64-11e5-bc49-025d3d22c211:1-1348359,63f38fe7-9e3b-11e5-9554-02eeb2c1042f:1Auto_Position:11rowinset(0.00sec)好的，昨天凌晨你想看什么数据？都在B库了~~~附：设置@@global.gtid_purged时的常见问题，可能会遇到错误：mysql>SETGLOBALgtid_purged="8133046e-4282-11e6-848e-026ca51d284c:1-4920155";ERROR1840(HY000):@@GLOBAL.GTID_PURGEDcanonlybesetwhen@@GLOBAL.GTID_EXECUTEDisempty因为这个MySQL。@@GLOBAL.GTID_EXECUTED不为空，只需执行以下重置主操作：mysql>SETGLOBALgtid_purged="8133046e-4282-11e6-848e-026ca51d284c:1-4920155";ERROR1840(HY000):@@GLOBAL.GTID_PURGEDcanonlybeset@GLOBAL。GTID_EXECUTEDisempty.mysql>showmasterstatus;+----------------+--------+---------------+----------------+-----------------------------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+----------------+----------+------------+----------------+-------------------------------------------------+|mysql-bin.000002|191|||3857c25c-2caa-11e6-b61b-021feddc3827:1-14|+----------------+--------+------------+-------------------+------------------------------------------+1rowinset(0.00sec)mysql>resetmaster;QueryOK,0rowsaffected(0.01sec)mysql>showmasterstatus;+--------------------+--------+------------+----------------+----------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+----------------+----------+------------+----------------+------------------+|mysql-bin.000001|151||||+----------------+---------+------------+----------------+-----------------+1rowinset(0.00sec)mysql>SETGLOBALgtid_purged="8133046e-4282-11e6-848e-026ca51d284c:1-4920155";QueryOK,0rowsaffected(0.00sec)
