MySQL備份和恢復 · php開發筆記

#### 一、備份恢復策略進行備份或恢復操作時需要考慮一些因素 1、確定要備份的表的存儲引擎是事務型還是非事務型，兩種不同的存儲引擎備份方式在處理數據一致性方面是不太一樣的。 2、確定使用全備份還是增量備份。全備份的優點是備份保持最新備份，恢復的時候可以花費更少的時間；缺點是如果數據量大，將會花費很多的時間，并對系統造成較長時間的壓力。增量備份相反，只需要備份每天的增量日志，備份時間少，對負載壓力也小；缺點就是恢復的時候需要全備份加上次備份到故障前的所有日志，恢復時間長一些。 3、可以考慮采用復制的方法來做異地備份，但不能代替備份，它對數據庫的誤操作也無能為力。 4、要定期做備份，備份的周期要充分考慮系統可以承受的恢復時間。備份要在系統負載較小的時候進行。 5、確保 MySQL 打開 log-bin 選項，有了 binlog，MySQL 才可以在必要的時候做完整恢復，或基于時間點的恢復，或基于位置的恢復。 6、經常做備份恢復測試，確保備份是有效的，是可以恢復的。 #### 二、binlog MySQL中的日志比較重要的有binlog（歸檔日志）、redo log（重做日志）以及undo log。binlog我們中文一般稱作歸檔日志，當我們搭建MySQL主從的時候就離不開binlog。 binlog是MySQL Server層的日志，而不是存儲引擎自帶的日志，它記錄了所有的 DDL和DML(不包含數據查詢語句)語句，而且是以事件形式記錄，還包含語句所執行的消耗的時間等，需要注意的是： * binlog是一種邏輯日志，他里邊所記錄的是一條SQL語句的原始邏輯，例如給某一個字段 +1，注意這個區別于redo log的物理日志（在某個數據頁上做了什么修改）。 * binlog文件寫滿后，會自動切換到下一個日志文件繼續寫，而不會覆蓋以前的日志，這個也區別于redo log，redo log是循環寫入的，即后面寫入的可能會覆蓋前面寫入的。 * 一般來說，我們在配置binlog的時候，可以指定binlog文件的有效期，這樣在到期后，日志文件會自動刪除，這樣避免占用較多存儲空間。根據MySQL官方文檔的介紹，開啟binlog之后，大概會有1%的性能損耗，不過這還是可以接受的，一般來說，binlog有兩個重要的使用場景： * MySQL主從復制時：在主機上開啟binlog，主機將binlog同步給從機，從機通過binlog來同步數據，進而實現主機和從機的數據同步。 * MySQL數據恢復，通過使用mysqlbinlog 工具再結合binlog文件，可以將數據恢復到過去的某一時刻。 #### 三、邏輯備份和恢復在MySQL中，邏輯備份的最大優點是對于各種存儲引擎都可以用同樣的方法來備份；而物理備份則不同，不同的存儲引擎有著不同的備份方法，因此，對于不同存儲引擎混合的數據庫，邏輯備份會簡單一點。 ##### 3.1 備份 ###### 3.1.1 語法 MySQL中的邏輯備份是將數據庫中的數據備份為一個文本文件，備份的文件可以被查看和編輯。在MySQL中，可以使用mysqldump工具來完成邏輯備份。 ~~~sql // 備份指定的數據庫或者數據庫中的某些表 shell> mysqldump [options] db_name [tables] // 備份指定的一個或多個數據庫 shell> mysqldump [options] --database DB1 [DB2,DB3...] // 備份所有數據庫 shell> mysqldump [options] --all-database ~~~ 如果沒有指定數據庫中的任何表，默認導出所有數據庫中的所有表。 ###### 3.1.2 示例 1、備份所有數據庫 ~~~sql shell>mysqldump -uroot -p --all-database > all.sql ~~~ 2、備份數據庫 test ~~~sql shell>mysqldump -uroot -p test > test.sql ~~~ 3、備份數據庫test下的表emp ~~~sql shell> mysqldump -uroot -p test emp > emp.sql ~~~ 4、備份數據庫test下的表emp和dept ~~~sql shell> mysqldump -uroot -p test emp dept > emp_dept.sql ~~~ 5、備份數據庫test下的所有表為逗號分割的文本，備份到/tmp ~~~sql 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)，使得備份的數據能夠保證一致性。 ##### 3.2 完全恢復 ###### 3.2.1 語法 mysqldump的恢復也很簡單，將備份作為輸入執行即可 ~~~sql mysql -uroot -p db_name < backfile ~~~ 注意，將備份恢復后數據并不完整，還需要將備份后執行的日志進行重做 ~~~sql mysqlbinlog binlog-file | mysql -uroot -p ~~~ ###### 3.2.2 完整的mysqldump備份與恢復示例 1、凌晨2:00，備份數據庫 ~~~sql root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp Enter password: ~~~ 其中-l參數表示給所有表加讀鎖，-F表示生成一個新的日志文件，此時，t2中emp表的數據如下： ~~~sql # 為了便于測試，執行 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、備份完畢后，插入新的數據 ~~~sql # 因為上一步執行是加入了 -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、刪庫跑路:數據庫突然故障，數據無法訪問，需要恢復備份 ~~~sql # 這里為了便于測試，不把刪庫操作記入日志，當前 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; ~~~ 4、數據恢復 ~~~sql 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) ~~~ 5、使用mysqlbinlog恢復自mysqldump備份以來的binglog 根據前面操作的內容，可知從備份的時間點到刪庫的時間點之間的操作被記錄到了mysql-bin.000002文件中 ~~~sql 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.3 基于時間點恢復由于誤操作，比如誤刪除了一張表，這時使用完全恢復時沒有用的，因為日志里面還存在誤操作的語句，我們需要的是恢復到誤操作之前的狀態，然后跳過誤操作語句，再恢復后面執行的語句，完成恢復。這種恢復叫不完全恢復，在MySQL中，不完全恢復分為基于時間點的恢復和基于位置的恢復。基于時間點恢復的操作步驟： 1、如果是上午 10 點發生了誤操作，可以用以下語句用備份和 binlog 將數據恢復到故障前 ~~~sql shell>mysqlbinlog --stop-date="2017-09-30 9:59:59" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword ~~~ 2、跳過故障時的時間點，繼續執行后面的 binlog，完成恢復 ~~~sql shell>mysqlbinlog --start-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword ~~~ ##### 3.4 基于位置恢復和基于時間點的恢復類似，但是更精確，因為同一個時間點可能有很多條sql語句同時執行。恢復的操作步驟如下： 1、在shell下執行命令 ~~~sql 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、恢復了以前的備份文件后，應從命令行輸入下面的內容 ~~~sql 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日志將反映事務執行的原時間。 #### 四、表的導入導出 ##### 4.1 導出在某些情況下，為了一些特定的目的，經常需要將表里的數據導出為某些符號分割的純數據文本，而不是sql語句： * 用來作為Excel顯示。 * 單純為了節省備份空間。 * 為了快速的加載數據，load data的加載速度比普通sql加載要快20倍以上。 ###### 4.1.1 使用select ...into outfile ... 命令來導出數據使用select ...into outfile ... 命令來導出數據，具體語法如下 ~~~sql mysql> select * from tablename into outfile 'target_file' [option]; ~~~ 其中option參數可以是以下選項 ~~~sql 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表中數據導出為數據文本，其中，字段分隔符為“,”,字段引用符為“"”,記錄結束符為回車符 ~~~sql MySQL [t2]> select * from test into outfile '/data/mysql/outfile.txt' fields terminated by "," enclosed by '"';Query OK, 4 rows affected (0.02 sec) ~~~ ~~~sql zj@bogon:/data/mysql$ more outfile.txt"1","a","helloworld""2","b","helloworld""3","c","helloworld""4","d","helloworld" ~~~ 發現第一列是數值型，如果不希望字段兩邊用引號引起，則語句改為 ~~~sql 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" ~~~ ###### 4.1.2 轉義字符測試轉義字符，MySQL導出數據中需要轉義的字符主要包括以下3類： 1、轉義字符本身 2、字段分隔符 3、記錄分隔符 ~~~sql 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" ~~~ 注意：`select ... into outfile ...`產生的輸出文件如果在目標目錄下有重名文件，將不會被創建成功，源文件不會被自動覆蓋。 ###### 4.1.3 使用mysqldump導出數據為文本使用mysqldump導出數據為文本的具體語法如下 ~~~sql mysqldump -u username -T target_dir dbname tablename [option] ~~~ 例子 ~~~sql 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實際調用的就是后者提供的接口，并在其上面添加了一些新的功能而已。 ##### 4.2 導入導入用`select ... into outfile`或者mysqldump導出的純數據文本。和導出類似，導入也有兩種不同的方法，分別是`load data infile...`和mysqlimport，它們的本質是一樣的，區別只是在于一個在MySQL內部執行，另一個在MySQL外部執行。 ###### 4.2.1 使用load data infile...命令使用load data infile...命令，具體語法如下 ~~~sql mysql> load data [local]infile 'filename' into table tablename [option] ~~~ ###### 4.2.2 使用mysqldump實現語法 ~~~sql shell> mysqlimport -uroot -p [--local] dbname order_tab.txt [option] ~~~ 注意：如果導入和導出是跨平臺操作的（windows 和 linux），那么要注意設置參數`line-terminated-by`，windows上設置為`line-terminated-by='rn'`, linux上設置為`line-terminated-by='n'`。