• xtrabackup全备+binlog模拟slave恢复到某一时间点-启用GTID


    背景说明:3306端口数据库,做一次xtrabackup全备之后,删除表,模拟恢复到 3308 端口数据库。
    采用模拟slave线程恢复的方式,速度更快。
    当恢复的场景是从全备恢复某一张表时,也可以使用复制过滤功能,只应用对应表的binlog,不用全部binlog都恢复。
    前提:数据库启用了GTID,即,my.cnf需要有如下参数:
    master_info_repository = TABLE
    relay_log_info_repository = TABLE
    gtid_mode = on 
    log-bin = mysql-bin 
    log-slave-updates = 1 
    enforce-gtid-consistency = 1
    
    先做一次完整备份:
    innobackupex --defaults-file=/etc/my.cnf --user root --password Jimstars /data/mysqlbak
    
    执行完命令后,/data/mysqlbak 目录下会生成目录:2020-08-18_09-45-12
    
    多切换几次 binlog日志
    mysql -uroot -pJimstars -e "flush logs;"
    
    插入测试数据
    mysql -uroot -pJimstars scott -e "create table test_100(id int,name varchar(200));"
    mysql -uroot -pJimstars scott -e "insert into test_100 values (20001,'full_bak');"
    
    
    多切换几次 binlog日志
    mysql -uroot -pJimstars -e "flush logs;"
    
    再插入测试数据
    mysql -uroot -pJimstars scott -e "insert into test_100 values (20002,'full_bak2');"
    
    多切换几次 binlog日志
    mysql -uroot -pJimstars -e "flush logs;"
    
    再插入测试数据
    mysql -uroot -pJimstars scott -e "insert into test_100 values (20003,'full_bak3');"
    
    多切换几次 binlog日志
    mysql -uroot -pJimstars -e "flush logs;"
    
    
    假设在这里误删除了表
    root@localhost:mysql.sock/scott> drop table test_100;
    Query OK, 0 rows affected (0.02 sec)
    
    root@localhost:mysql.sock/scott> show master status;
    +---------------------------+----------+--------------+------------------+------------------------------------------+
    | File                      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
    +---------------------------+----------+--------------+------------------+------------------------------------------+
    | VM_0_15_centos-bin.000008 |      382 |              |                  | 5c5286d8-a94c-11ea-9333-52540088b727:1-5 |
    +---------------------------+----------+--------------+------------------+------------------------------------------+
    1 row in set (0.00 sec)
    
    
    
    多切换几次 binlog日志
    mysql -uroot -pJimstars -e "flush logs;"
    
    
    创建另外的测试表,作为对比参考,如果是按照指定点恢复,下面创建的表不应该被恢复出来
    mysql -uroot -pJimstars scott -e "create table test_200 like t1;"
    mysql -uroot -pJimstars scott -e "insert into test_200 select * from t1;"
    
    
    解析binlog,找到误删除操作对应的 GTID :
    mysqlbinlog --base64-output=DECODE-ROWS -vv -d scott /data/mysql/VM_0_15_centos-bin.000008 > /tmp/scott.log
    
    
    
    [root@VM_0_15_centos 2020-08-18_09-45-12]#  grep -C 30 test_100 /tmp/scott.log 
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #200818  9:46:20 server id 2003306  end_log_pos 123 CRC32 0xc766ec26     Start: binlog v 4, server v 5.7.22-log created 200818  9:46:20
    # Warning: this binlog is either in use or was not closed properly.
    # at 123
    #200818  9:46:20 server id 2003306  end_log_pos 194 CRC32 0xadd61034     Previous-GTIDs
    # 5c5286d8-a94c-11ea-9333-52540088b727:1-4
    # at 194
    #200818  9:46:34 server id 2003306  end_log_pos 259 CRC32 0x890daa29     GTID    last_committed=0    sequence_number=1    rbr_only=no
    SET @@SESSION.GTID_NEXT= '5c5286d8-a94c-11ea-9333-52540088b727:5'/*!*/;
    # at 259
    #200818  9:46:34 server id 2003306  end_log_pos 382 CRC32 0xcc983992     Query    thread_id=19    exec_time=0    error_code=0
    use `scott`/*!*/;
    SET TIMESTAMP=1597715194/*!*/;
    SET @@session.pseudo_thread_id=19/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=268435456/*!*/;
    SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
    /*!C utf8mb4 *//*!*/;
    SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    DROP TABLE `test_100` /* generated by server */
    /*!*/;
    # at 382
    #200818  9:49:45 server id 2003306  end_log_pos 447 CRC32 0xd66b6450     GTID    last_committed=1    sequence_number=2    rbr_only=no
    SET @@SESSION.GTID_NEXT= '5c5286d8-a94c-11ea-9333-52540088b727:6'/*!*/;
    # at 447
    #200818  9:49:45 server id 2003306  end_log_pos 544 CRC32 0xbacbb61a     Query    thread_id=21    exec_time=0    error_code=0
    SET TIMESTAMP=1597715385/*!*/;
    create table test_200 like t1
    /*!*/;
    # at 544
    #200818  9:49:45 server id 2003306  end_log_pos 609 CRC32 0xef101783     GTID    last_committed=2    sequence_number=3    rbr_only=yes
    /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
    SET @@SESSION.GTID_NEXT= '5c5286d8-a94c-11ea-9333-52540088b727:7'/*!*/;
    # at 609
    #200818  9:49:45 server id 2003306  end_log_pos 682 CRC32 0x8360492f     Query    thread_id=22    exec_time=0    error_code=0
    SET TIMESTAMP=1597715385/*!*/;
    BEGIN
    /*!*/;
    # at 682
    #200818  9:49:45 server id 2003306  end_log_pos 739 CRC32 0xac7ea76e     Table_map: `scott`.`test_200` mapped to number 1078
    # at 739
    #200818  9:49:45 server id 2003306  end_log_pos 8951 CRC32 0x20e90ae5     Write_rows: table id 1078
    # at 8951
    #200818  9:49:45 server id 2003306  end_log_pos 17163 CRC32 0x36bec563     Write_rows: table id 1078
    # at 17163
    #200818  9:49:45 server id 2003306  end_log_pos 25375 CRC32 0x4b45b983     Write_rows: table id 1078
    # at 25375
    #200818  9:49:45 server id 2003306  end_log_pos 27005 CRC32 0x4a32b241     Write_rows: table id 1078 flags: STMT_END_F
    ### INSERT INTO `scott`.`test_200`
    ### SET
    
    
     
    从上述日志,可以得出我们要找出的点为: 5c5286d8-a94c-11ea-9333-52540088b727:5
    
    
    备份文件xtrabackup_binlog_info中找到备份对应的binlog位置
    [root@VM_0_15_centos 2020-08-18_09-45-12]# cat xtrabackup_binlog_info 
    VM_0_15_centos-bin.000001    154    
     
     
    恢复全备,这里采用恢复本地端口 3306的数据库 恢复到 3308 
    innobackupex --apply-log /data/mysqlbak/2020-08-18_09-45-12
    innobackupex --defaults-file=/etc/my_3308.cnf --copy-back /data/mysqlbak/2020-08-18_09-45-12
    chown -R mysql.mysql /data/mysql_3308 
    
    
    拷贝binlog 到 /data/mysql_3308 
    cp /data/mysql/VM_0_15_centos-bin.0* /data/mysql_3308 
    chown -R mysql.mysql /data/mysql_3308
    
    
    将binlog改名为relay log 
    rename "bin" "relay-bin" VM_0_15_centos-bin.0*
    ls ./VM_0_15_centos-relay-bin.0* > mysql-relay.index
    chown -R mysql.mysql /data/mysql_3308
    
    
    [mysqld] 中设置参数 
    relay_log = /data/mysql_3308/mysql-relay.index
    
    # server_id 必须设置和原来实例的 server_id 不一样,不然不会应用 binlog 
    server_id=2003308
    # relay_log_recovery=1时,relay log 会在MySQL重启、复制线程启动时被清除
    relay_log_recovery=0
    # 开启并行复制
    slave-parallel-type=LOGICAL_CLOCK 
    # 设置work线程数量 
    slave_parallel_workers=8 
    # 设置redo log异步刷盘,每秒1次
    innodb_flush_log_at_trx_commit=0 
    # 设置binlog刷盘策略
    sync_binlog = 0
    
     
    
    启动MySQL。
    mysqld --defaults-file=/etc/my_3308.cnf &
    
    
    查看 xtrabackup_binlog_info 里面记录的GTID 信息,恢复全备后的新库中执行:
    mysql> reset master;
    mysql> set global gtid_purged='5c5286d8-5863-a94c-96e7-02000aba3dad:1-3322473';
    
    
    建立复制通道:
    mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='VM_0_15_centos-relay-bin.000001',RELAY_LOG_POS=154;
    
    
    确认slave sql thread的起始位置和设置的一样
    mysql> select * from mysql.slave_relay_log_info;
    
    
    启动复制前,如果只需要恢复指定表,可以使用复制过滤功能,加快binlog回放速度:
    mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('scott.test_100');
    
    
    启动复制线程,到误删除那个事务停止:
    mysql> START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = '5c5286d8-a94c-11ea-9333-52540088b727:5';
    
    
    如果是恢复全部binlog,则
    mysql> start slave sql_thread;
    
    
    恢复到生产环境
    binlog应用结束后可以从error log中看到提示信息:
    2020-08-18T02:04:03.842356Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='1.1.1.1', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
    2020-08-18T02:04:16.643065Z 3 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'FIRST' at position 0, relay log '/data/mysql_3308/VM_0_15_centos-relay-bin.000001' position: 154
    2020-08-18T02:04:16.718389Z 3 [Note] Slave SQL thread stopped because it reached UNTIL SQL_BEFORE_GTIDS 5c5286d8-a94c-11ea-9333-52540088b727:5
    
    
    接下来把误操作的表导出,再导入到生产环境。
     
     
  • 相关阅读:
    [引用]SQLServer占CPU100%
    负能量程序员杂谈(2)- 管理中的情和义
    负能量程序员杂谈(1)-世界上最单纯的职业:程序员
    FLV文件格式官方规范详解
    rtmp官方标准规范详细解析
    万恶的KPI、新兴的OKR及让人纠结的程序员考核
    管理点滴(一)
    选拨管理者的一个必要条件
    团队管理的简单总结:少即是多,体力透支,负能量管理,自我进化团队,沟通
    我的2015计划,目标
  • 原文地址:https://www.cnblogs.com/liang545621/p/13519023.html
Copyright © 2020-2023  润新知