快捷搜索:
来自 网络数据库 2019-07-07 02:32 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

与MySQL传统复制相比

##==========================================##

与MySQL传统复制相比,GTID有哪些独特的复制姿势?

MySQL 5.6版本引入GTID来解决主从切换时BINLOG位置点难定位的问题,MHA从0.56版本开始支持基于GTID的复制,在发生故障切换时判断群集是否能采用基于GTID的方式进行切换

本文为DBA 社群的投稿文章:


##==========================================##
基于GTID进行故障切换的条件:
1、所有节点开启GTID模式,设置gtid_mode=1
2、所有节点上Executed_Gtid_Set不为空
3、至少一个节点使用Auto_Position=1

与MySQL传统复制相比,GTID有哪些独特的复制姿势?

##==========================================##
基于GTID进行故障切换:
1、如果候选Master节点不拥有最新的Relay log,那么将候选Master连接到拥有最新Relay log的Salve上进行日志补偿
2、如果群集中使用Binlog Server,则尝试从Binlog Server上拉取缺失的Binlog并应用到候选Master上
3、候选Matser拥有最新数据,将其升级为新Master,将其他slave连接到新Master上进行数据同步,可以给masterha_master_switch传入–wait_until_gtid_in_sync=1参数使其不等其它Slave完成数据同步,以加快切换速度。

前言

GTID(Global Transaction ID)是MySQL5.6引入的功能,可以在集群全局范围标识事务,用于取代过去通过binlog文件偏移量定位复制位置的传统方式。借助GTID,在发生主备切换的情况下,MySQL的其它Slave可以自动在新主上找到正确的复制位置,这大大简化了复杂复制拓扑下集群的维护,也减少了人为设置复制位置发生误操作的风险。另外,基于GTID的复制可以忽略已经执行过的事务,减少了数据发生不一致的风险。

GTID虽好,要想运用自如还需充分了解其原理与特性,特别要注意与传统的基于binlog文件偏移量复制方式不一样的地方。本文概述了关于GTID的几个常见问题,希望能对理解和使用基于GTID的复制有所帮助。

##==========================================##
基于GTID模式进行故障切换时,无论原Master节点OS是否正常,都不会尝试从原Master节点读取BINLOG进行日志补偿。
基于GTID模式的MHA支持在复制拓扑中使用BINLOG Server来进行日志补偿,而非GTID模式的MHA会忽略BINLOG Server。
建议在基于GTID模式的群集中,不使用MHA进行"手动主从切换",该操作可能会导致原主库上部分BINLOG丢失。

GTID长什么样

根据官方文档定义,GTID由source_id加transaction_id构成。

GTID = source_id:transaction_id 

上面的source_id指示发起事务的MySQL实例,值为该实例的server_uuid。server_uuid由MySQL在第一次启动时自动生成并被持久化到auto.cnf文件里,transaction_id是MySQL实例上执行的事务序号,从1开始递增。 例如:

e6954592-8dba-11e6-af0e-fa163e1cf111:1 

一组连续的事务可以用'-'连接的事务序号范围表示。例如

e6954592-8dba-11e6-af0e-fa163e1cf111:1-5 

更一般的情况是GTID的集合。GTID集合可以包含来自多个source_id的事务,它们之间用逗号分隔;如果来自同一source_id的事务序号有多个范围区间,各组范围之间用冒号分隔,例如:

e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27 

即,GTID集合拥有如下的形式定义:

gtid_set:    uuid_set [, uuid_set] ...    | ''uuid_set:    uuid:interval[:interval]...uuid:    hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhhh:    [0-9|A-F]interval:    n[-n]    (n >= 1) 

##==========================================##
在非GTID模式下,会先进行Phase 3.1阶段,从拥有最新BINLOG的从库上获取差异日志,再进行Phase 3.2阶段,尝试从原Master服务器上获取最新BINLOG。
 新京葡娱乐场网址 1

如何查看GTID

