MySQL - kopie tabulky

Zdravím, potřeboval bych poradit.
mám dva servery, jeden starý s MySQL a druhý nový s MariaDB (Linux)

otázka 1
existuje způsob jak bych pomocí PHP mohl provádět synchronizaci tabulek, vše co se za den změní ve staré DB by se aktualizovalo do nového serveru? Klidně i za cenu smazání tabulky na novém a vždy nahrání nových dat na nový server?

proč vymýšlím tak divnou věc.

- na starém serveru mám pouze FTP přístup a k datum se dostávám přes phpMyAdmin
- nový server mám komplet ve správě
- z nového serveru se nepřipojím vzdáleně na starý server, na starém je pouze přístup k DB localhost

pouze PHP scriptem ze starého serveru se dokážu spojit s novým serverem a tam nahrát data.

otázka 2
pokud bych chtěl přistupovat několika serverama do jedné DB, je lepší nechat DB na jednom ze serverů, který má 30GB paměť, nebo mám využít Cloud Databázi a k ní se připojit? bude znát vzdálené připojení ?

K 1. otázce:

Na starém serveru si vytvořte můstek v PHP, v podstatě to může být jednoduché REST API.

Předpokládám, že ne všechny tabulky mají datetime uloženého záznamu, ale všechny mají unikátní id (nebo nějakou kombinaci, která je unikátní a můžete ji tak považovat za id).

POST /save-db-diff
Pro každou tabulku uloží hash každého jednotlivého řádku. Přepíše všechna původní data (uložené předchozí den), protože se za tu dobu mohly změnit.
Tuhle URL budete volat např. každý den v 00:00.

Příklad uložených dat po zavolání endpointu:

Kód:

users:
1=c4ca4238a0b923820dcc509a6f75849b
2=c81e728d9d4c2f636f067f89cc14862c
3=eccbc87e4b5ce2fe28308fd9f2a7baf3

products:
1=a87ff679a2f3e71d9181a67b7542122c
2=e4da3b7fbbce2345d7772b0674a318d5
3=1679091c5a880faf6fb5e6087eb1b2dc

POST /export-db-diff
1) najde nové řádky oproti uloženému seznamu => pošle do exportu
2) najde smazané řádky oproti uloženém seznamu => pošle do exportu
3) u zbylých vypočítá hash a zjistí, jestli se změnily. => Pokud ano, pošle do exportu
Tuhle URL budete volat např. 24 hodin po prvním endpointu, takže ve 23:59 každý den.
Získaná data pak uložíte na novém serveru.

Příklad exportu po zavolání endpointu:

Kód:

usersToInsert: [
  {id: 4, name: "Pepa", email: "pepa@seznamc.cz"} # ID 4 není v původně uloženém seznamu hashů
],
usersToDelete: [
  {id: 2} # ID 2 je v původně uloženém seznamu hashů, ale už není v DB
],
usersToUpdate: [
  {id: 3, name: "Honza", email: "honza@seznam.cz"} # ID 3 má nyní odlišný hash, takže se nějaké sloupce změnily. Do exportu se posílají nové hodnoty
]

productsToInsert: [],
productsToDelete: [],
productsToUpdate: []

Endpointy můžete rozšířit např. o datetime, pak budete moci číst nejen poslední diff, ale i předchozí uložené.

Edit: Upravil jsem příspěvek, aby řešil i update/delete.


A nebo jiné řešení, rozhodně méně náročné na výkon a programově čistší:
Na DB úrovni si vytvoříte trigger, který uloží záznam o každém insert/update/delete do speciální tabulky včetně datetime výskytu. A tuhle speciální tabulku pak budete vracet přes endpoint POST /export-db-diff s filtrem na datetime od-do.

---

K 2. otázce:

Subjektivně bych si vybral cloud DB. Vycházím z předpokladu, že běží na serveru optimalizovaném pro databáze (neobsluhuje žádné webové aplikace, e-maily, apod.) a vzdálené připojení k nim. Navíc se o něj starají lidi, kteří rozumí databázím i serverům víc než já.

Ale pokud máte u toho cloud DB serveru napsané, že má jen 4 GB RAM sdílenou pro 100 dalších lidí, nebo nějaké jiné podmínky srovnatelně horší s vlastním řešením, tak v tu chvíli bych šel do vlastního.

Proč to jednorázově nezkopírujete na nový server a neběžíte pak už jen z něj?

také bych db měl pouze na jednom serveru, ideálně na tom novém, nevypadá to, že by se jednalo o nějakou velkou databázi. Taková replikace je problematická a může vám přinést problémy.

Postup, který navrhuje Petr Hejda je příliš složitý, problematický a můžeš si tam zanést spousty bezpečnostních chyb, dělat vlastní formát není nikdy dobré. Raději bych už použil Adminer od Jakuba Vrány, umí dělat exporty na ftp, ty si pak můžeš stáhnout a přenést na druhou stranu.

Nevím co v tvém kontextu znamená Cloud Databáze.

Zdravíčko,
ad 1) Napadá mě upravit si metodu mysqli::query() tak, že každý příkaz, který mění databázi, normálně spustíte, a v případě, že byl proveden bez chyby, připíšete daný příkaz k souboru pojmenovaném dle aktuálního dne (např. "log/2018-12-25.sql"). Aktualizace do druhé databáze pak spočívá v importu (vykonání) příkazů v tomto souboru.

Přes svátky jsem testoval, a výsledky nejsou moc uspokojivé.

Vytvořil jsem DB na novém serveru a připojil ostatní servery na DB, odezva serveru byla v řádech několika vteřin.

Založil jsem tedy u OVH DB cloud, vyloženě DB na strojích k tomu určených a optimalizovaných.
Odezva byla taktéž velká v řadu vteřin. Mají tam i metriky na měření jak dotazy trvají dlouho, kolik je aktuálních spojení a tyto hodnoty byly v pohodě.

Bude asi problém ve vzdálenosti a dostupností severů. DB cloud je ve Francii a servery zde v ČR.

odezvu si můžeš změřit, podle zkušenosti z ČR do OVH bude mezi 5 - 15 ms. V tom případě máš na stránce příliš moc sql dotazů, pokud každý dotaz bude mít latenci 15 ms a máš jich 100, máš jenom na komunikaci 2,5 vteřiny zpoždění a všechny metriky mohou být samozřejmě ok.

Databáze se buď dává do stejné sítě, kde odezva je 0,2 ms nebo se dotazy združují a posílají se na db najednou, to děláme u globálních projektů, kde lokalita db není vždy ideální.