{"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\/sk\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","title":{"rendered":"Kr\u00ed\u017eov\u00e1 replik\u00e1cia medzi PostgreSQL a MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Kr\u00ed\u017eov\u00e1 replik\u00e1cia medzi PostgreSQL a MySQL\" src=\"\/wp-content\/uploads\/2019\/09\/47cf48f9024f406d625ecb6b21020c74.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V stru\u010dnosti sazn\u00e1me o kr\u00ed\u017eovej replik\u00e1cii medzi PostgreSQL a MySQL, ako aj o met\u00f3dach nastavenia kr\u00ed\u017eovej replik\u00e1cie medzi t\u00fdmito dvoma datab\u00e1zov\u00fdmi servermi. Zvy\u010dajne sa datab\u00e1zy v kr\u00ed\u017eovej replik\u00e1cii naz\u00fdvaj\u00fa homog\u00e9nnymi a je to pohodln\u00fd sp\u00f4sob prechodu z jedn\u00e9ho servera rela\u010dnej DBMS na in\u00fd.<\/p>\n<p><\/p>\n<p>Datab\u00e1zy PostgreSQL a MySQL sa pova\u017euj\u00fa za rela\u010dn\u00e9, ale s dodato\u010dn\u00fdmi roz\u0161\u00edreniami pon\u00fakaj\u00fa mo\u017enosti NoSQL. Tu budeme diskutova\u0165 o replik\u00e1cii medzi PostgreSQL a MySQL z poh\u013eadu rela\u010dn\u00fdch DBMS.<\/p>\n<p><\/p>\n<p>Nebudeme popisova\u0165 cel\u00fa vn\u00fatorn\u00fa kuchy\u0148u, len z\u00e1kladn\u00e9 princ\u00edpy, aby ste z\u00edskali predstavu o nastaven\u00ed replik\u00e1cie medzi datab\u00e1zov\u00fdmi servermi, v\u00fdhod\u00e1ch, obmedzeniach a scen\u00e1roch pou\u017eitia.<\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>Replik\u00e1cia medzi dvoma identick\u00fdmi datab\u00e1zov\u00fdmi servermi sa zvy\u010dajne vykon\u00e1va bu\u010f v bin\u00e1rnom re\u017eime, alebo pomocou dopytov medzi ved\u00facim uzlom (rovnako ako publik\u00e1tor, hlavn\u00fd alebo akt\u00edvny) a nasledovn\u00edkom (predplatite\u013e, \u010daka\u0165 alebo pas\u00edvny). Cie\u013eom replik\u00e1cie je poskytn\u00fa\u0165 v re\u00e1lnom \u010dase k\u00f3piu hlavnej datab\u00e1zy na strane nasledovn\u00edka. \u00dadaje sa pritom pren\u00e1\u0161aj\u00fa z ved\u00faceho k nasledovn\u00edkovi, teda z akt\u00edvneho na pas\u00edvny, preto\u017ee replik\u00e1cia sa vykon\u00e1va len jedn\u00fdm smerom. Je v\u0161ak mo\u017en\u00e9 nastavi\u0165 replik\u00e1ciu medzi dvoma datab\u00e1zami obojstranne, aby sa \u00fadaje pren\u00e1\u0161ali od nasledovn\u00edka k ved\u00facemu v konfigur\u00e1cii \u201eakt\u00edvny-akt\u00edvny\u201c. To v\u0161etko, vr\u00e1tane kask\u00e1dovej replik\u00e1cie, je mo\u017en\u00e9 medzi dvoma alebo viacer\u00fdmi identick\u00fdmi datab\u00e1zov\u00fdmi servermi. Konfigur\u00e1cia \u201eakt\u00edvny-akt\u00edvny\u201c alebo \u201eakt\u00edvny-pas\u00edvny\u201c z\u00e1vis\u00ed od potreby, dostupnosti tak\u00fdchto mo\u017enost\u00ed v p\u00f4vodnej konfigur\u00e1cii alebo pou\u017eitia extern\u00fdch rie\u0161en\u00ed na nastavenie a existuj\u00facich kompromisov.<\/p>\n<p><\/p>\n<p>Op\u00edsan\u00e1 konfigur\u00e1cia je mo\u017en\u00e1 medzi r\u00f4znymi datab\u00e1zov\u00fdmi servermi. Server m\u00f4\u017ee by\u0165 nastaven\u00fd na prij\u00edmanie replikovan\u00fdch \u00fadajov z in\u00e9ho datab\u00e1zov\u00e9ho servera a pritom udr\u017eiava\u0165 sn\u00edmky replikovan\u00fdch \u00fadajov v re\u00e1lnom \u010dase. MySQL a PostgreSQL pon\u00fakaj\u00fa v\u00e4\u010d\u0161inu t\u00fdchto konfigur\u00e1ci\u00ed svojimi silami alebo s pomocou extern\u00fdch roz\u0161\u00edren\u00ed, vr\u00e1tane met\u00f3d bin\u00e1rneho denn\u00edka, blokovania disku a met\u00f3d zalo\u017een\u00fdch na oper\u00e1toroch a riadkoch.<\/p>\n<p><\/p>\n<p>Kr\u00ed\u017eov\u00e1 replik\u00e1cia medzi MySQL a PostgreSQL je potrebn\u00e1 na jednorazov\u00fa migr\u00e1ciu z jedn\u00e9ho datab\u00e1zov\u00e9ho servera na druh\u00fd. Tieto datab\u00e1zy pou\u017e\u00edvaj\u00fa r\u00f4zne protokoly, tak\u017ee ich nemo\u017eno priamo prepoji\u0165. Na zavedenie v\u00fdmeny \u00fadajov je mo\u017en\u00e9 pou\u017ei\u0165 extern\u00fd open-source n\u00e1stroj, ako napr\u00edklad pg_chameleon.<\/p>\n<p><\/p>\n<h3 id=\"chto-takoe-pg_chameleon\">\u010co je pg_chameleon<\/h3>\n<p><\/p>\n<p>pg_chameleon je replika\u010dn\u00fd syst\u00e9m z MySQL do PostgreSQL nap\u00edsan\u00fd v Pythone 3. Vyu\u017e\u00edva open-source kni\u017enicu mysql-replication, taktie\u017e v Pythone. Z\u00e1znamy s\u00fa extrahovan\u00e9 z tabuliek MySQL a ukladan\u00e9 ako objekty JSONB v datab\u00e1ze PostgreSQL, a potom s\u00fa de\u0161ifrovan\u00e9 funkciou pl\/pgsql a reprodukovan\u00e9 v datab\u00e1ze PostgreSQL.<\/p>\n<p><\/p>\n<h3 id=\"vozmozhnosti-pg_chameleon\">Mo\u017enosti pg_chameleon<\/h3>\n<p><\/p>\n<p>Nieko\u013eko sch\u00e9m MySQL z jedn\u00e9ho klastra m\u00f4\u017ee by\u0165 replikovan\u00fdch do jednej cie\u013eovej datab\u00e1zy PostgreSQL s konfigur\u00e1ciou \"jeden ku mnoh\u00fdm\"<br \/>\nN\u00e1zvy zdrojovej a cie\u013eovej sch\u00e9my sa nem\u00f4\u017eu zhodova\u0165.<br \/>\n\u00dadaje replik\u00e1cie m\u00f4\u017eu by\u0165 extrahovan\u00e9 z kask\u00e1dovej repliky MySQL.<br \/>\nTabu\u013eky, ktor\u00e9 nem\u00f4\u017eu by\u0165 replikovan\u00e9 alebo sp\u00f4sobuj\u00fa chyby, s\u00fa vyl\u00fa\u010den\u00e9.<br \/>\nKa\u017ed\u00fa funkciu replik\u00e1cie riadia d\u00e9moni.<br \/>\nKontrola pomocou parametrov a konfigura\u010dn\u00fdch s\u00faborov vo form\u00e1te YAML.<\/p>\n<p><\/p>\n<h3 id=\"primer\">Pr\u00edklad<\/h3>\n<p><\/p>\n<p>Hostite\u013e<br \/>\nvm1<br \/>\nvm2<\/p>\n<p><strong>Verzia OS<\/strong><br \/>\nCentOS Linux 7.6 x86_64<br \/>\nCentOS Linux 7.5 x86_64<\/p>\n<p><strong>Verzia servera DB<\/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>Najprv si pripravte v\u0161etky potrebn\u00e9 komponenty na in\u0161tal\u00e1ciu pg_chameleon. V tomto pr\u00edklade je nain\u0161talovan\u00fd Python 3.6.8, ktor\u00fd vytv\u00e1ra virtu\u00e1lne prostredie 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 \u00faspe\u0161nej in\u0161tal\u00e1cii Python3.6 je potrebn\u00e9 splni\u0165 \u010fal\u0161ie po\u017eiadavky, ako je vytvorenie a aktiv\u00e1cia virtu\u00e1lneho prostredia. Okrem toho sa modul pip aktualizuje na najnov\u0161iu verziu a pou\u017e\u00edva sa na in\u0161tal\u00e1ciu pg_chameleon. V ni\u017e\u0161ie uveden\u00fdch pr\u00edkazoch sa \u00famyselne in\u0161taluje pg_chameleon 2.0.9, hoci najnov\u0161ia verzia je 2.0.10. Toto je potrebn\u00e9 na vyhnutie sa nov\u00fdm chyb\u00e1m vo verzii po aktualiz\u00e1cii.<\/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>Potom vol\u00e1me pg_chameleon (chameleon je pr\u00edkaz) so z argumentom set_configuration_files, aby sme zapli pg_chameleon a vytvorili predvolen\u00e9 adres\u00e1re a konfigura\u010dn\u00e9 s\u00fabory.<\/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\/\ncopying configuration example in \/root\/.pg_chameleon\/configuration\/\/config-example.yml<\/code><\/pre>\n<p><\/p>\n<p>Teraz vytv\u00e1rame k\u00f3piu config-example.yml ako default.yml, aby sa stal predvolen\u00fdm konfigura\u010dn\u00fdm s\u00faborom. Uk\u00e1\u017eka konfigura\u010dn\u00e9ho s\u00faboru pre tento pr\u00edklad je uveden\u00e1 ni\u017e\u0161ie.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; cat default.yml\n---\n#glob\u00e1lne nastavenia\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 pou\u017e\u00edvate\u013eovi predefinova\u0165 predvolen\u00fa konverziu typu na in\u00fd.\ntype_override:\n  \"tinyint(1)\":\n    override_to: boolean\n    override_tables:\n      - \"*\"\n\n#postgres cie\u013eov\u00e9 pripojenie\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 de\u0148'\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 #presk\u00e1\u010de vkladanie do tabu\u013eky delphis_mediterranea.foo\n      delete:\n        - delphis_mediterranea #presk\u00e1\u010de mazanie v sch\u00e9me delphis_mediterranea\n      update:<\/code><\/pre>\n<p><\/p>\n<p>Konfigura\u010dn\u00fd s\u00fabor v tomto pr\u00edklade je vzorom s\u00faboru s pg_chameleon s drobn\u00fdmi zmenami v s\u00falade so zdrojov\u00fdm a cie\u013eov\u00fdm prostred\u00edm, a ni\u017e\u0161ie sa uv\u00e1dzaj\u00fa r\u00f4zne oddiely konfigura\u010dn\u00e9ho s\u00faboru.<\/p>\n<p><\/p>\n<p>V konfigura\u010dnom s\u00fabore default.yml je sekcia glob\u00e1lnych parametrov (global settings), kde m\u00f4\u017eete spravova\u0165 nastavenia, ako s\u00fa umiestnenie s\u00faboru z\u00e1mku, umiestnenie logov, doba uchov\u00e1vania logov a pod. N\u00e1sledne nasleduje sekcia predefinovania typov (type override), kde je uveden\u00e1 sada pravidiel na predefinovanie typov po\u010das replik\u00e1cie. V predvolenom pr\u00edpade sa pou\u017e\u00edva pravidlo predefinovania typu, ktor\u00e9 konvertuje tinyint(1) na booleovsk\u00fa hodnotu. V nasleduj\u00facej sekcii uv\u00e1dzame podrobnosti o pripojen\u00ed k cie\u013eovej datab\u00e1ze. V na\u0161om pr\u00edpade je to datab\u00e1za PostgreSQL, ozna\u010den\u00e1 ako pg_conn. V poslednej sekcii uv\u00e1dzame \u00fadaje o zdroji, t.j. parametre pripojenia k zdrojovej datab\u00e1ze, sch\u00e9mu mapovania medzi zdrojovou a cie\u013eovou datab\u00e1zou, tabu\u013eky, ktor\u00e9 sa maj\u00fa presko\u010di\u0165, \u010dasov\u00fd limit, pam\u00e4\u0165, ve\u013ekos\u0165 d\u00e1vky. V\u0161imnite si, \u017ee \"sources\" je uveden\u00e9 v mno\u017enom \u010d\u00edsle, \u010do znamen\u00e1, \u017ee m\u00f4\u017eeme prida\u0165 nieko\u013eko zdrojov\u00fdch datab\u00e1z pre jednu cie\u013eov\u00fa datab\u00e1zu, aby sme nastavili konfigur\u00e1ciu \"mnoh\u00ed na jedn\u00e9ho\".<\/p>\n<p><\/p>\n<p>Datab\u00e1za world_x v pr\u00edklade obsahuje 4 tabu\u013eky so z\u00e1znamami, ktor\u00e9 komunita MySQL pon\u00faka ako vzor. M\u00f4\u017eete ju na\u010d\u00edta\u0165. <noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/index-other.html\">tu<\/a><\/noindex>. Pr\u00edklad datab\u00e1zy je dod\u00e1van\u00fd vo forme tar a komprimovan\u00e9ho arch\u00edvu s pokynmi na vytvorenie a import z\u00e1znamov.<\/p>\n<p><\/p>\n<p>V datab\u00e1zach MySQL a PostgreSQL sa vytv\u00e1ra \u0161peci\u00e1lny pou\u017e\u00edvate\u013e s rovnak\u00fdm menom usr_replica. V MySQL mu s\u00fa udelen\u00e9 \u010fal\u0161ie pr\u00e1va na \u010d\u00edtanie v\u0161etk\u00fdch replikovan\u00fdch tabuliek.<\/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 strane PostgreSQL sa vytv\u00e1ra datab\u00e1za db_replica, ktor\u00e1 bude prij\u00edma\u0165 zmeny z datab\u00e1zy MySQL. Pou\u017e\u00edvate\u013e usr_replica v PostgreSQL sa automaticky nastavuje ako vlastn\u00edk dvoch sch\u00e9m pgworld_x a sch_chameleon, ktor\u00e9 obsahuj\u00fa faktick\u00e9 replikovan\u00e9 tabu\u013eky a tabu\u013eky s katal\u00f3gmi replik\u00e1cie. O automatick\u00fa konfigur\u00e1ciu sa star\u00e1 argument create_replica_schema, ako uvid\u00edte ni\u017e\u0161ie.<\/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\u00e1za MySQL sa konfiguruje so zmenami niektor\u00fdch parametrov, aby sa pripravila na replik\u00e1ciu, ako je zn\u00e1zornen\u00e9 ni\u017e\u0161ie. Bude potrebn\u00e9 re\u0161tartova\u0165 datab\u00e1zov\u00fd server, aby sa zmeny prejavili.<\/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 teraz d\u00f4le\u017eit\u00e9 skontrolova\u0165 pripojenie k obom datab\u00e1zov\u00fdm serverom, aby sa pri vykon\u00e1van\u00ed pr\u00edkazov pg_chameleon nevyskytli probl\u00e9my.<\/p>\n<p><\/p>\n<p>Na uzle 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 uzle 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>Nasleduj\u00face tri pr\u00edkazy pg_chameleon (chameleon) pripravuj\u00fa prostredie, prid\u00e1vaj\u00fa zdroj a inicializuj\u00fa replik\u00e1ciu. Argument create_replica_schema v pg_chameleon vytv\u00e1ra predvolen\u00fa sch\u00e9mu (sch_chameleon) a sch\u00e9mu replik\u00e1cie (pgworld_x) v datab\u00e1ze PostgreSQL, ako sme u\u017e uviedli. Argument add_source prid\u00e1va zdrojov\u00fa datab\u00e1zu do konfigur\u00e1cie, \u010d\u00edtan\u00edm konfigura\u010dn\u00e9ho s\u00faboru (default.yml), a v tomto pr\u00edpade ide o mysql, a init_replica inicializuje konfigur\u00e1ciu na z\u00e1klade parametrov v konfigura\u010dnom s\u00fabore.<\/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\u00fdchto troch pr\u00edkazov jasne nazna\u010duj\u00fa ich \u00faspe\u0161n\u00e9 vykonanie. V\u0161etky chyby alebo syntaxov\u00e9 chyby s\u00fa uveden\u00e9 v jednoduch\u00fdch a zrozumite\u013en\u00fdch spr\u00e1vach s n\u00e1vrhmi na opravu probl\u00e9mov.<\/p>\n<p><\/p>\n<p>Nakoniec spust\u00edme replik\u00e1ciu pomocou start_replica a obdr\u017e\u00edme spr\u00e1vu o \u00faspe\u0161nom vykonan\u00ed.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon start_replica --config default --source mysql \nv\u00fdstup: Sp\u00fa\u0161\u0165am proces replik\u00e1cie pre zdroj mysql<\/code><\/pre>\n<p><\/p>\n<p>Stav replik\u00e1cie je mo\u017en\u00e9 skontrolova\u0165 pomocou argumentu show_status a preh\u013ead ch\u00fdb pomocou argumentu show_errors.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/iYMdAf0b\">V\u00fdsledok.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Ako sme u\u017e spomenuli, ka\u017edou funkciou replik\u00e1cie riadia d\u00e9m\u00f3ny. Aby sme ich videli, po\u017eiadame o tabu\u013eku procesov pr\u00edkazom Linux ps, ako je uveden\u00e9 ni\u017e\u0161ie.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/59GfdvHA\">V\u00fdsledok.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Replik\u00e1cia sa nepova\u017euje za nastaven\u00fa, pok\u00fdm ju neotestujeme v re\u00e1lnom \u010dase, ako je uveden\u00e9 ni\u017e\u0161ie. Vytvor\u00edme tabu\u013eku, vlo\u017e\u00edme nieko\u013eko z\u00e1znamov do datab\u00e1zy MySQL a vyvol\u00e1me argument sync_tables v pg_chameleon, aby sme aktualizovali d\u00e9m\u00f3ny a replikovali tabu\u013eku so z\u00e1znamami do datab\u00e1zy PostgreSQL.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; create table t1 (n1 int primary key, n2 varchar(10));\nDotaz bol \u00faspe\u0161n\u00fd, 0 riadkov ovplyvnen\u00fdch (0.01 sec)\nmysql&gt; insert into t1 values (1,'one');\nDotaz bol \u00faspe\u0161n\u00fd, 1 riadok ovplyvnen\u00fd (0.00 sec)\nmysql&gt; insert into t1 values (2,'two');\nDotaz bol \u00faspe\u0161n\u00fd, 1 riadok ovplyvnen\u00fd (0.00 sec)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon sync_tables --tables world_x.t1 --config default --source mysql\nProces synchroniz\u00e1cie tabuliek pre zdroj mysql bol spusten\u00fd.<\/code><\/pre>\n<p><\/p>\n<p>Aby sme potvrdili v\u00fdsledky testu, po\u017eiadame o tabu\u013eku z datab\u00e1zy PostgreSQL a zobraz\u00edme riadky.<\/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>Ak vykon\u00e1vame migr\u00e1ciu, nasleduj\u00face pr\u00edkazy pg_chameleon ju ukon\u010dia. Pr\u00edkazy je potrebn\u00e9 vykona\u0165 po tom, \u010do sa presved\u010d\u00edme, \u017ee v\u0161etky riadky cie\u013eov\u00fdch tabuliek boli replikovan\u00e9, a v\u00fdsledkom bude prec\u00edzne prenesen\u00e1 datab\u00e1za PostgreSQL bez odkazov na p\u00f4vodn\u00fa datab\u00e1zu alebo sch\u00e9mu replik\u00e1cie (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 \u017eelanie je mo\u017en\u00e9 nasleduj\u00facimi pr\u00edkazmi odstr\u00e1ni\u0165 p\u00f4vodn\u00fa konfigur\u00e1ciu a sch\u00e9mu replik\u00e1cie.<\/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 nastavenie a konfigur\u00e1cia.<br \/>\nPohodln\u00e9 odstra\u0148ovanie probl\u00e9mov a odha\u013eovanie anom\u00e1li\u00ed s jasn\u00fdmi chybov\u00fdmi spr\u00e1vami.<br \/>\nDo replik\u00e1cie je mo\u017en\u00e9 prida\u0165 \u010fal\u0161ie \u0161peci\u00e1lne tabu\u013eky po inicializ\u00e1cii, bez zmeny zvy\u0161ku konfigur\u00e1cie.<br \/>\nJe mo\u017en\u00e9 nastavi\u0165 nieko\u013eko zdrojov\u00fdch datab\u00e1z pre jednu cie\u013eov\u00fa, \u010do je ve\u013emi pohodln\u00e9, ak kombinujete \u00fadaje z jednej alebo viacer\u00fdch datab\u00e1z MySQL do jednej datab\u00e1zy PostgreSQL.<br \/>\nNie je potrebn\u00e9 replikova\u0165 vybran\u00e9 tabu\u013eky.<\/p>\n<p><\/p>\n<h3 id=\"nedostatki-pg_chameleon\">Nev\u00fdhody pg_chameleon<\/h3>\n<p><\/p>\n<p>Podporuje sa iba s MySQL 5.5 a vy\u0161\u0161\u00edm ako zdrojom a PostgreSQL 9.5 a vy\u0161\u0161\u00edm ako cie\u013eovou datab\u00e1zou.<br \/>\nKa\u017ed\u00e1 tabu\u013ea mus\u00ed ma\u0165 prim\u00e1rny alebo unik\u00e1tny k\u013e\u00fa\u010d, inak sa tabu\u013eky inicializuj\u00fa po\u010das procesu init_replica, ale nereplikuj\u00fa.<br \/>\nJednosmern\u00e1 replik\u00e1cia \u2014 len z MySQL do PostgreSQL. Preto sa hod\u00ed iba pre sch\u00e9mu \"akt\u00edvny-pas\u00edvny\".<br \/>\nZdrojem m\u00f4\u017ee by\u0165 iba datab\u00e1za MySQL, a podpora datab\u00e1zy PostgreSQL ako zdroja je len experiment\u00e1lna a s obmedzeniami (dozviete sa viac <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/configuration_file.html#postgresql-source-type-experimental\">tu<\/a><\/noindex>)<\/p>\n<p><\/p>\n<h3 id=\"itogi-po-pg_chameleon\">Zhrnutie k pg_chameleon<\/h3>\n<p><\/p>\n<p>Met\u00f3da replik\u00e1cie v pg_chameleon je skvel\u00e1 na migr\u00e1ciu datab\u00e1zy z MySQL do PostgreSQL. Podstatnou nev\u00fdhodou je, \u017ee replik\u00e1cia je len jednosmern\u00e1, preto odborn\u00edci na datab\u00e1zy pravdepodobne nebud\u00fa chcie\u0165 pou\u017e\u00edva\u0165 tento n\u00e1stroj na ni\u010d in\u00e9, ako na migr\u00e1ciu. Probl\u00e9m jednosmernej replik\u00e1cie sa v\u0161ak d\u00e1 vyrie\u0161i\u0165 \u010fal\u0161\u00edm open-source n\u00e1strojom \u2014 SymmetricDS.<\/p>\n<p><\/p>\n<p>Viac inform\u00e1ci\u00ed n\u00e1jdete v ofici\u00e1lnej dokument\u00e1cii <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/\">tu<\/a><\/noindex>. N\u00e1vod k pr\u00edkazov\u00e9mu riadku m\u00f4\u017eete n\u00e1js\u0165 <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/usage.html#https:\/\/pgchameleon.org\/documents\/usage.html\">tu<\/a><\/noindex>.<\/p>\n<p><\/p>\n<h3 id=\"obzor-symmetricds\">Preh\u013ead SymmetricDS<\/h3>\n<p><\/p>\n<p>SymmetricDS je open-source n\u00e1stroj, ktor\u00fd replik\u00e1cie ak\u00fako\u013evek datab\u00e1zu do akejko\u013evek inej be\u017enej datab\u00e1zy: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird a \u010fal\u0161ie cloudov\u00e9 datab\u00e1zov\u00e9 instance, ako Redshift, a Azure at\u010f. Dostupn\u00e9 funkcie: synchroniz\u00e1cia datab\u00e1z a s\u00faborov, replik\u00e1cia viacer\u00fdch hlavn\u00fdch datab\u00e1z, filtrovan\u00e1 synchroniz\u00e1cia, transform\u00e1cie a \u010fal\u0161ie. Je to n\u00e1stroj na Jave a vy\u017eaduje \u0161tandardn\u00e9 vydanie JRE alebo JDK (verzia 8.0 alebo vy\u0161\u0161ie). Tu je mo\u017en\u00e9 zaznamena\u0165 zmeny \u00fadajov cez sp\u00fa\u0161\u0165a\u010de v zdrojovej datab\u00e1ze a smerova\u0165 ich do pr\u00edslu\u0161nej cie\u013eovej datab\u00e1zy vo forme bal\u00ed\u010dkov.<\/p>\n<p><\/p>\n<h3 id=\"vozmozhnosti-symmetricds\">Mo\u017enosti SymmetricDS<\/h3>\n<p><\/p>\n<p>N\u00e1stroj je multiplatformov\u00fd, \u010do znamen\u00e1, \u017ee dve alebo viac r\u00f4znych datab\u00e1z m\u00f4\u017eu vymie\u0148a\u0165 \u00fadaje.<br \/>\nRela\u010dn\u00e9 datab\u00e1zy sa synchronizuj\u00fa pomocou zaznamen\u00e1vania zmien \u00fadajov, zatia\u013e \u010do datab\u00e1zy zalo\u017een\u00e9 na s\u00faborov\u00fdch syst\u00e9moch pou\u017e\u00edvaj\u00fa synchroniz\u00e1ciu s\u00faborov.<br \/>\nDvojcestn\u00e1 replik\u00e1cia pomocou met\u00f3d Push a Pull na z\u00e1klade s\u00faboru pravidiel.<br \/>\nPrenos d\u00e1t je mo\u017en\u00fd cez zabezpe\u010den\u00e9 siete a siete s n\u00edzkou priepustnos\u0165ou.<br \/>\nAutomatick\u00e9 obnovenie pri obnoven\u00ed funk\u010dnosti uzlov po zlyhan\u00ed a automatick\u00e9 rie\u0161enie konfliktov.<br \/>\nKompatibilita s cloudom a efekt\u00edvne API roz\u0161\u00edren\u00ed.<\/p>\n<p><\/p>\n<h3 id=\"primer-1\">Pr\u00edklad<\/h3>\n<p><\/p>\n<p>SymmetricDS m\u00f4\u017ee by\u0165 nakonfigurovan\u00fd dvoma sp\u00f4sobmi:<br \/>\nHlavn\u00fd (rodi\u010dovsk\u00fd) uzol, ktor\u00fd centralizovane koordinuje replik\u00e1ciu d\u00e1t medzi dvoma podriaden\u00fdmi (dc\u00e9rskymi) uzlami, pri\u010dom v\u00fdmena d\u00e1t medzi dc\u00e9rskymi uzlami prebieha iba cez rodi\u010dovsk\u00fd.<br \/>\nAkt\u00edvny uzol (uzol 1) m\u00f4\u017ee vymie\u0148a\u0165 d\u00e1ta na replik\u00e1ciu s in\u00fdm akt\u00edvnym uzlom (uzol 2) bez sprostredkovate\u013ea.<\/p>\n<p><\/p>\n<p>V oboch pr\u00edpadoch prebieha v\u00fdmena d\u00e1t pomocou Push a Pull. V tomto pr\u00edklade sa zameriame na konfigur\u00e1ciu \u201eakt\u00edvny-akt\u00edvny\u201c. Opisovanie celej architekt\u00fary by trvalo pr\u00edli\u0161 dlho, tak\u017ee si pre\u0161tudujte <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.10\/html\/user-guide.html#_architecture\">pr\u00edru\u010dka<\/a><\/noindex>, aby ste sa dozvedeli viac o fungovan\u00ed SymmetricDS.<\/p>\n<p><\/p>\n<p>In\u0161tal\u00e1cia SymmetricDS je ve\u013emi jednoduch\u00e1: stiahnite si open-source verziu zip s\u00faboru <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/download\">odtia\u013eto<\/a><\/noindex> a rozba\u013ete ju kamko\u013evek chcete. V tabu\u013eke ni\u017e\u0161ie s\u00fa uveden\u00e9 inform\u00e1cie o mieste in\u0161tal\u00e1cie a verzii SymmetricDS v tomto pr\u00edklade, ako aj verzie datab\u00e1z, verzie Linuxu, IP adresy a porty pre oba uzly.<\/p>\n<p><\/p>\n<p>Hostite\u013e<br \/>\nvm1<br \/>\nvm2<\/p>\n<p><strong>Verzia OS<\/strong><br \/>\nCentOS Linux 7.6 x86_64<br \/>\nCentOS Linux 7.6 x86_64<\/p>\n<p><strong>Verzia servera DB<\/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>Verzia SymmetricDS<\/strong><br \/>\nSymmetricDS 3.9<br \/>\nSymmetricDS 3.9<\/p>\n<p><strong>Cesta in\u0161tal\u00e1cie 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\u00e1zov uzla SymmetricDS<\/strong><br \/>\ncorp-000<br \/>\nstore-001<\/p>\n<p><\/p>\n<p>Tu in\u0161talujeme SymmetricDS do \/usr\/local\/symmetric-server-3.9.20, a tu bud\u00fa ulo\u017een\u00e9 r\u00f4zne vnoren\u00e9 adres\u00e1re a s\u00fabory. Zauj\u00edmaj\u00fa n\u00e1s vnoren\u00e9 adres\u00e1re samples a engines. V adres\u00e1ri samples sa nach\u00e1dzaj\u00fa uk\u00e1\u017eky konfigura\u010dn\u00fdch s\u00faborov s vlastnos\u0165ami uzla, ako aj uk\u00e1\u017eky SQL skriptov na r\u00fdchly za\u010diatok demon\u0161tr\u00e1cie.<\/p>\n<p><\/p>\n<p>V adres\u00e1ri samples vid\u00edme tri konfigura\u010dn\u00e9 s\u00fabory s vlastnos\u0165ami uzla \u2013 n\u00e1zov nazna\u010duje charakter uzla v konkr\u00e9tnej sch\u00e9me.<\/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 s\u00fa v\u0161etky potrebn\u00e9 konfigura\u010dn\u00e9 s\u00fabory pre z\u00e1kladn\u00fa sch\u00e9mu pozost\u00e1vaj\u00facu z 3 uzlov (varianta 1), a tie ist\u00e9 s\u00fabory m\u00f4\u017eeme pou\u017ei\u0165 aj pre sch\u00e9mu z 2 uzlov (varianta 2). Skop\u00edrujeme potrebn\u00fd konfigura\u010dn\u00fd s\u00fabor z adres\u00e1ra samples do engines na hostite\u013eovi vm1. Vyzer\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>Tento uzol v konfigur\u00e1cii SymmetricDS sa naz\u00fdva corp-000 a pripojenie k datab\u00e1ze spracov\u00e1va ovl\u00e1da\u010d mysql jdbc, ktor\u00fd pou\u017e\u00edva vy\u0161\u0161ie uveden\u00fd pripojovac\u00ed re\u0165azec a prihlasovacie \u00fadaje. Prip\u00e1jame sa k datab\u00e1ze replica_db a po\u010das vytv\u00e1rania sch\u00e9my bud\u00fa vytvoren\u00e9 tabu\u013eky. sync.url ukazuje miesto spojenia s uzlom pre synchroniz\u00e1ciu.<\/p>\n<p><\/p>\n<p>Uzol 2 na hoste vm2 sa nastavuje ako store-001 a zvy\u0161ok je uveden\u00fd v s\u00fabore node.properties, ktor\u00fd je uveden\u00fd ni\u017e\u0161ie. Uzol store-001 vykon\u00e1va datab\u00e1zu PostgreSQL a pgdb_replica je datab\u00e1za pre replik\u00e1ciu. registration.url umo\u017e\u0148uje hostite\u013eovi vm2 spoji\u0165 sa s hostite\u013eom vm1 a z\u00edska\u0165 od neho podrobnosti konfigur\u00e1cie.<\/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 pr\u00edklad SymmetricDS obsahuje parametre na nastavenie obojsmernej replik\u00e1cie medzi dvoma servermi datab\u00e1z (dvoma uzlami). Ni\u017e\u0161ie uveden\u00e9 kroky sa vykon\u00e1vaj\u00fa na hoste vm1 (corp-000), ktor\u00fd vytvor\u00ed pr\u00edklad sch\u00e9my so 4 tabu\u013ekami. N\u00e1sledne vykonanie create-sym-tables pr\u00edkazom symadmin vytvor\u00ed tabu\u013eky katalogu, kde bud\u00fa ulo\u017een\u00e9 pravidl\u00e1 a smer replik\u00e1cie medzi uzlami. Nakoniec sa do tabuliek na\u010d\u00edta pr\u00edklad d\u00e1t.<\/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 pr\u00edklade s\u00fa tabu\u013eky item a item_selling_price nastaven\u00e9 automaticky na replik\u00e1ciu z corp-000 do store-001, a tabu\u013eky sale (sale_transaction a sale_return_line_item) s\u00fa automaticky nastaven\u00e9 na replik\u00e1ciu zo store-001 do corp-000. Teraz vytvor\u00edme sch\u00e9mu v datab\u00e1ze PostgreSQL na hoste vm2 (store-001), aby sme ju pripravili na prij\u00edmanie d\u00e1t 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>Ur\u010dite over\u00edme, \u017ee v datab\u00e1ze MySQL na vm1 s\u00fa pr\u00edklady tabuliek a tabu\u013eky katalogu SymmetricDS. V\u0161imnite si, \u017ee syst\u00e9mov\u00e9 tabu\u013eky SymmetricDS (s prefixom sym_) s\u00fa moment\u00e1lne k dispoz\u00edcii len na uzle corp-000, preto\u017ee tam sme vykonali pr\u00edkaz create-sym-tables a budeme riadi\u0165 replik\u00e1ciu. Okrem toho v datab\u00e1ze na uzle store-001 bud\u00fa len 4 tabu\u013eky pr\u00edkladu bez d\u00e1t.<\/p>\n<p><\/p>\n<p>V\u0161etko. Prostredie je pripraven\u00e9 na spustenie serverov\u00fdch procesov sym na oboch uzloch, ako je uveden\u00e9 ni\u017e\u0161ie.<\/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 logov sa posielaj\u00fa do s\u00faboru pozadia (symmetric.log) v prie\u010dinku logov v adres\u00e1ri, kde je nain\u0161talovan\u00fd SymmetricDS, ako aj do \u0161tandardn\u00e9ho v\u00fdstupu. Server sym teraz m\u00f4\u017ee by\u0165 inicializovan\u00fd na uzle 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>Ak spust\u00edte serverov\u00fd proces sym na hostite\u013eovi vm2, vytvor\u00ed tabu\u013eky adres\u00e1ra SymmetricDS aj v datab\u00e1ze PostgreSQL. Ak spust\u00edte serverov\u00fd proces sym na oboch uzloch, koordinuj\u00fa sa navz\u00e1jom, aby replikovali d\u00e1ta z corp-000 na store-001. Ak po nieko\u013ek\u00fdch sekund\u00e1ch po\u017eiadame o v\u0161etky 4 tabu\u013eky na oboch stran\u00e1ch, uvid\u00edme, \u017ee replik\u00e1cia bola \u00faspe\u0161n\u00e1. Alebo m\u00f4\u017eeme odosla\u0165 po\u010diato\u010dn\u00e9 na\u010d\u00edtanie na uzol store-001 z corp-000 nasleduj\u00facim pr\u00edkazom.<\/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 tomto kroku je do tabu\u013eky item v datab\u00e1ze MySQL na uzle corp-000 (host: vm1) vlo\u017een\u00fd nov\u00fd z\u00e1znam a m\u00f4\u017eeme skontrolova\u0165 jeho replik\u00e1ciu do datab\u00e1zy PostgreSQL na uzle store-001. Vid\u00edme oper\u00e1ciu Pull na presun d\u00e1t z corp-000 do store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into item values ('22000002','Jelly Bean');\nQuery OK, 1 row affected (0.00 sec)<\/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 rows)<\/code><\/pre>\n<p><\/p>\n<p>Aby sme vykonali oper\u00e1ciu Push na presun d\u00e1t z store-001 do corp-000, vlo\u017e\u00edme z\u00e1znam do tabu\u013eky sale_transaction a over\u00edme, \u017ee replik\u00e1cia bola \u00faspe\u0161n\u00e1.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/B23cJ4sP\">V\u00fdsledok.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Vid\u00edme \u00faspe\u0161n\u00e9 nastavenie obojsmernej replik\u00e1cie tabu\u013eiek medzi datab\u00e1zami MySQL a PostgreSQL. Na nastavenie replik\u00e1cie pre nov\u00e9 pou\u017e\u00edvate\u013esk\u00e9 tabu\u013eky vykon\u00e1me nasleduj\u00face kroky. Vytv\u00e1rame tabu\u013eku t1 ako pr\u00edklad a nastavujeme pravidl\u00e1 jej replik\u00e1cie nasleduj\u00facim sp\u00f4sobom. Takto nastavujeme iba replik\u00e1ciu z corp-000 do store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; create table  t1 (no integer);\nQuery OK, 0 rows affected (0.01 sec)<\/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);\nQuery OK, 1 row affected (0.01 sec)<\/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);\nQuery OK, 1 row affected (0.01 sec)<\/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);\nQuery OK, 1 row affected (0.01 sec)<\/code><\/pre>\n<p><\/p>\n<p>Potom konfigur\u00e1cia prijme ozn\u00e1menie o zmene sch\u00e9my, t.j. pridan\u00ed novej tabu\u013eky, pomocou pr\u00edkazu symadmin s argumentom sync-triggers, ktor\u00fd znovu vytvor\u00ed sp\u00fa\u0161\u0165a\u010de na zladenie defin\u00edci\u00ed tabuliek. Vykon\u00e1va sa send-schema na odoslanie zmien sch\u00e9my na uzol store-001 a replik\u00e1cia tabu\u013eky t1 je nastaven\u00e1.<\/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 in\u0161tal\u00e1cia a konfigur\u00e1cia, vr\u00e1tane pripraven\u00e9ho bal\u00edka s\u00faborov s parametrami na vytvorenie sch\u00e9my s tromi alebo dvoma uzlami.<br \/>\nKros-platformnos\u0165 datab\u00e1z a nez\u00e1vislos\u0165 od platformy, vr\u00e1tane serverov, notebookov a mobiln\u00fdch zariaden\u00ed.<br \/>\nReplik\u00e1cia akejko\u013evek datab\u00e1zy do akejko\u013evek inej datab\u00e1zy lok\u00e1lne, v WAN alebo v cloude.<br \/>\nMo\u017enos\u0165 optim\u00e1lnej pr\u00e1ce s p\u00e1rom datab\u00e1z alebo nieko\u013ek\u00fdmi tis\u00edcmi pre pohodln\u00fa replik\u00e1ciu.<br \/>\nPlaten\u00e1 verzia s grafick\u00fdm rozhran\u00edm a vynikaj\u00facou podporou.<\/p>\n<p><\/p>\n<h3 id=\"nedostatki-symmetricds\">Nev\u00fdhody SymmetricDS<\/h3>\n<p><\/p>\n<p>Je potrebn\u00e9 ru\u010dne definova\u0165 v pr\u00edkazovom riadku pravidl\u00e1 a smer replik\u00e1cie cez SQL oper\u00e1tory na na\u010d\u00edtanie tabuliek katal\u00f3gov, \u010do m\u00f4\u017ee by\u0165 nepr\u00edjemn\u00e9.<br \/>\nNastavovanie mno\u017estva tabuliek na replik\u00e1ciu m\u00f4\u017ee by\u0165 \u00fanavn\u00e9, ak sa nepou\u017e\u00edvaj\u00fa skripty na vytvorenie SQL oper\u00e1torov, ktor\u00e9 definuj\u00fa pravidl\u00e1 a smer replik\u00e1cie.<br \/>\nDo logov sa zapisuje pr\u00edli\u0161 ve\u013ea inform\u00e1ci\u00ed a niekedy je potrebn\u00e9 uprata\u0165 s\u00fabor logu, aby nezaberalo pr\u00edli\u0161 ve\u013ea miesta.<\/p>\n<p><\/p>\n<h3 id=\"itogi-po-symmetricds\">Zhrnutie k SymmetricDS<\/h3>\n<p><\/p>\n<p>SymmetricDS umo\u017e\u0148uje nastavova\u0165 obojsmern\u00fa replik\u00e1ciu medzi dvoma, tromi a dokonca nieko\u013ek\u00fdmi tis\u00edckami uzlov, aby vykon\u00e1val replik\u00e1ciu a synchronizoval s\u00fabory. Je to unik\u00e1tne n\u00e1stroje, ktor\u00e9 samo vykon\u00e1va mnoho \u00faloh, ako napr\u00edklad automatick\u00e9 obnovenie d\u00e1t po dlhom v\u00fdpadku na uzle, zabezpe\u010den\u00e1 a efekt\u00edvna v\u00fdmena d\u00e1t medzi uzlami cez HTTPS, automatick\u00e9 riadenie konfliktov na z\u00e1klade s\u00faboru pravidiel at\u010f. SymmetricDS vykon\u00e1va replik\u00e1ciu medzi ak\u00fdmiko\u013evek datab\u00e1zami, tak\u017ee ho mo\u017eno vyu\u017ei\u0165 pre r\u00f4zne scen\u00e1re, vr\u00e1tane migr\u00e1cie, prechodu na nov\u00fa verziu, distrib\u00facie, filtrovania a transform\u00e1cie d\u00e1t na r\u00f4znych platform\u00e1ch.<\/p>\n<p><\/p>\n<p>Pr\u00edklad bol vytvoren\u00fd na z\u00e1klade ofici\u00e1lneho <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.9\/html\/tutorials.html\">stru\u010dn\u00e9ho sprievodcu<\/a><\/noindex> k SymmetricDS. V <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.9\/html\/user-guide.html\">u\u017e\u00edvate\u013eskej pr\u00edru\u010dke<\/a><\/noindex> podrobne sa opisuj\u00fa r\u00f4zne pojmy t\u00fdkaj\u00face sa nastavenia replik\u00e1cie pomocou 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\/sk\/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=\"sk_SK\" \/>\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\/sk\/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\udd47Kr\u00ed\u017eov\u00e1 replik\u00e1cia medzi PostgreSQL a MySQL | ProHoster","description":"V skratke sa porozpr\u00e1vam o.","canonical_url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sk_SK","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\/sk\/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\/sk\/wp-json\/wp\/v2\/posts\/38146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=38146"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/38146\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media\/28635"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=38146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=38146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=38146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}