MySQLのバックアップとレストア

MySQLでデータをダンプする方法は各種いろいろあるのでまとめ。

SQL文でデータを書き出す

$ mysqldump -u [ユーザ名] -p[パスワード] --opt -Q [データベース名] [テーブル名] > [ダンプするファイル名]

--optを指定してやると、テーブルのデッドロックを回避しながらエクスポートを行ってくれる。ただし全てのテーブルがlockされるので大きすぎるデータベースだとアクセスできない時間が長くなる。-Qはテーブル名、カラム名を'`'(バッククォート)で囲んでくれる。

また、文字コード指定のオプションは --default-character-set=ujis。これを指定してやらないと文字化けるケースもある。

ex) # mysqldump -u yuki2021 -pyuki_passwd --opt bbs_db > bbs_dump.sql

INSERT構文のみ出力する

$ mysqldump -u [ユーザ名] -p[パスワード] --opt -t [データベース名] > [ダンプするファイル名]

-tを指定してやるとCREATE TABLEを出力せずにエクスポートを行う。--no-create-infoを指定するのと同じです。

CREATE TABLE構文のみ出力する

$ mysqldump -u [ユーザ名] -p[パスワード] --opt -d [データベース名] > [ダンプするファイル名]

-dを指定してやるとINSERT構文を出力せずにエクスポートを行います。--no-dataを指定するのと同じです。

はじめにバックアップを行う時や、データベースの構造を変えたときにはCREATE TABLE構文のSQLを出力して、日常的なバックアップにはINSERT構文のデータを出力するのが効率的です。

SQL文よりデータベースへレストア

# mysql -u [ユーザ名] -p[パスワード] [データベース名] < [ダンプファイル名]