Ntugharị gafere n'etiti PostgreSQL na MySQL

Aga m akọwapụta ntụgharị ntụgharị n'etiti PostgreSQL na MySQL, yana ụzọ maka ịtọlitegharịa n'etiti sava nchekwa data abụọ. Dịka, a na-akpọ ọdụ data ndị na-agbanwe agbanwe agbanwe, ọ bụkwa usoro dị mma nke isi n'otu ihe nkesa RDBMS gaa na nke ọzọ.

A na-ewere ọdụ data PostgreSQL na MySQL dị ka njikọ, mana yana mgbakwunye mgbakwunye ha na-enye ikike NoSQL. N'ebe a, anyị ga-atụle ntugharị n'etiti PostgreSQL na MySQL site na echiche DBMS mmekọrịta.

Anyị agaghị akọwa ọrụ dị n'ime dum, naanị ụkpụrụ ndị bụ isi ka ị nweta echiche nke ịhazigharị n'etiti sava nchekwa data, uru, njedebe na ikpe ojiji.

Dịka, a na-eme mmegharị n'etiti sava nchekwa data abụọ yiri ya na ọnọdụ ọnụọgụ abụọ ma ọ bụ iji ajụjụ n'etiti nna ukwu (aka onye nkwusa, nna ukwu, ma ọ bụ onye na-arụ ọrụ) na ohu (onye debanyere aha, njikere, ma ọ bụ passive). Ebumnobi nke nmegharịgharị bụ ịnye ezigbo oge nke nchekwa data nna ukwu n'akụkụ ohu. N'okwu a, a na-ebufe data site na nna ukwu gaa na ohu, ya bụ, site na-arụ ọrụ na-agafe agafe, n'ihi na a na-emeghachi nanị n'otu ntụziaka. Ma ị nwere ike ịtọ ngbanwe n'etiti ọdụ data abụọ n'akụkụ abụọ ahụ, nke mere na a na-ebufe data site na ohu gaa na nna ukwu na nhazi ọrụ na-arụ ọrụ. Ihe ndị a niile, gụnyere ntugharị cascading, ga-ekwe omume n'etiti sava nchekwa data abụọ ma ọ bụ karịa. Nhazi na-arụ ọrụ ma ọ bụ na-arụ ọrụ na-arụ ọrụ na-adabere na mkpa, nnweta ikike dị otú ahụ na nhazi mbụ ma ọ bụ iji ngwọta nhazi nke mpụga na ahịa ahịa dị adị.

Nhazi a kọwara ga-ekwe omume n'etiti sava nchekwa data dị iche iche. Enwere ike ịhazi ihe nkesa ahụ ka ọ nabata data emegharịgharịrị site na ihe nkesa nchekwa data ọzọ ma ka na-edobe foto ozugbo nke data emegharịgharịrị. MySQL na PostgreSQL na-enye ọtụtụ nhazi ndị a n'ime ụlọ ma ọ bụ site na ndọtị ndị ọzọ, gụnyere ụzọ ndekọ ọnụọgụ abụọ, mkpọchi diski, na nkwupụta- na usoro dabere na ahịrị.

Ntugharị gafere n'etiti MySQL na PostgreSQL dị mkpa maka mbugharị otu oge site na otu ihe nkesa nchekwa data gaa na nke ọzọ. Ebe nchekwa data ndị a na-eji protocol dị iche iche, yabụ na ọ gaghị ekwe omume ijikọ ha ozugbo. Iji guzobe mgbanwe data, ị nwere ike iji ngwa ọrụ mepere emepe, dịka ọmụmaatụ pg_chameleon.

Gịnị bụ pg_chameleon

pg_chameleon bụ usoro mmegharị sitere na MySQL ruo PostgreSQL na Python 3. Ọ na-eji ọba akwụkwọ mysql-replication mepere emepe, dịkwa na Python. A na-ewepụta onyonyo ahịrị site na tebụl MySQL ma chekwaa dị ka ihe JSONB na nchekwa data PostgreSQL, wee mebie ya site na ọrụ pl/pgsql wee megharịa ya na nchekwa data PostgreSQL.

Njirimara pg_chameleon

