mysqldump –where: Jak exportovat pouze řádky, které potřebujete (s praktickými příklady)

1. Úvod

Potřeba extrahovat „pouze požadovaná data“ během zálohovacích operací

Při provozu MySQL jsou pravidelné zálohy nezbytné. Nicméně v závislosti na situaci existuje mnoho případů, kdy „nemusíte dumpovat všechna data.“ Například:

• Extrahovat pouze data z konkrétního období z velké tabulky
• Dumpovat jen záznamy, kde má sloupec status konkrétní hodnotu
• Vyloučit stará data a migrovat jen aktuálně používané záznamy
• Přenést jen požadovaná data do testovacího prostředí

V těchto scénářích je volba --where nástroje mysqldump mimořádně užitečná.

Jak mysqldump souvisí s podmínkami WHERE

Obvykle se mysqldump používá jako mechanismus pro „zálohování všech řádků v tabulce.“ S --where však můžete zadat podmínky stejně jako v SQL klauzuli WHERE a vytvořit dump soubor, který obsahuje jen požadované záznamy.

• Datové podmínky jako created_at >= '2024-01-01'
• Podmínky statusu jako status = 'active'
• Rozsahové filtry jako id BETWEEN 100 AND 200
• Extrakce s více podmínkami spojenými pomocí AND / OR

Tímto způsobem není mysqldump jen nástroj pro zálohování – může sloužit také jako flexibilní nástroj pro extrakci dat.

Výhody podmíněných dumpů

Využitím --where získáte následující výhody:

• Zkrácený čas zálohování – protože jsou extrahovány jen požadované záznamy, zátěž je menší.
• Menší velikost dump souboru – obzvláště efektivní v prostředích s velkými tabulkami.
• Efektivnější migrace dat – můžete načíst jen data, která potřebujete, do testovacích nebo stagingových prostředí.
• Ideální pro archivaci – umožňuje flexibilní návrhy jako „uložit stará data odděleně jako archiv.“

Co se v tomto článku naučíte

Tento článek poskytuje komplexní vysvětlení – od základů mysqldump po psaní dumpů filtrovaných pomocí WHERE, praktické ukázky, provozní upozornění a řešení problémů.

• Základní syntaxe mysqldump
• Praktické příklady dumpů filtrovaných pomocí WHERE
• Specifické vzory podmínek využívající data a hodnoty statusu
• Jak uvažovat o výkonu u velkých tabulek
• Časté chyby a jak je opravit
• Nejlepší postupy, které se vztahují k reálným operacím

Aby byl článek přívětivý pro začátečníky, pečlivě vysvětlujeme ukázkové příkazy a kdy je použít.

2. Rychlý přehled: základní použití mysqldump

mysqldump je oficiální zálohovací nástroj MySQL. Jeho klíčovou vlastností je, že může uložit data i struktury tabulek jako textový SQL soubor. Než se pustíme do podmínek WHERE, projděme si základy, které byste měli znát.

Základní syntaxe a hlavní volby

Základní syntaxe mysqldump je velmi jednoduchá:

mysqldump -u username -p database_name > dump.sql

Po spuštění příkazu budete vyzváni k zadání hesla.
V tomto tvaru jsou dumpovány všechny tabulky v databázi.

Často používané hlavní volby

mysqldump má mnoho voleb, ale následující jsou obzvláště běžné:

• --single-transaction Zálohuje InnoDB tabulky bez jejich zamykání. Bezpečné i na živém systému.
• --quick Zpracovává řádky po jednom a udržuje nízkou spotřebu paměti. Efektivní pro velké tabulky.
• --default-character-set=utf8mb4 Zabrání poškození textu tím, že určí znakovou sadu dump souboru.
• --no-data Dumpuje jen struktury tabulek (žádná data).
• --tables Umožní specifikovat názvy tabulek a dumpovat jen to, co potřebujete.

Kombinací těchto voleb můžete vytvořit optimální zálohu pro vaši situaci.

Jak dumpovat jen konkrétní tabulky

S mysqldump můžete po názvu databáze uvést názvy tabulek, které chcete dumpovat jen tyto tabulky.

mysqldump -u root -p mydb users orders > selected_tables.sql

V tomto příkladu jsou dumpovány jen users a orders.

To je velmi užitečné, když potřebujete více tabulek, ale nepotřebujete celou databázi.

