はじめに
Oracle GoldenGate Veridata 23c を試すシリーズの補足として、異なるデータベース間での表突合を実施した(4)で使用したMySQLの準備工程を作業メモとして残しておきます。
環境について
環境は GoldenGate Veridata 23c Server とは別のサーバに構築します。あくまで突合用の表を１つ作るだけなので最小限の環境としています。
OS : Oracle Linux 8.7 (8cre / 12GB RAM / 128GB HDD)
Oracle GoldenGate Veridata 23c マニュアルより
実装の手順および実行
1. MySQL モジュールの取得とインストール
[root@sourcedbsrv2 ~]# dnf module disable mysql
MySQL 8.4 LTS Community Server 4.3 MB/s | 1.0 MB 00:00
MySQL Connectors Community 933 kB/s | 156 kB 00:00
MySQL Tools 8.4 LTS Community 3.1 MB/s | 534 kB 00:00
依存関係が解決しました。
================================================================================================================================
パッケージ アーキテクチャー バージョン リポジトリー サイズ
================================================================================================================================
モジュールの無効化:
mysql
トランザクションの概要
================================================================================================================================
これでよろしいですか? [y/N]: y
完了しました!
[root@sourcedbsrv2 ~]# yum install mysql-community-server
メタデータの期限切れの最終確認: 0:00:25 時間前の 2025年04月17日 19時22分06秒 に実施しました。
依存関係が解決しました。
================================================================================================================================
パッケージ アーキテクチャー バージョン リポジトリー サイズ
================================================================================================================================
インストール:
mysql-community-server x86_64 8.4.5-1.el8 mysql-8.4-lts-community 61 M
置き換え mariadb-connector-c-config.noarch 3.1.11-2.el8_3
依存関係のインストール:
mysql-community-client x86_64 8.4.5-1.el8 mysql-8.4-lts-community 15 M
mysql-community-client-plugins x86_64 8.4.5-1.el8 mysql-8.4-lts-community 4.6 M
mysql-community-common x86_64 8.4.5-1.el8 mysql-8.4-lts-community 692 k
mysql-community-icu-data-files x86_64 8.4.5-1.el8 mysql-8.4-lts-community 2.2 M
mysql-community-libs x86_64 8.4.5-1.el8 mysql-8.4-lts-community 1.5 M
トランザクションの概要
================================================================================================================================
インストール 6 パッケージ
ダウンロードサイズの合計: 85 M
これでよろしいですか? [y/N]: y
パッケージのダウンロード:
(1/6): mysql-community-client-8.4.5-1.el8.x86_64.rpm 30 MB/s | 15 MB 00:00
(2/6): mysql-community-common-8.4.5-1.el8.x86_64.rpm 716 kB/s | 692 kB 00:00
(3/6): mysql-community-client-plugins-8.4.5-1.el8.x86_64.rpm 4.4 MB/s | 4.6 MB 00:01
(4/6): mysql-community-icu-data-files-8.4.5-1.el8.x86_64.rpm 2.7 MB/s | 2.2 MB 00:00
(5/6): mysql-community-libs-8.4.5-1.el8.x86_64.rpm 3.5 MB/s | 1.5 MB 00:00
(6/6): mysql-community-server-8.4.5-1.el8.x86_64.rpm 40 MB/s | 61 MB 00:01
--------------------------------------------------------------------------------------------------------------------------------
合計 33 MB/s | 85 MB 00:02
MySQL 8.4 LTS Community Server 3.0 MB/s | 3.1 kB 00:00
GPG 鍵 0xA8D3785C をインポート中:
Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
Fingerprint: BCA4 3417 C3B4 85DD 128E C6D4 B7B3 B788 A8D3 785C
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023
これでよろしいですか? [y/N]: y
鍵のインポートに成功しました
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
準備 : 1/1
インストール中 : mysql-community-common-8.4.5-1.el8.x86_64 1/7
インストール中 : mysql-community-client-plugins-8.4.5-1.el8.x86_64 2/7
インストール中 : mysql-community-libs-8.4.5-1.el8.x86_64 3/7
scriptletの実行中: mysql-community-libs-8.4.5-1.el8.x86_64 3/7
インストール中 : mysql-community-client-8.4.5-1.el8.x86_64 4/7
インストール中 : mysql-community-icu-data-files-8.4.5-1.el8.x86_64 5/7
scriptletの実行中: mysql-community-server-8.4.5-1.el8.x86_64 6/7
インストール中 : mysql-community-server-8.4.5-1.el8.x86_64 6/7
scriptletの実行中: mysql-community-server-8.4.5-1.el8.x86_64 6/7
廃止 : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 7/7
scriptletの実行中: mariadb-connector-c-config-3.1.11-2.el8_3.noarch 7/7
検証 : mysql-community-client-8.4.5-1.el8.x86_64 1/7
検証 : mysql-community-client-plugins-8.4.5-1.el8.x86_64 2/7
検証 : mysql-community-common-8.4.5-1.el8.x86_64 3/7
検証 : mysql-community-icu-data-files-8.4.5-1.el8.x86_64 4/7
検証 : mysql-community-libs-8.4.5-1.el8.x86_64 5/7
検証 : mysql-community-server-8.4.5-1.el8.x86_64 6/7
検証 : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 7/7
インストール済み:
mysql-community-client-8.4.5-1.el8.x86_64 mysql-community-client-plugins-8.4.5-1.el8.x86_64
mysql-community-common-8.4.5-1.el8.x86_64 mysql-community-icu-data-files-8.4.5-1.el8.x86_64
mysql-community-libs-8.4.5-1.el8.x86_64 mysql-community-server-8.4.5-1.el8.x86_64
完了しました!
2. MySQL 稼働確認
[root@sourcedbsrv2 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead)
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
[root@sourcedbsrv2 ~]# systemctl start mysqld
[root@sourcedbsrv2 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2025-04-17 19:33:08 EDT; 1s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 6893 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 6984 (mysqld)
Status: "Server is operational"
Tasks: 35 (limit: 77534)
Memory: 487.6M
CGroup: /system.slice/mysqld.service
mq6984 /usr/sbin/mysqld
4月 17 19:32:56 sourcedbsrv2.jp.oracle.com systemd[1]: Starting MySQL Server...
4月 17 19:33:08 sourcedbsrv2.jp.oracle.com systemd[1]: Started MySQL Server.
[root@sourcedbsrv2 ~]# cat /var/log/mysqld.log
2025-04-17T23:33:00.515605Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2025-04-17T23:33:00.518565Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.4.5) initializing of server in progress as process 6940
2025-04-17T23:33:00.550187Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-17T23:33:01.673359Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-17T23:33:03.595200Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 5,g,Oye:IUz-
2025-04-17T23:33:06.798844Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
2025-04-17T23:33:06.858216Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2025-04-17T23:33:07.103437Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.5) starting as process 6984
2025-04-17T23:33:07.115886Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-17T23:33:07.720034Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-17T23:33:08.241992Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2025-04-17T23:33:08.242060Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2025-04-17T23:33:08.301604Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2025-04-17T23:33:08.301799Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.5' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
3. MySQL 管理ユーザのパスワード確認と変更
[root@sourcedbsrv2 ~]# grep 'temporary password' /var/log/mysqld.log
2025-04-17T23:33:03.595200Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 5,g,Oye:IUz-
[root@sourcedbsrv2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.5
Copyright (c) 2000, 2025, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Welcome123##';
Query OK, 0 rows affected (0.00 sec)
mysql>
4. MySQL Database と Object
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
4 rows in set (0.01 sec)
CREATE USER 'oracle'@'localhost' IDENTIFIED BY 'Welcome123##';
mysql> CREATE USER 'oracle'@'localhost' IDENTIFIED BY 'Welcome123##';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | oracle |
| localhost | root |
+-----------+------------------+
5 rows in set (0.00 sec)
mysql> CREATE DATABASE mydb8451;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb8451 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mydb8451
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mydb8451 |
+------------+
1 row in set (0.00 sec)
mysql> GRANT ALL ON mydb8451.* to 'oracle'@'localhost';
Query OK, 0 rows affected (0.00 sec)
[root@sourcedbsrv2 ~]# mysql -u oracle -p mydb8451
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.5 MySQL Community Server - GPL
Copyright (c) 2000, 2025, 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> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mydb8451 |
+------------+
1 row in set (0.00 sec)
mysql> SHOW TABLES;
Empty set (0.01 sec)
mysql> CREATE TABLE EMP
-> (EMPNO INT,ENAME VARCHAR(10),JOB VARCHAR(9),MGR INT,HIREDATE DATE,SAL INT,COMM INT,DEPTNO INT);
Query OK, 0 rows affected (0.02 sec)
mysql> desc EMP;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | YES | | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | int | YES | | NULL | |
| COMM | int | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
mysql> select * from EMP;
+-------+-------+-------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
+-------+-------+-------+------+------------+------+------+--------+
1 row in set (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-2-1981','%d-%m-%Y'),1600,300,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-2-1981','%d-%m-%Y'),1250,500,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7566,'JONES','MANAGER',7839,STR_TO_DATE('2-4-1981','%d-%m-%Y'),2975,NULL,20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981','%d-%m-%Y'),1250,1400,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7698,'BLAKE','MANAGER',7839,STR_TO_DATE('1-5-1981','%d-%m-%Y'),2850,NULL,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7782,'CLARK','MANAGER',7839,STR_TO_DATE('9-6-1981','%d-%m-%Y'),2450,NULL,10);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7788,'SCOTT','ANALYST',7566,STR_TO_DATE('13-6-1987','%d-%m-%Y'),3000,NULL,20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7839,'KING','PRESIDENT',NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981','%d-%m-%Y'),1500,0,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7876,'ADAMS','CLERK',7788,STR_TO_DATE('13-6-1987','%d-%m-%Y'),1100,NULL,20);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO EMP VALUES
-> (7900,'JAMES','CLERK',7698,STR_TO_DATE('3-12-1981','%d-%m-%Y'),950,NULL,30);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7902,'FORD','ANALYST',7566,STR_TO_DATE('3-12-1981','%d-%m-%Y'),3000,NULL,20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EMP VALUES
-> (7934,'MILLER','CLERK',7782,STR_TO_DATE('23-1-1982','%d-%m-%Y'),1300,NULL,10);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from EMP;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-06-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-06-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
突合用のターゲットはOracle 19c (19.26)で、古いサンプルスキーマ SCOTTのものを使用します
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-06-13 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-06-13 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
SQL> desc scott.emp
名前 NULL? 型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
5. MySQL にリモート(Veridata 23c Server) から接続するためのユーザを作成
mysql> create user 'vdtuser'@'10.185.151.145' identified by 'Welcome123##';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'vdtuser'@'10.185.151.145' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT host, user FROM mysql.user;
+----------------+------------------+
| host | user |
+----------------+------------------+
| 10.185.151.145 | vdtuser |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | oracle |
| localhost | root |
+----------------+------------------+
6 rows in set (0.00 sec)
vdtuser で MySQL:mydb8451に作成した EMP表が検索できる事を確認しておくと良いです。
最後に
Oracle GoldenGate Veridata 23c を試すシリーズの補足として、異なるデータベース間での表突合のため、MySQLの環境を手探りで進めてきましたが、あくまで異なるデータベース上に同じデータを持つオブジェクトを準備する目的であり、決して実業務を想定したものではない事をご了承下さい。