MySQL 备份与恢复

一、数据库备份的类型

备份类型分类

按照服务器的运行状态，可分为：冷备、热备、温备。

冷备：数据库处于停机状态下进行备份。
热备：数据库处于运行状态下备份，在热备期间，数据库的读写操作均可正常进行。

在mysql中，MyISAM存储引擎不支持热备，InnoDB存储引擎支持热备。
温备：数据库处于运行状态下备份，但是在温备期间数据库只能进行读操作，不能进行写操作

按照备份后的内容量，可分为：全备、增备、差异备份。

全量备份：对所有数据进行一个完全的备份。
增备备份：对上一次”备份”以后变化的数据的备份。
差异备份：对上一次”全量备份”以后变化的数据的备份。

按照备份的方式，可分为：逻辑备份、物理备份。

逻辑备份：将数据从数据库中导出，并将导出的数据进行存档备份。
物理备份：直接备份数据库所对应的数据文件。物理备份相对逻辑备份来说，性能更强。

备份方式优缺点

MySQL进行全量数据备份时，可以选择物理备份（如 Xtrabackup）和逻辑备份（如 mysqldump）两种方式。这两种备份方法各有优缺点，适用于不同的场景：

物理备份（Xtrabackup）

优点：

速度快：物理备份直接复制数据文件，对于大型数据库，备份和恢复的速度通常比逻辑备份快得多。
资源占用较低：进行物理备份时，对数据库的影响较小，不需要像逻辑备份那样执行大量SQL查询。
支持热备份：使用Xtrabackup可以在数据库运行时进行备份，不需要停机。

缺点：

平台依赖性：物理备份与MySQL的存储引擎和文件系统紧密相关，因此可能不易于跨平台恢复。
灵活性较低：物理备份通常备份整个数据库实例，不如逻辑备份那样容易实现对单个数据库或表的备份和恢复。

逻辑备份（mysqldump）

优点：

兼容性好：逻辑备份生成的是SQL语句，因此备份文件在不同平台和版本的MySQL之间具有很高的兼容性。
灵活性高：可以选择备份整个数据库、单个数据库或特定表。还可以通过SQL对备份数据进行筛选。
易于理解和修改：备份文件是纯文本的SQL语句，可以直接查看和编辑。

缺点：

速度慢：对于大型数据库，mysqldump可能会非常慢，因为它需要读取每一行数据并生成SQL语句。
资源消耗大：逻辑备份在运行时会对数据库性能产生影响，尤其是锁表操作会暂停对应表的读写。

如何选择

对于大型生产环境，如果要求最小化对业务的影响，且备份和恢复速度非常关键，推荐使用物理备份方法（Xtrabackup）。
对于小型数据库，或者在兼容性和灵活性要求较高的场景下，逻辑备份（mysqldump）是一个简单有效的选择。
灾难恢复计划：在灾难恢复计划中，通常建议结合使用这两种备份方法，以平衡它们的优缺点，确保数据的安全和恢复的灵活性。

二、数据库备份方式

在数据库备份当中，一般会采用逻辑备份、物理备份这两种方式对数据库进行备份。

2.1 逻辑备份

基于SQL语句进行备份。

常见得逻辑备份方式：

以binlog方式进行备份
使用mysqldump备份工具备份
replication（数据库主从）
select into outfile （使用SQL语句导出数据到文件）

2.2 物理备份

基于数据文件的备份。

常见物理备份方式：

使用xtrabackup备份工具备份

三、MySQL主流备份工具之mysqldump

mysqldump是MySQL自带的免费备份工具，是一种逻辑备份工具。

在MySQL 5.7中新增了一个mysqldump的改进版备份工具mysqlpump，它支持基于表级别的并行备份。

3.1 备份方法

备份单表或者一组指定的库表

mysqldump [options] db_name [tbl_name ...]

备份一组或者一个数据库下的所有表

mysqldump [options] --databases db_name ...

