MySQL のバックアップとリストア手順のメモです。
環境
OS: Ubuntu 20.04 LTS
DB: MySQL 8.0
※ MySQL のインストール手順は MySQL のインストールと初期設定 を参照してください。
概要
MySQL でのバックアップは基本的に論理バックアップ (データをバイナリ形式で出力するのではなく、SQL 文の形で出力する) となります。
物理バックアップ (データファイルをそのままバックアップする方法) も可能ですが、データの整合性を保つためには MySQL を停止した状態で行う必要があります。Enterprise Edition ではオンライン物理バックアップを行うツールがあるようですが、Community Edition では使用できないためこの記事では扱いません。
論理バックアップを取得するには mysqldump というツールを使用し、トランザクションログ (バイナリログ または binlog と呼ばれます) を取得するには mysqlbinlog というツールを使用します。
この記事ではこれらのツールを使用したバックアップの取得方法、およびバックアップからのリストア方法を説明します。
バックアップ
mysqldump の使い方
まず mysqldump の使い方と一般的に使用されるパラメータを説明します。mysqldump コマンドは以下の形式で実行できます。
# バックアップするデータベースを指定する場合
$ mysqldump [--options] --databases <database-name> ...
# 全てのデータベースをバックアップする場合
$ mysqldump [--options] --all-databases出力形式
mysqldump を実行すると、データベースのスキーマおよびデータが SQL 文として標準出力に出力されます。スキーマは CREATE TABLE 文として出力され、データは INSERT 文として出力されます。これらの SQL は標準出力に出力されるため、通常はリダイレクトしてファイルに書き出します。
なお本記事では扱いませんが、パラメータを指定すればスキーマのみ、またはデータのみ出力することも可能です。
認証
データベースへ接続するためのホスト名、ユーザー名、およびパスワードは、それぞれ --host、--user、--password オプションで指定します。スクリプトファイル内で mysqldump を実行する場合、パスワードを直接書くのは非推奨で、オプションファイル内に書くのが推奨されています。オプションファイルは 以下のようなフォーマットで記述し、--defaults-extra-file パラメータで指定します。
オプションファイル例
[mysqldump]
host=localhost # ホスト名 または IP アドレス
user=myuser # ユーザー名
password=mypassword # パスワードテーブルロックの回避とログポジションの出力
mysqldump はデフォルトでバックアップ時にテーブルロックを取得します。データ量が多いテーブルでは mysqldump によるテーブルロックがアプリケーションのパフォーマンスに影響することがあるため非推奨です。--single-transaction オプションを指定することで、mysqldump はテーブルロックを取得せずバックアップできます。
併せて、--master-data オプションを指定すると、現在の binlog ファイルのファイル名とポジションを出力できます。このオプションはもともとレプリケーションを開始する時に使用されるものでしたが、--master-data=2 と指定することでレプリケーション用のコマンドがコメントとして記述されるため、binlog ファイル名を確認でき、かつデータベースを復元する時には影響が無いようにできます。
なお今回テストした MySQL 8.0.31 では、--master-data は将来廃止される予定のため --source-data を使うよう警告が出ましたが、名前が変わったのみでその他の変更はありませんでした。
mysqlbinlog の使い方
mysqlbinlog は binlog のバックアップ/リストアに使うツールです。binlog は MySQL の設定ファイルにて log-bin パラメータで指定した場所に出力されます。
MySQL ではトランザクションがコミットされた時に binlog にトランザクションが書き込まれるため、cp コマンドなどでファイルを継続してコピーするのでもデータに不整合が発生することは無いのですが、mysqlbinlog では継続してファイルをコピーするオプションがあるため、今回はツールを使った方法を紹介します。
出力形式
mysqlbinlog は、デフォルトでは binlog ファイルを読み込んで内容を SQL 文で標準出力に出力します。
--raw オプションを指定することで、元のバイナリデータをそのまま出力することができますので、バックアップ時はこのオプションを指定します。
また --result-file オプションを使用すると、指定したファイルに出力を書き出すことができます。--result-file が --raw オプションと一緒に使われた場合、元の binlog ファイルと同じ名前のファイルに出力されますので、--result-file オプションには出力先のフォルダ名を指定します。
データベースへの接続とコネクション維持
mysqlbinlog は、通常は binlog ファイルのパスを指定してファイルを読み込みますが、でデータベースに接続し、そのデータベースが書き出した binlog を読み込むこともできます。データベースに接続するには --read-from-remote-server オプションを指定します。
--read-from-remote-server を指定した場合、デフォルトでは指定した binlog ファイルを読み込んだ後コネクションを閉じます。しかし --read-from-remote-server と併せて --stop-never オプションを指定すると、指定した binlog から最後の binlog ファイルまで全てのファイルを読み、また最後のファイルを読んだ後もコネクションを維持します。その後新しいトランザクションログが書き出されたら、その内容を継続的に読み込みます。
認証
--read-from-remote-server を指定した場合、データベース接続時の認証情報は mysqldump と同じ方法で指定できます。オプションで指定することもできますが、オプションファイルを記述して --defaults-extra-file オプションで指定するのが推奨です。
mysqlbinlog 用のオプションファイルは以下のように記述します。
オプションファイル例
[client]
host=localhost # ホスト名 または IP アドレス
user=myuser # ユーザー名
password=mypassword # パスワードバックアップ手順
以上を踏まえ、バックアップを行うには以下のコマンドを実行します。
ベースバックアップの取得
shell
$ mysqldump --defaults-extra-file=/path/to/option-file.cnf --single-transaction --flush-logs --master-data=2 --all-databases > /path/to/backup.sql これで /path/to/backup.sql に全てのデータベースのバックアップが出力されます。
cron などで定期的にベースバックアップを取得することで、リストアに必要な binlog の数が減りデータベースの復元にかかる時間を短縮できます。
binlog ファイルのバックアップ
mysqlbinlog で binlog ファイルをバックアップする場合、どの binlog ファイルからバックアップするかを指定する必要があります。
binlog ファイルの一覧は、MySQLの show binary logs コマンドで表示することができます。
shell
# log-bin パラメータには "/var/lib/mysql/mysql-bin/mysql-bin" と設定されていると仮定
$ cat /etc/mysql/mysql.conf.d/mysqld.cnf
...
log_bin = /var/lib/mysql/mysql-bin/mysql-bin
...
$ mysql -u root -p
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 919 | No |
| mysql-bin.000002 | 204 | No |
| mysql-bin.000003 | 204 | No |
| mysql-bin.000004 | 204 | No |
| mysql-bin.000005 | 204 | No |
| mysql-bin.000006 | 157 | No |
+------------------+-----------+-----------+
6 rows in set (0.00 sec)上記の例では 6個の binlog ファイルがあるのが確認できます。今回は “mysql-bin.000004” という名前の binlog ファイルからバックアップしたいと仮定します。
binlog ファイルのバックアップは以下のコマンドを実行します。
shell
$ mysqlbinlog --raw --result-file=/path/to/backup-folder --defaults-extra-file=/path/to/option-file.cnf --read-from-remote-server --stop-never mysql-bin.000004 &このコマンドを実行すると、mysqlbinlog プロセスは終了せず継続的に binlog のバックアップを取得するので、バックグラウンドでプロセスを実行するようにしています。
接続先のサーバーが停止した場合や、mysqlbinlog プロセスが何らかのエラーで停止した場合、プロセスは停止し再接続されません。そのため実際に使う際はこのコマンドを daemon 化して常に実行されているようにするのが良いと思います。
リストア
バックアップからのリストアは、まずベースバックアップを復元した後、binlog を復元します。
リストア手順
ベースバックアップのリストア
ベースバックアップのリストアは簡単で、ただバックアップした SQL ファイルを mysql コマンドを使って実行するだけです。
shell
# ベースバックアップのリストア
$ mysql -u username -p < /path/to/backup.sqlbinlog のリストア
binlog も同様に、バックアップした binlog ファイルを mysqlbinlog を使って SQL に出力し、mysql コマンドを使って実行するだけです。
どの binlog からリストアすべきかは、ベースバックアップファイルのコメントに書かれています。
ベースバックアップファイル例
$ cat /path/to/backup.sql
...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=157;
...上記コメント行で、CHANGE MASTER TO の後に mysql-bin.000005 と書かれています。これは、このベースバックアップには mysql-bin.000005 の1つ前 (つまり mysql-bin.000004) までのデータが含まれているという意味です。
なので、mysql-bin.000005 ファイルからリストアすれば、データベースを完全に復元することができます。
binlog をリストアする時は、復元する全ての binlog ファイルを一度に適用するのがベストプラクティスです。例えばリストアする binlog ファイルが 4つあった場合、1ファイルずつ復元していくのではなく、全てのファイルを一度に復元するか、各ファイルを一度 別のファイルに書き出してから、そのファイルを一度に適用するのが推奨されています。
上記の例では、mysql-bin.000004、mysql-bin.000005、mysql-bin.000006 の 3つのファイルをバックアップしましたが、mysql-bin.000004 はベースバックアップに含まれているため、mysql-bin.000005 と mysql-bin.000006 の 2ファイルのみをリストアします。
binlog ファイルをリストアする手順は以下の通りです。
shell
$ cd /path/to/backup-folder
# 一度に全てのファイルを復元する場合
mysqlbinlog mysql-bin.000005 mysql-bin.000006 | mysql -u username -p
# 一度ファイルに書き出してから復元する場合
mysqlbinlog mysql-bin.000005 > restore-binlog.sql
mysqlbinlog mysql-bin.000006 >> restore-binlog.sql
mysql -u username -p < restore-binlog.sqlMySQL のバックアップとリストアの方法は以上です。mysqldump および mysqlbinlog には多くのオプションがあり様々なユースケースに対応できるようになっています。各オプションは公式ドキュメント (mysqldump, mysqlbinlog) を参照してください。