可以通过MySQL的几个变量查看相关的GTID信息。

  • gtid_executed
    在当前实例上执行过的GTID集合; 实际上包含了所有记录到binlog中的事务。所以,设置set sql_log_bin=0后执行的事务不会生成binlog 事件,也不会被记录到gtid_executed中。执行RESET MASTER可以将该变量置空。

  • gtid_purged
    binlog不可能永远驻留在服务上,需要定期进行清理(通过expire_logs_days可以控制定期清理间隔),否则迟早它会把磁盘用尽。gtid_purged用于记录已经被清除了的binlog事务集合,它是gtid_executed的子集。只有gtid_executed为空时才能手动设置该变量,此时会同时更新gtid_executed为和gtid_purged相同的值。gtid_executed为空意味着要么之前没有启动过基于GTID的复制,要么执行过RESET MASTER。执行RESET MASTER时同样也会把gtid_purged置空,即始终保持gtid_purged是gtid_executed的子集。

  • gtid_next
    会话级变量,指示如何产生下一个GTID。可能的取值如下:

    • AUTOMATIC:
      自动生成下一个GTID,实现上是分配一个当前实例上尚未执行过的序号最小的GTID。
    • ANONYMOUS:
      设置后执行事务不会产生GTID。
    • 显式指定的GTID:
      可以指定任意形式合法的GTID值,但不能是当前gtid_executed中的已经包含的GTID,否则,下次执行事务时会报错。

这些变量可以通过show命令查看,比如

mysql> show global variables like 'gtid%'; ---------------------- ------------------------------------------ | Variable_name        | Value                                    | ---------------------- ------------------------------------------ | gtid_deployment_step | OFF                                      || gtid_executed        | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-6 || gtid_mode            | ON                                       || gtid_owned           |                                          || gtid_purged          |                                          | ---------------------- ------------------------------------------ 5 rows in set (0.02 sec)mysql> show  variables like 'gtid_next'; --------------- ----------- | Variable_name | Value     | --------------- ----------- | gtid_next     | AUTOMATIC | --------------- ----------- 1 row in set (0.00 sec) 

使用非GTID模式切换的日志

如何产生GTID

GTID的生成受gtid_next控制。 在Master上,gtid_next是默认的AUTOMATIC,即在每次事务提交时自动生成新的GTID。它从当前已执行的GTID集合(即gtid_executed)中,找一个大于0的未使用的最小值作为下个事务GTID。同时在binlog的实际的更新事务事件前面插入一条set gtid_next事件。

以下是一条insert语句生成的binlog记录

mysql> use `test`Database changedmysql> insert into tbx1 values(1);Query OK, 1 row affected (0.01 sec)mysql> show binlog events IN 'binlog.000015'; --------------- ----- ---------------- ----------- ------------- ------------------------------------------------------------------- | Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                              | --------------- ----- ---------------- ----------- ------------- ------------------------------------------------------------------- ...| binlog.000015 | 707 | Gtid           |         1 |         755 | SET @@SESSION.GTID_NEXT= 'e10c75be-5c1b-11e6-ab7c-000c296078ae:9' || binlog.000015 | 755 | Query          |         1 |         834 | BEGIN                                                             || binlog.000015 | 834 | Query          |         1 |         934 | use `test`; insert into tbx1 values(1)                            || binlog.000015 | 934 | Xid            |         1 |         965 | COMMIT /* xid=20 */                                               | 

在Slave上回放主库的binlog时,先执行set gtid_next ...,然后再执行真正的insert语句,确保在主和备上这条insert对应于相同的GTID。

一般情况下,GTID集合是连续的,但使用多线程复制(MTS)以及通过gtid_next进行人工干预时会导致gtid空洞。比如下面这样:

