[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 -  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キーを追加します、

「公開キーの追加」をクリックします。
![スクリーンショット 2020-11-24 16.52.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/d1ec5c57-5266-b12e-3966-e5be0e9d6073.png)

**/home/opc/.oci/oci_api_key_public.pem**の内容をコピー＆ペーストして「追加」をクリックします。
![スクリーンショット 2020-11-24 16.52.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/a42e8b9c-fad7-ab13-6a44-a20f83ead6b1.png)
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**を確認してみます。
![スクリーンショット 2020-11-24 16.56.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/d6f428af-d473-2361-d352-157ba08063f7.png)
**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からアクセスします。
![スクリーンショット 2020-11-24 15.50.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/3730dfc2-ec67-21ac-f42f-bef7fd56be4b.png)

オブジェクト・ストレージにアクセスするためのクレデンシャルを作成します。

```
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;
```

![スクリーンショット 2020-11-24 17.14.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/7751d8d7-d27d-85b7-c864-a5d339ec508a.png)

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;
```
![スクリーンショット 2020-11-24 17.17.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/dc3018b9-01db-ea8b-3be4-458332b81f2a.png)

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](https://dev.mysql.com/doc/mysql-shell/8.0/en/)
[OCI CLI](https://docs.cloud.oracle.com/ja-jp/iaas/Content/API/Concepts/cliconcepts.htm)
[Autunomous Data Warehouse：外部データの問い合わせ](https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/query-external-data.html#GUID-72DC8BB6-5245-4262-A100-C35A2E553F70)