Průvodce mysqldump: Jak zálohovat a obnovit databáze MySQL (s příklady)

1. Úvod

Zálohování a obnovení databáze jsou základní součástí správy dat a nezbytné pro spolehlivý provoz. mysqldump od MySQL je široce používán jako efektivní a flexibilní nástroj pro vytváření záloh databází. V tomto průvodci vysvětlíme vše od základního použití mysqldump po pokročilé možnosti, metody obnovení a podrobné řešení problémů. Na konci článku představíme také osvědčené postupy a další zdroje, takže tuto příručku použijte jako užitečný odkaz k zvládnutí mysqldump.

2. Co je mysqldump?

2.1 Přehled mysqldump

mysqldump je nástroj příkazové řádky pro vytváření záloh databází MySQL. Můžete exportovat celou databázi, konkrétní tabulky nebo jen data, která splňují určité podmínky, ve formě SQL skriptu. Tento soubor s výpisem lze použít k obnovení dat nebo k jejich migraci na nový server.

2.2 Běžné případy použití

• Záloha : Provádějte pravidelné zálohy, abyste byli připraveni na selhání systému nebo ztrátu dat.
• Migrace dat : Přesuňte databáze mezi servery nebo zkopírujte data do vývojového prostředí.
• Analýza dat : Extrahujte konkrétní datové sady pro analýzu a ověření.

3. Základní použití

3.1 Základní syntaxe příkazu

Základní syntaxe příkazu mysqldump je následující:

mysqldump -u username -p database_name > output_file.sql

• -u username : Uživatelské jméno použité pro přístup k databázi.
• -p : Vyzve vás k zadání hesla.
• database_name : Název databáze, kterou chcete zálohovat.
• > output_file.sql : Cílová cesta/název souboru pro výpis.

3.2 Možnosti autentizace uživatele

• -h hostname : Název hostitele databázového serveru (výchozí je localhost).
• -P port_number : Číslo portu, ke kterému se připojit (výchozí je 3306).

3.3 Příklad: Zálohování celé databáze

mysqldump -u root -p mydatabase > backup.sql

Tento příkaz zálohuje všechna data v mydatabase do souboru backup.sql. Pokud do názvu souboru zálohy zahrnete datum pro verzování, bude snazší sledovat historii vašich záloh.

4. Vysvětlení klíčových možností

4.1 --all-databases (-A)

Tato volba zálohuje všechny databáze najednou. Je užitečná, když chcete provést úplnou zálohu celého serveru.

mysqldump -u root -p --all-databases > all_databases_backup.sql

4.2 --no-data (-d)

Použijte tuto volbu, pokud chcete zálohovat pouze schéma tabulek bez zahrnutí jakýchkoli dat. Například je užitečná, když chcete exportovat jen strukturu tabulek pro nastavení vývojového prostředí.

mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql

4.3 --where (-w)

Použijte tuto volbu, pokud chcete zálohovat jen data, která splňují konkrétní podmínky. Například pro zálohování jen záznamů, kde je sloupec is_active nastaven na 1:

mysqldump -u root -p mydatabase --where="is_active=1" > filtered_data_backup.sql

4.4 --ignore-table

Použijte tuto volbu k vyloučení konkrétních tabulek ze zálohy. Je užitečná, když existují tabulky, které nechcete zahrnout.

mysqldump -u root -p mydatabase --ignore-table=mydatabase.table1 > partial_backup.sql

5. Praktické příklady

5.1 Zálohování pouze konkrétních tabulek

Pokud chcete zálohovat jen konkrétní tabulky, uveďte název tabulky za názvem databáze.

mysqldump -u root -p mydatabase table1 > table1_backup.sql

Tento příkaz uloží pouze data z table1 do souboru table1_backup.sql.

5.2 Zálohování pouze dat / pouze schématu

• Data only : mysqldump -u root -p mydatabase --no-create-info > data_only_backup.sql Zálohuje pouze data a neobsahuje strukturu tabulek.
• Schema only : bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql Zálohuje pouze schéma tabulek.

5.3 Podmíněné zálohování

Pro zálohování jen dat, která splňují konkrétní podmínky, použijte volbu --where.

mysqldump -u root -p mydatabase --where="created_at >= '2023-01-01'" > recent_data_backup.sql

Tento příkaz zálohuje pouze data, kde je created_at dne 1. ledna 2023 nebo později.

6. Jak obnovit

Pro obnovení databáze zálohované pomocí mysqldump použijte příkaz mysql. Obnovení je proces použití souboru zálohy k vrácení databáze do předchozího stavu.

6.1 Základní syntaxe obnovení

mysql -u username -p database_name < dump_file.sql

• -u username : Uživatelské jméno použité pro připojení k databázi.
• -p : Vyžádá zadání hesla.
• database_name : Název cílové databáze.
• < dump_file.sql : Soubor dumpu použitý pro obnovení.

