はじめに
MySQL Database Service(以下MDS)には、いくつかの制限があります。そのため、オンプレミスのMySQLからMDSへデータを移行しようとすると問題が発生する可能性があります。
MySQL Shellでは、オンプレミスのMySQLからMDSにデータをインポートする際に、互換性チェックに使用できるocimdsオプションがあります。今回このオプションについて紹介していきたいと思います。
今回は以下のバージョンで機能紹介と検証を行います。
- MySQL 8.0.29
- MySQL Shell 8.0.29
ocimdsオプションについて
ocimdsオプションを有効にすると、CREATE TABLEのDATADIRECTORY、INDEX DIRECTORY、ENCRYPTIONオプションがコメント化されます。
また、互換性のチェックも行われます。例えば、MDSではInnoDBストレージエンジンのみサポートされているため、MyISAMストレージエンジンを使用しているテーブルをダンプしようとすると、ダンプは停止します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
shell-js> util.dumpSchemas(["d1"],"/backup-oci" ,{ocimds: true}) Acquiring global read lock Global read lock acquired Initializing - done 1 schemas will be dumped and within them 2 tables, 0 views. Gathering information - done All transactions have been started Locking instance for backup Global read lock has been released Checking for compatibility with MySQL Database Service 8.0.29 NOTE: Database 'd1' had unsupported ENCRYPTION option commented out ERROR: Table 'd1'.'t2' uses unsupported storage engine MyISAM (fix this with 'force_innodb' compatibility option) Compatibility issues with MySQL Database Service 8.0.29 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL. Validating MDS compatibility - done Util.dumpSchemas: While 'Validating MDS compatibility': Compatibility issues were found (MYSQLSH 52004) |
ここでチェックされる問題はcompatibility オプションを使用して出力内容を自動で修正することが可能です。
compatibility: array of strings
ダンプ出力のすべてのテーブルに MySQL Database Service との互換性のために指定された要件を適用し、ダンプファイルを necessary.From MySQL Shell 8.0.23 として変更します。このオプションはすべてのユーティリティで使用でき、そのリリースより前は、インスタンスダンプユーティリティおよびスキーマダンプユーティリティでのみ使用できます。
参照 : 8.5 インスタンスダンプユーティリティ、スキーマダンプユーティリティおよびテーブルダンプユーティリティ
以下のリストを指定することができます。
-
force_innodb
InnoDB ストレージエンジンを使用していないテーブルをInnoDB ストレージエンジンに変更します。
これは、MDSではInnoDBストレージエンジンのみサポートされているためです。 -
skip_invalid_accounts
MDSでサポートされていない外部認証プラグインで作成されたユーザーアカウントを削除します。また、MySQL8.0.26以降ではパスワードが設定されていないユーザーアカウントも削除します。
ただし、パスワードが設定されていないアカウントがロールとして識別される場合は、CREATE ROLEステートメントを使用してダンプされます。
-
strip_definers
ビュー、ルーチン、イベント、トリガーからDEFINER句を削除します。
また、ビューとルーチンのSQL SECURITY句をDEFINERからINVOKERに変更します。これはMDSで特定の権限が制限されていることに関連しています。
制限されている具体的な権限はドキュメントのデフォルトのMySQL権限を参照してください。ビュー、ルーチン、イベント、トリガーを作成する際は
DEFINER句でユーザーを指定できます。
SET_USER_ID権限かSUPER権限を持つユーザーであれば、任意のユーザーを指定することが可能です。
この権限を持たないユーザーは自身のアカウントのみを指定することができます。前述の通り、MDSに作成される管理ユーザのデフォルトのMySQL権限には
SET_USER_ID、SUPER権限も含まれていないため、MDSではロードを実行するユーザーがDEFINERとなります。 -
strip_restricted_grants
MDSによって制限されている特定の権限を削除します。
これは上で説明した通り、MDSでは一部権限を付与できないことに関係しています。 -
strip_tablespaces
すべてのテーブルがデフォルトのテーブルスペースに作成されるように、CREATE TABLEステートメントからTABLESPACE句を削除します。 MySQL Database Service には、テーブルスペースに対するいくつかの制限があります。 -
ignore_missing_pks
テーブルに主キーがない場合も無視してダンプを実行します
MDSでは主キーに関する制限はありません。ただし、高可用性では前提として主キーが必要となります。 -
create_invisible_pks
主キーがないテーブルに主キーを追加します
検証
実際に、前章で紹介した、MDSと互換性のない機能などをMySQLで使用して、ダンプをとります。
そして、ocimdsオプションを使用した場合と使用していない場合のダンプファイルを比較してみます。
次のように互換性のないオブジェクトを作成していきます。
MyISAMテーブルの作成
|
1 2 |
mysql> CREATE DATABASE d1; mysql> CREATE TABLE d1.t1(id int Primary key ) ENGINE=MyISAM; |
テーブルスペースを作成し、TABLESPACE句で指定する
|
1 2 |
mysql> CREATE TABLESPACE ts1; mysql> CREATE TABLE d1.t2(id int Primary key) TABLESPACE = ts1; |
主キーがないテーブルを作成する。
|
1 |
mysql> CREATE TABLE d1.t3(id int); |
空のパスワードを設定できるようにmy.cnfに以下の設定を追加する
|
1 2 3 4 5 6 |
[mysqld] validate_password.length=0 validate_password.mixed_case_count=0 validate_password.number_count=0 validate_password.policy=LOW validate_password.special_char_count=0 |
ユーザー(パスワードなし)とロールを作成する
|
1 2 |
mysql> CREATE USER nopass@localhost; mysql> CREATE ROLE role_test; |
トリガーとルーチンを作成
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> CREATE DATABASE d2; mysql> use d2 mysql> CREATE TABLE d2.t1(id int Primary key); mysql> CREATE TABLE d2.t2(id int Primary key); mysql> CREATE DEFINER = root@localhost TRIGGER test_trigger_1 AFTER INSERT ON d2.t1 FOR EACH ROW INSERT INTO d2.t2 VALUES(1); mysql> DELIMITER // mysql> CREATE DEFINER = root@localhost PROCEDURE procedure_1() SQL SECURITY INVOKER BEGIN UPDATE d2.t1 SET id = id + 1; END; // mysql> DELIMITER ; mysql> DELIMITER // mysql> CREATE DEFINER = root@localhost PROCEDURE procedure_2() SQL SECURITY DEFINER BEGIN UPDATE d2.t1 SET id = id + 1; END; // mysql> DELIMITER ; |
まずはocimdsオプションなしでダンプします。
|
1 |
shell-js> util.dumpInstance("/backup") |
次に、ocimdsオプションを使用してダンプします。なお今回は、create_invisible_pksを使用し主キーがないテーブルに主キーを追加するようにします。
|
1 |
shell-js> util.dumpInstance("/backup_mds",{ocimds: true, compatibility: ["force_innodb" , "skip_invalid_accounts" , "strip_definers","strip_restricted_grants", "strip_tablespaces", "create_invisible_pks" ]}) |
ダンプされるファイルの種類に違いはありませんでした。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# ls /backup/ d1.json d1@t2@@0.tsv.zst d1@t3.tsv.zst d2@t1.json d2@t2.sql d1.sql d1@t2@@0.tsv.zst.idx d1@t3.tsv.zst.idx d2@t1.sql @.done.json d1@t1@@0.tsv.zst d1@t2.json d2.json d2@t1.triggers.sql @.json d1@t1@@0.tsv.zst.idx d1@t2.sql d2.sql d2@t2@@0.tsv.zst @.post.sql d1@t1.json d1@t3.json d2@t1@@0.tsv.zst d2@t2@@0.tsv.zst.idx @.sql d1@t1.sql d1@t3.sql d2@t1@@0.tsv.zst.idx d2@t2.json @.users.sql # ls /backup_mds/ d1.json d1@t2@@0.tsv.zst d1@t3.tsv.zst d2@t1.json d2@t2.sql d1.sql d1@t2@@0.tsv.zst.idx d1@t3.tsv.zst.idx d2@t1.sql @.done.json d1@t1@@0.tsv.zst d1@t2.json d2.json d2@t1.triggers.sql @.json d1@t1@@0.tsv.zst.idx d1@t2.sql d2.sql d2@t2@@0.tsv.zst @.post.sql d1@t1.json d1@t3.json d2@t1@@0.tsv.zst d2@t2@@0.tsv.zst.idx @.sql d1@t1.sql d1@t3.sql d2@t1@@0.tsv.zst.idx d2@t2.json @.users.sql |
それでは、それぞれ変更された部分を確認していきます。
まずd1.sqlを確認するとCREATE DATABASEからENCRYPTION句がコメントアウトされていました。
ドキュメントにはCREATE TABLEからENCRYPTION句をコメントアウトすると記載されていましたが、
CREATE DATABASEのENCRYPTION句も削除するようです。
これは、CREATE TABLEでENCRYPTION 句が指定されていない場合、テーブルはスキーマの設定が継承されるためだと思われます。
|
1 2 3 4 5 6 |
# diff /backup/d1.sql /backup_mds/d1.sql 11c11,12 < CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'd1' /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; --- > CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'd1' /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 -- DEFAULT ENCRYPTION='N' > */; |
d1@t1.sqlを確認するとENGINEがMyISAMからInnoDBに修正されています。
|
1 2 3 4 5 |
# diff /backup/d1@t1.sql /backup_mds/d1@t1.sql 16c16 < ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
d1@t2.sqlを確認するとTABLESPACE 句が削除されています。
|
1 2 3 4 5 |
# diff /backup/d1@t2.sql /backup_mds/d1@t2.sql 16c16 < ) /*!50100 TABLESPACE 'ts1' */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --- > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
d1@t3.sqlを比較します。
d1.t3には主キーを作成していませんが、DDLには差異はありませんでした。
ドキュメントにも、ダンプメタデータにフラグを追加して、MySQL Shellのダンプロードユーティリティに通知し、主キーを含まないテーブルごとに、非表示の列に主キーを追加する、とあります。
Add a flag in the dump metadata to notify MySQL Shell’s dump loading utility to add primary keys in invisible columns, for each table that does not contain a primary key.
参照 : 11.5 Instance Dump Utility, Schema Dump Utility, and Table Dump Utility
主キーの追加は、他と異なりDDLを書き換えたりはしないようです。
|
1 |
# diff /backup/d1@t3.sql /backup_mds/d1@t3.sql |
d2.sqlを比較します。
d1.sql同様、ENCRYPTION句がコメントアウトされていました。
また、プロシージャからDEFINER句が削除されています。
そして、procedure_2はSQL SECURITY DEFINERとしていましたが、ocimdsオプションを使用しない場合は、SQL SECURITY 句は省略されており、ocimdsオプションをした場合は、SQL SECURITY INVOKERが追加されていました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
# diff -C 1 /backup/d2.sql /backup_mds/d2.sql *** /backup/d2.sql 2022-05-18 19:19:46.197703855 -0700 --- /backup_mds/d2.sql 2022-05-18 19:20:00.132517730 -0700 *************** *** 10,12 **** ! CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'd2' /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; --- 10,13 ---- ! CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'd2' /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 -- DEFAULT ENCRYPTION='N' ! */; *************** *** 33,35 **** DELIMITER ;; ! CREATE DEFINER='root'@'localhost' PROCEDURE 'procedure_2'() BEGIN --- 34,37 ---- DELIMITER ;; ! CREATE PROCEDURE 'procedure_2'() ! SQL SECURITY INVOKER BEGIN *************** *** 55,57 **** DELIMITER ;; ! CREATE DEFINER='root'@'localhost' PROCEDURE 'procedure_1'() SQL SECURITY INVOKER --- 57,59 ---- DELIMITER ;; ! CREATE PROCEDURE 'procedure_1'() SQL SECURITY INVOKER |
d2@t1.triggers.sqlを比較します。こちらもCREATE TRIGGER文からDEFINER句が削除されています。
|
1 2 3 4 5 |
# diff /backup/d2@t1.triggers.sql /backup_mds/d2@t1.triggers.sql 22c22 < /*!50003 CREATE DEFINER='root'@'localhost' TRIGGER 'test_trigger_1' AFTER INSERT ON 't1' FOR EACH ROW INSERT INTO d2.t2 VALUES(1) */;; --- > /*!50003 CREATE TRIGGER 'test_trigger_1' AFTER INSERT ON 't1' FOR EACH ROW INSERT INTO d2.t2 VALUES(1) */;; |
最後に@.users.sql を比較します。見やすいように出力を半分に分けています。
nopass@localhostはパスワードが空なので、ocimdsオプションの場合は、nopass@localhostのCREATE USER文とGRANT文が削除されています。
また、role_testロールはCREATE USER IF NOT EXISTS 'role_test'@'%'からCREATE ROLE IF NOT EXISTS 'role_test'@'%'に修正されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# diff /backup/@.users.sql /backup_mds/@.users.sql 10,13d9 < -- begin user 'nopass'@'localhost' < CREATE USER IF NOT EXISTS 'nopass'@'localhost' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; < -- end user 'nopass'@'localhost' < 15c11,12 < CREATE USER IF NOT EXISTS 'role_test'@'%' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; > CREATE ROLE IF NOT EXISTS 'role_test'@'%'; > ALTER USER 'role_test'@'%' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT --- PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; 22,25d18 < -- begin grants 'nopass'@'localhost' < GRANT USAGE ON *.* TO 'nopass'@'localhost'; < -- end grants 'nopass'@'localhost' < |
続いて、root@localhostの権限について、もともとはすべての権限を持っていましたが、MDSの制限に基づいて権限が削除されています。
|
1 2 3 4 5 6 7 |
31,33c24,25 < GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO 'root'@'localhost' WITH GRANT OPTION; < GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO 'root'@'localhost' WITH GRANT OPTION; < GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION; --- > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO 'root'@'localhost' WITH GRANT OPTION; > GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,REPLICATION_APPLIER,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,XA_RECOVER_ADMIN ON *.* TO 'root'@'localhost' WITH GRANT OPTION; |
具体的には以下の権限が削除されていました。
|
1 2 3 4 5 6 7 8 9 |
RELOAD, SHUTDOWN,FILE, SUPER, CREATE TABLESPACE, PROXY, AUDIT_ABORT_EXEMPT, AUDIT_ADMIN, AUTHENTICATION_POLICY_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, ENCRYPTION_KEY_ADMIN, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, GROUP_REPLICATION_STREAM, INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE, PASSWORDLESS_USER_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_SLAVE_ADMIN,ROLE_ADMIN, SENSITIVE_VARIABLES_OBSERVER, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN |
まとめ
MDSの制限は複数あり、それらを把握しデータ移行の際にダンプファイルを手動で修正するのは大変です。
今回紹介したocimdsオプションを使用することで、これらを自動で修正してくれるため非常に便利な機能です。また、本記事で具体的にどのような修正が行われるかが確認できたかと思います。
データ移行の際は、ぜひMySQL Shellの活用をご検討ください。
