How to migrate Docker containers from Oracle to MySQL

⛳️ 1. Create a private network

docker network create --subnet=168.66.6.0/24 db-network
[root@docker ~]# docker network inspect db-network --查看网络信息

##⛳️ 2. Oracle 12C deployment

✨ 2.1 Image download

docker pull registry.cn-shanghai.aliyuncs.com/techerwang/oracle:ora12c_12201

✨ 2.2 Container creation

docker run -itd --name jemora1221 -h jemora1221
–net=db-network --ip 168.66.6.34
-p 1526:1521 -p 3396:3389
–privileged=true
registry.cn-shanghai.aliyuncs.com/techerwang/oracle:ora12c_12201 init

✨ 2.3 Create business user

[root@jeames ~]# docker exec -it jemora1221 bash
[root@jemora1221 /]# su - oracle
[oracle@jemora1221 ~]$ sqlplus / as sysdba
SYS@jem> startup
SYS@jem> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JEMPDB                         MOUNTED
alter pluggable database JEMPDB open;
SYS@jem> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
SYS@jem> alter session set container=JEMPDB;
SYS@jem> create user jemmes identified by jemmes
SYS@jem> GRANT DBA to jemmes ;

✨ 2.4 Monitoring startup

[oracle@jemora1221 ~]$ cd $ORACLE_HOME/network
[oracle@jemora1221 network]$ cd admin

## 配置TNS，后续连接数据库
[oracle@jemora1221 admin]$ vi tnsnames.ora
JEMPDB =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = jemora1221)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = JEMPDB)
 )
 )
 
 ## 监听启动
lsnrctl start
lsnrctl status

⛳️ 3. MySQL8 deployment

✨ 3.1 Container creation

docker run -d --name mysql8027 -h mysql8027 -p 3418:3306
–net=db-network --ip 168.66.6.35
-v /etc/mysql/mysql8027/conf:/etc/mysql/conf.d
-e MYSQL_ROOT_PASSWORD=jeames -e TZ=Asia/Shanghai
mysql:8.0.27

View container

[root@jeames ~]# docker ps --format “table {<!--{cke_protected}{C}%3C!%2D%2D%20%2D%2D%3E-->{.ID}}\t{<!--{cke_protected}{C}%3C!%2D%2D%20%2D%2D%3E-->{.Names}}\t{<!--{cke_protected}{C}%3C!%2D%2D%20%2D%2D%3E-->{.Status}}”
CONTAINER ID NAMES STATUS
043d1652404d jemora1221 Up 7 minutes
dc2989508b67 mysql8027 Up 23 seconds
7e6a53d71017 centos7.8 Up 20 minutes

##✨ 3.2 Parameter settings

cat > /etc/mysql/mysql8027/conf/my.cnf << “EOF”
[mysqld]
default-time-zone = ‘+8:00&#39;
log_timestamps = SYSTEM
skip-name-resolve
log-bin
server_id=80273418
character_set_server=utf8mb4
default_authentication_plugin=mysql_native_password
EOF

✨ 3.3 Log in to MySQL

mysql -uroot -pjeames -h 168.66.6.35
create database jemdb;

⛳️ 4. Migrate kettle from Oracle to MySQL

✨4.1 Deploy kettle

Kettle is a foreign open source ETL tool written in pure Java and can run on Window, Linux, and Unix.

Kettle’s Chinese name is Kettle. MATT, the main programmer of the project, hopes to use various The data is put into a pot and then streamed out in a specified format. Kettle is an ETL toolset that allows you to manage data from different databases by providing a graphical user environment to describe what you want to do, rather than how you want to do it. There are two kinds of script files in Kettle, transformation and job. Transformation completes the basic transformation of data, and job completes the control of the entire workflow.

Composition of kettle

The whole process is divided into two steps: one is to install the JAVA environment; the other is to download the installation of kettle Package

Install AVA JDK

After downloading the jdk installation package, open the file and start the installation

Add the following 3 variables

[1]JAVA_HOME: The path to the Java installation just now, mine is: C:\Program Files\Java\jdk1.8.0_231

[2]CLASSPATH:.; %JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\lib\tools.jar;
[3] Configure environment variable Path
Double-click Path and add "%JAVA_HOME%\bin"; add "%JAVA_HOME%" \jre\bin".
Win R key pops up the run window, enter cmd, press Enter to run
Enter "java -version" and "javac" respectively to test. If the following content is displayed, the configuration is successful

Download the kettle installation package

Here we choose version 7.1

##After the download is complete, extract it to any path, open the folder and find Spoon .bat, create a desktop shortcut, open

Database driver package download

Put the mysql driver and oracle driver Just under the lib package under the kettle package.

MySQL JDBC driver download

The file suffix .tar.gz is a compressed package for Linux/IOS; the suffix .zip is a compressed package under Windows.

Download according to the system selection.
Download this version: mysql-connector-java-5.1.49-bin.jar
Oracle JDBC driver download

Database connection

A. Create transformation

Go to File-> New Transformation. After creating the new transformation, create a DB connection in the main object tree on the left to connect to the database

## B. Database connection MySQL

C. Database connection Oracle 12C

✨4.2 Oracle side data verification

## 创建测试表
 SYS@jem> conn jemmes/jemmes@JEMPDB    
@/home/oracle/demo_ora_create.sql
@/home/oracle/demo_ora_insert.sql

create table flower(
           id varchar2(32)  default sys_guid() primary key,
           flower_name varchar2(200),
           color varchar2(100),
           origin varchar2(200),
           moral varchar2(200),
           create_time timestamp default sysdate,
           update_time timestamp
    );
 JEMMES@JEMPDB> insert into flower(flower_name) values('dd');
--oracle所有表
JEMMES@JEMPDB> select * from tab;

--迁移前相关检查
col TABLE_NAME format a30
SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='JEMMES' ;
select object_type,count(*) from dba_objects where owner='JEMMES' group by object_type;
select object_type,status,count(*) from dba_objects where owner='JEMMES' group by object_type,status;
select sum(bytes)/1024/1024 from dba_segments where owner='JEMMES';

✨4.3 Migrate Oracle to MySQL

Copy multiple tables

##After completion, the following execution tree will be automatically generated:

Click run to start execution:

##✨4.4 MySQL data verification after migration

The above is the detailed content of How to migrate Docker containers from Oracle to MySQL. For more information, please follow other related articles on the PHP Chinese website!