mysqldumpをするときによく使うオプションとリストア方法

概要

• mysqldumpする時に色々と気をつける必要があるけど結構忘れがちなのでメモ
• オプションは、mysql5.6公式リファンレスに記載の内容をベースとして設定。
  • https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html

手順

事前準備

• --default-character-setでどの文字コードで mysqldump を取得するか、対象 DB の現在の文字コードを確認する

$ mysql -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOST} -e 'show variables like "character_set_database";'

mysqldump取得

圧縮しない時

• テーブル単位の取得の場合

$ mysqldump -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOST} --single-transaction --default-character-set=${CHARASET} --routines ${DB_NAME} ${TABLE_NAME_1} ${TABLE_NAME_2} > hogehoge_`date +%Y%m%d`.sql

• DBスキーマ単位で取得の場合

$ mysqldump -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOST} --single-transaction --default-character-set=${CHARASET} --routines --add-drop-database --databases ${DB_NAME} > hogehoge_`date +%Y%m%d`.sql

圧縮する時

• テーブル単位の取得の場合

$ mysqldump  -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOST} --single-transaction --default-character-set=${CHARASET} --routines ${DB_NAME} ${TABLE_NAME_1} ${TABLE_NAME_2} | gzip > hogehoge_`date +%Y%m%d`.sql.gz

• DBスキーマ単位で取得の場合

$ mysqldump -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOST} --single-transaction --default-character-set=${CHARASET} --routines --add-drop-database --databases ${DB_NAME} | gzip > hogehoge_`date +%Y%m%d`.sql.gz

各オプションの内容

single-transaction

• 対象がinnodbのときのみ有効（myIsamでは効果がない）
• dump取得時にテーブルロックをしないため、更新時の待ちが発生しない。

default-character-set

• dump時の文字コードを指定する。
• 指定した文字列に変換してdumpを作成する。
• 指定しない場合、サーバー側で指定されているデフォルト文字コードが自動指定されるため、取得側とリストア側で文字コードのデフォルトが違う場合には、文字化けが起きる可能性が高まる。

routines

• ストアドルーチン (プロシージャーおよび関数) も一緒にdumpに含める。

add-drop-database

• 指定したDBスキーマをdrop database（削除）するクエリをdumpに含める。

リストア方法

TABLEのリストア

$ mysql -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOST}  -D ${DB_NAME} < hogehoge_`date +%Y%m%d`.sql

DBのリストア

$ mysql -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOST} < hogehoge_`date +%Y%m%d`.sql