Enwere ike ịmegharị ọtụtụ atụmatụ MySQL sitere na otu ụyọkọ ahụ gaa na otu nchekwa data PostgreSQL lekwasịrị anya na nhazi otu-na-ọtụtụ.
Aha isi na ebumnuche ebumnuche enweghị ike ịbụ otu.
Enwere ike iweghachite data nmegharịgharị site na oyiri MySQL cascaded.
Ewepụghị tebụl ndị na-enweghị ike imepụtaghachi ma ọ bụ mepụta mperi.
A na-ejikwa daemons na-achịkwa ọrụ mmegharị ọ bụla.
Jikwaa site na paramita dabere na YAML na faịlụ nhazi.

Ihe nlele:

Onye ọbịa
Ogbogu Okonji
Ogbogu Okonji

Ụdị OS
CentOS Linux 7.6 x86_64
CentOS Linux 7.5 x86_64

Ụdị nkesa DB
MySQL 5.7.26
PostgreSQL 10.5

DB ọdụ ụgbọ mmiri
3306
5433

Adreesị IP
192.168.56.102
192.168.56.106

Iji malite, dozie ihe niile dị mkpa iji wụnye pg_chameleon. Ihe atụ a na-etinye Python 3.6.8, nke na-emepụta ma na-arụ ọrụ gburugburu ebe obibi.

$> 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

Mgbe ị wụnye Python3.6 nke ọma, ịkwesịrị imezu ihe ndị fọdụrụnụ, dị ka ịmepụta na ịgbalite gburugburu ebe obibi. Na mgbakwunye, a na-emelite modul pip ka ọ bụrụ ụdị kachasị ọhụrụ ma jiri ya tinye pg_chameleon. Iwu ndị dị n'okpuru kpachaara anya wụnye pg_chameleon 2.0.9, n'agbanyeghị na ụdị kachasị ọhụrụ bụ 2.0.10. Nke a dị mkpa iji zere ahụhụ ọhụrụ na ụdị emelitere.

$> python3.6 -m venv venv
$> source venv/bin/activate
(venv) $> pip install pip --upgrade
(venv) $> pip install pg_chameleon==2.0.9

Anyị na-akpọ pg_chameleon (chameleon bụ iwu) yana arụmụka set_configuration_files iji mee ka pg_chameleon mepụta akwụkwọ ndekọ aha na faịlụ nhazi.

