网站开发用c语言吗,深圳网页设计公司排名,wordpress cookie失效,建设网站一般要多久转自 MySQL5.6 新特性之GTID - jyzhou - 博客园http://www.cnblogs.com/zhoujinyi/p/4717951.html 背景#xff1a; MySQL5.6在5.5的基础上增加了一些改进#xff0c;本文章先对其中一个一个比较大的改进GTID进行说明。 概念#xff1a; GTID即全局事务ID#…转自 MySQL5.6 新特性之GTID - jyzhou - 博客园http://www.cnblogs.com/zhoujinyi/p/4717951.html 背景 MySQL5.6在5.5的基础上增加了一些改进本文章先对其中一个一个比较大的改进GTID进行说明。 概念 GTID即全局事务IDglobal transaction identifierGTID实际上是由UUIDTID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量并且随着事务提交单调递增所以GTID能够保证每个MySQL实例事务的执行不会重复执行同一个事务并且会补全没有执行的事务。下面是一个GTID的具体形式 4e659069-3cd8-11e5-9a49-001c4270714e:1-77 更具体的说明见官方说明。 GTID意义 引入GTID的意义是什么 1)因为清楚了GTID的格式所以通过UUID可以知道这个事务在哪个实例上提交的。 2)通过GTID可以极方便的进行复制结构上的故障转移新主设置。很好的解决了下面这个图图来自高性能MySQL第10章的问题。 上面图的意思是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_HOSTxxx MASTER_AUTO_POSITION命令就可以直接完成failover的工作。 原理 从服务器连接到主服务器之后把自己执行过的GTID(Executed_Gtid_Set)SQL线程 、获取到的GTID(Retrieved_Gtid_SetIO线程发给主服务器主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候找出同步最成功的那台从服务器直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主 先change到同步最成功的那台从服务器 等把GTID全部补全了就可以把它提升为主了。 测试 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-modeON 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-modeON 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_host127.0.0.1,master_userrep,master_passwordrep,master_log_filemysql-bin3306.000001,master_log_pos151,/*master_auto_position1*/; 报错 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_FILEMASTER_LOG_POS参数不能使用。 使用5.6之后的主从change mysql change master to master_host127.0.0.1,master_userrep,master_passwordrep,master_port3306,master_auto_position1; 在执行上面的命令的时候会报错2个warnings主要的原因是复制账号安全的问题相关的信息可以看这里。 从总体上看来由于要支持GTID所以不需要手工确定主服务器的MASTER_LOG_FILE及MASTER_LOG_POS。要是不需要GTID则需要指定FILE和POS。在2个从上执行上面命令到此主从环境搭建完成。GTID的主从完成之后可以通过show processlist查看 mysql show processlist\G;
*************************** 1. row ***************************Id: 38User: repHost: localhost:52321db: NULLCommand: Binlog Dump GTID #通过GTID复制Time: 48State: Master has sent all binlog to slave; waiting for binlog to be updatedInfo: NULLRows_sent: 0
Rows_examined: 0 2测试复制的故障转移 server1(3306)挂了服务器起不来了。需要把其中的一个从设置为主另一个设置为其的从库 server2(3307): Master_Log_File: mysql-bin3306.000002Read_Master_Log_Pos: 4156773Exec_Master_Log_Pos: 4156773 server3(3308): Master_Log_File: mysql-bin3306.000001Read_Master_Log_Pos: 83795320Exec_Master_Log_Pos: 83795320 相比之下server2完成的事务要比server3更接近或则等于server1现在需要把server3设置为server2的从库。 在MySQL5.6之前这里的计算会很麻烦要计算之前主库的log_pos和当前要设置成主库的log_pos很有可能出错。所以出现了一些高可用性的工具如MHAMMM等解决问题。 在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_host127.0.0.1,master_userrep,master_passwordrep,master_port3307,master_auto_position1; #指定到另一个比较接近主的从上。
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql start slave; #成功的切换到新主
Query OK, 0 rows affected (0.03 sec) 主从结构已经变更server2是Masterserver3是Slave。因为不需要计算pos的值所以通过GTID很简单的解决了这个问题。 3跳过复制错误gtid_next、gtid_purged ① 从服务器跳过一个错误的事务: mysql show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin3306.000001Read_Master_Log_Pos: 38260944Relay_Log_File: mysqld-relay-bin3307.000002Relay_Log_Pos: 369Relay_Master_Log_File: mysql-bin3306.000001Slave_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: 1008Last_Error: Error Cant drop database mablevi; database doesnt exist on query. Default database: mablevi. Query: drop database mableviSkip_Counter: 0Exec_Master_Log_Pos: 151Relay_Log_Space: 38261371Until_Condition: NoneUntil_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: NULL
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 1008Last_SQL_Error: Error Cant drop database mablevi; database doesnt exist on query. Default database: mablevi. Query: drop database mableviReplicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714eMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0 #通过在change的时候指定如change master to master_delay600延迟10分钟同步。SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Master_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 150810 23:38:39Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-48Executed_Gtid_Set: Auto_Position: 1 在MySQL5.6之前只需要执行 mysql set global sql_slave_skip_counter1; 跳过一个错误的事务就可以继续进行复制了。但在MySQL5.6之后则不行 mysql set global sql_slave_skip_counter1;
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:151 Exec_Master_Log_Pos: 151 的时候报错所以通过mysqlbinlog找到了GTID # at 151
#150810 22:57:45 server id 1 end_log_pos 199 CRC32 0x5e14d88f GTID [commityes]
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_next4e659069-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 status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin3306.000001Read_Master_Log_Pos: 38260944Relay_Log_File: mysqld-relay-bin3307.000003Relay_Log_Pos: 716Relay_Master_Log_File: mysql-bin3306.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_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: 38260944Relay_Log_Space: 38261936Until_Condition: NoneUntil_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: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714eMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0 #延迟同步SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-48Executed_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-48Auto_Position: 1 在此成功跳过了错误同步继续。可以通过这个办法来处理复制失败的问题这里还有个例子有兴趣的可以看一下从服务器中跳过一条语句/事务 mysql stop slave;
Query OK, 0 ROWS affected (0.05 sec)
mysql CHANGE master TO MASTER_DELAY600;
Query OK, 0 ROWS affected (0.27 sec)
mysql START slave;
Query OK, 0 ROWS affected, 1 warning (0.06 sec)master 原本是正常的 然后意外地执行了 truncate table:mysql INSERT INTO t SET titlec;
Query OK, 1 ROW affected (0.03 sec)
mysql INSERT INTO t SET titled;
Query OK, 1 ROW affected (0.05 sec)mysql SHOW master STATUS \G
*************************** 1. ROW ***************************File: black-bin.000001POSITION: 2817Binlog_Do_DB: Binlog_Ignore_DB:
Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-10
1 ROW IN SET (0.00 sec)mysql TRUNCATE TABLE t;
Query OK, 0 ROWS affected (0.15 sec)mysql SHOW master STATUS \G
*************************** 1. ROW ***************************File: black-bin.000001POSITION: 2948Binlog_Do_DB: Binlog_Ignore_DB:
Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-11
1 ROW IN SET (0.00 sec)slave有延迟 虽然已经获取到了gtid及对应的events, 但是并未执行mysql SHOW slave STATUS \G
*************************** 1. ROW ***************************Slave_IO_State: Waiting FOR master TO send event
.......
.......SQL_Delay: 600SQL_Remaining_Delay: 565Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed eventMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:9-11Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-8Auto_Position: 1
1 ROW IN SET (0.00 sec)要想办法在slave中跳过 GTID0c005b76-d3c7-11e2-a27d-274c063b18c4:11 也就是那条truncate table语句 。
办法就是设置GTID_NEXT然后提交一个空的事务。mysql stop slave;
Query OK, 0 ROWS affected (0.03 sec)
mysql SET session gtid_next0c005b76-d3c7-11e2-a27d-274c063b18c4:11;
Query OK, 0 ROWS affected (0.00 sec)
mysql BEGIN; commit;
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.01 sec)mysql SET SESSION GTID_NEXT AUTOMATIC;
Query OK, 0 ROWS affected (0.00 sec)
mysql START slave;
Query OK, 0 ROWS affected, 1 warning (0.07 sec)查看复制状态
mysql SHOW slave STATUS \G
*************************** 1. ROW ***************************Slave_IO_State: Waiting FOR master TO send event
.......
.......Retrieved_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:9-11Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-11Auto_Position: 1
1 ROW IN SET (0.00 sec)mysql SELECT * FROM t;
-----------
| id | title |
-----------
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
-----------
4 ROWS IN SET (0.00 sec)成功跳过 truncate table, 当然此时主从的数据已经不一致了。 注意通过GTID的复制都是没有指定MASTER_LOG_FILE和MASTER_LOG_POS的所以通过GTID复制都是从最先开始的事务开始除非在自己的binlog里面有执行过之前的记录才会继续后面的执行。 ② 要是事务日志被purge再进行change mysql show master logs;
---------------------------------
| Log_name | File_size |
---------------------------------
| mysql-bin3306.000001 | 38260944 |
---------------------------------
1 row in set (0.00 sec)mysql flush logs;
Query OK, 0 rows affected (0.03 sec)mysql show tables;
---------------
| Tables_in_mmm |
---------------
| patent_family |
| t1 |
| t2 |
---------------
3 rows in set (0.01 sec)mysql create table t3(id int)engine tokudb;
Query OK, 0 rows affected (0.02 sec)mysql insert into t3 values(3),(4);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql flush logs;
Query OK, 0 rows affected (0.02 sec)mysql create table ttt(id int)engine tokudb;
Query OK, 0 rows affected (0.02 sec)mysql insert into ttt values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql show master logs;
---------------------------------
| Log_name | File_size |
---------------------------------
| mysql-bin3306.000001 | 38260995 |
| mysql-bin3306.000002 | 656 |
| mysql-bin3306.000003 | 619 |
---------------------------------
3 rows in set (0.00 sec)mysql purge binary logs to mysql-bin3306.000003; #日志被purge
Query OK, 0 rows affected (0.02 sec)mysql show master logs; #日志被purge之后等下的binlog
---------------------------------
| Log_name | File_size |
---------------------------------
| mysql-bin3306.000003 | 619 |
------------------------------3308登陆之后执行mysql change master to master_host127.0.0.1,master_userrep,master_passwordrep,master_port3306,master_auto_position1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql start slave;
Query OK, 0 rows affected (0.02 sec)mysql show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Master_Host: 127.0.0.1Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: mysqld-relay-bin3308.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: NoSlave_SQL_Running: YesReplicate_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: 0Relay_Log_Space: 151Until_Condition: NoneUntil_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: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 1236Last_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: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714eMaster_Info_File: /var/lib/mysql3/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 150811 00:02:50Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 报错 Last_IO_Errno: 1236Last_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. 这里需要解决的是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; 到这从的同步就正常了。 mysql show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin3306.000003Read_Master_Log_Pos: 619Relay_Log_File: mysqld-relay-bin3308.000002Relay_Log_Pos: 797Relay_Master_Log_File: mysql-bin3306.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_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: 619Relay_Log_Space: 1006Until_Condition: NoneUntil_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: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714eMaster_Info_File: /var/lib/mysql3/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:51-52Executed_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-52Auto_Position: 1
1 row in set (0.00 sec)mysql use mmm
Database changed
mysql show tables;
---------------
| Tables_in_mmm |
---------------
| ttt |
---------------
1 row in set (0.00 sec) ③ 通过另一个从库恢复从库数据 比如一台从库误操作数据丢失了可以通过另一个从库来进行恢复 slave23308
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 status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 127.0.0.1Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin3306.000001Read_Master_Log_Pos: 38260553Relay_Log_File: mysqld-relay-bin3308.000002Relay_Log_Pos: 38260771Relay_Master_Log_File: mysql-bin3306.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_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: 38260553Relay_Log_Space: 38260980Until_Condition: NoneUntil_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: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714eMaster_Info_File: /var/lib/mysql3/master.infoSQL_Delay: 0 #延迟同步SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-46Executed_Gtid_Set: 081ccacf-3ce4-11e5-9a95-001c4270714e:1, #多出了一个GTID(本身实例执行的事务)
4e659069-3cd8-11e5-9a49-001c4270714e:1-46Auto_Position: 1数据被误删除之后最好停止复制stop slave;恢复数据从slave13307上备份数据并还原到slave23308中。
备份
mysqldump -uzjy -p123456 -h127.0.0.1 -P3307 --default-character-setutf8 --set-gtid-purgedON -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.还原
rootzjy:~# mysql -uzjy -p123456 -h127.0.0.1 -P3308 --default-character-setutf8 mmm.sql 开启同步
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 dont want to restore GTIDs, pass --set-gtid-purgedOFF. To make a complete dump, pass --all-databases --triggers --routines --events 备份文件里面会出现 SET GLOBAL.GTID_PURGED4e659069-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-purgedON参数出现GTID_PURGED直接还原的时候执行从库不需要其他操作就可以直接change到主。关于GTID更多的信息可以到官方文档里查看。 总结 GTID就是全局事务ID(global transaction identifier )最初由google实现官方MySQL在5.6才加入该功能。要是主从结构只有一台Master和一台Slave对于GTID来说就没有优势了而对于2台主以上的结构优势异常明显可以在数据不丢失的情况下切换新主。 使用GTID需要注意的是在构建主从复制之前在一台将成为主的实例上进行一些操作如数据清理等通过GTID复制这些在主从成立之前的操作也会被复制到从服务器上引起复制失败。即通过GTID复制都是从最先开始的事务日志开始即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作接着在server2上执行change的操作会使得server2也进行server1的清理操作。 参考文章 http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html http://www.cnblogs.com/cenalulu/p/4309009.html http://mysqllover.com/?p594 http://forum.z27315.com/topic/22781-mysql56%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/ www.zhaokunyao.com/archives/4131
