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

安裝

以 Ubuntu 為例:

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

進階應用

default-character-set 編碼

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

mysqldump --default-character-set=utf8

utf8 or utf8mb4

總言之,utf8mb4 才是完成支援 UTF-8 標準,原本的 utf8 charset 只支援到最多 3 bytes UTF-8 character,4 bytes UTF-8 如𡘙。以下為修改欄位編碼 SQL 範例:

ALTER TABLE database.table MODIFY COLUMN col VARCHAR(255)  
    CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

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,此參數為全域無法個別設定

flush-log

  • --flush-log

下dump後目標資料庫的Bin log將會停止並產生新的下一個Log,適合用於差異備份指定資料庫還原點。

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

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

Leave a Reply

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