LoginSignup
2
1

More than 1 year has passed since last update.

@500InternalServerErrorin日本オラクル株式会社

[OCI]Autonomous Database：MySQL DBシステムへのデータベース・リンクを作成してデータの参照/追加/更新/削除を試してみた

Last updated at Posted at 2022-06-16

はじめに

プライベート・エンドポイントを持つAutonomous DatabaseからOracle CloudのMySQL DBシステムにデータベース・リンクが作成できるようになったので、検証してみました。

1.事前準備

下図のようなネットワークを構成し、パブリック・サブネットにSQL*Plus、mysqlクライアントをインストールしたComputeインスタンス、 プライベート・サブネットにAutonomous DatabaseとMySQL DBシステムを配置しました。
また、セキュリティ・リストで、Computeインスタンスからプライベート・サブネットへのネットワークアクセス、プライベート・サブネット内のインスタンス間でのネットワークアクセスを許可しました。

スクリーンショット 2022-06-16 14.59.44.png

MySQL DBシステムのエンドポイントは以下のようになっています。
スクリーンショット 2022-06-16 14.35.00.png

2.MySQL DBシステムの準備

Computeインスタンス上のmysqlクライアントからMySQL DBシステムに接続します。

[opc@client ~]$ mysql -u admin -p -h mysqlds.privatesubnet.testvcn1.oraclevcn.com
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.29-u1-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2022, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql>

データベース「testdb」を作成します。

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.01 sec)

mysql>

作成したデータベース「testdb」に接続します。

mysql> use testdb;
Database changed
mysql>

検証用のテーブル「dept」を作成します。