mysql> show master status; --------------- ---------- -------------- ------------------ ------------------------------------------ | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        | --------------- ---------- -------------- ------------------ ------------------------------------------ | binlog.000015 |      965 |              |                  | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-9 | --------------- ---------- -------------- ------------------ ------------------------------------------ 1 row in set (0.00 sec)mysql> set gtid_next='e10c75be-5c1b-11e6-ab7c-000c296078ae:12';Query OK, 0 rows affected (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> set gtid_next='AUTOMATIC';Query OK, 0 rows affected (0.00 sec)mysql> show master status; --------------- ---------- -------------- ------------------ --------------------------------------------- | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           | --------------- ---------- -------------- ------------------ --------------------------------------------- | binlog.000015 |     1158 |              |                  | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-9:12 | --------------- ---------- -------------- ------------------ --------------------------------------------- 1 row in set (0.00 sec) 

继续执行事务,MySQL会分配一个最小的未使用GTID,也就是从出现空洞的地方分配GTID,最终会把空洞填上。

mysql> insert into tbx1 values(1);Query OK, 1 row affected (0.01 sec)mysql> show master status; --------------- ---------- -------------- ------------------ ---------------------------------------------- | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            | --------------- ---------- -------------- ------------------ ---------------------------------------------- | binlog.000015 |     1416 |              |                  | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10:12 | --------------- ---------- -------------- ------------------ ---------------------------------------------- 1 row in set (0.00 sec) 

这意味着严格来说我们即不能假设GTID集合是连续的,也不能假定GTID序号大的事务在GTID序号小的事务之后执行,事务的顺序应由事务记录在binlog中的先后顺序决定。

新京葡娱乐场网址 2View Code

GTID的持久化

GTID相关的信息存储在binlog文件中,为此MySQL5.6新增了下面2个binlog事件。

  • Previous_gtids_log_event在每个binlog文件的开头部分,记录在该binlog文件之前已执行的GTID集合。
  • Gtid_log_event即前面看到的set gtid_next ...,它出现在每个事务的前面,表明下一个事务的gtid。

示例如下:

mysql> show binlog events IN 'binlog.000015'; --------------- ----- ---------------- ----------- ------------- ------------------------------------------------------------------- | Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                              | --------------- ----- ---------------- ----------- ------------- ------------------------------------------------------------------- | binlog.000015 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.31-77.0-log, Binlog ver: 4                        || binlog.000015 | 120 | Previous_gtids |         1 |         191 | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-6                          || binlog.000015 | 191 | Gtid           |         1 |         239 | SET @@SESSION.GTID_NEXT= 'e10c75be-5c1b-11e6-ab7c-000c296078ae:7' || binlog.000015 | 239 | Query          |         1 |         318 | BEGIN                                                             || binlog.000015 | 318 | Query          |         1 |         418 | use `test`; insert into tbx1 values(1)                            || binlog.000015 | 418 | Xid            |         1 |         449 | COMMIT /* xid=13 */                                               || binlog.000015 | 449 | Gtid           |         1 |         497 | SET @@SESSION.GTID_NEXT= 'e10c75be-5c1b-11e6-ab7c-000c296078ae:8' || binlog.000015 | 497 | Query          |         1 |         576 | BEGIN                                                             || binlog.000015 | 576 | Query          |         1 |         676 | use `test`; insert into tbx1 values(1)                            || binlog.000015 | 676 | Xid            |         1 |         707 | COMMIT /* xid=17 */                                               || binlog.000015 | 707 | Gtid           |         1 |         755 | SET @@SESSION.GTID_NEXT= 'e10c75be-5c1b-11e6-ab7c-000c296078ae:9' || binlog.000015 | 755 | Query          |         1 |         834 | BEGIN                                                             || binlog.000015 | 834 | Query          |         1 |         934 | use `test`; insert into tbx1 values(1)                            || binlog.000015 | 934 | Xid            |         1 |         965 | COMMIT /* xid=20 */                                               | --------------- ----- ---------------- ----------- ------------- ------------------------------------------------------------------- 14 rows in set (0.00 sec) 

MySQL服务器启动时,通过读binlog文件,初始化gtid_executed和gtid_purged,使它们的值能和上次MySQL运行时一致。

  • gtid_executed被设置为最新的binlog文件中Previous_gtids_log_event和所有Gtid_log_event的并集。
  • gtid_purged为最老的binlog文件中Previous_gtids_log_event。

由于这两个重要的变量值记录在binlog中,所以开启gtid_mode时必须同时在主库上开启log_bin在备库上开启log_slave_updates。

但是,在MySQL5.7中没有这个限制。MySQL5.7中,新增加一个系统表mysql.gtid_executed用于持久化已执行的GTID集合。当主库上没有开启log_bin或在备库上没有开启log_slave_updates时,mysql.gtid_executed会跟用户事务一起每次更新。否则只在binlog日志发生rotation时更新mysql.gtid_executed。

##==========================================##
新京葡娱乐场网址 ,在基于GTID模式下,不会进行Phase 3.2阶段,即尝试从原Master服务器中获取最新BINLOG。

如何配置基于GTID的复制

MySQL服务器的my.cnf配置文件中增加GTID相关的参数

log_bin                        = /mysql/binlog/mysql_binlog_slave_updates              = truegtid_mode                      = ON enforce_gtid_consistency       = true relay_log_info_repository      = TABLErelay_log_recovery             = ON 

然后在Slave上指定MASTER_AUTO_POSITION = 1执行CHANGE MASTER TO即可。比如:

CHANGE MASTER TO MASTER_HOST='node1',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_AUTO_POSITION=1; 

新京葡娱乐场网址 3

基于GTID的复制如何工作

在MASTER_AUTO_POSITION = 1的情况下 ,MySQL会使用COM_BINLOG_DUMP_GTID协议进行复制。过程如下:

备库发起复制连接时,将自己的已接受和已执行的gtids的并集(后面称为slave_gtid_executed)发送给主库。即下面的集合:

UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID) 

