{"id":185,"date":"2025-01-28T10:15:11","date_gmt":"2025-01-28T09:15:11","guid":{"rendered":"https:\/\/blog.ctr24.co.pl\/?p=185"},"modified":"2025-01-28T10:15:11","modified_gmt":"2025-01-28T09:15:11","slug":"synchronizacja-tabel-mysql","status":"publish","type":"post","link":"https:\/\/blog.ctr24.co.pl\/?p=185","title":{"rendered":"Synchronizacja tabel MYSQL"},"content":{"rendered":"\n

Kopiowanie schematu tabeli<\/strong><\/h3>\n\n\n\n

<\/p>\n\n\n

mysqldump -u [u\u017cytkownik] -p --no-data [nazwa_bazy] > schemat.sql<\/pre><\/div>\n\n\n
Zapytania SQL na poziomie serwera<\/strong><\/h3>\n\n\n\n
Je\u015bli obie bazy znajduj\u0105 si\u0119 na tym samym serwerze MySQL, mo\u017cesz u\u017cy\u0107 prostych zapyta\u0144 SQL do synchronizacji:<\/p>\n\n\n
INSERT INTO target_database.target_table (col1, col2, col3)\nSELECT col1, col2, col3 FROM source_database.source_table\nON DUPLICATE KEY UPDATE \n    col1 = VALUES(col1), \n    col2 = VALUES(col2), \n    col3 = VALUES(col3);<\/pre><\/div>\n\n\n
<\/h2>\n\n\n\n
Automatyczna synchronizacja z Event Scheduler<\/strong><\/h3>\n\n\n\n
Je\u015bli chcesz, aby synchronizacja dzia\u0142a\u0142a automatycznie co jaki\u015b czas, mo\u017cesz stworzy\u0107 zadanie wbudowane w MySQL:<\/p>\n\n\n\n
Krok 1: W\u0142\u0105cz Event Scheduler<\/h4>\n\n\n\n
Upewnij si\u0119, \u017ce Event Scheduler<\/strong> jest w\u0142\u0105czony:<\/p>\n\n\n\n
SET GLOBAL event_scheduler = ON;
<\/code><\/pre>\n\n\n\n
Krok 2: Utw\u00f3rz zdarzenie (Event)<\/h4>\n\n\n\n
Stw\u00f3rz zdarzenie, kt\u00f3re b\u0119dzie synchronizowa\u0107 tabel\u0119, np. co godzin\u0119:<\/p>\n\n\n\n
CREATE EVENT sync_table_event
ON SCHEDULE EVERY 1 HOUR
DO
    INSERT INTO target_database.target_table (col1, col2, col3)
    SELECT col1, col2, col3 FROM source_database.source_table
    ON DUPLICATE KEY UPDATE 
        col1 = VALUES(col1), 
        col2 = VALUES(col2), 
        col3 = VALUES(col3);
<\/code><\/pre>\n\n\n\n
    \n
  • Zmieniasz harmonogram (EVERY 1 HOUR<\/code>) w zale\u017cno\u015bci od potrzeb.<\/li>\n\n\n\n
  • Zdarzenie b\u0119dzie dzia\u0142a\u0142o automatycznie.<\/li>\n<\/ul>\n\n\n\n
    Krok 3: Sprawd\u017a aktywne zdarzenia<\/h4>\n\n\n\n
    Mo\u017cesz sprawdzi\u0107 utworzone zdarzenia za pomoc\u0105:<\/p>\n\n\n\n
    SHOW EVENTS;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
    Kopiowanie schematu tabeli Zapytania SQL na poziomie serwera Je\u015bli obie bazy znajduj\u0105 si\u0119 na tym samym serwerze MySQL, mo\u017cesz u\u017cy\u0107<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"class_list":["post-185","post","type-post","status-publish","format-standard","hentry","category-tips"],"_links":{"self":[{"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=\/wp\/v2\/posts\/185","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=185"}],"version-history":[{"count":2,"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=\/wp\/v2\/posts\/185\/revisions"}],"predecessor-version":[{"id":187,"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=\/wp\/v2\/posts\/185\/revisions\/187"}],"wp:attachment":[{"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=185"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=185"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ctr24.co.pl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=185"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}