MySQL之备份和还原

在实际项目中对于数据库的安全是重中之重，为防万一我们需要做好备份工作。备份分为全量备份和增量备份，今天我们就来实践下备份和还原操作。

一、为什么需要备份

在生产环境中数据库可能会遭遇到各种各样的不测从而导致数据丢失, 大致上可以分为以下几种：

硬件故障
软件故障
自然灾害
黑客攻击
误操作 (占比最大)

所以, 为了在数据丢失之后能够有效地恢复数据, 我们就需要定期的备份数据。备份数据的策略需要根据不同的应用场景进行定制, 大致有如下几个参考事项：

备份需要锁定数据库资源多长时间？（是否影响业务）
备份所需要的时长?
备份时服务器的负载情况如何？
数据恢复所需要的时长？

我们可以根据这些参考事项定制符合特定环境中的数据备份策略。

二、备份方式分类

MySQL 的备份方式多种多样，可以根据备份手段、备份的数据范围及备份时数据服务是否在线等来划分。

2.1 备份手段

物理备份

是指备份时直接复制备份数据库的数据文件。

逻辑备份

是指备份时，是使用备份工具从数据库导出数据库的一个或多个备份文件。

2.2 备份的数据范围

全量备份

是指将整个数据库内容做完整的备份。

增量备份

是指仅备份自上一次完全备份或增量备份之后所增加的数据内容。

差异备份

是指仅备份自上一个完全备份之后所增加的内容。

事务日志备份

事务日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。为了使数据库具有鲁棒性(健壮和强壮的意思)，推荐每小时甚至更频繁的备份事务日志。

2.3 备份时数据服务是否在线

热备份

是指在不停用数据库所提供的数据服务的读写操作下所做的备份。

冷备份

是指在停用数据库所提供的数据服务的读写操作下进行备份。

温备份

是指在停用数据库的写入操作，但不停用读取操作的情况下进行备份。

三、备份工具对比

备份方法	备份速度	恢复速度	便捷性	功能	使用场景	提供者
cp	快	快	一般、灵活性低	弱	少量数据备份	liunx 系统提供
mysqldump	慢	慢	一般、可无视存储引擎的差异	一般	中小型数据量的备份	MySQL自带
xtrabackup	较快	较快	实现innodb热备、对存储引擎有要求	强大	较大规模的备份	percona提供

四、准备工作

4.1 查看 MySQL 运行状态：

[root@VM_0_15_centos tmp]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2019-07-09 08:40:19 CST; 1 months 11 days ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 2922 (mysqld)
    Tasks: 36
   Memory: 4.6M
   CGroup: /system.slice/mysqld.service
           └─2922 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 09 08:40:18 VM_0_15_centos systemd[1]: Starting MySQL Server...
Jul 09 08:40:19 VM_0_15_centos systemd[1]: Started MySQL Server.

4.2 进入mysql

[root@VM_0_15_centos etc]# mysql -uroot -p

4.3 查看当前数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

4.4 新建数据库

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

4.5 新建表

mysql> use test;
Database changed
mysql> create table sys_user(id int ,name varchar(50));
Query OK, 0 rows affected (0.11 sec)

4.6 插入记录

mysql> insert into sys_user values(1,'a');
Query OK, 1 row affected (0.02 sec)

五、cp 方式备份

5.1 创建备份目录

[root@VM_0_15_centos mysql]# mkdir -p /backup/cp

5.2 拷贝源数据文件

[root@VM_0_15_centos cp]# cp -a /var/lib/mysql/test /backup/cp
[root@VM_0_15_centos cp]# ll
total 4
drwxr-x--- 2 mysql mysql 4096 Aug 20 15:20 test

5.3 模拟数据丢失(将文件删除)

[root@VM_0_15_centos cp]# rm -rf /var/lib/mysql/test

5.4 查看数据库列表

[root@VM_0_15_centos cp]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

可以看到 test 数据库已经不存在了。

5.5 将数据库文件还原并重启服务

[root@VM_0_15_centos cp]# cp -a /backup/cp/test /var/lib/mysql
[root@VM_0_15_centos cp]# systemctl restart mysqld

5.6 验证数据

[root@VM_0_15_centos cp]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from sys_user;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

可以看到 test 数据库还原成功

六、mysqldump 方式备份

6.1 创建备份目录

[root@VM_0_15_centos mysql]# mkdir -p /backup/mysqldump

6.2 备份数据库数据文件

[root@VM_0_15_centos mysqldump]# mysqldump -uroot -p test > /backup/mysqldump/test.sql
Enter password: 
[root@VM_0_15_centos mysqldump]# ll
total 4
-rw-r--r-- 1 root root 1846 Aug 21 09:47 test.sql

6.3 模拟数据丢失(将文件删除，生产环境谨慎操作)

[root@VM_0_15_centos mysqldump]# rm -rf /var/lib/mysql/test

该步骤无效，导致之后还原操作有问题，我这边直接重新部署了全新的 MySQL 进行还原。

物理删除数据库文件后mysql可能存有数据库表的记录，导致恢复的时候提示表已存在，再用sql进行删库就ok了。

6.4 查看数据库列表

[root@VM_0_15_centos mysqldump]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

6.5 还原数据

[root@VM_0_15_centos mysqldump]# mysqladmin -uroot -p create test
Enter password: 
[root@VM_0_15_centos ~]# mysql -uroot -p test < /backup/mysqldump/test.sql
Enter password:

6.6 验证数据

[root@VM_0_15_centos ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from sys_user;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

可以看到数据已还原。

七、xtrabackup 方式备份

7.1 创建备份

[root@VM_0_15_centos backup]# xtrabackup --uroot -p123456 --databases=test --backup --target-dir=/backup/xtrabackup/

如果目标目录不存在，xtrabackup 会创建它。xtrabackup不会覆盖现有文件，如果目标文件已存在它会因操作系统错误17而失败。

7.2 准备备份

xtrabackup --prepare --target-dir=/backup/xtrabackup/

一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。--prepare参数实现通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

7.3 修改数据

[root@VM_0_15_centos xtrabackup]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database test;

Query OK, 1 row affected (0.11 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

7.4 恢复备份

[root@VM_0_15_centos xtrabackup]# systemctl stop mysqld # 关闭 MySQL 服务
[root@VM_0_15_centos xtrabackup]# rsync -avrP /backup/xtrabackup/ /var/lib/mysql/ # 还原数据
[root@VM_0_15_centos xtrabackup]# chown -R mysql:mysql /var/lib/mysql 
[root@VM_0_15_centos xtrabackup]# systemctl start mysqld # 重启 MySQL 服务

7.5 验证数据

[root@VM_0_15_centos xtrabackup]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from sys_user;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)