一、 備份恢復策略

進行備份或恢復操作時需要考慮一些因素：

1、確定要備份的表的存儲引擎是事務型還是非事務型，兩種不同的存儲引擎備份方式在處理數據一致性方面是不太一樣的。

2、確定使用全備份還是增量備份。全備份的優點是備份保持最新備份，恢復的時候可以花費更少的時間；缺點是如果數據量大，將會花費很多的時間，并對系統造成較長時間的壓力。增量備份相反，只需要備份每天的增量日志，備份時間少，對負載壓力也小；缺點就是恢復的時候需要全備份加上次備份到故障前的所有日志，恢復時間長一些。

3、可以考慮采用復制的方法來做異地備份，但不能代替備份，它對數據庫的誤操作也無能為力。

4、要定期做備份，備份的周期要充分考慮系統可以承受的恢復時間。備份要在系統負載較小的時候進行

5、確保 MySQL 打開 log-bin 選項，有了 binlog，MySQL 才可以在必要的時候做完整恢復，或基于時間點的恢復，或基于位置的恢復。

6、經常做備份恢復測試，確保備份是有效的，是可以恢復的。

二、 邏輯備份和恢復

在 MySQL 中，邏輯備份的最大優點是對于各種存儲引擎都可以用同樣的方法來備份；而物理備份則不同，不同的存儲引擎有著不同的備份方法，因此，對于不同存儲引擎混合的數據庫，邏輯備份會簡單一點。

1. 備份

MySQL 中的邏輯備份是將數據庫中的數據備份為一個文本文件，備份的文件可以被查看和編輯。在 MySQL 中，可以使用 mysqldump 工具來完成邏輯備份：

// 備份指定的數據庫或者數據庫中的某些表  
shell> mysqldump [options] db_name [tables]  
// 備份指定的一個或多個數據庫  
shell> mysqldump [options] --database DB1 [DB2,DB3...]  
// 備份所有數據庫  
shell> mysqldump [options] --all-database

如果沒有指定數據庫中的任何表，默認導出所有數據庫中的所有表。

示例：

1. 備份所有數據庫：

shell>mysqldump -uroot -p --all-database > all.sql

2. 備份數據庫 test

shell>mysqldump -uroot -p test > test.sql

3. 備份數據庫 test 下的表 emp

shell> mysqldump -uroot -p test emp > emp.sql

4. 備份數據庫 test 下的表 emp 和 dept

shell> mysqldump -uroot -p test emp dept > emp_dept.sql

5. 備份數據庫test 下的所有表為逗號分割的文本，備份到 /tmp:

shell> mysqlddump -uroot -p -T /tmp test emp --fields-terminated-by ','shell> more emp.txt  1,z12,z23,z34,z4

注意：為了保證數據備份的一致性， myisam存儲引擎在備份時需要加上-l參數,表示將所有表加上讀鎖，在備份期間，所有表將只能讀而不能進行數據更新。但是對于事務存儲引擎來說，可以采用更好的選項 --single-transaction，此選項使得 innodb 存儲引擎得到一個快照(snapshot)，使得備份的數據能夠保證一致性。

2. 完全恢復

mysqldump 的恢復也很簡單，將備份作為輸入執行即可：

mysql -uroot -p db_name < backfile

注意，將備份恢復后數據并不完整，還需要將備份后執行的日志進行重做：

mysqlbinlog binlog-file | mysql -uroot -p

完整的 mysqldump 備份與恢復示例：

1. 凌晨 2:00，備份數據庫：

root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp
Enter password:

其中 -l參數表示給所有表加讀鎖，-F表示生成一個新的日志文件，此時，t2 中 emp 表的數據如下：

# 為了便于測試，執行 reset master 刪除所有 binlog。
MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)
# 此時只有一個 binlog 日志文件   mysql-bin.000001
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

2. 備份完畢后，插入新的數據：

# 因為上一步執行是加入了 -F 選項, 所以接下來的操作會被記錄到新的二進制文件，即名為 mysql-bin.000002 的文件
MySQL [t2]> insert into test values (3,'c');
Query OK, 1 row affected (0.00 sec)
MySQL [t2]> insert into test values (4,'d');
Query OK, 1 row affected (0.00 sec)

3. 數據庫突然故障(其實是小伙伴沒事兒刪庫練手玩兒)，數據無法訪問。需要恢復備份：

刪庫跑路：

# 這里為了便于測試，不把刪庫操作記入日志，當前 session 設置 sql_log_bin 為 off。
# 刪庫后，執行 flush logs，讓后續的 binlog 到新的文件中，即名為 mysql-bin.000003中
MySQL [t2]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)
MySQL [t2]> show variables like "%sql_log_bin%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
MySQL [t2]> drop database t2;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.22 sec)
MySQL [t2]> drop database t2;
Query OK, 3 rows affected (0.23 sec)
MySQL [(none)]> exit;
Bye

數據恢復：

root@bogon:/usr/local/mysql/bin# ./mysql -e "create database t2"  
root@bogon:/usr/local/mysql/bin# ./mysql t2 < t2.dmp
*******************************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