(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

Ugbu a, anyị na-emepụta otu oyiri config-example.yml dị ka default.yml ka ọ bụrụ faịlụ nhazi nke ndabara. Enyere faịlụ nhazi ihe atụ maka ihe atụ a n'okpuru.

$> 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:

Faịlụ nhazi dị na ihe atụ a bụ ihe atụ pg_chameleon faịlụ nwere obere mgbanwe dabara na isi mmalite na gburugburu ebe ebumnuche, na n'okpuru bụ ntụle nke akụkụ dị iche iche nke faịlụ nhazi ahụ.

Na faịlụ nhazi ndabara.yml enwere ngalaba nke ntọala ụwa, ebe ị nwere ike ijikwa ntọala dịka ebe faịlụ mkpọchi, ebe ndekọ, oge nchekwa maka ndekọ, wdg na-abịa n'ihu ụdị nkwụsị nke ngalaba, ebe. usoro iwu maka ụdị ịfefe n'oge mmegharị. Ọmụmaatụ a na-adaba na ụdị iwu nbibi nke na-atụgharị obere(1) ka ọ bụrụ uru boolean. Na ngalaba na-esote, anyị ezipụta nkọwa njikọ na nchekwa data ebumnuche. N'ọnọdụ anyị, nke a bụ nchekwa data PostgreSQL, pg_conn akpọpụtara. N'akụkụ nke ikpeazụ, anyị na-egosi data isi mmalite, ya bụ, paramita njikọ nke isi iyi nchekwa data, atụmatụ nkewa n'etiti isi iyi na ọdụ data lekwasịrị anya, tebụl ndị kwesịrị ịwụpụ, oge echere, ebe nchekwa, nha ngwugwu. Rịba ama na "isi mmalite" bụ ọtụtụ, nke pụtara na anyị nwere ike itinye ọtụtụ ọdụ data isi na otu nchekwa data ezubere iche iji guzobe nhazi ọtụtụ na-otu.

Ihe atụ nchekwa data world_x nwere tebụl anọ nwere ahịrị ndị obodo MySQL na-enye dịka ọmụmaatụ. Enwere ike ibudata ya ebe a. Ihe nchekwa data nlele na-abịa dị ka ebe nchekwa tar na abịakọrọ nwere ntuziaka maka imepụta na ibubata ahịrị.

Na MySQL na PostgreSQL ọdụ data, a na-emepụta onye ọrụ pụrụ iche nwere otu aha usr_replica. Na MySQL, a na-enyekwu ikike ịgụ ya na tebụl niile emegharịgharịrị.

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;

N'akụkụ PostgreSQL, a na-emepụta nchekwa data db_replica nke ga-anabata mgbanwe na nchekwa data MySQL. A na-ahazi onye ọrụ usr_replica dị na PostgreSQL ka ọ bụrụ onye nwe schemas abụọ, pgworld_x na sch_chameleon, nke nwere tebụl ndị e ṅomiri n'ezie yana tebụl ndekọ n'otu n'otu. Arụmụka create_replica_schema bụ maka nhazi akpaaka, dịka ị ga-ahụ n'okpuru.

postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';
CREATE ROLE
postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;
CREATE DATABASE

A na-ahazi nchekwa data MySQL na ụfọdụ mgbanwe paramita iji kwado ya maka mmụgharị dịka egosiri n'okpuru. Ị ga-achọ ịmalitegharị ihe nkesa nchekwa data maka mgbanwe ndị ahụ iji mee ihe.

$> vi /etc/my.cnf
binlog_format= ROW
binlog_row_image=FULL
log-bin = mysql-bin
server-id = 1

Ugbu a ọ dị mkpa ịlele njikọ na sava nchekwa data abụọ ka ọ ghara inwe nsogbu mgbe ị na-agba ọsọ pg_chameleon iwu.

Na ọnụ PostgreSQL:

$> mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_x

Na ọnụ MySQL:

$> psql -p 5433 -U usr_replica -h 192.168.56.106 db_replica

Iwu pg_chameleon (chameleon) atọ na-esote na-akwadebe gburugburu ebe obibi, tinye isi mmalite, wee bido oyiri. Arụmụka create_replica_schema na pg_chameleon na-emepụta atụmatụ ndabara (sch_chameleon) na nhazigharị (pgworld_x) na nchekwa data PostgreSQL, dịka anyị tụlere. Arụmụka add_source na-agbakwụnye nchekwa data isi na nhazi site na ịgụ faịlụ nhazi (default.yml), na n'ọnọdụ anyị ọ bụ mysql, na init_replica na-amalite nhazi ahụ dabere na paramita dị na faịlụ nhazi.

$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --source mysql --debug
$> chameleon init_replica --config default --source mysql --debug

Nsonaazụ nke iwu atọ a na-egosi n'ụzọ doro anya na e gburu ha nke ọma. A na-akọ mkpọka ọ bụla ma ọ bụ njehie syntax ọ bụla n'ime ozi dị mfe, doro anya nwere ntụnye maka otu esi edozi nsogbu ahụ.

N'ikpeazụ, anyị na-amalite mmegharị site na iji start_replica ma nweta ozi ịga nke ọma.

$> chameleon start_replica --config default --source mysql 
output: Starting the replica process for source mysql

Enwere ike jụọ ọkwa ngbanwe site na iji arụmụka show_status, enwere ike lelee mperi site na iji arụmụka show_errors.

Nsonaazụ.

Dịka anyị kwurula, daemons na-achịkwa ọrụ mmegharị ọ bụla. Iji lelee ha, anyị na-ajụ usoro usoro na iwu Linux ps, dị ka egosiri n'okpuru.

Nsonaazụ.

A naghị ewere nrụgharị ahụ ka ahaziri ya ruo mgbe anyị nwalere ya ozugbo, dị ka egosiri n'okpuru. Anyị na-emepụta tebụl, fanye ndekọ abụọ n'ime nchekwa data MySQL, wee kpọọ arụmụka sync_tables na pg_chameleon iji melite daemons wee megharịa tebụl ahụ na ndekọ na nchekwa data 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.

Iji kwado nsonaazụ ule, anyị na-ajụ tebụl site na nchekwa data PostgreSQL wee wepụta ahịrị ndị ahụ.

$> psql -p 5433 -U usr_replica -d db_replica -c "select * from pgworld_x.t1";
 n1 |  n2
----+-------
  1 | one
  2 | two

Ọ bụrụ na anyị na-eme njem, iwu pg_chameleon ga-abụ njedebe ya. Ekwesịrị ime iwu ndị a mgbe anyị ji n'aka na emegharịla ahịrị nke tebụl ebumnuche niile, nsonaazụ ga-abụ nchekwa data PostgreSQL kwagara nke ọma na-enweghị ntụaka na nchekwa data isi mmalite ma ọ bụ atụmatụ mmeghari (sch_chameleon).

$> chameleon stop_replica --config default --source mysql 
$> chameleon detach_replica --config default --source mysql --debug

Ọ bụrụ na-ịchọrọ, ịnwere ike iji iwu ndị a ka ihichapụ nhazi mbụ na atụmatụ mmegharị.

$> chameleon drop_source --config default --source mysql --debug
$> chameleon drop_replica_schema --config default --source mysql --debug

Uru nke pg_chameleon

Nhazi na nhazi dị mfe.
Jiri ozi mperi doro anya dozie nsogbu ma chọpụta ihe adịghị mma.
Enwere ike ịgbakwunye tebụl pụrụ iche ndị ọzọ na mmegharị mgbe mmalite na-enweghị ịgbanwe nhazi ndị ọzọ.
Ọ ga-ekwe omume ịhazi ọtụtụ ọdụ data isi mmalite maka otu nchekwa data ebumnuche, nke a bara ezigbo uru ma ọ bụrụ na ị na-ejikọta data sitere na otu ma ọ bụ karịa MySQL ọdụ data n'ime otu PostgreSQL nchekwa data.
Ịkwesighi ịmegharị tebụl ndị ahọpụtara.

Ọdịmma nke pg_chameleon

Naanị akwadoro na MySQL 5.5 na n'elu dị ka isi iyi yana PostgreSQL 9.5 na n'elu dị ka nchekwa data ebumnuche.
Tebụl ọ bụla ga-enwerịrị igodo isi ma ọ bụ pụrụ iche, ma ọ bụghị ya, a na-ebido tebụl ndị ahụ n'oge usoro init_replica mana anaghị emegharị ya.
Ntugharị otu ụzọ - naanị site na MySQL ruo PostgreSQL. Ya mere, ọ dị mma maka sekit "na-arụsi ọrụ ike".
Isi mmalite nwere ike ịbụ naanị nchekwa data MySQL, yana nkwado maka nchekwa data PostgreSQL dịka isi iyi bụ naanị nnwale yana nwere oke (mụtakwuo. ebe a)

Nsonaazụ maka pg_chameleon

Usoro ntụgharị na pg_chameleon dị mma maka ịkwaga nchekwa data site na MySQL gaa PostgreSQL. Ihe dị ịrịba ama bụ na mmegharị ahụ bụ naanị otu ụzọ, ya mere ndị ọkachamara nchekwa data agaghị achọ iji ya maka ihe ọ bụla ọzọ karịa ịkwaga. Mana enwere ike idozi nsogbu nke mmegharị otu ụzọ site na ngwa ọrụ mepere emepe ọzọ - SymmetricDS.

Gụkwuo na akwụkwọ gọọmentị ebe a. Enwere ike ịchọta enyemaka ahịrị iwu ebe a.

Nchịkọta nke SymmetricDS

SymmetricDS bụ ngwá ọrụ mepere emepe nke na-emegharị nchekwa data ọ bụla na nchekwa data ọ bụla ọzọ: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird na ihe nchekwa data igwe ojii ndị ọzọ, dịka Redshift, na Azure, wdg. Atụmatụ dị: nchekwa data na mmekọrịta faịlụ, multi-master database replication, nzacha nzacha, mgbanwe na ndị ọzọ. Nke a bụ ngwa Java ma chọọ ntọhapụ ọkọlọtọ nke JRE ma ọ bụ JDK (ụdị 8.0 ma ọ bụ karịa). N'ebe a, enwere ike ịdekọ mgbanwe data na ihe na-akpalite na ebe nchekwa data ma zigara ya na ebe nchekwa data kwesịrị ekwesị n'ụdị batches.

Njirimara SymmetricDS

Ngwá ọrụ ahụ nwere onwe ya, nke pụtara na ọdụ data abụọ ma ọ bụ karịa dị iche iche nwere ike ịgbanwe data.
A na-emekọrịta ọdụ data mmekọrịta site na iji ndekọ mgbanwe data, ebe nchekwa data dabere na faịlụ na-eji mmekọrịta faịlụ.
Ntugharị ụzọ abụọ site na iji ụzọ Push na Pull dabere na usoro iwu.
Enwere ike ịnyefe data n'elu netwọk echekwabara na bandwit dị ala.
Iweghachite akpaaka mgbe ọnụ ọnụ maliteghachiri ọrụ mgbe ọdịda na mkpebi esemokwu akpaka gasịrị.
API ndọtị dakọtara na igwe ojii.

Ihe nlele:

Enwere ike ịhazi SymmetricDS n'otu ụzọ abụọ:
Ọnụ nna ukwu (nne na nna) nke na-ahazi data n'etiti ọnụ ụzọ ohu abụọ (ụmụaka), na nkwurịta okwu n'etiti ọnụ ụmụaka na-apụta nanị site na nne na nna.
Ọnụ nọ n'ọrụ (Node 1) nwere ike iji ọnụ ọzọ na-arụ ọrụ na-ekwurịta okwu maka mmegharịgharị (Node 2) na-enweghị onye etiti.

Na nhọrọ abụọ a, mgbanwe data na-eme site na iji Push na Pull. N'ihe atụ a, anyị ga-atụle nhazi nke na-arụsi ọrụ ike. Ọ ga-ewe ogologo oge iji kọwaa ihe owuwu niile, yabụ mee nyocha gị. njikwaịmụtakwu maka ngwaọrụ SymmetricDS.

Ịwụnye SymmetricDS dị nnọọ mfe: budata ụdị faịlụ zip mepere emepe site n'ebe a ma wepụta ya ebe ọ bụla ịchọrọ. Tebụlụ dị n'okpuru na-enye ozi gbasara ebe nrụnye na ụdị SymmetricDS na ihe atụ a, yana ụdị nchekwa data, ụdị Linux, adreesị IP, na ọdụ ụgbọ mmiri maka ọnụ abụọ ahụ.

Onye ọbịa
Ogbogu Okonji
Ogbogu Okonji

Ụdị OS
CentOS Linux 7.6 x86_64
CentOS Linux 7.6 x86_64

Ụdị nkesa DB
MySQL 5.7.26
PostgreSQL 10.5

DB ọdụ ụgbọ mmiri
3306
5832

Adreesị IP
192.168.1.107
192.168.1.112

Ụdị SymmetricDS
SymmetricDS 3.9
SymmetricDS 3.9

Ụzọ ntinye SymmetricDS
/usr/local/symmetric-server-3.9.20
/usr/local/symmetric-server-3.9.20

SymmetricDS aha ọnụ
corp-000
ụlọ ahịa-001

N'ebe a, anyị wụnye SymmetricDS na /usr/local/symmetric-server-3.9.20, a ga-echekwa akwụkwọ ndekọ aha na faịlụ dị iche iche n'ebe ahụ. Anyị nwere mmasị na samples na engines subdirectories. Akwụkwọ ndekọ ihe atụ nwere faịlụ nhazi ihe atụ nwere njirimara ọnụ, yana ọmụmaatụ scripts SQL ka ịmalite ịmalite ngwa ngwa.

N'ime ndekọ ihe atụ, anyị na-ahụ faịlụ nhazi atọ nwere njirimara ọnụ - aha ahụ na-egosi ọdịdị nke ọnụ na atụmatụ ụfọdụ.

corp-000.properties
store-001.properties
store-002.properties

SymmetricDS nwere faịlụ nhazi niile dị mkpa maka nhazi 3-node (nhọrọ 1), enwere ike iji otu faịlụ ahụ maka nhazi 2-node (nhọrọ 2). Detuo faịlụ nhazi achọrọ site na ndekọ ihe nlele gaa na injin na onye ọbịa vm1. Ọ tụgharịrị dị ka nke a:

$> 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

A na-akpọ ọnụ ọnụ a na nhazi SymmetricDS corp-000, na njikọ data data bụ onye ọkwọ ụgbọ ala mysql jdbc na-eji eriri njikọ dị n'elu na nzere nbanye. Anyị jikọọ na replica_db nchekwa data na a ga-emepụta tebụl n'oge schema. sync.url na-egosi ebe a ga-akpọtụrụ ọnụ maka mmekọrịta.

A na-ahazi Node 2 na vm2 onye ọbịa ka ọ bụrụ ụlọ ahịa-001 ma akọwapụtara ndị ọzọ na faịlụ node.properties dị n'okpuru. Ụlọ ahịa Node-001 na-agba ọsọ nchekwa data PostgreSQL na pgdb_replica bụ nchekwa data nnomi. registration.url na-enye ohere onye ọbịa vm2 ịkpọtụrụ onye ọbịa vm1 wee nweta nkọwa nhazi site na ya.

$> 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

Ọmụmaatụ SymmetricDS emechara nwere parampat maka ịtọliteghachi mmegharị ụzọ abụọ n'etiti sava nchekwa data abụọ (ọnụ abụọ). A na-eme usoro ndị dị n'okpuru na onye ọbịa vm1 (corp-000), nke ga-emepụta atụmatụ atụ nwere tebụl anọ. Mgbe ahụ na-agba ọsọ mepụta-sym-tables na iwu symadmin na-emepụta tebụl ndekọ ebe a ga-echekwa iwu na ntụziaka nke mmegharị n'etiti ọnụ. N'ikpeazụ, a na-etinye data nlele n'ime tebụl.

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

N'ihe atụ, a na-ahazi ihe na tebụl item_selling_price na-akpaghị aka ka ha megharịa site na corp-000 gaa na ụlọ ahịa-001, na tebụl ire ere (sale_transaction na sale_return_line_item) na-ahazi na-akpaghị aka na-emepụta site na ụlọ ahịa-001 ruo corp-000. Ugbu a, anyị na-emepụta atụmatụ na nchekwa data PostgreSQL na vm2 (store-001) kwadoro ya iji nweta data sitere na corp-000.

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> ./dbimport --engine store-001 --format XML create_sample.xml

Jide n'aka na ịlele na MySQL nchekwa data na vm1 nwere ihe atụ tebụl na SymmetricDS katalọgụ tebụl. Rịba ama na tebụl sistemụ SymmetricDS (nke prefix na sym_) dị ugbu a na node corp-000 n'ihi na ọ bụ ebe ahụ ka anyị na-agba iwu ike-sym-tables ma ga na-ejikwa ntugharị. Na na nchekwa data na ụlọ ahịa node-001 a ga-enwe naanị tebụl ihe atụ 4 na-enweghị data.

Ha niile. Gburugburu ebe a dị njikere iji usoro ihe nkesa sym na ọnụ abụọ dị ka egosiri n'okpuru.

vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> sym 2>&1 &

A na-eziga ndenye ndekọ na faịlụ ndekọ ndabere (symmetric.log) na nchekwa ndekọ dị na ndekọ ebe arụnyere SymmetricDS, yana na mmepụta ọkọlọtọ. Enwere ike ibido ihe nkesa sym na ụlọ ahịa node-001.

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> sym 2>&1 &

Ọ bụrụ na ị na-eme usoro ihe nkesa sym na vm2 ọbịa, ọ ga-emepụtakwa tebụl katalọgụ SymmetricDS na nchekwa data PostgreSQL. Ọ bụrụ na ị na-eme usoro ihe nkesa sym na ọnụ abụọ ahụ, ha na-ejikọta ibe ha iji megharịa data sitere na corp-000 gaa na ụlọ ahịa-001. Ọ bụrụ na mgbe sekọnd ole na ole gasịrị, anyị jụrụ ajụjụ tebụl anọ niile dị n'akụkụ abụọ, anyị ga-ahụ na mmegharị ahụ gara nke ọma. Ma ọ bụ ị nwere ike izipu bootstrap na ụlọ ahịa node-4 site na corp-001 site na iwu na-esonụ.

vm1$> ./symadmin --engine corp-000 reload-node 001

N'oge a, a na-etinye ndekọ ọhụrụ n'ime tebụl ihe dị na MySQL nchekwa data na node corp-000 (onye ọbịa: vm1), ma ị nwere ike ịlele nsụgharị ya na nchekwa data PostgreSQL na ụlọ ahịa node-001 (onye ọbịa: vm2). Anyị na-ahụ ọrụ Pull ịkwaga data site na corp-000 gaa na ụlọ ahịa-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)

