Test Environment Configuration

Method

1. MariaDB Configuration

Granting Permissions to the Target User

Since MariaDB can grant permissions for remote and local access separately, grant permissions to allow remote access.

mairadb -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.4.32-MariaDB-1:10.4.32+maria~ubu2004 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mariadb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mariadb]> select user, host from user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| root        | %         |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1       |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
6 rows in set (0.001 sec)

-- '%' : Means remote access is allowed; specific IPs can be specified
MariaDB [mariadb]> create user test@'%' identified by 'test123#';
Query OK, 0 rows affected (0.008 sec)


-- * : Means granting privileges for all DB objects
MariaDB [mariadb]> grant all privileges on mariadb.* to test@'%';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mariadb]> Flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mariadb]> select user, host from user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| test        | %         |
| root        | %         |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1       |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
7 rows in set (0.001 sec)

2. Tibero Configuration

Creating the Target User and Table

First, check the MariaDB table to be migrated and create a table compatible with it.

[tibero@kimmi ~]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> create user mig_test identified by mig_test;

User 'MIG_TEST' created.

SQL> create table mig_test.test (a number);

Table 'MIG_TEST.TEST' created.

SQL> grant dba to mig_test;

Granted.

3. TableMigrator Configuration

Before using TableMigrator, you need to configure connection settings for the Source DB and Target DB. After extracting the TableMigrator archive, configure as shown below.

• TableMigrator file location: $TB_HOME/client/bin/table_migrator.zip
• TableMigrator configuration file sample: migrator.properties.eg

-- Check TableMigrator location

$ cd $TB_HOME/client/bin

$ ls -alrt table_migrator*
-rw-r--r--. 1 tibero dba   6373537 Oct 13 11:23 table_migrator.zip

-- Change execution location for TableMigrator
$ cp table_migrator.zip $HOME/table_migrator
$ cd $HOME/table_migrator
$ unzip table_migrator.zip

-- Copy configuration file sample
$ cp migrator.properties.eg mig.pro

3.1 Downloading and Configuring the MariaDB jar File

Download the jar file that matches your MariaDB version and upload it to the TableMigrator folder.

• Download: https://mariadb.com/docs/release-notes/connectors/java/all-releases

$ cd $HOME/table_migrator
$  ls -alrt mariadb*
-rw-r--r--. 1 tibero dba 760265 Nov 24 14:40 mariadb-java-client-3.5.6.jar


-- Enter the uploaded jar file name
$ vi migrator.sh
java -classpath migrator_cli.jar:mariadb-java-client-3.5.6.jar:ojdbc8.jar:osdt_core.jar:osdt_cert.jar:oraclepki.jar:postgresql-42.2.27.jre6.jar:msllogger-18.jar:toolcom.jar:internal-jdbc-8.jar:antlr-4.7.2-complete.jar com.m.migrator.Main "$@"

3.2 Source DB Configuration

$ vi mig.pro

#######################
# Source DB Connection
#######################

# Source DB type (Optional, generic | default | ORACLE)
# ORACLE option needs SOURCE_LOGIN_AS property
# GENERIC option needs SOURCE_DRIVER property
SOURCE_TYPE=GENERIC 

# Source DB JDBC driver class name
# Use with SOURCE_TYPE=GENERIC option
SOURCE_DRIVER=org.mariadb.jdbc.MariaDbDataSource

# Source DB JDBC connection URL (Optional, Default=jdbc:oracle:thin:@localhost:1521:orcl)
SOURCE_URL=jdbc:mariadb://192.168.41.182:3316/mariadb
# Source DB login ID (Optional, Default=sys)

SOURCE_USER=test

# Source DB login password (Optional, Default=oracle)
SOURCE_PASSWORD=test123#

# Oracle source DB login option (Optional, Default=sysdba)
# Use with SOURCE_TYPE=ORACLE option
SOURCE_LOGIN_AS=NORMAL

# Source table schema (Optional)
# Default value is the same with SOURCE_USER
#SOURCE_SCHEMA=mariadb

#SOURCE_SCHEMA="dbo"

#SOURCE_SCHEMA=

# Source table name (Mandatory)
#SOURCE_TABLE=tableName1
#SOURCE_TABLE="tableName2"
#SOURCE_TABLE=bt_scen_tran
#SOURCE_TABLE=t1

# Ignore deadlock error that occur during migration for the tables created using the dedup option
# Use when BATCH_THRESHOLD=1
# If not, Data integrity is not guaranteed
SOURCE_TABLE_DEDUPLICATE=N

3.3 Target DB Configuration

vi mig.pro

#######################
# Target DB Connection
#######################

# Target DB type (Optional, DEFAULT)
TARGET_TYPE=DEFAULT

# Target DB JDBC driver class name
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver

# Target DB JDBC connection URL (Optional, Default=jdbc:internal:thin:@localhost:8629:tibero)

TARGET_URL=jdbc:internal:thin:@192.168.41.110:8629:tibero

# Target DB login ID (Optional, Default=sys)
TARGET_USER=sys

# Target DB login password (Optional, Default=tibero)
TARGET_PASSWORD=tibero

# Target table schema (Optional)
# Default value is the same with TARGET_USER
TARGET_SCHEMA=mig_test

# Target table name (Optional)
# Default value is the same with SOURCE_TABLE
# TARGET_TABLE=t1

4. Running TableMigrator

When running TableMigrator, enter <table name>.<user name> to start the data migration.

-- Execution command
sh migrator.sh PROPERTY_FILE=mig.pro SOURCE_TABLE=mariadb.test TARGET_TABLE=mariadb.test

