PostgreSQL ãš MySQL éã®ã¯ãã¹ ã¬ããªã±ãŒã·ã§ã³ãšãXNUMX ã€ã®ããŒã¿ããŒã¹ ãµãŒããŒéã®ã¯ãã¹ ã¬ããªã±ãŒã·ã§ã³ãèšå®ããæ¹æ³ã«ã€ããŠæŠèª¬ããŸãã éåžžãçžäºã¬ããªã±ãŒããããããŒã¿ããŒã¹ã¯åçš®ããŒã¿ããŒã¹ãšåŒã°ãããã RDBMS ãµãŒããŒããå¥ã® RDBMS ãµãŒããŒã«ç§»åãã䟿å©ãªæ¹æ³ã§ãã
PostgreSQL ããŒã¿ããŒã¹ãš MySQL ããŒã¿ããŒã¹ã¯äžè¬ã«ãªã¬ãŒã·ã§ãã«ãšã¿ãªãããŸãããè¿œå ã®æ¡åŒµæ©èœã䜿çšãããš NoSQL æ©èœãæäŸããŸãã ããã§ã¯ããªã¬ãŒã·ã§ãã« DBMS ã®èŠ³ç¹ãã PostgreSQL ãš MySQL ã®éã®ã¬ããªã±ãŒã·ã§ã³ã«ã€ããŠèª¬æããŸãã
ããã§ã¯å éšã®ä»çµã¿å šäœã«ã€ããŠã¯èª¬æããŸãããããŒã¿ããŒã¹ ãµãŒããŒéã®ã¬ããªã±ãŒã·ã§ã³ã®æ§æãå©ç¹ãå¶éã䜿çšäŸãç解ã§ããããã«ãåºæ¬ååã ãã説æããŸãã
éåžžãXNUMX ã€ã®åäžã®ããŒã¿ããŒã¹ ãµãŒããŒéã®ã¬ããªã±ãŒã·ã§ã³ã¯ããã€ã㪠ã¢ãŒãã§å®è¡ããããããã¹ã¿ãŒ (ãããªãã·ã£ããã¹ã¿ãŒããŸãã¯ã¢ã¯ãã£ããšãåŒã°ããŸã) ãšã¹ã¬ãŒã (ãµãã¹ã¯ã©ã€ããã¹ã¿ã³ãã€ããŸãã¯ããã·ã) ã®éã®ã¯ãšãªã䜿çšããŠå®è¡ãããŸãã ã¬ããªã±ãŒã·ã§ã³ã®ç®çã¯ããã¹ã¿ãŒ ããŒã¿ããŒã¹ã®ãªã¢ã«ã¿ã€ã ã³ããŒãã¹ã¬ãŒãåŽã«æäŸããããšã§ãã ãã®å Žåãã¬ããªã±ãŒã·ã§ã³ã¯äžæ¹åã§ã®ã¿å®è¡ããããããããŒã¿ã¯ãã¹ã¿ãŒããã¹ã¬ãŒããã€ãŸãã¢ã¯ãã£ãããããã·ãã«è»¢éãããŸãã ãã ããXNUMX ã€ã®ããŒã¿ããŒã¹éã®ã¬ããªã±ãŒã·ã§ã³ãäž¡æ¹åã«èšå®ããŠãããŒã¿ãã¢ã¯ãã£ã/ã¢ã¯ãã£ãæ§æã§ã¹ã¬ãŒããããã¹ã¿ãŒã«è»¢éãããããã«ããããšãã§ããŸãã ã«ã¹ã±ãŒã ã¬ããªã±ãŒã·ã§ã³ãå«ãããããã¹ãŠã¯ãXNUMX ã€ä»¥äžã®åäžã®ããŒã¿ããŒã¹ ãµãŒããŒéã§å¯èœã§ããã¢ã¯ãã£ã/ã¢ã¯ãã£ããŸãã¯ã¢ã¯ãã£ã/ããã·ãæ§æã¯ãããŒãºãåææ§æã§ã®ãã®ãããªæ©èœã®å¯çšæ§ããŸãã¯å€éšæ§æãœãªã¥ãŒã·ã§ã³ã®äœ¿çšãããã³æ¢åã®ãã¬ãŒããªãã«ãã£ãŠç°ãªããŸãã
説æããæ§æã¯ãç°ãªãããŒã¿ããŒã¹ ãµãŒããŒéã§ãå¯èœã§ãã ãµãŒããŒã¯ãå¥ã®ããŒã¿ããŒã¹ ãµãŒããŒããè€è£œãããããŒã¿ãåãå ¥ããè€è£œãããããŒã¿ã®ãªã¢ã«ã¿ã€ã ã¹ãããã·ã§ãããç¶æããããã«æ§æã§ããŸãã MySQL ãš PostgreSQL ã¯ããã€ã㪠ãã° ã¡ãœããããã£ã¹ã¯ ããã¯ãã¹ããŒãã¡ã³ãããŒã¹ããã³è¡ããŒã¹ã®ã¡ãœãããªã©ããããã®æ§æã®ã»ãšãã©ã瀟å ãŸãã¯ãµãŒãããŒãã£ã®æ¡åŒµæ©èœãéããŠæäŸããŸãã
MySQL ãš PostgreSQL éã®ã¯ãã¹ ã¬ããªã±ãŒã·ã§ã³ã¯ãããããŒã¿ããŒã¹ ãµãŒããŒããå¥ã®ããŒã¿ããŒã¹ ãµãŒããŒãžã® XNUMX åéãã®ç§»è¡ã«å¿ èŠã§ãã ãããã®ããŒã¿ããŒã¹ã¯ç°ãªããããã³ã«ã䜿çšããŠãããããçŽæ¥ãªã³ã¯ããããšã¯ã§ããŸããã ããŒã¿äº€æã確ç«ããã«ã¯ãpg_chameleon ãªã©ã®å€éšãªãŒãã³ ãœãŒã¹ ããŒã«ã䜿çšã§ããŸãã
pg_ã«ã¡ã¬ãªã³ãšã¯
pg_chameleon ã¯ãPython 3 ã§ã® MySQL ãã PostgreSQL ãžã®ã¬ããªã±ãŒã·ã§ã³ ã·ã¹ãã ã§ããããã Python ã®ãªãŒãã³ãœãŒã¹ mysql-replication ã©ã€ãã©ãªã䜿çšããŸãã è¡ã€ã¡ãŒãžã¯ MySQL ããŒãã«ããæœåºãããJSONB ãªããžã§ã¯ããšã㊠PostgreSQL ããŒã¿ããŒã¹ã«ä¿åãããpl/pgsql é¢æ°ã«ãã£ãŠåŸ©å·åãã㊠PostgreSQL ããŒã¿ããŒã¹ã«åçŸãããŸãã
pg_chameleonã®ç¹åŸŽ
åãã¯ã©ã¹ã¿ãŒã®è€æ°ã® MySQL ã¹ããŒãããXNUMX 察å€ã®æ§æã§åäžã®ã¿ãŒã²ãã PostgreSQL ããŒã¿ããŒã¹ã«ã¬ããªã±ãŒãã§ããŸã
ãœãŒã¹ãšã¿ãŒã²ããã®ã¹ããŒãåãåãã«ããããšã¯ã§ããŸããã
ã¬ããªã±ãŒã·ã§ã³ ããŒã¿ã¯ãã«ã¹ã±ãŒãããã MySQL ã¬ããªã«ããååŸã§ããŸãã
è€è£œã§ããªãããŒãã«ããšã©ãŒãçºçããããŒãã«ã¯é€å€ãããŸãã
åã¬ããªã±ãŒã·ã§ã³æ©èœã¯ããŒã¢ã³ã«ãã£ãŠå¶åŸ¡ãããŸãã
YAML ããŒã¹ã®ãã©ã¡ãŒã¿ãŒãšæ§æãã¡ã€ã«ãä»ããŠå¶åŸ¡ããŸãã
äŸ
ãã¹ã
vm1
vm2
OSããŒãžã§ã³
CentOS Linux 7.6 x86_64
CentOS Linux 7.5 x86_64
DBãµãŒããŒã®ããŒãžã§ã³
MySQLã®5.7.26
PostgreSQL 10.5
DBããŒã
3306
5433
IPã¢ãã¬ã¹
192.168.56.102
192.168.56.106
ãŸããpg_chameleon ãã€ã³ã¹ããŒã«ããããã«å¿ èŠãªã³ã³ããŒãã³ãããã¹ãŠæºåããŸãã ãã®äŸã§ã¯ãä»®æ³ç°å¢ãäœæããŠã¢ã¯ãã£ãåãã Python 3.6.8 ãã€ã³ã¹ããŒã«ããŸãã
$> wget https://www.python.org/ftp/python/3.6.8/Python-3.6.8.tar.xz
$> tar -xJf Python-3.6.8.tar.xz
$> cd Python-3.6.8
$> ./configure --enable-optimizations
$> make altinstall
Python3.6 ãæ£åžžã«ã€ã³ã¹ããŒã«ããããä»®æ³ç°å¢ã®äœæãã¢ã¯ãã£ãåãªã©ã®æ®ãã®èŠä»¶ãå®äºããå¿ èŠããããŸãã ããã«ãpip ã¢ãžã¥ãŒã«ãææ°ããŒãžã§ã³ã«æŽæ°ãããpg_chameleon ã®ã€ã³ã¹ããŒã«ã«äœ¿çšãããŸãã 以äžã®ã³ãã³ãã¯ãææ°ããŒãžã§ã³ã¯ 2.0.9 ã§ãããæå³çã« pg_chameleon 2.0.10 ãã€ã³ã¹ããŒã«ããŸãã ããã¯ãæŽæ°ãããããŒãžã§ã³ã§ã®æ°ããªãã°ãåé¿ããããã«å¿ èŠã§ãã
$> python3.6 -m venv venv
$> source venv/bin/activate
(venv) $> pip install pip --upgrade
(venv) $> pip install pg_chameleon==2.0.9
次ã«ãset_configuration_files åŒæ°ãæå®ã㊠pg_chameleon (chameleon ã¯ã³ãã³ãã§ã) ãåŒã³åºããpg_chameleon ãæå¹ã«ããããã©ã«ãã®ãã£ã¬ã¯ããªãšæ§æãã¡ã€ã«ãäœæããŸãã
(venv) $> chameleon set_configuration_files
creating directory /root/.pg_chameleon
creating directory /root/.pg_chameleon/configuration/
creating directory /root/.pg_chameleon/logs/
creating directory /root/.pg_chameleon/pid/
copying configuration example in /root/.pg_chameleon/configuration//config-example.yml
ããã§ãconfig-example.yml ã®ã³ããŒãdefault.yml ãšããŠäœæãããããããã©ã«ãã®æ§æãã¡ã€ã«ã«ãªãããã«ããŸãã ãã®äŸã®ãµã³ãã«æ§æãã¡ã€ã«ã以äžã«ç€ºããŸãã
$> cat default.yml
---
#global settings
pid_dir: '~/.pg_chameleon/pid/'
log_dir: '~/.pg_chameleon/logs/'
log_dest: file
log_level: info
log_days_keep: 10
rollbar_key: ''
rollbar_env: ''
# type_override allows the user to override the default type conversion into a different one.
type_override:
"tinyint(1)":
override_to: boolean
override_tables:
- "*"
#postgres destination connection
pg_conn:
host: "192.168.56.106"
port: "5433"
user: "usr_replica"
password: "pass123"
database: "db_replica"
charset: "utf8"
sources:
mysql:
db_conn:
host: "192.168.56.102"
port: "3306"
user: "usr_replica"
password: "pass123"
charset: 'utf8'
connect_timeout: 10
schema_mappings:
world_x: pgworld_x
limit_tables:
# - delphis_mediterranea.foo
skip_tables:
# - delphis_mediterranea.bar
grant_select_to:
- usr_readonly
lock_timeout: "120s"
my_server_id: 100
replica_batch_size: 10000
replay_max_rows: 10000
batch_retention: '1 day'
copy_max_memory: "300M"
copy_mode: 'file'
out_dir: /tmp
sleep_loop: 1
on_error_replay: continue
on_error_read: continue
auto_maintenance: "disabled"
gtid_enable: No
type: mysql
skip_events:
insert:
- delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo
delete:
- delphis_mediterranea #skips deletes on schema delphis_mediterranea
update:
ãã®äŸã®æ§æãã¡ã€ã«ã¯ããœãŒã¹ç°å¢ãšã¿ãŒã²ããç°å¢ã«åãããŠè¥å¹²ã®å€æŽãå ãããµã³ãã« pg_chameleon ãã¡ã€ã«ã§ãã以äžã«ãæ§æãã¡ã€ã«ã®ããŸããŸãªã»ã¯ã·ã§ã³ã®æŠèŠã瀺ããŸãã
default.yml æ§æãã¡ã€ã«ã«ã¯ãã°ããŒãã«èšå®ã®ã»ã¯ã·ã§ã³ãããããã㯠ãã¡ã€ã«ã®å Žæããã°ã®å Žæããã°ã®ä¿åæéãªã©ã®èšå®ã管çã§ããŸãã次ã«ã¿ã€ã ãªãŒããŒã©ã€ã ã»ã¯ã·ã§ã³ãç¶ããŸããã¬ããªã±ãŒã·ã§ã³äžã«ã¿ã€ãããªãŒããŒã©ã€ãããããã®äžé£ã®ã«ãŒã«ã ãã®äŸã§ã¯ãããã©ã«ãã§ã tinyint(1) ãããŒã«å€ã«å€æããåãªãŒããŒã©ã€ã ã«ãŒã«ãèšå®ãããŠããŸãã 次ã®ã»ã¯ã·ã§ã³ã§ã¯ãã¿ãŒã²ãã ããŒã¿ããŒã¹ãžã®æ¥ç¶ã®è©³çŽ°ãæå®ããŸãã ãã®äŸã§ã¯ããã㯠pg_conn ãšæå®ããã PostgreSQL ããŒã¿ããŒã¹ã§ãã æåŸã®ã»ã¯ã·ã§ã³ã§ã¯ããœãŒã¹ ããŒã¿ãã€ãŸããœãŒã¹ ããŒã¿ããŒã¹ã®æ¥ç¶ãã©ã¡ãŒã¿ããœãŒã¹ ããŒã¿ããŒã¹ãšã¿ãŒã²ãã ããŒã¿ããŒã¹éã®ãããã³ã° ã¹ããŒã ãã¹ãããããå¿ èŠãããããŒãã«ãåŸ æ©æéãã¡ã¢ãªãããã±ãŒãž ãµã€ãºã瀺ããŸãã ããœãŒã¹ãã¯è€æ°åœ¢ã§ããããšã«æ³šæããŠãã ãããããã¯ãè€æ°ã®ãœãŒã¹ ããŒã¿ããŒã¹ã XNUMX ã€ã®ã¿ãŒã²ãã ããŒã¿ããŒã¹ã«è¿œå ããŠãå€å¯Ÿ XNUMX æ§æãã»ããã¢ããã§ããããšãæå³ããŸãã
ãµã³ãã« ããŒã¿ããŒã¹ world_x ã«ã¯ãMySQL ã³ãã¥ããã£ããµã³ãã«ãšããŠæäŸããè¡ãå«ã 4 ã€ã®ããŒãã«ãå«ãŸããŠããŸãã ããŠã³ããŒãã§ããŸã
MySQL ããã³ PostgreSQL ããŒã¿ããŒã¹ã§ã¯ãåãåå usr_replica ã§ç¹å¥ãªãŠãŒã¶ãŒãäœæãããŸãã MySQL ã§ã¯ãã¬ããªã±ãŒãããããã¹ãŠã®ããŒãã«ã«å¯Ÿããè¿œå ã®èªã¿åãæš©éãäžããããŸãã
mysql> CREATE USER usr_replica ;
mysql> SET PASSWORD FOR usr_replica='pass123';
mysql> GRANT ALL ON world_x.* TO 'usr_replica';
mysql> GRANT RELOAD ON *.* to 'usr_replica';
mysql> GRANT REPLICATION CLIENT ON *.* to 'usr_replica';
mysql> GRANT REPLICATION SLAVE ON *.* to 'usr_replica';
mysql> FLUSH PRIVILEGES;
PostgreSQL åŽã§ã¯ãMySQL ããŒã¿ããŒã¹ããã®å€æŽãåãå ¥ãã db_replica ããŒã¿ããŒã¹ãäœæãããŸãã PostgreSQL ã®ãŠãŒã¶ãŒ usr_replica ã¯ãå®éã®ã¬ããªã±ãŒã ããŒãã«ãšã¬ããªã±ãŒã·ã§ã³ ãã£ã¬ã¯ã㪠ããŒãã«ãããããå«ã XNUMX ã€ã®ã¹ããŒã pgworld_x ãš sch_chameleon ã®ææè ãšããŠèªåçã«æ§æãããŸãã 以äžã«ç€ºãããã«ãcreate_replica_schema åŒæ°ã¯èªåæ§æãæ åœããŸãã
postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';
CREATE ROLE
postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;
CREATE DATABASE
MySQL ããŒã¿ããŒã¹ã¯ã以äžã«ç€ºãããã«ãã¬ããªã±ãŒã·ã§ã³ã®æºåãšããŠããã€ãã®ãã©ã¡ãŒã¿ãŒãå€æŽããŠæ§æãããŠããŸãã å€æŽãæå¹ã«ããã«ã¯ãããŒã¿ããŒã¹ ãµãŒããŒãåèµ·åããå¿ èŠããããŸãã
$> vi /etc/my.cnf
binlog_format= ROW
binlog_row_image=FULL
log-bin = mysql-bin
server-id = 1
ããã§ãpg_chameleon ã³ãã³ãã®å®è¡æã«åé¡ãçºçããªãããã«ãäž¡æ¹ã®ããŒã¿ããŒã¹ ãµãŒããŒãžã®æ¥ç¶ã確èªããããšãéèŠã§ãã
PostgreSQL ããŒãäž:
$> mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_x
MySQL ããŒãäž:
$> psql -p 5433 -U usr_replica -h 192.168.56.106 db_replica
次㮠XNUMX ã€ã® pg_chameleon (ã«ã¡ã¬ãªã³) ã³ãã³ãã¯ãç°å¢ãæºåãããœãŒã¹ãè¿œå ããã¬ããªã«ãåæåããŸãã ãã§ã«èª¬æããããã«ãpg_chameleon ã® create_replica_schema åŒæ°ã¯ãPostgreSQL ããŒã¿ããŒã¹ã«ããã©ã«ã ã¹ããŒã (sch_chameleon) ãšã¬ããªã±ãŒã·ã§ã³ ã¹ããŒã (pgworld_x) ãäœæããŸãã add_source åŒæ°ã¯ãæ§æãã¡ã€ã« (default.yml) ãèªã¿åãããšã§ãœãŒã¹ ããŒã¿ããŒã¹ãæ§æã«è¿œå ããŸãããã®å Žåããã㯠mysql ã§ãããinit_replica ã¯æ§æãã¡ã€ã«å ã®ãã©ã¡ãŒã¿ãŒã«åºã¥ããŠæ§æãåæåããŸãã
$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --source mysql --debug
$> chameleon init_replica --config default --source mysql --debug
ããã XNUMX ã€ã®ã³ãã³ãã®åºåã¯ãããããæ£åžžã«å®è¡ãããããšãæ確ã«ç€ºããŠããŸãã ã¯ã©ãã·ã¥ãæ§æãšã©ãŒã¯ãåé¡ã®è§£æ±ºæ¹æ³ã®ãã³ããå«ãã·ã³ãã«ã§æ確ãªã¡ãã»ãŒãžã§å ±åãããŸãã
æåŸã«ãstart_replica ã䜿çšããŠã¬ããªã±ãŒã·ã§ã³ãéå§ããæåã¡ãã»ãŒãžãåãåããŸãã
$> chameleon start_replica --config default --source mysql
output: Starting the replica process for source mysql
ã¬ããªã±ãŒã·ã§ã³ã®ã¹ããŒã¿ã¹ã¯ show_status åŒæ°ã䜿çšããŠç §äŒã§ãããšã©ãŒã¯ show_errors åŒæ°ã䜿çšããŠè¡šç€ºã§ããŸãã
ãã§ã«è¿°ã¹ãããã«ãåã¬ããªã±ãŒã·ã§ã³æ©èœã¯ããŒã¢ã³ã«ãã£ãŠå¶åŸ¡ãããŸãã ãããã衚瀺ããã«ã¯ã次ã«ç€ºãããã«ãLinux ps ã³ãã³ãã䜿çšããŠããã»ã¹ ããŒãã«ãã¯ãšãªããŸãã
以äžã«ç€ºãããã«ãã¬ããªã±ãŒã·ã§ã³ã¯ãªã¢ã«ã¿ã€ã ã§ãã¹ããããŸã§æ§æãããŠãããšã¯èŠãªãããŸããã ããŒãã«ãäœæããããã€ãã®ã¬ã³ãŒãã MySQL ããŒã¿ããŒã¹ã«æ¿å ¥ããpg_chameleon ã® sync_tables åŒæ°ãåŒã³åºããŠããŒã¢ã³ãæŽæ°ããã¬ã³ãŒããå«ãããŒãã«ã PostgreSQL ããŒã¿ããŒã¹ã«è€è£œããŸãã
mysql> create table t1 (n1 int primary key, n2 varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,'one');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (2,'two');
Query OK, 1 row affected (0.00 sec)
$> chameleon sync_tables --tables world_x.t1 --config default --source mysql
Sync tables process for source mysql started.
ãã¹ãçµæã確èªããããã«ãPostgreSQL ããŒã¿ããŒã¹ããããŒãã«ã«ã¯ãšãªãå®è¡ããè¡ãåºåããŸãã
$> psql -p 5433 -U usr_replica -d db_replica -c "select * from pgworld_x.t1";
n1 | n2
----+-------
1 | one
2 | two
移è¡ãè¡ã£ãŠããå Žåã¯ã次㮠pg_chameleon ã³ãã³ãã§çµäºããŸãã ãã¹ãŠã®ã¿ãŒã²ãã ããŒãã«ã®è¡ãã¬ããªã±ãŒããããããšã確èªããåŸã§ã³ãã³ããå®è¡ããå¿ èŠããããŸãããã®çµæããœãŒã¹ ããŒã¿ããŒã¹ãã¬ããªã±ãŒã·ã§ã³ ã¹ããŒã (sch_chameleon) ãåç §ããã«ãé©åã«ç§»è¡ããã PostgreSQL ããŒã¿ããŒã¹ãäœæãããŸãã
$> chameleon stop_replica --config default --source mysql
$> chameleon detach_replica --config default --source mysql --debug
å¿ èŠã«å¿ããŠã次ã®ã³ãã³ãã䜿çšããŠãå ã®æ§æãšã¬ããªã±ãŒã·ã§ã³ ã¹ããŒã ãåé€ã§ããŸãã
$> chameleon drop_source --config default --source mysql --debug
$> chameleon drop_replica_schema --config default --source mysql --debug
pg_chameleonã®å©ç¹
ã»ããã¢ãããšæ§æãç°¡åã
æ確ãªãšã©ãŒ ã¡ãã»ãŒãžã§ç°¡åã«ãã©ãã«ã·ã¥ãŒãã£ã³ã°ãè¡ããç°åžžãç¹å®ããŸãã
åæååŸã«æ®ãã®æ§æãå€æŽããã«ãè¿œå ã®ç¹å¥ãªããŒãã«ãã¬ããªã±ãŒã·ã§ã³ã«è¿œå ã§ããŸãã
åäžã®ã¿ãŒã²ãã ããŒã¿ããŒã¹ã«å¯ŸããŠè€æ°ã®ãœãŒã¹ ããŒã¿ããŒã¹ãæ§æããããšãå¯èœã§ãããããã¯ãXNUMX ã€ä»¥äžã® MySQL ããŒã¿ããŒã¹ã®ããŒã¿ãåäžã® PostgreSQL ããŒã¿ããŒã¹ã«çµåããå Žåã«éåžžã«äŸ¿å©ã§ãã
éžæããããŒãã«ãè€è£œããå¿
èŠã¯ãããŸããã
pg_chameleon ã®æ¬ ç¹
ãœãŒã¹ãšã㊠MySQL 5.5 以éãã¿ãŒã²ãã ããŒã¿ããŒã¹ãšã㊠PostgreSQL 9.5 以éã§ã®ã¿ãµããŒããããŸãã
åããŒãã«ã«ã¯äž»ããŒãŸãã¯äžæããŒãå¿
èŠã§ããããã§ãªãå ŽåãããŒãã«ã¯ init_replica ããã»ã¹äžã«åæåãããŸãããã¬ããªã±ãŒããããŸããã
äžæ¹åã¬ããªã±ãŒã·ã§ã³ - MySQL ãã PostgreSQL ãžã®ã¿ã ãããã£ãŠããã¢ã¯ãã£ã-ããã·ããåè·¯ã«ã®ã¿é©ããŠããŸãã
ãœãŒã¹ãšããŠäœ¿çšã§ããã®ã¯ MySQL ããŒã¿ããŒã¹ã®ã¿ã§ãããœãŒã¹ãšããŠã® PostgreSQL ããŒã¿ããŒã¹ã®ãµããŒãã¯å®éšçãªãã®ã§ãããå¶éããããŸã (詳现ã¯ãã¡ã)
pg_chameleon ã®çµæ
pg_chameleon ã®ã¬ããªã±ãŒã·ã§ã³æ¹æ³ã¯ãããŒã¿ããŒã¹ã MySQL ãã PostgreSQL ã«ç§»è¡ããã®ã«æé©ã§ãã é倧ãªæ¬ ç¹ã¯ãã¬ããªã±ãŒã·ã§ã³ãäžæ¹åã®ã¿ã§ãããããããŒã¿ããŒã¹ã®å°é家ãã¬ããªã±ãŒã·ã§ã³ã移è¡ä»¥å€ã®ç®çã§äœ¿çšããå¯èœæ§ãäœãããšã§ãã ããããäžæ¹åã¬ããªã±ãŒã·ã§ã³ã®åé¡ã¯ãå¥ã®ãªãŒãã³ãœãŒã¹ ããŒã«ã§ãã SymmetricDS ã䜿çšããŠè§£æ±ºã§ããŸãã
詳现ã¯å
¬åŒããã¥ã¡ã³ããã芧ãã ãã
SymmetricDSã®æŠèŠ
SymmetricDS ã¯ãä»»æã®ããŒã¿ããŒã¹ãä»ã®äžè¬çãªããŒã¿ããŒã¹ (OracleãMongoDBãPostgreSQLãMySQLãSQL ServerãMariaDBãDB2ãSybaseãGreenplumãInformixãH2ãFirebirdãããã³ãã®ä»ã®ã¯ã©ãŠã ããŒã¿ããŒã¹ ã€ã³ã¹ã¿ã³ã¹ (äŸ: Redshift)) ã«ã¬ããªã±ãŒããããªãŒãã³ ãœãŒã¹ ããŒã«ã§ãã Azure ãªã© å©çšå¯èœãªæ©èœ: ããŒã¿ããŒã¹ãšãã¡ã€ã«ã®åæããã«ããã¹ã¿ãŒ ããŒã¿ããŒã¹ ã¬ããªã±ãŒã·ã§ã³ããã£ã«ã¿ãŒåæãå€æãªã©ã ãã㯠Java ããŒã«ã§ãããJRE ãŸã㯠JDK ã®æšæºãªãªãŒã¹ (ããŒãžã§ã³ 8.0 以é) ãå¿ èŠã§ãã ããã§ã¯ããœãŒã¹ ããŒã¿ããŒã¹ã®ããªã¬ãŒã«å¯ŸããããŒã¿å€æŽãèšé²ããããã圢åŒã§é©åãªã¿ãŒã²ãã ããŒã¿ããŒã¹ã«éä¿¡ã§ããŸãã
SymmetricDS ã®æ©èœ
ãã®ããŒã«ã¯ãã©ãããã©ãŒã ã«äŸåããŸãããã€ãŸããXNUMX ã€ä»¥äžã®ç°ãªãããŒã¿ããŒã¹ãããŒã¿ã亀æã§ããŸãã
ãªã¬ãŒã·ã§ãã« ããŒã¿ããŒã¹ã¯ããŒã¿å€æŽã¬ã³ãŒãã䜿çšããŠåæãããŸããããã¡ã€ã« ã·ã¹ãã ããŒã¹ã®ããŒã¿ããŒã¹ã¯ãã¡ã€ã«åæã䜿çšããŸãã
äžé£ã®ã«ãŒã«ã«åºã¥ããããã·ã¥ããã³ãã«æ¹åŒã䜿çšããåæ¹åã¬ããªã±ãŒã·ã§ã³ã
ããŒã¿è»¢éã¯ãå®å
šã§äœåž¯åå¹
ã®ãããã¯ãŒã¯çµç±ã§å¯èœã§ãã
é害åŸã«ããŒããåäœãåéãããšãã®èªåå埩ãšèªå競å解決ã
ã¯ã©ãŠãäºææ§ã®ãã匷åãªæ¡åŒµ APIã
äŸ
SymmetricDS ã¯ã次㮠XNUMX ã€ã®ãªãã·ã§ã³ã®ããããã§æ§æã§ããŸãã
XNUMX ã€ã®ã¹ã¬ãŒã (å) ããŒãéã®ããŒã¿è€è£œãéäžçã«èª¿æŽãããã¹ã¿ãŒ (芪) ããŒããåããŒãéã®éä¿¡ã¯èŠªãä»ããŠã®ã¿è¡ãããŸãã
ã¢ã¯ãã£ã ããŒã (ããŒã 1) ã¯ã仲ä»è
ãªãã§å¥ã®ã¢ã¯ãã£ã ããŒã (ããŒã 2) ãšè€è£œã®ããã«éä¿¡ã§ããŸãã
ã©ã¡ãã®ãªãã·ã§ã³ã§ããããŒã¿äº€æã¯ããã·ã¥ãšãã«ã䜿çšããŠè¡ãããŸãã ãã®äŸã§ã¯ãã¢ã¯ãã£ã/ã¢ã¯ãã£ãæ§æãæ€èšããŸãã ã¢ãŒããã¯ãã£å
šäœã説æãããšé·ããªãã®ã§ã調ã¹ãŠãã ããã
SymmetricDS ã®ã€ã³ã¹ããŒã«ã¯éåžžã«ç°¡åã§ããzip ãã¡ã€ã«ã®ãªãŒãã³ãœãŒã¹ ããŒãžã§ã³ãããŠã³ããŒãããŸãã
ãã¹ã
vm1
vm2
OSããŒãžã§ã³
CentOS Linux 7.6 x86_64
CentOS Linux 7.6 x86_64
DBãµãŒããŒã®ããŒãžã§ã³
MySQLã®5.7.26
PostgreSQL 10.5
DBããŒã
3306
5832
IPã¢ãã¬ã¹
192.168.1.107
192.168.1.112
SymmetricDSã®ããŒãžã§ã³
SymmetricDS 3.9
SymmetricDS 3.9
SymmetricDSã®ã€ã³ã¹ããŒã«ãã¹
/usr/local/察称ãµãŒããŒ-3.9.20
/usr/local/察称ãµãŒããŒ-3.9.20
SymmetricDS ããŒãå
corp-000
ã¹ãã¢-001
ããã§ã¯ãSymmetricDS ã /usr/local/symmetric-server-3.9.20 ã«ã€ã³ã¹ããŒã«ããŸããããã«ããŸããŸãªãµããã£ã¬ã¯ããªãšãã¡ã€ã«ãä¿åãããŸãã ç§ãã¡ã¯ãµã³ãã«ãšãšã³ãžã³ã®ãµããã£ã¬ã¯ããªã«èå³ããããŸãã ãµã³ãã« ãã£ã¬ã¯ããªã«ã¯ãããŒã ããããã£ãå«ããµã³ãã«æ§æãã¡ã€ã«ãšãããã«éå§ã§ãããµã³ãã« SQL ã¹ã¯ãªãããå«ãŸããŠããŸãã
ãµã³ãã« ãã£ã¬ã¯ããªã«ã¯ãããŒã ããããã£ãå«ã XNUMX ã€ã®æ§æãã¡ã€ã«ããããŸããååã¯ãç¹å®ã®ã¹ããŒã ã«ãããããŒãã®æ§è³ªã瀺ããŠããŸãã
corp-000.properties
store-001.properties
store-002.properties
SymmetricDS ã«ã¯ãåºæ¬ç㪠3 ããŒãèšèš (ãªãã·ã§ã³ 1) ã«å¿ èŠãªæ§æãã¡ã€ã«ããã¹ãŠå«ãŸããŠãããåããã¡ã€ã«ã 2 ããŒãèšèš (ãªãã·ã§ã³ 2) ã«äœ¿çšã§ããŸãã å¿ èŠãªæ§æãã¡ã€ã«ããµã³ãã« ãã£ã¬ã¯ããªãã vm1 ãã¹ãäžã®ãšã³ãžã³ã«ã³ããŒããŸãã 次ã®ããã«ãªããŸãã
$> cat engines/corp-000.properties
engine.name=corp-000
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://192.168.1.107:3306/replica_db?autoReconnect=true&useSSL=false
db.user=root
db.password=admin123
registration.url=
sync.url=http://192.168.1.107:31415/sync/corp-000
group.id=corp
external.id=000
SymmetricDS æ§æå ã®ãã®ããŒã㯠corp-000 ãšåŒã°ããããŒã¿ããŒã¹æ¥ç¶ã¯ãäžèšã®æ¥ç¶æååãšãã°ã€ã³è³æ Œæ å ±ã䜿çšãã mysql jdbc ãã©ã€ããŒã«ãã£ãŠåŠçãããŸãã plica_db ããŒã¿ããŒã¹ã«æ¥ç¶ãããšãã¹ããŒãã®äœæäžã«ããŒãã«ãäœæãããŸãã sync.url ã¯ãåæã®ããã«ããŒãã«æ¥ç¶ããå Žæã瀺ããŸãã
ãã¹ã vm2 äžã®ããŒã 2 㯠store-001 ãšããŠæ§æãããæ®ãã¯ä»¥äžã® node.properties ãã¡ã€ã«ã§æå®ãããŸãã ããŒãstore-001ã¯PostgreSQLããŒã¿ããŒã¹ãå®è¡ããpgdb_replicaã¯ã¬ããªã±ãŒã·ã§ã³ããŒã¿ããŒã¹ã§ãã register.url ã«ããããã¹ã vm2 ã¯ãã¹ã vm1 ã«æ¥ç¶ãããã¹ã vmXNUMX ããæ§æã®è©³çŽ°ãåä¿¡ã§ããããã«ãªããŸãã
$> cat engines/store-001.properties
engine.name=store-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.1.112:5832/pgdb_replica
db.user=postgres
db.password=admin123
registration.url=http://192.168.1.107:31415/sync/corp-000
group.id=store
external.id=001
å®æãã SymmetricDS ã®äŸã«ã¯ã1 ã€ã®ããŒã¿ããŒã¹ ãµãŒã㌠(000 ã€ã®ããŒã) éã§åæ¹åã¬ããªã±ãŒã·ã§ã³ãèšå®ããããã®ãã©ã¡ãŒã¿ãå«ãŸããŠããŸãã 以äžã®æé ã¯ãã¹ã vm4 (corp-XNUMX) ã§å®è¡ãããXNUMX ã€ã®ããŒãã«ãæã€ãµã³ãã« ã¹ããŒããäœæãããŸãã 次ã«ãsymadmin ã³ãã³ã㧠create-sym-tables ãå®è¡ãããšãããŒãéã®ã¬ããªã±ãŒã·ã§ã³ã®ã«ãŒã«ãšæ¹åãä¿åããããã£ã¬ã¯ã㪠ããŒãã«ãäœæãããŸãã æåŸã«ããµã³ãã« ããŒã¿ãããŒãã«ã«ããŒããããŸãã
vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> ./dbimport --engine corp-000 --format XML create_sample.xml
vm1$> ./symadmin --engine corp-000 create-sym-tables
vm1$> ./dbimport --engine corp-000 insert_sample.sql
ãã®äŸã§ã¯ãitem ããŒãã«ãš item_delivery_price ããŒãã«ã¯ãcorp-000 ãã store-001 ã«è€è£œãããããã«èªåçã«æ§æãããsale ããŒãã« (sale_transaction ããã³ sale_return_line_item) ã¯ãstore-001 ãã corp-000 ã«è€è£œãããããã«èªåçã«æ§æãããŸãã 次ã«ããã¹ã vm2 (store-001) äžã® PostgreSQL ããŒã¿ããŒã¹ã«ã¹ããŒããäœæããcorp-000 ããããŒã¿ãåä¿¡ã§ããããã«æºåããŸãã
vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> ./dbimport --engine store-001 --format XML create_sample.xml
vm1 äžã® MySQL ããŒã¿ããŒã¹ã«ãµã³ãã« ããŒãã«ãš SymmetricDS ã«ã¿ãã° ããŒãã«ãããããšãå¿ ã確èªããŠãã ããã SymmetricDS ã·ã¹ãã ããŒãã« (sym_ ã®ãã¬ãã£ãã¯ã¹ãä»ã) ã¯ãcreate-sym-tables ã³ãã³ããå®è¡ããã¬ããªã±ãŒã·ã§ã³ã管çããããŒã corp-000 ã§ã®ã¿äœ¿çšã§ããããšã«æ³šæããŠãã ããã ãŸããããŒã store-001 ã®ããŒã¿ããŒã¹ã«ã¯ãããŒã¿ã®ãªããµã³ãã« ããŒãã«ã 4 ã€ã ããããŸãã
å šãŠã 以äžã«ç€ºãããã«ãç°å¢ã¯äž¡æ¹ã®ããŒã㧠sym ãµãŒã㌠ããã»ã¹ãå®è¡ããæºåãã§ããŠããŸãã
vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> sym 2>&1 &
ãã° ãšã³ããªã¯ãæšæºåºåã ãã§ãªããSymmetricDS ãã€ã³ã¹ããŒã«ãããŠãããã£ã¬ã¯ããªã® logs ãã©ã«ãã«ããããã¯ã°ã©ãŠã³ã ãã° ãã¡ã€ã« (symmetric.log) ã«ãéä¿¡ãããŸãã ããã§ãããŒãstore-001ã§symãµãŒããŒãéå§ã§ããããã«ãªããŸãã
vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> sym 2>&1 &
vm2 ãã¹ã㧠sym ãµãŒã㌠ããã»ã¹ãå®è¡ãããšãPostgreSQL ããŒã¿ããŒã¹ã« SymmetricDS ã«ã¿ãã° ããŒãã«ãäœæãããŸãã äž¡æ¹ã®ããŒã㧠sym ãµãŒã㌠ããã»ã¹ãå®è¡ãããšãçžäºã«é£æºããŠããŒã¿ã corp-000 ãã store-001 ã«è€è£œããŸãã æ°ç§åŸã«äž¡åŽã® 4 ã€ã®ããŒãã«ãã¹ãŠã«ã¯ãšãªãå®è¡ãããšãã¬ããªã±ãŒã·ã§ã³ãæåããããšãããããŸãã ãŸãã¯ã次ã®ã³ãã³ãã䜿çšããŠãcorp-001 ããããŒã store-000 ã«ããŒãã¹ãã©ãããéä¿¡ã§ããŸãã
vm1$> ./symadmin --engine corp-000 reload-node 001
ãã®æç¹ã§ãããŒã corp-000 (ãã¹ã: vm1) äžã® MySQL ããŒã¿ããŒã¹ã®é ç®ããŒãã«ã«æ°ããã¬ã³ãŒããæ¿å ¥ãããããŒã store-001 (ãã¹ã: vm2) äžã® PostgreSQL ããŒã¿ããŒã¹ãžã®ã¬ããªã±ãŒã·ã§ã³ã確èªã§ããŸãã ããŒã¿ãcorp-000ããstore-001ã«ç§»åãããã«æäœã衚瀺ãããŸãã
mysql> insert into item values ('22000002','Jelly Bean');
Query OK, 1 row affected (0.00 sec)
vm2$> psql -p 5832 -U postgres pgdb_replica -c "select * from item"
item_id | name
----------+-----------
11000001 | Yummy Gum
22000002 | Jelly Bean
(2 rows)
ããã·ã¥æäœãå®è¡ããŠãstore-001 ãã corp-000 ã«ããŒã¿ã移åããã«ã¯ãsale_transaction ããŒãã«ã«ã¬ã³ãŒããæ¿å ¥ããã¬ããªã±ãŒã·ã§ã³ãæåããããšã確èªããŸãã
MySQL ããŒã¿ããŒã¹ãš PostgreSQL ããŒã¿ããŒã¹ã®éã§ãµã³ãã« ããŒãã«ã®åæ¹åã¬ããªã±ãŒã·ã§ã³ãæ£åžžã«ã»ããã¢ãããããŠããããšãããããŸãã æ°ãããŠãŒã¶ãŒ ããŒãã«ã®ã¬ããªã±ãŒã·ã§ã³ãèšå®ããã«ã¯ã次ã®æé ã«åŸããŸãã ããšãã°ããŒãã« t1 ãäœæãããã®ã¬ããªã±ãŒã·ã§ã³ ã«ãŒã«ã次ã®ããã«æ§æããŸãã ãã®ããã«ããŠãcorp-000 ãã store-001 ãžã®ã¬ããªã±ãŒã·ã§ã³ã®ã¿ãæ§æããŸãã
mysql> create table t1 (no integer);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into sym_channel (channel_id,create_time,last_update_time)
values ('t1',current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sym_trigger (trigger_id, source_table_name,channel_id,
last_update_time, create_time) values ('t1', 't1', 't1', current_timestamp,
current_timestamp);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sym_trigger_router (trigger_id, router_id,
Initial_load_order, create_time,last_update_time) values ('t1',
'corp-2-store-1', 1, current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)
次ã«ãsync-triggers åŒæ°ãæå®ãã symadmin ã³ãã³ãã䜿çšããŠãã¹ããŒãã®å€æŽãã€ãŸãæ°ããããŒãã«ã®è¿œå ãæ§æã«éç¥ãããŸããããã«ãããããŒãã«å®çŸ©ããããããããªã¬ãŒãåäœæãããŸãã send-schema ãå®è¡ãããŠã¹ããŒãå€æŽãããŒã store-001 ã«éä¿¡ãããããŒãã« t1 ã®ã¬ããªã±ãŒã·ã§ã³ãæ§æãããŸãã
vm1$> ./symadmin -e corp-000 --node=001 sync-triggers
vm1$> ./symadmin send-schema -e corp-000 --node=001 t1
SymmetricDS ã®å©ç¹
XNUMX ããŒããŸã㯠XNUMX ããŒãåè·¯ãäœæããããã®ãã©ã¡ãŒã¿ãå«ãæ¢è£œã®ãã¡ã€ã« ã»ãããå«ããã€ã³ã¹ããŒã«ãšæ§æãç°¡åã§ãã
ãµãŒããŒãã©ããããããã¢ãã€ã« ããã€ã¹ãªã©ãã¯ãã¹ãã©ãããã©ãŒã ã®ããŒã¿ããŒã¹ãšãã©ãããã©ãŒã ã®ç¬ç«æ§ã
ããããããŒã¿ããŒã¹ãããŒã«ã«ãWANããŸãã¯ã¯ã©ãŠãäžã®ä»ã®ããŒã¿ããŒã¹ã«ã¬ããªã±ãŒãããŸãã
䟿å©ãªã¬ããªã±ãŒã·ã§ã³ã®ããã«ãããã€ãã®ããŒã¿ããŒã¹ãŸãã¯æ°åã®ããŒã¿ããŒã¹ã䜿çšããŠæé©ã«åäœããå¯èœæ§ããããŸãã
GUIãšåªãããµããŒããåããææçã
SymmetricDS ã®æ¬ ç¹
ã«ã¿ãã° ããŒãã«ãããŒãããã«ã¯ãSQL ã¹ããŒãã¡ã³ãã䜿çšããŠã³ãã³ã ã©ã€ã³ã§ã¬ããªã±ãŒã·ã§ã³ã®ã«ãŒã«ãšæ¹åãæåã§å®çŸ©ããå¿
èŠããããŸãããããã¯äžäŸ¿ãªå ŽåããããŸãã
ã¹ã¯ãªããã䜿çšããŠã¬ããªã±ãŒã·ã§ã³ã®ã«ãŒã«ãšæ¹åãå®çŸ©ãã SQL ã¹ããŒãã¡ã³ããäœæããªãéããã¬ããªã±ãŒã·ã§ã³çšã«å€æ°ã®ããŒãã«ãèšå®ããã®ã¯é¢åãªäœæ¥ã«ãªãå¯èœæ§ããããŸãã
ãã°ã«èšé²ãããæ
å ±ãå€ããããããã¹ããŒã¹ãå æããããªãããã«ãã° ãã¡ã€ã«ãæŽçããå¿
èŠãããå ŽåããããŸãã
SymmetricDS ã®çµæ
SymmetricDS ã䜿çšãããšãXNUMX ã€ãXNUMX ã€ããããã¯æ°åã®ããŒãéã§åæ¹åã¬ããªã±ãŒã·ã§ã³ãèšå®ããŠããã¡ã€ã«ãè€è£œããã³åæã§ããŸãã ããã¯ãããŒãã§ã®é·æéã®ããŠã³ã¿ã€ã åŸã®èªåããŒã¿å埩ãHTTPS ãä»ããããŒãéã®å®å šãã€å¹ççãªããŒã¿äº€æãäžé£ã®ã«ãŒã«ã«åºã¥ãèªå競å管çãªã©ãå€ãã®ã¿ã¹ã¯ãç¬ç«ããŠå®è¡ããç¬èªã®ããŒã«ã§ãã SymmetricDS ã¯ããããã£ãŠãä»»æã®ããŒã¿ããŒã¹éã§ã¬ããªã±ãŒã·ã§ã³ãå®è¡ã§ããããããã©ãããã©ãŒã éã§ã®ããŒã¿ã®ç§»è¡ã移è¡ãåæ£ããã£ã«ã¿ãªã³ã°ãå€æãªã©ãããŸããŸãªã·ããªãªã«äœ¿çšã§ããŸãã
äŸã¯å
¬åŒã«åºã¥ããŠããŸã
åºæïŒ habr.com