Jak dumpovat více databází najednou

Pokud chcete zálohovat více databází najednou, použijte --databases nebo --all-databases.

• Vytvoření zálohy více specifikovaných databází mysqldump -u root -p --databases db1 db2 db3 > multi_db.sql
• Vytvoření zálohy všech databází na serveru mysqldump -u root -p --all-databases > all.sql

Protože zálohy filtrovány WHERE se v podstatě používají pro tabulku, je důležité nejprve pochopit koncept „zálohy na úrovni tabulky“.

Základní tok zálohování a obnovení s mysqldump

Základní tok zálohování pomocí mysqldump je následující:

1. Vytvoření souboru zálohy s mysqldump
2. Volitelně ho komprimovat s gzip atd.
3. Uložit ho na bezpečné místo (jiný server, externí úložiště atd.)
4. Obnovení importem pomocí příkazu mysql

Obnovení se provádí takto:

mysql -u root -p mydb < dump.sql

Protože mysqldump produkuje prostý text SQL, je snadno ovladatelný a není vázán na specifické prostředí.

3. Podmíněné zálohy pomocí volby --where

Jednou z nejmocnějších volb v mysqldump je --where.
Můžete specifikovat podmínky stejným způsobem, jako byste to dělali v klauzuli MySQL SELECT WHERE, a vypsat pouze řádky, které potřebujete.

Co může --where udělat?

Normální mysqldump zálohuje celou tabulku.
Ale s --where můžete provádět „zálohy ve stylu extrakce“, jako:

• Extrahovat pouze nová data
• Vypsat pouze řádky, kde je stav aktivní
• Zálohovat pouze data specifického uživatele
• Extrahovat pouze řádky v určitém rozsahu ID
• Kombinovat více podmínek (AND/OR)

To je důvod, proč lze mysqldump používat nejen jako nástroj pro zálohování, ale také jako
„nástroj pro migraci dat s filtry extrakce.“

Základní syntaxe

Základní forma --where je:

mysqldump -u root -p mydb mytable --where="condition_expression" > filtered.sql

Výraz podmínky lze napsat stejně jako standardní SQL klauzuli WHERE.

Běžné příklady podmínek

1. Filtrování podle ID

mysqldump -u root -p mydb users --where="id > 1000" > users_over_1000.sql

2. Filtrování podle data (created_at je 2024 nebo později)

mysqldump -u root -p mydb logs --where="created_at >= '2024-01-01'" > logs_2024.sql

3. Filtrování podle stavu (pouze aktivní)

mysqldump -u root -p mydb orders --where="status = 'active'" > orders_active.sql

4. Více podmínek (AND)

mysqldump -u root -p mydb orders \
--where="status = 'active' AND created_at >= '2024-01-01'" \
> orders_active_recent.sql

5. Kombinace podmínek OR

mysqldump -u root -p mydb products \
--where="category = 'A' OR category = 'B'" \
> products_ab.sql

6. Částečné shody s LIKE

mysqldump -u root -p mydb members --where="email LIKE '%@example.com'" > example_members.sql

Poznámky při používání podmínek WHERE

1. Používání dvojitých uvozovek vs. jednoduchých uvozovek

--where="status = 'active'"

Jak je znázorněno výše,
Vnější → dvojité uvozovky
Vnitřní → jednoduché uvozovky
je standardní přístup.

2. Lze ho použít pouze pro tabulku

--where nelze použít pro celou databázi.
Musíte ho specifikovat pro každou tabulku.

3. Buďte opatrní s formáty dat a řetězců

Pokud formát neodpovídá definici sloupce v MySQL, řádky nebudou extrahovány.

4. Těžké podmínky mohou způsobit zpomalení zpracování

Zvláště pokud podmínka WHERE nemůže použít index, záloha bude pomalejší.

Praktické případy použití

Případ 1: Extrahovat pouze logy z určitého období

Tento příklad extrahuje pouze nedávné logy potřebné pro operace z velké tabulky logů.

mysqldump -u root -p app logs \
--where="created_at >= NOW() - INTERVAL 30 DAY" \
> logs_last_30days.sql

Případ 2: Migrace pouze aktivních uživatelů (do nového prostředí)

mysqldump -u root -p service users \
--where="status = 'active'" \
> active_users.sql

