数据库应用——MySQL完全备份与恢复

前言：随着办公自动化和电子商务的飞速发展，企业对信息系统的依赖性越来越高，数据库作为信息系统的核心担当着重要的角色。数据库备份，是在数据丢失的情况下，能及时恢复重要数据，防止数据丢失的一种重要手段。一个合理的数据库备份方案，应该能够在数据丢失时，有效地恢复数据，同时需要考虑技术实现难度和有效地利用资源

一、MySQL完全备份

1.数据库备份方式

1）数据库备份的重要性

• 提高系统的高可用性和灾难可恢复性，在数据库系统崩溃时，没有数据库备份就没法找到数据
• 使用数据库备份还原数据库，是数据库崩溃时提供数据恢复最小代价的最优方案，如果让用户重新添加数据，代价未免太大
• 没有数据就没有一切，数据库备份是一种防范灾难的强力手段

数据丢失的原因

• 程序错误：指对数据库操作的程序难免有些错误，造成数据丢失
• 认为错误：指由于使用人员的误操作造成的数据被破坏，还有可能是黑客对系统攻击造成的数据丢失等
• 计算机失败：指运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏
• 磁盘失败：指硬盘等存储数据的硬件设置，长时间运行后可能损坏
• 灾难（如火灾、地震）和偷窃：指自然灾害的发生，有可能造成数据丢失

2）数据库备份的分类

备份方式分很多种，从物理与逻辑的角度，备份分为以下几类

• 物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）
  • 冷备份：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性
  • 热备份：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件
• 逻辑备份：指对数据库逻辑组件（如表等数据库对象）的备份
  从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份
  • 完全备份：每次对数据进行完整的备份。缺点是周期长
  • 差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容。优点是存储和恢复速度快
  • 增量备份：只有那些在上次完全备份或增量备份后被修改的文件才会被备份
    几种备份的区别如下：
    
    完全备份每次都是把整个数据库中的内容进行备份
    差异备份在第1次添加内容后备份，是备份新添加的内容，第2次添加内容后备份，把第1次添加的内容也进行了备份，它是根据完全备份时的状态绝对的。差异备份每次都是把完全备份之后所有的修改进行备份，不管前面是否做过差异备份
    增量备份只备份每次新修改的内容，第1次创建表3，就对表3备份，第2次添加表4后，因为之前已经备份过表3，所以只备份表4的内容即可

3）MySQL完全备份概念

• MySQL的备份方式主要有完全备份与增量备份。完全备份是对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是增量备份的基础
• 完全备份的优点是备份与恢复操作简单方便，缺点是数据存在大量重复，占用大量的备份空间，备份的时间长
• 在生产环境中，这两种备份方式都会使用，需要指定合理高效的方案达到备份数据的目的，避免数据丢失造成严重的后果

2.mysqldump备份

MySQL数据库的备份可以采用两种方式，因为数据库实际上都是文件，直接打包数据库文件夹，或者是使用专用备份工具mysqldump都可以进行备份工作

1）使用tar打包文件夹备份

MySQL的数据库文件默认都是保存在安装目录的data文件夹下面，可以直接保存data文件夹，但是占用的空间较大，可以使用tar打包压缩进行保存

[root@localhost data]# yum install xz -y   //安装xz压缩格式工具
[root@localhost data]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/  对数据库文件夹进行打包
[root@localhost data]# ls /opt   //查看已经生成了备份文件
mysql-2020-01-24.tar.xz  rh
[root@localhost data]# du -sh /opt/mysql-2020-01-24.tar.xz 
676K    /opt/mysql-2020-01-24.tar.xz
[root@localhost data]# du -sh /usr/local/mysql/data/
134M    /usr/local/mysql/data/
//对比打包前后的文件大小，可以看到压缩的备份文件占用空间很小

[root@localhost data]# tar Jxvf /opt/mysql-2020-01-24.tar.xz /usr/local/mysql/data/
//如果数据库文件损坏数据丢失，可以解压缩备份文件，相当于做了数据恢复

2）使用mysqldump工具备份

使用mysqldump可以更加灵活地控制备份的内容，比如某几个表或库都可以单独备份

• 使用mysqldump命令对某些表进行完全备份，命令格式如下

mysqldump -u 用户名 -p[密码][选项][数据库名][数据表名]>/备份路径/备份文件名

• 使用mysqldump命令对单个库进行完全备份，命令格式如下

mysqldump -u 用户名 -p [密码][选项][数据库名]>/备份路径/备份文件名

• 使用mysqldump命令对多个库进行完全备份，命令格式如下

mysqldump -u 用户名 -p [密码][选项] --databases 库名1[库名2]...>/备份路径/备份文件名

• 使用mysqldump命令对所有库进行完全备份，命令格式如下

mysqldump -u 用户名 -p [密码][选项] --all-databases >/备份路径/备份文件名

• 使用mysqldump命令也可以直接备份表结构，命令格式如下

mysqldump -u 用户名 -p [密码] -d 数据库名 表名 >/备份路径/备份文件名

备份的本质就是保存数据库当前表结构的创建语句和现有数据的插入语句，有了它们可以方便地恢复数据库的状态

二、MySQL完全恢复

前面介绍了数据库的完全备份，使用mysqldump工具可以把数据库的创建语句保存在备份脚本文件中，当数据出现错误时，可以使用以下几种方式对它进行恢复操作