主库将自己的gtid_executed与slave_gtid_executed的差集的binlog发送给Slave。主库的binlog dump过程如下:

  1. 检查slave_gtid_executed是否是主库gtid_executed的子集,如否那么主备数据可能不一致,报错。
  2. 检查主库的purged_executed是否是slave_gtid_executed的子集,如否代表缺失备库需要的binlog,报错
  3. 从最后一个Binlog开始扫描,获取文件头部的PREVIOUS_GTIDS_LOG_EVENT,如果它是slave_gtid_executed的子集,则这是需要发送给Slave的第一个binlog文件,否则继续向前扫描。
  4. 从第3步找到的binlog文件的开头读取binlog记录,判断binlog记录是否已被包含在slave_gtid_executed中,如果已包含跳过不发送。

从上面的过程可知,在指定MASTER_AUTO_POSITION = 1时,Master发送哪些binlog记录给Slave,取决于Slave的gtid_executed和Retrieved_Gtid_Set以及Master的gtid_executed,和relay_log_info以及master_log_info中保存的复制位点没有关系。

使用GTID模式切换的日志:

如何修复复制错误

在基于GTID的复制拓扑中,要想修复Slave的SQL线程错误,过去的SQL_SLAVE_SKIP_COUNTER方式不再适用。需要通过设置gtid_next或gtid_purged完成,当然前提是已经确保主从数据一致,仅仅需要跳过复制错误让复制继续下去。比如下面的场景:

在从库上创建表tb1

mysql> set sql_log_bin=0;Query OK, 0 rows affected (0.00 sec)mysql> create table tb1(id int primary key,c1 int);Query OK, 0 rows affected (1.06 sec)mysql> set sql_log_bin=1;Query OK, 0 rows affected (0.00 sec) 

在主库上创建表tb1

mysql> create table tb1(id int primary key,c1 int);Query OK, 0 rows affected (1.06 sec) 

由于从库上这个表已经存在,从库的复制SQL线程出错停止。

mysql> show slave statusG*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.125.134                  Master_User: sn_repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000001          Read_Master_Log_Pos: 1422               Relay_Log_File: mysqld-relay-bin.000003                Relay_Log_Pos: 563        Relay_Master_Log_File: binlog.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: 1050                   Last_Error: Error 'Table 'tb1' already exists' on query. Default database: 'test'. Query: 'create table tb1(id int primary key,c1 int)'                 Skip_Counter: 0          Exec_Master_Log_Pos: 1257              Relay_Log_Space: 933              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1050               Last_SQL_Error: Error 'Table 'tb1' already exists' on query. Default database: 'test'. Query: 'create table tb1(id int primary key,c1 int)'  Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                  Master_UUID: e10c75be-5c1b-11e6-ab7c-000c296078ae             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:            Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp: 161203 15:14:17               Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: e10c75be-5c1b-11e6-ab7c-000c296078ae:5-6            Executed_Gtid_Set: e10c75be-5c1b-11e6-ab7c-000c296078ae:1-5                Auto_Position: 11 row in set (0.00 sec) 

从上面的输出可以知道,从库已经执行过的事务是'e10c75be-5c1b-11e6-ab7c-000c296078ae:1-5',执行出错的事务是'e10c75be-5c1b-11e6-ab7c-000c296078ae:6',当前主备的数据其实是一致的,可以通过设置gtid_next跳过这个出错的事务。

在从库上执行以下SQL:

