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 |
+----+--------+-------+