はじめに
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 - disabled
mysql-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
・表： dept
MySQL 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のテーブルエクスポートユーティリティ util.exportTable()を用いて、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.csv
10,"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.js
util.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：外部データの問い合わせ