最終更新日: 2018-02-11
MySQL データベースのバックアップには、mysqldump を使うことが多いようである。 (他の手段もある)
Docker の場合には、だいたい以下のようにするのが良さそうである。 以下ではホスト側にも MySQL クライアント一式 (mysql) のインストールが必要である。
> mysqldump -u root -p --host=127.0.0.1 -x --all-databases --triggers --routines --events | xz -9 > foo.sql.xz
--host=127.0.0.1 を指定することにより、Docker のネットワークの MySQL
に対し操作している。
(うちでは --host=localhost だとうまくいかない (Docker 側の問題か)。)
--port=〜 を省略すると 3306 になる (--port=3306)。
-x により、全てのデータベースに渡り全てのテーブルをロックしている
(lock all tables across all databases)。
-x を付けると、--single-transaction (後述) が自動的に off になる。
(-x と --single-transaction の対比について
バックアップ時間?
の所を参照)
出力は SQL のテキストファイルになるので、ここではさらに xz コマンドで圧縮している。
ちなみに、-x には --lock-all-tables という長い形式もあって、--all-databases には -A という短い形式もあるのだが、何故か -x --all-databases という単・長の組み合わせで紹介しているサイトが多い。
場合によっては、これらのオプションがないと、警告が出る。
nsmrtks@fb103ufs:~ % date ; mysqldump -u foo -pパスワード -x --all-databases | xz -9 > mysqldump.sql.xz ; date
2018年 2月 6日 火曜日 17時02分27秒 JST
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that change
d suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass
--all-databases --triggers --routines --events.
2018年 2月 6日 火曜日 17時03分46秒 JST
データベースにストアドプログラムがある場合、それをダンプするにはさらに --events --routines --triggers のオプションが必要なのだという。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 7.4.5.3 ストアドプログラムのダンプ
【エンジニアブログ】mysqldumpのオプション（MySQL5.6）
例: foo_barbaz データベースのみをバックアップ
> mysqldump -u root -p --host=127.0.0.1 --single-transaction --triggers --routines --events foo_barbaz | xz -9 > foo_barbaz.sql.xz
(--single-transaction について バックアップ時間? の所を参照)
ちなみに、データベース名の後にさらにテーブル名を指定して、特定のデータベースの特定のテーブルのみバックアップすることも可能らしい。
また、複数の特定のデータベースを指定してバックアップする方法もあるようだ。
> xzcat foo.sql.xz | mysql -u root -p --host=127.0.0.1
このとき SQL の中では、INSERT に先立って DROP TABLE IF EXISTS `hoge` や CREATE TABLE `hoge` などなどを行っているので、もともとあったテーブルの内容は削除されてしまう。
> xzcat foo_barbaz.sql.xz | mysql -u root -p --host=127.0.0.1 foo_barbaz
上のように、データベース名の指定 (上の例では foo_barbaz) も必要である。
ということは、全てのデータベースをリストアする場合と違い、もしそのデータベースが既に存在していないといきなりエラーになる。
その場合、少なくとも create database データベース名; などの操作が必要。
-x は、データベースのすべてのテーブルをリードロックするというものであり、このとき参照系のクエリは発行できるが、更新系のクエリはロックが解除されるまで待ち状態となる。
(以降も含め
mysqlのバックアップ(mysqldump)のロック問題 を参考)
すると、バックアップにどれくらい時間がかかるかが特に問題となるが、 DBAのためのmysqldumpのtips 25選 では、「使用可能なメモリやHDDのスピードなどハードウェアによるが、mysqldumpに向いているのは5GBから20GBぐらいのデータベースだろう。」「100GBのデータベースなら2時間以上はかかるだろう」などと言っている。
-x に対し、データベース (テーブル?) が全て
InnoDB であれば、--single-transaction
を使ったダンプを行う事でロックせずにバックアップする事ができるという。
例: mysqldump -u root -p --single-transaction -A > my_dumpall.db (※)
(※) この mysqlのバックアップ(mysqldump)のロック問題 の例では -A (--all-databases と同じ) に -x でなく --single-transaction を指定している。 他の例で、--all-databases (-A) に --single-transaction でなく -x を指定している例ばかりなのは、全てのデータベースが InnoDB とは限らないからだろうか?
DBAのためのmysqldumpのtips 25選
にも、--single-transaction を「InnoDBでは常に付ける」とある。
MySQLのデータベースをmysqldumpでバックアップ/復元する方法
にも --single-transaction を常に付けた方が良いとある。
しかし、その中の -x と --single-transaction
を同時に指定している例では、--single-transaction
は無効になるのではないだろうか?
以下は単にバックアップ時間の比較である。
/var/lib/mysql の下が 201MB の状態 (この目安でよいかどうか分からないが) で、-x --all-databases 〜 でバックアップを取ると、5秒程度であった。
[nsmrtks@foohoge ~]$ date ; mysqldump -u root -pパスワード --host=127.0.0.1 --port=3306 -x --all-databases --triggers --routines --even
ts | xz -9 > foo.sql.xz ; date
Thu Feb 8 05:49:32 UTC 2018
Thu Feb 8 05:49:37 UTC 2018
-x の代わりに --single-transaction でバックアップを取ってみたところ、時間は変わらなかった。
[nsmrtks@foohoge ~]$ date ; mysqldump -u root -pパスワード --host=127.0.0.1 --port=3306 --single-transaction --all-databases --triggers
--routines --events | xz -9 > foo1.sql.xz ; date
Thu Feb 8 05:50:06 UTC 2018
Thu Feb 8 05:50:11 UTC 2018
もしかしたらデータベースが小さすぎるのかも知れないということで、別の比較が以下である。
/var/db/mysql のサイズが 1.7G の、VirtualBox 上の FreeBSD のシステムで、-x --all-databases 〜 で 1分18秒程度だった。
nsmrtks@fb103ufs:~ % date ; mysqldump -u foo -pパスワード -x --all-databases --triggers --routines --events | xz -9 > mysqldump.sql.xz
; date
2018年 2月 8日 木曜日 15時08分50秒 JST
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2018年 2月 8日 木曜日 15時10分08秒 JST
-x を --single-transaction にしてみたが、同程度だった。
nsmrtks@fb103ufs:~ % date ; mysqldump -u foo -pパスワード --single-transaction --all-databases --triggers --routines --events | xz -9 >
mysqldump1.sql.xz ; date
2018年 2月 8日 木曜日 15時10分26秒 JST
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2018年 2月 8日 木曜日 15時11分45秒 JST
バックアップデータについては、ダンプを取った時刻が違うのみで、どちらのシステムでも、同じものが得られた。
[nsmrtks@foohoge ~]$ unxz foo.sql.xz
[nsmrtks@foohoge ~]$ unxz foo1.sql.xz
[nsmrtks@foohoge ~]$ diff foo.sql foo1.sql
7595c7595
< -- Dump completed on 2018-02-08 5:49:44
---
> -- Dump completed on 2018-02-08 5:50:17
nsmrtks@fb103ufs:~ % unxz mysqldump.sql.xz
nsmrtks@fb103ufs:~ % unxz mysqldump1.sql.xz
nsmrtks@fb103ufs:~ % diff mysqldump.sql mysqldump1.sql
2015c2015
< -- Dump completed on 2018-02-08 15:10:08
---
> -- Dump completed on 2018-02-08 15:11:45
バックアップ中のロック時間が問題になる場合、あらためて -x や --single-transaction の比較検証が必要かもしれない。
mysqldump の場合は、バックアップよりもリストアに非常に時間がかかると述べるページあり。
実際にやってみると、前述の「201MB」のデータで、以下のように 2倍程度の時間であったが..
[nsmrtks@foohoge ~]$ date ; xzcat foo.sql.xz | mysql -u root -pパスワード --host=127.0.0.1 --port=3307 ; date
Thu Feb 8 06:45:40 UTC 2018
Thu Feb 8 06:45:51 UTC 2018
/var/db/mysql のサイズが 1.7G の、VirtualBox 上の FreeBSD のシステムでは、以下のように、ダンプ時より少し長い程度であったが..
nsmrtks@fb103ufs:~ % date ; xzcat mysqldump.sql.xz | mysql -u root -pパスワード ; date
2018年 2月 8日 木曜日 17時10分02秒 JST
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
2018年 2月 8日 木曜日 17時10分02秒 JST
nsmrtks@fb103ufs:~ % mysql -u foo -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
〜
foo@localhost [(none)]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000056
Position: 994
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9674523a-295d-11e6-977a-08002752701a:1-2171
1 row in set (0.03 sec)
foo@localhost [(none)]> reset master;
Query OK, 0 rows affected (1.84 sec)
foo@localhost [(none)]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
foo@localhost [(none)]> exit
Bye
nsmrtks@fb103ufs:~ % date ; xzcat mysqldump.sql.xz | mysql -u root -pパスワード ; date
2018年 2月 8日 木曜日 17時17分44秒 JST
mysql: [Warning] Using a password on the command line interface can be insecure.
2018年 2月 8日 木曜日 17時19分13秒 JST
書籍『MySQL5 逆引き大全』「038 データベースのダンプを行うには」には以下のようにあるが、本当だろうか?
外部キー制約を持つテーブルをリストアする場合、被参照テーブルを参照元テーブルよりも先にリストアする必要があります。 mysqldump コマンドはデフォルトでアルファベット順にダンプ出力を行いますので、外部キー制約のあるデータベースの場合はあらかじめ SQL の順序を編集してください。
外部キー制約がある場合、SET FOREIGN_KEY_CHECKS=0; でリストアせよと述べるページがある。
MySQLで外部キー付きテーブルをSET FOREIGN_KEY_CHECKS=0;でリストアしているのに外部キー制約違反が出るとき
mysqldumpでバックアップしたSQLでリストアすると外部キー制約に引っかかる
以下を見ると InnoDB では正しくダンプ/リストアされるようにも読める。 foreign_key_checks = 0; についても言及されている。
MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 9.4.4 FOREIGN KEY 制約
以下の日本MySQLユーザ会の人の発表資料が、MySQL におけるバックアップの全般を述べていて (mysqldump の話題はそのごく一部)、非常に興味深い。
MySQLのバックアップ運用について色々
docker exec による mysqldump / mysql 起動でも出来ないこともないが手間がかかったりする。
stty -echo
docker exec -i コンテナ名 mysqldump -u root -p -x --all-databases | xz -9 > foo.sql.gz
stty echo
xzcat foo.sql.xz | docker exec -it コンテナ名 mysql -u root -pパスワード
パスワードをコマンドラインに書かずにリストアする方法は不明。