{"id":38146,"date":"2019-10-31T22:21:54","date_gmt":"2019-10-31T19:21:54","guid":{"rendered":"https:\/\/prohoster.info\/blog\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql\/"},"modified":"2019-10-31T22:21:54","modified_gmt":"2019-10-31T19:21:54","slug":"perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","title":{"rendered":"K\u0159\u00ed\u017eov\u00e1 replikace mezi PostgreSQL a MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"K\u0159\u00ed\u017eov\u00e1 replikace mezi PostgreSQL a MySQL\" src=\"\/wp-content\/uploads\/2019\/09\/47cf48f9024f406d625ecb6b21020c74.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Obecn\u011b se pod\u00edv\u00e1m na k\u0159\u00ed\u017eovou replikaci mezi PostgreSQL a MySQL a tak\u00e9 na metody nastaven\u00ed k\u0159\u00ed\u017eov\u00e9 replikace mezi t\u011bmito dv\u011bma datab\u00e1zov\u00fdmi servery. Obvykle se datab\u00e1ze v k\u0159\u00ed\u017eov\u00e9 replikaci pova\u017euj\u00ed za homogenn\u00ed a je to pohodln\u00fd zp\u016fsob p\u0159echodu z jednoho serveru rela\u010dn\u00ed DB na jin\u00fd.<\/p>\n<p><\/p>\n<p>Datab\u00e1ze PostgreSQL a MySQL jsou obecn\u011b pova\u017eov\u00e1ny za rela\u010dn\u00ed, ale s dal\u0161\u00edmi roz\u0161\u00ed\u0159en\u00edmi nab\u00edzej\u00ed mo\u017enosti NoSQL. Zde budeme diskutovat o replikaci mezi PostgreSQL a MySQL z pohledu rela\u010dn\u00edch DB.<\/p>\n<p><\/p>\n<p>Nebudeme popisovat celou vnit\u0159n\u00ed strukturu, pouze z\u00e1kladn\u00ed principy, abyste z\u00edskali p\u0159edstavu o nastaven\u00ed replikace mezi datab\u00e1zov\u00fdmi servery, v\u00fdhod\u00e1ch, omezen\u00edch a sc\u00e9n\u00e1\u0159\u00edch pou\u017eit\u00ed.<\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>Obvykle se replikace mezi dv\u011bma identick\u00fdmi datab\u00e1zov\u00fdmi servery prov\u00e1d\u00ed bu\u010f v bin\u00e1rn\u00edm re\u017eimu, nebo pomoc\u00ed dotaz\u016f mezi hlavn\u00edm uzlem (tak\u00e9 zn\u00e1m\u00fdm jako vydavatel, hlavn\u00ed nebo aktivn\u00ed) a pod\u0159\u00edzen\u00fdm (odb\u011bratelem, \u010dekaj\u00edc\u00edm nebo pasivn\u00edm). C\u00edlem replikace je poskytnout v re\u00e1ln\u00e9m \u010dase kopii hlavn\u00ed datab\u00e1ze na stran\u011b pod\u0159\u00edzen\u00e9ho. P\u0159itom jsou data p\u0159en\u00e1\u0161ena od hlavn\u00edho k pod\u0159\u00edzen\u00e9mu, tedy od aktivn\u00edho k pasivn\u00edmu, proto\u017ee replikace prob\u00edh\u00e1 pouze jedn\u00edm sm\u011brem. Ale je mo\u017en\u00e9 nastavit replikaci mezi dv\u011bma datab\u00e1zemi obousm\u011brn\u011b, aby data byla p\u0159en\u00e1\u0161ena od pod\u0159\u00edzen\u00e9ho k hlavn\u00edmu v konfiguraci \"aktivn\u00ed-aktivn\u00ed\". To v\u0161e, v\u010detn\u011b kask\u00e1dov\u00e9 replikace, je mo\u017en\u00e9 mezi dv\u011bma a v\u00edce identick\u00fdmi datab\u00e1zov\u00fdmi servery. Konfigurace \"aktivn\u00ed-aktivn\u00ed\" nebo \"aktivn\u00ed-pasivn\u00ed\" z\u00e1vis\u00ed na pot\u0159eb\u011b, dostupnosti takov\u00fdch mo\u017enost\u00ed v p\u016fvodn\u00ed konfiguraci nebo pou\u017eit\u00ed extern\u00edch \u0159e\u0161en\u00ed pro nastaven\u00ed a existuj\u00edc\u00edch kompromis\u016f.<\/p>\n<p><\/p>\n<p>Popisovan\u00e1 konfigurace je mo\u017en\u00e1 mezi r\u016fzn\u00fdmi datab\u00e1zov\u00fdmi servery. Server m\u016f\u017ee b\u00fdt nastaven tak, aby p\u0159ij\u00edmal replikovan\u00e1 data z jin\u00e9ho datab\u00e1zov\u00e9ho serveru, a z\u00e1rove\u0148 uchov\u00e1val sn\u00edmky replikovan\u00fdch dat v re\u00e1ln\u00e9m \u010dase. MySQL a PostgreSQL nab\u00edzej\u00ed v\u011bt\u0161inu t\u011bchto konfigurac\u00ed bu\u010f nativn\u011b, nebo pomoc\u00ed extern\u00edch roz\u0161\u00ed\u0159en\u00ed, v\u010detn\u011b metod bin\u00e1rn\u00edho protokolu, z\u00e1mk\u016f disku a metod zalo\u017een\u00fdch na oper\u00e1torech a \u0159\u00e1dc\u00edch.<\/p>\n<p><\/p>\n<p>K\u0159\u00ed\u017eov\u00e1 replikace mezi MySQL a PostgreSQL je pot\u0159ebn\u00e1 pro jednor\u00e1zovou migraci z jednoho datab\u00e1zov\u00e9ho serveru na druh\u00fd. Tyto datab\u00e1ze pou\u017e\u00edvaj\u00ed r\u016fzn\u00e9 protokoly, a proto je nelze propojit p\u0159\u00edmo. Pro usnadn\u011bn\u00ed v\u00fdm\u011bny dat lze pou\u017e\u00edt extern\u00ed open-source n\u00e1stroj, nap\u0159\u00edklad pg_chameleon.<\/p>\n<p><\/p>\n<h3 id=\"chto-takoe-pg_chameleon\">Co je pg_chameleon<\/h3>\n<p><\/p>\n<p>pg_chameleon je syst\u00e9m replikace z MySQL do PostgreSQL napsan\u00fd v Pythonu 3. Vyu\u017e\u00edv\u00e1 open-source knihovnu mysql-replication tak\u00e9 na Pythonu. Obrazce \u0159\u00e1dk\u016f jsou extrahov\u00e1ny z tabulek MySQL a ukl\u00e1d\u00e1ny jako objekty JSONB v datab\u00e1zi PostgreSQL, a pot\u00e9 jsou dek\u00f3dov\u00e1ny funkc\u00ed pl\/pgsql a reprodukov\u00e1ny v datab\u00e1zi PostgreSQL.<\/p>\n<p><\/p>\n<h3 id=\"vozmozhnosti-pg_chameleon\">Mo\u017enosti pg_chameleon<\/h3>\n<p><\/p>\n<p>N\u011bkolik sch\u00e9mat MySQL z jednoho clusteru m\u016f\u017ee b\u00fdt replikov\u00e1no do jedn\u00e9 c\u00edlov\u00e9 datab\u00e1ze PostgreSQL s konfigurac\u00ed \u201ejeden k mnoha\u201c.<br \/>\nN\u00e1zvy zdrojov\u00e9ho a c\u00edlov\u00e9ho sch\u00e9matu se nesm\u00ed shodovat.<br \/>\nData replikace mohou b\u00fdt extrahov\u00e1na z kask\u00e1dov\u00e9 repliky MySQL.<br \/>\nTabulky, kter\u00e9 nelze replikovat nebo vytv\u00e1\u0159ej\u00ed chyby, jsou vylou\u010deny.<br \/>\nKa\u017edou funkci replikace \u0159\u00edd\u00ed d\u00e9moni.<br \/>\nKontrola pomoc\u00ed parametr\u016f a konfigura\u010dn\u00edch soubor\u016f ve form\u00e1tu YAML.<\/p>\n<p><\/p>\n<h3 id=\"primer\">P\u0159\u00edklad<\/h3>\n<p><\/p>\n<p>Hostitel<br \/>\nvm1<br \/>\nvm2<\/p>\n<p><strong>Verze OS<\/strong><br \/>\nCentOS Linux 7.6 x86_64<br \/>\nCentOS Linux 7.5 x86_64<\/p>\n<p><strong>Verze datab\u00e1zov\u00e9ho serveru<\/strong><br \/>\nMySQL 5.7.26<br \/>\nPostgreSQL 10.5<\/p>\n<p><strong>Port DB<\/strong><br \/>\n3306<br \/>\n5433<\/p>\n<p><strong>IP adresa<\/strong><br \/>\n192.168.56.102<br \/>\n192.168.56.106<\/p>\n<p><\/p>\n<p>Nejprve p\u0159ipravte v\u0161echny pot\u0159ebn\u00e9 komponenty pro instalaci pg_chameleon. V tomto p\u0159\u00edkladu je nainstalov\u00e1n Python 3.6.8, kter\u00fd vytvo\u0159\u00ed virtu\u00e1ln\u00ed prost\u0159ed\u00ed a aktivuje ho.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; wget https:\/\/www.python.org\/ftp\/python\/3.6.8\/Python-3.6.8.tar.xz\n$&gt; tar -xJf Python-3.6.8.tar.xz\n$&gt; cd Python-3.6.8\n$&gt; .\/configure --enable-optimizations\n$&gt; make altinstall<\/code><\/pre>\n<p><\/p>\n<p>Po \u00fasp\u011b\u0161n\u00e9 instalaci Pythonu 3.6 je t\u0159eba splnit dal\u0161\u00ed po\u017eadavky, nap\u0159\u00edklad vytvo\u0159it a aktivovat virtu\u00e1ln\u00ed prost\u0159ed\u00ed. Krom\u011b toho se modul pip aktualizuje na nejnov\u011bj\u0161\u00ed verzi a pou\u017e\u00edv\u00e1 se k instalaci pg_chameleon. V n\u00e1sleduj\u00edc\u00edch p\u0159\u00edkazech je \u00famysln\u011b nainstalov\u00e1na verze pg_chameleon 2.0.9, i kdy\u017e posledn\u00ed verze je 2.0.10. To je pot\u0159eba, aby se p\u0159ede\u0161lo nov\u00fdm chyb\u00e1m v aktualizovan\u00e9 verzi.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; python3.6 -m venv venv\n$&gt; source venv\/bin\/activate\n(venv) $&gt; pip install pip --upgrade\n(venv) $&gt; pip install pg_chameleon==2.0.9<\/code><\/pre>\n<p><\/p>\n<p>Pot\u00e9 vol\u00e1me pg_chameleon (chameleon je p\u0159\u00edkaz) s argumentem set_configuration_files, aby se aktivoval pg_chameleon a vytvo\u0159ily se v\u00fdchoz\u00ed adres\u00e1\u0159e a konfigura\u010dn\u00ed soubory.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">(venv) $&gt; chameleon set_configuration_files\ncreating directory \/root\/.pg_chameleon\ncreating directory \/root\/.pg_chameleon\/configuration\/\ncreating directory \/root\/.pg_chameleon\/logs\/\ncreating directory \/root\/.pg_chameleon\/pid\/\ncoping configuration example in \/root\/.pg_chameleon\/configuration\/\/config-example.yml<\/code><\/pre>\n<p><\/p>\n<p>Nyn\u00ed vytv\u00e1\u0159\u00edme kopii config-example.yml jako default.yml, aby se stal v\u00fdchoz\u00edm konfigura\u010dn\u00edm souborem. Uk\u00e1zka konfigura\u010dn\u00edho souboru pro tento p\u0159\u00edklad je uvedena n\u00ed\u017ee.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; cat default.yml\n---\n#glob\u00e1ln\u00ed nastaven\u00ed\npid_dir: '~\/.pg_chameleon\/pid\/'\nlog_dir: '~\/.pg_chameleon\/logs\/'\nlog_dest: file\nlog_level: info\nlog_days_keep: 10\nrollbar_key: ''\nrollbar_env: ''\n\n# type_override umo\u017e\u0148uje u\u017eivateli p\u0159epsat v\u00fdchoz\u00ed p\u0159evod typ\u016f na jin\u00fd.\ntype_override:\n  \"tinyint(1)\":\n    override_to: boolean\n    override_tables:\n      - \"*\"\n\n#postgres  c\u00edlov\u00e9 p\u0159ipojen\u00ed\npg_conn:\n  host: \"192.168.56.106\"\n  port: \"5433\"\n  user: \"usr_replica\"\n  password: \"pass123\"\n  database: \"db_replica\"\n  charset: \"utf8\"\n\nsources:\n  mysql:\n    db_conn:\n      host: \"192.168.56.102\"\n      port: \"3306\"\n      user: \"usr_replica\"\n      password: \"pass123\"\n      charset: 'utf8'\n      connect_timeout: 10\n    schema_mappings:\n      world_x: pgworld_x\n    limit_tables:\n#      - delphis_mediterranea.foo\n    skip_tables:\n#      - delphis_mediterranea.bar\n    grant_select_to:\n      - usr_readonly\n    lock_timeout: \"120s\"\n    my_server_id: 100\n    replica_batch_size: 10000\n    replay_max_rows: 10000\n    batch_retention: '1 day'\n    copy_max_memory: \"300M\"\n    copy_mode: 'file'\n    out_dir: \/tmp\n    sleep_loop: 1\n    on_error_replay: continue\n    on_error_read: continue\n    auto_maintenance: \"disabled\"\n    gtid_enable: No\n    type: mysql\n    skip_events:\n      insert:\n        - delphis_mediterranea.foo #p\u0159eskakuje vkl\u00e1d\u00e1n\u00ed do tabulky delphis_mediterranea.foo\n      delete:\n        - delphis_mediterranea #p\u0159eskakuje maz\u00e1n\u00ed ve sch\u00e9matu delphis_mediterranea\n      update:<\/code><\/pre>\n<p><\/p>\n<p>Konfigura\u010dn\u00ed soubor v tomto p\u0159\u00edkladu je vzorek souboru s pg_chameleon s drobn\u00fdmi zm\u011bnami v souladu se zdrojov\u00fdm a c\u00edlov\u00fdm prost\u0159ed\u00edm, a n\u00ed\u017ee je shrnut\u00ed r\u016fzn\u00fdch sekc\u00ed konfigura\u010dn\u00edho souboru.<\/p>\n<p><\/p>\n<p>V konfigura\u010dn\u00edm souboru default.yml je sekce glob\u00e1ln\u00edch parametr\u016f (global settings), kde lze \u0159\u00eddit nastaven\u00ed, jako je um\u00edst\u011bn\u00ed souboru z\u00e1mku, um\u00edst\u011bn\u00ed log\u016f, doba uchov\u00e1n\u00ed log\u016f atd. D\u00e1le n\u00e1sleduje sekce p\u0159episov\u00e1n\u00ed typ\u016f (type override), kde je uvedena sada pravidel pro p\u0159episov\u00e1n\u00ed typ\u016f b\u011bhem replikace. V p\u0159\u00edkladu se pou\u017e\u00edv\u00e1 pravidlo p\u0159episov\u00e1n\u00ed typu, kter\u00e9 p\u0159ev\u00e1d\u00ed tinyint(1) na logickou hodnotu. V dal\u0161\u00ed sekci uv\u00e1d\u00edme detaily p\u0159ipojen\u00ed k c\u00edlov\u00e9 datab\u00e1zi. V na\u0161em p\u0159\u00edpad\u011b je to datab\u00e1ze PostgreSQL, ozna\u010den\u00e1 jako pg_conn. V posledn\u00ed sekci uv\u00e1d\u00edme \u00fadaje o zdroji, tj. parametry p\u0159ipojen\u00ed zdrojov\u00e9 datab\u00e1ze, sch\u00e9ma mapov\u00e1n\u00ed zdrojov\u00e9 a c\u00edlov\u00e9 datab\u00e1ze, tabulky, kter\u00e9 je t\u0159eba p\u0159esko\u010dit, \u010dasov\u00e9 limity, pam\u011b\u0165, velikost v\u00e1rky. V\u0161imn\u011bte si, \u017ee \u201esources\u201c je uvedeno v mno\u017en\u00e9m \u010d\u00edsle, tj. m\u016f\u017eeme p\u0159idat n\u011bkolik zdrojov\u00fdch datab\u00e1z\u00ed pro jednu c\u00edlovou, abychom nastavili konfiguraci \u201emnoh\u00e9ho k jednomu\u201c.<\/p>\n<p><\/p>\n<p>Datab\u00e1ze world_x v uk\u00e1zce obsahuje 4 tabulky se \u0159\u00e1dky, kter\u00e9 komunit\u011b MySQL slou\u017e\u00ed jako p\u0159\u00edklad. Lze ji na\u010d\u00edst <noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/index-other.html\">zde<\/a><\/noindex>. Uk\u00e1zkov\u00e1 datab\u00e1ze je dod\u00e1v\u00e1na ve form\u00e1tu tar a jako komprimovan\u00fd archiv s pokyny k vytvo\u0159en\u00ed a importu \u0159\u00e1dk\u016f.<\/p>\n<p><\/p>\n<p>Ve datab\u00e1z\u00edch MySQL a PostgreSQL se vytv\u00e1\u0159\u00ed speci\u00e1ln\u00ed u\u017eivatel se stejn\u00fdm n\u00e1zvem usr_replica. V MySQL mu jsou ud\u011blena dal\u0161\u00ed pr\u00e1va pro \u010dten\u00ed v\u0161ech replikovan\u00fdch tabulek.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; CREATE USER usr_replica ;\nmysql&gt; SET PASSWORD FOR usr_replica='pass123';\nmysql&gt; GRANT ALL ON world_x.* TO 'usr_replica';\nmysql&gt; GRANT RELOAD ON *.* to 'usr_replica';\nmysql&gt; GRANT REPLICATION CLIENT ON *.* to 'usr_replica';\nmysql&gt; GRANT REPLICATION SLAVE ON *.* to 'usr_replica';\nmysql&gt; FLUSH PRIVILEGES;<\/code><\/pre>\n<p><\/p>\n<p>Na stran\u011b PostgreSQL se vytv\u00e1\u0159\u00ed datab\u00e1ze db_replica, kter\u00e1 bude p\u0159ij\u00edmat zm\u011bny z datab\u00e1ze MySQL. U\u017eivatel usr_replica v PostgreSQL je automaticky nastaven jako vlastn\u00edk dvou sch\u00e9mat pgworld_x a sch_chameleon, kter\u00e9 obsahuj\u00ed skute\u010dn\u00e9 replikovan\u00e9 tabulky a tabulky pro katalogizaci replikace. O automatickou konfiguraci se postar\u00e1 argument create_replica_schema, jak uvid\u00edte n\u00ed\u017ee.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';\nCREATE ROLE\npostgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;\nCREATE DATABASE<\/code><\/pre>\n<p><\/p>\n<p>Datab\u00e1ze MySQL se nastavuje se zm\u011bnami n\u011bkter\u00fdch parametr\u016f, aby byla p\u0159ipravena na replikaci, jak je uk\u00e1z\u00e1no n\u00ed\u017ee. Bude t\u0159eba restartovat datab\u00e1zov\u00fd server, aby zm\u011bny nabyly \u00fa\u010dinnosti.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; vi \/etc\/my.cnf\nbinlog_format= ROW\nbinlog_row_image=FULL\nlog-bin = mysql-bin\nserver-id = 1<\/code><\/pre>\n<p><\/p>\n<p>Je nyn\u00ed d\u016fle\u017eit\u00e9 zkontrolovat p\u0159ipojen\u00ed k ob\u011bma datab\u00e1zov\u00fdm server\u016fm, aby p\u0159i prov\u00e1d\u011bn\u00ed p\u0159\u00edkaz\u016f pg_chameleon nevznikly probl\u00e9my.<\/p>\n<p><\/p>\n<p>Na uzlu PostgreSQL:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_x<\/code><\/pre>\n<p><\/p>\n<p>Na uzlu MySQL :<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; psql -p 5433 -U usr_replica -h 192.168.56.106 db_replica<\/code><\/pre>\n<p><\/p>\n<p>N\u00e1sleduj\u00edc\u00ed t\u0159i p\u0159\u00edkazy pg_chameleon (chameleon) p\u0159ipravuj\u00ed prost\u0159ed\u00ed, p\u0159id\u00e1vaj\u00ed zdroj a inicializuj\u00ed replikaci. Argument create_replica_schema v pg_chameleon vytv\u00e1\u0159\u00ed v\u00fdchoz\u00ed sch\u00e9ma (sch_chameleon) a sch\u00e9ma replikace (pgworld_x) v datab\u00e1zi PostgreSQL, jak jsme ji\u017e zm\u00ednili. Argument add_source p\u0159id\u00e1v\u00e1 zdrojovou datab\u00e1zi do konfigurace, \u010dten\u00edm konfigura\u010dn\u00edho souboru (default.yml), a v na\u0161em p\u0159\u00edpad\u011b je to mysql, a init_replica inicializuje konfiguraci na z\u00e1klad\u011b parametr\u016f v konfigura\u010dn\u00edm souboru.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon create_replica_schema --debug\n$&gt; chameleon add_source --config default --source mysql --debug\n$&gt; chameleon init_replica --config default --source mysql --debug<\/code><\/pre>\n<p><\/p>\n<p>V\u00fdstupy t\u011bchto t\u0159\u00ed p\u0159\u00edkaz\u016f zjevn\u011b ukazuj\u00ed na jejich \u00fasp\u011b\u0161n\u00e9 proveden\u00ed. V\u0161echny chyby nebo syntaktick\u00e9 probl\u00e9my jsou uvedeny v jednoduch\u00fdch a srozumiteln\u00fdch zpr\u00e1v\u00e1ch s tipy na opravu probl\u00e9m\u016f.<\/p>\n<p><\/p>\n<p>Nakonec spust\u00edme replikaci pomoc\u00ed start_replica a obdr\u017e\u00edme zpr\u00e1vu o \u00fasp\u011b\u0161n\u00e9m proveden\u00ed.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon start_replica --config default --source mysql \nv\u00fdstup: Spou\u0161t\u00ed se proces replikace pro zdroj mysql<\/code><\/pre>\n<p><\/p>\n<p>Stav replikace lze dotazovat pomoc\u00ed argumentu show_status, a chyby zkontrolovat pomoc\u00ed argumentu show_errors.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/iYMdAf0b\">Result.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Jak ji\u017e bylo zm\u00edn\u011bno, ka\u017edou funkc\u00ed replikace \u0159\u00edd\u00ed d\u00e9moni. Abychom je mohli zobrazit, dot\u00e1z\u00e1me se na tabulku proces\u016f pomoc\u00ed p\u0159\u00edkazu Linux ps, jak je uvedeno n\u00ed\u017ee.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/59GfdvHA\">Result.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Replikace se nepova\u017euje za nastavenou, dokud ji neprov\u011b\u0159\u00edme v re\u00e1ln\u00e9m \u010dase, jak je uk\u00e1z\u00e1no n\u00ed\u017ee. Vytvo\u0159\u00edme tabulku, vlo\u017e\u00edme p\u00e1r z\u00e1znam\u016f do datab\u00e1ze MySQL a zavol\u00e1me argument sync_tables v pg_chameleon, abychom aktualizovali d\u00e9mony a replikovali tabulku se z\u00e1znamy do datab\u00e1ze PostgreSQL.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; create table t1 (n1 int primary key, n2 varchar(10));\nDotaz OK, 0 \u0159\u00e1dk\u016f ovlivn\u011bno (0.01 sek)\nmysql&gt; insert into t1 values (1,'one');\nDotaz OK, 1 \u0159\u00e1dek ovlivn\u011bn (0.00 sek)\nmysql&gt; insert into t1 values (2,'two');\nDotaz OK, 1 \u0159\u00e1dek ovlivn\u011bn (0.00 sek)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon sync_tables --tables world_x.t1 --config default --source mysql\nProces synchronizace tabulky pro zdroj mysql byl spu\u0161t\u011bn.<\/code><\/pre>\n<p><\/p>\n<p>Abychom potvrdili v\u00fdsledky testu, dotazujeme se na tabulku v datab\u00e1zi PostgreSQL a zobrazujeme \u0159\u00e1dky.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; psql -p 5433 -U usr_replica -d db_replica -c \"select * from pgworld_x.t1\";\n n1 |  n2\n----+-------\n  1 | one\n  2 | two<\/code><\/pre>\n<p><\/p>\n<p>Pokud prov\u00e1d\u00edme migraci, n\u00e1sleduj\u00edc\u00ed p\u0159\u00edkazy pg_chameleon budou jej\u00edm ukon\u010den\u00edm. P\u0159\u00edkazy je t\u0159eba prov\u00e9st pot\u00e9, co se ujist\u00edme, \u017ee \u0159\u00e1dky v\u0161ech c\u00edlov\u00fdch tabulek byly replikov\u00e1ny, a v\u00fdsledkem bude pe\u010dliv\u011b p\u0159enesen\u00e1 datab\u00e1ze PostgreSQL bez odkaz\u016f na p\u016fvodn\u00ed datab\u00e1zi nebo sch\u00e9ma replikace (sch_chameleon).<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon stop_replica --config default --source mysql \n$&gt; chameleon detach_replica --config default --source mysql --debug<\/code><\/pre>\n<p><\/p>\n<p>Na \u017e\u00e1dost lze n\u00e1sleduj\u00edc\u00edmi p\u0159\u00edkazy odstranit p\u016fvodn\u00ed konfiguraci a sch\u00e9ma replikace.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon drop_source --config default --source mysql --debug\n$&gt; chameleon drop_replica_schema --config default --source mysql --debug<\/code><\/pre>\n<p><\/p>\n<h3 id=\"preimuschestva-pg_chameleon\">V\u00fdhody pg_chameleon<\/h3>\n<p><\/p>\n<p>Jednoduch\u00e9 nastaven\u00ed a konfigurace.<br \/>\nSnadn\u00e9 odstra\u0148ov\u00e1n\u00ed probl\u00e9m\u016f a odhalov\u00e1n\u00ed anom\u00e1li\u00ed s jasn\u00fdmi chybov\u00fdmi zpr\u00e1vami.<br \/>\nDo replikace lze po inicializaci p\u0159idat dal\u0161\u00ed speci\u00e1ln\u00ed tabulky, ani\u017e by se m\u011bnila zbytek konfigurace.<br \/>\nLze nastavit n\u011bkolik zdrojov\u00fdch datab\u00e1z\u00ed pro jednu c\u00edlovou, co\u017e je velmi u\u017eite\u010dn\u00e9, pokud slu\u010dujete data z jedn\u00e9 nebo v\u00edce datab\u00e1z\u00ed MySQL do jedn\u00e9 datab\u00e1ze PostgreSQL.<br \/>\nVybran\u00e9 tabulky nen\u00ed nutn\u00e9 replikovat.<\/p>\n<p><\/p>\n<h3 id=\"nedostatki-pg_chameleon\">Nev\u00fdhody pg_chameleon<\/h3>\n<p><\/p>\n<p>Podporov\u00e1na je pouze MySQL 5.5 a nov\u011bj\u0161\u00ed jako zdroj a PostgreSQL 9.5 a nov\u011bj\u0161\u00ed jako c\u00edlov\u00e1 datab\u00e1ze.<br \/>\nKa\u017ed\u00e1 tabulka mus\u00ed m\u00edt prim\u00e1rn\u00ed nebo unik\u00e1tn\u00ed kl\u00ed\u010d, jinak budou tabulky inicializov\u00e1ny v procesu init_replica, ale nebudou replikov\u00e1ny.<br \/>\nJednosm\u011brn\u00e1 replikace \u2014 pouze z MySQL do PostgreSQL. Proto je vhodn\u00e1 pouze pro sch\u00e9ma \u201eaktivn\u00ed-pasivn\u00ed\u201c.<br \/>\nZdrojem m\u016f\u017ee b\u00fdt pouze datab\u00e1ze MySQL, a podpora datab\u00e1ze PostgreSQL jako zdroje je pouze experiment\u00e1ln\u00ed a s omezen\u00edmi (v\u00edce informac\u00ed zde <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/configuration_file.html#postgresql-source-type-experimental\">zde<\/a><\/noindex>)<\/p>\n<p><\/p>\n<h3 id=\"itogi-po-pg_chameleon\">Z\u00e1v\u011bry o pg_chameleon<\/h3>\n<p><\/p>\n<p>Metoda replikace v pg_chameleon je skv\u011bl\u00e1 pro migraci datab\u00e1ze z MySQL do PostgreSQL. Podstatnou nev\u00fdhodou je to, \u017ee replikace je pouze jednosm\u011brn\u00e1, proto se odborn\u00edci na datab\u00e1ze pravd\u011bpodobn\u011b nebudou cht\u00edt pou\u017e\u00edvat k n\u011b\u010demu jin\u00e9mu ne\u017e migraci. Ale probl\u00e9m jednosm\u011brn\u00e9 replikace lze vy\u0159e\u0161it dal\u0161\u00edm open-source n\u00e1strojem \u2014 SymmetricDS.<\/p>\n<p><\/p>\n<p>Podrobnosti najdete v ofici\u00e1ln\u00ed dokumentaci <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/\">zde<\/a><\/noindex>. N\u00e1pov\u011bdu k p\u0159\u00edkazov\u00e9mu \u0159\u00e1dku najdete <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/usage.html#https:\/\/pgchameleon.org\/documents\/usage.html\">zde<\/a><\/noindex>.<\/p>\n<p><\/p>\n<h3 id=\"obzor-symmetricds\">P\u0159ehled SymmetricDS<\/h3>\n<p><\/p>\n<p>SymmetricDS je open-source n\u00e1stroj, kter\u00fd replikujte jakoukoli datab\u00e1zi do jak\u00e9koli jin\u00e9 b\u011b\u017en\u00e9 datab\u00e1ze: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird a dal\u0161\u00ed cloudov\u00e9 instance DB, jako je Redshift a Azure atd. K dispozici: synchronizace datab\u00e1z\u00ed a soubor\u016f, replikace n\u011bkolika hlavn\u00edch datab\u00e1z\u00ed, filtrovan\u00e1 synchronizace, transformace a dal\u0161\u00ed. Je to n\u00e1stroj v Jav\u011b, vy\u017eaduj\u00edc\u00ed standardn\u00ed vyd\u00e1n\u00ed JRE nebo JDK (verze 8.0 a vy\u0161\u0161\u00ed). Zde lze zapisovat zm\u011bny dat pomoc\u00ed spou\u0161t\u011b\u010d\u016f v zdrojov\u00e9 datab\u00e1zi a sm\u011brovat je do odpov\u00eddaj\u00edc\u00ed c\u00edlov\u00e9 datab\u00e1ze ve form\u011b bal\u00ed\u010dk\u016f.<\/p>\n<p><\/p>\n<h3 id=\"vozmozhnosti-symmetricds\">Funkce SymmetricDS<\/h3>\n<p><\/p>\n<p>N\u00e1stroj je nez\u00e1visl\u00fd na platform\u011b, co\u017e znamen\u00e1, \u017ee dv\u011b nebo v\u00edce r\u016fzn\u00fdch datab\u00e1z\u00ed mohou m\u011bnit data.<br \/>\nRelational databases are synchronized using data change recording, whereas file system-based databases use file synchronization.<br \/>\nOboustrann\u00e1 replikace pomoc\u00ed metod Push a Pull na z\u00e1klad\u011b pravidel.<br \/>\nP\u0159en\u00e1\u0161en\u00ed dat je mo\u017en\u00e9 prost\u0159ednictv\u00edm zabezpe\u010den\u00fdch s\u00edt\u00ed a s\u00edt\u00ed s n\u00edzkou \u0161\u00ed\u0159kou p\u00e1sma.<br \/>\nAutomatick\u00e9 obnoven\u00ed p\u0159i znovuobnoven\u00ed provozu uzl\u016f po selh\u00e1n\u00ed a automatick\u00e9 \u0159e\u0161en\u00ed konflikt\u016f.<br \/>\nKompatibilita s cloudem a efektivn\u00ed rozhran\u00ed API pro roz\u0161\u00ed\u0159en\u00ed.<\/p>\n<p><\/p>\n<h3 id=\"primer-1\">P\u0159\u00edklad<\/h3>\n<p><\/p>\n<p>SymmetricDS lze nastavit jedn\u00edm ze dvou zp\u016fsob\u016f:<br \/>\nHlavn\u00ed (rodi\u010dovsk\u00fd) uzel, kter\u00fd centralizovan\u011b koordinuje replikaci dat mezi dv\u011bma pod\u0159\u00edzen\u00fdmi uzly, a v\u00fdm\u011bna dat mezi pod\u0159\u00edzen\u00fdmi uzly prob\u00edh\u00e1 pouze prost\u0159ednictv\u00edm rodi\u010dovsk\u00e9ho uzlu.<br \/>\nAktivn\u00ed uzel (uzel 1) m\u016f\u017ee vym\u011b\u0148ovat data pro replikaci s jin\u00fdm aktivn\u00edm uzlem (uzel 2) bez prost\u0159edn\u00edka.<\/p>\n<p><\/p>\n<p>V obou variant\u00e1ch doch\u00e1z\u00ed k v\u00fdm\u011bn\u011b dat pomoc\u00ed Push a Pull. V tomto p\u0159\u00edkladu si uk\u00e1\u017eeme konfiguraci \"aktivn\u00ed-aktivn\u00ed\". Popsat celou architekturu by trvalo p\u0159\u00edli\u0161 dlouho, tak se pod\u00edvejte <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.10\/html\/user-guide.html#_architecture\">pr\u016fvodce<\/a><\/noindex>, abyste se dozv\u011bd\u011bli v\u00edce o za\u0159\u00edzen\u00ed SymmetricDS.<\/p>\n<p><\/p>\n<p>Instalace SymmetricDS je velmi jednoduch\u00e1: st\u00e1hn\u011bte open-source verzi zip souboru <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/download\">odsud<\/a><\/noindex> a rozbalte ji kamkoliv chcete. V n\u00e1sleduj\u00edc\u00ed tabulce se nach\u00e1z\u00ed informace o m\u00edst\u011b instalace a verzi SymmetricDS v tomto p\u0159\u00edkladu, stejn\u011b jako verze datab\u00e1z\u00ed, verze Linuxu, IP adresy a porty pro oba uzly.<\/p>\n<p><\/p>\n<p>Hostitel<br \/>\nvm1<br \/>\nvm2<\/p>\n<p><strong>Verze OS<\/strong><br \/>\nCentOS Linux 7.6 x86_64<br \/>\nCentOS Linux 7.6 x86_64<\/p>\n<p><strong>Verze datab\u00e1zov\u00e9ho serveru<\/strong><br \/>\nMySQL 5.7.26<br \/>\nPostgreSQL 10.5<\/p>\n<p><strong>Port DB<\/strong><br \/>\n3306<br \/>\n5832<\/p>\n<p><strong>IP adresa<\/strong><br \/>\n192.168.1.107<br \/>\n192.168.1.112<\/p>\n<p><strong>Verze SymmetricDS<\/strong><br \/>\nSymmetricDS 3.9<br \/>\nSymmetricDS 3.9<\/p>\n<p><strong>Cesta instalace SymmetricDS<\/strong><br \/>\n\/usr\/local\/symmetric-server-3.9.20<br \/>\n\/usr\/local\/symmetric-server-3.9.20<\/p>\n<p><strong>N\u00e1zev uzlu SymmetricDS<\/strong><br \/>\ncorp-000<br \/>\nstore-001<\/p>\n<p><\/p>\n<p>Zde instalujeme SymmetricDS do \/usr\/local\/symmetric-server-3.9.20, kde budou ulo\u017eeny r\u016fzn\u00e9 pod\u0159azen\u00e9 adres\u00e1\u0159e a soubory. Zaj\u00edm\u00e1me se o pod\u0159azen\u00e9 adres\u00e1\u0159e samples a engines. V adres\u00e1\u0159i samples se nach\u00e1zej\u00ed p\u0159\u00edklady konfigura\u010dn\u00edch soubor\u016f se vlastnostmi uzlu, stejn\u011b jako p\u0159\u00edklady SQL skript\u016f pro rychl\u00fd start demonstrace.<\/p>\n<p><\/p>\n<p>V adres\u00e1\u0159i samples vid\u00edme t\u0159i konfigura\u010dn\u00ed soubory s vlastnostmi uzlu \u2014 n\u00e1zev ukazuje charakter uzlu v dan\u00e9m sch\u00e9matu.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">corp-000.properties\nstore-001.properties\nstore-002.properties<\/code><\/pre>\n<p><\/p>\n<p>V SymmetricDS jsou v\u0161echny pot\u0159ebn\u00e9 konfigura\u010dn\u00ed soubory pro z\u00e1kladn\u00ed sch\u00e9ma ze 3 uzl\u016f (varianta 1), a tyto stejn\u00e9 soubory lze pou\u017e\u00edt i pro sch\u00e9ma ze 2 uzl\u016f (varianta 2). Zkop\u00edrujeme pot\u0159ebn\u00fd konfigura\u010dn\u00ed soubor z adres\u00e1\u0159e samples do engines na hostiteli vm1. Vypad\u00e1 to takto:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; cat engines\/corp-000.properties\nengine.name=corp-000\ndb.driver=com.mysql.jdbc.Driver\ndb.url=jdbc:mysql:\/\/192.168.1.107:3306\/replica_db?autoReconnect=true&amp;useSSL=false\ndb.user=root\ndb.password=admin123\nregistration.url=\nsync.url=http:\/\/192.168.1.107:31415\/sync\/corp-000\ngroup.id=corp\nexternal.id=000<\/code><\/pre>\n<p><\/p>\n<p>Toto uzel v konfiguraci SymmetricDS se naz\u00fdv\u00e1 corp-000, a p\u0159ipojen\u00ed k datab\u00e1zi se zpracov\u00e1v\u00e1 ovlada\u010dem mysql jdbc, kter\u00fd vyu\u017e\u00edv\u00e1 v\u00fd\u0161e uveden\u00fd p\u0159ipojovac\u00ed \u0159et\u011bzec a p\u0159ihla\u0161ovac\u00ed \u00fadaje. P\u0159ipojujeme se k datab\u00e1zi replica_db, a b\u011bhem vytv\u00e1\u0159en\u00ed sch\u00e9matu budou vytvo\u0159eny tabulky. sync.url ukazuje m\u00edsto spojen\u00ed s uzlem pro synchronizaci.<\/p>\n<p><\/p>\n<p>Uzel 2 na hostiteli vm2 se nastavuje jako store-001, a zbytek je uveden v souboru node.properties, kter\u00fd je uveden n\u00ed\u017ee. Uzel store-001 prov\u00e1d\u00ed datab\u00e1zi PostgreSQL, a pgdb_replica je datab\u00e1z\u00ed pro replikaci. registration.url umo\u017e\u0148uje hostiteli vm2 p\u0159ipojit se k hostiteli vm1 a z\u00edskat od n\u011bj detaily konfigurace.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; cat engines\/store-001.properties\nengine.name=store-001\ndb.driver=org.postgresql.Driver\ndb.url=jdbc:postgresql:\/\/192.168.1.112:5832\/pgdb_replica\ndb.user=postgres\ndb.password=admin123\nregistration.url=http:\/\/192.168.1.107:31415\/sync\/corp-000\ngroup.id=store\nexternal.id=001<\/code><\/pre>\n<p><\/p>\n<p>Hotov\u00fd p\u0159\u00edklad SymmetricDS obsahuje parametry pro nastaven\u00ed obousm\u011brn\u00e9 replikace mezi dv\u011bma servery datab\u00e1z\u00ed (dv\u011bma uzly). N\u00e1sleduj\u00edc\u00ed kroky se prov\u00e1d\u011bj\u00ed na hostiteli vm1 (corp-000), kter\u00fd vytvo\u0159\u00ed p\u0159\u00edklad sch\u00e9matu se 4 tabulkami. Pot\u00e9 proveden\u00ed p\u0159\u00edkazu create-sym-tables pomoc\u00ed symadmin vytvo\u0159\u00ed tabulky katalogu, kde budou ulo\u017eena pravidla a sm\u011br replikace mezi uzly. Nakonec se do tabulek na\u010dtou p\u0159\u00edkladov\u00e1 data.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm1$&gt; .\/dbimport --engine corp-000 --format XML create_sample.xml\nvm1$&gt; .\/symadmin --engine corp-000 create-sym-tables\nvm1$&gt; .\/dbimport --engine corp-000 insert_sample.sql<\/code><\/pre>\n<p><\/p>\n<p>V p\u0159\u00edkladu jsou tabulky item a item_selling_price automaticky nastaveny pro replikaci z corp-000 do store-001, a tabulky sale (sale_transaction a sale_return_line_item) jsou automaticky nastaveny pro replikaci z store-001 do corp-000. Nyn\u00ed vytv\u00e1\u0159\u00edme sch\u00e9ma v datab\u00e1zi PostgreSQL na hostiteli vm2 (store-001), abychom ji p\u0159ipravili na p\u0159\u00edjem dat od corp-000.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm2$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm2$&gt; .\/dbimport --engine store-001 --format XML create_sample.xml<\/code><\/pre>\n<p><\/p>\n<p>Nezapome\u0148te zkontrolovat, \u017ee v datab\u00e1zi MySQL na vm1 existuj\u00ed p\u0159\u00edklady tabulek a tabulky katalog\u016f SymmetricDS. V\u0161imn\u011bte si, \u017ee syst\u00e9mov\u00e9 tabulky SymmetricDS (s prefixem sym_) jsou nyn\u00ed dostupn\u00e9 pouze na uzlu corp-000, proto\u017ee tam jsme provedli p\u0159\u00edkaz create-sym-tables a budeme spravovat replikaci. A tak\u00e9, v datab\u00e1zi na uzlu store-001 bude celkem 4 tabulky p\u0159\u00edkladu bez dat.<\/p>\n<p><\/p>\n<p>V\u0161echno. Prost\u0159ed\u00ed je p\u0159ipraveno k spu\u0161t\u011bn\u00ed serverov\u00fdch proces\u016f sym na obou uzlech, jak je uvedeno n\u00ed\u017ee.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm1$&gt; sym 2&gt;&amp;1 &amp;<\/code><\/pre>\n<p><\/p>\n<p>Z\u00e1znamy log\u016f jsou odes\u00edl\u00e1ny do souboru pozad\u00ed logu (symmetric.log) ve slo\u017ece log\u016f v adres\u00e1\u0159i, kde je nainstalov\u00e1n SymmetricDS, a tak\u00e9 do standardn\u00edho v\u00fdstupu. Server sym lze nyn\u00ed spustit na uzlu store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm2$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm2$&gt; sym 2&gt;&amp;1 &amp;<\/code><\/pre>\n<p><\/p>\n<p>Pokud spust\u00edme serverov\u00fd proces sym na hostiteli vm2, vytvo\u0159\u00ed tabulky katalogu SymmetricDS i v datab\u00e1zi PostgreSQL. Pokud spust\u00edme serverov\u00fd proces sym na obou uzlech, koordinuj\u00ed se navz\u00e1jem, aby replikovaly data z corp-000 na store-001. Pokud po n\u011bkolika sekund\u00e1ch po\u017e\u00e1d\u00e1me o v\u0161echny 4 tabulky na obou stran\u00e1ch, uvid\u00edme, \u017ee replikace prob\u011bhla \u00fasp\u011b\u0161n\u011b. Nebo m\u016f\u017eeme poslat po\u010d\u00e1te\u010dn\u00ed na\u010dten\u00ed na uzel store-001 z corp-000 n\u00e1sleduj\u00edc\u00edm p\u0159\u00edkazem.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; .\/symadmin --engine corp-000 reload-node 001<\/code><\/pre>\n<p><\/p>\n<p>V t\u00e9to f\u00e1zi se do tabulky item v datab\u00e1zi MySQL na uzlu corp-000 (host: vm1) vlo\u017e\u00ed nov\u00fd z\u00e1znam a m\u016f\u017eeme zkontrolovat jeho replikaci do datab\u00e1ze PostgreSQL na uzlu store-001. Vid\u00edme operaci Pull k p\u0159esunu dat z corp-000 do store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into item values ('22000002','Jelly Bean');\nDotaz OK, 1 \u0159\u00e1dek ovlivn\u011bn (0.00 s)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">vm2$&gt; psql -p 5832 -U postgres pgdb_replica -c \"select * from item\"\n item_id  |   name\n----------+-----------\n 11000001 | Yummy Gum\n 22000002 | Jelly Bean\n(2 \u0159\u00e1dky)<\/code><\/pre>\n<p><\/p>\n<p>Abychom provedli operaci Push k p\u0159esunu dat ze store-001 do corp-000, vlo\u017e\u00edme z\u00e1znam do tabulky sale_transaction a zkontrolujeme, \u017ee replikace prob\u011bhla.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/B23cJ4sP\">Result.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Vid\u00edme \u00fasp\u011b\u0161n\u00e9 nastaven\u00ed obousm\u011brn\u00e9 replikace tabulek p\u0159\u00edkladu mezi datab\u00e1zemi MySQL a PostgreSQL. Abychom nastavili replikaci pro nov\u00e9 u\u017eivatelsk\u00e9 tabulky, provedeme n\u00e1sleduj\u00edc\u00ed kroky. Vytvo\u0159\u00edme tabulku t1 pro p\u0159\u00edklad a nastav\u00edme pravidla jej\u00ed replikace n\u00e1sledovn\u011b. Tak nastavujeme pouze replikaci z corp-000 do store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; create table t1 (no integer);\nDotaz OK, 0 \u0159\u00e1dk\u016f ovlivn\u011bno (0.01 s)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into sym_channel (channel_id,create_time,last_update_time) \nvalues ('t1',current_timestamp,current_timestamp);\nDotaz OK, 1 \u0159\u00e1dek ovlivn\u011bn (0.01 s)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into sym_trigger (trigger_id, source_table_name,channel_id,\nlast_update_time, create_time) values ('t1', 't1', 't1', current_timestamp,\ncurrent_timestamp);\nDotaz OK, 1 \u0159\u00e1dek ovlivn\u011bn (0.01 s)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into sym_trigger_router (trigger_id, router_id,\nInitial_load_order, create_time,last_update_time) values ('t1',\n'corp-2-store-1', 1, current_timestamp,current_timestamp);\nDotaz OK, 1 \u0159\u00e1dek ovlivn\u011bn (0.01 s)<\/code><\/pre>\n<p><\/p>\n<p>Konfigurace dost\u00e1v\u00e1 ozn\u00e1men\u00ed o zm\u011bn\u011b sch\u00e9matu, tedy p\u0159id\u00e1n\u00ed nov\u00e9 tabulky, pomoc\u00ed p\u0159\u00edkazu symadmin s argumentem sync-triggers, kter\u00fd znovu vytv\u00e1\u0159\u00ed spou\u0161t\u011b\u010de pro porovn\u00e1n\u00ed definic tabulek. Prob\u00edh\u00e1 send-schema pro odesl\u00e1n\u00ed zm\u011bn sch\u00e9matu na uzel store-001 a replikace tabulky t1 je nastavena.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; .\/symadmin -e corp-000 --node=001 sync-triggers\nvm1$&gt; .\/symadmin send-schema -e corp-000 --node=001 t1<\/code><\/pre>\n<p><\/p>\n<h3 id=\"preimuschestva-symmetricds\">V\u00fdhody SymmetricDS<\/h3>\n<p><\/p>\n<p>Jednoduch\u00e1 instalace a konfigurace, v\u010detn\u011b hotov\u00e9 sady soubor\u016f s parametry pro vytvo\u0159en\u00ed sch\u00e9matu se t\u0159emi nebo dv\u011bma uzly.<br \/>\nMezi platformami kompatibiln\u00ed datab\u00e1ze a nez\u00e1vislost na platform\u00e1ch, v\u010detn\u011b server\u016f, notebook\u016f a mobiln\u00edch za\u0159\u00edzen\u00ed.<br \/>\nReplikace jak\u00e9koli datab\u00e1ze do jak\u00e9koli jin\u00e9 datab\u00e1ze lok\u00e1ln\u011b, v WAN nebo v cloudu.<br \/>\nSchopnost optim\u00e1ln\u011b pracovat s p\u00e1rem datab\u00e1z\u00ed nebo tis\u00edci pro pohodlnou replikaci.<br \/>\nPlacen\u00e1 verze s grafick\u00fdm rozhran\u00edm a vynikaj\u00edc\u00ed podporou.<\/p>\n<p><\/p>\n<h3 id=\"nedostatki-symmetricds\">Nev\u00fdhody SymmetricDS<\/h3>\n<p><\/p>\n<p>Je nutn\u00e9 ru\u010dn\u011b ur\u010dovat v p\u0159\u00edkazov\u00e9m \u0159\u00e1dku pravidla a sm\u011br replikace prost\u0159ednictv\u00edm SQL oper\u00e1tor\u016f pro na\u010d\u00edt\u00e1n\u00ed tabulek katalog\u016f, co\u017e m\u016f\u017ee b\u00fdt nepohodln\u00e9.<br \/>\nNastavov\u00e1n\u00ed mnoha tabulek pro replikaci m\u016f\u017ee b\u00fdt \u00fanavn\u00e9, pokud se nepou\u017eij\u00ed skripty pro vytvo\u0159en\u00ed SQL oper\u00e1tor\u016f, kter\u00e9 ur\u010duj\u00ed pravidla a sm\u011br replikace.<br \/>\nDo log\u016f se zaznamen\u00e1v\u00e1 p\u0159\u00edli\u0161 mnoho informac\u00ed, a n\u011bkdy je pot\u0159eba uspo\u0159\u00e1dat soubor logu, aby nezab\u00edral p\u0159\u00edli\u0161 mnoho m\u00edsta.<\/p>\n<p><\/p>\n<h3 id=\"itogi-po-symmetricds\">Shrnut\u00ed k SymmetricDS<\/h3>\n<p><\/p>\n<p>SymmetricDS umo\u017e\u0148uje nastavovat obousm\u011brnou replikaci mezi dv\u011bma, t\u0159emi a dokonce i n\u011bkolika tis\u00edci uzly pro prov\u00e1d\u011bn\u00ed replikace a synchronizaci soubor\u016f. Je to jedine\u010dn\u00fd n\u00e1stroj, kter\u00fd s\u00e1m vykon\u00e1v\u00e1 mnoho \u00fakol\u016f, jako je automatick\u00e9 obnoven\u00ed dat po dlouh\u00e9 v\u00fdpadku na uzlu, zabezpe\u010den\u00e1 a efektivn\u00ed v\u00fdm\u011bna dat mezi uzly p\u0159es HTTPS, automatick\u00e9 \u0159\u00edzen\u00ed konflikt\u016f na z\u00e1klad\u011b souboru pravidel atd. SymmetricDS prov\u00e1d\u00ed replikaci mezi jak\u00fdmikoli datab\u00e1zemi, tak\u017ee jej lze pou\u017e\u00edt pro r\u016fzn\u00e9 sc\u00e9n\u00e1\u0159e, v\u010detn\u011b migrace, p\u0159echodu na novou verzi, distribuce, filtrov\u00e1n\u00ed a transformace dat na r\u016fzn\u00fdch platform\u00e1ch.<\/p>\n<p><\/p>\n<p>P\u0159\u00edklad byl vytvo\u0159en na z\u00e1klad\u011b ofici\u00e1ln\u00edho <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.9\/html\/tutorials.html\">stru\u010dn\u00e9ho pr\u016fvodce<\/a><\/noindex> k SymmetricDS. V <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.9\/html\/user-guide.html\">u\u017eivatelsk\u00e9 p\u0159\u00edru\u010dce<\/a><\/noindex> podrobn\u011b vysv\u011btleny r\u016fzn\u00e9 pojmy souvisej\u00edc\u00ed s nastaven\u00edm replikace pomoc\u00ed SymmetricDS.<\/p>\n<p>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/southbridge\/blog\/467313\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e \u043f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u0438 \u043c\u0435\u0436\u0434\u0443 PostgreSQL \u0438 MySQL, \u0430 \u0435\u0449\u0435 \u043e \u043c\u0435\u0442\u043e\u0434\u0430\u0445 \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0438 \u043f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u0438 \u043c\u0435\u0436\u0434\u0443 \u044d\u0442\u0438\u043c\u0438 \u0434\u0432\u0443\u043c\u044f \u0441\u0435\u0440\u0432\u0435\u0440\u0430\u043c\u0438 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445. \u041e\u0431\u044b\u0447\u043d\u043e \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u043f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u0438 \u043d\u0430\u0437\u044b\u0432\u0430\u044e\u0442\u0441\u044f \u043e\u0434\u043d\u043e\u0440\u043e\u0434\u043d\u044b\u043c\u0438, \u0438 \u044d\u0442\u043e \u0443\u0434\u043e\u0431\u043d\u044b\u0439 \u043c\u0435\u0442\u043e\u0434 \u043f\u0435\u0440\u0435\u0445\u043e\u0434\u0430 \u0441 \u043e\u0434\u043d\u043e\u0433\u043e \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u043e\u0439 \u0421\u0423\u0411\u0414 \u043d\u0430 \u0434\u0440\u0443\u0433\u043e\u0439. \u0411\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 PostgreSQL \u0438 MySQL \u043f\u0440\u0438\u043d\u044f\u0442\u043e \u0441\u0447\u0438\u0442\u0430\u0442\u044c \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u043c\u0438, \u043d\u043e \u0441 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":28635,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-38146","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.3 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u0430\u044f \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u043c\u0435\u0436\u0434\u0443 PostgreSQL \u0438 MySQL | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T19:21:54+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:21:54+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47K\u0159\u00ed\u017eov\u00e1 replikace mezi PostgreSQL a MySQL | ProHoster","description":"Ve stru\u010dnosti v\u00e1m pov\u00edm o.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u0430\u044f \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u043c\u0435\u0436\u0434\u0443 PostgreSQL \u0438 MySQL | ProHoster","og:description":"\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e.","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:21:54+00:00","article:modified_time":"2019-10-31T19:21:54+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"38146","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-23 20:38:22","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:59:41","updated":"2026-01-23 20:38:22","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/38146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=38146"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/38146\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/28635"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=38146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=38146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=38146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}