6.2 Příklad: Spuštění obnovení

mysql -u root -p mydatabase < backup.sql

Tento příkaz obnoví data do mydatabase ze souboru backup.sql.

6.3 Důležité poznámky k obnově

• Pokud databáze, kterou chcete obnovit, neexistuje, musíte ji nejprve vytvořit.
• Obnovení velkého množství dat může trvat dlouho, proto je důležité plánovat dopředu.

7. Nejlepší postupy pro mysqldump

7.1 Plánování záloh

Automatizujte pravidelné zálohy pomocí skriptování mysqldump a použití plánovače, jako je cron. V následujícím příkladu shell skriptu se každý den o půlnoci provádí úplná záloha všech databází.

#!/bin/bash
mysqldump -u root -p'password' --all-databases > /path/to/backup/all_databases_$(date +\%F).sql

7.2 Šifrování souborů záloh

Protože soubory záloh mohou obsahovat citlivé informace, doporučuje se je šifrovat pomocí nástrojů, jako je gpg.

gpg -c /path/to/backup/all_databases_$(date +\%F).sql

7.3 Kompatibilita verzí

Při migraci dat mezi různými verzemi MySQL je třeba dbát na problémy s kompatibilitou. Před aktualizací simulujte postup zálohování a obnovení v testovacím prostředí a ověřte kompatibilitu.

1. Obnovit definice tabulek : mysqldump --all-databases --no-data --routines --events > dump-defs.sql Tento příkaz exportuje pouze strukturu tabulek, poté ji obnovíte v prostředí novější verze a zkontrolujete kompatibilitu.
2. Obnovit data : mysqldump --all-databases --no-create-info > dump-data.sql Po potvrzení, že definice tabulek jsou kompatibilní, obnovte pouze data.
3. Ověřit v testovacím prostředí : Pro ověření kompatibility napříč verzemi proveďte zálohování a obnovení v testovacím prostředí. Po potvrzení, že vše funguje správně, pokračujte s migrací v produkčním prostředí.

7.4 Ukládání a ověřování záloh

• Ukládat zálohy bezpečně : Ukládejte soubory záloh na externí úložiště nebo do cloudu a pravidelně je aktualizujte. Ukládání mimo místo pomáhá chránit data před fyzickými selháními.
• Pravidelně ověřovat obnovení : Provádějte testy obnovení pravidelně, abyste potvrdili, že zálohy lze správně obnovit. Je důležité nevynechávat ověřování obnovení pro případ, že by zálohy byly neplatné.

8. Řešení problémů

8.1 Časté chyby a opravy

• Chyba: @@GLOBAL.GTID_PURGED cannot be changed : Tato chyba se objeví, když nastanou problémy související s GTID v MySQL 8.0. Můžete ji předejít tím, že zakomentujete nastavení GTID pomocí volby --set-gtid-purged=COMMENTED. mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
• Chyba: Nedostatek místa na disku : Pokud během zálohování velké databáze dojde k nedostatku místa na disku, zálohu komprimujte nebo změňte cíl. Například můžete zálohu komprimovat pomocí gzip takto: mysqldump -u root -p mydatabase | gzip > backup.sql.gz
• Chyba: Nedostatečná oprávnění : Pokud uživatel databáze nemá dostatečná oprávnění, zálohování nebo obnovení selže. Přidělte požadovaná oprávnění (např. SELECT, LOCK TABLES, SHOW VIEW atd.) a zkuste to znovu.

8.2 Problémy s kompatibilitou verzí

Kompatibilitní problémy mezi různými verzemi MySQL lze vyřešit testováním před aktualizací. Zejména při migraci z MySQL 5.7 na 8.0 se doporučuje obnovit pouze definice tabulek pomocí volby --no-data a ověřit kompatibilitu.

• Test na nekompatibility : Před aktualizací simulujte migraci v testovacím prostředí, abyste identifikovali potenciální problémy. Sledujte nekompatibilní funkce nebo syntaxi a podle potřeby upravte SQL skripty.

9. Shrnutí

mysqldump je spolehlivý a výkonný nástroj pro zálohování a obnovu databází MySQL. V tomto článku jsme pokryli vše od základního použití po pokročilé možnosti, osvědčené postupy a řešení problémů. Použitím těchto znalostí můžete své databáze chránit a spravovat efektivněji pomocí mysqldump.

Začleněním osvědčených postupů, jako je plánování záloh a šifrování souborů, můžete zlepšit bezpečnost dat a zvýšit spolehlivost provozu databáze. Používejte mysqldump správně, abyste se připravili na možné problémy s databází.

10. Odkazy a další zdroje

• Official MySQL Documentation

Odkazujte na tento zdroj, abyste se dozvěděli více o mysqldump a aplikovali jej v reálných situacích. Také prováděním pravidelných záloh a ověřováním obnovy můžete udržet bezpečnost databáze a být připraveni na nečekanou ztrátu dat.