## [WARNING] Ignored parameters:
INSERT_THREAD_COUNT

=== Parameters ===
PROPERTY_FILE=mig.pro
SOURCE_TYPE=GENERIC
SOURCE_DRIVER=org.mariadb.jdbc.MariaDbDataSource
SOURCE_URL=jdbc:mariadb://192.168.41.182:3316/mariadb
SOURCE_USER=test
SOURCE_PASSWORD=************
SOURCE_SCHEMA=test
SOURCE_TABLE=mariadb.test
SELECT_SUBPARTITION=
BATCH_THRESHOLD=128
EXTRACT_PARALLEL=N
EXTRACT_THREAD_COUNT=1
SOURCE_TABLE_DEDUPLICATE=N
USE_PARALLEL_HINT=N
PARALLELTARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@192.168.41.110:8629:tibero
TARGET_USER=sys
TARGET_PASSWORD=************
TARGET_SCHEMA=mariadb
TARGET_TABLE=test
COMMIT_SIZE=-1
INDEX_DISABLE=N
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
DB_FILE_MULTIBLOCK_READ_COUNT=-1
INSERT_METHOD=dpl
INSERT_BATCH=Y
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_COLUMN_NAME_CASE_SENSITIVE=N
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
LOG_FILE_NAME=tableMigrator.log
LOG_LEVEL=INFO
CHECK_COLUMN_METADATA=N

==================
15:45:11.609 [main] INFO   -
********************************************************************************
*
* Start Log for table migrator.
*     Vendor:  tibero
*     Product: table migrator
*     Version: 303667
*
* Trace is started on 15:45:11.607
*     Log level: INFO
*
********************************************************************************

15:45:11.611 [main] INFO   - PROPERTY_FILE=mig.pro
SOURCE_TYPE=GENERIC
SOURCE_DRIVER=org.mariadb.jdbc.MariaDbDataSource
SOURCE_URL=jdbc:mariadb://192.168.41.182:3316/mariadb
SOURCE_USER=test
SOURCE_PASSWORD=************
SOURCE_SCHEMA=test
SOURCE_TABLE=mariadb.test
SELECT_SUBPARTITION=
BATCH_THRESHOLD=128
EXTRACT_PARALLEL=N
EXTRACT_THREAD_COUNT=1
SOURCE_TABLE_DEDUPLICATE=N
USE_PARALLEL_HINT=N
PARALLEL_HINT_CNT=-1
TARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@192.168.41.110:8629:tibero
TARGET_USER=sys
TARGET_PASSWORD=************
TARGET_SCHEMA=mig_test
TARGET_TABLE=test
COMMIT_SIZE=-1
INDEX_DISABLE=N
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
DB_FILE_MULTIBLOCK_READ_COUNT=-1
INSERT_METHOD=dpl
INSERT_BATCH=Y
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_COLUMN_NAME_CASE_SENSITIVE=N
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
LOG_FILE_NAME=tableMigrator.log
LOG_LEVEL=INFO
CHECK_COLUMN_METADATA=N

15:45:11.612 [main] INFO   - connect to the source - url : jdbc:mariadb://192.168.41.182:3316/mariadb id : test
15:45:11.690 [main] INFO   - connect to the target - url : jdbc:internal:thin:@192.168.41.110:8629:tibero id : sys
15777 [main] INFO   - connect to the target - url : jdbc:internal:thin:@192.168.41.110:8629:tibero id : sys

SOUCE_SCEMA : mariadb
SOUCE_TABLE : test
TARGET_SCEMA : mariadb
TARGET_TABLE : test

15:45:11.789 [main] INFO   - extract thread count : 1
15:45:11.789 [main] INFO   - start extracting thread...
15:45:11.790 [main] INFO   - start loading thread...
[E0]1763966711790 - STARTED
[E0]1763966711793 - TOTAL Extracted ROWS: 5 (source schema = mariadb, source table = test)
[L0]1763966711794 Loader started
15:45:11.794 [L0] INFO   - [L0]1763966711794 Loader started
15:45:11.795 [L0] INFO   - DPL Loading Information
15:45:11.795 [L0] INFO   - table name : mariadb.test
15:45:11.795 [L0] INFO   - columns [1] - "A"
[L0]1763966711819 TOTAL Loaded ROWS: 5 (target schema = mariadb, target table = test)
15:45:11.819 [L0] INFO   - [L0]1763966711819 TOTAL Loaded ROWS: 5 (target schema = mariadb, target table = test)
15:45:11.819 [L0] INFO   - Loader finally
15:45:11.819 [L0] INFO   - Loader complete
15:45:11.819 [main] INFO   - extract conneciton close
15:45:11.822 [main] INFO   - loading data commit and connection close
Loading is ended.
15:45:11.823 [main] INFO   - Loading is ended.
Elapsed Time (milliseconds) : 212
15:45:11.823 [main] INFO   - Elapsed Time (milliseconds) : 212
ADD BATCH : 0
15:45:11.823 [main] INFO   - ADD BATCH : 0
EXECUTE BATCH:45:11.823 [main] INFO   - EXECUTE BATCH : 0
[VERIFICATION] Source Info : mariadb.test : 5 Rows, Target Info : mariadb.test : 5 Rows [OK]

5. Data Verification

-- MariaDB

MariaDB [(none)]> select * from mariadb.test;
+------+
| col1 |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.000 sec)


-- Tibero 
SQL> select * from mariadb.test;

         A
----------
         1
         2
         3
         4
         5

5 rows selected.