MYSQL的备份与恢复--物理备份xtrabackup

我叫张贺，贪财好色。一名合格的LINUX运维工程师，专注于LINUX的学习和研究，曾负责某中型企业的网站运维工作，爱好佛学和跑步。
个人博客：传送阵
笔者微信：zhanghe15069028807

目录

• 1、数据库完整备份与恢复
  • （1）环境准备
  • （2）完全备份恢复流程
• 2、数据库增量备份与恢复
  • （1）增量和差异概述
  • （2）增量备份和恢复
• 3、数据库差异备份与恢复
• 4、简单命令进行物理备份
• 5、实际生产环境

percona-xtrabackup开源免费支持mysql热备的软件，支持增量、差异备份。

yum -y install https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-8/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.8-1.el7.x86_64.rpm
[root@mysql02 ~]# rpm -ql  percona-xtrabackup-80.x86_64
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-80-8.0.8
/usr/share/doc/percona-xtrabackup-80-8.0.8/LICENSE
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

1、数据库完整备份与恢复

系统版本	MYSQL版本	安装方式
centos7	5.7	二进制安装

（1）环境准备

[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir=/soft/mysql
datadir=/soft/mysql/data
default_password_lifetime=0
socket=/tmp/mysql.sock
[root@mysql02 ~]# /etc/init.d/mysqld restart
[root@mysql02 ~]# mysql -uroot -pcba-123 -e "create database test1;"
[root@mysql02 ~]# mysql -uroot -pcba-123 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |   #注意这里
+--------------------+
[root@mysql02 ~]#yum -y install  
https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm
[root@mysql02 ~]# innobackupex --user=root --password="cba-123" -S /tmp/mysql.sock /xtrabackup
[root@mysql02 ~]# ls /xtrabackup/
2019-11-29_08-53-04

（2）完全备份恢复流程

[root@mysql02 ~]# /etc/init.d/mysqld stop
[root@mysql02 ~]# rm -rf /soft/mysql/data/*
[root@mysql02 ~]# innobackupex --apply-log /xtrabackup/2019-11-29_08-53-04/ #重演
[root@mysql02 ~]# innobackupex --copy-back /xtrabackup/2019-11-29_08-53-04/ #恢复
[root@mysql02 ~]# chown -R mysql:mysql /soft/mysql/data						 #恢复后加上权限
[root@mysql02 ~]# /etc/init.d/mysqld start
[root@mysql02 ~]# mysql -pcba-123 -e "show databases;"  #test1库回来了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+

2、数据库增量备份与恢复

在做增量或差异备份恢复的项目之前，我们一定要先明确增量备份与差异备份之间的区别，类比来说。

（1）增量和差异概述

无论是增量还是差异备份，基础都是全量备份

我们要先上幼儿园，小学、然后上初中，再然后是高中，最后是大学。中学是在小学的基础上，高中是在初中的基础上，大学是在高中的基础上，当前的“事务”依赖于前一个阶段的“事务”，此谓增量。假设说我们现在失忆了，我们要先恢复幼儿园（全量）的记忆，然后小学，初中，再然后高中，最后才是大学，顺序非常重要，如果顺序错乱了，那么记忆之间的衔接就会错乱。

差异备份的思路就比较诡异了，我们上述的增量备份依赖于前一阶段的备份，而差异备份对比的并不是前一阶段的备份，对比的是第一次的全备。第一次是全备，第二备份的时候时会与全备做一个对比，只备份差异的部分，第三次备份的时候同样还会与全备做对比，只备份差异的部分，依次类推。

用脚想一下就可以做出这样的结论，差异备份与增量备份相比要备份的数据量更大，但是好处也很明显，恢复的时候只需要全备和最近的一次差异备份，操作相比于增量备份简单了。

（2）增量备份和恢复

第一步：准备数据

//周三的数据
mysql> create database bgxdb;
mysql> use bgxdb;
mysql> create table t1(id int,name varchar(20));
mysql> insert into t1 values (3,'day3');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
+------+------

第二步：使用物理全备

[root@mysql02 ~]# yum -y install  https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm

[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir=/soft/mysql
datadir=/soft/mysql/data
default_password_lifetime=0
socket=/tmp/mysql.sock
server-id=241
log-bin=/log/mysql/bin_log
[root@mysql02 ~]# mkdir /log/mysql -p ; touch /log/mysql/bin_log ; chown -R mysql:mysql /log/mysql/
[root@mysql02 ~]# mkdir /xtrabackup ; chown -R mysql:mysql /xtrabackup
[root@mysql02 ~]# /etc/init.d/mysqld restart
[root@mysql02 ~]# innobackupex --username=root --password=cba-123 -S /tmp/mysql.sock /xtrabackup/
[root@mysql02 ~]# ls /xtrabackup/
2019-11-29_09-47-55

第三步：周四，全备完成之后继续插入数据

[root@mysql02 ~]# date -s '2018-11-30'
[root@mysql02 ~]# mysql -pcba-123
mysql> insert into bgxdb.t1 values (4,'day4');
mysql> select * from bgxdb.t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
+------+------+
//下面的操作关键了，是增量备份
[root@mysql02 ~]# innobackupex --user=root --password='cba-123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-11-29_09-47-55 -S /tmp/mysql.sock

上面的这个操作需要解释一下，"incremental"是增量的意思。上述的命令的含义就是使用增量备份，备份到/xtrabackup这个目录里面，基于周三的全备做增量，最后指明了套接字。

第四步：周五，继续插入数据

[root@mysql02 ~]# date -s '2019-12-01'
[root@mysql02 ~]# mysql -pcba-123
mysql> insert into bgxdb.t1 values (5,'da');
mysql> select * from bgxdb.t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
|    5 | da   |
+------+------+
//基于周四的基础上进行增备
[root@mysql02 ~]# innobackupex --user=root --password='cba-123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2018-11-30_00-06-20 -S /tmp/mysql.sock
[root@mysql02 ~]# cat /xtrabackup/2019-12-01_00-07-51/xtrabackup_binlog_info 
bin_log.000001	668

第五步：模拟故障

[root@mysql02 ~]# /etc/init.d/mysqld stop    
[root@mysql02 ~]# rm -rf /soft/mysql/data/*

第六步：依次重演

//恢复周三的全备数据
[root@mysql02 ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-11-29_09-47-55/
//恢复周三的增量数据
[root@mysql02 ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-11-29_09-47-55 --incremental-dir=/xtrabackup/2018-11-30_00-06-20
//恢复周五的增量数据
[root@mysql02 ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-11-29_09-47-55 --incremental-dir=/xtrabackup/2019-12-01_00-07-51

注意，文件的顺序不要搞错。

第七步：执行回滚

//恢复对应数据
[root@mysql02 ~]# innobackupex --copy-back /xtrabackup/'2019-11-29_09-47-55'
[root@mysql02 ~]# chown -R mysql:mysql /soft/mysql/data
[root@mysql02 ~]# /etc/init.d/mysqld restart
[root@mysql02 ~]# mysql -pcba-123 -e "select * from bgxdb.t1;"
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
|    5 | da   |
+------+------+

3、数据库差异备份与恢复

系统版本	MYSQL版本	安装方式
centos7	MYSQL5.7	YUM安装

第一步：打开二进制日志并准备周三的数据

[root@mysql01 ~]# cat /etc/my.cnf
[mysqld]
datadir=/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
server-id = 240
log-bin=/log/mysql/bin_log
[root@mysql01 ~]# mkdir /log/mysql -p ; touch /log/mysql/bin_log ; chown -R mysql:mysql /log/mysql
[root@mysql01 ~]# systemctl restart mysqld

[root@mysql01 ~]# date -s '2018-05-08'
mysql> create database bgxdb;
mysql> use bgxdb;
mysql> create table t2(id int);
mysql> insert into t2 values(3);
mysql> select * from t2;
+------+
| id   |
+------+
|    3 |
+------+

第二步：对周三物理全备

[root@mysql01 ~]# mmkdir /xtrabackup ; chown -R mysql:mysql /xtrabackup
[root@mysql01 ~]# yum -y install  https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm
[root@mysql01 ~]# yum -y install ./percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm
[root@mysql01 ~]# innobackupex --user=root --password='cba-123' /xtrabackup
[root@mysql01 ~]# cat /xtrabackup/2018-05-08_00-00-10/xtrabackup_checkpoints 
backup_type = full-backuped    #全备
from_lsn = 0
to_lsn = 2671547
last_lsn = 2671556
compact = 0
recover_binlog_info = 0

第三步：周四增加数据，并做增备

[root@mysql01 ~]# date -s '2018-05-09'
[root@mysql01 ~]# mysql -uroot -pcba-123 -e "insert into bgxdb.t2 values(4);"
[root@mysql01 ~]# mysql -uroot -pcba-123 -e "select * from bgxdb.t2;"
+------+
| id   |
+------+
|    3 |
|    4 |
+------+
[root@mysql01 ~]# innobackupex --user=root --password='cba-123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2018-05-08_00-00-10
[root@mysql01 ~]# ls /xtrabackup/    #多了一个9号的
2018-05-08_00-00-10  2018-05-09_00-03-43

第四步：周五增加数据，并做增备

[root@mysql01 ~]# date -s '2018-05-10'
[root@mysql01 ~]# mysql -u root -pcba-123 -e "insert into bgxdb.t2 values(5);"
[root@mysql01 ~]# mysql -u root -pcba-123 -e "select * from bgxdb.t2;"
+------+
| id   |
+------+
|    3 |
|    4 |
|    5 |
+------+
[root@mysql01 ~]# innobackupex --user=root --password='cba-123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2018-05-08_00-00-10
[root@mysql01 ~]# ls /xtrabackup/
2018-05-08_00-00-10  2018-05-09_00-03-43  2018-05-10_00-00-23

第四步：周六增加数据，并做增备

[root@mysql01 ~]# date -s "2018-05-11"
[root@mysql01 ~]# mysql -u root -pcba-123 -e "insert into bgxdb.t2 values(6);"
[root@mysql01 ~]# mysql -u root -pcba-123 -e "select * from bgxdb.t2;"
+------+
| id   |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
+------+
[root@mysql01 ~]#  innobackupex --user=root --password='cba-123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2018-05-08_00-00-10
[root@mysql01 ~]# ls /xtrabackup/
2018-05-08_00-00-10  2018-05-09_00-03-43  2018-05-10_00-00-23  2018-05-11_00-07-25

第五步：保持当前时间，模块错误操作

mysql> create database Linux;
mysql> drop database Linux;

第六步：开始差异恢复

//停止数据库
[root@mysql01 ~]# systemctl stop mysqld
//清理环境
[root@mysql01 ~]# ls /mysql/
auto.cnf    ca.pem           ib_buffer_pool  ib_logfile1         private_key.pem  server-key.pem
bgxdb       client-cert.pem  ibdata1         mysql               public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0     performance_schema  server-cert.pem
[root@mysql01 ~]# rm -rf /mysql/*
//重演回滚，将差异备份应用至完整备份上
[root@mysql01 ~]# innobackupex --apply-log --redo-only /xtrabackup/2018-05-08_00-00-10/
//重演回滚，将差异备份应用至完整备份上
[root@mysql01 ~]# innobackupex --apply-log --redo-only /xtrabackup/2018-05-08_00-00-10/ --incremental-dir=/xtrabackup/2018-05-11_00-07-25
//应用数据
[root@mysql01 ~]# innobackupex --copy-back /xtrabackup/2018-05-08_00-00-10/
//修改权限
[root@mysql01 ~]# chown -R mysql:mysql /mysql/
//启动数据库
[root@mysql01 ~]# systemctl start mysqld
//查看数据库
mysql> select * from bgxdb.t2;
+------+
| id   |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
+------+
mysql> show databases;  #被删除的那个数据库依然没有找到
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bgxdb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

第七步：恢复被误删除的数据库

//哪天删除的就查看哪天的二进制日志
[root@mysql01 ~]# cat /xtrabackup/2018-05-11_00-07-25/xtrabackup_binlog_info 
bin_log.000001	654
[root@mysql01 ~]# mysqlbinlog --start-position=654 --base64-output="decode-rows" -v bin_log.000001 bin_log.000002 >db.sql

[root@mysql01 mysql]# mysqlbinlog --start-position=654 --stop-position=816 bin_log.000001 | mysql -uroot -p'cba-123'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| bgxdb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

4、简单命令进行物理备份

使用简单命令进行备份时，无法保证数据的可用性，可能需要停止服务，慎用！

系统版本	MYSQL版本	安装方式
centos7	MYSQL5.7	YUM安装

//查看一下数据库的存放位置
[root@mysql01 ~]# cat /etc/my.cnf
[mysqld]
datadir=/mysql
socket=/var/lib/mysql/mysql.sock
//打包备份
[root@mysql01 ~]# tar -cf /backup/`date +%F`-mysql-all.tar /mysql 
[root@mysql01 ~]# ls /backup
2019-11-30-mysql-all.tar

//停止数据库
[root@mysql01 ~]# systemctl stop mysqld
[root@mysql01 ~]# rm -rf /mysql/*
[root@mysql01 ~]# tar xf /backup/2019-11-30-mysql-all.tar -C /
[root@mysql01 ~]# ls /mysql
auto.cnf    ca.pem           ib_buffer_pool  ib_logfile1  performance_schema  server-cert.pem
bgx         client-cert.pem  ibdata1         ibtmp1       private_key.pem     server-key.pem
ca-key.pem  client-key.pem   ib_logfile0     mysql        public_key.pem      sys
[root@mysql01 ~]# systemctl start mysqld;

5、实际生产环境

• 实际的生产环境上通常都周末全备，周一到周六都是增备。
• 中小型企业，一般还是使用mysqldump居多，而大型企业都是xtrabakcup居多，当然也有的企业使用专业的存储设备进行备份。