MySQL 的備份與復原

在 linux 系統上有兩種方法

方法一

備份：

將 MySQL 停下來，再把存資料的地方整個帶走。
MySQL 存資料的地方在 /usr/local/mysql/data/[資料庫名稱]/。 只要把這地方的資料複製一份存起來就可以。



復原：

將資料放回原來的地方。

風險：

使用的 MySQL 版本要與備份時的版本一樣，比較不會有不可預期的狀況發生。


方法二

備份：

不需要停下 MySQL，利用 mysqldump 來備份資料，做法如下...

mysqldump --user=[資料庫使用者] -p --default-character-set=utf8 [資料庫名稱] > [備份檔名].sql

如果要將全部資料庫備份，做法如下...

mysqldump --user=[資料庫使用者] -p --all-databases > [備份檔名].sql


復原：

製作新的資料庫用來擺放復原的資料，做法如下...

mysql --user=[資料庫使用者] -p --default-character-set=utf8 [資料庫名稱] < [備份檔名].sql

如果要將全部資料庫復原，做法如下...

mysql --user=[資料庫使用者] -p --all-databases < [備份檔名].sql

當然以新的乾淨的資料庫來放資料是比較好的，做法如下...

mysql --user=[資料庫使用者] -p -e "DROP DATABASE IF EXISTS `database_name`;"
mysql --user=[資料庫使用者] -p -e "CREATE DATABASE `database name` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;"


如果想要順便壓縮的 gz 檔，可以稍微改一下指令

mysqldump --user=Username  -p DatabaseName | gzip > BackupDB.sql.gz

如果要 Restore 回來

gunzip < BackupDB.sql.gz | mysql [mysql option]

補充

mysqldump 的使用方法

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3 ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

Default options 會從下列檔案依先後次序尋找並讀取
/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf

常用的 OPTIONS 有

-?, --help
得到使用 mysqldump 的詳細使用方法。

--add-locks
在 insert 語句區塊前後增加 lock/unlock 描述。

--add-drop-table
在每個 create 語句之前增加 drop table 描述。

--create-options
引入 MySQL 指定的所有的 create options。

-K, --disable-keys
在輸出資料裡加入下列兩行
/*!40000 ALTER TABLE tb_name DISABLE KEYS */;
/*!40000 ALTER TABLE tb_name ENABLE KEYS */;

-e, --extended-insert
使用新的比較快速的 INSERT 語法。

-x, --lock-all-tables
在 dump 資料期間使用 global read lock 鎖住所有資料庫裡的所有資料表。該 option 會自動關閉 --single-transaction 和 --lock-tables 的功能。

-l, --lock-tables
鎖住資料庫裡所有資料表來讀取資料。

-q, --quick
不緩沖，直接導出至 stdout。

--opt
同 --add-drop-table --add-locks --create-options --quick --extended-insert --lock-tables --set-charset --disable-keys。預設是開啟的，可以使用 --skip-opt 來關閉。應該是用來盡快導出資料的設定。

--set-charset
在輸出資料裡加入 "SET NAMES default_character_set"。預設是開啟的，可以使用 --skip-set-charset 來關閉。

--single-transaction
確保導出一致性的資料。目前只有 InnoDB Engine 支援。當 --single-transaction 運作時，若資料庫沒有停下來，其他資料庫連線不可以使用 ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE。該 option 會自動關閉 --lock-tables。