【邏輯備份】深入淺出mysqldump：常用操作、案例分享、意外終止的原因以及解決方法 ㈠ 常用操作 ① 備份全庫 語法： mysqldump -h主機名 -P端口 -u用戶名 -p密碼 (–database) 數據庫名 > 文件名.sql 例子： mysqldump -hlocalhost -P3306 -urocky -p123456 db_test > bakfile1.sql ② 帶刪除表的全庫備份 備份MySQL數據庫為帶刪除表的格式、能夠讓該備份覆蓋已有數據庫而不需要手動刪除原有數據庫 語法： mysqldump -–add-drop-table -u{username} -p{password} {databasename} > {backfile.sql} 例子： mysqldump -–add-drop-table –urocky -p123456 db_test > bakfile2.sql ③ 壓縮備份 語法： mysqldump -h{hostname} -u{username} -p{password} {databasename} | gzip > {backfile.sql.gz} 例子： mysqldump –hlocalhost –urocky –p123456 db_test | gzip > bakfile3.sql.gz ④ 備份某些表 語法： mysqldump -h主機名 -P端口 -u用戶名 -p密碼 (–tables | –quick) 數據庫名 表名1 (表名2 …) > 文件名.sql 例子： mysqldump -hlocalhost -urocky -p123456 db_test tbl_test > bakfile4-1.sql mysqldump -hlocalhost -P3306 -urocky -p123456 db_test tbl_test > bakfile4-2.sql mysqldump -hlocalhost -P3306 -urocky -p123456 --quick db_test tbl_test > bakfile4-3.sql mysqldump -hlocalhost -P3306 -urocky -p123456 --tables db_test tbl_test1 tbl_test2 > bakfile4-4.sql ⑤ 同時備份多個庫 語法： mysqldump -h{hostname} (-P{port}) -u{username} -p{password} –databases {dbname1} {dbname2} {dbname3} > multibackfile.sql 例子： mysqldump -hlocalhost -urocky -p123456 –databases db_test1 db_test2 db_test3 > multibackfile.sql ⑥ 備份服務器上的所有數據庫 語法： mysqldump –all-databases > allbackupfile.sql ⑦ 僅僅備份數據庫結構 語法： mysqldump –no-data –databases {databasename1} {databasename2} > {structurebackfile.sql} 例子： mysqldump –no-data –databases db_test1 db_test2 > structurebackfile.sql ⑧ 導出某個表的部分數據 語法： mysqldump -u用戶名 -p密碼 數據庫名 表名 --where="篩選條件" > 導出文件路徑 例子： mysqldump -uroot -p123456 test test_data --where=" id > 100" > /tmp/test.sql ㈡ 案例分享 ① 案例描述： mysqldump命令常規方式創建備份拉到某機器上恢復、恢復執行很成功、一條錯誤信息都沒看著 但等恢復完登錄到數據庫中一瞅、你猜怎麼地、數據不全 第一反應當然是查看備份文件、經過檢查、果然、恢復操作確實沒有問題、 因為備份集中的內容就不全，那麼，為什麼備份集內容不全 ② 原因分析： 分析發現、原來是在導出某個視圖對象時報錯、mysqldump自動中止、因此所有該對象之後的就都沒備份了 ③ 場景模擬回放： [plain] Session_A： mysql> use test; Database changed mysql> create table rocky (id int,name varchar(100)); Query OK, 0 rows affected (0.04 sec) mysql> create view rocky_view as select * from rocky; Query OK, 0 rows affected (0.01 sec) mysql> rename table rocky to robbin; Query OK, 0 rows affected (0.03 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) Session_B： [mysql@localhost bin]$ ./mysqldump --tables test robbin rocky_view > bak.sql mysqldump: Got error: 1356: View 'test.rocky_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when doing LOCK TABLES 創建備份時、view對象引用的表對象不存在、執行LOCK TABLES失敗、於是mysqldump就中止了 這其實真不怪 mysqldump、因為mysqldump執行過程中遇到任何問題、默認情況下都是直接退出 ④ 解決方案： 執行mysqldump時附加--force參數、該參數功能是當遇到錯誤時忽略、繼續執行後面的操作 這個參數提供類似 Oracle 數據庫中exp命令的ignore=y參數的功能、 事實上在 Oracle 數據庫中執行exp時通常都會指定ignore、 對應到MySQL數據庫、我想在執行mysqldump命令行過程中、--force參數也應做為必備參數調用 ㈢ mysqldump意外終止的原因以及解決方法 ① 錯誤現象： Lost connection to MySQL server at ‘reading initial communication packet’： 原因分析： 因為DNS不穩定導致的 解決方案： 開啟skip-name-resolve選項將會最大程度避免這個問題 ② 錯誤現象： Lost connection to MySQL server at ‘reading authorization packet’： 原因分析： 從MySQL獲取一個可用的連接是多次握手的結果。在多次握手的過程中，網絡波動會導致握手失敗 解決方案： 最好的解決辦法是讓mysqldump重新發起連接請求 ③ 錯誤現象： Lost connection to MySQL server during query 原因分析： mysqldump處理數據過慢（NFS、gzip引起）會導致MySQL主動斷開連接 解決方案： 加大net_write_timeout的設置
