Zálohy MySQL: mysqldump, XtraBackup a Point-in-Time Recovery

Proč zálohy jsou „teď", ne „později"

Vývojář spustí migraci na production — a SQL skript bez správné WHERE klauzule poškodí 40 % tabulky objednávek. Skutečný případ z naší praxe. Panika, klient ztrácí peníze každou minutu, manažeři volají nepřetržitě.

Výsledek? 45 minut — a 100 % dat obnoveno. Zachránil to automatický snapshot vytvořený 15 minut před incidentem. Většinu času nezabralo samotné obnovení, ale nasazení dumpu na samostatný server pro selektivní extrakci poškozených řádků.

To je happy end. Ale je možný pouze tehdy, když jsou zálohy nastaveny správně. Mnoho klientů, kteří k nám přichází, má tři měsíce starou zálohu někde na stejném serveru. Nebo cron job, který tiše selhává už půl roku.

Tento průvodce je o tom, jak nastavit zálohy MySQL, abyste mohli klidně spát. Od jednoduchého mysqldump po production-ready strategie s inkrementálními zálohami a point-in-time recovery.

Logické a fyzické zálohy: jaký je rozdíl

Než si vyberete nástroj — musíte pochopit, co vlastně dělá. Zálohy MySQL jsou dvou typů a každý má své silné stránky.

Logické zálohy

To je SQL dump: sada příkazů CREATE TABLE, INSERT INTO, které znovu vytvoří strukturu a data databáze od nuly. Nástroje — mysqldump, mysqlpump, phpMyAdmin.

Výhody: přenositelnost (lze přenést mezi verzemi MySQL/MariaDB), čitelnost (je to prostý text), možnost vybrat konkrétní tabulky. Nevýhody: pomalé obnovení u velkých databází, zatížení serveru během dumpu.

Fyzické zálohy

To je kopie souborů databáze přímo z disku. Nástroje — Percona XtraBackup, Mariabackup, LVM snapshots.

Výhody: rychlé obnovení (prostě zkopírujete soubory zpět), minimální zatížení během zálohování. Nevýhody: vázanost na verzi MySQL, nelze obnovit jednotlivé tabulky (obvykle).

Plné, inkrementální a diferenční

Kromě způsobu kopírování se zálohy liší podle rozsahu:

• Plná — kopie celé databáze. Nejjednodušší, ale největší velikost.
• Inkrementální — pouze změny od poslední zálohy. Rychlá a kompaktní, ale pro obnovení je potřeba plná + všechny inkrementy v sekvenci.
• Diferenční — změny od poslední plné zálohy. Kompromis mezi velikostí a jednoduchostí obnovení.

Pro většinu projektů je optimální kombinace: plná záloha jednou týdně + denní inkrementální.

Pravidlo 3-2-1: zlatý standard záloh

Jedna z nejčastějších chyb je ukládat zálohu na stejný server, kde běží databáze. Disk umře — a ztratíte databázi i zálohu najednou.

Strategie 3-2-1 to řeší:

• 3 kopie dat (originál + 2 zálohy)
• 2 typy médií (např. lokální disk + cloudové úložiště)
• 1 kopie offsite (fyzicky na jiném místě/datovém centru)

Hostiserver nabízí Backup Hosting přesně pro tento účel — specializované úložiště na bázi HDD polí s vysokou odolností proti selhání, přístupné přes FTP/SFTP/Rsync. Nejčastěji jej využívají eCommerce projekty, mediální portály a SaaS platformy. Pomáháme nastavit automatický přenos kopií ihned po jejich vytvoření na lokálním serveru.

Retention Policy: schéma GFS

Používáme schéma „Grandfather-Father-Son" (GFS) — rovnováha mezi hloubkou archivu a náklady na úložiště:

Pro kritická data (finanční transakce, zdravotní záznamy) se doba uchování denních kopií prodlužuje na 30 dní.

mysqldump: spolehlivá klasika