4. 使用 mysqlbinlog 恢復自 mysqldump 備份以來的 binglog

根據前面操作的內容，可知從備份的時間點到刪庫的時間點之間的操作被記錄到了 mysql-bin.000002 文件中

root@bogon:/usr/local/mysql/bin# ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000002 | ./mysql t2
*******************************************************
MySQL [t2]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

至此，數據恢復成功。

3. 基于時間點恢復

由于誤操作，比如誤刪除了一張表，這時使用完全恢復時沒有用的，因為日志里面還存在誤操作的語句，我們需要的是恢復到誤操作之前的狀態，然后跳過誤操作語句，再恢復后面執行的語句，完成恢復。這種恢復叫不完全恢復，在 MySQL 中，不完全恢復分為 基于時間點的恢復和基于位置的恢復。基于時間點恢復的操作步驟：

(1) 如果是上午 10 點發生了誤操作，可以用以下語句用備份和 binlog 將數據恢復到故障前：

shell>mysqlbinlog --stop-date="2017-09-30 9:59:59" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

(2) 跳過故障時的時間點，繼續執行后面的 binlog，完成恢復。

shell>mysqlbinlog --start-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

4. 基于位置恢復

和基于時間點的恢復類似，但是更精確，因為同一個時間點可能有很多條 sql 語句同時執行。恢復的操作步驟如下：

(1) 在 shell 下執行命令：

shell>mysqlbinlog --start-date="2019-09-30 9:59:59" --stop-date="2019-09-30 10:01:00"/data/mysql/mysql-bin.123456 > /tmp/mysql_restore.sql

該命令將在 /tmp 目錄創建小的文本文件，編輯此文件，知道出錯語句前后的位置號，例如前后位置號分別為 368312 和 368315。

(2) 恢復了以前的備份文件后，應從命令行輸入下面的內容：

shell>mysqlbinlog --stop-position="368312" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword  
shell>mysqlbinlog --start-position="368315" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

上面的第一行將恢復到停止位置為止的所有事務。下一行將恢復從給定的起始位置直到二進制日志結束的所有事務。因為 mysqlbinlog 的輸出包括每個 sql 語句記錄之前的 set timestamp 語句，因此恢復的數據和相關的 mysql 日志將反映事務執行的 原時間。

三、 表的導入導出

在數據庫的日常維護中，表的導入導出時很頻繁的一類操作。

1. 導出

在某些情況下，為了一些特定的目的，經常需要將表里的數據導出為某些符號分割的純數據文本，而不是 sql 語句：

1、用來作為 Excel 顯示；2、單純為了節省備份空間；3、為了快速的加載數據，load data 的加載速度比普通 sql 加載要快 20 倍以上。

使用 select ...into outfile ... 命令來導出數據，具體語法如下：

mysql> select * from tablename into outfile 'target_file' [option];

其中 option 參數可以是以下選項：

fields terminated by 'string'                   // 字段分隔符，默認為制表符't'
fields [optionally] enclosed by 'char'          // 字段引用符，如果加 optionally 選項則只用在 char、varchar 和 text 等字符型字段上，默認不使用引用符  
fields escaped by ‘char’                        // 轉移字符、默認為 ''  
lines starting by 'string'                      // 每行前都加此字符串，默認''  
lines terminated by 'string'                    // 行結束符，默認為'n'  
# char 表示此符號只能是單個字符，string表示可以是字符串。

例如，將 test 表中數據導出為數據文本，其中，字段分隔符為“,”,字段引用符為“"”,記錄結束符為回車符：

MySQL [t2]> select * from test into outfile '/data/mysql/outfile.txt' fields terminated by "," enclosed by '"';Query OK, 4 rows affected (0.02 sec)

zj@bogon:/data/mysql$ more outfile.txt"1","a","helloworld""2","b","helloworld""3","c","helloworld""4","d","helloworld"

發現第一列是數值型，如果不希望字段兩邊用引號引起，則語句改為：

MySQL [t2]> select * from test into outfile '/data/mysql/outfile2.txt' fields terminated by "," optionally  enclosed by '"';Query OK, 4 rows affected (0.03 sec)zj@bogon:/data/mysql$ more outfile2.txt1,"a","helloworld"2,"b","helloworld"3,"c","helloworld"4,"d","helloworld"

測試轉義字符，MySQL 導出數據中需要轉義的字符主要包括以下 3 類：

1、轉義字符本身 2、字段分隔符 3、記錄分隔符

MySQL [t2]> update test set content = '\"##!aa' where  id=1;Query OK, 1 row affected (0.05 sec)Rows matched: 1  Changed: 1  Warnings: 0MySQL [t2]> select * from test into outfile '/data/mysql/outfile3.txt' fields terminated by "," optionally enclosed by '"';Query OK, 4 rows affected (0.03 sec)*******************************************zj@bogon:/data/mysql$ more outfile3.txt1,"a","\"##!aa"2,"b","helloworld"3,"c","helloworld"4,"d","helloworld"

