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


概要

手順

事前準備

  • --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