mysql> set gtid_next='e10c75be-5c1b-11e6-ab7c-000c296078ae:6';Query OK, 0 rows affected (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> set gtid_next='AUTOMATIC';Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.02 sec) 

设置gtid_next的方法一次只能跳过一个事务,要批量的跳过事务可以通过设置gtid_purged完成。假设下面的场景:

主库上已执行的事务

mysql> show master status; --------------- ---------- -------------- ------------------ ------------------------------------------- | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         | --------------- ---------- -------------- ------------------ ------------------------------------------- | binlog.000001 |     2364 |              |                  | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10 | --------------- ---------- -------------- ------------------ ------------------------------------------- 1 row in set (0.00 sec) 

从库上已执行的事务

mysql> show master status; --------------- ---------- -------------- ------------------ ------------------------------------------ | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        | --------------- ---------- -------------- ------------------ ------------------------------------------ | binlog.000001 |     1478 |              |                  | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-6 | --------------- ---------- -------------- ------------------ ------------------------------------------ 1 row in set (0.00 sec) 

假设经过修复从库已经和主库的数据一致了,但由于复制错误Slave的SQL线程依然处于停止状态。现在可以通过把从库的gtid_purged设置为和主库的gtid_executed一样跳过不一致的GTID使复制继续下去,步骤如下。

在从库上执行

mysql> reset master;Query OK, 0 rows affected (0.01 sec)mysql> set GLOBAL gtid_purged='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';Query OK, 0 rows affected (0.03 sec)mysql> show master status; --------------- ---------- -------------- ------------------ ------------------------------------------- | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         | --------------- ---------- -------------- ------------------ ------------------------------------------- | binlog.000002 |      191 |              |                  | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10 | --------------- ---------- -------------- ------------------ ------------------------------------------- 1 row in set (0.00 sec) 

此时从库的Executed_Gtid_Set已经包含了主库上'1-10'的事务,再开启复制会从后面的事务开始执行,就不会出错了。

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

使用gtid_next和gtid_purged修复复制错误的前提是,跳过那些事务后仍可以确保主备数据一致。如果做不到,就要考虑pt-table-sync或者拉备份的方式了。

新京葡娱乐场网址 4新京葡娱乐场网址 5

GTID与备份恢复

在做备份恢复的时候,有时需要恢复出来的MySQL实例可以作为Slave连上原来的主库继续复制,这就要求从备份恢复出来的MySQL实例拥有和数据一致的gtid_executed值。这也是通过设置gtid_purged实现的,下面看下mysqldump做备份的例子。