1、當導出命令中包含字段引用符時，數據中含有轉義字符本身和字段引用符的字符需要被轉義；

2、當導出命令中不包含字段引用符時，數據中含有轉義字符本身和字段分割符的字符需要被轉義。

注意：select ... into outfile ... 產生的輸出文件如果在目標目錄下有重名文件，將不會被創建成功，源文件不會被自動覆蓋。

使用 mysqldump 導出數據為文本的具體語法如下：

mysqldump -u username -T target_dir dbname tablename [option]

其中，option 參數可以是以下選項：

1、--fields-terminated-by=name (字段分隔符)；

2、--fields-enclosed-by=name (字段引用符)；

3、--fields-optionally-enclosed-by=name (字段引用符，只用在 char、varchar 和 test 等字符型字段上)；

4、--fields-escaped-by=name (轉義字符);

5、--lines-terminated-by=name (記錄結束符);

例子：

root@bogon:/usr/local/mysql/bin# ./mysqldump -uroot -p -T /data/mysql/dump t2 test --fields-terminated-by ',' --fields-optionally-enclosed-by '"'**************** test.txt **********************zj@bogon:/data/mysql/dump$ more test.txt1,"a","\"##!aa"2,"b","helloworld"3,"c","helloworld"4,"d","helloworld"***************** test.sql *********************zj@bogon:/data/mysql/dump$ more test.sql-- MySQL dump 10.13  Distrib 5.7.18, for linux (x86_64)---- Host: localhost    Database: t2-- -------------------------------------------------------- Server version    5.7.18-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8mb4 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `test`--DROP TABLE IF EXISTS `test`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `test` (  `id` int(11) DEFAULT NULL,  `name` varchar(10) DEFAULT NULL,  `content` varchar(100) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client */;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2017-09-25 11:14:06

可以發現，除多了一個表的創建腳本文件，mysqldump 和 select ... into outfile ... 的選項和語法非常相似。其實 mysqldump 實際調用的就是后者提供的接口，并在其上面添加了一些新的功能而已。

2. 導入

導入用 select ... into outfile 或者 mysqldump 導出的純數據文本

和導出類似，導入也有兩種不同的方法，分別是 load data infile... 和 mysqlimport，它們的本質是一樣的，區別只是在于一個在 MySQL 內部執行，另一個在 MySQL 外部執行。

使用 “load data infile...” 命令，具體語法如下

mysql> load data [local]infile 'filename' into table tablename [option]  

option 可以是以下選項：

1、fields terminated by 'string' (字段分割符，默認為制表符't');

2、fields [optionally] enclosed by 'char' (字段引用符，如果加 optionally 選項則只用在 char varchar text 等字符型字段上。默認不使用引用符)；

3、fields escaped by 'char' (轉義字符，默認為'')

4、lines starting by 'string' (每行前都加此字符串，默認為'')

5、lines terminated by 'string' (行結束符，默認為'n')

6、ignore number lines (忽略輸入文件中的前幾行數據)

7、(colnameoruservar,...) (按照列出的字段順序和字段數量加載數據)；8、set col_name = expr,...將列做一定的數值轉換后再加載。

fields 、lines 和前面 select...into outfile...的含義完全相同，不同的是多了幾個不同的選項，下面的例子將文件'test.txt'中的數據加載到表 test 中:

// 清空表 test  MySQL [t2]> truncate table test;Query OK, 0 rows affected (0.07 sec)MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"';Query OK, 4 rows affected (0.10 sec)Records: 4  Deleted: 0  Skipped: 0  Warnings: 0MySQL [t2]> select * from test;+------+------+------------+| id   | name | content    |+------+------+------------+|    1 | a    | helloworld ||    2 | b    | helloworld ||    3 | c    | helloworld ||    4 | d    | helloworld |+------+------+------------+4 rows in set (0.00 sec)

如果不希望加載文件中的前兩行，可以進行如下操作：

MySQL [t2]> truncate table test;Query OK, 0 rows affected (0.02 sec)MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"' ignore 2 lines;Query OK, 2 rows affected (0.00 sec)Records: 2  Deleted: 0  Skipped: 0  Warnings: 0MySQL [t2]> select * from test;+------+------+------------+| id   | name | content    |+------+------+------------+|    3 | c    | helloworld ||    4 | d    | helloworld |+------+------+------------+2 rows in set (0.02 sec)

使用 mysqldump 實現

語法：

shell> mysqlimport -uroot -p [--local] dbname order_tab.txt [option]

其中，option 參數可以是以下選項：

1、fields-terminated-by=name (字段分隔符) 2、fields-enclosed-by=name (字段引用符) 3、fields-optionally-enclosed-by=name (字段引用符，只用在 char、varchar、text等字符型字段上) 4、fields-escaped-by=name (轉義字符) 5、lines-terminated-by=name (記錄結束符) 6、ignore-lines=number (忽略前幾行)

注意：如果導入和導出是跨平臺操作的（windows 和 linux），那么要注意設置參數 line-terminated-by，windows 上設置為 line-terminated-by='rn', linux 上設置為 line-terminated-by='n'。