您的位置:澳门新葡8455最新网站 > 数据库管理 > GTID环境下恢复从库思路方法,一次事故的回顾

GTID环境下恢复从库思路方法,一次事故的回顾

发布时间:2019-11-14 16:55编辑:数据库管理浏览(113)

    背景:MySQL5.6.40,库异常的小,row+gtid复制情形,但由于原先各样原因,备份还原在从库后,开启复制存在大批量1062,1032荒唐,gtid卡在靠前岗位。做复制的时候从不其余从库,每小时的备份也被运转停了。

    要研商如何回复从库,大家得先来询问如下一些概念:

    原先平昔没蒙受过这种情景,相对测验景况正式情况比较复杂,何况估摸只怕是早前备份还原一直没用过备份风流倜傥致性参数导致,并且开掘错误也绝非手工业检查(那些标题还在钻探中,有相逢并理解原因的伴儿款待教导卡塔 尔(阿拉伯语:قطر‎。

    GTID_EXECUTED:它是生龙活虎组包括已经记录在二进制日志文件中的事务群集

    为了以往幸免因为复苏比不上时引致的多少遗失,极度总计此次故障进程和大家谈谈、分享。

    GTID_PU奥迪Q5GED:它是生机勃勃组包涵已经从二进制日志删除掉的事务集结。

    简化时间轴如下图:

     

    起先---->备份主库---->恢复生机从库---->复制error1032,1062---->删除从库再度恢复---->复制error1032,1062---->reset master从库、主库---->希图删除从库---->误操作删主库----->复苏主库----->跳过多量1062、1032不当---->找drop db地点苏醒从库---->相比中央数据---->手工业补数据---->甘休

     

    上面依据笔者的回想描述下登时的光景:

    在世襲研讨时,大家先来看下如何新建多个根据GTID的slave。

    生龙活虎、第贰次备份主库、搭建从库

    首次搭建从库,从主库的备份未利用master-data=2 single-transaction(保险专门的学业备份时的大器晚成致性卡塔 尔(英语:State of Qatar)参数迁移后,报多量1062和1032谬误(家家有本难念的经,相当的少说了卡塔尔

     

    图片 1

    经过询问上边的五个参数,我们未来只必要:

    二、第贰次恢复生机主库到从库

    于是乎第一回重复导入。

    相同报错。在导入从库前采纳reset master;将从库binlog杀绝。

    鉴于操作职员持续解reset master含义及奉行结果,又在主库做了reset master;

    结果形成主库全数binlog日志被免除并且binlog position置为1;

    那边贴以下官方认证,别没事干就在主库上用那条。

     

    图片 2

    重新导入发掘依旧大量报1032,1062谬误。

    出于疑惑是因为备份时没利用--single-transaction参数,计划删除从库,加参数重新备份主库。

    1.从主库上做三个备份时记下备份时gtid_executed的值。

    三、误删除主库

    结果误操作删除主库(这么些锅黄金年代部分原因要甩给mysql naivcat这些工具,垂直排列库,微微不理会就便于点错。依旧提出我们听吴老师的用合法的workbench卡塔 尔(阿拉伯语:قطر‎,删库如故两人校对,在操作系统上实行,删前没把握最佳备份二回。

    删库这种操作严慎当心再谨慎小心,首要的事体说叁回!

    删库这种操作步步为营再小心谨严,首要的业务说叁次!

    删库这种操作小心谨慎再小心,主要的政工说叁次!

    drop database;(在naivcat上右键删除库,但binlog日志中仍然会记录DROP DATABASE那条记下卡塔尔国

    那个时候为了确认保障职业不间断,立马在主库上经过事先的备份文件苏醒了风流洒脱套库,当然数据料定遗失了,但能够推算遗失数据的岁月段(从备份达成早先--->DROP DATABASE卡塔 尔(英语:State of Qatar)。

    PS.请不要问小编干什么删库,为何删完又回涨了风姿洒脱套库,因为都不是自己干的。。。。。。

    幸运的是误删除主库但一向不删除从库,并且从库的io_thread仍处yes状态(回看吴先生的课程,也便是说即便库被去除了但实际删库前的数据=备份数据+io_thread已下载的删除主库前的数码卡塔 尔(阿拉伯语:قطر‎,由于sql_thread依然停到gtid靠前之处

     

    图片 3

    2.在新的slave上复苏此备份时设置从库的gtid_purged的值为备份时master上gtid_executed的值。

    四、跳过大批量1032,1062八花九裂

    那个时候假使看下备份文件的gtid地点,并purge到该地方(以前备份丢了,随便找了二个备份的截图,精通万岁卡塔 尔(阿拉伯语:قطر‎。

    ##那边说美赞臣(Aptamil卡塔尔下为啥平素purge到备份的最后地方,因为书库备份的多寡中1032和1062错误太多,且主库已经去除无法通过脚本相比较跳过多量1032,1062荒诞(吴老老师和朋友情提供卡塔 尔(阿拉伯语:قطر‎,在能力所能达到确定保证是从主库逻辑备份过来的意况下(主从数据风度翩翩致卡塔尔,大家选拔快捷跳过大批量破绽超级多(偷懒加情状急卡塔尔,直接purge到备份最后的职位。

     

    图片 4

    ##上海教室是随意截的二个备份文件最起先的岗位,请忽视那些gtid的值,意思驾驭就行。

    set @@gtid_purged='fb1f83af-1915-11e8-811b-000c29c4d77d:1-500';

    注:‘500’代表备份文件最终三个履行的作业的gtid。gtid_purged代表数据库已经在从库上重播过1-500这段工作。

     

    五、找到主库DROP DATABASE的GTID地方

    purge到该地方然后再分明drop database的职位上(思路:假设不鲜明dropdatabase的地点就start slave 那么从库会应用主库的binlog也就能够实施主库drop database的操作,为了防止从库重播主库drop database的操作,我们要想尽让gtid在从库停到drop database前一个gtid的职位卡塔尔

    注:能够通过大约删库时间还是从从库的show slave statusG上观察主库的binlog地点从后往前找DROP DATABASE的岗位,要是删库后做了reset master那就必须要从从库的relay-bin-log上找了(切记主库没事别reset master);

    mysqlbinlog    -vvv  --base64-output=decode-rows  relay-bin.000017

     

    图片 5

    透过mysqldump能够形成大家须要的功力。

    六、运行从库SQL_THREAD

    在从库上执行start slave sql_thread until的吩咐,这里需求表达,因为主库已经苏醒,业务跑起来了,那时开启io_thread未有怎么意思,所以只用让从库的sql_thread线程重放DROP DATABASE早前的政工就能够。

    root@localhost[{none}]>start slave sql_thread until sql_before_gtid='fb1f83af-1915-11e8-811b-000c29c4d77d:2343';

    开发银行slave,并且让从库gtid停在主库drop database操作早前贰个gtid就足以,再还原到主库就能够立时投入使用,还不会引致数据遗失。

     

    图片 6

    保障从库executed_gtid_set到了大家before的前贰个值就足以备份了,然后dump那份数据恢复生机主库,当然假使从库质量不错的话能够思索应用端修正连接,那样速度更加快一些。

    但相比较费劲的就是,要作保临盆的实时性,删库后立时在主库上还原了前头用来过来从库的备份文件,那就必定会导致中间数据错失。

     

    七、数据相比较还原

    那儿必须要动用用事先用于搭建从库的备份再苏醒四个库,再用pt-table-checksum相比较主库和回复库,从库和重振旗鼓库不均等的多少,用pt-table-sync生成对应语句。然后手工把数据补进系统中。

    对比1:主库:备份数据苏醒的库---->指标:找到主库在删库之后选拔又写入了如何数据。

    对比2:从库:备份数据恢复生机的库---->目的:找到备份数据之后,删库早前运用在主Curry写了什么数据。

    因为量不是比超大,手工业相比较一下就能够,当然数据恢复的坑也会有相当多,可是基本上都被研究开发填了。

     

    总结:

    第2轮遭受删库处境依旧多少蒙,幸而主库用的是GTID找binlog日志中的位置相对轻松一点。本次恢复生机最幸运的正是幸亏从库卡在靠前的岗位,要否则就是有了从库,数据也会被删了,恢复生机起来相对更麻烦些。

    对于gtid的苏醒,课上吴炳锡先生都讲过,不过生龙活虎上手依然慢了几拍,依然要透超过实际战多练习加深手感制止在实况下懵逼。

    最终特别多谢:知数堂叶金荣先生和吴炳锡先生在故障发生时授予的赞助和扶助。

    转发请评释出处

    现阶段主库上的境况(3301卡塔 尔(阿拉伯语:قطر‎:

    图片 7

    [zejin] 3301>show global variables like 'gtid_executed';
    +---------------+-------------------------------------------+
    | Variable_name | Value |
    +---------------+-------------------------------------------+
    | gtid_executed | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15 |
    +---------------+-------------------------------------------+
    1 row in set (0.00 sec)
    
    [zejin] 3301>show global variables like 'gtid_purged';
    +---------------+-------------------------------------------+
    | Variable_name | Value |
    +---------------+-------------------------------------------+
    | gtid_purged | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-13 |
    +---------------+-------------------------------------------+
    1 row in set (0.00 sec)
    

    图片 8

     

     

     

    step1:用mysqldump做多少个全备

    mysqldump --all-databases --single-transaction --triggers --routines --events --host=127.0.0.1 --port=3301 --user=root --password=123 > dump3301.sql

     

    开发duDVD301.sql大家能够见见如下语句:

    SET @@GLOBAL.GTID_PURGED='a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15';

    此值即为master3301上gtid_executed的值。

     

    step2:崭新运营贰个新的库3303,注目的在于配备文件中配置enforce_gtid_consistency及gtid_mode=on

    图片 9

    mysqld_safe --defaults-file=/home/mysql/my3303.cnf &
    此时新库3303上的状态应该是这样的:
    
    [(none)] 3303>show global variables like 'gtid_executed';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_executed | |
    +---------------+-------+
    1 row in set (0.01 sec)
    
    [(none)] 3303>show global variables like 'gtid_purged';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_purged | |
    +---------------+-------+
    1 row in set (0.00 sec)
    

    图片 10

     

     

    step3:导入备份文件并查看情状值:

    图片 11

    mysql -uroot -h127.0.0.1 -p123 -P3303 < dump3301.sql
    [(none)] 3303>show global variables like 'gtid_executed';
    +---------------+-------------------------------------------+
    | Variable_name | Value |
    +---------------+-------------------------------------------+
    | gtid_executed | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15 |
    +---------------+-------------------------------------------+
    1 row in set (0.02 sec)
    
    [(none)] 3303>show global variables like 'gtid_purged';
    +---------------+-------------------------------------------+
    | Variable_name | Value |
    +---------------+-------------------------------------------+
    | gtid_purged | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15 |
    +---------------+-------------------------------------------+
    1 row in set (0.00 sec)
    

    图片 12

     

     

     

    step4:做主从change语句

    图片 13

    [zejin] 3303>change master to master_host='192.168.1.240',master_port=3301,master_user='repl',master_password='123',master_auto_position=1;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    
    [zejin] 3303>start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    [zejin] 3303>show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.240
                      Master_User: repl
                      Master_Port: 3301
                    Connect_Retry: 60
                  Master_Log_File: binlog57.000014
              Read_Master_Log_Pos: 194
                   Relay_Log_File: zejin240-relay-bin.000002
                    Relay_Log_Pos: 365
            Relay_Master_Log_File: binlog57.000014
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 194
                  Relay_Log_Space: 575
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 3301
                      Master_UUID: a97983fc-5a29-11e6-9d28-000c29d4dc3f
                 Master_Info_File: /home/mysql/I3303/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    

    图片 14

     

    由来完毕了踏向生龙活虎台新的slave的GTID主从情状。

     

     

    若是大家近来有所黄金时代主带两从的情状:

    master(3301)

    slave(3302)

    slave(3303)

     

    作者们来设想那样大器晚成种分外意况,由于各种原因,有相当大希望主库上业已purge掉了有的binlog,但从库都还未接纳到(如slave停了意气风发段时间,而master已经把某个binlog给purge掉了。卡塔 尔(英语:State of Qatar)

     

    主库近来的情况是:

    图片 15

    [zejin] 3301>show global variables like 'gtid_executed';
    +---------------+-------------------------------------------+
    | Variable_name | Value |
    +---------------+-------------------------------------------+
    | gtid_executed | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-21 |
    +---------------+-------------------------------------------+
    1 row in set (0.00 sec)
    
    [zejin] 3301>show global variables like 'gtid_purged';
    +---------------+-------------------------------------------+
    | Variable_name | Value |
    +---------------+-------------------------------------------+
    | gtid_purged | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-20 |
    +---------------+-------------------------------------------+
    1 row in set (0.00 sec)
    
    [zejin] 3301>select * from t_users;
    +----+------+
    | id | name |
    +----+------+
    | 1 | chen |
    | 2 | ok |
    | 3 | li |
    +----+------+
    3 rows in set (0.00 sec)
    

    图片 16

     

    在从库3303上,我们得以观察如下错误提醒:

    Last_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.'

    图片 17

    [zejin] 3303>show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: 
                      Master_Host: 192.168.1.240
                      Master_User: repl
                      Master_Port: 3301
                    Connect_Retry: 60
                  Master_Log_File: binlog57.000014
              Read_Master_Log_Pos: 457
                   Relay_Log_File: zejin240-relay-bin.000003
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: binlog57.000014
                 Slave_IO_Running: No
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 457
                  Relay_Log_Space: 194
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 1236
                    Last_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: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 3301
                      Master_UUID: a97983fc-5a29-11e6-9d28-000c29d4dc3f
                 Master_Info_File: /home/mysql/I3303/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 160809 17:25:39
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:16
                Executed_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-16
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    
    [zejin] 3303>select * from t_users;
    +----+------+
    | id | name |
    +----+------+
    |  1 | li   |
    |  2 | zhou |
    +----+------+
    2 rows in set (0.00 sec)
    

    图片 18

     

    主旨已经中断,数据也已不后生可畏致。

     

    接下去大家来看哪样复苏:

    出于GTID具有全局唯后生可畏性,那么别的正规的gtid已经被复制到了slave3302上,所以大家能够把3303指向3302,同步达成后再指回master3301(从前提基于3302的binlog还未被purge掉,即存在3303从未有过从master3301采用到的GTID事务卡塔 尔(阿拉伯语:قطر‎

    操作方法如下:

    图片 19

    [zejin] 3303>change master to master_host='192.168.1.240',master_port=3302,master_user='repl',master_password='123',master_auto_position=1;
    
    [zejin] 3303>start slave;
    Query OK, 0 rows affected (0.03 sec)
    
    [zejin] 3303>show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.240
                      Master_User: repl
                      Master_Port: 3302
                    Connect_Retry: 60
                  Master_Log_File: binlog57.000007
              Read_Master_Log_Pos: 1723
                   Relay_Log_File: zejin240-relay-bin.000002
                    Relay_Log_Pos: 1687
            Relay_Master_Log_File: binlog57.000007
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 1723
                  Relay_Log_Space: 1937
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 3302
                      Master_UUID: 5cee6f9f-5ab8-11e6-a081-000c29d4dc3f
                 Master_Info_File: /home/mysql/I3303/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:17-21
                Executed_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-21
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    [zejin] 3303>select * from t_users;
    +----+------+
    | id | name |
    +----+------+
    |  1 | chen |
    |  2 | ok   |
    |  3 | li   |
    +----+------+
    3 rows in set (0.00 sec)
    
    
    数据也已经完全与主的一致了,复制正常后再change到3301master上。
    [zejin] 3303>change master to master_host='192.168.1.240',master_port=3301,master_user='repl',master_password='123',master_auto_position=1;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    
    [zejin] 3303>start slave;
    Query OK, 0 rows affected (0.00 sec)
    

    图片 20

     

    上边这种气象是依照还会有另一个从库已经吸收接纳到了master的全部binlog的事态下,那假设结果只是M-S,也产生了上述的难点,那又该怎么苏醒,大家好似下三种方式:

     

    近些日子Master上情形为:

    图片 21

    [zejin] 3301>show global variables like '%gtid%';
    +----------------------------------+-------------------------------------------+
    | Variable_name                    | Value                                     |
    +----------------------------------+-------------------------------------------+
    
    | gtid_executed                    | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-27 |
    ……
    | gtid_purged                      | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-25 |
    ……
    +----------------------------------+-------------------------------------------+
    8 rows in set (0.00 sec)
    

    图片 22

     

    Slave上景况为:

    图片 23

    [zejin] 3303>show slave status G
    *************************** 1. row ***************************
                   Slave_IO_State: 
                      Master_Host: 192.168.1.240
                      Master_User: repl
                      Master_Port: 3301
                    Connect_Retry: 60
                  Master_Log_File: binlog57.000016
              Read_Master_Log_Pos: 729
                   Relay_Log_File: zejin240-relay-bin.000003
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: binlog57.000016
                 Slave_IO_Running: No
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 729
                  Relay_Log_Space: 194
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 1236
                    Last_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: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 3301
                      Master_UUID: a97983fc-5a29-11e6-9d28-000c29d4dc3f
                 Master_Info_File: /home/mysql/I3303/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 160809 17:54:42
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:22
                Executed_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-22
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    

    图片 24

    和前边同样类型的失实,大家过来的思绪为:

    把slave上的gtid_purged设置为master还平昔不被purge掉的值,最终仰仗第三方豆蔻梢头致性同步工具来做多少的大器晚成致性同步。

     

    大家需求先在slave上做一下reset master来消逝gtid的风姿罗曼蒂克部分音信,直接设置会报如下错误:

    [zejin] 3303>set global GTID_PURGED="a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-26";
    ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
    

     

    对的操作步骤如下(在slave上试行卡塔尔国:

    图片 25

    [zejin] 3303>reset master;
    Query OK, 0 rows affected (0.02 sec)
    
    [zejin] 3303>set global GTID_PURGED="a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-26";
    Query OK, 0 rows affected (0.00 sec)
    
    [zejin] 3303>start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    [zejin] 3303>show slave status G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.240
                      Master_User: repl
                      Master_Port: 3301
                    Connect_Retry: 60
                  Master_Log_File: binlog57.000018
              Read_Master_Log_Pos: 728
                   Relay_Log_File: zejin240-relay-bin.000004
                    Relay_Log_Pos: 718
            Relay_Master_Log_File: binlog57.000018
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 728
                  Relay_Log_Space: 968
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 3301
                      Master_UUID: a97983fc-5a29-11e6-9d28-000c29d4dc3f
                 Master_Info_File: /home/mysql/I3303/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:22:27
                Executed_Gtid_Set: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-27
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    

    图片 26

    自然推行完这一个今后数据是不平等的,那么那时候就能够透过pt-table-checksum和pt-table-sync来做多少的生机勃勃致性恢复生机了。

     

     

    大家还会有另意气风发种办法,这便是重新建立slave,方法如本文最开头的那样新建一个slave,但是在由于当下slave上意气风发度有gtid的部分音讯,所以在恢复生机时得先在slave上reset master,具体操作如下:

    在slave上操作:

    图片 27

    reset master
    source dump3301.sql;
    change master to master_host='192.168.1.240',master_port=3301,master_user='repl',master_password='123',master_auto_position=1;
    start slave;
    show slave statusG
    

    图片 28

     

    到现在完结slave同步分外的过来。

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:GTID环境下恢复从库思路方法,一次事故的回顾

    关键词:

上一篇:没有了

下一篇:开垦进级篇连串