Případ 3: Extrahovat pouze data specifického uživatele pro vyšetřování

mysqldump -u root -p crm payments \
--where="user_id = 42" \
> payments_user_42.sql

Případ 4: Rozdělení dumpů podle rozsahu ID (pro velké datové sady)

mysqldump -u root -p mydb orders --where="id BETWEEN 1 AND 500000" > part1.sql
mysqldump -u root -p mydb orders --where="id BETWEEN 500001 AND 1000000" > part2.sql

Jedná se o praktický přístup běžně používaný pro velmi velké tabulky.

Nejlepší postupy (doporučená nastavení)

• Kombinovat s --single-transaction Pro InnoDB můžete předejít zamykání a zároveň zachovat konzistentní zálohu.
• Použít --quick ke snížení využití paměti
• Ověřte, že sloupce dumpu mají indexy Pokud je WHERE pomalé, často je to kvůli chybějícímu indexu.
• Komprimovat pomocí gzip ke snížení velikosti souboru Příklad: mysqldump ... | gzip > backup.sql.gz
• Buďte opatrní při spouštění během pracovní doby Protože to může způsobit zatížení, doporučují se noční hodiny nebo údržbová okna.

4. Klíčové body při obnově

I když byl dump soubor extrahován s podmínkou WHERE, základní postup obnovy je stejný jako u běžné obnovy mysqldump. Nicméně, protože obsahuje „pouze vybrané záznamy“, je třeba dbát na několik bodů.

Postup obnovy z podmíněného dumpu

Nejstandardnější metoda obnovy je:

mysql -u root -p database_name < dump.sql

Když spustíte tento příkaz, CREATE TABLE a INSERT příkazy obsažené ve výstupu mysqldump jsou aplikovány na databázi tak, jak jsou.

Nicméně u dumpů filtrovaných pomocí WHERE je třeba věnovat pozornost následujícím bodům.

Poznámky při obnově dumpu filtrovaného pomocí WHERE

1. Může dojít ke konfliktu s existujícími daty v původní tabulce

Podmíněný dump extrahuje „pouze některé záznamy“.

Například:

• Cílová tabulka již obsahuje stejný primární klíč (id)
• Částečný INSERT způsobuje duplicity

V takových případech můžete během importu vidět chyby jako tato:

ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'

→ Protiopatření

• Předem TRUNCATE cílové tabulky, pokud je to potřeba
• Upravit SQL tak, aby bylo možné použít INSERT IGNORE nebo ON DUPLICATE KEY UPDATE
• Ověřit, že cíl je v první řadě „prázdná tabulka“

Protože mysqldump ve výchozím nastavení generuje INSERT příkazy, musíte být opatrní ohledně duplicit.

2. Dbejte na omezení cizích klíčů

Podmíněný dump automaticky neextrahuje všechny související tabulky najednou.

Příklad:

• Extrahovat pouze tabulku users pomocí WHERE
• Ale tabulka orders, která odkazuje na user_id, není přítomna

V tomto případě může během obnovy nastat chyba cizího klíče.

→ Protiopatření

• Dočasně zakázat kontrolu cizích klíčů pomocí SET FOREIGN_KEY_CHECKS=0;
• V případě potřeby dumpovat související tabulky se stejnými podmínkami
• Předem pochopit, zda je pro váš případ potřeba referenční integrita

3. Dbejte na rozdíly ve schématu (migrace vývoj vs. produkce)

Pokud se struktury tabulek liší mezi vývojem a produkcí, mohou během obnovy nastat chyby.

Příklady:

• Sloupec A existuje lokálně, ale byl odstraněn v produkci
• Produkce má NOT NULL, ale data v dumpu obsahují NULL
• Pořadí sloupců nebo datové typy se liší

→ Protiopatření

• Ověřit předem pomocí SHOW CREATE TABLE table_name;
• V případě potřeby použít --no-create-info (vyloučit schéma) a načíst pouze data
• Sjednotit schémata před dumpováním a obnovou

Použití pro diferenční zálohy a migrace

Dumpy filtrované pomocí WHERE jsou vysoce efektivní, když chcete „přenést pouze data, která potřebujete, do jiného prostředí“.

1. Migrovat pouze požadovaný rozsah do testovacího prostředí

• Pouze posledních 30 dní logů
• Pouze aktivní uživatelé
• Pouze prodejní období, které chcete ověřit

