Nejbezpečnější způsob, jak provádět mysqldump na živém systému s aktivním čtením a zápisem?

Všechna data jsou InnoDB

To je to, co vám poskytne přesný momentální snímek dat:

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

--single-transaction vytváří kontrolní bod, který umožňuje výpisu zachytit všechna data před kontrolním bodem při přijímání příchozích změn. Tyto příchozí změny se nestávají součástí skládky. To zajišťuje stejný časový okamžik pro všechny tabulky.

--routines vypíše všechny uložené procedury a uložené funkce

--triggers vypíše všechny spouštěče pro každou tabulku, která je obsahuje

Všechna data jsou MyISAM nebo mix InnoDB/MyISAM

Budete muset zavést globální zámek čtení, provést mysqldump a uvolnit globální zámek

mysql -uuser -ppass -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
sleep 5
mysql -uuser -ppass -ANe"SHOW PROCESSLIST" | grep "SELECT SLEEP(86400)" > /tmp/proclist.txt
SLEEP_ID=`cat /tmp/proclist.txt | awk '{print $1}'`
echo "KILL ${SLEEP_ID};" > /tmp/kill_sleep.sql
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql
mysql -uuser -ppass -A < /tmp/kill_sleep.sql

Vyzkoušejte to !!!

AKTUALIZACE 2012-06-22 08:12 EDT

Protože máte <50 MB celkových dat, mám další možnost. Namísto spouštění příkazu SLEEP na pozadí pro udržení globálního zámku čtení po dobu 86 400 sekund (tedy 24 hodin), jen abychom získali ID procesu a zabili venku, zkusme nastavit 5 sekundový časový limit v mysql, nikoli v OS:

SLEEP_TIMEOUT=5
SQLSTMT="FLUSH TABLES WITH READ LOCK; SELECT SLEEP(${SLEEP_TIMEOUT})"
mysql -uuser -ppass -Ae"${SQLSTMT}" &
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

Toto je čistší a jednodušší přístup pro velmi malé databáze.

• Pro tabulky InnoDB byste měli použít --single-transaction možnost, jak je uvedeno v jiné odpovědi.
• Pro MyISAM existuje --lock-tables .

Oficiální dokumentaci naleznete zde

Zde je návod, jak jsem to udělal. Mělo by to fungovat ve všech případech, protože používá FLUSH TABLES WITH READ LOCK .

#!/bin/bash

DB=example
DUMP_FILE=export.sql

# Lock the database and sleep in background task
mysql -uroot -proot $DB -e "FLUSH TABLES WITH READ LOCK; DO SLEEP(3600);" &
sleep 3

# Export the database while it is locked
mysqldump -uroot -proot --opt $DB > $DUMP_FILE

# When finished, kill the previous background task to unlock
kill $! 2>/dev/null
wait $! 2>/dev/null

echo "Finished export, and unlocked !"

Shell sleep Tento příkaz slouží pouze k tomu, aby se ujistil, že úloha na pozadí se spuštěným příkazem zamykání mysql bude provedena před spuštěním mysqldump. Můžete to zkrátit na 1 sekundu a stále by to mělo být v pořádku. Zvyšte jej na 30 sekund a zkuste vložit hodnoty do libovolné tabulky z jiného klienta, během těchto 30 sekund uvidíte, že je uzamčena.

Použití tohoto ručního zamykání na pozadí namísto použití mysqldump má 2 výhody možnosti --single-transaction a --lock-tables :

1. To vše uzamkne, pokud máte smíšené tabulky MyISAM/InnoDB.
2. Kromě příkazu mysqldump můžete spouštět i další příkazy ve stejné zamykací době. Je to užitečné například při nastavování replikace na hlavním uzlu, protože potřebujete získat pozici binárního protokolu pomocí SHOW MASTER STATUS; v přesném stavu výpisu, který jste vytvořili (před odemknutím databáze), abyste mohli vytvořit replikační slave.