xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

GTID有啥出色的复制姿势

时间:2019-10-02 04:22来源:澳门新葡8522最新网站
##==========================================## 与MySQL古板复制比较,GTID有怎么着非凡的复制姿势? MySQL5.6版本引进GTID来化解主干切换时BINLOG地点点难定位的标题,MHA从0.56版本开首补助基于GTID的

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

与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。
 澳门新葡8522最新网站 1

如何查看GTID

能够通过MySQL的多少个变量查占卜关的GTID音讯。

  • gtid_executed
    在近年来实例上实践过的GTID集结; 实际上包蕴了具备记录到binlog中的事务。所以,设置set sql_log_bin=0后施行的事体不会生成binlog 事件,也不会被记录到gtid_executed中。实践RESET MASTEPRADO能够将该变量置空。

  • gtid_purged
    binlog不容许永世驻留在服务上,需求定时开展清理(通过expire_logs_days能够控制订期清理间隔),不然势必它会把磁盘用尽。gtid_purged用于记录已经被铲除了的binlog事务集合,它是gtid_executed的子集。只有gtid_executed为空时手艺手动设置该变量,此时会同临时间更新gtid_executed为和gtid_purged一样的值。gtid_executed为空意味着要么在此以前并未有运营过基于GTID的复制,要么施行过RESET MASTEMercedes-AMG。实践RESET MASTEKuga时同样也会把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中的先后顺序决定。

澳门新葡8522最新网站 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_澳门新葡8522最新网站,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; 

澳门新葡8522最新网站 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中,假若已包括跳过不发送。

从地点的进度可以,在钦赐MASTEENVISION_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_COUNTELacrosse格局不再适用。须求通过设置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大概拉备份的章程了。

澳门新葡8522最新网站 4澳门新葡8522最新网站 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_PURAV4GED的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 MASTE君越到原来的主节点就可以伊始复制。

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; 

##==========================================##
MHA在检查时,使用SHOW SLAVE STATUS获取结构中Auto_Position的值来决断是不是利用master_auto_position参数来搭建主从复制。

GTID与MHA

MHA是被大范围运用MySQL HA组件,MHA 0.56随后支持基于GTID的复制。 MHA在failover时会自动剖断是或不是是GTID based failover,供给满意上面3个原则即为GTID based failover

  • 具备节点gtid_mode=1
  • 富有节点Executed_Gtid_Set不为空
  • 起码二个节点Auto_Position=1

和事先的基于binlog文件地方的复制比较,基于GTID复制下,MHA在故障切换时的生成注重如下:

  • 基于binlog文件地方的复制

    • 在Master宕机后会尝试从Master上拷贝binlog日志进行补偿   
    • 要是候选Master不具备最新的relay log,会从有着新型relay log的Slave上变化差距的binlog传送到候选Master并实施补给  
    • 新Master的日志补偿到位后,同样应用选择差距binlog的主意将别的Slave和新Master同步后再change master到新Master  
  • 基于GTID的复制  

    • 只要候选Master不具有最新的relay log,让候选Master连上富有新型relay log的Salve举办补偿。  
    • 品尝从binlog server上拉取缺点和失误的binlog并行使
    • 新Master的多少同步到新型后,让其余的Slave连上新Master并等待数据形成一块。並且能够给masterha_master_switch传入--wait_until_gtid_in_sync=1参数使其不等另外Slave完毕多少同步,以加速切换速度。

在GTID方式下MHA不会尝试从旧Master上拷贝binlog日志实行填补,所以在MySQL进程crash而OS仍旧健康的景况下,应尽量不要做主备切换而是原地重启MySQL,除非有别的能担保切换后不丢数据的措施。

在GTID格局下MHA帮助在复制拓扑中追加五个或七个binlog server起到日志补偿的效果与利益,非GTID形式下正是配置了binlog server也会被MHA忽略。

日记补偿能够说是MHA中最复杂也最精粹的片段,有了GTID后故障切换变得更简便易行了,不再须要原来复杂的binlog日志分析和补偿。所以Oracle官方推出了只援助GTID复制的切换工具mysqlfailover,在GTID的支援下,大家有越多可相信的HA工具得以挑选。

MHA在切换时,假诺应用非GTID方式切换,则在CHANGE MASTEGranCabrio中不会带上参数master_auto_position=0,而只要该从库从前安插为master_auto_position=1,那么CHANGE MASTEHaval会报错,不可能不奇怪开展切换。

GTID与crash safe slave

crash safe slave是MySQL 5.6提供的效果,意思是说在slave crash后,把slave重新拉起来能够三番五次从Master进行复制,不会油不过生复制错误也不会出现数量不等同。

进而不可能简单修改Server.PM或DBHelper.pm文件来将依据GTID方式切换的会集修改为使用非GTID情势开展切换。

基于binlog文件地方的复制

在基于binlog文件地点的复制下,要力保crash safe slave,配置下边的参数就可以。

relay_log_info_repository      = TABLErelay_log_recovery             = ON 

那样可行的原由是,relay_log_info_repository = TABLE时,apply

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