Iji rụọ ọrụ Push ịkwaga data site na ụlọ ahịa-001 gaa na corp-000, anyị na-etinye ndekọ na tebụl sale_transaction wee chọpụta na mmegharị ahụ na-aga nke ọma.

Nsonaazụ.

Anyị na-ahụ nhazi nke ọma nke ntụgharị ụzọ abụọ nke tebụl ihe atụ n'etiti MySQL na ọdụ data PostgreSQL. Ka ịtọlitegharịa mmegharị maka tebụl onye ọrụ ọhụrụ, soro usoro ndị a: Anyị na-emepụta tebụl t1 dịka ọmụmaatụ ma hazie iwu mmegharị ya dị ka ndị a. Otu a anyị na-ahazi naanị mmegharị site na corp-000 gaa na ụlọ ahịa-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)

Mgbe ahụ, a na-eme ka nhazi ahụ mara ọkwa mgbanwe nke schema, ya bụ, mgbakwunye nke tebụl ọhụrụ, na-eji iwu symadmin na arụmụka sync-triggers, nke na-emegharị ihe ndị na-akpali akpali iji map nkọwa tebụl. A na-eme iziga-schema iji zipu mgbanwe schema na ụlọ ahịa ọnụ-001, na ahaziri mmegharị nke tebụl t1.