mysqldump je nástroj, který je na každém serveru s MySQL. Jednoduchý, léty prověřený a pro databáze do 10–20 GB — zcela dostačující.

Základní záloha

mysqldump -u root -p --single-transaction --routines --triggers --events --hex-blob mydb > /backup/mydb_$(date +%Y%m%d_%H%M).sql

Rozeberme parametry — to je standard, který v Hostiserveru používáme ve výchozím nastavení:

• --single-transaction — povinné pro InnoDB. Vytváří zálohu bez zamykání tabulek, web pokračuje v práci.
• --routines — zahrnuje uložené procedury a funkce (business logika na úrovni DB).
• --triggers — zahrnuje triggery.
• --events — zahrnuje naplánované události MySQL.
• --hex-blob — správné zpracování binárních dat (BLOB polí). Bez toho se binární data mohou při obnovení poškodit.

Pokud potřebujete replikaci nebo PITR, přidejte --master-data=2 — zaznamená pozici binárního logu jako komentář v dumpu.

Záloha s kompresí

mysqldump -u root -p --single-transaction mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

SQL dumpy se komprimují 5–10×. 2 GB databáze se stane souborem 200–400 MB.

Záloha všech databází najednou

mysqldump -u root -p --single-transaction --all-databases --routines --triggers | gzip > /backup/all_db_$(date +%Y%m%d).sql.gz

Obnovení

# Z běžného souboru
mysql -u root -p mydb < /backup/mydb_20260302.sql
# Z gzip archivu
gunzip < /backup/mydb_20260302.sql.gz | mysql -u root -p mydb

Percona XtraBackup: zálohy bez zastavení databáze

Pro production servery s databázemi od 50 GB, kde i pár sekund zamčení je problém, existuje Percona XtraBackup. Je to open-source nástroj, který dělá „horké" fyzické zálohy InnoDB bez zastavení MySQL.

Jak to funguje: XtraBackup kopíruje soubory InnoDB a současně zapisuje redo logy. Poté ve fázi prepare aplikuje tyto logy pro získání konzistentního stavu. To vše — zatímco databáze pokračuje ve zpracování dotazů.

Plná záloha

# Vytvoření zálohy
xtrabackup --backup --target-dir=/backup/full
# Příprava (nutné před obnovením!)
xtrabackup --prepare --target-dir=/backup/full
# Obnovení
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Inkrementální záloha

# Nejprve plná záloha
xtrabackup --backup --target-dir=/backup/full
# Pondělní inkrement
xtrabackup --backup --target-dir=/backup/inc_mon --incremental-basedir=/backup/full
# Úterní inkrement
xtrabackup --backup --target-dir=/backup/inc_tue --incremental-basedir=/backup/inc_mon

Inkrementální zálohy kopírují pouze změněné stránky InnoDB — proto jsou mnohem menší a rychlejší než plné zálohy.

Automatizace: zálohy, které fungují bez vás

Ruční zálohy jsou zálohy, na které se zapomíná. Automatizace přes cron je minimum, které by mělo být na každém serveru.

Denní záloha přes cron

# Úprava crontab
crontab -e
# Záloha každý den ve 3:00 ráno
0 3 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

Zálohovací skript

#!/bin/bash
# /usr/local/bin/backup_mysql.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M)
RETENTION_DAYS=14
# Vytvoření zálohy
mysqldump -u backup_user -p'secure_password' \
  --single-transaction --routines --triggers --events --hex-blob \
  --all-databases | gzip > "$BACKUP_DIR/all_db_$DATE.sql.gz"
# Kontrola úspěchu
if [ $? -eq 0 ]; then
  echo "[$DATE] Záloha vytvořena úspěšně: $(du -h $BACKUP_DIR/all_db_$DATE.sql.gz | cut -f1)"
else
  echo "[$DATE] CHYBA: záloha nebyla vytvořena!" >& 2
  # Zde lze přidat odesílání alertu
fi
# Smazání starých záloh
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$DATE] Smazány zálohy starší než $RETENTION_DAYS dní"