Tyto extrakce také výrazně přispívají ke snížení velikosti testovacích databází.

2. Archivovat stará data

Pokud se produkční databáze zvětšuje, můžete extrahovat pouze stará data a uložit je odděleně takto:

mysqldump -u root -p mydb logs \
--where="created_at < '2023-01-01'" \
> logs_archive_2022.sql

3. Poznámky k slučování

Pokud spojíte více podmíněných dumpů a načtete je do jedné tabulky, musíte věnovat velkou pozornost primárním klíčům a konzistenci.

Shrnutí: Dumpy filtrované pomocí WHERE jsou výkonné, ale při obnově buďte opatrní

Možnost WHERE v mysqldump je velmi pohodlná, ale při obnovách byste si měli pamatovat následující body:

• Duplicitní záznamy v cílové/původní tabulce
• Omezení cizích klíčů
• Neshody ve schématu
• Potenciální problémy s konzistencí způsobené filtrováním

To však, pokud ovládáte podmíněné dumpy, vaše každodenní zálohy, archivace a migrace dat se stanou výrazně efektivnějšími.

5. Řešení problémů / časté otázky

mysqldump vypadá jako jednoduchý nástroj, ale v kombinaci s podmínkami WHERE se mohou objevit neočekávané chyby v závislosti na vašem běhovém prostředí, struktuře dat a nastavení oprávnění. Tato sekce systematicky vysvětluje běžné reálné problémy a jak je řešit.

Běžné chyby a opravy

1. Nedostatečná oprávnění (Přístup odmítnut)

mysqldump: Got error: 1044: Access denied for user ...

Hlavní příčiny

• Chybějící oprávnění SELECT
• Další oprávnění mohou být vyžadována, pokud jsou zahrnuty triggery nebo pohledy
• Selhání při pokusu o dump systémové databáze mysql

Jak opravit

• Alespoň udělte oprávnění SELECT na cílových tabulkách
• Pokud jsou pohledy → SHOW VIEW
• Pokud jsou triggery → TRIGGER
• Je-li to možné, vytvořte dedikovaného uživatele pro zálohování

2. Filtr WHERE se neaplikuje a vše se dumpuje

Příčiny

• Nesprávné uvozovky
• Speciální znaky jsou interpretovány shellem
• Výraz neodpovídá sloupci (nesoulad formátu řetězce/ data)

Příklad (častá chyba)

--where=status='active'

Správná forma

--where="status = 'active'"

Jak opravit

• Používejte dvojité uvozovky na vnější úrovni a jednoduché uvozovky uvnitř
• Stejně postupujte při použití LIKE, > nebo < (zabalte do uvozovek)
• Zkontrolujte, že formát data odpovídá tomu, jak je uložen v DB

3. Velikost dumpu je neobvykle velká / zpracování je pomalé

Příčiny

• Chybí index na sloupci použitém v podmínce WHERE
• Používání neprefixových shod jako LIKE ‘%keyword’
• Podmínky jsou příliš složité
• Prohledávání velké tabulky bez indexů

Jak opravit

• Zvažte přidání indexu na sloupce použité v WHERE
• U velkých tabulek rozdělte dumpy do více běhů podle rozsahu ID
• Vždy používejte --quick ke snížení zatížení paměti
• Spouštějte to v noci nebo během hodin s nízkým provozem

4. Poškozený text (problémy s kódováním znaků)

Příčiny

• Výchozí znakové sady se liší podle prostředí
• Znaková sada při dumpu a při obnově se neshoduje
• Míchání utf8 a utf8mb4

Jak opravit

Vždy při dumpování specifikujte znakovou sadu:

--default-character-set=utf8mb4

※ Použití stejného nastavení při obnově pomáhá zabránit poškozenému textu.

5. Nelze importovat kvůli duplicitnímu záznamu (duplicitní primární klíč)

Protože podmíněné dumpy extrahují „pouze požadované záznamy“, můžete narazit na chyby duplicit, když:

• Existující tabulka již obsahuje stejný ID
• Pokusíte se sloučit dumpy a vzniknou duplicity

Jak opravit

• TRUNCATE (vyprázdněte) cílovou tabulku
• Upravte SQL podle potřeby a změňte na INSERT IGNORE
• Při slučování zkontrolujte duplicity před načtením