vm1$> ./symadmin -e corp-000 --node=001 sync-triggers    
vm1$> ./symadmin send-schema -e corp-000 --node=001 t1

Uru nke SymmetricDS

Nrụnye na nhazi dị mfe, gụnyere nhazi faịlụ dị njikere nke nwere parampat maka ịmepụta sekit ọnụ atọ ma ọ bụ abụọ.
Ebe nchekwa data na-agafe agafe na nnwere onwe ikpo okwu, gụnyere sava, laptọọpụ na ngwaọrụ mkpanaka.
Megharịa nchekwa data ọ bụla na nchekwa data ọ bụla ọzọ na mpaghara, na WAN ma ọ bụ n'igwe ojii.
Enwere ike ịrụ ọrụ kacha mma na ọdụ data di na nwunye ma ọ bụ ọtụtụ puku maka mmegharị ahụ dabara adaba.
Ụdị akwụ ụgwọ na GUI yana nkwado mara mma.

Ọdịmma nke SymmetricDS

Ịkwesịrị iji aka kọwaa iwu na ntụziaka nke mmegharị na ahịrị iwu site na nkwupụta SQL iji buo tebụl katalọgụ, nke nwere ike ọ gaghị adị mma.
Ịtọlite ​​​​ọtụtụ tebụl maka mmegharị ahụ nwere ike ịgwụ ike ọ gwụla ma ị na-eji script mepụta nkwupụta SQL nke na-akọwapụta iwu na ntụzịaka nke ntụgharị.
Enwere ọtụtụ ozi a na-edekọ na ndekọ, na mgbe ụfọdụ ị ga-edozi faịlụ ndekọ ka ọ ghara iburu ohere buru ibu.