[mysqldump]
user=backup_user
password=secure_password

Monitoring záloh

Zálohovací systém, který mlčí, je zálohovací systém, který může být rozbitý. Přidejte kontroly:

• Alert, pokud je soubor zálohy menší než očekávaná velikost (prázdný dump = problém)
• Alert, pokud se záloha neobjevila podle rozvrhu
• Týdenní kontrola integrity: gunzip -t backup.sql.gz

Point-in-Time Recovery: obnovení do konkrétní sekundy

Představte si: ve 14:35 vývojář omylem spustí UPDATE bez WHERE a přepíše e-maily všech zákazníků. Poslední plná záloha je ze 3:00 ráno. Bez PITR ztratíte vše, co se stalo mezi 3:00 a 14:35 — objednávky, registrace, platby.

Point-in-Time Recovery to řeší. Princip: MySQL zapisuje každou změnu do binary logu. Po obnovení plné zálohy „přehrajete" binary logy do požadovaného okamžiku.

PITR je standardní praxe pro Enterprise klienty a projekty, kde ztráta i hodiny dat je kritická (eCommerce, finance, SaaS). Konfigurujeme rotaci binárních logů a jejich pravidelný přenos na Backup Hosting. To umožňuje v případě havárie „nakotit" všechny změny, které se staly po poslední noční záloze.

Konfigurace binary logu

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 14
max_binlog_size = 100M

Obnovení do konkrétního času

# 1. Obnovit plnou zálohu
mysql -u root -p mydb < /backup/full_20260302_0300.sql
# 2. Přehrát binary logy do okamžiku PŘED chybou
mysqlbinlog --stop-datetime="2026-03-02 14:34:59" \
  /var/log/mysql/mysql-bin.000042 \
  /var/log/mysql/mysql-bin.000043 | mysql -u root -p mydb

Výsledek: databáze obnovena se všemi daty do 14:34:59 — sekundu před chybou.

Záloha, která nebyla testována, neexistuje

To není přehánění. Viděli jsme případy, kdy klient měsíce dělal zálohy přes cron, a pak při obnovení se ukázalo, že soubory jsou prázdné — protože se změnilo heslo MySQL a mysqldump tiše selhával.

Jak ověřovat

• Denně: kontrola velikosti souboru (není nulový? není anomálně malý?)
• Týdně: testovací obnovení na staging server
• Měsíčně: plné testovací obnovení s ověřením dat

Automatická verifikace

# Kontrola, že soubor není prázdný a je větší než 1 MB
BACKUP_FILE="/backup/mysql/all_db_$(date +%Y%m%d)*.sql.gz"
MIN_SIZE=1048576  # 1 MB v bajtech
FILE_SIZE=$(stat -c%s $BACKUP_FILE 2>/dev/null || echo 0)
if [ "$FILE_SIZE" -lt "$MIN_SIZE" ]; then
  echo "ALERT: Záloha podezřele malá nebo chybí!" | mail -s "Backup Alert" admin@example.com
fi

Který nástroj zvolit

Zde je porovnání hlavních nástrojů pro zálohování MySQL/MariaDB pro různé scénáře:

5 chyb se zálohami, které vidíme neustále

1. Záloha na stejném disku jako databáze. Disk umře — ztratíte vše. Vždy kopírujte zálohy na samostatný nosič nebo do cloudového úložiště.

2. Nikdo zálohy netestuje. Cron tiše selhává měsíce. Přidejte monitoring velikosti souborů a alerty na chyby.

3. Záloha bez --single-transaction. mysqldump zamyká tabulky — web leží. Pro InnoDB vždy přidejte tento příznak.

4. Zapomněli na procedury a triggery. mysqldump ve výchozím nastavení NEZAHRNUJE stored procedures, triggers a events. Použijte --routines --triggers --events.

5. Heslo v plaintext v crontab. Kdokoli s přístupem k serveru uvidí heslo přes ps aux. Použijte ~/.my.cnf s právy 600.

Často kladené otázky