MySQL Shellを利用したクラウドバックアップ/リストア

• Oracle MySQL Database Serviceの使い方メモ〜番外編その2
• MySQL Shellを利用したOracle Cloud上のObjectStorageへのダンプバックアップ取得/リストア
• MySQL Shell Dump UtilityとDump Loading Utilityの使い方は別記事にアップしました

はじめに

MySQL ShellのDump Utility / Dump Loading Utilityを利用してローカル環境で稼働しているMySQLインスタンスからOracle Cloud Infrastructure (OCI) Object Storageへダンプバックアップの取得、データロードによるリストア手順の検証を行いました。ダンプバックアップの取得先となるOCI Object StorageはOracle Cloudで利用可能な安価なオブジェクトストレージで、Amazon S3、Azure BLOBに相当するクラウド・ストレージ・サービスになります。Oracle Cloudでは東京リージョンの他に大阪リージョンを利用できるため、容易にデータ保護・災害対策を実現することができます。本記事中のリストアの検証では、ダンプバックアップの取得を行ったソースMySQLインスタンスへのリストア以外に、異なるローカル環境で稼働している上位バージョンのMySQLインスタンスへのリストア手順も確認しました。検証の構成イメージは下記を確認してください。

検証環境

• macOS 10.15.7 Catalina : クライアント環境
• MySQL 5.7.32 : クライアント上で稼働・ソース/ターゲットMySQLインスタンスとして利用
• MySQL 8.0.22 : クライアント上で稼働・リストアを行うターゲットMySQLインスタンスとして利用
• MySQL Shell 8.0.22 : クライアント上で利用

事前準備

前提条件

• MySQのバージョンが5.7以上であること
• エクスポート先にOCI Object Storageを利用する場合は、MySQL Shellを実行する環境からOCI CLIを利用してOCI Object Storageバケット (インターネット) に直接アクセスできること
• OCI CLIでアクセスするOCIユーザーはOCI Object Storageを操作する権限を持つこと
• OCI Object Storageの事前認証済リクエストが利用されるため、利用するデータベースに格納されているデータのセキュリティ要件の確認必須 ¹

詳細は別記事のMySQL Shell Dump Utilityを使用する前提条件の項をご確認ください

OCI CLIの準備

OCI CLIのインストールについては下記のドキュメント・チュートリアル・Qiita記事をご参照ください。

Oracle Cloud Infrastructureドキュメント コマンドライン・インタフェース(CLI)

チュートリアル : Oracle Cloud Infrastructure を使ってみよう コマンドライン(CLI)でOCIを操作する - Oracle Cloud Infrastructureアドバンスド

Oracle Cloud : コマンド・ライン・インタフェース(CLI) をインストールしてみた

ここでハマったのはOCI CLIの構成ファイルであるconfig内の記述、API Keyのファイルパスは絶対パスで記載しないとMySQL Shellでエラーが発生しました。

MySQL Shellのインストール

公式のダウンロードサイトよりインストーラをダウンロードして実行してください。
Linux環境の場合は 前回の記事 も参照ください。

バックアップ先となるOCI Object Storage バケットの作成

OCIダッシュボードメニューから、コア・インフラストラクチャ>オブジェクト・ストレージ>オブジェクト・ストレージにアクセスし、コンソール左下のコンパートメント・セレクタメニューより作業するコンパートメントを選択します。その後、バケットの作成ボタンを選択し、下記の設定項目入力を入力してバケットを作成します。

• バケット名 : 任意 (MySQLBackup)
• ストレージ層 : 標準
• オブジェクト・イベント : 任意 (設定しない)
• オブジェクト・バージョニング : 任意 (設定しない)
• 暗号化 : ORACLE管理キーを使用した暗号化

バケットが作成されたらバケット名を選択し、バケットの詳細画面からネームスペースの値をメモしておいてください。

MySQLServerのObject Storageへのバックアップの実行

バックアップ対象となるtestスキーマの準備

ローカルで稼働しているMySQLインスタンスにMySQL Shellから接続します。下記では、MySQL Shellからアクセスし、SQLモードに移行、バックアップ対象となるtestスキーマを作成後、MySQL Shell Dump Utilityを実行するために再度Javascriptモードに戻っています。

mysqlsh -uroot -p
MySQL Shell 8.0.22

Copyright (c) 2016, 2020, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 5
Server version: 5.7.32 Homebrew
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost  SQL > CREATE DATABASE test;
Query OK, 1 row affected (0.0014 sec)

 MySQL  localhost  SQL > USE test;
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.

 MySQL  localhost  test  SQL > CREATE TABLE users (id INT AUTO_INCREMENT, usr_name TEXT, PRIMARY KEY (id));
Query OK, 0 rows affected (0.0173 sec)

 MySQL  localhost  test  SQL > INSERT INTO users(usr_name) VALUES ('hoge'), ('hogehoge'), ('hogehogehoge');
