mysql之數據備份還原

場景：

①由于權限控制，遠程數據庫在外網不能訪問；

②遠程數據庫連接和查詢比較慢，影響工作效率；

③即將發布生產時，驗證數據庫更新腳本是否無遺漏（如字段是否完整）；

分析：

對于①、②，可以將遠程數據庫的表結構和已有數據COPY到本地的mysql服務器，并將配置的信息指向本地的mysql連接即可。

對于③還需要運行數據庫更新腳本，并測試一遍新增功能，檢查是否無異常；

操作（基于navicat for mysql）

方法一【推薦】

1、首先打開數據庫連接；

2、右鍵數據庫，選擇【數據傳輸】，如下；

3、設置傳輸屬性；

注意：

（1）常規-要根據需要勾選對應的數據庫對象；

（2）常規-如果目標是選擇【文件】，則注意選擇編碼，我這里是UTF-8；

（3）高級-根據需要選擇只導出表結構、只導出數據、導出表和數據；

（4）高級-【強烈建議】勾選使用完整插入語句；（因為如果你導出了數據，后面改了表結構等，需要只還原數據的話，能使用完整的插入語句【insert into TableA （Row1,Row2，……） values（'1','2',……）】，如果使用擴展插入語句的話【insert into TableA  values（'1','2',……）】則插不進去，畢竟可能調整了字段的順序，增加/刪除了字段，導致對不上）；

（5）點擊“開始”按鈕開始導出數據；

4、還原/導入剛剛傳輸/導出的表及數據/sql

右鍵數據庫，選擇【運行sql文件】

優點：可根據實際需要直接傳輸結構和數據 或者 導出對應的sql文件，很靈活，實用性很強；

缺點：操作繁瑣；

方法二

【轉儲SQL文件】是直接導出整個數據庫的sql語句；

（1）如果要導出某個表的sql語句，則可在表名右鍵使用此功能；

（2）此功能導出的數據插入數據，是使用【擴展插入語句】；

優點：一鍵導出；

缺點：insert語句不是完整的插入語句，而是擴展性插入語句；

（3）導入sql

參照方法一的操作；

方法三

使用navicat for mysql自帶的備份、還原功能

根據備份向導操作即可；

優點：備份速度很快；

缺點：備份包只能在navicat中使用；

注意：

（1）導入sql時，一定要使用有足夠權限的用戶；

否則會報錯：

[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[Err] CREATE TABLE ……

（2）導出的DDL語句格式不是通用語句（通常是創建視圖，函數/儲存過程），如：

 CREATE ALGORITHM=UNDEFINED DEFINER=`User1`@`%` SQL SECURITY DEFINER VIEW `View1` AS select ……;

執行時會報錯：

[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[Err] CREATE ALGORITHM=UNDEFINED DEFINER=`User1`@`%` SQL SECURITY DEFINER VIEW `View1` AS select ……;

解決：將紅色部分去掉即可；

（3）如果表之間存在外鍵外鍵，那么在還原整個數據庫時，可能會報錯，如下；

[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`DtBase`.`mer_relation_log`, CONSTRAINT `mer_relation_log_ibfk_1` FOREIGN KEY (`MER_ID`) REFERENCES `mer_info` (`ID`))

[Err] INSERT INTO `mer_relation_log` VALUES ('20', '1', '2016-05-29 16:47:22', '976', '44', '42', null, '69');

解決：將源表數據先行還原即可；

（4）導入時報 Table 'DbTest.SYS_CONF' doesn't exist；

可能原因1是該表的確不存在；

可能原因2是mysql配置成字母大小寫敏感；（默認情況下，MySQL在windows下是不區分大小寫的，在linux服務器中是區分的；解決：在linux下更改MySQL的設置使其也不區分大小寫即可；

（5）網絡要良好，不能中斷，否則會報錯如下；

[Err] 2003 - Can't connect to MySQL server on '192.168.1.7' (10060)

[Err] INSERT INTO `ba_info` VALUES ('61445', '0', '2016-01-27 12:00:00', '403');

后果就是之前的數據插進去了，后面的沒有，需要單獨插入數據；

最好連接網線，而不是無線；