Sun Jul  8 23:35:21 2018 - [info] MHA::MasterMonitor version 0.56.
Sun Jul  8 23:35:21 2018 - [info] GTID failover mode = 1
Sun Jul  8 23:35:21 2018 - [info] Dead Servers:
Sun Jul  8 23:35:21 2018 - [info] Alive Servers:
Sun Jul  8 23:35:21 2018 - [info]   10.0.203.104(10.0.203.104:3358)
Sun Jul  8 23:35:21 2018 - [info]   10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:35:21 2018 - [info]   10.0.203.117(10.0.203.117:3358)
Sun Jul  8 23:35:21 2018 - [info] Alive Slaves:
Sun Jul  8 23:35:21 2018 - [info]   10.0.203.104(10.0.203.104:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:35:21 2018 - [info]     GTID ON
Sun Jul  8 23:35:21 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:35:21 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Jul  8 23:35:21 2018 - [info]   10.0.203.117(10.0.203.117:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:35:21 2018 - [info]     GTID ON
Sun Jul  8 23:35:21 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:35:21 2018 - [info] Current Alive Master: 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:35:21 2018 - [info] Checking slave configurations..
Sun Jul  8 23:35:21 2018 - [info]  read_only=1 is not set on slave 10.0.203.104(10.0.203.104:3358).
Sun Jul  8 23:35:21 2018 - [info]  read_only=1 is not set on slave 10.0.203.117(10.0.203.117:3358).
Sun Jul  8 23:35:21 2018 - [info] Checking replication filtering settings..
Sun Jul  8 23:35:21 2018 - [info]  binlog_do_db= , binlog_ignore_db= 
Sun Jul  8 23:35:21 2018 - [info]  Replication filtering check ok.
Sun Jul  8 23:35:21 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sun Jul  8 23:35:21 2018 - [info] Checking SSH publickey authentication settings on the current master..
Sun Jul  8 23:35:22 2018 - [info] HealthCheck: SSH to 10.0.203.109 is reachable.
Sun Jul  8 23:35:22 2018 - [info] 
10.0.203.109(10.0.203.109:3358) (current master)
  --10.0.203.104(10.0.203.104:3358)
  --10.0.203.117(10.0.203.117:3358)

Sun Jul  8 23:35:22 2018 - [warning] master_ip_failover_script is not defined.
Sun Jul  8 23:35:22 2018 - [warning] shutdown_script is not defined.
Sun Jul  8 23:35:22 2018 - [info] Set master ping interval 1 seconds.
Sun Jul  8 23:35:22 2018 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Sun Jul  8 23:35:22 2018 - [info] Starting ping health check on 10.0.203.109(10.0.203.109:3358)..
Sun Jul  8 23:35:22 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Sun Jul  8 23:35:58 2018 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sun Jul  8 23:35:58 2018 - [info] Executing SSH check script: exit 0
Sun Jul  8 23:35:58 2018 - [info] HealthCheck: SSH to 10.0.203.109 is reachable.
Sun Jul  8 23:35:59 2018 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Sun Jul  8 23:35:59 2018 - [warning] Connection failed 2 time(s)..
Sun Jul  8 23:36:00 2018 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Sun Jul  8 23:36:00 2018 - [warning] Connection failed 3 time(s)..
Sun Jul  8 23:36:01 2018 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Sun Jul  8 23:36:01 2018 - [warning] Connection failed 4 time(s)..
Sun Jul  8 23:36:01 2018 - [warning] Master is not reachable from health checker!
Sun Jul  8 23:36:01 2018 - [warning] Master 10.0.203.109(10.0.203.109:3358) is not reachable!
Sun Jul  8 23:36:01 2018 - [warning] SSH is reachable.
Sun Jul  8 23:36:01 2018 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status..
Sun Jul  8 23:36:01 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jul  8 23:36:01 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sun Jul  8 23:36:01 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sun Jul  8 23:36:01 2018 - [info] GTID failover mode = 1
Sun Jul  8 23:36:01 2018 - [info] Dead Servers:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info] Alive Servers:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.104(10.0.203.104:3358)
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.117(10.0.203.117:3358)
Sun Jul  8 23:36:01 2018 - [info] Alive Slaves:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.104(10.0.203.104:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.117(10.0.203.117:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info] Checking slave configurations..
Sun Jul  8 23:36:01 2018 - [info]  read_only=1 is not set on slave 10.0.203.104(10.0.203.104:3358).
Sun Jul  8 23:36:01 2018 - [info]  read_only=1 is not set on slave 10.0.203.117(10.0.203.117:3358).
Sun Jul  8 23:36:01 2018 - [info] Checking replication filtering settings..
Sun Jul  8 23:36:01 2018 - [info]  Replication filtering check ok.
Sun Jul  8 23:36:01 2018 - [info] Master is down!
Sun Jul  8 23:36:01 2018 - [info] Terminating monitoring script.
Sun Jul  8 23:36:01 2018 - [info] Got exit code 20 (Master dead).
Sun Jul  8 23:36:01 2018 - [info] MHA::MasterFailover version 0.56.
Sun Jul  8 23:36:01 2018 - [info] Starting master failover.
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 1: Configuration Check Phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] GTID failover mode = 1
Sun Jul  8 23:36:01 2018 - [info] Dead Servers:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info] Checking master reachability via MySQL(double check)...
Sun Jul  8 23:36:01 2018 - [info]  ok.
Sun Jul  8 23:36:01 2018 - [info] Alive Servers:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.104(10.0.203.104:3358)
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.117(10.0.203.117:3358)
Sun Jul  8 23:36:01 2018 - [info] Alive Slaves:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.104(10.0.203.104:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.117(10.0.203.117:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info] Starting GTID based failover.
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 2: Dead Master Shutdown Phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] Forcing shutdown so that applications never connect to the current master..
Sun Jul  8 23:36:01 2018 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Sun Jul  8 23:36:01 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Jul  8 23:36:01 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 3: Master Recovery Phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] The latest binary log file/position on all slaves is mysql-bin.000008:6689
Sun Jul  8 23:36:01 2018 - [info] Retrieved Gtid Set: 541e0f07-8047-11e8-8434-0800270b00d2:49-69
Sun Jul  8 23:36:01 2018 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.104(10.0.203.104:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.117(10.0.203.117:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info] The oldest binary log file/position on all slaves is mysql-bin.000008:6689
Sun Jul  8 23:36:01 2018 - [info] Retrieved Gtid Set: 541e0f07-8047-11e8-8434-0800270b00d2:49-69
Sun Jul  8 23:36:01 2018 - [info] Oldest slaves:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.104(10.0.203.104:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.117(10.0.203.117:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 3.3: Determining New Master Phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] Searching new master from slaves..
Sun Jul  8 23:36:01 2018 - [info]  Candidate masters from the configuration file:
Sun Jul  8 23:36:01 2018 - [info]   10.0.203.104(10.0.203.104:3358)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Jul  8 23:36:01 2018 - [info]     GTID ON
Sun Jul  8 23:36:01 2018 - [info]     Replicating from 10.0.203.109(10.0.203.109:3358)
Sun Jul  8 23:36:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Jul  8 23:36:01 2018 - [info]  Non-candidate masters:
Sun Jul  8 23:36:01 2018 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Sun Jul  8 23:36:01 2018 - [info] New master is 10.0.203.104(10.0.203.104:3358)
Sun Jul  8 23:36:01 2018 - [info] Starting master failover..
Sun Jul  8 23:36:01 2018 - [info] 
From:
10.0.203.109(10.0.203.109:3358) (current master)
  --10.0.203.104(10.0.203.104:3358)
  --10.0.203.117(10.0.203.117:3358)

To:
10.0.203.104(10.0.203.104:3358) (new master)
  --10.0.203.117(10.0.203.117:3358)
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 3.3: New Master Recovery Phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info]  Waiting all logs to be applied.. 
Sun Jul  8 23:36:01 2018 - [info]   done.
Sun Jul  8 23:36:01 2018 - [info] Getting new master's binlog name and position..
Sun Jul  8 23:36:01 2018 - [info]  mysql-bin.000006:77499
Sun Jul  8 23:36:01 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.203.104', MASTER_PORT=3358, MASTER_AUTO_POSITION=1, MASTER_USER='replicater', MASTER_PASSWORD='xxx';
Sun Jul  8 23:36:01 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000006, 77499, 41d8a420-8047-11e8-8580-080027e837eb:1-92,
541e0f07-8047-11e8-8434-0800270b00d2:1-69
Sun Jul  8 23:36:01 2018 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Sun Jul  8 23:36:01 2018 - [info] ** Finished master recovery successfully.
Sun Jul  8 23:36:01 2018 - [info] * Phase 3: Master Recovery Phase completed.
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 4: Slaves Recovery Phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 4.1: Starting Slaves in parallel..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] -- Slave recovery on host 10.0.203.117(10.0.203.117:3358) started, pid: 5680. Check tmp log /var/log/masterha/app1/10.0.203.117_3358_20180708233601.log if it takes time..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] Log messages from 10.0.203.117 ...
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info]  Resetting slave 10.0.203.117(10.0.203.117:3358) and starting replication from the new master 10.0.203.104(10.0.203.104:3358)..
Sun Jul  8 23:36:01 2018 - [info]  Executed CHANGE MASTER.
Sun Jul  8 23:36:01 2018 - [info]  Slave started.
Sun Jul  8 23:36:01 2018 - [info]  gtid_wait(41d8a420-8047-11e8-8580-080027e837eb:1-92,
541e0f07-8047-11e8-8434-0800270b00d2:1-69) completed on 10.0.203.117(10.0.203.117:3358). Executed 0 events.
Sun Jul  8 23:36:01 2018 - [info] End of log messages from 10.0.203.117.
Sun Jul  8 23:36:01 2018 - [info] -- Slave on host 10.0.203.117(10.0.203.117:3358) started.
Sun Jul  8 23:36:01 2018 - [info] All new slave servers recovered successfully.
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] * Phase 5: New master cleanup phase..
Sun Jul  8 23:36:01 2018 - [info] 
Sun Jul  8 23:36:01 2018 - [info] Resetting slave info on the new master..
Sun Jul  8 23:36:01 2018 - [info]  10.0.203.104: Resetting slave info succeeded.
Sun Jul  8 23:36:01 2018 - [info] Master failover to 10.0.203.104(10.0.203.104:3358) completed successfully.
Sun Jul  8 23:36:01 2018 - [info] 