Query OK, 3 rows affected (0.0072 sec)
Records: 3  Duplicates: 0  Warnings: 0

 MySQL  localhost  test  SQL > CREATE TABLE revenue (id INT AUTO_INCREMENT, usr_id INT, revenue INT, INDEX usr_index(usr_id), FOREIGN KEY usr_id (usr_id) references users(id), PRIMARY KEY (id));
Query OK, 0 rows affected (0.0178 sec)

 MySQL  localhost  test  SQL > INSERT INTO revenue(usr_id, revenue) VALUES (1,10000), (2, 5000), (1,1000);
Query OK, 3 rows affected (0.0008 sec)
Records: 3  Duplicates: 0  Warnings: 0

 MySQL  localhost  test  SQL > SELECT users.id AS 'ID', users.usr_name AS 'CUSTOMER NAME', SUM(revenue.revenue) AS 'REVENUE' FROM users LEFT OUTER JOIN revenue on users.id = revenue.usr_id GROUP BY id;
+----+---------------+---------+
| ID | CUSTOMER NAME | REVENUE |
+----+---------------+---------+
|  1 | hoge          |   11000 |
|  2 | hogehoge      |    5000 |
|  3 | hogehogehoge  |    NULL |
+----+---------------+---------+
3 rows in set (0.0007 sec)

 MySQL  localhost  test  SQL > SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| revenue        |
| users          |
+----------------+
2 rows in set (0.0004 sec)

 MySQL  localhost  test  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.0008 sec)

 MySQL  localhost  test  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost  test  JS > 

Instance Dump Utilityを利用したObject Storageへのバックアップ

次にInstance Dump Utilityを利用してOCI Object Storageにバックアップを取得します。利用したInstance　Dump　Utilityのコマンドは下記です。

util.dumpInstance("dumpinstance_onp2onp", {osBucketName: "MySQLBackup", osNamespace: "MyNamespace", threads: 4, dryRun: true})

ここで"dumpinstance_onp2onp"はObject Storage上オブジェクトを識別するための接頭辞、osBucketName: "MySQLBackup"は上記手順で確認したObject Storageバケットが存在するネームスペース、threads:エクスポートを実行するスレッド数、dryRun: trueは実行前にコマンドの検証を行うことを意味しています。
はじめにdryRunを実施し、Utilityのコマンドを検証、2回目の実行で実際にバックアップの取得を行っています。

 MySQL  localhost  test  JS > util.dumpInstance("dumpinstance_onp2onp", {osBucketName: "MySQLBackup", osNamespace: "MyNamespace", threads: 4, dryRun: true})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL for schema `test`
Writing DDL for table `test`.`revenue`
Writing DDL for table `test`.`users`
Preparing data dump for table `test`.`revenue`
Data dump for table `test`.`revenue` will be chunked using column `id`
Preparing data dump for table `test`.`users`
Data dump for table `test`.`users` will be chunked using column `id`

 MySQL  localhost  test  JS > util.dumpInstance("dumpinstance_onp2onp", {osBucketName: "MySQLBackup", osNamespace: "MyNamespace", threads: 4})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL for schema `test`
Writing DDL for table `test`.`users`
Writing DDL for table `test`.`revenue`
Preparing data dump for table `test`.`revenue`
Data dump for table `test`.`revenue` will be chunked using column `id`
Preparing data dump for table `test`.`users`
Data dump for table `test`.`users` will be chunked using column `id`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `test`.`revenue` will be written to 2 files
Data dump for table `test`.`users` will be written to 2 files
4 thds dumping - 33% (2 rows / ~6 rows), 1.00 row/s, 17.00 B/s uncompressed, 0.001 thds dumping - 100% (6 rows / ~6 rows), 1.00 row/s, 17.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 61 bytes
Compressed data size: 0 bytes
Compression ratio: 61.0
Rows written: 6 
Bytes written: 0 bytes
Average uncompressed throughput: 51.87 B/s
Average compressed throughput: 0.00 B/s

MySQL  localhost  test  JS > 

OCIコンソールのObject Storageバケットの詳細画面からも取得されたダンプファイルのオブジェクトを確認することができます。

Object Storage上のバックアップのリストア

バックアップ取得元と同一のMySQLServerにデータロード

バックアップを取得したMySQLインスタンスにInstance Dump Utilityで取得したダンプファイルをロードします。
下記では、testスキーマを削除した後、Dump Loading Utilityを用いてデータロードを行い、元に戻っていることを確認しました

 MySQL  localhost  test  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost  test  SQL > DROP DATABASE test;
Query OK, 2 rows affected (0.0109 sec)

 MySQL  localhost  test  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0043 sec)

 MySQL  localhost  test  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost  JS > util.loadDump("dumpinstance_onp2onp",{osBucketName:"MySQLBackup",osNamespace:"MyNamespace",threads:4})