event和更新relay_log_info表的操作被含有在同一个事情里,innodb要么让它们同不常间生效,要么同时不见效,保险位点新闻和已经运用的专业正确相配。同一时间relay_log_recovery

ON时,会抛弃master_log_info中记录的复制位点,依据relay_log_info的实施职位再度从Master获取binlog,那就逃避了是因为未共同刷盘导致的binlog文件接受地方和骨子里分歧样以及relay log文件被截断的标题。

在同临时候选用MTS(multi-threaded slave)时,为力保crash safe slave基于binlog文件地方的复制还须求安装sync_relay_log=1,因为MySQL在Crash复苏时必需先经过读取relay log补齐MTS导致的事务空洞。

若果集结因为某种原因导致基本节点上的Executed_Gtid_Set不同,如:

基于GTID的复制

上面的设置并不适用于依赖GTID的复制。在依赖GTID的复制下,crash的Slave重启后,从binlog中深入分析的gtid_executed决定了要apply哪些binlog记录,所以binlog必得和innodb存款和储蓄引擎的多郎中持一致。要产生那点,需求把sync_binlog和innodb_flush_log_at_trx_commit都设置为1,即所谓的"双1"。

别的mysql运营时,会从relay log文件中收获已抽出的GTIDs并创新Retrieved_Gtid_Set。由于relay log文件或然不完整,所以需求遗弃已吸取的relay log文件。由此relay_log_recovery = ON也是必需的。

如此,对于基于GTID的复制,保障crash safe slave的安装就是上面那样。

sync_binlog                    = 1innodb_flush_log_at_trx_commit = 1relay_log_recovery             = ON 

关于什么设置以确认保证crash safe slave,官方文书档案有鲜明记载,见17.3.2 Handling an Unexpected 哈尔t of a Replication Slave。

