---

使用 mysqldump 將資料庫 test 備份到 /home/knuckles/test.sql
然後把備份的資料庫檔裝到另一台資料庫主機 db2 上

將資料庫 test 輸出成 test.sql 檔，執行後需要輸入 MySQL 的 root 密碼
$ mysqldump -u root -p test > /home/knuckles/test.sql

使用 gzip 壓縮為 test.sql.gz 檔
$ gzip /home/knuckles/test.sql

用 scp 將檔案傳送到 db2 去
$ scp /home/knuckles/test.sql.gz knuckles@db2:/home/knuckles/

登入目標主機 db2

解壓縮
$ gzip -d /home/knuckles/test.sql.gz

目標站要先建立一個空的資料庫 test

還原
$ mysql -u root -p test < /home/knuckles/test.sql


===參數===


若有使用 log_bin ，加上 --flush-logs 會先將目前新的 Binary Log 先存好後再開始備份

若編碼有使用 utf8mb4 要加上參數 --default-character-set=utf8mb4
mysql 8 以上的話預設就是用 utf8mb4


◎ 備份所有資料庫

使用 --all-databases 可備份所有資料庫

$ mysqldump -u root -p --all-databases > full.sql

還原時可用
$ mysql -u root -p test < full.sql
還原其中一個資料庫

只要還原其中一個資料表 mytable 的話，可以用
$ sed -n -e '/DROP TABLE.*mytable/,/UNLOCK TABLES/p' full.sql > mytable.sql

將該資料表的部份從 full.sql 中截取出來，再用
$ mysql -u root -p test < mytable.sql
加進資料庫


◎ 備份或排除某個資料表

例如只想要備份資料庫 test 中的資料表 table1 和 table2 時，可以用
$ mysqldump -u root -p test table1 table2 > /home/knuckles/test.tables.sql

想要備份資料庫 test，但想略過其中的資料表 table3 和 table4 時，可以用
$ mysqldump -u root -p test --ignore-table=test.table3 --ignore-table=test.table4 > /home/knuckles/test.tables.sql



====錯誤解決記錄====


若出現 ERROR 1153 (08S01) at line 541: Got a packet bigger than 'max_allowed_packet' bytes
應該是單一筆 query/insert/update 的資料長度超過 max_allowed_packet (預設1MB)

修改mySQL設定檔，將 max_allowed_packet 為1G (最大只能設到1G)
$ vim /etc/my.cnf

[mysqld]
max_allowed_packet = 1G

[mysqldump]
max_allowed_packet = 1G
single-transaction # 加這個備份時會鎖住表


如果資料表太大，設定 max_allowed_packet = 1G 也不行時，
可能記憶體不足，可調高 swap 大小再試試

或是在 mysqldump 加上參數 --skip-extended-insert
將一筆資料用一個 insert 指令插入，而不會將多筆資料組成一個 insert 指令
產生的 sql 檔會比較大，且還原資料時會慢很多



=====編輯記錄=====
加參數 --max_allowed_packet=1G 好像沒有用
$ mysql -u root -p --max_allowed_packet=1G test < /root/test.sql




====參考====
http://mic1491.pixnet.net/blog/category/1658469


--
※ 作者: Knuckles 時間: 2012-06-29 15:24:56
※ 編輯: Knuckles 時間: 2024-03-22 22:00:53 (台灣)