Nsonaazụ maka SymmetricDS

SymmetricDS na-enye gị ohere ịhazi mmegharị ụzọ abụọ n'etiti abụọ, atọ, ma ọ bụ ọbụna ọtụtụ puku ọnụ iji megharịa na mekọrịta faịlụ. Nke a bụ ngwá ọrụ pụrụ iche nke na-arụ ọrụ dị iche iche n'onwe ya, dị ka mgbake data na-akpaghị aka mgbe ogologo oge nkwụsịtụ na ọnụ ọnụ, mgbanwe data echekwara na nke ọma n'etiti ọnụ site na HTTPS, njikwa esemokwu akpaka dabere na usoro iwu, wdg SymmetricDS na-arụ ọrụ. mmeghari n'etiti ọdụ data ọ bụla, ya mere, enwere ike iji ya mee ihe dị iche iche dị iche iche, gụnyere mbugharị, mbugharị, nkesa, nzacha, na ngbanwe nke data n'ofe nyiwe.

Ihe atụ dabere na onye ọrụ gọọmentị ngwa ngwa ndu nke SymmetricDS. N'ime ntuziaka onye ọrụ Na-akọwa n'ụzọ zuru ezu echiche dị iche iche dị n'ịmepụta mmegharị na SymmetricDS.

isi: www.habr.com