Výkonové a provozní upozornění

Základní strategie pro velké datové sady

• Rozdělte dumpy podle rozsahu ID
• Rozdělte do více souborů podle rozsahu dat
• Komprimujte pomocí gzip nebo pigz, pokud je potřeba
• Spouštějte během hodin s nízkým zatížením, například pozdě v noci

O rizicích zamykání

MyISAM zamyká tabulky během dumpů.
Pro InnoDB se doporučuje následující volba:

--single-transaction

This helps you extract consistent data while mostly avoiding locks.

Kontrolní seznam operací

• Ověřte podmínku WHERE pomocí SELECT dotazu předem
• Zkontrolujte volné místo na disku před dumpováním
• Vždy ukládejte soubory dumpu bezpečně (šifrujte a/nebo komprimujte)
• Ověřte, že schéma cílové tabulky odpovídá

Často kladené otázky (FAQ)

Q1. Lze podmínky WHERE použít napříč více tabulkami?

Ne.
Filtrování WHERE v mysqldump funguje na tabulku.
Nelze použít JOIN.

Q2. Je v pořádku použít LIKE v podmínkách WHERE?

Ano, můžete. Nicméně neprefixové shody jako %keyword nemohou využít indexy a budou pomalejší.

Q3. Můžu dumpovat schéma, ale filtrovat data pomocí WHERE?

Pokud potřebujete jen schéma, použijete --no-data, takže podmínka WHERE obvykle není potřeba.

Q4. Při obnově podmíněného dumpu dostanu chybu cizího klíče

Spusťte následující pro dočasné vypnutí omezení:

SET FOREIGN_KEY_CHECKS=0;

Buďte však opatrní, abyste neporušili konzistenci.

Q5. Jaký je nejlepší přístup, když velká data trvají příliš dlouho?

• Zkontrolujte, zda jsou sloupce použité v WHERE indexovány
• Rozdělte dumpy do více souborů pomocí rozsahů ID
• Použijte --quick
• Přesuňte čas provádění na pozdní noc. Toto jsou nejefektivnější přístupy v reálných operacích.

6. Shrnutí

mysqldump je jedním z nejjednodušších nástrojů pro zálohování v MySQL a kombinací s volbou --where můžete jít nad rámec jednoduchých záloh a použít jej jako „flexibilní nástroj pro extrakci dat.“

V reálných operacích často potřebujete extrahovat jen konkrétní období, jen určitý stav nebo rozdělit velká data na menší části. V takových situacích je --where mimořádně výkonný a významně přispívá k efektivní správě dat.

Klíčové body v tomto článku

• Základní syntaxe mysqldump Jednoduché zálohy jsou možné zadáním pouze uživatelského jména a názvu databáze.
• Podmíněné dumpy s --where Extrahujte jen požadované záznamy, podobně jako SQL klauzule WHERE.
• Praktické příklady podmínek Podporuje mnoho vzorů filtrování: časové intervaly, stav, rozsahy ID, LIKE a kombinované podmínky.
• Upozornění při obnově Při načítání částečných dat dejte pozor na duplikáty a omezení cizích klíčů.
• Časté problémy a opatření Pokrývá nedostatečná oprávnění, neaplikování WHERE, pokles výkonu, problémy s kódováním a duplikaci primárních klíčů.

Výhody dumpů filtrovaných pomocí WHERE

• Rychlejší zálohy Není nutné zálohovat vše—filtrování snižuje dobu zpracování.
• Menší velikost souborů Zvláště efektivní u velkých tabulek.
• Snadnější migrace dat do testovacích/staging prostředí Načtěte jen data, která potřebujete.
• Užitečné pro archivaci Umožňuje snadnější správu starých dat jako samostatných souborů.

Co vyzkoušet dál

Jakmile pochopíte dumpy filtrované pomocí WHERE, můžete zvážit následující kroky:

• Automatizace záloh pomocí cron (Linux) s využitím zálohovacích skriptů
• Automatická komprese kombinovaná s gzip nebo zip
• Použití rychlejších fyzických nástrojů pro zálohování místo mysqldump (např. Percona XtraBackup)
• Návrh zálohování pro rozsáhlá prostředí

mysqldump je jednoduchý, ale s správným pochopením a používáním výrazně rozšiřuje vaše možnosti návrhu záloh.