Kuv mam li piav qhia txog kev sib sau ua ke ntawm PostgreSQL thiab MySQL, nrog rau cov txheej txheem rau kev teeb tsa tus ntoo khaub lig-replication ntawm ob lub database servers. Feem ntau, cross-replicated databases yog hu ua homogeneous, thiab nws yog ib txoj hauv kev yooj yim ntawm kev txav ntawm ib tus neeg rau zaub mov RDBMS mus rau lwm qhov.
PostgreSQL thiab MySQL databases feem ntau suav hais tias yog kev sib raug zoo, tab sis nrog kev txuas ntxiv lawv muaj peev xwm NoSQL. Ntawm no peb yuav tham txog kev rov ua dua ntawm PostgreSQL thiab MySQL los ntawm kev sib txheeb DBMS foundations.
Peb yuav tsis piav qhia tag nrho cov kev ua haujlwm sab hauv, tsuas yog cov hauv paus ntsiab lus kom koj tau txais ib lub tswv yim ntawm kev teeb tsa kev rov ua dua ntawm cov ntaub ntawv servers, qhov zoo, kev txwv thiab kev siv rooj plaub.
Feem ntau, kev rov ua dua ntawm ob lub chaw khaws ntaub ntawv zoo ib yam yog ua tiav hauv hom binary lossis siv cov lus nug ntawm tus tswv (aka tus tshaj tawm, tus tswv, lossis tus neeg ua haujlwm) thiab tus qhev (tus neeg siv khoom, standby, lossis passive). Lub hom phiaj ntawm kev rov ua dua yog muab cov ntawv luam ntawm lub sijhawm tiag tiag ntawm tus tswv database ntawm tus qhev sab. Nyob rau hauv rooj plaub no, cov ntaub ntawv yog pauv los ntawm tus tswv mus rau qhev, uas yog, los ntawm active mus rau passive, vim hais tias replication tsuas yog ua nyob rau hauv ib qho kev taw qhia. Tab sis koj tuaj yeem teeb tsa kev rov ua dua ntawm ob lub databases hauv ob qho kev qhia, kom cov ntaub ntawv raug xa tawm los ntawm qhev mus rau tus tswv hauv kev ua haujlwm nquag. Tag nrho cov no, suav nrog cascading replication, yog ua tau ntawm ob los yog ntau dua zoo tib yam database servers. Active-active lossis active-passive configuration nyob ntawm qhov xav tau, muaj peev xwm ntawm qhov kev teeb tsa thawj zaug lossis siv cov kev daws teeb meem sab nraud thiab kev lag luam uas twb muaj lawm.
Cov kev piav qhia configuration yog ua tau ntawm txawv database servers. Tus neeg rau zaub mov tuaj yeem teeb tsa kom lees txais cov ntaub ntawv rov ua dua los ntawm lwm tus neeg rau zaub mov database thiab tseem tuav lub sijhawm tiag tiag ntawm cov ntaub ntawv rov ua dua. MySQL thiab PostgreSQL muaj feem ntau ntawm cov kev teeb tsa hauv tsev lossis los ntawm cov neeg thib peb txuas ntxiv, suav nrog cov txheej txheem binary log, disk kaw, thiab nqe lus- thiab kab raws txoj kev.
Hla replication ntawm MySQL thiab PostgreSQL yog xav tau rau kev tsiv teb tsaws ib zaug los ntawm ib tus neeg rau zaub mov mus rau lwm qhov. Cov databases no siv cov txheej txheem sib txawv, yog li nws tsis tuaj yeem txuas ncaj qha rau lawv. Txhawm rau tsim cov ntaub ntawv sib pauv, koj tuaj yeem siv cov cuab yeej qhib sab nraud, piv txwv li pg_chameleon.
Dab tsi yog pg_chameleon
pg_chameleon yog qhov kev rov ua dua los ntawm MySQL rau PostgreSQL hauv Python 3. Nws siv qhov qhib mysql-replication tsev qiv ntawv, kuj hauv Python. Cov duab kab yog muab rho tawm los ntawm MySQL cov ntxhuav thiab khaws cia li JSONB cov khoom hauv PostgreSQL database, thiab tom qab ntawd decrypted los ntawm pl / pgsql muaj nuj nqi thiab rov tsim dua hauv PostgreSQL database.
Nta ntawm pg_chameleon
Ntau MySQL schemas los ntawm tib pawg tuaj yeem rov ua dua rau ib lub hom phiaj PostgreSQL database hauv ib-rau-ntau qhov teeb tsa
Lub hauv paus thiab lub hom phiaj schema npe yuav tsis zoo ib yam.
Cov ntaub ntawv rov qab tuaj yeem rov qab tau los ntawm kev sib tw MySQL.
Cov ntxhuav uas tsis tuaj yeem rov ua dua lossis tsim qhov yuam kev raug cais tawm.
Txhua replication muaj nuj nqi yog tswj los ntawm daemon.
Tswj los ntawm YAML-raws li tsis muaj thiab teeb tsa cov ntaub ntawv.
Piv Txwv:
Tug Tswv
vwm 1
vwm 2
OS version
CentOS Linux 7.6 x86_64
CentOS Linux 7.5 x86_64
DB server version
MySQL 5.7.26
PostgreSQL 10.5
DB chaw nres nkoj
3306
5433
IP chaw nyob
192.168.56.102
192.168.56.106
Txhawm rau pib, npaj tag nrho cov khoom tsim nyog rau nruab pg_chameleon. Qhov piv txwv no tau teeb tsa Python 3.6.8, uas tsim thiab qhib lub chaw virtual.
$> 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 altinstallTom qab ua tiav kev txhim kho Python3.6, koj yuav tsum ua kom tiav cov kev xav tau ntxiv, xws li tsim thiab ua kom muaj ib puag ncig virtual. Tsis tas li ntawd, lub pip module tau hloov kho mus rau qhov tseeb version thiab siv rau nruab pg_chameleon. Cov lus txib hauv qab no txhob txwm nruab pg_chameleon 2.0.9, txawm hais tias qhov tseeb version yog 2.0.10. Qhov no yog qhov tsim nyog kom tsis txhob muaj kab mob tshiab hauv qhov hloov tshiab.
$> python3.6 -m venv venv
$> source venv/bin/activate
(venv) $> pip install pip --upgrade
(venv) $> pip install pg_chameleon==2.0.9Peb mam li hu rau pg_chameleon (chameleon yog ib qho kev hais kom ua) nrog rau cov lus sib cav set_configuration_files kom pab tau pg_chameleon thiab tsim cov npe thiab cov ntaub ntawv teeb tsa.
(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.ymlTam sim no peb tsim ib daim qauv ntawm config-example.yml li default.yml kom nws dhau los ua cov ntaub ntawv teeb tsa. Ib qho piv txwv configuration file rau qhov piv txwv no yog muab hauv qab no.
$> 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:Cov ntaub ntawv teeb tsa hauv qhov piv txwv no yog ib qho piv txwv pg_chameleon cov ntaub ntawv nrog cov kev hloov kho me me kom haum rau qhov chaw thiab lub hom phiaj ib puag ncig, thiab hauv qab no yog cov ntsiab lus ntawm cov ntu sib txawv ntawm cov ntaub ntawv teeb tsa.
Nyob rau hauv cov ntaub ntawv default.yml configuration muaj ib seem ntawm lub ntiaj teb no chaw, qhov twg koj yuav tswj tau tej chaw xws li qhov chaw ntawm lub xauv cov ntaub ntawv, qhov chaw ntawm lub cav, lub sij hawm cia rau cav, thiab lwm yam. Tom ntej no los ntawm hom override seem, qhov twg ib txheej ntawm cov cai rau overriding hom thaum replication. Qhov piv txwv tsis raug rau hom overriding txoj cai uas hloov tinyint(1) mus rau tus nqi boolean. Hauv seem tom ntej, peb qhia meej cov ntsiab lus txuas mus rau lub hom phiaj database. Hauv peb cov ntaub ntawv, qhov no yog PostgreSQL database, xaiv pg_conn. Hauv seem kawg, peb qhia txog cov ntaub ntawv qhov chaw, uas yog, qhov sib txuas tsis sib xws ntawm cov ntaub ntawv hauv qhov chaw, cov phiaj xwm phiaj xwm ntawm qhov chaw thiab lub hom phiaj databases, cov ntxhuav uas yuav tsum tau hla, tos lub sijhawm, nco, pob loj. Nco ntsoov tias "qhov chaw" yog ntau tshaj plaws, lub ntsiab lus peb tuaj yeem ntxiv ntau qhov chaw databases rau ib lub hom phiaj database los teeb tsa ntau-rau-ib qhov kev teeb tsa.
Piv txwv database world_x muaj 4 lub rooj nrog kab uas MySQL zej zog muab ua piv txwv. Nws tuaj yeem rub tawm . Cov qauv database los ua tar thiab compressed archive nrog cov lus qhia rau kev tsim thiab importing kab.
Hauv MySQL thiab PostgreSQL databases, tus neeg siv tshwj xeeb yog tsim nrog tib lub npe usr_replica. Hauv MySQL, nws tau muab cov cai nyeem ntxiv rau txhua lub rooj sib txuas.
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;Ntawm PostgreSQL sab, db_replica database yog tsim uas yuav txais kev hloov pauv los ntawm MySQL database. Tus neeg siv usr_replica nyob rau hauv PostgreSQL tau txais kev teeb tsa raws li tus tswv ntawm ob lub tswv yim, pgworld_x thiab sch_chameleon, uas muaj cov rooj sib txawv tiag tiag thiab cov ntawv teev npe rov ua dua, raws li. Cov kev sib cav create_replica_schema yog lub luag haujlwm rau kev teeb tsa tsis siv neeg, raws li koj yuav pom hauv qab no.
postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';
CREATE ROLE
postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;
CREATE DATABASELub MySQL database tau teeb tsa nrog qee qhov kev hloov pauv los npaj nws rau kev rov ua dua raws li qhia hauv qab no. Koj yuav tsum rov pib dua lub database server rau cov kev hloov pauv kom muaj txiaj ntsig.
$> vi /etc/my.cnf
binlog_format= ROW
binlog_row_image=FULL
log-bin = mysql-bin
server-id = 1Tam sim no nws yog ib qho tseem ceeb los xyuas qhov kev sib txuas rau ob lub database servers kom tsis muaj teeb meem thaum khiav pg_chameleon cov lus txib.
Ntawm PostgreSQL node:
$> mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_xNtawm MySQL node:
$> psql -p 5433 -U usr_replica -h 192.168.56.106 db_replicaPeb tom ntej no peb pg_chameleon (chameleon) cov lus txib npaj ib puag ncig, ntxiv qhov chaw, thiab pib lub replica. Lub create_replica_schema sib cav rau pg_chameleon tsim lub neej ntawd schema (sch_chameleon) thiab ib tug replication schema (pgworld_x) nyob rau hauv lub PostgreSQL database, raws li peb twb tau tham. Cov lus sib cav add_source ntxiv cov ntaub ntawv los ntawm kev teeb tsa los ntawm kev nyeem cov ntaub ntawv teeb tsa (default.yml), thiab hauv peb cov ntaub ntawv nws yog mysql, thiab init_replica pib qhov kev teeb tsa raws li qhov tsis muaj nyob hauv cov ntaub ntawv teeb tsa.
$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --source mysql --debug
$> chameleon init_replica --config default --source mysql --debugCov zis ntawm peb cov lus txib no qhia meej tias lawv tau ua tiav. Ib qho kev sib tsoo lossis kev ua yuam kev syntax raug tshaj tawm hauv cov lus yooj yim, meej nrog cov lus qhia txog yuav kho qhov teeb meem li cas.
Thaum kawg, peb pib replication siv start_replica thiab tau txais cov lus ua tiav.
$> chameleon start_replica --config default --source mysql
output: Starting the replica process for source mysqlCov xwm txheej rov ua dua tuaj yeem nug siv qhov kev sib cav show_status, thiab qhov yuam kev tuaj yeem raug saib siv qhov kev sib cav show_errors.
Raws li peb twb tau hais lawm, txhua qhov kev ua haujlwm replication yog tswj los ntawm daemon. Txhawm rau saib lawv, peb nug cov lus txheej txheem nrog Linux ps hais kom ua, raws li qhia hauv qab no.
Replication tsis suav tias yog teeb tsa kom txog thaum peb sim nws hauv lub sijhawm, raws li qhia hauv qab no. Peb tsim ib lub rooj, ntxig ob peb cov ntaub ntawv rau hauv MySQL database, thiab hu rau sync_tables kev sib cav hauv pg_chameleon hloov tshiab daemons thiab rov ua dua lub rooj nrog cov ntaub ntawv rau PostgreSQL database.
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.Txhawm rau kom paub meej cov txiaj ntsig kev xeem, peb nug cov lus los ntawm PostgreSQL database thiab tso tawm cov kab.
$> psql -p 5433 -U usr_replica -d db_replica -c "select * from pgworld_x.t1";
n1 | n2
----+-------
1 | one
2 | twoYog tias peb tab tom ua kev tsiv teb tsaws chaw, cov lus txib hauv qab no pg_chameleon yuav yog qhov kawg ntawm nws. Cov lus txib yuav tsum tau ua tom qab peb paub tseeb tias cov kab ntawm tag nrho cov phiaj xwm tau rov ua dua, thiab qhov tshwm sim yuav yog qhov zoo tshaj plaws hauv PostgreSQL database tsis muaj kev xa mus rau lub hauv paus database lossis replication scheme (sch_chameleon).
$> chameleon stop_replica --config default --source mysql
$> chameleon detach_replica --config default --source mysql --debugYog tias xav tau, koj tuaj yeem siv cov lus txib hauv qab no kom tshem tawm cov txheej txheem qub thiab rov ua dua.
$> chameleon drop_source --config default --source mysql --debug
$> chameleon drop_replica_schema --config default --source mysql --debugQhov zoo ntawm pg_chameleon
Yooj yim teeb thiab configuration.
Yooj yim daws teeb meem thiab txheeb xyuas qhov tsis txaus ntseeg nrog cov lus tseeb yuam kev.
Cov rooj tshwj xeeb ntxiv tuaj yeem muab ntxiv rau kev rov ua dua tom qab pib yam tsis tau hloov pauv qhov seem ntawm kev teeb tsa.
Nws tuaj yeem teeb tsa ntau qhov chaw databases rau ib lub hom phiaj database, thiab qhov no yog qhov tseem ceeb heev yog tias koj muab cov ntaub ntawv los ntawm ib lossis ntau dua MySQL databases rau hauv ib qho PostgreSQL database.
Koj tsis tas yuav rov ua dua cov lus xaiv.
Disadvantages ntawm pg_chameleon
Tsuas yog txhawb nrog MySQL 5.5 thiab siab dua li qhov chaw thiab PostgreSQL 9.5 thiab siab dua li lub hom phiaj database.
Txhua lub rooj yuav tsum muaj lub ntsiab lus tseem ceeb lossis tshwj xeeb, txwv tsis pub cov ntxhuav tau pib thaum lub sijhawm init_replica tab sis tsis rov ua dua.
Ib txoj kev replication - tsuas yog los ntawm MySQL mus rau PostgreSQL. Yog li ntawd, nws tsuas yog tsim nyog rau "active-passive" Circuit Court.
Lub hauv paus tuaj yeem tsuas yog MySQL database, thiab kev txhawb nqa rau PostgreSQL database raws li qhov chaw tsuas yog sim thiab muaj kev txwv (kawm ntxiv )
Cov txiaj ntsig rau pg_chameleon
Txoj kev rov ua dua tshiab hauv pg_chameleon yog qhov zoo rau kev hloov pauv cov ntaub ntawv los ntawm MySQL rau PostgreSQL. Qhov tseem ceeb downside yog tias replication tsuas yog ib txoj hauv kev, yog li cov kws tshaj lij database tsis zoo li xav siv nws rau lwm yam uas tsis yog kev tsiv teb tsaws chaw. Tab sis qhov teeb meem ntawm ib-txoj kev replication tuaj yeem daws tau nrog lwm qhov qhib qhov cuab yeej - SymmetricDS.
Nyeem ntxiv hauv cov ntaub ntawv raug cai . Cov kab hais kom pab nrhiav tau .
Txheej txheem cej luam ntawm SymmetricDS
SymmetricDS yog cov cuab yeej qhib uas rov ua dua cov ntaub ntawv rau lwm cov ntaub ntawv sib xws: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird thiab lwm yam huab database, piv txwv li Redshift, thiab Azure, thiab lwm yam muaj nta: database thiab cov ntaub ntawv synchronization, multi-master database replication, lim synchronization, transformation thiab lwm yam. Qhov no yog Java cuab yeej thiab yuav tsum tau tso tawm tus qauv ntawm JRE lossis JDK (version 8.0 lossis siab dua). Ntawm no, cov ntaub ntawv hloov pauv rau qhov tshwm sim hauv qhov chaw khaws ntaub ntawv tuaj yeem raug kaw thiab xa mus rau lub hom phiaj tsim nyog hauv daim ntawv ntawm cov khoom siv.
SymmetricDS nta
Cov cuab yeej yog platform ywj siab, txhais tau tias ob lossis ntau qhov sib txawv databases tuaj yeem pauv cov ntaub ntawv.
Relational databases yog synchronized siv cov ntaub ntawv hloov ntaub ntawv, thaum cov ntaub ntawv system-based databases siv cov ntaub ntawv synchronization.
Kev rov ua dua ob txoj hauv kev siv Push thiab rub txoj hauv kev raws li cov txheej txheem.
Kev hloov ntaub ntawv tuaj yeem ua tau los ntawm kev ruaj ntseg thiab qis-bandwidth networks.
Tsis siv neeg rov qab thaum cov nodes rov ua haujlwm tom qab tsis ua haujlwm thiab tsis siv neeg daws teeb meem.
Huab tau tshaj thiab muaj zog txuas ntxiv APIs.
Piv Txwv:
SymmetricDS tuaj yeem teeb tsa hauv ib qho ntawm ob txoj kev xaiv:
Tus tswv (niam txiv) ntawm lub hauv paus tswj hwm cov ntaub ntawv rov ua dua ntawm ob tug qhev (me nyuam) nodes, thiab kev sib txuas lus ntawm tus me nyuam nodes tshwm sim los ntawm niam txiv.
Ib qho active node (Node 1) tuaj yeem sib txuas lus rau kev rov ua dua nrog lwm tus neeg nquag (Node 2) yam tsis muaj tus neeg nruab nrab.
Hauv ob qho kev xaiv, cov ntaub ntawv sib pauv tshwm sim siv Push thiab Pull. Hauv qhov piv txwv no peb yuav xav txog qhov kev ua haujlwm nquag-ua haujlwm. Nws yuav siv sij hawm ntev heev los piav qhia tag nrho cov architecture, yog li ua koj cov kev tshawb fawb. kom paub ntau ntxiv txog SymmetricDS ntaus ntawv.
Txhim kho SymmetricDS yog qhov yooj yim heev: rub tawm qhov qhib version ntawm zip file thiab coj nws tawm mus qhov twg koj xav tau. Cov lus hauv qab no muab cov ntaub ntawv hais txog qhov chaw teeb tsa thiab cov qauv ntawm SymmetricDS hauv qhov piv txwv no, nrog rau cov ntaub ntawv database, Linux versions, IP chaw nyob, thiab cov chaw nres nkoj rau ob lub nodes.
Tug Tswv
vwm 1
vwm 2
OS version
CentOS Linux 7.6 x86_64
CentOS Linux 7.6 x86_64
DB server version
MySQL 5.7.26
PostgreSQL 10.5
DB chaw nres nkoj
3306
5832
IP chaw nyob
192.168.1.107
192.168.1.112
SymmetricDS version
SymmetricDS 3.9
SymmetricDS 3.9
SymmetricDS installation txoj kev
/usr/local/symmetrical-server-3.9.20
/usr/local/symmetrical-server-3.9.20
SymmetricDS node npe
koom-000
khw-001
Ntawm no peb nruab SymmetricDS hauv /usr/local/symmetric-server-3.9.20, thiab ntau yam subdirectories thiab cov ntaub ntawv yuav muab khaws cia rau ntawd. Peb txaus siab nyob rau hauv cov qauv thiab cav subdirectories. Cov ntaub ntawv coj mus kuaj muaj piv txwv cov ntaub ntawv teeb tsa nrog cov khoom ntawm cov khoom, nrog rau piv txwv SQL scripts kom koj pib sai.
Hauv daim ntawv teev cov qauv peb pom peb cov ntaub ntawv teeb tsa nrog cov khoom ntawm node - lub npe qhia qhov xwm txheej ntawm cov node hauv ib lub tswv yim.
corp-000.properties
store-001.properties
store-002.propertiesSymmetricDS muaj tag nrho cov ntaub ntawv tsim nyog rau kev tsim qauv 3-node (xaiv 1), thiab tib cov ntaub ntawv tuaj yeem siv rau 2-node tsim (xaiv 2). Luam cov ntaub ntawv teeb tsa uas xav tau los ntawm cov qauv coj mus kuaj rau lub cav ntawm vm1 tus tswv. Nws hloov tawm li no:
$> 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=000Cov node hauv SymmetricDS configuration yog hu ua corp-000, thiab cov ntaub ntawv kev sib txuas yog tswj los ntawm mysql jdbc tsav tsheb, uas siv cov hlua txuas saum toj no thiab cov ntawv pov thawj nkag. Peb txuas mus rau replica_db database thiab cov ntxhuav yuav raug tsim thaum lub sij hawm tsim schema. sync.url qhia qhov twg los hu rau ntawm node rau synchronization.
Node 2 ntawm tus tswv vm2 tau teeb tsa raws li khw-001 thiab tus so yog teev nyob rau hauv cov ntaub ntawv node.properties hauv qab no. Node store-001 sau PostgreSQL database thiab pgdb_replica yog cov ntaub ntawv rov ua dua. register.url tso cai rau tus tswv vm2 hu rau tus tswv vm1 thiab tau txais cov ntsiab lus teeb tsa los ntawm nws.
$> 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=001Qhov ua tiav SymmetricDS piv txwv muaj cov kev txwv rau kev teeb tsa ob txoj kev rov ua dua ntawm ob lub chaw khaws ntaub ntawv (ob lub nodes). Cov kauj ruam hauv qab no yog ua tiav ntawm tus tswv vm1 (corp-000), uas yuav tsim ib qho piv txwv schema nrog 4 lub rooj. Tom qab ntawd khiav tsim-sym-cov lus nrog cov lus txib symadmin tsim cov ntawv teev npe uas cov cai thiab kev coj ua ntawm kev rov ua dua ntawm cov nodes yuav muab khaws cia. Thaum kawg, cov ntaub ntawv piv txwv yog loaded rau hauv cov ntxhuav.
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.sqlHauv qhov piv txwv, cov khoom thiab cov khoom_selling_price cov rooj tau txais kev teeb tsa kom rov ua dua los ntawm corp-000 mus rau khw-001, thiab cov rooj muag khoom (sale_transaction thiab sale_return_line_item) tau teeb tsa kom rov ua dua los ntawm khw-001 mus rau corp-000. Tam sim no peb tsim ib lub tswv yim hauv PostgreSQL database ntawm tus tswv vm2 (store-001) los npaj nws kom tau txais cov ntaub ntawv los ntawm corp-000.
vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> ./dbimport --engine store-001 --format XML create_sample.xmlNco ntsoov xyuas tias MySQL database ntawm vm1 muaj cov lus piv txwv thiab SymmetricDS catalog tables. Nco ntsoov tias SymmetricDS system cov rooj (ua ntej nrog sym_) tam sim no tsuas yog muaj nyob rau ntawm node corp-000 vim tias qhov ntawd yog qhov uas peb tau khiav cov lus txib tsim-sym-cov lus thiab yuav tswj hwm kev rov ua dua. Thiab nyob rau hauv lub database ntawm node store-001 yuav tsuas muaj 4 piv txwv rooj tsis muaj ntaub ntawv.
Tag nrho. Ib puag ncig yog npaj los khiav sym server txheej txheem ntawm ob lub nodes raws li qhia hauv qab no.
vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> sym 2>&1 &Cov ntaub ntawv nkag raug xa mus rau cov ntaub ntawv keeb kwm yav dhau los (symmetric.log) hauv cov ntawv teev cia hauv phau ntawv teev npe uas SymmetricDS tau teeb tsa, nrog rau cov qauv tsim tawm. Tam sim no sym server tuaj yeem pib ntawm node store-001.
vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> sym 2>&1 &Yog tias koj khiav cov txheej txheem sym server ntawm vm2 tus tswv, nws tseem yuav tsim SymmetricDS catalog cov lus hauv PostgreSQL database. Yog tias koj khiav cov txheej txheem sym neeg rau zaub mov ntawm ob lub nodes, lawv sib koom ua ke kom rov ua dua cov ntaub ntawv los ntawm corp-000 rau store-001. Yog tias tom qab ob peb lub vib nas this peb nug tag nrho 4 lub rooj ntawm ob sab, peb yuav pom tias kev rov ua dua tau ua tiav. Lossis koj tuaj yeem xa bootstrap mus rau ntawm khw-001 los ntawm corp-000 nrog cov lus txib hauv qab no.
vm1$> ./symadmin --engine corp-000 reload-node 001Lub sijhawm no, cov ntaub ntawv tshiab tau muab tso rau hauv cov lus hauv MySQL database ntawm node corp-000 (tus tswv: vm1), thiab koj tuaj yeem tshawb xyuas nws cov ntaub ntawv rov qab rau PostgreSQL database ntawm node store-001 (tus tswv: vm2). Peb pom kev ua haujlwm rub kom txav cov ntaub ntawv los ntawm corp-000 mus rau khw-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)Txhawm rau ua haujlwm Push kom txav cov ntaub ntawv los ntawm khw-001 mus rau corp-000, peb ntxig cov ntaub ntawv rau hauv lub rooj muag khoom thiab xyuas kom meej tias kev rov ua tiav tiav.
Peb pom kev teeb tsa ua tiav ntawm ob txoj kev rov ua dua ntawm cov qauv piv txwv ntawm MySQL thiab PostgreSQL databases. Txhawm rau teeb tsa replication rau cov neeg siv tshiab cov lus, ua raws li cov kauj ruam no: Peb tsim cov lus t1 piv txwv thiab teeb tsa nws cov cai replication raws li hauv qab no. Txoj kev no peb teeb tsa tsuas yog replication los ntawm corp-000 rau 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)Cov kev teeb tsa raug ceeb toom ntawm qhov kev hloov pauv, uas yog, qhov sib ntxiv ntawm lub rooj tshiab, siv cov lus txib symadmin nrog cov lus sib cav sib ceg, uas rov tsim cov txiaj ntsig los qhia cov lus txhais. xa-schema raug tua kom xa schema hloov pauv rau ntawm lub khw-001, thiab rov ua dua ntawm cov lus t1 tau teeb tsa.
vm1$> ./symadmin -e corp-000 --node=001 sync-triggers
vm1$> ./symadmin send-schema -e corp-000 --node=001 t1Cov txiaj ntsig ntawm SymmetricDS
Ib qho yooj yim installation thiab configuration, nrog rau ib tug npaj-ua txheej ntawm cov ntaub ntawv nrog tsis rau tsim ib tug peb-node los yog ob-node Circuit Court.
Hla-platform databases thiab platform kev ywj pheej, suav nrog servers, laptops thiab mobile devices.
Hloov cov ntaub ntawv rau lwm qhov chaw hauv zos, ntawm WAN lossis hauv huab.
Muaj peev xwm ua haujlwm zoo nrog ob peb lub databases lossis ntau txhiab rau kev rov ua dua tshiab.
Them version nrog GUI thiab kev txhawb nqa zoo heev.
Disadvantages ntawm SymmetricDS
Koj yuav tsum tau manually txhais cov cai thiab cov kev taw qhia ntawm replication ntawm kab hais kom ua los ntawm SQL nqe lus kom thauj cov rooj catalog, uas tuaj yeem tsis yooj yim.
Kev teeb tsa ntau lub rooj rau kev rov ua dua tuaj yeem ua rau ntxhov siab tshwj tsis yog tias koj siv cov ntawv sau los tsim SQL nqe lus uas txhais cov cai thiab kev coj ua ntawm kev rov ua dua.
Muaj ntau cov ntaub ntawv raug kaw rau hauv cov cav, thiab qee zaum koj yuav tsum ua kom huv si ntawm cov ntaub ntawv log kom nws tsis txhob siv ntau qhov chaw.
Cov txiaj ntsig rau SymmetricDS
SymmetricDS tso cai rau koj los teeb tsa ob txoj kev rov ua dua ntawm ob, peb, lossis txawm tias ntau txhiab tus rau rov ua dua thiab synchronize cov ntaub ntawv. Qhov no yog cov cuab yeej tshwj xeeb uas nws tus kheej ua tau ntau yam haujlwm, xws li tsis siv neeg cov ntaub ntawv rov qab tom qab lub sij hawm ntev ntawm downtime ntawm ib lub ntawm, ruaj ntseg thiab muaj txiaj ntsig cov ntaub ntawv pauv ntawm cov nodes ntawm HTTPS, kev tswj tsis siv neeg tsis sib haum xeeb raws li cov txheej txheem, thiab lwm yam. SymmetricDS ua haujlwm. replication ntawm ib qho databases, yog li ntawd, nws tuaj yeem siv rau ntau yam xwm txheej, suav nrog kev tsiv teb tsaws, tsiv teb tsaws, faib, lim, thiab hloov pauv cov ntaub ntawv hla lub platform.
Qhov piv txwv yog raws li tus nom los ntawm SymmetricDS. IN Piav qhia meej txog ntau lub tswv yim koom nrog hauv kev teeb tsa kev rov ua dua nrog SymmetricDS.
Tau qhov twg los: www.hab.com