Loading DDL and Data from OCI ObjectStorage bucket=MySQLBackup, prefix='dumpinstance_onp2onp' using 4 threads.
Opening dump...
Target is MySQL 5.7.32. Dump was produced from MySQL 5.7.32
Fetching dump data from remote location...
Fetching 2 table metadata files for schema `test`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `test`
[Worker003] Executing DDL script for `test`.`revenue`
[Worker000] Executing DDL script for `test`.`users`
[Worker002] test@[e-mail]: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] test@[e-mail]: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] test@users@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] test@revenue@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL                                         
                                                       
4 chunks (6 rows, 61 bytes) for 2 tables in 1 schemas were loaded in 2 sec (avg throughput 30.50 B/s)
0 warnings were reported during the load.

 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  localhost  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.0006 sec)
 MySQL  localhost  SQL > USE test;
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.

 MySQL  localhost  test  SQL > SELECT users.id AS 'ID', users.usr_name AS 'CUSTOMER NAME', SUM(revenue.revenue) AS 'REVENUE' FROM users LEFT OUTER JOIN revenue on users.id = revenue.usr_id GROUP BY id;
+----+---------------+---------+
| ID | CUSTOMER NAME | REVENUE |
+----+---------------+---------+
|  1 | hoge          |   11000 |
|  2 | hogehoge      |    5000 |
|  3 | hogehogehoge  |    NULL |
+----+---------------+---------+
3 rows in set (0.0009 sec)

 MySQL  localhost  test  SQL > 

testスキーマ、usersテーブルとrevenueテーブルが元に戻っていることを確認できました。

バックアップ取得元とは異なるMySQL 8.0インスタンスにデータロード

バックアップを取得したMySQLインスタンスとは異なるMySQLインスタンスにOCI Object Storage上に取得したダンプファイルをDump Loading Utilityでロードします。別環境で用意したMySQL ServerはMySQL 8.0.22であり、ダンプファイルを取得したMySQL Serverの上位バージョンとなっています。

 MySQL  localhost:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:33060+ ssl  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0010 sec)

 MySQL  localhost:33060+ ssl  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost:33060+ ssl  JS > util.loadDump("dumpinstance_onp2onp",{osBucketName:"MySQLBackup",osNamespace:"MyNamespace",threads:4,ignoreVersion: true})
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.loadDump: local_infile disabled in server (RuntimeError)

 MySQL  localhost:33060+ ssl  JS > 

ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.と怒られました。local-infileパラメータをonに設定する必要があるみたいです。SQLモードに移行後、'local-infile'を有効にして再度Dump Loading Utilityを用いてデータロードを行いました。

  MySQL  localhost:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:33060+ ssl  SQL > SET GLOBAL local_infile=on; 
Query OK, 0 rows affected (0.0003 sec)

 MySQL  localhost:33060+ ssl  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost:33060+ ssl  JS > util.loadDump("dumpinstance_onp2onp",{osBucketName:"MySQLBackup",osNamespace:"MyNamespace",threads:4,ignoreVersion: true})
Loading DDL and Data from OCI ObjectStorage bucket=MySQLBackup, prefix='dumpinstance_onp2onp' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 5.7.32
WARNING: Destination MySQL version is newer than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. 'ignoreVersion' option is enabled, so loading anyway.
Fetching dump data from remote location...
Fetching 2 table metadata files for schema `test`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `test`
[Worker003] Executing DDL script for `test`.`revenue`
[Worker001] Executing DDL script for `test`.`users`
[Worker002] test@[e-mail]: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] test@[e-mail]: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] test@users@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] test@revenue@@1.tsv.zst: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL                                          
                                                        
4 chunks (6 rows, 61 bytes) for 2 tables in 1 schemas were loaded in 1 sec (avg throughput 61.00 B/s)
0 warnings were reported during the load.

 MySQL  localhost:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:33060+ ssl  SQL > SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.0009 sec)

 MySQL  localhost:33060+ ssl  SQL > USE test;
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.

 MySQL  localhost:33060+ ssl  test  SQL > SELECT users.id AS 'ID', users.usr_name AS 'CUSTOMER NAME', SUM(revenue.revenue) AS 'REVENUE' FROM users LEFT OUTER JOIN revenue on users.id = revenue.usr_id GROUP BY id;
+----+---------------+---------+
| ID | CUSTOMER NAME | REVENUE |
+----+---------------+---------+
|  1 | hoge          |   11000 |
|  2 | hogehoge      |    5000 |
|  3 | hogehogehoge  |    NULL |
+----+---------------+---------+
3 rows in set (0.0011 sec)

元のMySQLインスタンスとは別のインスタンスにデータをロードできることを確認できました。

関連情報

MySQL Technology Café #7 MySQL Shellを使ってみよう！

Oracle Cloud Infrastructureドキュメント オブジェクト・ストレージの概要

Oracle Cloud Infrastructure Documentation MySQL Database Importing and Exporting Databases

MySQL Shell 8.0 7.5 Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

MySQL Shell 8.0 7.6 Dump Loading Utility

1. Oracle Cloud Infrastructureドキュメント オブジェクト・ストレージ 事前認証済リクエストの使用 ↩