使用SQL Thread来恢复MySQL数据库

1. 介绍

MySQL如果需要恢复到指定时间点,一般是使用xtrabackup + mysqlbinlog,具体参考利用xtrabackup和mysqlbinlog完全恢复数据库
但是使用mysqlbinlog来增量恢复binlog时,可能会碰到以下问题:

  • Max_allowed_packet问题
  • 恼人的Blob/Binary/text字段问题
  • 特殊字符的转义问题
  • 没有”断点恢复”:执行出错后,没有足够的报错,也很难从失败的地方继续恢复
  • 所以这里提出另一种恢复思路,使用slave 的SQL Thread来进行binlog恢复,因为slave的sql thread一般是读取relaylog,所以这里需要将binlog处理成relaylog。
    其实binlog与relaylog,基本一样,SQL Thread完全能够一样读取

    恢复思路:直接将要恢复的binlog拷贝到relay log目录,并修改slave-info相关的文件,让MySQL把binlog当做relay log来执行
    注意:MySQL默认应用完relay-log后就自动删除relay-log,如果我们不复制备份的binlog到relay目录,而是直接使用备份的binlog来恢复,需要配置
    relay_log_purge=0
    建议把备份的binlog复制到relay-log指定的目录进行恢复,这里使用的relaylog目录为:/data/logbin

    2. xtrabackup恢复备份

    这一步比较简单,就不再说明了,具体可以参考前面的文章。
    这里稍微说明一下我这里xtrabacup的备份环境:
    单独提供一台mysql备份服务器(RHEL操作系统上挂个大存储,做个大的文件系统,简单吧),MySQL生产库上使用xtrabacup进行全备份并压缩传输到备份服务器上,生产库上不落地。
    效果还不错,35g左右的库,备份压缩后,差不多5g,20分钟左右备份完成。
    所以这里恢复,直接在备份服务器上进行的异机恢复,使用的my.cnf如下:

    [root@test2 ~]# cat /etc/my.cnf
    [mysqld]
    port                                  = 3306
    socket                                = /data/mysql/mysql.sock
    datadir                               = /data/mysql
    sql_mode                              = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    lower_case_table_names                = 1
    character-set-server                  = utf8
    init_connect                          = 'SET NAMES utf8'
    max_connections                       = 800
    query_cache_size                      = 128M
    query_cache_type                      = 1
    tmp_table_size                        = 128M
    #innodb_sort_buffer_size              = 32M
    join_buffer_size                      = 32M
    innodb_thread_concurrency             = 0
    innodb_buffer_pool_size               = 1024M
    innodb_file_per_table                 = true
    innodb_log_file_size                  = 100M
    innodb_log_buffer_size                = 16M
    innodb_buffer_pool_load_at_startup    = true
    #innodb_buffer_pool_dump_at_shutdown  = true
    innodb_read_io_threads                = 10 
    innodb_write_io_threads               = 10
    innodb_io_capacity                    = 1000
    innodb_log_files_in_group             = 4
    skip_name_resolve                     = 1
    innodb_purge_threads                  = 32
    server_id                             =1
    log_timestamps                        = SYSTEM
    gtid_mode                             = ON
    enforce-gtid-consistency              = ON
    
    [mysql]
    prompt                                = "\u@\h[\d]> "
    no-auto-rehash
    default-character-set                 = utf8
    no-auto-rehash
    
    [client]
    socket                                = /data/mysql/mysql.sock
    

    解压备份

    [mysql@test2 backup]$ tar xvfz test-201704251555.tar.gz -C /data/mysql
    

    应用日志

    [mysql@test2 backup]$ innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --use-memory=2G --apply-log /data/mysql
    

    就可以直接启动MySQL了,这里是异机恢复,–copy-back就不需要了

    [root@test2 ~]# service mysql.server start
    Starting MySQL.Logging to '/data/mysql/test2.err'.
    .                                                          [  OK  ]
    

    3. 生产库产生了增量数据

    root@localhost[(none)]> use test;
    Database changed
    
    root@localhost[test]> select * from books;
    +-----+------+
    | id  | name |
    +-----+------+
    |   1 | aa   |
    |   2 | bb   |
    |   3 | cc   |
    |   8 | ttt  |
    |  10 | cc   |
    |  11 | dd   |
    |  12 | ee   |
    |  13 | hh   |
    |  14 | e    |
    |  15 | f    |
    | 101 | ttt  |
    +-----+------+
    11 rows in set (0.06 sec)
    
    root@localhost[test]> delete from books;
    Query OK, 11 rows affected (0.04 sec)
    
    root@localhost[test]> insert into books values (1, 'a');
    Query OK, 1 row affected (0.03 sec)
    
    root@localhost[test]> insert into books values (2, 'b');
    Query OK, 1 row affected (0.05 sec)
    

    这次就使用slave 的 sql_thread方式来把生产上的binlog应用到备份机器上的恢复库里

    4. 使用slave SQL Thread恢复过程

    4.1 修改relaylog参数

    在/etc/my.cnf中添加或修改如下参数:

    relay_log                             = /data/logbin/master-bin
    relay-log-info-file                   = /data/logbin/relay-log.info
    relay-log-index                       = /data/logbin/relay-log.index
    relay_log_info_repository             = FILE
    relay_log_purge                       = 0
    skip-slave-start
    server_id = 2
    

    注意:这里需要修改service_id为非原始service_id,因为MySQL slave无法应用自已产生的binlog文件
    修改参数后,需要重启Server,让relay参数生效

    # service mysql.server restart
    

    4.2 准备slave进程

    root@localhost[(none)]> CHANGE MASTER TO
        -> MASTER_HOST='localhost',
        -> MASTER_USER='repl_user',
        -> MASTER_PASSWORD='oracle',
        -> MASTER_AUTO_POSITION = 0;
    Query OK, 0 rows affected, 2 warnings (0.08 sec)
    

    因为恢复只启用sql_thread,所以io_thread用到的参数不需要配置正确,随便给就可以了。创建完slave后,后面一步修改relaylog位置非常重要。
    执行完change master,才会生成relay-log.info和relay-log.index

    4.3 复制要恢复的binlog到指定目录

    通过xtrabackup_binlog_info得知,后续要恢复的binlog,只有生产库上当前正在写的binlog需要恢复,把这个binlog复制到备份机上的指定目录

    [root@test2 mysql]# cat xtrabackup_binlog_info
    master-bin.000021       194     05d54147-c68e-11e6-9c5a-001a4a1ee9ff:1-119
    [mysql@test1 logbin]$ scp master-bin.000021 test2:/data/logbin
    

    4.4 准备relay-log.index文件

    在备份机的恢复库上,relay-log.index里是没有要恢复的binlog信息,需要手工添加

    [mysql@test2 backup]$ cd /data/logbin
    [mysql@test2 logbin]$ ls -l
    total 16
    -rw-r-----. 1 mysql mysql  154 Apr 26 15:10 master-bin.000001
    -rw-r-----. 1 mysql mysql 1058 Apr 26 15:11 master-bin.000021
    -rw-r-----. 1 mysql mysql   31 Apr 26 15:10 relay-log.index
    -rw-r-----. 1 mysql mysql   45 Apr 26 15:10 relay-log.info
    [mysql@test2 logbin]$ echo "/data/logbin/master-bin.000021" >>relay-log.index
    

    4.5 重启MySQL Server

    # service mysql.server restart
    

    否则change master会报错

    ERROR 1380 (HY000): Failed initializing relay log position: Could not find target log file mentioned in relay log info in the index file '/data/logbin/relay-log.index' during relay log initialization
    

    4.6 修改replay应用位置

    root@localhost[(none)]> CHANGE MASTER TO
        -> RELAY_LOG_FILE='master-bin.000021',
        -> RELAY_LOG_POS=194;
    Query OK, 0 rows affected (0.01 sec)
    

    4.7 启动SQL Thread

    修改完成,就可以启动sql_thread了

    mysql> start slave sql_thread;
    

    如果需要应用到指定时间点,可以如下:

    root@localhost[(none)]> start slave sql_thread until
        -> relay_log_file='master-bin.000021',
        -> relay_log_pos=766;
    Query OK, 0 rows affected (0.01 sec)
    
    root@localhost[(none)]> select * from test.books;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    +----+------+
    1 row in set (0.00 sec)
    root@localhost[(none)]> start slave sql_thread until relay_log_file='master-bin.000021', relay_log_pos=1027;
    Query OK, 0 rows affected (0.01 sec)
    
    root@localhost[(none)]> select * from test.books;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    +----+------+
    2 rows in set (0.00 sec)
    

    这些位置,就需要使用mysqlbinlog工具来查看了

    关于紫砂壶

    感悟技术人生
    此条目发表在MySQL分类目录,贴了标签。将固定链接加入收藏夹。