MySQL 数据库完全备份

1. 数据库备份的重要性

         在生产环境中，数据库的安全性是至关重要的，任何数据的丢失都可能产生严重的后果。数据库的备份的重要性主要体现在：

（1） 提高系统的高可用性和灾难可恢复性，在数据库系统崩溃时，没有数据库备份就没法找到数据。

（2）使用数据库备份还原数据库，时数据库崩溃时提供数据恢复最小代价的最优方案，如果让用户重新添加数据，代价太大。

（3）没有数据库就没有一切，数据库的备份是一种灾害的强力手段。

          使用数据库的过程中，有多种原因造成数据丢失：程序错误、认为错误、计算机失误、磁盘失败、灾难（如火灾、地震）和盗窃。

2.数据库的备份分类

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

（1）物理备份：值对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份（冷备份）和联机备份 (热备份）。

     冷备份：在关闭数据库时进行的备份操作，能够较好的保证数据库的完整性。

     热备份：在数据库运行状态中进行操作，这种备份依赖于数据库的日志文件。

（2）逻辑备份：指对数据库逻辑组件 （如表等数据库对象）的备份。

从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份。

      完全备份：每次对数据库进行完整备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程中所有数据库对象。但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些。

       差异备份：备份那些自从上次完全备份之后被修改过的文件，值备份数据库的部分内容，比完全备份小，因此存储和恢复速度快。

       增量备份：只有那些在上次完全备份或者增量备份后修改的文件才会被备份。

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

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

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

（1）数据库文件很大，可以使用压缩率较大的xz格式压缩，首先检查 xz 压缩工具是否安装

[root@localhost opt]# rpm -q xz                 //zx压缩工具已经安装
xz-5.2.2-1.el7.x86_64

（2）都数据库文件夹  /usr/local/mysql/data/  进行打包操作 放到 /opt/目录下

[root@localhost opt]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/  /opt/

（3）查看打包命令是否成功，已经生产备份文件。对比压缩前后的文件大小。

[root@localhost opt]# ls
mysql-2018-09-03.tar.xz  mysql-5.7.17  rh                 //压缩生成的备份文件 ，时间格式为 年月日
[root@localhost opt]# du -sh /opt/mysql-2018-09-03.tar.xz
684K    /opt/mysql-2018-09-03.tar.xz
[root@localhost opt]# du -sh /usr/local/mysql/data/                      //压缩备份文件占用空间很小
134M    /usr/local/mysql/data/

（4）如果数据库文件丢失，可以解压缩备份文件，相当于做了数据恢复的工作。

[root@localhost opt]# tar Jxvf /opt/mysql-2018-09-03.tar.xz /usr/local/mysql/data/

2.使用mysqldump 工具备份

  前面介绍的对MtSQL 整个数据库目录压缩的方式，是备份数据库中所有内容。使用 mysqldump 可以更加灵活的控制备份内容，比如某几个表或库都可以单独备份。

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

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

下面演示对库 school 中的表  info 进行备份，备份文件是 /opt/school.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mogo               |
| mysql              |
| performance_schema |
| school             |                        //对库school 中的表 info 进行备份
| sys                |
+--------------------+

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

[root@bogon ~]# mysqldump -uroot -p school info > /opt/school.sql
Enter password:

[root@bogon opt]# ls
info.txt  mysql-5.7.17  rh  school.sql        //备份文件

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

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

下面演示对库 mogo 进行备份，备份文件是  /opt/mogo.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mogo               |                           //对库mogo 进行完整备份（库中所有的表都备份）
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

[root@bogon opt]# mysqldump -uroot -p --databases mogo > /opt/mogo.sql
Enter password:
[root@bogon opt]# ls
info.txt mogo.sql  mysql-5.7.17  rh  school.sql        //备份文件

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

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

下面演示对库 mogo  和 school  进行备份，备份文件是  /opt/school01.sql

[root@bogon opt]# mysqldump -uroot -p --databases school mogo > /opt/school01.sql
Enter password:
[root@bogon opt]# ls
info.txt  mogo.sql  mysql-5.7.17  rh  school01.sql  school.sql      //备份文件

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

mysqldump  -u  用户名   -p  [密码]   --all-databases    >  /opt/all.sql

下面演示对所有库 进行备份，备份文件是  /opt/all.sql

[root@bogon opt]# mysqldump -uroot -p --all-databases > all.sql
Enter password:
[root@bogon opt]# ls
all.sql  info.txt  mogo.sql  mysql-5.7.17  rh  school01.sql  school.sql       //所有库备份文件

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

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

只是多了一个 –d 选项，下面演示对库 school 中表  info  进行表结构备份，备份文件是 /opt/info.sql

[root@bogon opt]# mysqldump -uroot -p -d school info > /opt/info.sql
Enter password:
[root@bogon opt]# ls
all.sql info.sql  info.txt  mogo.sql  mysql-5.7.17  rh  school01.sql  school.sql           //表结构备份文件

分析备份文件 school.sql 内容

[root@bogon opt]# vim school.sql

CREATE TABLE `info` (                                              //创建表 info
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` char(10) DEFAULT NULL,
   `score` decimal(5,2) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `info`
--

LOCK TABLES `info` WRITE;
/*!40000 ALTER TABLE `info` DISABLE KEYS */;
INSERT INTO `info` VALUES (1,'tom',89.00),(2,'lili',96.00),(3,'test01',86.00),(4,'test02',86.00),(5,'test03',86.00);       //在表中插入数据
/*!40000 ALTER TABLE `info` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

3.MySQL 库完全恢复

         前面介绍了数据库的完整备份，使用 mysqldump 工具可以吧数据库的创建语句保存到备份脚本中，当数据出现错误时，可以使用以下方法对它进行恢复操作。

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

（1）source 命令恢复数据库

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

source     备份脚本

下面对库 mogo 进行备份操作，登录数据库，假设数据库损坏，删除数据库 mogo

[root@bogon opt]# mysql -uroot –p           //先登录数据库
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mogo               |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> drop database mogo;                      //模拟数据库损坏，删除数据库 mogo
Query OK, 1 row affected (0.02 sec)

mysql> source /opt/mogo.sql;                    //使用 source 命令恢复数据库 mogo
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;                 //查看数据库 mogo 是否恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mogo               |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec

（2）MySQL 命令整库恢复

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

使用备份文件对库mogo 进行恢复操作，假设数据库mogo损坏，删除数据库mogo ,使用 mysql 命令直接恢复。

mysql> drop database mogo;                        //模拟数据库损坏，删除数据库mogo
Query OK, 1 row affected (0.37 sec)

mysql> show databases;                       //数据库中没有 mogo 这个库了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> quit
Bye

[root@bogon opt]# mysql -uroot -p < /opt/mogo.sql               //使用mysql 命令直接恢复数据库
Enter password:
[root@bogon opt]# mysql -uroot -p -e 'show databases;'         //不登录数据库查询恢复情况
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mogo               |                                   //库mogo 已经恢复
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

4.恢复表操作

（1）使用source 命令恢复表操作与恢复库方式相同

使用备份文件 school.sql  进行表恢复操作，模拟数据库中的表损坏，删除数据库 school 中 的 表 Info,使用 source 命令恢复。

mysql> use school;                             //进入数据库school
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> show tables;                          //查看数据库中的表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

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

mysql> show tables;                  //已经没有表info
Empty set (0.00 sec)

mysql> source /opt/school.sql;               //使用source 命令恢复表操作
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;                  查看表info 已经恢复
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 96.00 |
|  3 | test01 | 86.00 |
|  4 | test02 | 86.00 |
|  5 | test03 | 86.00 |
+----+--------+-------+
5 rows in set (0.00 sec)

（2）MySQL 命令表恢复

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

使用备份school.sql文件对表进行恢复操作，假设数据库school 中的表info 损坏，删除表 info ,使用 mysql 命令直接恢复。

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

mysql> quit
Bye
[root@bogon opt]# mysql -uroot -p school < /opt/school.sql      // 使用mysql 命令恢复库school 中的表 info
Enter password:
[root@bogon opt]# mysql -uroot -p -e 'use school;show tables;select * from info;'              //不登录mysql 查看表info 的恢复情况
Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 96.00 |
|  3 | test01 | 86.00 |
|  4 | test02 | 86.00 |
|  5 | test03 | 86.00 |
+----+--------+-------+