[MySQL] Mysqldump 備份&還原資料庫 – 指令範例

安裝

apt-get install mysql-client-*

版本自行查閱,以下為5.1版本範例

apt-get install mysql-client-5.1

指令範例-備份

備份使用mysqldump

備份單一資料庫

mysqldump -h hostname -u root -p database_name > backup.sql;

備份資料庫中單一資料表

# mysqldump -u root -p database_name table_name > backup.sql;

備份資料庫中多張資料表

# mysqldump -u root -p database_name table1 table2 > backup.sql;

備份多個資料庫

# mysqldump -u root -p --databases db1 db2 > backup.sql;

備份所有資料庫

# mysqldump -u root -p --all-databases > backup.sql;

指令範例-復原

復原使用mysql

復原單一資料庫

# mysql -u root -p database_name < backup.sql

復原多個資料庫

# mysql -u root -p < backup.sql

因為新版mysqldump預設會使用UTF8,所以還原較沒問題, 若為舊版的mysqldump, 則需要使用–default-character-set 指定字集


進階應用

Ignore-table 濾除指定Table:

  • --ignore-table

    mysqldump -u username -p database --ignore-table=database.table1 > database.sql

    多個時重複宣告Flag定義Table

No-data (Schema only):

  • --no-data, -d

    mysqldump -d database > database.sql

    多個時重複宣告Table,此參數為全域無法個別設定

max_allowed_packet:

當一語句大於max_allowed_packet將造成錯誤:
ERROR 2006 (HY000) at line 653: MySQL server has gone away

  • 方法一:增大max_allowed_packet

    [my.cnf or ini]
    max_allowed_packet=64M
  • 方法二:使用逐筆insert dump

    如果不嫌匯入變慢的話,基本解法就是INSERT語句拆開:

    mysqldump --extended-insert=FALSE

    or

    mysqldump --skip-extended-insert

    但還是要考量單筆語句max_allowed_packet

  • innodb_log_file_size

    另外可能遇到innodb_log_file_size也需要調大的狀況:
    ERROR 1118 (42000) at line: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

    [mysqld]
    innodb_log_file_size=256M

Leave a Reply

Your email address will not be published. Required fields are marked *