문서유형ㅣ기술정보
분야ㅣ 마이그레이션
적용제품버전ㅣ7FS04PS
문서번호ㅣTMITI027
개요
MariaDB를 TableMigrator를 이용하여 이관하는 방법에 대해 기술하였습니다.
테스트 구성 환경
| 서버명 | OS version | DB Version | DB Name | Port | Username | IP |
|---|---|---|---|---|---|---|
| Tibero | CentOS 8.4.2105 | Tibero 7.2.4 | tibero | 8629 | sys | 192.168.41.110 |
| MariaDB | Ubuntu 9.4.0 | MariaDB 10.4.32 | mariadb | 3316 | test | 192.168.41.182 |
방법
1. MariaDB 설정
이관 대상 유저 권한 부여
MariaDB 원격 접속 및 로컬 접속을 구분하여 권한 부여가 가능하므로, 원격 접속이 가능하도록 권한을 부여합니다.
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) -- '%' : 원격 접속을 허용하겠다는 의미로, 특정 IP 명시 가능 MariaDB [mariadb]> create user test@'%' identified by 'test123#'; Query OK, 0 rows affected (0.008 sec) -- * : DB 전체 오브젝트에 대한 권한을 부여한다는 의미 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 설정
이관 대상 유저 및 테이블 생성
먼저 MariaDB 이관 대상 테이블을 확인하고 해당 테이블과 호환성이 유지되도록 테이블을 생성합니다.
[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 설정
TableMigrator 사용 전 Source DB, Target DB에 대한 접속 설정을 해주어야 합니다. TableMigrator 압축 해제 후, 설정 아래와 같이 설정합니다.
- TableMigrator 파일 위치 : $TB_HOME/client/bin/table_migrator.zip
- TableMigrator 설정 파일 샘플 : migrator.properties.eg
-- TableMigrator 위치 확인 $ cd $TB_HOME/client/bin $ ls -alrt table_migrator* -rw-r--r--. 1 tibero dba 6373537 10월 13 11:23 table_migrator.zip -- TableMigrator 수행 위치 변경 $ cp table_migrator.zip $HOME/table_migrator $ cd $HOME/table_migrator $ unzip table_migrator.zip -- 설정 파일 샘플 복사 $ cp migrator.properties.eg mig.pro
3.1 MariaDB jar 파일 다운로드 및 설정
MariaDB 버전에 맞는 jar 파일 다운로드 후 TableMigrator 폴더에 업로드 합니다.
$ cd $HOME/table_migrator $ ls -alrt mariadb* -rw-r--r--. 1 tibero dba 760265 11월 24 14:40 mariadb-java-client-3.5.6.jar -- 업로드한 jar 파일명 입력 $ 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 설정
| 속성명 | 설정값 | 비고 |
|---|---|---|
| SOURCE_TYPE | GENERIC | 고정값 |
| SOURCE_DRIVER | org.mariadb.jdbc.MariaDbDataSource | 고정값 |
| SOURCE_URL | jdbc:mariadb://192.168.41.182:3316/mariadb | IP, Service Port, DB명은 환경에 맞게 변경 |
| SOURCE_USER | test | 접속할 유저명 |
| SOURCE_PASSWORD | test123# | 접속할 유저의 패스워드 |
$ 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 설정
| 속성명 | 설정값 | 비고 |
|---|---|---|
| TARGET_TYPE | DEFAULT | 고정값 |
| TARGET_DRIVER | com.m.internal.jdbc.MInternalDriver | 고정값 |
| TARGET_URL | jdbc:internal:thin:@192.168.41.110:8629:tibero | IP, Service Port, DB명은 환경에 맞게 변경 |
| TARGET_USER | sys | 접속할 유저명 |
| TARGET_PASSWORD | tibero | 접속할 유저의 패스워드 |
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. TableMigrator 수행
TableMigrator 수행 시 이관할 <테이블명>.<유저명> 입력하여 데이터 이관을 시작합니다.
-- 수행 명령어 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 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=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 15:45:11.777 [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 : 0 15:45:11.823 [main] INFO - EXECUTE BATCH : 0 [VERIFICATION] Source Info : mariadb.test : 5 Rows, Target Info : mariadb.test : 5 Rows [OK]
5. 데이터 확인
-- 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.