Il modo più sicuro per eseguire mysqldump su un sistema live con letture e scritture attive?

Tutti i dati sono InnoDB

Questo è ciò che ti darà un'istantanea puntuale esatta dei dati:

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

--single-transaction produce un checkpoint che consente al dump di acquisire tutti i dati prima del checkpoint durante la ricezione delle modifiche in arrivo. Quelle modifiche in arrivo non diventano parte del dump. Ciò garantisce lo stesso punto nel tempo per tutte le tabelle.

--routines scarica tutte le procedure memorizzate e le funzioni memorizzate

--triggers scarica tutti i trigger per ogni tabella che li ha

Tutti i dati sono MyISAM o un mix di InnoDB/MyISAM

Dovrai imporre un blocco di lettura globale, eseguire il mysqldump e rilasciare il blocco globale

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

Provalo !!!

AGGIORNAMENTO 22-06-2012 08:12 EDT

Dato che hai <50 MB di dati totali, ho un'altra opzione. Invece di lanciare un comando SLEEP in background per mantenere il blocco di lettura globale per 86400 secondi (quelle 24 ore) solo per ottenere l'ID del processo e uccidere all'esterno, proviamo a impostare un timeout di 5 secondi in mysql anziché nel sistema operativo:

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

Questo è un approccio più pulito e semplice per database molto piccoli.

• Per le tabelle InnoDB, dovresti usare --single-transaction opzione, come menzionato in un'altra risposta.
• Per MyISAM c'è --lock-tables .

Consulta la documentazione ufficiale qui

Ecco come l'ho fatto. Dovrebbe funzionare in tutti i casi poiché utilizza 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 !"

La shell sleep Il comando è solo per assicurarsi che l'attività in background che esegue il comando di blocco mysql venga eseguita prima dell'avvio di mysqldump. Potresti ridurlo a 1 secondo e dovrebbe comunque andare bene. Aumentalo a 30 secondi e prova a inserire un valore in qualsiasi tabella da un altro client durante quei 30 secondi vedrai che è bloccato.

Ci sono 2 vantaggi nell'usare questo blocco manuale in background, invece di usare il mysqldump opzioni --single-transaction e --lock-tables :

1. Questo blocca tutto, se hai tabelle miste MyISAM/InnoDB.
2. Puoi eseguire altri comandi oltre a mysqldump durante lo stesso periodo di blocco. È utile, ad esempio, quando si imposta la replica su un nodo master, perché è necessario ottenere la posizione del log binario con SHOW MASTER STATUS; allo stato esatto del dump che hai creato (prima di sbloccare il database), per poter creare uno slave di replica.