1.恢复整库操作

当需要恢复整库的时候，可以使用source命令和mysql命令

1）source命令整库恢复

登录MySQL后，可以使用source命令进行整库恢复，命令格式如下

source 备份脚本路径

[root@localhost ~]# mysqldump -uroot -p1234 school >/opt/school.sql     //先做备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p1234    //进入数据库
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> drop database school;    //删除库
Query OK, 1 row affected (0.01 sec)

mysql> show databases;      //查看school已经删除
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chartest           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> source /opt/school.sql
。。。。省略内容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql>use school；           //进入库
Database changed

mysql> describe biao;           //用describe命令查看表结构
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(4)        | NO   | PRI | NULL    |       |
| name    | char(10)      | NO   |     | NULL    |       |
| address | varchar(50)   | YES  |     | nj      |       |
| score   | decimal(10,0) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
//使用source恢复整库后，库school重新可以使用，表里的数据也恢复了

2）MySQL命令整库恢复

在不登录MySQL的情况下，可以使用mysql命令直接恢复整库

[root@localhost ~]# mysqldump -uroot -p1234 school >/opt/school.sql     //先做备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p1234    //进入数据库
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> drop database school;    //删除库
Query OK, 1 row affected (0.01 sec)

mysql> show databases;      //查看school已经删除
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chartest           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

• 不登录MySQL，使用mysql命令恢复库school，命令格式如下

mysql -u 用户名 -p[密码] <库备份脚本的路径

此时库school已经被删除了，需要先创建再进行恢复操作，否则会报错

[root@localhost ~]# mysql -uroot -p1234
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> create database school;
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p1234 school< /opt/school.sql 
[root@localhost ~]# mysql -uroot -p1234 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |            //库school恢复成功
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;            //进入数据库
Database changed

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| biao             |           //表也恢复成功
+------------------+
1 row in set (0.00 sec)

mysql> select * from biao;            //查询‘biao‘中的所有内容
+----+-------+---------+-------+
| id | name  | address | score |
+----+-------+---------+-------+
|  1 | stu01 | bj      |    87 |
|  2 | stu02 | sh      |    79 |
|  3 | stu03 | nj      |    90 |
|  4 | stu04 |         |    68 |
+----+-------+---------+-------+
4 rows in set (0.00 sec)
//结果显示恢复成功

2.恢复表操作

恢复数据表同样可以使用source和mysql命令操作

1）使用source命令恢复表

使用scource命令恢复表的操作与恢复库的操作方式相同

[root@localhost ~]# mysqldump -uroot -p1234 school biao > /opt/biao.sql   //给表做备份
[root@localhost ~]# mysql -uroot -p1234
mysql> use school；
Database changed

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| biao             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from biao;
+----+-------+
| id | name  |
+----+-------+
|  1 | stu01 |
|  2 | stu02 |
+----+-------+
2 rows in set (0.00 sec)

mysql> drop table biao;        //删除表
Query OK, 0 rows affected (0.04 sec)

mysql> use school;     //进入库
Database changed
mysql> source /opt/biao.sql    //恢复
Query OK, 0 rows affected (0.00 sec)
。。。省略内容
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| biao             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from biao;
+----+-------+
| id | name  |
+----+-------+
|  1 | stu01 |
|  2 | stu02 |
+----+-------+
2 rows in set (0.01 sec)

使用source命令恢复表后，表里的数据也恢复了，说明恢复是成功的

2）使用mysql命令恢复表

使用mysql命令恢复表的操作时，当备份文件只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在。

[root@localhost ~]# mysqldump -uroot -p1234 school biao > /opt/biao.sql   //给表做备份
[root@localhost ~]# mysql -uroot -p1234
mysql> use school；
Database changed

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| biao             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from biao;
+----+-------+
| id | name  |
+----+-------+
|  1 | stu01 |
|  2 | stu02 |
+----+-------+
2 rows in set (0.00 sec)

mysql> drop table biao;        //删除表
Query OK, 0 rows affected (0.04 sec)

不登陆MySQL，使用mysql命令恢复表，命令格式如下

mysql -u 用户名 -p [密码] 库名 < 库备份脚本的路径

[root@localhost ~]# mysql -uroot -p1234 school < /opt/biao.sql  
[root@localhost ~]# mysql -uroot -p1234
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use school;
Database changed

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| biao             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from biao;
+----+-------+
| id | name  |
+----+-------+
|  1 | stu01 |
|  2 | stu02 |
+----+-------+
2 rows in set (0.00 sec)

需要具体指定是哪个库，使用mysql命令恢复表后，表中的数据也恢复了，说明恢复成功了

三、MySQL备份思路

MySQL需要定期实施备份，制定合适的备份计划或者策略，并严格遵守。除了进行完全备份，开启MySQL服务器的日志功能也很重要，完全备份加上日志，可以对MySQL进行最大化还原
备份文件的名字需要使用统一和易理解的名称，不要使用backup1等这样没有意义的名字，推荐使用库名或者表名加上时间的命名规则，在需要恢复数据库的时候很容易定位到相应的备份文件

总结

• 从物理与逻辑的角度，备份可分为物理备份（冷备份、热备份）、逻辑备份
• 从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份
• MySQL中使用mysqldump工具备份，它生成的是SQL的脚本文件
• 恢复数据使用mysql、source命令
• 备份可以针对整库、某些库或某些表进行备份