Skip to content

[Percona Live 2021] Case study

Jump to bottom
DONG CHAN, SUNG edited this page May 12, 2021 · 16 revisions

Introduction

Here are some examples that can be done using Uldra replicator.

I have put some of this in the Percona Live 2021 presentation, and this is the script for the entire test.

Preparation

First, create an account and schema to be used in this test as follows.

DROP USER IF EXISTS 'repl'@'%';
DROP USER IF EXISTS 'target'@'%';
DROP USER IF EXISTS 'target01'@'%';
DROP USER IF EXISTS 'target02'@'%';

## ID: repl, PW: repl
CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%`;

## ID: target, PW: target
CREATE USER 'target'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E4F5876ABD8277202DB74BE1D22FCBBAB218967C';
GRANT ALL PRIVILEGES ON `target`.* TO `target`@`%`;

CREATE USER 'target01'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*4FE42250EFBD569111001D2A54D8DED5F2A4B683';
GRANT ALL PRIVILEGES ON `target01`.* TO `target01`@`%`;

CREATE USER 'target02'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*DC8BFD6BE5F7DF92F69B75ED7D43EECA0F7A12EC';
GRANT ALL PRIVILEGES ON `target02`.* TO `target02`@`%`;

Build uldra-replicator

git clone https://github.com/gywndi/uldra-replicator.git
cd uldra-replicator
mvn install
ls -al target/uldra-replicator-0.0.1.jar
-rw-r--r--  1 chan  staff  16795166  4 24 13:04 target/uldra-replicator-0.0.1.jar

Separate data

Separate data

Table schema

## origin schema
DROP DATABASE IF EXISTS origin;
CREATE DATABASE origin;
USE origin;
CREATE TABLE `s_user` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `reg_dttm` datetime DEFAULT NULL,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

## target schema
DROP DATABASE IF EXISTS target;
CREATE DATABASE target;
USE target;
CREATE TABLE `t_user_name` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `reg_dttm` datetime DEFAULT NULL,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

CREATE TABLE `t_user_contact` (
  `userid` varchar(10) NOT NULL,
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `reg_dttm` datetime DEFAULT NULL,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

reset master;

uldra-config.yml

workerCount: 16
wokerQueueSize: 5000

binlogServer: 127.0.0.1:3306
binlogServerID: 5
binlogServerUsername: repl
binlogServerPassword: repl
binlogInfoFile: "binlog.info"

binlogPolicies:
- name: origin.s_user
  groupKey: userid
  replicatPolicies:
  - name: "t_user_name"
    colums: ["userid", "name", "reg_dttm", "mod_tmsp"]
  - name: "t_user_contact"
    colums: ["userid", "email", "phone", "addr", "reg_dttm", "mod_tmsp"]
    
dataSources:
- !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://127.0.0.1:3306/target?autoReconnect=true&useSSL=false&sessionVariables=SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
  username: target
  password: target
  maxTotal: 30
  maxWaitMillis: 100
  validationQuery: SELECT 1
  testOnBorrow: false
  testOnReturn: false
  testWhileIdle: true
  timeBetweenEvictionRunsMillis: 60000
  minEvictableIdleTimeMillis : 1200000
  numTestsPerEvictionRun : 10

start uldra-replicator

rm -rf binlog.info
java -jar target/uldra-replicator-0.0.1.jar --config-file="uldra-config.yml"

Test query

##########################
## Insert source table
##########################
mysql> insert into origin.s_user values ('user01', 'user01-name', 'user01@email.com', '00000-01', 'street01', now(), now());
mysql> insert into origin.s_user values ('user02', 'user02-name', 'user02@email.com', '00000-02', 'street02', now(), now());
mysql> insert into origin.s_user values ('user03', 'user03-name', 'user03@email.com', '00000-03', 'street03', now(), now());
mysql> insert into origin.s_user values ('user04', 'user04-name', 'user04@email.com', '00000-04', 'street04', now(), now());
mysql> insert into origin.s_user values ('user05', 'user05-name', 'user05@email.com', '00000-05', 'street05', now(), now());
mysql> insert into origin.s_user values ('user06', 'user06-name', 'user06@email.com', '00000-06', 'street06', now(), now());
mysql> insert into origin.s_user values ('user07', 'user07-name', 'user07@email.com', '00000-07', 'street07', now(), now());
mysql> insert into origin.s_user values ('user08', 'user08-name', 'user08@email.com', '00000-08', 'street08', now(), now());
mysql> insert into origin.s_user values ('user09', 'user09-name', 'user09@email.com', '00000-09', 'street09', now(), now());

## Result
mysql> select * from target.t_user_name;
+--------+-------------+---------------------+---------------------+
| userid | name        | reg_dttm            | mod_tmsp            |
+--------+-------------+---------------------+---------------------+
| user01 | user01-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user02 | user02-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user03 | user03-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user04 | user04-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user05 | user05-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user06 | user06-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user07 | user07-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user08 | user08-name | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user09 | user09-name | 2021-04-24 16:15:02 | 2021-04-24 16:15:02 |
+--------+-------------+---------------------+---------------------+
9 rows in set (0.01 sec)

mysql> select * from target.t_user_contact;
+--------+------------------+----------+----------+---------------------+---------------------+
| userid | email            | phone    | addr     | reg_dttm            | mod_tmsp            |
+--------+------------------+----------+----------+---------------------+---------------------+
| user01 | user01@email.com | 00000-01 | street01 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user02 | user02@email.com | 00000-02 | street02 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user03 | user03@email.com | 00000-03 | street03 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user04 | user04@email.com | 00000-04 | street04 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user05 | user05@email.com | 00000-05 | street05 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user06 | user06@email.com | 00000-06 | street06 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user07 | user07@email.com | 00000-07 | street07 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user08 | user08@email.com | 00000-08 | street08 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user09 | user09@email.com | 00000-09 | street09 | 2021-04-24 16:15:02 | 2021-04-24 16:15:02 |
+--------+------------------+----------+----------+---------------------+---------------------+
9 rows in set (0.01 sec)

##########################
## Update source table
##########################
## Update single column
mysql> update origin.s_user set phone = '1111-01' limit 3;
Query OK, 3 rows affected (0.00 sec)

## Result
mysql> select * from target.t_user_contact;
+--------+------------------+----------+----------+---------------------+---------------------+
| userid | email            | phone    | addr     | reg_dttm            | mod_tmsp            |
+--------+------------------+----------+----------+---------------------+---------------------+
| user01 | user01@email.com | 1111-01  | street01 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user02 | user02@email.com | 1111-01  | street02 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user03 | user03@email.com | 1111-01  | street03 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user04 | user04@email.com | 00000-04 | street04 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user05 | user05@email.com | 00000-05 | street05 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user06 | user06@email.com | 00000-06 | street06 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user07 | user07@email.com | 00000-07 | street07 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user08 | user08@email.com | 00000-08 | street08 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user09 | user09@email.com | 00000-09 | street09 | 2021-04-24 16:15:02 | 2021-04-24 16:15:02 |
+--------+------------------+----------+----------+---------------------+---------------------+
9 rows in set (0.01 sec)

## Update multi-columns
mysql> update origin.s_user set name=concat(userid, '-newname'), phone = '2222-01' limit 3;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from target.t_user_name;
+--------+----------------+---------------------+---------------------+
| userid | name           | reg_dttm            | mod_tmsp            |
+--------+----------------+---------------------+---------------------+
| user01 | user01-newname | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user02 | user02-newname | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user03 | user03-newname | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user04 | user04-name    | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user05 | user05-name    | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user06 | user06-name    | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user07 | user07-name    | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user08 | user08-name    | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user09 | user09-name    | 2021-04-24 16:15:02 | 2021-04-24 16:15:02 |
+--------+----------------+---------------------+---------------------+
9 rows in set (0.01 sec)

## Result
mysql> select * from target.t_user_contact;
+--------+------------------+----------+----------+---------------------+---------------------+
| userid | email            | phone    | addr     | reg_dttm            | mod_tmsp            |
+--------+------------------+----------+----------+---------------------+---------------------+
| user01 | user01@email.com | 2222-01  | street01 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user02 | user02@email.com | 2222-01  | street02 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user03 | user03@email.com | 2222-01  | street03 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user04 | user04@email.com | 00000-04 | street04 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user05 | user05@email.com | 00000-05 | street05 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user06 | user06@email.com | 00000-06 | street06 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user07 | user07@email.com | 00000-07 | street07 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user08 | user08@email.com | 00000-08 | street08 | 2021-04-24 16:15:01 | 2021-04-24 16:15:01 |
| user09 | user09@email.com | 00000-09 | street09 | 2021-04-24 16:15:02 | 2021-04-24 16:15:02 |
+--------+------------------+----------+----------+---------------------+---------------------+
9 rows in set (0.01 sec)

##########################
## Delete source table
##########################

## Delete all
mysql> delete from origin.s_user;
Query OK, 9 rows affected (0.03 sec)

## Result
mysql> select * from target.t_user_name;
Empty set (0.01 sec)

mysql> select * from target.t_user_contact;
Empty set (0.00 sec)

Merge data

Merge data

Table schema

## origin schema
DROP DATABASE IF EXISTS origin;
CREATE DATABASE origin;
USE origin;
CREATE TABLE `s_user` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `reg_dttm` datetime DEFAULT NULL,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

CREATE TABLE `s_user_status` (
  `userid` varchar(10) NOT NULL,
  `vaild_yn` varchar(1) DEFAULT 'Y',
  `login_dttm` varchar(16) DEFAULT ' ',
  `login_cnt` int DEFAULT '1',
  `reg_dttm` datetime,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

## target schema
DROP DATABASE IF EXISTS target;
CREATE DATABASE target;
USE target;
CREATE TABLE `t_user_all_info` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `vaild_yn` varchar(1) DEFAULT 'Y',
  `login_dttm` varchar(16) DEFAULT ' ',
  `login_cnt` int DEFAULT '0',
  `reg_dttm` datetime,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

reset master;

uldra-config.yml

workerCount: 16
wokerQueueSize: 5000

binlogServer: 127.0.0.1:3306
binlogServerID: 5
binlogServerUsername: repl
binlogServerPassword: repl
binlogInfoFile: "binlog.info"

binlogPolicies:
- name: origin.s_user
  groupKey: userid
  replicatPolicies:
  - name: "t_user_all_info"
    upsertMode: true
    colums: ["userid", "name", "email", "phone", "addr", "reg_dttm", "mod_tmsp"]
- name: origin.s_user_status
  groupKey: userid
  replicatPolicies:
  - name: "t_user_all_info"
    upsertMode: true
    colums: ["userid", "vaild_yn", "login_dttm", "login_cnt", "mod_tmsp"]
    
dataSources:
- !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://127.0.0.1:3306/target?autoReconnect=true&useSSL=false&sessionVariables=SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
  username: target
  password: target
  maxTotal: 30
  maxWaitMillis: 100
  validationQuery: SELECT 1
  testOnBorrow: false
  testOnReturn: false
  testWhileIdle: true
  timeBetweenEvictionRunsMillis: 60000
  minEvictableIdleTimeMillis : 1200000
  numTestsPerEvictionRun : 10

start uldra-replicator

rm -rf binlog.info
java -jar target/uldra-replicator-0.0.1.jar --config-file="uldra-config.yml"

Test query

##############################
## Insert source table: s_user
##############################
mysql> insert into origin.s_user values ('user01', 'user01-name', 'user01@email.com', '00000-01', 'street01', now(), now());
mysql> insert into origin.s_user values ('user02', 'user02-name', 'user02@email.com', '00000-02', 'street02', now(), now());
mysql> insert into origin.s_user values ('user03', 'user03-name', 'user03@email.com', '00000-03', 'street03', now(), now());
mysql> insert into origin.s_user values ('user04', 'user04-name', 'user04@email.com', '00000-04', 'street04', now(), now());
mysql> insert into origin.s_user values ('user05', 'user05-name', 'user05@email.com', '00000-05', 'street05', now(), now());
mysql> insert into origin.s_user values ('user06', 'user06-name', 'user06@email.com', '00000-06', 'street06', now(), now());
mysql> insert into origin.s_user values ('user07', 'user07-name', 'user07@email.com', '00000-07', 'street07', now(), now());
mysql> insert into origin.s_user values ('user08', 'user08-name', 'user08@email.com', '00000-08', 'street08', now(), now());
mysql> insert into origin.s_user values ('user09', 'user09-name', 'user09@email.com', '00000-09', 'street09', now(), now());

## Result
mysql> select * from target.t_user_all_info;
+--------+-------------+------------------+----------+----------+----------+------------+-----------+---------------------+---------------------+
| userid | name        | email            | phone    | addr     | vaild_yn | login_dttm | login_cnt | reg_dttm            | mod_tmsp            |
+--------+-------------+------------------+----------+----------+----------+------------+-----------+---------------------+---------------------+
| user01 | user01-name | user01@email.com | 00000-01 | street01 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user02 | user02-name | user02@email.com | 00000-02 | street02 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user03 | user03-name | user03@email.com | 00000-03 | street03 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user04 | user04-name | user04@email.com | 00000-04 | street04 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user05 | user05-name | user05@email.com | 00000-05 | street05 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user06 | user06-name | user06@email.com | 00000-06 | street06 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user07 | user07-name | user07@email.com | 00000-07 | street07 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user08 | user08-name | user08@email.com | 00000-08 | street08 | Y        |            |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:45:23 |
| user09 | user09-name | user09@email.com | 00000-09 | street09 | Y        |            |         0 | 2021-04-24 18:45:24 | 2021-04-24 18:45:24 |
+--------+-------------+------------------+----------+----------+----------+------------+-----------+---------------------+---------------------+
9 rows in set (0.01 sec)

#####################################
## Insert source table: s_user_status
#####################################
mysql> insert into origin.s_user_status values ('user01', 'Y', '201901010714', '0', now(), now());
mysql> insert into origin.s_user_status values ('user02', 'Y', '201902120924', '0', now(), now());
mysql> insert into origin.s_user_status values ('user03', 'Y', '202003230134', '0', now(), now());
mysql> insert into origin.s_user_status values ('user04', 'Y', '202004040744', '0', now(), now());
mysql> insert into origin.s_user_status values ('user05', 'Y', '202005150854', '0', now(), now());
mysql> insert into origin.s_user_status values ('user06', 'Y', '202006261904', '0', now(), now());
mysql> insert into origin.s_user_status values ('user07', 'Y', '202007041014', '0', now(), now());
mysql> insert into origin.s_user_status values ('user08', 'Y', '202101151124', '0', now(), now());
mysql> insert into origin.s_user_status values ('user09', 'Y', '202102271234', '0', now(), now());

## Result
mysql> select * from target.t_user_all_info;
+--------+-------------+------------------+----------+----------+----------+--------------+-----------+---------------------+---------------------+
| userid | name        | email            | phone    | addr     | vaild_yn | login_dttm   | login_cnt | reg_dttm            | mod_tmsp            |
+--------+-------------+------------------+----------+----------+----------+--------------+-----------+---------------------+---------------------+
| user01 | user01-name | user01@email.com | 00000-01 | street01 | Y        | 201901010714 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user02 | user02-name | user02@email.com | 00000-02 | street02 | Y        | 201902120924 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user03 | user03-name | user03@email.com | 00000-03 | street03 | Y        | 202003230134 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user04 | user04-name | user04@email.com | 00000-04 | street04 | Y        | 202004040744 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user05 | user05-name | user05@email.com | 00000-05 | street05 | Y        | 202005150854 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user06 | user06-name | user06@email.com | 00000-06 | street06 | Y        | 202006261904 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user07 | user07-name | user07@email.com | 00000-07 | street07 | Y        | 202007041014 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user08 | user08-name | user08@email.com | 00000-08 | street08 | Y        | 202101151124 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user09 | user09-name | user09@email.com | 00000-09 | street09 | Y        | 202102271234 |         0 | 2021-04-24 18:45:24 | 2021-04-24 18:46:12 |
+--------+-------------+------------------+----------+----------+----------+--------------+-----------+---------------------+---------------------+
9 rows in set (0.00 sec)

#####################################
## Update source table: s_user_status
#####################################
mysql> update origin.s_user_status set login_cnt = login_cnt + 1 order by rand() limit 3;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

## Result
mysql> select * from target.t_user_all_info;
+--------+-------------+------------------+----------+----------+----------+--------------+-----------+---------------------+---------------------+
| userid | name        | email            | phone    | addr     | vaild_yn | login_dttm   | login_cnt | reg_dttm            | mod_tmsp            |
+--------+-------------+------------------+----------+----------+----------+--------------+-----------+---------------------+---------------------+
| user01 | user01-name | user01@email.com | 00000-01 | street01 | Y        | 201901010714 |         1 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user02 | user02-name | user02@email.com | 00000-02 | street02 | Y        | 201902120924 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user03 | user03-name | user03@email.com | 00000-03 | street03 | Y        | 202003230134 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user04 | user04-name | user04@email.com | 00000-04 | street04 | Y        | 202004040744 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user05 | user05-name | user05@email.com | 00000-05 | street05 | Y        | 202005150854 |         1 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user06 | user06-name | user06@email.com | 00000-06 | street06 | Y        | 202006261904 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user07 | user07-name | user07@email.com | 00000-07 | street07 | Y        | 202007041014 |         1 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user08 | user08-name | user08@email.com | 00000-08 | street08 | Y        | 202101151124 |         0 | 2021-04-24 18:45:23 | 2021-04-24 18:46:12 |
| user09 | user09-name | user09@email.com | 00000-09 | street09 | Y        | 202102271234 |         0 | 2021-04-24 18:45:24 | 2021-04-24 18:46:12 |
+--------+-------------+------------------+----------+----------+----------+--------------+-----------+---------------------+---------------------+
9 rows in set (0.01 sec)

#####################################
## Delete source tables
#####################################

## Delete s_user
mysql> delete from origin.s_user;
Query OK, 9 rows affected (0.00 sec)

## Result
mysql> select * from target.t_user_all_info;
+--------+------+-------+-------+------+----------+--------------+-----------+----------+----------+
| userid | name | email | phone | addr | vaild_yn | login_dttm   | login_cnt | reg_dttm | mod_tmsp |
+--------+------+-------+-------+------+----------+--------------+-----------+----------+----------+
| user01 |      |       |       |      | Y        | 201901010714 |         1 | NULL     | NULL     |
| user02 |      |       |       |      | Y        | 201902120924 |         0 | NULL     | NULL     |
| user03 |      |       |       |      | Y        | 202003230134 |         0 | NULL     | NULL     |
| user04 |      |       |       |      | Y        | 202004040744 |         0 | NULL     | NULL     |
| user05 |      |       |       |      | Y        | 202005150854 |         1 | NULL     | NULL     |
| user06 |      |       |       |      | Y        | 202006261904 |         0 | NULL     | NULL     |
| user07 |      |       |       |      | Y        | 202007041014 |         0 | NULL     | NULL     |
| user08 |      |       |       |      | Y        | 202101151124 |         1 | NULL     | NULL     |
| user09 |      |       |       |      | Y        | 202102271234 |         0 | NULL     | NULL     |
+--------+------+-------+-------+------+----------+--------------+-----------+----------+----------+
9 rows in set (0.01 sec)


## Delete s_user_status
mysql> delete from  origin.s_user_status;
Query OK, 9 rows affected (0.00 sec)

## Result
mysql> select * from target.t_user_all_info;
+--------+------+-------+-------+------+----------+------------+-----------+----------+----------+
| userid | name | email | phone | addr | vaild_yn | login_dttm | login_cnt | reg_dttm | mod_tmsp |
+--------+------+-------+-------+------+----------+------------+-----------+----------+----------+
| user01 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user02 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user03 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user04 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user05 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user06 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user07 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user08 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
| user09 |      |       |       |      | Y        |            |         0 | NULL     | NULL     |
+--------+------+-------+-------+------+----------+------------+-----------+----------+----------+
9 rows in set (0.00 sec)

Transfer to Shard

Transfer to shard

Table schema

## origin schema
DROP DATABASE IF EXISTS origin;
CREATE DATABASE origin;
USE origin;
CREATE TABLE `s_user` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `reg_dttm` datetime DEFAULT NULL,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

## target01 schema
DROP DATABASE IF EXISTS target01;
CREATE DATABASE target01;
USE target01;
CREATE TABLE `t_user` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `reg_dttm` datetime,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
);

CREATE TABLE `t_user_name` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `reg_dttm` datetime,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
);

## target02 schema
DROP DATABASE IF EXISTS target02;
CREATE DATABASE target02;
USE target02;
CREATE TABLE `t_user` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `reg_dttm` datetime,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
);

CREATE TABLE `t_user_name` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `reg_dttm` datetime,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
);

reset master;

uldra-config.yml

workerCount: 16
wokerQueueSize: 5000

binlogServer: 127.0.0.1:3306
binlogServerID: 5
binlogServerUsername: repl
binlogServerPassword: repl
binlogInfoFile: "binlog.info"

binlogPolicies:
- name: origin.s_user
  groupKey: userid
  replicatPolicies:
  - name: "t_user"
  - name: "t_user_name"
    upsertMode: true
    colums: ["userid", "name", "reg_dttm", "mod_tmsp"]

dataSources:
- !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://127.0.0.1:3306/target01?autoReconnect=true&useSSL=false&sessionVariables=SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
  username: target01
  password: target01
  maxTotal: 30
  maxWaitMillis: 100
  validationQuery: SELECT 1
  testOnBorrow: false
  testOnReturn: false
  testWhileIdle: true
  timeBetweenEvictionRunsMillis: 60000
  minEvictableIdleTimeMillis : 1200000
  numTestsPerEvictionRun : 10
- !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://127.0.0.1:3306/target02?autoReconnect=true&useSSL=false&sessionVariables=SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
  username: target02
  password: target02
  maxTotal: 30
  maxWaitMillis: 100
  validationQuery: SELECT 1
  testOnBorrow: false
  testOnReturn: false
  testWhileIdle: true
  timeBetweenEvictionRunsMillis: 60000
  minEvictableIdleTimeMillis : 1200000
  numTestsPerEvictionRun : 10

start uldra-replicator

rm -rf binlog.info
java -jar target/uldra-replicator-0.0.1.jar --config-file="uldra-config.yml"

Test query

##############################
## Insert source table: s_user
##############################
mysql> insert into origin.s_user values ('user01', 'user01-name', 'user01@email.com', '00000-01', 'street01', now(), now());
mysql> insert into origin.s_user values ('user02', 'user02-name', 'user02@email.com', '00000-02', 'street02', now(), now());
mysql> insert into origin.s_user values ('user03', 'user03-name', 'user03@email.com', '00000-03', 'street03', now(), now());
mysql> insert into origin.s_user values ('user04', 'user04-name', 'user04@email.com', '00000-04', 'street04', now(), now());
mysql> insert into origin.s_user values ('user05', 'user05-name', 'user05@email.com', '00000-05', 'street05', now(), now());
mysql> insert into origin.s_user values ('user06', 'user06-name', 'user06@email.com', '00000-06', 'street06', now(), now());
mysql> insert into origin.s_user values ('user07', 'user07-name', 'user07@email.com', '00000-07', 'street07', now(), now());
mysql> insert into origin.s_user values ('user08', 'user08-name', 'user08@email.com', '00000-08', 'street08', now(), now());
mysql> insert into origin.s_user values ('user09', 'user09-name', 'user09@email.com', '00000-09', 'street09', now(), now());

## Result
mysql> select * from target01.t_user;
+--------+-------------+------------------+----------+----------+---------------------+---------------------+
| userid | name        | email            | phone    | addr     | reg_dttm            | mod_tmsp            |
+--------+-------------+------------------+----------+----------+---------------------+---------------------+
| user01 | user01-name | user01@email.com | 00000-01 | street01 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user02 | user02-name | user02@email.com | 00000-02 | street02 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user03 | user03-name | user03@email.com | 00000-03 | street03 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user08 | user08-name | user08@email.com | 00000-08 | street08 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user09 | user09-name | user09@email.com | 00000-09 | street09 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+-------------+------------------+----------+----------+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from target01.t_user_name;
+--------+-------------+---------------------+---------------------+
| userid | name        | reg_dttm            | mod_tmsp            |
+--------+-------------+---------------------+---------------------+
| user01 | user01-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user02 | user02-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user03 | user03-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user08 | user08-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user09 | user09-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+-------------+---------------------+---------------------+
5 rows in set (0.01 sec)

mysql> select * from target02.t_user;
+--------+-------------+------------------+----------+----------+---------------------+---------------------+
| userid | name        | email            | phone    | addr     | reg_dttm            | mod_tmsp            |
+--------+-------------+------------------+----------+----------+---------------------+---------------------+
| user04 | user04-name | user04@email.com | 00000-04 | street04 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user05 | user05-name | user05@email.com | 00000-05 | street05 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user06 | user06-name | user06@email.com | 00000-06 | street06 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user07 | user07-name | user07@email.com | 00000-07 | street07 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+-------------+------------------+----------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from target02.t_user_name;
+--------+-------------+---------------------+---------------------+
| userid | name        | reg_dttm            | mod_tmsp            |
+--------+-------------+---------------------+---------------------+
| user04 | user04-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user05 | user05-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user06 | user06-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user07 | user07-name | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+-------------+---------------------+---------------------+
4 rows in set (0.01 sec)

##########################
## Update source table
##########################
mysql> update origin.s_user set name = concat(userid, '-newname') order by rand() limit 3;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

## Result
mysql> select * from target01.t_user;
+--------+----------------+------------------+----------+----------+---------------------+---------------------+
| userid | name           | email            | phone    | addr     | reg_dttm            | mod_tmsp            |
+--------+----------------+------------------+----------+----------+---------------------+---------------------+
| user01 | user01-name    | user01@email.com | 00000-01 | street01 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user02 | user02-newname | user02@email.com | 00000-02 | street02 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user03 | user03-name    | user03@email.com | 00000-03 | street03 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user08 | user08-name    | user08@email.com | 00000-08 | street08 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user09 | user09-name    | user09@email.com | 00000-09 | street09 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+----------------+------------------+----------+----------+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from target01.t_user_name;
+--------+----------------+---------------------+---------------------+
| userid | name           | reg_dttm            | mod_tmsp            |
+--------+----------------+---------------------+---------------------+
| user01 | user01-name    | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user02 | user02-newname | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user03 | user03-name    | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user08 | user08-name    | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user09 | user09-name    | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+----------------+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from target02.t_user;
+--------+----------------+------------------+----------+----------+---------------------+---------------------+
| userid | name           | email            | phone    | addr     | reg_dttm            | mod_tmsp            |
+--------+----------------+------------------+----------+----------+---------------------+---------------------+
| user04 | user04-name    | user04@email.com | 00000-04 | street04 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user05 | user05-newname | user05@email.com | 00000-05 | street05 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user06 | user06-newname | user06@email.com | 00000-06 | street06 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user07 | user07-name    | user07@email.com | 00000-07 | street07 | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+----------------+------------------+----------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from target02.t_user_name;
+--------+----------------+---------------------+---------------------+
| userid | name           | reg_dttm            | mod_tmsp            |
+--------+----------------+---------------------+---------------------+
| user04 | user04-name    | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user05 | user05-newname | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user06 | user06-newname | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
| user07 | user07-name    | 2021-04-24 20:47:08 | 2021-04-24 20:47:08 |
+--------+----------------+---------------------+---------------------+
4 rows in set (0.00 sec)

##########################
## Delete source table
##########################
mysql> delete from origin.s_user ;
Query OK, 9 rows affected (0.01 sec)

## Result
mysql> select * from target01.t_user;
Empty set (0.00 sec)

mysql> select * from target02.t_user;
Empty set (0.00 sec)

mysql> select * from target01.t_user_name;
Empty set (0.01 sec)

mysql> select * from target02.t_user_name;
Empty set (0.00 sec)

Change content

Change content

Table schema

## origin schema
DROP DATABASE IF EXISTS origin;
CREATE DATABASE origin;
USE origin;
CREATE TABLE `s_user` (
  `userid` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT ' ',
  `email` varchar(50) DEFAULT ' ',
  `phone` varchar(20) DEFAULT ' ',
  `addr` varchar(100) DEFAULT ' ',
  `reg_dttm` datetime DEFAULT NULL,
  `mod_tmsp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB;