----- Failover Report -----

app1: MySQL Master failover 10.0.203.109(10.0.203.109:3358) to 10.0.203.104(10.0.203.104:3358) succeeded

Master 10.0.203.109(10.0.203.109:3358) is down!

Check MHA Manager logs at localhost.localdomain:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Selected 10.0.203.104(10.0.203.104:3358) as a new master.
10.0.203.104(10.0.203.104:3358): OK: Applying all logs succeeded.
10.0.203.117(10.0.203.117:3358): OK: Slave started, replicating from 10.0.203.104(10.0.203.104:3358)
10.0.203.104(10.0.203.104:3358): Resetting slave info succeeded.
Master failover to 10.0.203.104(10.0.203.104:3358) completed successfully.

通过mysqldump进行备份

通过mysqldump做一个全量备份

[[email protected] ~]# mysqldump --all-databases --single-transaction --routines --events --host=127.0.0.1 --port=3306 --user=root > dump.sql 

生成的dump.sql文件里包含了设置gtid_purged的语句

dump.sql:

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;SET @@SESSION.SQL_LOG_BIN= 0;...SET @@GLOBAL.GTID_PURGED='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';...SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; 

恢复数据前需要先通过reset master清空gtid_executed变量

[[email protected] ~]# mysql -h127.1 -e 'reset master'[[email protected] ~]# mysql -h127.1 <dump.sql 

