mysqldump es una herramienta de respaldo lógico incluida con MySQL. Su principio de funcionamiento consiste en conectarse a la base de datos MySQL a través del protocolo, consultar los datos que necesitan ser respaldados y convertir los datos consultados en sentencias INSERT correspondientes. Cuando necesitamos restaurar estos datos, basta con ejecutar estas sentencias INSERT para restaurarlos.

Comandos de respaldo

Formato del comando

1. mysqldump [opciones] nombre_base_datos [nombre_tabla] > nombre_script
2. mysqldump [opciones] --database nombre_base_datos [opciones nombre_tabla] > nombre_script
3. mysqldump [opciones] --all-databases [opciones] > nombre_script

Descripción de las opciones

Comandos de restauración

Comando a nivel de sistema

mysqladmin -uroot -p create nombre_base_datos
mysql -uroot -p nombre_base_datos < /backup/mysqldump/nombre_base_datos.db

Nota: Si la base de datos 'nombre_base_datos' no existe, debe crearse antes de importar. Además, debe tener el mismo nombre que la base de datos en 'nombre_base_datos.db' para poder importar.

Forma 'source'

mysql > use nombre_base_datos;
mysql > source /backup/mysqldump/nombre_base_datos.db;

Principio de funcionamiento de mysqldump

Flujo de respaldo

1. Llamada a FLUSH TABLES WITH READ LOCK (FWRL), prohibiendo globalmente la lectura y escritura.
2. Activación de lectura de instantánea (snapshot read) para obtener una instantánea del período actual (solo efectivo para InnoDB).
3. Respaldo de datos de tablas no InnoDB (*.frm, *.myi, *.myd, etc.).
4. Una vez completado el respaldo de tablas no InnoDB, se libera FTWRL.
5. Respaldo de datos de tablas InnoDB una por una.
6. Respaldo completado.

Ejecución de mysqldump y análisis de logs de respaldo

# Sentencia de ejecución
[root@localhost backup]# mysqldump -uroot -proot -h127.0.0.1 --all-databases --single-transaction --routines --events --triggers --master-data=2 --hex-blob --default-character-set=utf8mb4 --flush-logs --quick > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# tail -f /var/lib/mysql/localhost.log

# Primer paso:
FLUSH /*!40101 LOCAL */ TABLES
# Aquí se refrescan las tablas.

# Segundo paso:
FLUSH TABLES WITH READ LOCK
# Debido a que se activó --master-data=2, se necesita bloquear toda la base de datos con 'flush tables with read lock' para registrar el punto 'master_log_file' y 'master_log_pos' del momento.
# Aquí surge una duda:
# La ejecución de la operación 'flush tables' y la adición de un bloqueo de lectura global parecen ser redundantes. ¿Por qué no se añade el bloqueo durante la primera operación 'flush tables'?
# En resumen, se hace para evitar que operaciones de transacciones largas pospongan indefinidamente la operación FLUSH TABLES WITH READ LOCK, bloqueando al mismo tiempo otras operaciones de clientes.

# Tercer paso:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
# Propósito del parámetro --single-transaction: establecer el nivel de aislamiento de la transacción en REPEATABLE READ.
# Esto garantiza que todas las consultas idénticas dentro de una transacción lean los mismos datos.
# Por lo tanto, se garantiza aproximadamente que durante el volcado, si un hilo del motor InnoDB modifica los datos de la tabla y confirma, esto no afectará los datos del hilo de volcado. Sin embargo, esto no es suficiente, y se debe considerar la siguiente línea.

# Cuarto paso:
START TRANSACTION /*!40101 WITH CONSISTENT SNAPSHOT */
# Obtiene una instantánea de la base de datos actual. Esto es determinado por --single-transaction en mysqldump.
# WITH CONSISTENT SNAPSHOT garantiza que al inicio de la transacción, el primer resultado de la consulta será los datos A al inicio de la transacción.
# Incluso si otros hilos modifican los datos a B en ese momento, el resultado de la consulta seguirá siendo A.
# En resumen, se inicia una transacción y se ejecuta una operación SELECT en todas las tablas. Esto garantiza que durante el respaldo, la ejecución de 'select * from table' en cualquier momento obtendrá datos consistentes con los datos en el momento de ejecutar START TRANSACTION WITH CONSISTENT SNAPSHOT.
# ¡Atención! WITH CONSISTENT SNAPSHOT solo es efectivo bajo el nivel de aislamiento RR.

# Quinto paso:
SHOW MASTER STATUS
# Esto es determinado por --master-data. Registra la información de estado del binlog al inicio del respaldo, incluyendo MASTER_LOG_FILE y MASTER_LOG_POS.

Aquí es necesario distinguir claramente entre master-data y dump-slave:
master-data:
--master-data=2: Registra el binlog y el punto POS del servidor maestro durante el volcado, y comenta esta línea en el archivo de volcado.
--master-data=1: Registra el binlog y el punto POS del servidor maestro durante el volcado, y no comenta esta línea en el archivo de volcado, lo que significa que se ejecutará durante la restauración.

dump-slave:
--dump-slave=2: Durante el volcado, se realiza en un servidor esclavo. El proceso mysqldump también se ejecuta en el esclavo, registrando el binlog y el punto POS del servidor maestro en ese momento, y comenta esta línea en el archivo de volcado.
--dump-slave=1: Durante el volcado, se realiza en un servidor esclavo. El proceso mysqldump también se ejecuta en el esclavo, registrando el binlog y el punto POS del servidor maestro en ese momento, y no comenta esta línea en el archivo de volcado.

# Sexto paso:
UNLOCK TABLES
# Libera el bloqueo.

Diferencias en el respaldo de motores InnoDB y MyISAM con mysqldump

Para motores que admiten transacciones como InnoDB, el parámetro --single-transaction se utiliza para garantizar la consistencia de los datos durante el respaldo.

--single-transaction realiza en realidad las siguientes dos operaciones:

1. Al principio, establece el nivel de aislamiento de la transacción de la sesión en REPEATABLE READ.
2. Luego, inicia una transacción (ejecuta BEGIN) y finaliza la transacción al finalizar el respaldo (ejecuta COMMIT).

Con estas dos operaciones, los datos leídos durante el proceso de respaldo son los datos del momento en que se inició el respaldo (un punto único). Se puede entender que para el motor InnoDB, con este parámetro, los datos a respaldar se fijan al inicio del respaldo. Las transacciones confirmadas durante el proceso de respaldo no son visibles y no se respaldan.

Para motores que no admiten transacciones como MyISAM, solo se puede utilizar el bloqueo de tablas para garantizar la consistencia de los datos. Aquí hay dos situaciones:

1. Exportar toda la base de datos: use el parámetro --lock-all-tables. Esto iniciará un bloqueo de lectura global (ejecutando flush tables with read lock) al comienzo del respaldo. Otros sseiones pueden leer pero no actualizar datos. Como los datos no cambian durante el respaldo, los datos finales serán completamente consistentes.
2. Exportar una sola base de datos: use el parámetro --lock-tables. Esto bloqueará todas las tablas de esa base de datos al comienzo del respaldo. Otros sesiones pueden leer pero no actualizar ninguna tabla de esa base de datos, garantizando la consistencia de los datos de esa base de datos.