mysqldumpでバックアップ・リストアする

mysqldump コマンドでデータベースダンプすることができます。ダンプファイルには、CREATE TABLE, INSERT 等の SQL 文が含まれます。大量データがの場合はバックアップ・リストアに時間がかかることがありあります。

# mysqldump -h localhost -u root -pパスワード データベース名 テーブル名... > dump.sql
# mysqldump -h localhost -u root -pパスワード --databases データベース名... > dump.sql
# mysqldump -h localhost -u root -pパスワード --all-databases > dump.sql

おススメのオプションを下記に示します。--single-transaction の条件に注意して利用してください。

# mysqldump -h localhost -u root -pパスワード --databases データベース名 \
  --flush-logs --single-transaction --master-data=2 > dump.sql

--databases: 指定したデータベースをダンプします。ダンプファイルに CREATE DATABASE 文も加えられます。
--all-databases: MariaDB システムで利用する mysql データベースも含め、すべてのデータベースをダンプします。ダンプファイルに CREATE DATABASE 文も加えられます。
--flush-logs: ダンプ前にログファイルをフラッシュします。
--lock-all-tables: ダンプ中全てのテーブルを書き込みロックします。ダンプ中のテーブルアクセスがロックされますが、無条件で一貫性が保たれたデータをダンプすることができます。
--single-transaction: ひとつのトランザクションとしてダンプします。対象データベースがすべて InnoDB であること、および、ダンプ中に CREATE TABLE, DROP TABLE, ALTER TABLE, TRUNCATE TABLE を実行しないことを条件に、一貫性が保たれたデータをダンプすることができます。--all-databases を使用する場合は、MariaDB が内部で使用している mysql データベースのテーブルが InnoDB ではなく、MyISAM や、その改良版である Aria であることに注意してください。
--master-data=2: ダンプ開始時点のポジション情報をコメントとして出力します。レプリケーションやバイナリログからのロールフォワードに役立ちます。

バイナリログが退避されていれば、さらに障害発生の直前まで、データをロールフォワードすることができます。例えば、誤ってテーブルを削除してしまったのを復旧するケースを考えます。まず、ダンプ時のバイナリログ名とポジションを確認します。

# grep "CHANGE MASTER TO" dump.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000005', MASTER_LOG_POS=389;

退避したバイナリログをSQL文に変換します。

# mysqlbinlog /tmp/mariadb-bin.000005 > binlog_000005.sql
# vi binlog_00005.sql

「end_log_pos 番号」から次の「end_log_pos 番号」までがひとつの処理です。

#191207 19:01:10 server id 1  end_log_pos 389 ...
   :
# at 389
   :
   :
   :
#191207 19:04:11 server id 1  end_log_pos 3553 ...
   :
insert into users ( id, name ) values ( 3, 'Takeda' )
   :
# at 3553
#191207 19:04:11 server id 1  end_log_pos 3584 ...
   :
drop table sample
   :
# at 3584

389番の処理までは dump.sql でリストア済み。3584番の処理が誤ってテーブルを消してしまった処理なので、その直前の 3553番の処理までを SQL 文にして、流し込みます。

# mysqlbinlog --start-position=389 --stop-position=3553 mariadb-bin.000005 > binlog_000005.sql
# mysql -h localhost -u root -pパスワード < binlog_000005.sql