可是里面有关GTID的记载中存在笔误,将relay_log_recovery=1写成了relay_log_recovery=0(#83711)。同临时常间也不曾关系必需安装"双1",不过"双1"是必备的,不然crash的Slave重启后,恐怕会再一次应用binlog event也可能会孤陋寡闻应用binlog event(#70659)。当中遗漏应用binlog event的意况更吓人,因为Slave在不触发SQL错误的情景下就默默的和Master分化了。

1、对从库实行直接授权,导致从库比主库具有越来越多BINLOG,但该Binlog因种种缘由被Purged掉

设置"双1"对品质的震慑

是因为安全着想,猛烈推荐设置"双1"。"双1"会叠合各样业务的RT,但得益于MySQL的组提交机制,高并发下"双1"对系统一整合体tps的震慑在可承受范围内。

sysbencholtp.lua10张表每张表100w记录(qps/并发数)澳门新葡8522最新网站 6

澳门新葡8522最新网站 7
对峙异同一行那样无法有效互动的场地,"双1"对品质的震慑一点都一点都不小。

sysbenchupdate_non_index.lua1张表1条记录(qps/并发数)澳门新葡8522最新网站 8

澳门新葡8522最新网站 9
对无法有效互动的Slave replay,存在同样的主题素材。

经过钦点tx-rate实施sysbench的update_non_index.lua脚本压测30秒,达成后检查主备延迟。

能够开采在Slave被铺排为"双1"的情事下,延迟充裕惨痛,一千上述的qps就能够产出延迟,非"双1"下qps到六千以上才相会世延迟(主库配置为"双1")。

sysbenchupdate_non_index.lua1张表100w条记录 128并发(延迟/qps)澳门新葡8522最新网站 10

澳门新葡8522最新网站 11
如上测量试验意况是Percona Server 5.6运维在布署HDD的8 core虚机,由于测量试验结果和类别IO技能有比一点都不小关系,仅供参谋。

2、集结做过版本晋级,从未利用GTID的版本晋级到GTID版本,从库上曾一段时间内作为主库提供劳动,但该时间段日志被Purged掉

什么样在非"双1"下保险crash safe slave

设倘若MySQL 5.7得以关闭log_slave_updates,那样MySQL会将已进行的GTIDs实时记下到系统表mysql.gtid_executed中,mysql.gtid_executed是和顾客业务一同提交的,由此可以确认保障和实际的数额一致。

log_slave_updates              = OFFrelay_log_recovery             = ON 

一旦是MySQL 5.6能够运用如下变化的措施。

依据基于binlog文件复制时crash safe slave的渴求安装relay_log_info_repository = TABLE

relay_log_info_repository      = TABLErelay_log_recovery             = ON 

在Slave crash后,根据relay_log_info_repository设置相应的gitd_purged再张开复制,步骤如下。

  1. 初阶mysql,但不开启复制

    mysqld --skip-slave-start 
    
  2. 在Slave上修修改改为基于binlog文件地点的复制

    change master to MASTER_AUTO_POSITION = 0 
    
  3. 启动slave IO线程

    start slave io_thread 
    

    此间无法运转SQL线程,假使接受到的GTID已经在Slave的gtid_executed里了,会被Slave skip掉。

  4. 检查binlog传输的上马地方(即Retrieved_Gtid_Set的值)

    show slave statusG 
    

    借使输出的Retrieved_Gtid_Set值为e10c75be-5c1b-11e6-ab7c-000c296078ae:7-10

  5. 在Master上检查gtid_executed

    show master status 
    

    借使输出的Executed_Gtid_Set值为e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10

  6. 在Slave上设置gitd_purged为binlog传输地方的后面包车型客车GTID的会见

    reset master;set global gitd_purged='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-6'; 
    
  7. 修改回auto position的复制

    change master to MASTER_AUTO_POSITION = 1 
    
  8. 启动slave SQL线程

    start slave sql_thread 
    

可是,这种变化的议程不切合三十二线程复制。因为多线程复制或然发生gtid gap和Gap-free low-watermark position,那会促成Salve上海重机厂复apply已经apply过的event。后果正是多少不均等或许复制中断,除非设置binlog格式为row情势并且slave_exec_mode=IDEMPOTENT,slave_exec_mode=IDEMPOTENT允许Slave重放binlog时大意重复键和找不到键的一无是处,使得binlog回看具备幂等性,但那也表示一旦真的出现了主备数据差别也会被它忽略。

有上诉类似难点时,将从库进步为主库并运用master_auto_position=1来布署复制,复制会因为新主库无法提供丰富BINLOG事件而停业。

MTS下蓄意的难点

在同临时候使用MTS(slave_parallel_workers> 1)时,就算按上面crash safe slave的须要安装了基于GTID的复制,Slave crash后再重启照旧会招致复制中断。

经过强制杀掉MySQL所在虚机的章程模拟Slave宕机,然后再开发银行MySQL,mysql日志中有如下错误新闻:

---------------------------------2016-10-26 21:00:23 2699 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysql-relay-bin' to avoid this problem.2016-10-26 21:00:24 2699 [Note] Slave: MTS group recovery relay log info based on Worker-Id 1, group_relay_log_name ./mysql-relay-bin.000011, group_relay_log_pos 2017523 group_master_log_name binlog.000007, group_master_log_pos 20173632016-10-26 21:00:24 2699 [ERROR] Error looking for file after ./mysql-relay-bin.000012.2016-10-26 21:00:24 2699 [ERROR] Failed to initialize the master info structure2016-10-26 21:00:24 2699 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted.2016-10-26 21:00:24 2699 [Note] Event Scheduler: Loaded 0 events2016-10-26 21:00:24 2699 [Note] mysqld: ready for connections.Version: '5.6.31-77.0-log'  socket: '/data/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 77.0, Revision 5c1061c--------------------------------- 

开头slave时也会报错

mysql> start slave;ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository 

并发这种景观的缘故在于,relay_log_recovery=1且slave_parallel_workers>1的图景下,mysql运行时会进来MTS Group恢复生机流程,即读取relay log,尝试填补由于四线程复制导致的gap。然后relay log文件由于不是实时刷新的,在relay log文件中找不到gap对应的relay log记录(覆盖了gap的relay log初始和得了地方分别被叫做低水位和高水位,低水位点即slave_relay_log_info.Relay_log_pos的值)就能够报那几个错。

实质上,在GTID形式下,slave在apply event的时候能够跳过重复事件,所以能够安枕无忧的从低水位点应用日志,没须要解析relay log文件。 那看起来是四个bug,于是提交了二个bug报告#83713,如今还尚未收到回复。

用作逃避方法,能够经过免去relay log文件,跳过那一个破绽百出。施行步骤如下

reset slave;change master to MASTER_AUTO_POSITION = 1start slave; 

在此间,单纯的调reset slave不能够把情形清理深透,内部的Relay_log_info.inited标识位照旧居于未被初阶化状态,此时调用start slave仍旧会停业。因而供给补一刀change master。

拍卖措施:

Master的crash safe

前方平昔在讲crash safe slave,Master的crash safe同样主要。 要想Master保持crash safe须求按上边的参数实行安装,不然不仅仅会遗弃事务,gtid_executed还恐怕和骨子里的innodb存款和储蓄引擎中的数据不均等。

sync_binlog                    = 1innodb_flush_log_at_trx_commit = 1 

在Master配置为"双1"的情形下,Master crash后,若无发生failover,可以一而再作为Master。 假若产生了failover,能够检查旧Master和新Master上由旧Master实践的事务集结是还是不是一致。

 show master status 

万一同样,能够按MASTE奥迪Q3_AUTO_POSITION = 1的法子将旧Master作为Slave和新Master创设复制关系。不然,挂念做作业补偿或从新Master上拉取备份实行回复。

在Master配置不是"双1"的动静下,在Master crash后由于难以正确通晓旧Master上毕竟实践了哪些职业,安全的做法是实践主备切换,并从新Master上拉取备份,把旧Master作为新Master的Slave实行复苏。

本文为DBA 社会群众体育的投稿小说: 与MySQL古板复制相比较,GTID有哪些...

1、通过RESET MASTER和SET GLOBAL gtid_purged=''使得全部节点有所同样的GTID 集合

2、将具备复制修改为依照POS点搭建的复制。

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

澳门新葡8522最新网站 12

编辑:澳门新葡8522最新网站 本文来源:GTID有啥出色的复制姿势

关键词: www8029com