문서번호 : 11-2098651
Document Information
•
최초 작성일 : 2024.01.23
•
최종 수정일 : 2024.01.23
•
이 문서는 아래 버전을 기준으로 작성되었습니다.
◦
SinglestoreDB : 8.5.1
◦
Java : JRE 11 / JDK 11
Goal
SingleStoreDB 에 내장된 CDC 기능인 CREATE TABLE ~ INFER PIPELINE 구문을 사용하여 MySQL 에서 SingleStoreDB 로 테이블을 replicate 하는 동작을 테스트한다.
Solution
1. 환경 세팅
MySQL 환경 세팅
•
MySQL 설치
$ sudo dnf install mysql-server -y
Bash
복사
•
엔진 변수 설정 및 기동
# binlog 설정
$ sudo vi /etc/my.cnf.d/mysql-server.cnf
log_bin=mysql-bin
binlog_format=ROW
binlog_row_image=FULL
$ sudo systemctl start mysqld
$ sudo systemctl status mysqld
Bash
복사
•
Port 오픈
# MySQL Port 오픈
$ sudo firewall-cmd --zone=public --permanent --add-port=3306/tcp
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --list-ports
Bash
복사
•
참고사항
◦
TimeZone을 SingleStore와 일치시킨다. (SYSTEM이 아닌 Asia/Seoul로 설정)
SingleStoreDB 환경 세팅
•
SingleStoreDB 설치 및 기동
# yum repository 등록
$ sudo yum-config-manager --add-repo https://release.memsql.com/production/rpm/x86_64/repodata/memsql.repo
$ sudo yum install -y singlestore-client singlestoredb-toolbox
$ vi /home/opc/s2_license.key
# 발급받은 라이센스 key 값을 저장
$ export s2_lic=`cat /home/opc/s2_license.key`
$ sdb-deploy cluster-in-a-box --license $s2_lic --version 8.5 --password sdbadmin123! --bind-address 0.0.0.0 -y
$ export s2_lic=''
Bash
복사
•
Java 11 이상 설치
$ sudo yum install jdk-11 -y
# 설치된 java 경로 확인
$ which java
/usr/bin/java
$ ls -n /usr/bin/java
lrwxrwxrwx. 1 0 0 22 Jan 22 02:26 /usr/bin/java -> /etc/alternatives/java
$ ls -n /etc/alternatives/java
lrwxrwxrwx. 1 0 0 30 Jan 22 02:26 /etc/alternatives/java -> /usr/java/jdk-11.0.17/bin/java
Bash
복사
•
엔진 변수 설정
-- 디스크 공간 절약을 위해 사이즈 축소 (생략 가능)
singlestore> set global log_file_size_partitions=67108864;
singlestore> set global snapshot_trigger_size=1073741824;
-- MySQL CDC를 위한 pipeline 사용 시 설정 필요 (필수)
-- 기존 설정값 확인 후 변경
singlestore> SELECT @@experimental_enable_mysql_migration; -- 'ON'
singlestore> SELECT @@java_pipelines_heap_size; -- '256'
singlestore> SELECT @@java_pipelines_java11_path ; -- 위에서 확인한 java 경로 설정
singlestore> set global experimental_enable_mysql_migration='ON';
singlestore> set global java_pipelines_heap_size=256;
singlestore> set global java_pipelines_java11_path ='/usr/java/jdk-11.0.17/bin/java';
SQL
복사
2. CDC 동작 테스트
MySQL User, Table 생성
-- SingleStoreDB link 에서 연결 시 사용할 User 생성 및 권한 부여
mysql> CREATE USER sdb IDENTIFIED BY 'Password1!';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT CREATE, INSERT, DELETE, SELECT ON singlestore.* TO sdb;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO sdb;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON *.* TO sdb;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE DATABASE test; USE test;
Query OK, 1 row affected (0.00 sec)
Database changed
mysql> CREATE TABLE `tb_test` (
-> `id` int NOT NULL,
-> `col1` varchar(20) DEFAULT NULL,
-> `col2` double DEFAULT NULL,
-> `col3` int DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ;
Query OK, 0 rows affected (0.02 sec)
SQL
복사
SingleStoreDB Link, Table 생성
singlestore> CREATE DATABASE myDB; USE myDB;
Query OK, 1 row affected (2.58 sec)
-- MySQL 에서 생성한 User로 연결
-- ssl 사용하지 않는 경우 "database.ssl.mode" 옵션은 생략 가능
singlestore> CREATE LINK mylink AS MYSQL
-> CONFIG '{"database.hostname": "[mysql_ip_addr]",
-> "database.port": 3306,
-> "database.exclude.list": "mysql, performance_schema, information_schema, sys",
-> "database.ssl.mode":"required"}'
-> CREDENTIALS '{"database.password": "Password1!", "database.user": "sdb"}';
Query OK, 1 row affected (0.01 sec)
-- link 사용하여 Table, Procedure, Pipeline 생성
singlestore> CREATE TABLES AS INFER PIPELINE AS LOAD DATA LINK mylink "*" FORMAT AVRO;
Query OK, 0 rows affected, 1 warning (7.18 sec)
singlestore> SHOW TABLES;
+----------------+
| Tables_in_myDB |
+----------------+
| tb_test |
+----------------+
1 row in set (0.01 sec)
-- 출력 결과에서 DDL 만 발췌
singlestore> SHOW CREATE TABLE tb_test;
CREATE TABLE `tb_test` (
`id` int(11) NOT NULL,
`col1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`col2` double DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
SHARD KEY `__SHARDKEY` (`id`),
SORT KEY `__UNORDERED` ()
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES,NO_AUTO_CREATE_USER'
singlestore> SHOW PIPELINES;
+-------------------+---------+-----------+
| Pipelines_in_myDB | State | Scheduled |
+-------------------+---------+-----------+
| tb_test | Stopped | False |
+-------------------+---------+-----------+
1 row in set (0.00 sec)
singlestore> SHOW PROCEDURES;
+-----------------------+------------------+---------+
| Procedures_in_myDB | Routine Lifetime | Definer |
+-----------------------+------------------+---------+
| tb_test_apply_changes | Permanent | root@% |
+-----------------------+------------------+---------+
1 row in set (0.00 sec)
-- Pipeline 실행하면 CDC 시작
singlestore> START PIPELINE tb_test;
Query OK, 0 rows affected (0.00 sec)
SQL
복사
MySQL에서 DML 수행 후 SingleStoreDB에서 확인
1.
Insert
MySQL
mysql> SELECT * FROM tb_test ORDER BY 1;
Empty set (0.00 sec)
mysql> INSERT INTO tb_test values
-> (1,'AAA',1.111,1),
-> (2,'BBB',2.222,2),
-> (3,'CCC',3.333,3),
-> (4,'DDD',4.444,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tb_test ORDER BY 1;
+----+------+-------+------+
| id | col1 | col2 | col3 |
+----+------+-------+------+
| 1 | AAA | 1.111 | 1 |
| 2 | BBB | 2.222 | 2 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+------+-------+------+
4 rows in set (0.00 sec)
SQL
복사
SingleStoreDB
singlestore> SELECT * FROM tb_test ORDER BY 1;
Empty set (0.04 sec)
singlestore> SELECT * FROM tb_test ORDER BY 1;
+----+------+-------+------+
| id | col1 | col2 | col3 |
+----+------+-------+------+
| 1 | AAA | 1.111 | 1 |
| 2 | BBB | 2.222 | 2 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+------+-------+------+
4 rows in set (0.00 sec)
SQL
복사
2.
Update
MySQL
mysql> SELECT * FROM tb_test ORDER BY 1;
+----+------+-------+------+
| id | col1 | col2 | col3 |
+----+------+-------+------+
| 1 | AAA | 1.111 | 1 |
| 2 | BBB | 2.222 | 2 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+------+-------+------+
4 rows in set (0.00 sec)
mysql> UPDATE tb_test SET col1=CONCAT('TEST',id), col2=col2*2, col3=col3+2 where id<3;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM tb_test ORDER BY 1;
+----+-------+-------+------+
| id | col1 | col2 | col3 |
+----+-------+-------+------+
| 1 | TEST1 | 2.222 | 3 |
| 2 | TEST2 | 4.444 | 4 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+-------+-------+------+
4 rows in set (0.00 sec)
SQL
복사
SingleStoreDB
singlestore> SELECT * FROM tb_test ORDER BY 1;
+----+------+-------+------+
| id | col1 | col2 | col3 |
+----+------+-------+------+
| 1 | AAA | 1.111 | 1 |
| 2 | BBB | 2.222 | 2 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+------+-------+------+
4 rows in set (0.00 sec)
singlestore> SELECT * FROM tb_test ORDER BY 1;
+----+-------+-------+------+
| id | col1 | col2 | col3 |
+----+-------+-------+------+
| 1 | TEST1 | 2.222 | 3 |
| 2 | TEST2 | 4.444 | 4 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+-------+-------+------+
4 rows in set (0.00 sec)
SQL
복사
3.
Delete
MySQL
mysql> SELECT * FROM tb_test ORDER BY 1;
+----+-------+-------+------+
| id | col1 | col2 | col3 |
+----+-------+-------+------+
| 1 | TEST1 | 2.222 | 3 |
| 2 | TEST2 | 4.444 | 4 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+-------+-------+------+
4 rows in set (0.00 sec)
mysql> DELETE FROM tb_test WHERE id%2=0;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM tb_test ORDER BY 1;
+----+-------+-------+------+
| id | col1 | col2 | col3 |
+----+-------+-------+------+
| 1 | TEST1 | 2.222 | 3 |
| 3 | CCC | 3.333 | 3 |
+----+-------+-------+------+
2 rows in set (0.00 sec)
SQL
복사
SingleStoreDB
singlestore> SELECT * FROM tb_test ORDER BY 1;
+----+-------+-------+------+
| id | col1 | col2 | col3 |
+----+-------+-------+------+
| 1 | TEST1 | 2.222 | 3 |
| 2 | TEST2 | 4.444 | 4 |
| 3 | CCC | 3.333 | 3 |
| 4 | DDD | 4.444 | 4 |
+----+-------+-------+------+
4 rows in set (0.00 sec)
singlestore> SELECT * FROM tb_test ORDER BY 1;
+----+-------+-------+------+
| id | col1 | col2 | col3 |
+----+-------+-------+------+
| 1 | TEST1 | 2.222 | 3 |
| 3 | CCC | 3.333 | 3 |
+----+-------+-------+------+
2 rows in set (0.00 sec)
SQL
복사
References
History
일자 | 작성자 | 비고 |
2024.01.23 | mk.Kang | 최초작성 |
2024.10.16 | osk | TimeZone 설정 추가 |