## target schema
DROP DATABASE IF EXISTS target;
CREATE DATABASE target;
USE target;
CREATE TABLE `t_user_newname` (
`userid` varchar(10) NOT NULL,
`newname` varchar(20) DEFAULT ' ',
`reg_dttm` datetime,
`mod_tmsp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`userid`)
);
reset master;

RowHandlerCustom.java

package net.gywn.binlog.custom;

import net.gywn.binlog.handler.RowHandler;
import net.gywn.binlog.beans.BinlogOperation;

public class RowHandlerCustom implements RowHandler {

	@Override
	public void init() {
		// TODO Auto-generated method stub
	}

	@Override
	public void modify(final BinlogOperation operation) {
		String nameOrigin = operation.getDatMap().get("name");
		String addrOrigin = operation.getDatMap().get("addr");
		String newname = String.format("%s:%s", nameOrigin, addrOrigin);
		operation.getDatMap().put("newname", newname);
	}
}

uldra-config.yml

workerCount: 16
wokerQueueSize: 5000

binlogServer: 127.0.0.1:3306
binlogServerID: 5
binlogServerUsername: repl
binlogServerPassword: repl
binlogInfoFile: "binlog.info"

binlogPolicies:
- name: origin.s_user
  groupKey: userid
  rowHandlerClassName: "net.gywn.binlog.custom.RowHandlerCustom"
  rowHandlerParams:
    param1: "a"
    param2: "b"
  replicatPolicies:
  - name: "t_user_newname"
    colums: ["userid", "newname", "reg_dttm", "mod_tmsp"]
    
dataSources:
- !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://127.0.0.1:3306/target?autoReconnect=true&useSSL=false&sessionVariables=SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
  username: target
  password: target
  maxTotal: 30
  maxWaitMillis: 100
  validationQuery: SELECT 1
  testOnBorrow: false
  testOnReturn: false
  testWhileIdle: true
  timeBetweenEvictionRunsMillis: 60000
  minEvictableIdleTimeMillis : 1200000
  numTestsPerEvictionRun : 10

start uldra-replicator

mvn install
rm -rf binlog.info
java -jar target/uldra-replicator-0.0.1.jar --config-file="uldra-config.yml"

Test query

##############################
## Insert source table: s_user
##############################
mysql> insert into origin.s_user values ('user01', 'user01-name', 'user01@email.com', '00000-01', 'street01', now(), now());
mysql> insert into origin.s_user values ('user02', 'user02-name', 'user02@email.com', '00000-02', 'street02', now(), now());
mysql> insert into origin.s_user values ('user03', 'user03-name', 'user03@email.com', '00000-03', 'street03', now(), now());
mysql> insert into origin.s_user values ('user04', 'user04-name', 'user04@email.com', '00000-04', 'street04', now(), now());
mysql> insert into origin.s_user values ('user05', 'user05-name', 'user05@email.com', '00000-05', 'street05', now(), now());
mysql> insert into origin.s_user values ('user06', 'user06-name', 'user06@email.com', '00000-06', 'street06', now(), now());
mysql> insert into origin.s_user values ('user07', 'user07-name', 'user07@email.com', '00000-07', 'street07', now(), now());
mysql> insert into origin.s_user values ('user08', 'user08-name', 'user08@email.com', '00000-08', 'street08', now(), now());
mysql> insert into origin.s_user values ('user09', 'user09-name', 'user09@email.com', '00000-09', 'street09', now(), now());

## Result
mysql> select * from target.t_user_newname;
+--------+----------------------+---------------------+---------------------+
| userid | newname              | reg_dttm            | mod_tmsp            |
+--------+----------------------+---------------------+---------------------+
| user01 | user01-name:street01 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user02 | user02-name:street02 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user03 | user03-name:street03 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user04 | user04-name:street04 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user05 | user05-name:street05 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user06 | user06-name:street06 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user07 | user07-name:street07 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user08 | user08-name:street08 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user09 | user09-name:street09 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
+--------+----------------------+---------------------+---------------------+
9 rows in set (0.03 sec)

##########################
## Update source table
##########################
mysql> update origin.s_user set name = concat(userid, '-new') order by rand() limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

## Result
mysql> select * from target.t_user_newname;
+--------+----------------------+---------------------+---------------------+
| userid | newname              | reg_dttm            | mod_tmsp            |
+--------+----------------------+---------------------+---------------------+
| user01 | user01-name:street01 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user02 | user02-name:street02 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user03 | user03-name:street03 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user04 | user04-new:street04  | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user05 | user05-new:street05  | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user06 | user06-name:street06 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user07 | user07-name:street07 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user08 | user08-new:street08  | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
| user09 | user09-name:street09 | 2021-04-24 21:03:24 | 2021-04-24 21:03:24 |
+--------+----------------------+---------------------+---------------------+
9 rows in set (0.00 sec)

##########################
## Delete source table
##########################
mysql> delete from origin.s_user ;
Query OK, 9 rows affected (0.01 sec)

## Result
mysql> select * from target.t_user_newname;
Empty set (0.00 sec)

Clone this wiki locally