Restore Database

前回、基本的なバックアップの方法について書いたので、今回は基本的なリストアについてまとめてみます。

Backup Database

Basic Restore on PostgreSQL

データベース全体

psql -h 127.0.0.1 -U postgres -f <dump file> <対象DB>

ここでは、データベースとデータベース内のオブジェクトをDROPする手間を省くために、ダンプファイルに–cleanを指定してダンプしたファイルをリストアしています。

※ 以下の処理では、直前でバックアップを取得してリストアしています。

~/backup [10:34:58]> pg_dump -h 127.0.0.1 -U postgres POC --clean > dump_all.sql
Password:
~/backup [10:35:20]> psql -h 127.0.0.1 -U postgres -f dump_all.sql POC
Password for user postgres:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
DROP INDEX
ALTER TABLE
ALTER TABLE
DROP TABLE
psql:dump_all.sql:23: ERROR:  table "trains" does not exist
DROP SEQUENCE
DROP TABLE
DROP EXTENSION
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 35
COPY 0
COPY 6
COPY 6
 setval
--------
     35
(1 row)

ALTER TABLE
CREATE INDEX
~/backup [10:36:21]>

リストア後にDROPしたテーブルとデータがリカバリーされている事が確認出来ます。上記エラーはDROP TABLEを実行するコマンドが,対象テーブルが存在しない為エラーになっているだけなので無視しても問題有りません。

特定テーブルのリストア

psql -h 127.0.0.1 -U postgres -f <dump file> <対象DB>

~/backup [13:51:34]> pg_dump -h 127.0.0.1 -U postgres --clean -t trains POC > trains_dump.sql
Password:
~/backup [13:56:45]> psql -h 127.0.0.1 -U postgres -f trains_dump.sql POC
Password for user postgres:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
DROP TABLE
SET
SET
CREATE TABLE
ALTER TABLE
COPY 5
~/backup [13:57:09]>

Schema内のオブジェクトのみリストア

こちらの処理は、テーブルのみでデータを含みません。新しいDBに同じ定義で空オブジェクトを作成したり、適宜加工して同じ定義でテーブルを作成したりする用途であれば使える印象です。

~/backup [13:53:49]> pg_dump -h 127.0.0.1 -U postgres -s -t trains POC > trains_dump_schema_only.sql
Password:

~/backup [14:03:56]> psql -h 127.0.0.1 -U postgres -f trains_dump_schema_only.sql POC
Password for user postgres:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
~/backup [14:05:09]>

データのみ リストア

~/backup [14:09:09]> pg_dump -h 127.0.0.1 -U postgres -a -t trains POC > trains_dump_data_only.sql
Password:
~/backup [14:09:16]> psql -h 127.0.0.1 -U postgres -f trains_dump_data_only.sql POC
Password for user postgres:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
COPY 5

Basic Restore on MySQL

mysqldumpで取得したバックアップは、mysqlクライアントを利用してそのままデータベースに流し込みます。

--all-databases または --databases オプションを使用して、mysqldump によってダンプファイルが作成された場合、それには CREATE DATABASE および USE ステートメントが含まれ、データをロードするデフォルトのデータベースを指定する必要がありません。

参照：7.4.2 SQL フォーマットバックアップのリロード

データベース全体

システムデータベースを含むデータベースやデータベースオブジェクトをリストアします。

※ 以下の処理では、直前でバックアップを取得してリストアしています。

~/backup [14:23:44]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --hex-blob --default-character-set=utf8mb4 --all-databases \
> --single-transaction > mysql_backup_with_transaction_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
~/backup [14:24:34]> mysql -h 127.0.0.1 -u root -p < mysql_backup_with_transaction_dump.sql
Enter password:
~/backup [14:28:43]>

特定テーブルのダンプのリストア

~/backup [14:38:02]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --hex-blob --default-character-set=utf8mb4 \
> --single-transaction POC test > mysql_backup_with_transaction_specific_table_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

~/backup [14:38:05]> mysql -h 127.0.0.1 -u root -p POC < mysql_backup_with_transaction_specific_table_dump.sql
Enter password:
~/backup [14:39:57]>

Schemaのみリストア

~/backup [14:44:56]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --no-data --hex-blob --default-character-set=utf8mb4 \
> --single-transaction POC test > mysql_backup_with_transaction_specific_table_with_nodata_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

~/backup [14:45:07]> mysql -h 127.0.0.1 -u root -p POC < mysql_backup_with_transaction_specific_table_with_nodata_dump.sql
Enter password:
~/backup [14:46:17]>

データのみリストア

~/backup [14:48:28]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --no-create-info --hex-blob --default-character-set=utf8mb4 \
> --single-transaction POC test > mysql_backup_with_transaction_specific_table_with_data_only_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

~/backup [14:48:31]> mysql -h 127.0.0.1 -u root -p POC < mysql_backup_with_transaction_specific_table_with_data_only_dump.sql
Enter password:
~/backup [14:49:14]>

備考：

リストアする時は、Defaultで有効になっているバイナリーログを無効にすると良いでしょう。リストア完了後に再度有効にすることで、I/O負荷を軽減する事が可能です。

mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

バイナリロギングを無効にするには、起動時に –skip-log-bin または –disable-log-bin オプションを指定できます。 これらのオプションのいずれかが指定され、–log-bin も指定されている場合は、後で指定するオプションが優先されます。 バイナリロギングが無効になっている場合、log_bin システム変数は OFF に設定されます。

17.1.6.4 バイナリロギングのオプションと変数

※ MySQL5.7迄は、my.cnfからlog-binをコメントアウトすれば良かったかと思います。

MySQL8.0.21からはREDOログもインスタンス全体で無効に出来る様です。リストアのタイミングであれば大きくて時間のかかるリストアには活用出来そうです。

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;

参照：15.6.5 redo ログ

以下のスライドにもまとめましたが、色々なオプションがあるので必要に応じてご利用のバージョンに合わせたマニュアルを確認してください。

MySQLとPostgreSQLの基本的なバックアップ比較 from Shinya Sugiyama