- 投稿日：2020-11-24T19:03:08+09:00
[OCI] MySQL DBシステムからAutonomous Databaseへのデータ連携をMySQL Shellと外部表を使ってお手軽にやってみた
はじめに
MySQL Shellの持つオブジェクト・ストレージに表データをエクスポートする機能と、Autonomous Databaseの持つオブジェクト・ストレージ上のファイルを外部表として利用できる機能を用いて、Oracle Cloud上のMySQLのPaaSであるMySQL DBシステムのデータをAutonomous Databaseに連携するための方法を検証してみました。
1. MySQL ShellとMySQL Clientのインストール
コンピュート・インスタンス（Oracle Linux 7.9）にMySQL ShellとMySQL Clientをインストールします。
mysql80-community-release-el7-3.noarch.rpmをインストールします。
[opc@dev2 ~]$ sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Retrieving https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm warning: /var/tmp/rpm-tmp.xGjyoc: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql80-community-release-el7-3 ################################# [100%] [opc@dev2 ~]$ yum repolist all | grep mysql mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community disabled mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - disabled mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community disabled mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - disabled mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community disabled mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community - disabled mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 175 mysql-connectors-community-source MySQL Connectors Community - disabled mysql-tools-community/x86_64 MySQL Tools Community enabled: 120 mysql-tools-community-source MySQL Tools Community - Sourc disabled mysql-tools-preview/x86_64 MySQL Tools Preview disabled mysql-tools-preview-source MySQL Tools Preview - Source disabled mysql55-community/x86_64 MySQL 5.5 Community Server disabled mysql55-community-source MySQL 5.5 Community Server - disabled mysql56-community/x86_64 MySQL 5.6 Community Server disabled mysql56-community-source MySQL 5.6 Community Server - disabled mysql57-community/x86_64 MySQL 5.7 Community Server disabled mysql57-community-source MySQL 5.7 Community Server - disabled mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 211 mysql80-community-source MySQL 8.0 Community Server - disabledmysql-shellとmysql-community-clientをインストールします。
[opc@dev2 ~]$ sudo yum install -y mysql-shell mysql-community-client Loaded plugins: langpacks, ulninfo mysql-connectors-community | 2.6 kB 00:00:00 mysql-tools-community | 2.6 kB 00:00:00 mysql80-community | 2.6 kB 00:00:00 ol7_UEKR6 | 2.8 kB 00:00:00 ol7_addons | 2.8 kB 00:00:00 ol7_developer | 2.8 kB 00:00:00 ol7_developer_EPEL | 3.4 kB 00:00:00 ol7_ksplice | 2.8 kB 00:00:00 ol7_latest | 3.4 kB 00:00:00 ol7_oci_included | 2.9 kB 00:00:00 ol7_optional_latest | 2.8 kB 00:00:00 ol7_software_collections | 2.8 kB 00:00:00 (1/22): mysql-tools-community/x86_64/primary_db | 83 kB 00:00:00 (2/22): mysql80-community/x86_64/primary_db | 128 kB 00:00:00 (3/22): mysql-connectors-community/x86_64/primary_db | 68 kB 00:00:00 (4/22): ol7_UEKR6/x86_64/updateinfo | 56 kB 00:00:00 (5/22): ol7_developer/x86_64/primary_db | 657 kB 00:00:00 (6/22): ol7_developer_EPEL/x86_64/group_gz | 87 kB 00:00:00 (7/22): ol7_developer_EPEL/x86_64/updateinfo | 6.3 kB 00:00:00 (8/22): ol7_addons/x86_64/updateinfo | 94 kB 00:00:00 (9/22): ol7_ksplice/updateinfo | 6.1 kB 00:00:00 (10/22): ol7_ksplice/primary_db | 1.2 MB 00:00:00 (11/22): ol7_latest/x86_64/group_gz | 134 kB 00:00:00 (12/22): ol7_developer/x86_64/updateinfo | 7.9 kB 00:00:00 (13/22): ol7_developer_EPEL/x86_64/primary_db | 12 MB 00:00:00 (14/22): ol7_addons/x86_64/primary_db | 163 kB 00:00:00 (15/22): ol7_latest/x86_64/updateinfo | 3.1 MB 00:00:00 (16/22): ol7_oci_included/x86_64/primary_db | 404 kB 00:00:00 (17/22): ol7_UEKR6/x86_64/primary_db | 8.4 MB 00:00:00 (18/22): ol7_optional_latest/x86_64/updateinfo | 1.2 MB 00:00:00 (19/22): ol7_latest/x86_64/primary_db | 29 MB 00:00:00 (20/22): ol7_software_collections/x86_64/updateinfo | 8.6 kB 00:00:00 (21/22): ol7_optional_latest/x86_64/primary_db | 5.2 MB 00:00:00 (22/22): ol7_software_collections/x86_64/primary_db | 5.2 MB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package mysql-community-client.x86_64 0:8.0.22-1.el7 will be installed --> Processing Dependency: mysql-community-client-plugins = 8.0.22-1.el7 for package: mysql-community-client-8.0.22-1.el7.x86_64 --> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.22-1.el7.x86_64 ---> Package mysql-shell.x86_64 0:8.0.22-1.el7 will be installed --> Running transaction check ---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be obsoleted --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-9.el7.x86_64 --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-9.el7.x86_64 ---> Package mysql-community-client-plugins.x86_64 0:8.0.22-1.el7 will be installed ---> Package mysql-community-libs.x86_64 0:8.0.22-1.el7 will be obsoleting --> Processing Dependency: mysql-community-common(x86-64) >= 8.0.11 for package: mysql-community-libs-8.0.22-1.el7.x86_64 --> Running transaction check ---> Package mysql-community-common.x86_64 0:8.0.22-1.el7 will be installed ---> Package mysql-community-libs-compat.x86_64 0:8.0.22-1.el7 will be obsoleting --> Finished Dependency Resolution Dependencies Resolved ================================================================================== Package Arch Version Repository Size ================================================================================== Installing: mysql-community-client x86_64 8.0.22-1.el7 mysql80-community 48 M mysql-community-libs x86_64 8.0.22-1.el7 mysql80-community 4.6 M replacing mariadb-libs.x86_64 1:5.5.68-1.el7 mysql-community-libs-compat x86_64 8.0.22-1.el7 mysql80-community 1.2 M replacing mariadb-libs.x86_64 1:5.5.68-1.el7 mysql-shell x86_64 8.0.22-1.el7 mysql-tools-community 29 M Installing for dependencies: mysql-community-client-plugins x86_64 8.0.22-1.el7 mysql80-community 235 k mysql-community-common x86_64 8.0.22-1.el7 mysql80-community 616 k Transaction Summary ================================================================================== Install 4 Packages (+2 Dependent packages) Total download size: 83 M Downloading packages: warning: /var/cache/yum/x86_64/7Server/mysql80-community/packages/mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Public key for mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm is not installed (1/6): mysql-community-client-plugins-8.0.22-1.el7.x86_64. | 235 kB 00:00:00 (2/6): mysql-community-common-8.0.22-1.el7.x86_64.rpm | 616 kB 00:00:00 (3/6): mysql-community-libs-8.0.22-1.el7.x86_64.rpm | 4.6 MB 00:00:00 (4/6): mysql-community-libs-compat-8.0.22-1.el7.x86_64.rpm | 1.2 MB 00:00:00 (5/6): mysql-community-client-8.0.22-1.el7.x86_64.rpm | 48 MB 00:00:00 Public key for mysql-shell-8.0.22-1.el7.x86_64.rpm is not installed (6/6): mysql-shell-8.0.22-1.el7.x86_64.rpm | 29 MB 00:00:00 ---------------------------------------------------------------------------------- Total 95 MB/s | 83 MB 00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>" Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5 Package : mysql80-community-release-el7-3.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mysql-community-client-plugins-8.0.22-1.el7.x86_64 1/7 Installing : mysql-community-common-8.0.22-1.el7.x86_64 2/7 Installing : mysql-community-libs-8.0.22-1.el7.x86_64 3/7 Installing : mysql-community-libs-compat-8.0.22-1.el7.x86_64 4/7 Installing : mysql-community-client-8.0.22-1.el7.x86_64 5/7 Installing : mysql-shell-8.0.22-1.el7.x86_64 6/7 Erasing : 1:mariadb-libs-5.5.68-1.el7.x86_64 7/7 Verifying : mysql-community-libs-8.0.22-1.el7.x86_64 1/7 Verifying : mysql-community-client-plugins-8.0.22-1.el7.x86_64 2/7 Verifying : mysql-community-common-8.0.22-1.el7.x86_64 3/7 Verifying : mysql-shell-8.0.22-1.el7.x86_64 4/7 Verifying : mysql-community-libs-compat-8.0.22-1.el7.x86_64 5/7 Verifying : mysql-community-client-8.0.22-1.el7.x86_64 6/7 Verifying : 1:mariadb-libs-5.5.68-1.el7.x86_64 7/7 Installed: mysql-community-client.x86_64 0:8.0.22-1.el7 mysql-community-libs.x86_64 0:8.0.22-1.el7 mysql-community-libs-compat.x86_64 0:8.0.22-1.el7 mysql-shell.x86_64 0:8.0.22-1.el7 Dependency Installed: mysql-community-client-plugins.x86_64 0:8.0.22-1.el7 mysql-community-common.x86_64 0:8.0.22-1.el7 Replaced: mariadb-libs.x86_64 1:5.5.68-1.el7 Complete! [opc@dev2 ~]$2. MySQL Shellの動作確認とデータの準備
adminユーザでMySQL ShellからMySQL DBシステムに接続します。
[opc@dev2 ~]$ mysqlsh --uri admin@mysql1.subnet2.vcn1.oraclevcn.com:33060 Please provide the password for 'admin@mysql1.subnet2.vcn1.oraclevcn.com:33060': ************ Save password for 'admin@mysql1.subnet2.vcn1.oraclevcn.com:33060'? [Y]es/[N]o/Ne[v]er (default No): Y 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 'admin@mysql1.subnet2.vcn1.oraclevcn.com:33060' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 102 (X protocol) Server version: 8.0.22-u2-cloud MySQL Enterprise - Cloud No default schema selected; type \use <schema> to set one.テスト用に以下のものを作成します。
・DB：testdb
・ユーザ：test
・表： deptMySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl JS > \sql CREATE DATABASE testdb; Query OK, 1 row affected (0.0104 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl JS > \sql CREATE USER 'test' IDENTIFIED BY 'Demo#1Demo#1'; Query OK, 0 rows affected (0.0062 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl JS > \sql GRANT ALL ON testdb.* TO 'test'; Query OK, 0 rows affected (0.0032 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl JS > \use testdb Default schema `testdb` accessible through db. MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql CREATE TABLE dept (deptno INT ,dname VARCHAR(14), loc VARCHAR(13) , PRIMARY KEY (deptno) ); Query OK, 0 rows affected (0.0141 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); Query OK, 1 row affected (0.0037 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); Query OK, 1 row affected (0.0035 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql INSERT INTO dept VALUES (30,'SALES','CHICAGO'); Query OK, 1 row affected (0.0026 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); Query OK, 1 row affected (0.0032 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql COMMIT; Query OK, 0 rows affected (0.0006 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql SELECT * FROM dept; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.0008 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > Bye! [opc@dev2 ~]$testユーザでMySQL DBシステム上のtestdbに接続します。
[opc@dev2 ~]$ mysqlsh --uri test@mysql1.subnet2.vcn1.oraclevcn.com:33060/testdb Please provide the password for 'test@mysql1.subnet2.vcn1.oraclevcn.com:33060': ************ Save password for 'test@mysql1.subnet2.vcn1.oraclevcn.com:33060'? [Y]es/[N]o/Ne[v]er (default No): Y 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 'test@mysql1.subnet2.vcn1.oraclevcn.com:33060/testdb' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 106 (X protocol) Server version: 8.0.22-u2-cloud MySQL Enterprise - Cloud Default schema `testdb` accessible through db. MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql SELECT * FROM dept; Fetching table and column names from `testdb` for auto-completion... Press ^C to stop. +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.0004 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > Bye! [opc@dev2 ~]$3. OCI CLIのインストールと設定
MySQL Shellからオブジェクト・ストレージにアクセスする際にOCI CLIの設定情報を使用するので、OCI CLIをインストールします。
[opc@dev2 ~]$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 16053 100 16053 0 0 37438 0 --:--:-- --:--:-- --:--:-- 37507 ****************************************************************************** You have started the OCI CLI Installer in interactive mode. If you do not wish to run this in interactive mode, please include the --accept-all-defaults option. If you have the script locally and would like to know more about input options for this script, then you can run: ./install.sh -h If you would like to know more about input options for this script, refer to: https://github.com/oracle/oci-cli/blob/master/scripts/install/README.rst ****************************************************************************** Downloading Oracle Cloud Infrastructure CLI install script from https://raw.githubusercontent.com/oracle/oci-cli/v2.14.4/scripts/install/install.py to /tmp/oci_cli_install_tmp_s3T4. ######################################################################## 100.0% Running install script. python3 /tmp/oci_cli_install_tmp_s3T4 -- Verifying Python version. -- Python version 3.6.8 okay. ===> In what directory would you like to place the install? (leave blank to use '/home/opc/lib/oracle-cli'): -- Creating directory '/home/opc/lib/oracle-cli'. -- We will install at '/home/opc/lib/oracle-cli'. ===> In what directory would you like to place the 'oci' executable? (leave blank to use '/home/opc/bin'): -- Creating directory '/home/opc/bin'. -- The executable will be in '/home/opc/bin'. ===> In what directory would you like to place the OCI scripts? (leave blank to use '/home/opc/bin/oci-cli-scripts'): -- Creating directory '/home/opc/bin/oci-cli-scripts'. -- The scripts will be in '/home/opc/bin/oci-cli-scripts'. ===> Currently supported optional packages are: ['db (will install cx_Oracle)'] What optional CLI packages would you like to be installed (comma separated names; press enter if you don't need any optional packages)?: -- The optional packages installed will be ''. -- Trying to use python3 venv. -- Executing: ['/usr/bin/python3', '-m', 'venv', '/home/opc/lib/oracle-cli'] -- Executing: ['/home/opc/lib/oracle-cli/bin/pip', 'install', '--upgrade', 'pip'] Collecting pip Downloading https://files.pythonhosted.org/packages/cb/28/91f26bd088ce8e22169032100d4260614fc3da435025ff389ef1d396a433/pip-20.2.4-py2.py3-none-any.whl (1.5MB) 100% |████████████████████████████████| 1.5MB 729kB/s Installing collected packages: pip Found existing installation: pip 9.0.3 Uninstalling pip-9.0.3: Successfully uninstalled pip-9.0.3 Successfully installed pip-20.2.4 -- Executing: ['/home/opc/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpj_h4k49z', 'wheel', '--upgrade'] Collecting wheel Downloading wheel-0.35.1-py2.py3-none-any.whl (33 kB) Installing collected packages: wheel Successfully installed wheel-0.35.1 -- Executing: ['/home/opc/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpj_h4k49z', 'oci_cli', '--upgrade'] Collecting oci_cli Downloading oci_cli-2.15.0-py2.py3-none-any.whl (12.3 MB) |████████████████████████████████| 12.3 MB 11.7 MB/s Collecting jmespath==0.10.0 Downloading jmespath-0.10.0-py2.py3-none-any.whl (24 kB) Collecting terminaltables==3.1.0 Downloading terminaltables-3.1.0.tar.gz (12 kB) Collecting pytz>=2016.10 Downloading pytz-2020.4-py2.py3-none-any.whl (509 kB) |████████████████████████████████| 509 kB 37.0 MB/s Collecting pyOpenSSL==18.0.0 Downloading pyOpenSSL-18.0.0-py2.py3-none-any.whl (53 kB) |████████████████████████████████| 53 kB 2.7 MB/s Collecting retrying==1.3.3 Downloading retrying-1.3.3.tar.gz (10 kB) Collecting six==1.14.0 Downloading six-1.14.0-py2.py3-none-any.whl (10 kB) Collecting certifi Downloading certifi-2020.11.8-py2.py3-none-any.whl (155 kB) |████████████████████████████████| 155 kB 39.4 MB/s Collecting python-dateutil<3.0.0,>=2.5.3 Downloading python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB) |████████████████████████████████| 227 kB 38.0 MB/s Collecting cryptography==2.8 Downloading cryptography-2.8-cp34-abi3-manylinux2010_x86_64.whl (2.3 MB) |████████████████████████████████| 2.3 MB 37.2 MB/s Collecting configparser==4.0.2 Downloading configparser-4.0.2-py2.py3-none-any.whl (22 kB) Collecting PyYAML==5.1.2 Downloading PyYAML-5.1.2.tar.gz (265 kB) |████████████████████████████████| 265 kB 38.0 MB/s Collecting oci==2.24.0 Downloading oci-2.24.0-py2.py3-none-any.whl (7.0 MB) |████████████████████████████████| 7.0 MB 25.9 MB/s Collecting click==6.7 Downloading click-6.7-py2.py3-none-any.whl (71 kB) |████████████████████████████████| 71 kB 11.7 MB/s Collecting arrow==0.14.7 Downloading arrow-0.14.7-py2.py3-none-any.whl (39 kB) Collecting cffi!=1.11.3,>=1.8 Downloading cffi-1.14.3-cp36-cp36m-manylinux1_x86_64.whl (400 kB) |████████████████████████████████| 400 kB 38.5 MB/s Collecting pycparser Downloading pycparser-2.20-py2.py3-none-any.whl (112 kB) |████████████████████████████████| 112 kB 41.1 MB/s Building wheels for collected packages: terminaltables, retrying, PyYAML Building wheel for terminaltables (setup.py) ... done Created wheel for terminaltables: filename=terminaltables-3.1.0-py3-none-any.whl size=15354 sha256=bd83dbe85ab0051eaf79bb03c6ef4df21ea04294498e5a4514d0238dd4f2c2cd Stored in directory: /tmp/tmpj_h4k49z/wheels/86/1b/58/c23af2fe683acd8edc15d5a1268f0242be1ff2cf827fe34737 Building wheel for retrying (setup.py) ... done Created wheel for retrying: filename=retrying-1.3.3-py3-none-any.whl size=11429 sha256=cc5f0eb617a5687da3ae743f41cc545ea60748ee155e31fb805f820b6f22aa17 Stored in directory: /tmp/tmpj_h4k49z/wheels/ac/cb/8a/b27bf6323e2f4c462dcbf77d70b7c5e7868a7fbe12871770cf Building wheel for PyYAML (setup.py) ... done Created wheel for PyYAML: filename=PyYAML-5.1.2-cp36-cp36m-linux_x86_64.whl size=44103 sha256=04ee12ff94f291d305452dc12a6d9136999b6e32c27c61632fd619a0bc142be6 Stored in directory: /tmp/tmpj_h4k49z/wheels/d8/9b/e7/75af463b873c119dd444151fc54a8e190c87993593e1fa194a Successfully built terminaltables retrying PyYAML Installing collected packages: jmespath, terminaltables, pytz, six, pycparser, cffi, cryptography, pyOpenSSL, retrying, certifi, python-dateutil, configparser, PyYAML, oci, click, arrow, oci-cli Successfully installed PyYAML-5.1.2 arrow-0.14.7 certifi-2020.11.8 cffi-1.14.3 click-6.7 configparser-4.0.2 cryptography-2.8 jmespath-0.10.0 oci-2.24.0 oci-cli-2.15.0 pyOpenSSL-18.0.0 pycparser-2.20 python-dateutil-2.8.1 pytz-2020.4 retrying-1.3.3 six-1.14.0 terminaltables-3.1.0 ===> Modify profile to update your $PATH and enable shell/tab completion now? (Y/n): Y ===> Enter a path to an rc file to update (file will be created if it does not exist) (leave blank to use '/home/opc/.bashrc'): -- Backed up '/home/opc/.bashrc' to '/home/opc/.bashrc.backup' -- Tab completion set up complete. -- If tab completion is not activated, verify that '/home/opc/.bashrc' is sourced by your shell. -- -- ** Run `exec -l $SHELL` to restart your shell. ** -- -- Installation successful. -- Run the CLI with /home/opc/bin/oci --help [opc@dev2 ~]$ source .bashrc [opc@dev2 ~]$oci setup configコマンドでOCI CLIの設定を行います。
[opc@dev2 ~]$ oci setup config This command provides a walkthrough of creating a valid CLI config file. The following links explain where to find the information required by this script: User API Signing Key, OCID and Tenancy OCID: https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other Region: https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm General config documentation: https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm Enter a location for your config [/home/opc/.oci/config]: Enter a user OCID: ocid1.user.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Enter a tenancy OCID: ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Enter a region (e.g. ap-chiyoda-1, ap-chuncheon-1, ap-hyderabad-1, ap-melbourne-1, ap-mumbai-1, ap-osaka-1, ap-seoul-1, ap-sydney-1, ap-tokyo-1, ca-montreal-1, ca-toronto-1, eu-amsterdam-1, eu-frankfurt-1, eu-zurich-1, me-dubai-1, me-jeddah-1, sa-saopaulo-1, uk-cardiff-1, uk-gov-cardiff-1, uk-gov-london-1, uk-london-1, us-ashburn-1, us-gov-ashburn-1, us-gov-chicago-1, us-gov-phoenix-1, us-langley-1, us-luke-1, us-phoenix-1, us-sanjose-1): ap-tokyo-1 Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: Y Enter a directory for your keys to be created [/home/opc/.oci]: Enter a name for your key [oci_api_key]: Public key written to: /home/opc/.oci/oci_api_key_public.pem Enter a passphrase for your private key (empty for no passphrase): Private key written to: /home/opc/.oci/oci_api_key.pem Fingerprint: 49:13:0a:f4:df:ca:40:70:97:73:49:d7:9d:bf:df:ae Config written to /home/opc/.oci/config If you haven't already uploaded your API Signing public key through the console, follow the instructions on the page linked below in the section 'How to upload the public key': https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2 [opc@dev2 ~]$4. APIキーの登録
「コンソールメニュー」→「ユーザー」→「ユーザーの詳細」→「APIキー」
オブジェクト・ストレージ・バケットへのアクセスに使用するユーザのAPIキーを追加します、
「公開キーの追加」をクリックします。
/home/opc/.oci/oci_api_key_public.pemの内容をコピー＆ペーストして「追加」をクリックします。
APIキーの登録が完了したら、フィンガープリントをメモしておきます。5. 表データのオブジェクト・ストレージ・バケットへのエクスポート
MySQL Shellを用いて、MySQL DBシステム内のデータベースtestdbにあるdept表の内容を、オブジェクト・ストレージ・バケットMySQLtoADBにエクスポートします。
[opc@dev2 ~]$ mysqlsh --uri test@mysql1.subnet2.vcn1.oraclevcn.com:33060/testdb 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 'test@mysql1.subnet2.vcn1.oraclevcn.com:33060/testdb' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 112 (X protocol) Server version: 8.0.22-u2-cloud MySQL Enterprise - Cloud Default schema `testdb` accessible through db. MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > util.exportTable("testdb.dept", "dept.csv", { dialect: "csv", "osBucketName": "MySQLtoADB", "osNamespace": "XXXXXXXXXXXX" }) Preparing data dump for table `testdb`.`dept` Data dump for table `testdb`.`dept` will use column `deptno` as an index Running data dump using 1 thread. NOTE: Progress information uses estimated values and may not be accurate. Data dump for table `testdb`.`dept` will be written to 1 file 100% (4 rows / ~4 rows), 0.00 rows/s, 0.00 B/s Duration: 00:00:00s Data size: 100 bytes Rows written: 4 Bytes written: 100 bytes Average throughput: 100.00 B/s The dump can be loaded using: util.importTable("dept.csv", { "characterSet": "utf8mb4", "dialect": "csv", "osBucketName": "MySQLtoADB", "osNamespace": "XXXXXXXXXXXX", "schema": "testdb", "table": "dept" }) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > Bye! [opc@dev2 ~]$オブジェクト・ストレージ・バケットMySQLtoADBを確認してみます。
util.exportTableコマンドで指定したdept.csvという名前のファイルが作成されていることが確認できました。dept.csvをダウンロードして、ファイルの内容を見てみます。
dept.csv10,"ACCOUNTING","NEW YORK" 20,"RESEARCH","DALLAS" 30,"SALES","CHICAGO" 40,"OPERATIONS","BOSTON"MySQL DBシステム内のデータベースtestdbにあるdept表の内容がCSV形式でオブジェクト・ストレージ・バケットMySQLtoADBにdept.csvというファイル名で出力されたことが確認できました。
のちほどスクリプト化して実行するために、MySQL Shell内で実行するコマンドをファイルとして保存しておきます。
ファイルexport_to_os_bucket.jsを作成します。
[opc@dev2 ~]$ vi /home/opc/export_to_os_bucket.js/home/opc/export_to_os_bucket.jsutil.exportTable("testdb.dept", "dept.csv", { dialect: "csv", "osBucketName": "MySQLtoADB", "osNamespace": "nrhnlrqdttaw" })6. Autonomous Databaseでの外部表の作成
Autonomous DatabaseにSQL Developer Webからアクセスします。
オブジェクト・ストレージにアクセスするためのクレデンシャルを作成します。
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'MY_CRED', -- クレデンシャルの名前(任意の文字列) user_ocid => 'ocid1.user.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', --ユーザのOCID tenancy_ocid => 'ocid1.tenancy.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxx', --テナンシのOCID private_key => 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX', -- プライベートキー（oci_api_key.pemの内容からヘッダ、フッタを除いたもの） fingerprint => 'xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx' -- フィンガープリント ); END; /作成したクレデンシャルを使って、オブジェクト・ストレージ・バケットMySQLtoADB上のCSVファイルdept.csvをもとにした外部表dept_extを作成します。
BEGIN DBMS_CLOUD.CREATE_EXTERNAL_TABLE( table_name =>'dept_ext', -- 作成する外部表の名前 credential_name =>'MY_CRED', -- 認証に使用するクレデンシャルの名前 file_uri_list =>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/XXXXXXXXXXXX/b/MySQLtoADB/o/dept.csv', -- CSVファイルのエンドポイント format => json_object('type' value 'csv'), -- ファイルタイプの設定 column_list => 'deptno NUMBER, dname VARCHAR2(20), loc VARCHAR2(20)' -- 列名と型の宣言 ); END; /7. 動作確認
外部表dept_extの内容を確認します。
SELECT * FROM dept_ext;
MySQL DBシステムのdept表から出力したCSVファイルdept.csvの内容が反映されていることが確認できました。
ここで、MySQL DBシステム内のデータベースtestdbにあるdept表にレコードを追加します。
[opc@dev2 ~]$ mysqlsh --uri test@mysql1.subnet2.vcn1.oraclevcn.com:33060/testdb 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 'test@mysql1.subnet2.vcn1.oraclevcn.com:33060/testdb' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 125 (X protocol) Server version: 8.0.22-u2-cloud MySQL Enterprise - Cloud Default schema `testdb` accessible through db. MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql INSERT INTO dept VALUES (50,'VP OFFICE','AOYAMA'); Fetching table and column names from `testdb` for auto-completion... Press ^C to stop. Query OK, 1 row affected (0.0025 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql COMMIT; Query OK, 0 rows affected (0.0006 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > \sql SELECT * FROM dept; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 50 | VP OFFICE | AOYAMA | +--------+------------+----------+ 5 rows in set (0.0004 sec) MySQL mysql1.subnet2.vcn1.oraclevcn.com:33060+ ssl ... JS > Bye! [opc@dev2 ~]$MySQL Shellを使用して、再度dept表の内容をオブジェクト・ストレージ・バケットMySQLtoADBにエクスポートします。
[opc@dev2 ~]$ mysqlsh --uri test@mysql1.subnet2.vcn1.oraclevcn.com:33060/testdb < /home/opc/export_to_os_bucket.js Preparing data dump for table `testdb`.`dept` Data dump for table `testdb`.`dept` will use column `deptno` as an index Running data dump using 1 thread. NOTE: Progress information uses estimated values and may not be accurate. Data dump for table `testdb`.`dept` will be written to 1 file 125% (5 rows / ~4 rows), 0.00 rows/s, 0.00 B/s Duration: 00:00:00s Data size: 125 bytes Rows written: 5 Bytes written: 125 bytes Average throughput: 125.00 B/s The dump can be loaded using: util.importTable("dept.csv", { "characterSet": "utf8mb4", "dialect": "csv", "osBucketName": "MySQLtoADB", "osNamespace": "nrhnlrqdttaw", "schema": "testdb", "table": "dept" }) [opc@dev2 ~]$SQL Developer Webで外部表dept_extの内容を確認します。
SELECT * FROM dept_ext;
MySQL DBシステム内のデータベースtestdbにあるdept表の変更が、外部表dept_extに反映されていることが確認できました。
まとめ
MySQL Shellの持つOCI オブジェクト・ストレージに表データをエクスポートする機能と、Autonomous Databaseの持つオブジェクト・ストレージ上のファイルを外部表として利用できる機能を用いて、MySQL DBシステムとAutonomous Databaseの間で非常にシンプルにデータの連携ができることが確認できました。
cronなどでMySQL Shellによるオブジェクト・ストレージ・バケットへのエクスポートを自動実行することで、MySQL DBシステム側でのデータの更新を自動的にAutonomous Databaseに反映することができますね。
めでたし、めでたし。
参考文献
MySQL Shell 8.0
OCI CLI
Autunomous Data Warehouse：外部データの問い合わせ
- 投稿日：2020-11-24T11:57:58+09:00
mysqldumpで空ファイルしかできないと思ったら
mysqldumpで空ファイルしかできないと思ったら
背景
MySQLでdumpファイルを作りたく
mysqldump -uroot -p データベース名 > ファイル名.sqlのコマンドを打っているのにできるのは中身が空のファイル。
今までできていたのに何故？？？原因
単純なコマンドミスだった。
dumpファイル(空だが)自体は作成できていたため、指摘してもらうまでコマンドミスをしていることに全く気付かなった。mysql -uroot -p データベース名 > ファイル名.sqlでdumpの空ファイルはできる
間違い
mysql -uroot -p データベース名 > ファイル名.sql正解
mysqldump -uroot -p データベース名 > ファイル名.sqlmysqldumpと入力するべきところをmysqlと入力していた。
思い込むとなかなか気付けないこのミス。
空ファイルしかできなくて困ったときには一度コマンドを見直してみてください。
- 投稿日：2020-11-24T10:58:51+09:00
【コード・出力例あり】MySQLでテーブルを結合する方法【８選】
MySQLに置いてテーブル同士の結合方法を解説していきます。
用語解説
テーブル名：nameList
id name age addressId 1 Abe 42 3 2 Ide 31 2 3 Hide 24 3 4 Ide 39 1 テーブル
表の事。(上記なら nameList というテーブル名。)
レコード
表の行の事。(上記なら4つのレコードが存在する。)
カラム
表の列の事。(上記なら4つのカラムが存在する。)
テーブル結合について
※ちょっと小難しい話になりますので、飛ばしても構いません。
読む場合も「そうなんだ〜」程度に留めてもらって大丈夫です。正規化
まずデータベースを設計する段階で、カラムに重複する情報がある場合、そのカラムを別のテーブルに分けるという事をし、テーブルが保持するデータ量の削減と処理の高速化を図ります。これを
正規化と言います。
非正規化
正規化のようにテーブルを分けて、扱うデータ量を削減するのに対して、重複する値があってもテーブルを分けずに、１つのテーブルでデータをまとめてしまうという事。これが非正規化になります。
テーブル結合の意味
正規化されたデータは処理速度を早めるのに対して、まとまったデータの解析や抽出などには向いておりません。その為、データの解析や抽出を行う際は、あえて非正規化の状態に戻して、扱いやすいテーブルの構造にします。これがテーブル結合をする意味になります。
この記事で使用するテーブル
これ以降は指定がない限り、同様のテーブルを利用します。
テーブル名：nameList
id name age addressId 1 Abe 42 3 2 Ide 31 2 3 Hide 24 3 4 Ide 39 1
テーブル名：addressList
id address 1 Tokyo 2 Osaka 3 Aichi 4 Fukuoka
INNER JOIN(内部結合)
内部結合とは、２つのテーブルで共通のデータが存在しているレコードのみ抽出を行います。
実際のコード例はこちら。SELECT * FROM nameList INNER JOIN addressList ON nameList.addressId = addressList.id;これだけだとわかりにくいと思うので、順を追って解説していきます。
①まず元となるテーブル名を
FROMで記述。( nameList )
②その後ろにINNER JOINを記述。
③結合したいテーブル名を記述。( addressList )
④その後ろにONを記述。
⑤同じ情報が入ったカラムをイコールで繋ぐ。
SELECT内に複数のテーブルを指定する事になるので、カラムを指定する際はどのテーブルのカラムを言っているのか、テーブル名で指定してやる必要があります。(上記なら nameList.addressId と addressList.id です)また、
INNERは省略して書く事もできますが、他の人が見た際にわかりやすくするために、明示的に記述する事が多いです。
上記の出力結果はこちら。
id name age addressId id address 4 Ide 39 1 1 Tokyo 2 Ide 31 2 2 Osaka 1 Abe 42 3 3 Aichi 3 Hide 24 3 3 Aichi nameListテーブル が addressIdカラム 順に並び、隣にあるaddressテーブルのidカラムが対応しているというのが特徴です。
OUTER JOIN(外部結合)
外部結合とは、２つのテーブルで共通のデータが存在しているレコードと、結合させたテーブルでデータが存在しないレコードはNULLとして抽出を行います。
また、外部結合には
RIGHTとLEFTの２種類の結合方法があります。違いは、左右のテーブルの並び順です。
RIGHT JOIN
RIGHT JOINで結合したコードはこちらです。SELECT * FROM nameList RIGHT JOIN addressList ON nameList.addressId = addressList.id;簡単にいうと、先ほどの
INNER JOINの部分をRIGHT JOINに書き換えるだけです。
上記の出力結果はこちら。
id name age addressId id address 1 Abe 42 3 3 Aichi 2 Ide 31 2 2 Osaka 3 Hide 24 3 3 Aichi 4 Ide 39 1 1 Tokyo NULL NULL NULL NULL 4 Fukuoka 基本的には
INNER JOINと同じですが、RIGHT JOINの場合は nameListテーブル の addressIdカラム に、 addressListテーブル の idカラム と対応する値が無い場合、最後のレコードのようにNULL値を出力してくれるという点が違いです。
LEFT JOIN
LEFT JOINで結合したコードはこちらです。SELECT * FROM nameList LEFT JOIN addressList ON nameList.addressId = addressList.id;先程と同じで、
INNER JOINの部分をLEFT JOINに書き換えるだけです。
上記の出力結果はこちら。
id name age addressId id address 4 Ide 39 1 1 Tokyo 2 Ide 31 2 2 Osaka 1 Abe 42 3 3 Aichi 3 Hide 24 3 3 Aichi
RIGHT JOINは右側にあるテーブルをベースに、左側のテーブルに無い値をNULL値として出力。
LEFT JOINは左側にあるテーブルをベースに、右側のテーブルに無い値をNULL値とします。今回の例でいうと、左側にある addressListテーブル の idカラム に、 nameListテーブル の addressIdカラム で対応する値が全てあるので、NULL値の出力はありません。
そのため、こういう場合は
INNER JOINと同様の結果になります。
CROSS JOIN(掛け合わせ)
CROSS JOINとは、左右のテーブルで組み合わせ可能な全てのレコードを抽出する事ができます。例えば片方のテーブルで６件のレコード、もう片方のテーブルで４件のレコードが存在する場合、２４件のレコードからなるテーブルが出力されます。言葉ではわかりにくいと思うので、実際のコードから見ていきましょう。
SELECT * FROM nameList CROSS JOIN addressList;これまで紹介してきた結合と違うのは、
CROSS JOINの後に結合したいテーブルを記述した後、それぞれのテーブルで対応するカラム同士の記述が必要ないという点です。
上記の出力結果はこちら。
id name age addressId id address 1 Abe 42 3 1 Tokyo 2 Ide 31 2 1 Tokyo 3 Hide 24 3 1 Tokyo 4 Ide 39 1 1 Tokyo 1 Abe 42 3 2 Osaka 2 Ide 31 2 2 Osaka 3 Hide 24 3 2 Osaka 4 Ide 39 1 2 Osaka 1 Abe 42 3 3 Aichi 2 Ide 31 2 3 Aichi 3 Hide 24 3 3 Aichi 4 Ide 39 1 3 Aichi 1 Abe 42 3 4 Fukuoka 2 Ide 31 2 4 Fukuoka 3 Hide 24 3 4 Fukuoka 4 Ide 39 1 4 Fukuoka nameListテーブル のレコードは４件、 addressListテーブル のレコードも４件。したがって、全ての組み合わせを表示すると、
4レコード * 4レコード = 16レコード
という出力結果になります。
補足
因みに
CROSS JOINで、それぞれのテーブルで対応するカラム同士の記述をした場合は、INNER JOINと同じ出力結果になります。
実際に記述してみた例がこちら。SELECT * FROM nameList CROSS JOIN addressList ON nameList.addressId = addressList.id;
上記を実行すると、下記の出力結果のように、INNER JOINで内部結合した結果と同じになります。
id name age addressId id address 4 Ide 39 1 1 Tokyo 2 Ide 31 2 2 Osaka 1 Abe 42 3 3 Aichi 3 Hide 24 3 3 Aichi
SELF JOIN(同じテーブルの結合)
SELF JOINとは、同じテーブル同士を連結させて抽出する事ができます。こちらでは、以下のテーブルを利用します。
(住所ではなく家族関係を示すカラムとして、 addressIdカラム を familyIdカラム に変更。)
テーブル名：nameList
id name age familyId 1 Abe 42 3 2 Ide 31 2 3 Hide 24 3 4 Ide 39 1
では
SELF JOINする際の、実際のコードはこちら。SELECT * FROM nameList nL1 JOIN nameList nL2 ON nL1.familyId = nL2.id;
SELF JOINというクエリは無いので、代わりにJOIN(INNER JOIN)を利用します。ON以下でカラム同士の関係を表す際は、どちらのテーブルのカラムを指しているのか指定するために、テーブル名を記述した後にエイリアス(別名設定)を付けています。
では上記の出力結果はこちら。
id name age familyId id name age addressId 4 Ide 39 1 1 Abe 42 3 2 Ide 31 2 2 Ide 31 2 1 Abe 42 3 3 Hide 24 3 3 Hide 24 3 3 Hide 24 3 このように、同じテーブル同士のテーブル結合もできます。
ON以下でカラム同士の関係を逆にすると、左右のテーブルが入れ替わり、なんの為のテーブル結合かわかりにくくなるので、注意が必要です。
３つ以上のテーブル結合
３つ以上のテーブルを結合する場合は、テーブルの数だけ
JOINの記述するというやり方になります。例えば、何らかの理由で nameListテーブル に addressListテーブル を結合し、さらに nameListテーブル を結合するとします。その際のコードがこちら。
SELECT * FROM nameList nL1 INNER JOIN addressList aL ON nL1.id = aL.id INNER JOIN nameList nL2 ON aL.id = nL2.id;
上記の出力結果はこちら。
id name age addressId id address id name age addressId 1 Abe 42 3 1 Tokyo 1 Abe 42 3 2 Ide 31 2 2 Osaka 2 Ide 31 2 3 Hide 24 3 3 Aichi 3 Hide 24 3 4 Ide 39 1 4 Fukuoka 4 Ide 39 1 このように、３つ以上のテーブルの結合もできます。
ここでは省略しますが、
RIGHT JOINやLEFT JOINなどと組み合わせて、利用する事もできます。
UNION(縦結合)
今まで解説してきたテーブルは、全て横に結合するものでしたが、
UNIONを利用すればテーブル同士を縦に結合して抽出する事が可能です。また縦の結合には、
UNIONとUNION ALLという２種類の結合方法があります。違いは、重複しているレコードを抽出するかしないかです。
UNION
まずは実際のコードを見ていきましょう。
SELECT * FROM nameList WHERE id >= 3 UNION SELECT * FROM nameList WHERE id <= 2;特徴的なのは、
SELECTで作成したテーブル同士を、UNIONで記述ごと結合させている部分です。
解説すると、
UNIONの前にある記述は、 nameListテーブル から idカラム が３以上のレコードを取得。
id name age addressId 3 Hide 24 3 4 Ide 39 1
UNIONの後にある記述は、 nameListテーブル から idカラム が２以下のレコードを取得。
id name age addressId 1 Abe 42 3 2 Ide 31 2
そして、上記２つのテーブルを縦に結合するという流れです。
実際の出力結果はこちらです。
id name age addressId 3 Hide 24 3 4 Ide 39 1 1 Abe 42 3 2 Ide 31 2
また、
UNIONには重複したレコードは出力しない、という特徴があります。
例えば以下のコード。SELECT * FROM nameList WHERE id <= 3 UNION SELECT * FROM nameList WHERE id <= 2;
こちらの出力結果はこのようになります。
id name age addressId 1 Abe 42 3 2 Ide 31 2 3 Hide 24 3 さらに、ここでは紹介しませんが、別のテーブル同士での結合も可能です。
その際にカラムとして出力されるのは、同じカラム名とデータ型を持ったカラムのみになります。
UNION ALL
UNIONは重複するレコードを削除するのに対して、UNION ALLは重複するレコードも抽出を行います。
例えば、先ほどのコードをUNION ALLに変えたのがこちら。SELECT * FROM nameList WHERE id <= 3 UNION ALL SELECT * FROM nameList WHERE id <= 2;特徴としては、
UNIONの後にALLを付け加えただけです。
上記の出力結果がこちらです。
id name age addressId 1 Abe 42 3 2 Ide 31 2 3 Hide 24 3 1 Abe 42 3 2 Ide 31 2 このように
UNION ALLでは、重複したレコードも抽出を行うことが可能です。
その他の機能や注意事項は、UNIONで解説したものと同様です。
まとめ
以上が
MySQLにおける、テーブルの結合方法になります。ある程度使いこなせるようになれば、さらに
WHEREやGROUP BY、ORDER BYなどと組み合わせて、自由自在にデータの抽出が行えるようになります。また興味のある方は実際に
MySQLをインストールして、MySQL WorkbenchというGUIアプリを利用すれば、簡単に同じ事ができるので、ぜひ試してみてください。最後まで読んでいただき、ありがとうございました！
筆者：yuki｜学習10日目で初案件獲得→現在はフルスタックエンジニア転職に向けて学習中
Qiita：https://qiita.com/yuki4839
Twitter：https://twitter.com/yuki35522891
- 投稿日：2020-11-24T00:10:31+09:00
MySQL Shellを利用したOracle MySQL Database Serviceへのデータ移行
- Oracle MySQL Database Serviceの使い方メモ〜その3
- MySQL Shell Dump Utility / Dump Loading Utility
- ローカル環境のMySQL 5.7からOracle MySQL Database Service (MySQL 8.0) へのデータ移行
はじめに
MySQL Shellを利用してローカル環境で稼働しているMySQLServerからOracle Cloud Infrastructure (OCI) でマネージドサービスとして利用可能なOracle MySQL Database Serviceへのデータ移行方法を確認しました。Bastion (踏み台) サーバーを経由した方法とOCI Object Storageを利用した方法の2パターンを検証しています。MySQL Shell Dump Utility / Dump Loading Utilityについては前回の記事でも取り上げています。コマンドのオプション変数などツールの使い方でお悩みの方は下記の記事も併せてご確認ください。
MySQL Shell Dump UtilityとDump Loading Utilityの使い方
MySQL Shellを利用したクラウドバックアップ/リストア
検証環境
- MySQLバージョン : 8.0.22-u2-cloud (Oracle MySQL Database Service)
- MySQL Shell 8.0.22 : Bastionサーバ上およびクライアント上で利用
- macOS 10.15.7 Catalina : クライアント環境
- MySQL 5.7.32 : クライアント上のローカル環境で稼働・移行元のソースMySQLインスタンスとして利用
Bastion (踏み台) サーバーを経由したデータ移行
この方法はローカル環境でMySQL Shellを使用してエクスポートしたダンプファイルをBastionサーバにSCPでコピーして、BastionサーバのMySQL Shellを使用してMySQL Database Serviceインスタンスデータロードを行います。こちらの方法ではローカル環境からOCI上のBastionサーバへのファイル転送を行う手順が必要になりますが、何かしらの方法でファイルをOCI上にアップロードできれば良いので、後述するObject Storageを経由する方法に比べてローカル環境のネットワーク構成の制約はなく、セキュリティ要件に応じたネットワーク経路・方式を選択することができる利点があります。
構成イメージ
MySQL Shellはローカル環境とBastionサーバ上の2箇所で稼働することになります。
検証環境の準備
OCI側のBastionサーバおよびMySQL Database Serviceインスタンスの準備、BastionサーバへのMySQL Shellのインストール方法については前回の記事を参照してください。
Oracle MySQL Database Serviceのインスタンスを作ってMySQL Workbenchから接続してみた
ローカル環境へのMySQL Shellのインストールは、公式のダウンロードサイトよりインストーラをダウンロードして実行してください。
ローカル環境での作業
dryRunによる検証とCompatibilityオプションの付与
ローカル環境で稼働しているMySQL 5.7にMySQL Shellでアクセスします。Dump Instance Utility (
util.dumpInstance) では、ocimds: trueオプションを付与することでMySQL Database Serviceインスタンスへの移行性のチェックを行います。ダンプファイルをエクスポートする前にocimds: trueに加えてdryRun: trueオプションを付与して実行、 Compatibility Issueが無いことを確認します。下記の実行例では、1回目の実行ではERROR: User 'root'@'localhost' is granted restricted privilegesが発生したため、2回目の実行コマンドでcompatibility: ["strip_restricted_grants"]}オプションを追加しMySQL Database Service上で制約のある権限を取り除いています。$ 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 2 Server version: 5.7.32 Homebrew No default schema selected; type \use <schema> to set one. MySQL localhost test JS > util.dumpInstance("/Users/username/dumps", {dryRun: true, ocimds: 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 Checking for compatibility with MySQL Database Service 8.0.22 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade(). ERROR: User 'root'@'localhost' is granted restricted privileges: RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, PROXY (fix this with 'strip_restricted_grants' compatibility option) Compatibility issues with MySQL Database Service 8.0.22 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL. Util.dumpInstance: Compatibility issues were found (RuntimeError) MySQL localhost test JS > util.dumpInstance("/Users/username/dump", {dryRun: true, ocimds: true, compatibility: ["strip_restricted_grants"]}) 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 Checking for compatibility with MySQL Database Service 8.0.22 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade(). NOTE: User 'root'@'localhost' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, PROXY) removed Compatibility issues with MySQL Database Service 8.0.22 were found and repaired. Please review the changes made before loading them. 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`
compatibilityオプションの変数一覧など、util.dumpInstanceの利用方法については前回の記事を参照ください。MySQL Shell Dump UtilityとDump Loading Utilityの使い方
ダンプファイルのエクスポートとOCIへのアップロード
Compatibility Issueが無いことを確認後、
util.dumpInstanceをdryRunオプションなしで実行することでダンプファイルをエクスポートできます。下記手順ではエクスポートしたダンプ・ファイルをdumpディレクトリごと圧縮し、OCI上で稼働するXXX.XXX.XXX.XXXのパブリックIPを持つBastionサーバへscpでファイル転送を行っています。MySQL localhost test JS > util.dumpInstance("/Users/username/dump", {ocimds: true, compatibility: ["strip_restricted_grants"]}) 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 Checking for compatibility with MySQL Database Service 8.0.22 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade(). NOTE: User 'root'@'localhost' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, PROXY) removed Compatibility issues with MySQL Database Service 8.0.22 were found and repaired. Please review the changes made before loading them. 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` Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be accurate. Data dump for table `test`.`users` will be written to 2 files Data dump for table `test`.`revenue` will be written to 2 files 1 thds dumping - 150% (6 rows / ~4 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed Duration: 00:00:00s 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: 61.00 B/s Average compressed throughput: 0.00 B/s MySQL localhost test JS > \q Bye! $ tar -zcvf dump.tar.gz /Users/username/dump scp -i /Users/username/SSHKeys/privateKey dump.tar.gz opc@XXX.XXX.XXX.XXX:/home/opc $ tar -zcvf dump.tar.gz dump a dump a dump/@.sql a dump/@.users.sql a dump/test.sql a dump/test@users@@1.tsv.zst.idx a dump/test@users.json a dump/test@revenue@@1.tsv.zst a dump/test@users@@1.tsv.zst a dump/test@revenue.json a dump/test.json a dump/@.done.json a dump/test@users@0.tsv.zst.idx a dump/test@users.sql a dump/test@users@0.tsv.zst a dump/@.post.sql a dump/@.json a dump/test@revenue.sql a dump/test@revenue@@1.tsv.zst.idx a dump/test@revenue@0.tsv.zst a dump/test@revenue@0.tsv.zst.idx $ scp -i /Users/username/SSHKeys/privateKey dump.tar.gz opc@XXX.XXX.XXX.XXX:/home/opc dump.tar.gzOCI環境での作業
BastionサーバからMySQL Database Serviceインスタンスへのデータロード
OCIで稼働しているBastionサーバにアクセスし、アップロードしたファイルの確認と解凍を行います。ホームディレクトリ
/home/opc/にダンプファイル群が格納されたdumpディレクトリが作成されたことを確認してください$ ls dump.tar.gz $ tar -zxvf dump.tar.gz dump/ dump/@.sql dump/@.users.sql dump/test.sql dump/test@users@@1.tsv.zst.idx dump/test@users.json dump/test@revenue@@1.tsv.zst dump/test@users@@1.tsv.zst dump/test@revenue.json dump/test.json dump/@.done.json dump/test@users@0.tsv.zst.idx dump/test@users.sql dump/test@users@0.tsv.zst dump/@.post.sql dump/@.json dump/test@revenue.sql dump/test@revenue@@1.tsv.zst.idx dump/test@revenue@0.tsv.zst dump/test@revenue@0.tsv.zst.idx [opc@bastion ~]$ ls dump dump.tar.gzMySQL Shellを起動し、MySQL Database Serviceのユーザー名、エンドポイントのプライベートIPを使用してMySQL Database Serviceインスタンスへアクセスします。そして、Dump Loading Utility (
util.loadDump) をdryRun: trueオプションを付与して実行することでエラーの有無を確認します。下記の実行例では、1回目の実行ではMySQL version mismatch (RuntimeError)が発生したため、2回目の実行コマンドでignoreVersion: trueオプションを追加しエラーが解消できていることを確認しました。$ mysqlsh MDSUSERNAME@10.0.31.11 Please provide the password for 'MDSUSERNAME@10.0.31.11': ************** Save password for 'MDSUSERNAME@10.0.31.11'? [Y]es/[N]o/Ne[v]er (default No): y 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 'MDSUSERNAME@10.0.31.11' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 12 (X protocol) Server version: 8.0.22-u2-cloud MySQL Enterprise - Cloud No default schema selected; type \use <schema> to set one. MySQL 10.0.31.11:33060+ ssl JS > util.loadDump("/home/opc/dump",{dryRun: true}) Loading DDL and Data from '/home/opc/dump' using 4 threads. Opening dump... dryRun enabled, no changes will be made. Target is MySQL 8.0.22-u2-cloud. Dump was produced from MySQL 5.7.32 ERROR: 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. Enable the 'ignoreVersion' option to load anyway. Util.loadDump: MySQL version mismatch (RuntimeError) MySQL 10.0.31.11:33060+ ssl JS > util.loadDump("/home/opc/dump",{dryRun: true, ignoreVersion: true}) Loading DDL and Data from '/home/opc/dump' using 4 threads. Opening dump... dryRun enabled, no changes will be made. Target is MySQL 8.0.22-u2-cloud. 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. Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `test` [Worker002] Executing DDL script for `test`.`users` [Worker002] Executing DDL script for `test`.`revenue` Executing common postamble SQL No data loaded. 0 warnings were reported during the load.
util.loadDumpの利用方法の詳細については前回の記事を参照ください。MySQL Shell Dump UtilityとDump Loading Utilityの使い方
さらに下記では
dryRunオプションなしでDump Loading Utilityを実行し、testスキーマ、usersおよびrevenueテーブルがインポートされていることを確認できました。MySQL 10.0.31.11:33060+ ssl JS > util.loadDump("/home/opc/dump",{ignoreVersion: true}) Loading DDL and Data from '/home/opc/dump' using 4 threads. Opening dump... Target is MySQL 8.0.22-u2-cloud. 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. Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `test` [Worker002] Executing DDL script for `test`.`users` [Worker000] Executing DDL script for `test`.`revenue` [Worker002] test@users@@1.tsv.zst: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 [Worker000] test@revenue@@1.tsv.zst: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 [Worker001] test@revenue@0.tsv.zst: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 [Worker003] test@users@0.tsv.zst: Records: 2 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 10.0.31.11:33060+ ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL 10.0.31.11:33060+ ssl SQL > SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.0011 sec) MySQL 10.0.31.11: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 10.0.31.11:33060+ ssl test SQL > SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | revenue | | users | +----------------+ 2 rows in set (0.0025 sec) MySQL 10.0.31.11:33060+ ssl test SQL >OCI Object Storageを利用したデータ移行
この方法は、MySQL Shellを利用してローカル環境からOCI Object Storageへ直接ダンプファイルをエクスポートします。こちらの方法ではローカル環境からOCI上のBastionサーバへのファイル転送を行う手順が不要になり、安価なObject Storageを中間ストレージとして利用できるメリットがありますが、ローカルのMySQL Shellの実行環境から直接Object Storageへアクセスするネットワーク経路が必要になります。また、Object Storageの事前認証済リクエストを使用するため、セキュリティポリシーの確認、配慮が必要になります1。
構成イメージ
この手法でもMySQL Shellはローカル環境とBastionサーバ上の2箇所で稼働することになります。
検証環境の準備
ローカル環境ならびにBastionサーバ上にSQL Shellのインストールに加えてOCI CLIインストールと構成が必要になります。OCI CLIのインストールについては下記のドキュメント・チュートリアル・Qiita記事をご参照ください。
Oracle Cloud Infrastructureドキュメント コマンドライン・インタフェース(CLI)
Oracle Cloud : コマンド・ライン・インタフェース(CLI) をインストールしてみた
OCI CLIの構成ファイルであるconfig内の記述、API Keyのファイルパスは絶対パスで記載しないとMySQL Shellでエラーが発生するので注意が必要です。
ローカル環境での作業
Object Storageバケットへのダンプファイルのエクスポート
ローカル環境で稼働しているMySQL 5.7にMySQL Shellでアクセスします。そして、
ocimds: trueに加えてObject Storageへのアクセス情報をオプションで付与してDump Instance Utility (util.dumpInstance) を起動します。下記では、オブジェクト接頭辞にdumpinstance_onp2mdsを指定し、osBucketNameにObject Storageのバケット名mdsdump、osNamespaceにはObject Storageバケットの詳細コンソールから確認できるネームスペースを指定します。これらのオプション変数については前回の記事を参考にしてください。MySQL Shellを利用したクラウドバックアップ/リストア
下記実行例では、前項の
compatibilityオプションを参考にdryRun無しで実行していますが、実際の手順ではdryRun: trueオプションを付与して実行し、 Compatibility Issueが発生しないことを確認してください。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 18 Server version: 5.7.32 Homebrew No default schema selected; type \use <schema> to set one. MySQL localhost test JS > util.dumpInstance("dumpinstance_onp2mds", {dryRun: true, osBucketName:"mdsdump", osNamespace:"NAMESPACE", ocimds:true, compatibility:["strip_restricted_grants"]}) 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 Checking for compatibility with MySQL Database Service 8.0.22 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade(). NOTE: User 'root'@'localhost' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, PROXY) removed Compatibility issues with MySQL Database Service 8.0.22 were found and repaired. Please review the changes made before loading them. 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` MySQL localhost test JS > util.dumpInstance("dumpinstance_onp2mds", {osBucketName:"mdsdump", osNamespace:"NAMESPACE", ocimds:true, compatibility:["strip_restricted_grants"]}) 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 Checking for compatibility with MySQL Database Service 8.0.22 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade(). NOTE: User 'root'@'localhost' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, PROXY) removed Compatibility issues with MySQL Database Service 8.0.22 were found and repaired. Please review the changes made before loading them. 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` 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 1 thds dumping - 150% (6 rows / ~4 rows), 1.00 row/s, 19.00 B/s uncompressed, 0.00 B/s compressed Duration: 00:00:03s 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: 19.77 B/s Average compressed throughput: 0.00 B/s MySQL localhost test JS > \q Bye!OCI環境での作業
Object StorageからMySQL Database Serviceインスタンスへのデータロード
OCIで稼働しているBastionサーバにアクセスし、MySQL Shellを起動します。上記手順でダンプファイルがアップロードされたObject Storageへのアクセス情報をオプションで付与してDump Loading Utility (
util.dumpInstance) を実行することでObject StorageからMySQL Database Serviceインスタンスへデータロードを行うことができます。下記手順では、ignoreVersion: trueも付与した上でdryRunを実行、エラーが発生しないことを確認した上でデータのロードを行っています。$ mysqlsh MDSUSERNAME@10.0.31.11 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 'MDSUSERNAME@10.0.31.11' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 32 (X protocol) Server version: 8.0.22-u2-cloud MySQL Enterprise - Cloud No default schema selected; type \use <schema> to set one. MySQL 10.0.31.11:33060+ ssl JS > util.loadDump("dumpinstance_onp2mds", {dryRun: true, osBucketName:"mdsdump", osNamespace:"NAMESPACE", ignoreVersion: true}) Loading DDL and Data from OCI ObjectStorage bucket=mdsdump, prefix='dumpinstance_onp2mds' using 4 threads. Opening dump... dryRun enabled, no changes will be made. Target is MySQL 8.0.22-u2-cloud. 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` [Worker001] Executing DDL script for `test`.`revenue` [Worker000] Executing DDL script for `test`.`users` Executing common postamble SQL No data loaded. 0 warnings were reported during the load. MySQL 10.0.31.11:33060+ ssl JS > util.loadDump("dumpinstance_onp2mds", {osBucketName:"mdsdump", osNamespace:"NAMESPACE", ignoreVersion: true}) Loading DDL and Data from OCI ObjectStorage bucket=mdsdump, prefix='dumpinstance_onp2mds' using 4 threads. Opening dump... Target is MySQL 8.0.22-u2-cloud. 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` [Worker001] Executing DDL script for `test`.`revenue` [Worker000] Executing DDL script for `test`.`users` [Worker002] test@users@0.tsv.zst: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 [Worker003] test@revenue@0.tsv.zst: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 [Worker001] test@users@@1.tsv.zst: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 [Worker000] 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 10.0.31.11:33060+ ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL 10.0.31.11:33060+ ssl SQL > SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.0040 sec) MySQL 10.0.31.11: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 10.0.31.11:33060+ ssl test SQL > SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | revenue | | users | +----------------+ 2 rows in set (0.0015 sec)まとめ
本記事では、MySQL Shellを利用したMySQL Database Serviceインスタンスへのデータ移行方式の検証を行いました。実際に移行作業を検討する際は、移行元のMySQLバージョン、ローカル環境のネットワーク構成、セキュリティ要件、許容される移行時間などを踏まえて、従来から存在するmysqldumpなども含めて最適なツールを選択することが必要になります。データ移行も掛かる時間に目が行きがちですが、アプリケーションの動作確認と改修箇所の洗い出し、環境の切り戻し方式を含めた移行方式の策定、入念なリハーサルがクラウド移行を成功に導く重要な要素となります。
関連情報
MySQL Shell Dump UtilityとDump Loading Utilityの使い方
MySQL Shellを利用したクラウドバックアップ/リストア
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
