¿La forma más segura de realizar mysqldump en un sistema en vivo con lecturas y escrituras activas?

Todos los datos son InnoDB

Esto es lo que le dará una instantánea exacta de un punto en el tiempo de los datos:

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

--single-transaction produce un punto de control que permite que el volcado capture todos los datos antes del punto de control mientras recibe los cambios entrantes. Esos cambios entrantes no se convierten en parte del volcado. Eso asegura el mismo punto en el tiempo para todas las tablas.

--routines vuelca todos los procedimientos almacenados y funciones almacenadas

--triggers vuelca todos los disparadores para cada tabla que los tiene

Todos los datos son MyISAM o una mezcla de InnoDB/MyISAM

Tendrá que imponer un bloqueo de lectura global, ejecutar mysqldump y liberar el bloqueo global

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

Pruébalo !!!

ACTUALIZACIÓN 2012-06-22 08:12 EDT

Como tiene <50 MB de datos totales, tengo otra opción. En lugar de lanzar un comando SLEEP en segundo plano para mantener el bloqueo de lectura global durante 86400 segundos (esas 24 horas) solo para obtener la ID del proceso y matarlo afuera, intentemos configurar un tiempo de espera de 5 segundos en mysql en lugar de en el 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

Este es un enfoque más limpio y simple para bases de datos muy pequeñas.

• Para las tablas de InnoDB, debe usar el --single-transaction opción, como se menciona en otra respuesta.
• Para MyISAM hay --lock-tables .

Ver la documentación oficial aquí

Así es como lo hice. Debería funcionar en todos los casos ya que usa 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 concha sleep El comando es solo para asegurarse de que la tarea en segundo plano que ejecuta el comando de bloqueo de mysql se ejecute antes de que se inicie mysqldump. Puede reducirlo a 1 segundo y aún debería estar bien. Aumente a 30 segundos e intente insertar valores en cualquier tabla de otro cliente durante esos 30 segundos, verá que está bloqueado.

Hay 2 ventajas en usar este bloqueo de fondo manual, en lugar de usar el mysqldump opciones --single-transaction y --lock-tables :

1. Esto bloquea todo, si ha combinado tablas MyISAM/InnoDB.
2. Puede ejecutar otros comandos además del mysqldump durante el mismo período de bloqueo. Es útil, por ejemplo, al configurar la replicación en un nodo maestro, porque necesita obtener la posición del registro binario con SHOW MASTER STATUS; en el estado exacto del volcado que creó (antes de desbloquear la base de datos), para poder crear un esclavo de replicación.