mysql之資料備份還原

標籤：mysql.navicat   備份還原   

情境：

①由於許可權控制，遠端資料庫在外網不能訪問；

②遠端資料庫串連和查詢比較慢，影響工作效率；

③即將發布生產時，驗證資料庫更新指令碼是否無遺漏（如欄位是否完整）；

分析：

對於①、②，可以將遠端資料庫的表結構和已有資料COPY到本地的mysql伺服器，並將配置的資訊指向本地的mysql串連即可。

對於③還需要運行資料庫更新指令碼，並測試一遍新增功能，檢查是否無異常；

操作（基於navicat for mysql）

方法一【推薦】

1、首先開啟資料庫連接；

2、右鍵資料庫，選擇【資料轉送】，如下；

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M00/8A/A3/wKioL1g2hX_SKzZLAACwdAVAlDg593.png" title="QQ20161124140813.png" alt="wKioL1g2hX_SKzZLAACwdAVAlDg593.png" />

3、設定傳輸屬性；

注意：

（1）常規-要根據需要勾選對應的資料庫物件；

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

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M01/8A/A8/wKiom1g2hZix7vRSAAD5NtXzUzM825.png" title="QQ20161124140913.png" alt="wKiom1g2hZix7vRSAAD5NtXzUzM825.png" />

（3）進階-根據需要選擇只匯出表結構、只匯出資料、匯出表和資料；

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

（5）點擊[開始] 按鈕開始匯出資料；

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M01/8A/A4/wKioL1g2hgqQEgKiAAEhQJimwD4555.png" title="QQ20161124140946.png" alt="wKioL1g2hgqQEgKiAAEhQJimwD4555.png" />

4、還原/匯入剛剛傳輸/匯出的表及資料/sql

右鍵資料庫，選擇【運行sql檔案】

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M01/8A/A4/wKioL1g2h-nyFSB4AACk8dmiCL4448.png" title="QQ20161124141032.png" alt="wKioL1g2h-nyFSB4AACk8dmiCL4448.png" />

優點：可根據實際需要直接傳輸結構和資料 或者 匯出對應的sql檔案，很靈活，實用性很強；

缺點：操作繁瑣；

方法二

【轉儲SQL檔案】是直接匯出整個資料庫的sql語句；

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

（2）此功能匯出的資料插入資料，是使用【擴充插入語句】；

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M00/8A/A4/wKioL1g2iFmxUay-AACjelelO8w332.png" title="QQ20161124141058.png" alt="wKioL1g2iFmxUay-AACjelelO8w332.png" />

優點：一鍵匯出；

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

（3）匯入sql

參照方法一的操作；

方法三

使用navicat for mysql內建的備份、還原功能

根據備份嚮導操作即可；

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M01/8A/A8/wKiom1g2i1TgNQlwAAFYpsvI2CE018.png" title="QQ20161124141207.png" alt="wKiom1g2i1TgNQlwAAFYpsvI2CE018.png" />

優點：備份速度很快；

缺點：備份包只能在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）網路要良好，不能中斷，否則會報錯如下；

[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‘);

後果就是之前的資料插進去了，後面的沒有，需要單獨插入資料；

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

本文出自 “ydhome” 部落格，請務必保留此出處http://ydhome.blog.51cto.com/8948432/1876167

mysql之資料備份還原