mysql> CREATE TABLE dept (
    -> deptno INT,
    -> dname VARCHAR(14),
    -> loc VARCHAR(13),
    -> PRIMARY KEY (deptno)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>

テーブル「dept」にデータを追加します。

mysql> INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
Query OK, 1 row affected (0.00 sec)

mysql>

テーブル「dept」の内容を確認します。

mysql> SELECT * FROM dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
+--------+------------+----------+
2 rows in set (0.00 sec)

mysql>

3. クレデンシャルの作成

SQL*Plusからプライベート・エンドポイントを持つAutonomous Databaseに接続します。

[opc@client ~]$ sqlplus admin/Demo#1Demo#1@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=privateadb.adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=p36dah33ehxmnrf_privateadb_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

SQL*Plus: Release 21.0.0.0.0 - Production on 木 6月 16 04:32:02 2022
Version 21.6.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

最終正常ログイン時間: 木 6月  16 2022 04:18:16 +00:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.1.0
に接続されました。
SQL>

DBMS_CLOUD.CREATE_CREDENTIALプロシージャを使用して、MySQL DBシステムに対するデータベース・リンクを作成する際に使用するクレデンシャル「MYSQLDS_LINK_CRED」を作成します。
credential_name：作成するクレデンシャルの名前
username：MySQLユーザのユーザ名
password：MySQLユーザのパスワード

SQL> BEGIN
  2    DBMS_CLOUD.CREATE_CREDENTIAL(
  3  .
SQL> BEGIN
  2  	DBMS_CLOUD.CREATE_CREDENTIAL(
  3  		credential_name => 'MYSQLDS_LINK_CRED',
  4  		username => 'admin',
  5  		password => 'Demo#1Demo#1'
  6  	);
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。

SQL>

4.データベースリンクの作成

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINKプロシージャを使用して、MySQL DBシステムに対するデータベース・リンクを作成を作成します。
db_link_name：作成するデータベース・リンクの名前
hostname：MySQL DSのホスト名
port：MySQL DSのMySQLポート
service_name：データベース名（ここではtestdb）
credential_name ：作成したクレデンシャルの名前
gateway_params：JSON_OBJECT('db_type' value 'MYSQL')
ssl_server_cert_dn：NULL
private_target：TRUE

SQL> BEGIN
  2  	DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
  3  		db_link_name => 'MYSQLDS_LINK', 
  4  		hostname => 'mysqlds.privatesubnet.testvcn1.oraclevcn.com', 
  5  		port => '3306',
  6  		service_name => 'testdb',
  7  		credential_name => 'MYSQLDS_LINK_CRED',
  8  		gateway_params => JSON_OBJECT('db_type' value 'MYSQL'),
  9  		ssl_server_cert_dn => NULL,
 10  		private_target => TRUE
 11  	);
 12  END;
 13  /

PL/SQLプロシージャが正常に完了しました。

SQL>

Autonomous DatabaseからMySQL DBシステムに接続するデータベース・リンクが作成できました。

5.動作確認

＜SELECT＞

SQL> col dname for a20
SQL> col loc for a20
SQL> SELECT * FROM "dept"@mysqlds_link;

    deptno dname        		loc
---------- -------------------- --------------------
    	10 ACCOUNTING   		NEW YORK
	    20 RESEARCH     		DALLAS

SQL>

データベースリンク経由で、MySQL DS内のテーブルのデータを参照できることが確認できました。

＜INSERT＞

SQL> INSERT INTO "dept"@mysqlds_link VALUES (30,'SALES','SAN JOSE');

1行が作成されました。

SQL> col dname for a20
SQL> col loc for a20
SQL> SELECT * FROM "dept"@mysqlds_link;

    deptno dname	        	loc
---------- -------------------- --------------------
    	10 ACCOUNTING	    	NEW YORK
    	20 RESEARCH	        	DALLAS
    	30 SALES	        	SAN JOSE

SQL> COMMIT;

コミットが完了しました。

SQL> SELECT * FROM "dept"@mysqlds_link;

    deptno dname	        	loc
---------- -------------------- --------------------
    	10 ACCOUNTING    		NEW YORK
    	20 RESEARCH	        	DALLAS
    	30 SALES	        	SAN JOSE
SQL>

MySQL側でのテーブル「dept」の内容の確認

mysql> SELECT * FROM dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | SAN JOSE |
+--------+------------+----------+
3 rows in set (0.00 sec)

mysql>

データベースリンク経由で、MySQL DS内のテーブルにデータをINSERTできることが確認できました。

＜UPDATE＞

SQL> UPDATE "dept"@mysqlds_link
  2  SET "dname" = 'MARKETING'
  3  WHERE "deptno" = 30;

1行が更新されました。

SQL> SELECT * FROM "dept"@mysqlds_link;

    deptno dname        		loc
---------- -------------------- --------------------
    	10 ACCOUNTING    		NEW YORK
    	20 RESEARCH     		DALLAS
    	30 MARKETING    		SAN JOSE

SQL> COMMIT;

コミットが完了しました。

SQL> SELECT * FROM "dept"@mysqlds_link;

    deptno dname	        	loc
---------- -------------------- --------------------
    	10 ACCOUNTING    		NEW YORK
    	20 RESEARCH	        	DALLAS
    	30 MARKETING    		SAN JOSE

SQL>

MySQL側でのテーブル「dept」の内容の確認

mysql> SELECT * FROM dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | MARKETING  | SAN JOSE |
+--------+------------+----------+
3 rows in set (0.00 sec)

mysql>

データベースリンク経由で、MySQL DS内のテーブルのデータをUPDATEできることが確認できました。

＜DELETE＞

SQL> DELETE FROM "dept"@mysqlds_link
  2  WHERE "deptno" = 30;

1行が削除されました。

SQL> SELECT * FROM "dept"@mysqlds_link;

    deptno dname        		loc
---------- -------------------- --------------------
	    10 ACCOUNTING   	 	NEW YORK
    	20 RESEARCH      		DALLAS

SQL> COMMIT;

コミットが完了しました。

SQL> SELECT * FROM "dept"@mysqlds_link;

    deptno dname        	 	loc
---------- -------------------- --------------------
    	10 ACCOUNTING   		NEW YORK
    	20 RESEARCH     		DALLAS

SQL>

MySQL側でのテーブル「dept」の内容の確認

mysql> SELECT * FROM dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
+--------+------------+----------+
2 rows in set (0.00 sec)

mysql>

データベースリンク経由で、MySQL DS内のテーブルのデータをDELETEできることが確認できました。

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1