XtraBackup搭建Mysql物理备份

发表于 更新于 分类于

环境

  • Linux CentOS6 操作系统
  • MySQL 5.6
  • XtraBackup 2.4.8

XtraBackup简介

Percona XtraBackup是一个免费开源数据备份工具，支持在线无锁表同步复制和可并行高效率的安全备份恢复机制，相比mysqldump来说确实让人眼前一亮。

下载mysql安装包

在官网安装包。

安装

安装依赖包
1
2
3
4
5
# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
# rpm -ivh MySQL-shared-compat-5.6.38-1.el6.x86_64.rpm
# rpm -ivh perl-DBI-1.609-4.el6.x86_64.rpm 
# rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86_64.rpm
# rpm -ivh rsync-3.0.6-12.el6.x86_64.rpm
安装
1
# rpm -ivh percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm
全量备份
1
2
3
4
# innobackupex --user=root --password=123456 /home/databackup
171128 14:51:16 [00]        ...done
xtrabackup: Transaction log of lsn (1626027) to (1626027) was copied.
171128 14:51:17 completed OK!

备份数据存放在/home/databackup/下面，innobackupex会自动创建一个文件夹，是当前系统的时间戳。

增量备份

在进行增量备份时，首先要进行一次全量备份，第一次增量备份是基于全备的，之后的增量备份是基于上一次的增量备份，以此类推。

  1. 全量备份
  2. 在test库中创建表并增量备份
    1
    2
    3
    4
    CREATE TABLE test(  
      `id` INT NOT NULL,
      PRIMARY KEY (`id`)
    );
1
2
3
4
# innobackupex --user=root --password=123456 --incremental /home/databackup/inc/ --incremental-basedir=/home/databackup/full/2017-11-28_16-08-17/
171128 15:04:22 [00]        ...done
xtrabackup: Transaction log of lsn (1626027) to (1626027) was copied.
171128 15:04:22 completed OK!
  1. 添加数据并增量备份
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> insert test values(1);
    Query OK, 1 row affected (0.01 sec)

    mysql> select * from test;
    +----+
    | id |
    +----+
    |  1 |
    +----+
    1 row in set (0.00 sec)
1
2
3
4
# innobackupex --user=root --password=123456 --incremental /home/databackup/inc/ --incremental-basedir=/home/databackup/inc/2017-11-28_16-18-29/
171128 15:04:22 [00]        ...done
xtrabackup: Transaction log of lsn (1626027) to (1626027) was copied.
171128 15:04:22 completed OK!
全量恢复

  1. 删除数据库(生产不要这么搞)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # drop database test;
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | logs               |
    | mysql              |
    | performance_schema |
    +--------------------+
    4 rows in set (0.00 sec)

  2. 恢复全备
    恢复备份到mysql的数据文件目录，这一过程要先关闭mysql数据库，重命名或者删除原数据文件目录都可以，再创建一个新的数据文件目录，将备份数据复制到新的数据文件目录下，赋权，修改权限，启动数据库。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # innobackupex --apply-log /opt/databackup/full/2017-11-28_14-57-31/ 
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 1626152
    171128 15:24:34 completed OK!

    #innobackupex --defaults-file=/var/my.cnf --copy-back --rsync /home/databackup/full/2017-11-28_14-57-31/
    171128 15:36:01 [01]        ...done
    171128 15:36:01 [01] Copying ./test/db.opt to /home/data/mysql/test/db.opt
    171128 15:36:01 [01]        ...done
    171128 15:36:01 completed OK!

  3. 验证

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | logs               |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.00 sec)
增量恢复

增量备份的恢复大体为3个步骤

  1. 恢复完全备份
    注意：这里一定要加–redo-only参数，该参数的意思是只应用xtrabackup日志中已提交的事务数据，不回滚还未提交的数据

    1
    2
    3
    4
    5
    # innobackupex --apply-log --redo-only /home/databackup/full/2017-11-28_16-08-17/
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 1626171
    InnoDB: Number of pools: 1
    171128 16:24:41 completed OK!

  2. 恢复增量备份到完全备份
    注意：开始恢复的增量备份要添加–redo-only参数，到最后一次增量备份去掉–redo-only参数
    第1次：

    1
    2
    3
    4
    5
    6
    # innobackupex --apply-log --redo-only /home/databackup/full/2017-11-28_16-08-17/ --incremental-dir=/home/databackup/inc/2017-11-28_16-18-29/
    171128 16:28:00 [00] Copying /home/databackup/inc/2017-11-28_16-18-29//xtrabackup_binlog_info to ./xtrabackup_binlog_info
    171128 16:28:00 [00]        ...done
    171128 16:28:00 [00] Copying /home/databackup/inc/2017-11-28_16-18-29//xtrabackup_info to ./xtrabackup_info
    171128 16:28:00 [00]        ...done
    171128 16:28:00 completed OK!

第…次，同第1次。

最后一次

1
2
3
4
# innobackupex --apply-log  /home/databackup/full/2017-11-28_16-08-17/ --incremental-dir=/home/databackup/inc/2017-11-28_16-20-46/
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1636904
171128 16:32:58 completed OK!
  1. 对整体的完全备份进行恢复，回滚那些未提交的数据
    1
    2
    3
    4
    5
    # innobackupex --apply-log /home/databackup/full/2017-11-28_16-08-17/
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 1636942
    171128 16:36:41 completed OK!

把恢复完的备份复制到数据库目录文件中，和全量备份恢复一样。

1
2
3
4
5
6
# innobackupex --defaults-file=/var/my.cnf --copy-back --rsync /home/databackup/full/2017-11-28_14-57-31/
171128 16:42:34 [01] Copying ./test/test.ibd to /home/data/mysql/test/test.ibd
171128 16:42:34 [01]        ...done
171128 16:42:34 [01] Copying ./test/db.opt to /home/data/mysql/test/db.opt
171128 16:42:34 [01]        ...done
171128 16:42:34 completed OK!
1
2
3
4
5
6
7
mysql> select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

结语

真实场景还需仔细学习对应功能，写成自动化的脚本去运行。

参考资料

1.xtrabackup 详解
2.XtraBackup不停机不锁表搭建MySQL主从同步实践