xtrabackup三种备份和还原（一）
写这边博客心情不是太美好（博客已经停更2个多月了，实在是没心情学习新东西。2018我的黑暗年，呵呵）好了，不废话了，本文没有任何原理的部分，我也是刚开始接触xtrabackup这个工具。本文应该是一个系列，包括测试环境安装，配置，测试步骤，形成脚本等等，如果我还有激情，可能会找一些原理的部分好好理解一下。这个工具确实很强大。
一、安装测试环境
1 环境准备
[root@mysql test01]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) [root@mysql test01]# mysql --version mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper # 你没看错，是mysql，不是mariadb
2 安装配置
# 1、安装测试mysql-server 5.7 # clean mariadb env yum remove mariadb-* -y # install mysql-5.7 cd /root/mysql/resource yum install *.rpm # edit config echo 'yes' | cp /etc/my.cnf /etc/my.cnf.bak cat >/etc/my.cnf <<EOF [mysqld] #skip-grant-tables innodb_file_per_table=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid user=mysql binlog_format=ROW max_connections = 4096 bind-address= 0.0.0.0 default_storage_engine=innodb innodb_autoinc_lock_mode=2 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=122M log-bin=mysql-bin server-id=1 slow_query_log = ON slow_query_log_file = /var/log/slow.log long_query_time = 1 EOF systemctl start mysqld systemctl enable mysqld # 2、安装配置xtrabackup工具 yum install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm -y
下载mysql-5.7.23
mkdir /opt/mysql-5.7.23/ -p wget -P /opt/mysql-5.7.23/ https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.23-1.el7.x86_64.rpm wget -P /opt/mysql-5.7.23/ https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.23-1.el7.x86_64.rpm wget -P /opt/mysql-5.7.23/ https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-devel-5.7.23-1.el7.x86_64.rpm wget -P /opt/mysql-5.7.23/ https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.23-1.el7.x86_64.rpm wget -P /opt/mysql-5.7.23/ https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.23-1.el7.x86_64.rpm wget -P /opt/mysql-5.7.23/ https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.23-1.el7.x86_64.rpm
下载xtrabackup工具
mkdir -p /opt/xtrabackup/ wget -P /opt/xtrabackup/ https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.6/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
安装测试 so easy
二、备份和还原吧
我们这里测试6种场景，当然应该不止这6种场景，备份全库和还原全库、备份全库和还原指定库、备份指定库还原指定库、备份全库还原指定表、备份指定库还原指定表、增量备份的一些测试与脚本思考
1 备份全库和还原全库
# 创建测试数据 create database test01 default charset utf8; use test01; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create database test02 default charset utf8; use test02; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); # 1. 完全备份 # 创建存放目录 mkdir -pv /opt/test01 # 进行数据库全备 innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 /opt/test01 # 2. 数据被破坏了 drop database test01; drop database test02; # 3. 还原数据 # 停止数据库 systemctl stop mysqld # prepare innobackupex --apply-log /opt/test01/2018-08-20_18-15-18/ # 保证mysql数据目录为空 mkdir -p /opt/mysql_data mv /var/lib/mysql/* /opt/mysql_data # 还原 innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /opt/test01/2018-08-20_18-15-18/ chown -R mysql.mysql /var/lib/mysql systemctl start mysqld
1.2 增量备份来一波
# 1. 创建测试数据 create database test01 default charset utf8; use test01; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create database test02 default charset utf8; use test02; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); #2. 全量备份 innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp /opt/test01/test_full # 增量备份1 （ 以全备为基准：/opt/test01/test_full ） innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --incremental /opt/test01/inc01 --incremental-basedir=/opt/test01/test_full/ --parallel=2 # 再往 A 里插入数据： insert into A values (201,'aaa'),(202,'bbb'),(203,'ccc'),(326,'zzz'); # 增量备份2：（ 以增量1为基准：/opt/test01/inc01/2018-08-20_11-16-17 ） innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --incremental /opt/test01/inc02 --incremental-basedir=/opt/test01/inc01/2018-08-20_11-16-17/ --parallel=2 # 3. 还原 #增量备份的恢复 #增量备份的恢复需要有3个步骤 # 1、恢复完全备份 # 2、恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数，到最后一次增量备份要去掉--redo-only) # 3、对整体的完全备份进行恢复，回滚未提交的数据 具体步骤: # 准备一个全备 innobackupex --apply-log --redo-only /opt/test01/test_full/ #将增量1应用到完全备份 innobackupex --apply-log --redo-only /opt/test01/test_full/ --incremental-dir=/opt/test01/inc01/2018-08-20_11-16-17/ #将增量2应用到完全备份，注意不加 --redo-only 参数了 innobackupex --apply-log /opt/test01/test_full/ --incremental-dir=/opt/test01/inc02/2018-08-20_11-17-00/ #把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据# innobackupex --apply-log /opt/test01/test_full/ # 模拟测试： drop table A; systemctl stop mysqld # 保证mysql数据目录为空 mv /var/lib/mysql/* /opt/mysql/ innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /opt/test01/test_full/ chown -R mysql:mysql /var/lib/mysql systemctl start mysqld #数据已经恢复 select * from A;
2 备份全库和还原指定库
# 1. 准备测试数据 create database test01 default charset utf8; use test01; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create database test02 default charset utf8; use test02; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); # 1、备份全库 innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp /opt/test01/test_full # 2、破坏数据 drop database test01; drop database test02; # 3. 只还原test01库 innobackupex --apply-log /opt/test01/test_full # 手工删除 systemctl stop mysqld cd /var/lib/mysql # 这里最好不好删除，而是备份。防止出问题 rm -rf test01 test02 ibdata1 # 2).copy，因为是部分备份，不能直接用--copy-back，只能手动来复制需要的库，也要复制ibdata(数据字典） cd /opt/test01/test_full cp -r test01 /var/lib/mysql cp ibdata1 /var/lib/mysql # 3).改权限 chown -R mysql.mysql /var/lib/mysql systemctl start mysqld
3 备份指定库和还原指定库
可能会觉得这个和2有些重复，但是我们想如果我们有一个主数据库（里面有2个库）是1T的数据文件，三个从库分别只有主数据库的一个库。且三个库大小不一致，比如一个1G，一个2G，一个998G，这个很夸张，但是确实有可能存在。这个时候我们只想还原其中一个从数据库的库。就用到这种场景了。
# 1. 准备测试数据 create database test01 default charset utf8; use test01; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create database test02 default charset utf8; use test02; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); # 1、备份 innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp --databases="test01" /opt/test01/test01 # 测试删除库 mysql -uroot -proot1234 # drop database test01; create table C(id int,name varchar(10))engine=innodb; insert into C values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); # 2、还原 innobackupex --apply-log /opt/test01/test01 # 手工删除 systemctl stop mysqld cd /var/lib/mysql # 这里最好不好删除，而是备份。防止出问题 rm -rf test01 ibdata1 # 2).copy，因为是部分备份，不能直接用--copy-back，只能手动来复制需要的库，也要复制ibdata(数据字典） cd /opt/test01/test01 cp -r test01 /var/lib/mysql cp ibdata1 /var/lib/mysql # 3).改权限 chown -R mysql.mysql /var/lib/mysql systemctl start mysqld
3.1 增量来一波
# 1. 增量备份与恢复 # 我们以之前做的全备为基准，在其基础上做增量备份： # 在test01数据库上新建一张表，并插入数据作为增量 mysql -uroot -proot1234 use test01 create table tb2 (id int,name varchar(40)); insert into tb2 values (1,'aaa'),(2,'bbb'),(3,'ccc'),(26,'zzz'); # 全备份 innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp --databases="test01" /opt/test01/test01_full/ # 增量备份1 （ 以全备为基准：/opt/test01/test01_full/ ） innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp --databases="test01" --incremental /opt/test01/test01_inc01/ --incremental-basedir=/opt/test01/test01_full/ --parallel=2 # 再往 tb2 里插入数据： insert into tb2 values (201,'aaa'),(202,'bbb'),(203,'ccc'),(326,'zzz'); # 增量备份2：（ 以增量1为基准：/opt/test01/test01_inc01/ ） innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp --databases="test01" --incremental /opt/test01/test01_inc02/ --incremental-basedir=/opt/test01/test01_inc01/ --parallel=2 #增量备份的恢复 #增量备份的恢复需要有3个步骤 # 1、恢复完全备份 # 2、恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数，到最后一次增量备份要去掉--redo-only) # 3、对整体的完全备份进行恢复，回滚未提交的数据 具体步骤: # 准备一个全备 innobackupex --apply-log --redo-only /opt/test01/test01_full/ #将增量1应用到完全备份 innobackupex --apply-log --redo-only /opt/test01/test01_full/ --incremental-dir=/opt/test01/test01_inc01/ #将增量2应用到完全备份，注意不加 --redo-only 参数了 innobackupex --apply-log /opt/test01/test01_full/ --incremental-dir=/opt/test01/test01_inc02/ #把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据# innobackupex --apply-log /opt/test01/test01_full/ # 模拟测试： drop table tb2; # 手工删除 systemctl stop mysqld cd /var/lib/mysql # 这里最好不好删除，而是备份。防止出问题 rm -rf test01 ibdata1 # 2).copy，因为是部分备份，不能直接用--copy-back，只能手动来复制需要的库，也要复制ibdata(数据字典） cd /opt/test01/test01_full/ cp -r test01 /var/lib/mysql cp ibdata1 /var/lib/mysql # 3).改权限 chown -R mysql.mysql /var/lib/mysql systemctl start mysqld #数据已经恢复 select * from tb2; # 最终恢复到 增量备份2的那个状态
4 指定单表进行备份与还原
# innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp --databases="dba_test.A xtra_test.M" /home/wanstack/xtrabackup/backup02/ # 我们很可能是全量备份，加上增量备份的备份策略，如果仅仅只是恢复某张表从而进行恢复多个数据库全量备份恢复，再加上 N 个增量的恢复， # 上百 G 甚至上 T 的数据，在等数据恢复、拷贝数据库、追同步完成的过程中，估计你后面已经站满了领导/(ㄒoㄒ)/~~ # 本文主要介绍利用 XtraBackup 备份工具来实现单表快速恢复，在紧急数据恢复场景下，用最短的时间来恢复数据，最大程度降低损失。 # 在 MySQL 5.6 之前，对基于 InnoDB 存储引擎的表，即便开启 innodb_file_per_table 选项，也是无法通过复制数据文件来达到在不同实例之间复制表的目的的。 # 从mysql 5.6版本开始，支持可移动表空间（Transportable Tablespace），利用这个功能也可以实现单表的恢复，下面进行从备份中恢复单张innodb表进行演练。 """ 1. 针对InnoDB表恢复 2. 开启了参数innodb_file_per_table 3. 安装工具：mysql-utilities，其中mysqlfrm可以读取表结构。 """ # 1、工具安装 yum install mysql-utilities -y # 2 创建测试数据 create database test01 default charset utf8; use test01; create table A(id int,name varchar(10))engine=innodb; insert into A values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); create table B(id int,name varchar(10))engine=innodb; insert into B values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); # 3 进行xtrabackup备份操作： innobackupex --defaults-file='/etc/my.cnf' --user='root' --password='root1234' --no-timestamp /opt/test01/test01_full/ # apply-log innobackupex --defaults-file='/etc/my.cnf' --user='root' --password='root1234' --apply-log /opt/test01/test01_full/ # 进行完整备份后，我们继续往测试表A表里插入数据，尽量模拟线上环境： insert into A values(10,'A'),(20,'B'),(30,'C'),(40,'D'),(50,'E'); """ # 插入前 mysql> select * from A; +------+------+ | id | name | +------+------+ | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | +------+------+ 10 rows in set (0.00 sec) mysql> insert into A values(10,'A'),(20,'B'),(30,'C'),(40,'D'),(50,'E'); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: test01 Query OK, 5 rows affected (0.06 sec) Records: 5 Duplicates: 0 Warnings: 0 # 插入后 mysql> select * from A; +------+------+ | id | name | +------+------+ | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | | 10 | A | | 20 | B | | 30 | C | | 40 | D | | 50 | E | +------+------+ 15 rows in set (0.00 sec) """ # xtrabackup备份里只有5条数据，备份后的数据，我们一会使用binlog来进行恢复。 # 进行误操操作，把表drop了： drop table A; # 使用mysqlfrm从备份中读取表结构： [root@mysql test01]# mysqlfrm --diagnostic /opt/test01/test01_full/test01/A.frm # WARNING: Cannot generate character set or collation names without the --server option. # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct. # Reading .frm file for /opt/test01/test01_full/test01/A.frm: # The .frm file is a TABLE. # CREATE TABLE Statement: CREATE TABLE `test01`.`A` ( `id` int(11) DEFAULT NULL, `name` varchar(30) DEFAULT NULL ) ENGINE=InnoDB; #...done. # 登录数据库进行建表： mysql -uroot -proot1234 CREATE TABLE `test01`.`A` ( `id` int(11) DEFAULT NULL, `name` varchar(30) DEFAULT NULL ) ENGINE=InnoDB; # 加一个写锁，确保安全: lock tables A write; #丢弃表空间： alter table A discard tablespace; # 从备份中拷贝ibd文件，并且修改权限: cp /opt/test01/test01_full/test01/A.ibd /var/lib/mysql/test01/ chown -R mysql:mysql /var/lib/mysql/test01/ # 载入表空间： alter table A import tablespace; show warnings; select * from A; unlock tables; """ mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test01/A.cfg', will attempt to import without schema verification | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from A; +------+------+ | id | name | +------+------+ | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | +------+------+ 10 rows in set (0.01 sec) """
你以为还原表完了吗？没有哦这个待续吧。
5 指定库备份还原续
# 很抱歉，如果您按照上面的步骤还原是没办法还原存储过程，函数等等信息的。需要使用如下方式 # 1、备份 innobackupex --defaults-file=/etc/my.cnf --user=root --password=root1234 --no-timestamp --databases="test01" /opt/test01/test01 # 测试删除库 mysql -uroot -proot1234
drop database test01; # 2、还原 innobackupex --apply-log /opt/test01/test01 mv /var/lib/mysql/ib_logfile* ../mysql_bak02 mv /var/lib/mysql/ibdata1 ../mysql_bak02 mv /var/lib/mysql/test01 ../mysql_bak02 mv /var/lib/mysql/* ../mysql_bak innobackupex --defaults-file=/etc/my.cnf --copy-back /opt/test01/test01 mv ../mysql_bak ../mysql chown -R mysql:mysql /var/lib/mysql service mysql start
生活不会突变，你要做的只是耐心和积累。人这一辈子没法做太多的事情，所以每一件都要做得精彩绝伦。你的时间有限，做喜欢的事情会令人愉悦，所以跟随自己的本心。