备份整个数据库实例

mysqldump [options] --all-databases

3.2 mysqldump常用命令行选项

-A，–all-databases：备份所有数据库下的所有表。
-B，–databases：备份指定数据库下的所有表。
–master-data[=value]：则在备份文件中会生成一条CHANGE MASTER TO语句（包含binlog pos，binlog file）
- value=2：备份文件中生成的CHANGE MASTER TO语句将被注释
- value=1：该语句不会被注释
- value=0：不记录CHANGE MASTER TO语句
-F，–flush-logs：在开始备份之前刷新MySQL二进制日志文件。
-E,–events：在备份数据中包含数据库的事件。
–triggers：在备份文件中包含每个备份表的触发器。
–single-transaction：在备份数据之前开启一个事务快照，然后在事务快照内进行备份。
- 结合–master-data实现热备
-d，–no-data：不备份表数据，只备份表结构。
-t，–no-create-info：只备份数据，不备份表结构。
-R，–routines：备份数据中存储过程和函数。

3.3 mysqldump备份注意

mysqldump在备份和恢复时都需要MySQL实例启动为前提
一般数据量级100G以内，大约15-30分钟可以恢复（TB、PB、EB就需要考虑别的方式）
mysqldump是以覆盖的形式恢复数据的

3.4 实战：模拟完全备份和恢复

在备份源数据库中插入一些数据

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | tset1 |
|  2 | test2 |
|  3 | test3 |
|  4 | test4 |
|  5 | test5 |
|  6 | test6 |
+----+-------+
6 rows in set (0.00 sec)

mysql> insert into test(name) values("tset7"),("test8"),("test9"),("test10");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
......
|  7 | test7  |
|  8 | test8  |
|  9 | test9  |
| 10 | test10 |
+----+--------+
10 rows in set (0.00 sec)

在远程备份服务器中使用mysqldump远程备份数据：

[root@db02 ~]# mkdir /data/backup/mysql/mysqldump -p
[root@db02 ~]# cd /data/backup/mysql/mysqldump/
[root@db02 mysqldump]# mysqldump -h 172.16.1.51 -uroot -p123 --single-transaction \
--master-data=2 --triggers --events -R -A > backup_`date +%F_%H_%M_%S`.sql
[root@db02 mysqldump]# ls -lh
total 347M
-rw-r--r-- 1 root root 347M Jun 19 23:37 backup_2020-06-19_23_37_03.sql

查看备份文件中的binlog pos位置、binlog文件：

[root@db02 mysqldump]# head -100 backup_2020-06-19_23_37_03.sql | grep -i CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=911;

把备份文件导入恢复服务器的数据库实例中：

[root@db01 mysqldump]# mysql -uroot -p123 -e "reset master;"
[root@db01 mysqldump]# mysql -uroot -p123 < backup_2020-06-19_23_37_03.sql

登录恢复服务器中的数据库，校验数据：

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | tset1  |
|  2 | test2  |
|  3 | test3  |
|  4 | test4  |
|  5 | test5  |
|  6 | test6  |
|  7 | tset7  |
|  8 | test8  |
|  9 | test9  |
| 10 | test10 |
+----+--------+
10 rows in set (0.01 sec)

四、MySQL主流备份工具之XtraBackup

XtraBackup是一款开源的热备份软件（物理备份），它可以非阻塞地对InnoDB和XtraDB数据库进行备份。

XtraBackup的出现，解决了mysqldump在备份数据量大的数据库时，备份和恢复的时间高开销的问题。

4.1 XtraBackup的都优点

快速可靠地完成备份。
在备份期间不间断地处理事务。
节省磁盘空间和网络带宽。
自动备份验证。
更快地恢复，以保障业务有更长的在线时间。

4.2 备份方式（物理备份）

对于非innodb表（比如myisam）是直接锁表，cp数据文件，属于一种温备。
对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
备份时读取配置文件/etc/my.cnf