否则执行设置GTID_PURGED的SQL时会报下面的错误

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 

此时恢复出的MySQL实例的GTID_EXECUTED和备份时点一致

mysql> show master status; --------------- ---------- -------------- ------------------ ------------------------------------------- | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         | --------------- ---------- -------------- ------------------ ------------------------------------------- | binlog.000002 |      191 |              |                  | e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10 | --------------- ---------- -------------- ------------------ ------------------------------------------- 1 row in set (0.00 sec) 

由于恢复出的MySQL实例已经被设置的正确的GTID_EXECUTED,以master_auto_postion = 1的方式CHANGE MASTER到原来的主节点即可开始复制。

CHANGE MASTER TO MASTER_HOST='node1', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1 

如果不希望备份文件中生成设置GTID_PURGED的SQL,可以给mysqldump传入--set-gtid-purged=OFF关闭。

View Code

通过Xtrabackup进行备份

相比mysqldump,Xtrabackup是效率更高并且被广泛使用的备份方式。使用Xtrabackup进行备份的举例如下。

通过Xtrabackup创一个全量备份(可以在Slave上创建备份,以避免对主库的性能冲击)

innobackupex --defaults-file=/etc/my.cnf --host=127.1 --user=root --password=mysql --no-timestamp --safe-slave-backup --slave-info /mysql/bak 

应用日志

innobackupex --apply-log /mysql/bak 

查看备份目录中的xtrabackup_binlog_info文件可以找到备份时已经执行过的gtids

[[email protected] ~]# cat /mysql/bak/xtrabackup_binlog_infomysql_bin.000001    191 e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10 

由于备份时添加了”--slave-info”选项并且从Slave节点拉取的备份,所以会生成xtrabackup_slave_info文件,也可以从这个文件里查找建立复制的SQL语句。

[[email protected] ~]# cat /mysql/bak/xtrabackup_slave_infoSET GLOBAL gtid_purged='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';CHANGE MASTER TO MASTER_AUTO_POSITION=1 

将备份文件传送到新的节点node3的/mysql/bak目录并恢复(如果直接把备份传输到数据目录了,这一步可以省略)。

[[email protected] ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /mysql/bak 

启动MySQL。

[[email protected] ~]# mysqld --defaults-file=/home/mysql/etc/my.cnf --skip-slave-start & 

如果是从Slave拉的备份,一定不能直接开启Slave复制,这时的gtid_executed是错误的。需要手动设置gtid_purged后再start slave

reset master;SET GLOBAL gtid_purged='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';CHANGE MASTER TO MASTER_HOST='node1',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_AUTO_POSITION=1;start slave; 

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:与MySQL传统复制相比

关键词: