Der sicherste Weg, mysqldump auf einem Live-System mit aktiven Lese- und Schreibvorgängen auszuführen?

Alle Daten sind InnoDB

So erhalten Sie eine genaue Momentaufnahme der Daten:

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

--single-transaction erzeugt einen Checkpoint, der es dem Dump ermöglicht, alle Daten vor dem Checkpoint zu erfassen, während eingehende Änderungen empfangen werden. Diese eingehenden Änderungen werden nicht Teil des Dumps. Dadurch wird für alle Tabellen der gleiche Point-in-Time sichergestellt.

--routines gibt alle gespeicherten Prozeduren und gespeicherten Funktionen aus

--triggers gibt alle Trigger für jede Tabelle aus, die sie enthält

Alle Daten sind MyISAM oder eine Mischung aus InnoDB/MyISAM

Sie müssen eine globale Lesesperre auferlegen, mysqldump ausführen und die globale Sperre aufheben

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

Probieren Sie es aus !!!

UPDATE 2012-06-22 08:12 EDT

Da Sie <50 MB Gesamtdaten haben, habe ich eine andere Option. Anstatt einen SLEEP-Befehl im Hintergrund zu starten, um die globale Lesesperre für 86400 Sekunden (diese 24 Stunden) aufrechtzuerhalten, nur um die Prozess-ID zu erhalten und draußen zu beenden, versuchen wir, ein 5-Sekunden-Timeout in mysql anstatt im Betriebssystem festzulegen:

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

Dies ist ein sauberer und einfacherer Ansatz für sehr kleine Datenbanken.

• Für InnoDB-Tabellen sollten Sie den --single-transaction verwenden Option, wie in einer anderen Antwort erwähnt.
• Für MyISAM gibt es --lock-tables .

Siehe die offizielle Dokumentation hier

So habe ich es gemacht. Es sollte in allen Fällen funktionieren, da es FLUSH TABLES WITH READ LOCK verwendet .

#!/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 !"

Die Shell sleep Der Befehl soll nur sicherstellen, dass die Hintergrundaufgabe, die den mysql-Sperrbefehl ausführt, ausgeführt wird, bevor mysqldump gestartet wird. Sie könnten es auf 1 Sekunde reduzieren und es sollte immer noch in Ordnung sein. Erhöhen Sie es auf 30 Sekunden und versuchen Sie, während dieser 30 Sekunden Werte in eine beliebige Tabelle von einem anderen Client einzufügen, Sie werden sehen, dass es gesperrt ist.

Es gibt 2 Vorteile bei der Verwendung dieser manuellen Sperre im Hintergrund, anstatt den mysqldump zu verwenden Optionen --single-transaction und --lock-tables :

1. Dies sperrt alles, wenn Sie MyISAM/InnoDB-Tabellen gemischt haben.
2. Sie können zusätzlich zu mysqldump andere Befehle ausführen während der gleichen Sperrzeit. Dies ist beispielsweise nützlich, wenn Sie die Replikation auf einem Master-Knoten einrichten, da Sie die Position des Binärlogs mit SHOW MASTER STATUS; abrufen müssen im genauen Zustand des Dumps, den Sie erstellt haben (vor dem Entsperren der Datenbank), um einen Replikations-Slave erstellen zu können.