4.3 下载安装XtraBackup软件

XtraBackup 的 RPM 包可去 Percona 官网下载相应的版本。

安装依赖

yum install -y perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

安装XtraBackup

yum install -y https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

4.4 innobackupex命令常用选项

–user=USER：备份服务器数据库用户名
–password=PASSWORD：备份服务器数据库密码
–host=HOST：备份服务器的IP
–port=PORT：备份服务器的端口号
–no-timestamp：不在指定备份目录下创建以时间戳命名的目录
–defaults-file：指定默认的my.cnf配置文件
–apply-log：在指定备份目录下执行应用xtrabackup_logfile文件中的事务日志操作，同时根据配置文件中的innodb_log_file_size系统参数生成新的事务日志
–redo-only：针对未提交的事务不执行回滚操作，结合–apply-log一起使用
–copy-back：会从指定目录中读取数据文件，并复制到目标数据库的数据目录中
–incremental：告诉xtrabackup创建一个增量备份，而不是一个完整的备份
–incremental-basedir=DIRECTORY：与–incremental一同使用，指定完全备份或上一次增量备份的路径
–incremental-dir=DIRECTORY：项用于在应用redo日志时指定一个增量备份目录

4.5 实战：模拟完全备份与恢复

使用innobackupex命令完全备份数据库文件，并将备份文件放在/data/backup/mysql/test_backup目录下。

[root@db01 ~]# mkdir -p /data/backup/mysql/test_backup
[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf  --user=root --password=123 \
--no-timestamp /data/backup/mysql/test_backup/
......
200621 21:18:48 completed OK!     #看到类似的输出信息，则表示备份成功！

对备份目录执行–apply-log操作：

[root@db01 ~]# innobackupex --user=root --password=123 --apply-log /data/backup/mysql/test_backup/
......
200621 21:22:06 completed OK!     #看到类似的输出信息，则表示--apply-log操作执行成功！

现在模拟数据库数据丢失情况。即先停止数据库，清空数据目录。

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /usr/local/mysql/data/*

恢复数据，对备份目录执行–copy-back操作，把备份文件复制到之前清空的数据目录下：

[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/mysql/test_backup/
......
200621 21:51:42 completed OK!     #看到类似的输出信息，表示--copy-back操作执行成功！

修改数据目录的属主、属组，启动数据库：

[root@db01 ~]# chown -R mysql:mysql /usr/local/mysql/data
[root@db01 ~]# /etc/init.d/mysqld start

登录数据库，校验数据是否正确：

[root@db01 ~]# mysql -uroot -p123 -e "show databases;"

4.6 实战：模拟增量备份与恢复

增量备份特性：

基于上一次备份进行增量
增量备份无法单独恢复，必须基于全备进行恢复
所有增量必须要按顺序合并到全备当中

4.6.1 完全备份和增量备份

使用innobackupex命令给数据库进行一次完全备份，备份目录到backup-$(date +%F)

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /data/backup/mysql/backup-$(date +%F)
......
200622 18:38:04 completed OK!

完全备份成功后，进入数据库新建库、表并插入一些数据（制造增量数据）：

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> use test1;
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tb1(id) values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

进行第一次增备备份。第一次增量备份的basedir是完全备份，因为在第一次增量备份之前的最近一次备份只有完全备份。

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/data/backup/mysql/backup-2020-06-22/ --incremental /data/backup/mysql/incremental_one
......
200622 18:54:29 completed OK!

继续制造测试数据：

mysql> create table tb2(id int,name varchar(10));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into tb2(id,name) values(1,'name1'),(2,'name2'),(3,'name3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb2;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
+------+-------+
3 rows in set (0.00 sec)

进行第二次增量备份。第二次增量备份就不需要再基于完全备份了，因为最近一次备份是第一次增量备份，这里第二次增量备份的basedir是基于第一次增量备份的。

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/data/backup/mysql/incremental_one/ --incremental /data/backup/mysql/incremental_two
......
200622 19:06:28 completed OK!

4.6.2 增量备份恢复

在恢复数据前，先停止数据库并清空相关数据目录（建议先备份在情况）。

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /usr/local/mysql/data/*

进入完全备份目录，执行–apply-log操作需要带上–redo-only选项，只应用完成redo日志，不对未提交的事务执行回滚操作。

[root@db01 ~]# cd /data/backup/mysql/backup-2020-06-22
[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only ./
......
200622 19:54:00 completed OK!

在完全备份的基础上执行第一次增量备份–apply-log操作（带上–redo-only）

[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only /data/backup/mysql/backup-2020-06-22/ --incremental-dir=/data/backup/mysql/incremental_one/
......
200622 19:58:29 completed OK!

在执行–apply-log操作的第一次增量备份的完全备份目录中，再对第二次增量备份执行–apply-log操作，因为这是最后一次增量备份，所以不需要加–redo-only选项。

[root@db01 backup-2020-06-22]# innobackupex --apply-log /data/backup/mysql/backup-2020-06-22/ --incremental-dir=/data/backup/mysql/incremental_two/
......
200622 20:02:57 completed OK!

最后，在对完全备份目录执行一次–apply-log操作，生成redo log文件。

[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only /data/backup/mysql/backup-2020-06-22/
......
200622 20:38:34 completed OK!

[root@db01 backup-2020-06-22]# ll -h
total 243M
-rw-r----- 1 root root  430 Jun 22 20:33 backup-my.cnf
drwxr-x--- 2 root root   70 Jun 22 20:37 binlog
drwxr-x--- 2 root root 4.0K Jun 22 20:37 db_test1
drwxr-x--- 2 root root   20 Jun 22 20:37 db_test3
-rw-r----- 1 root root  76M Jun 22 20:38 ibdata1
-rw-r----- 1 root root  50M Jun 22 20:33 ibdata2
-rw-r----- 1 root root  48M Jun 22 20:38 ib_logfile0
-rw-r----- 1 root root  48M Jun 22 20:38 ib_logfile1
-rw-r----- 1 root root  12M Jun 22 20:37 ibtmp1
drwxr-x--- 2 root root 4.0K Jun 22 20:37 mysql
drwxr-x--- 2 root root 4.0K Jun 22 20:37 performance_schema
drwxr-x--- 2 root root   60 Jun 22 20:37 test
drwxr-x--- 2 root root   80 Jun 22 20:37 test1
drwxr-x--- 2 root root  176 Jun 22 20:37 world
-rw-r----- 1 root root   27 Jun 22 20:37 xtrabackup_binlog_info
-rw-r--r-- 1 root root   27 Jun 22 20:38 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root  117 Jun 22 20:38 xtrabackup_checkpoints
-rw-r----- 1 root root  595 Jun 22 20:37 xtrabackup_info
-rw-r----- 1 root root 8.0M Jun 22 20:35 xtrabackup_logfile

将完全备份目录下的数据文件复制到相关目录下：

[root@db01 backup-2020-06-22]# innobackupex --copy-back ./
......
200622 20:40:22 completed OK!

修改数据目录属主并启动MySQL：

[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data/
[root@db01 ~]# /etc/init.d/mysqld start

登录数据库，效验数据。

[root@db01 ~]# mysql -uroot -p123 -e "use test1;select * from tb1,tb2;"

4.7 XtraBackup 备份所需权限

使用 XtraBackup 工具对数据库进行备份时，建议创建一个最小权限集合的账号。
备份所需最小权限集合：RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS
当使用到备份的高级功能是可能需要用到 CREATE TABLESPACE, CREATE, INSERT, SELECT, SUPER 等权限，根据需求添加:

GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS ON ****.**** TO bkpdbuser@'10.168.%' IDENTIFIED BY 'bkpdbpass';