La guía definitiva de mysqldump: copia de seguridad, restauración y automatización de bases de datos MySQL

mysqldump es una utilidad de línea de comandos incluida con MySQL y MariaDB que genera copias de seguridad lógicas serializando objetos de base de datos y datos como una secuencia de sentencias SQL. El archivo de volcado resultante puede recrear una base de datos idéntica en cualquier servidor compatible, lo que lo convierte en la herramienta estándar del sector para copias de seguridad, migraciones entre servidores, actualizaciones de versiones y flujos de trabajo de recuperación ante desastres.

A diferencia de las herramientas de copia de seguridad física como Percona XtraBackup o MySQL Enterprise Backup, mysqldump opera en la capa SQL — lee datos en vivo a través del protocolo MySQL y escribe SQL portátil y legible por humanos. Esta portabilidad es su mayor fortaleza y, a escala, su principal limitación.

Lo que mysqldump hace internamente

Cuando invocas mysqldump, el cliente se conecta al servidor MySQL, consulta el esquema de información y el diccionario de datos, y emite un flujo de sentencias `CREATE DATABASE`, `CREATE TABLE`, `INSERT` y DDL a la salida estándar. Redirige ese flujo a un archivo, una tubería o una utilidad de compresión.

Para tablas InnoDB con `–single-transaction`, mysqldump abre una transacción de lectura repetible antes de leer cualquier dato. Esto proporciona una instantánea consistente en un punto en el tiempo sin adquirir bloqueos de lectura globales — la base de datos permanece completamente escribible durante el volcado. Para tablas MyISAM, no existe tal mecanismo; mysqldump recurre a `FLUSH TABLES WITH READ LOCK`, que bloquea brevemente las escrituras.

Comprender esta distinción es fundamental antes de elegir mysqldump para cargas de trabajo en producción. Si tu esquema mezcla tablas InnoDB y MyISAM, `–single-transaction` por sí solo es insuficiente — necesitarás `–lock-all-tables` o una ventana de mantenimiento.

Requisitos previos y privilegios necesarios

Antes de ejecutar cualquier comando de volcado, verifica lo siguiente:

• MySQL o MariaDB está instalado y accesible (socket local o TCP/IP).
• El usuario de copia de seguridad tiene los privilegios mínimos requeridos:
• `SELECT` en todas las tablas de destino
• `LOCK TABLES` (requerido a menos que `–single-transaction` se use exclusivamente con InnoDB)
• `SHOW VIEW` para incluir vistas
• `TRIGGER` para incluir disparadores
• `PROCESS` cuando se usa `–single-transaction` en MySQL 8+
• `RELOAD` para `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` si necesitas coordenadas del registro binario para configuración de replicación

Crea un usuario de copia de seguridad dedicado en lugar de ejecutar volcados como root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Ejecutar mysqldump como root con una contraseña incrustada en el comando de shell expone las credenciales en los listados de procesos y el historial de shell — un riesgo de seguridad significativo en cualquier sistema compartido o multiusuario.

Sintaxis básica

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Referencia completa de opciones

Opciones de conexión

Opciones de alcance

Opciones de consistencia y bloqueo

Opciones de salida y contenido

mysqldump vs. métodos alternativos de copia de seguridad

Elegir la estrategia de copia de seguridad correcta depende del tamaño de la base de datos, los requisitos de RTO/RPO y la infraestructura. Aquí se muestra cómo mysqldump se compara con las alternativas más comunes:

Para bases de datos de menos de 10–20 GB en un entorno de Hosting VPS, mysqldump sigue siendo la solución más práctica y portátil. Más allá de ese umbral, las herramientas de copia de seguridad física ofrecen ventanas de copia de seguridad y restauración significativamente más rápidas.

Ejemplos de uso práctico

Ejemplo 1: Hacer copia de seguridad de una sola base de datos

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

La sustitución `$(date +%F)` añade automáticamente la fecha ISO (p. ej., `2025-07-15`) al nombre del archivo, evitando sobreescrituras.

Ejemplo 2: Hacer copia de seguridad de múltiples bases de datos específicas

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

El indicador `–databases` hace que mysqldump emita sentencias `CREATE DATABASE` y `USE`, haciendo que el volcado sea autónomo para la restauración.

Ejemplo 3: Hacer copia de seguridad de todas las bases de datos

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Incluye siempre `–events`, `–routines` y `–triggers` en los volcados completos del servidor. Estos objetos se omiten silenciosamente sin indicadores explícitos.

Ejemplo 4: Copia de seguridad InnoDB consistente (segura para producción)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` rota el registro binario al inicio del volcado. `–source-data=2` escribe el nombre del archivo de registro binario actual y la posición como comentario SQL, habilitando la recuperación en un punto en el tiempo reproduciendo los binlogs posteriores desde esa posición.

Ejemplo 5: Copia de seguridad comprimida con gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

Para servidores con CPU limitada, sustituye `pigz` (gzip paralelo) para utilizar múltiples núcleos:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Ejemplo 6: Copia de seguridad solo del esquema (estructura sin datos)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Útil para controlar versiones de tu esquema en Git o desplegar en un entorno de pruebas sin copiar datos de producción.

Ejemplo 7: Copia de seguridad solo de datos (sin esquema)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Usa esto cuando el esquema de destino ya existe y solo necesitas poblar o actualizar datos.

Ejemplo 8: Hacer copia de seguridad de una sola tabla

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Ejemplo 9: Exportar un subconjunto filtrado de filas

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

La opción `–where` está infrautilizada pero es extremadamente poderosa para exportaciones parciales, archivado de datos y depuración de conjuntos de registros específicos.

Ejemplo 10: Excluir tablas específicas

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Excluir tablas grandes y efímeras (cachés, almacenes de sesiones, tablas de registros) puede reducir el tamaño y la duración del volcado en un orden de magnitud.

Ejemplo 11: Incluir procedimientos almacenados, funciones y disparadores

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Ejemplo 12: Copia de seguridad de base de datos remota

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

Al hacer una copia de seguridad de un servidor remoto, el tráfico atraviesa la red sin cifrar de forma predeterminada. Añade los indicadores `–ssl-ca`, `–ssl-cert` y `–ssl-key` o tuneliza a través de SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Restaurar una copia de seguridad de mysqldump

Restaurar una sola base de datos

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Si la base de datos de destino aún no existe, créala primero:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Restaurar todas las bases de datos desde un volcado completo del servidor

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Dado que `–all-databases` incrusta sentencias `CREATE DATABASE` y `USE`, no se necesita argumento de base de datos de destino.

Restaurar desde una copia de seguridad comprimida

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

O usando sustitución de proceso:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Restaurar una sola tabla desde un volcado completo de base de datos

Este es un escenario operativo común que el archivo de volcado original hace no trivial. Usa `sed` o `grep` para extraer la sección relevante:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Alternativamente, usa `mysql_extract_table.sh` o importa a una base de datos temporal y copia la tabla:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Recuperación en un punto en el tiempo usando registros binarios

Si tu volcado se realizó con `–source-data=2` y el registro binario está habilitado, puedes recuperarte a cualquier punto después del volcado:

1. Identifica la posición del registro binario desde el comentario de encabezado del archivo de volcado.
2. Restaura el volcado base.
3. Aplica los eventos de registro binario posteriores hasta la marca de tiempo deseada:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Automatizar copias de seguridad con Cron

Trabajo de copia de seguridad diaria básica

Almacena las credenciales en `~/.my.cnf` en lugar de incrustarlas en los comandos de cron:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Establece permisos estrictos:

“`bash

chmod 600 ~/.my.cnf

“`

Luego crea el trabajo de cron:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Script de copia de seguridad de nivel de producción

Para Servidores Dedicados que alojan múltiples bases de datos, un script más robusto maneja el registro de errores, verificaciones de espacio en disco y descarga remota:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Refuerzo de seguridad para operaciones de mysqldump

La gestión de credenciales es el aspecto más comúnmente descuidado de la seguridad de las copias de seguridad. Nunca pases `-pYourPassword` directamente en la línea de comandos — es visible en la salida de `ps aux` y en el historial de shell. Usa uno de estos enfoques en su lugar:

• `~/.my.cnf` con `chmod 600` (por usuario)
• `/etc/mysql/backup.cnf` con `chmod 640`, propiedad de root, legible por el grupo de copia de seguridad
• Variable de entorno `MYSQL_PWD` (visible en `/proc`, usar solo en contenedores aislados)
• MySQL Vault o HashiCorp Vault para entornos empresariales

Los permisos de los archivos de copia de seguridad deben ser restrictivos:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Cifrado en reposo: Para datos sensibles, cifra los archivos de copia de seguridad antes de almacenarlos o transferirlos:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Cifrado en tránsito: Al volcar desde un servidor remoto, usa siempre SSL/TLS o un túnel SSH. En un entorno de VPS con cPanel, la interfaz de copia de seguridad de cPanel gestiona esto automáticamente, pero las operaciones manuales de mysqldump requieren indicadores SSL explícitos.

Errores comunes y cómo evitarlos

Las discrepancias de juego de caracteres son la causa más frecuente de restauraciones corruptas. Especifica siempre el juego de caracteres explícitamente:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

La falta de `–column-statistics=0` causa fallos cuando un cliente MySQL 8.0 vuelca desde un servidor MySQL 5.7 o MariaDB. El cliente MySQL 8 intenta volcar estadísticas de columnas que los servidores más antiguos no admiten:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Olvidar `–routines`, `–triggers` y `–events` omite silenciosamente objetos críticos de la base de datos. Estos indicadores no están habilitados por defecto (excepto `–triggers`) y se olvidan frecuentemente en volcados ad-hoc.

Volcados de tablas grandes que causan OOM: mysqldump almacena en búfer conjuntos de resultados completos en memoria de forma predeterminada. Para tablas muy grandes, añade `–quick` (habilitado por defecto en la mayoría de versiones, pero vale la pena verificarlo) para transmitir filas de una en una en lugar de almacenarlas en búfer:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Restaurar a una versión diferente de MySQL: Los volcados de MySQL 8.0 pueden contener sintaxis no compatible con MySQL 5.7 (p. ej., índices funcionales, columnas invisibles). Prueba siempre las restauraciones en un entorno con versión coincidente antes de depender de migraciones entre versiones.

Deriva del valor de auto-incremento: Si restauras una tabla en un esquema existente que ya tiene filas, las sentencias `INSERT` fallarán en conflictos de clave primaria a menos que incluyas `–add-drop-table` o trunces manualmente la tabla de destino primero.

Usar mysqldump para migraciones de bases de datos

mysqldump es el enfoque estándar para migrar bases de datos entre servidores — por ejemplo, al mover un sitio WordPress desde Hosting Web Compartido a un VPS, o replatformar a un entorno de Paneles de Control VPS con más recursos.

El flujo de trabajo de migración recomendado:

1. Volcar la base de datos de origen con opciones completas:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Transferir de forma segura usando rsync sobre SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Crear la base de datos de destino con el juego de caracteres correspondiente:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Restaurar y verificar:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Actualizar la configuración de la aplicación para apuntar al nuevo host de base de datos.

Para aplicaciones que también dependen de infraestructura de correo electrónico, asegúrate de que los registros DNS y las configuraciones de Hosting de Correo Electrónico se actualicen en paralelo con la migración de la base de datos para evitar interrupciones del servicio.

Verificar la integridad de las copias de seguridad

Una copia de seguridad que nunca ha sido probada no es una copia de seguridad — es una suposición no verificada. Implementa una rutina de verificación:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Ejecuta este script de verificación semanalmente mediante cron y alerta en caso de fallo.

Matriz de decisión: cuándo usar mysqldump

Lista de verificación de puntos clave técnicos

• Usa siempre `–single-transaction` para bases de datos solo InnoDB para evitar bloqueos de escritura durante la copia de seguridad.
• Incluye siempre `–routines –triggers –events` en cualquier volcado destinado a ser una copia de seguridad completa.
• Almacena las credenciales en `~/.my.cnf` o `/etc/mysql/backup.cnf` con `chmod 600/640` — nunca en línea en scripts o comandos de cron.
• Añade `–column-statistics=0` cuando uses un cliente MySQL 8.0 contra un servidor MySQL 5.7 o MariaDB.
• Especifica siempre `–default-character-set=utf8mb4` tanto en el volcado como en la restauración para evitar la corrupción de codificación de caracteres.
• Comprime todas las copias de seguridad con gzip o pigz; cifra los volcados sensibles con AES-256 antes de la transferencia fuera del sitio.
• Incluye `–flush-logs –source-data=2` en los volcados de producción para habilitar la recuperación en un punto en el tiempo mediante registros binarios.
• Automatiza la limpieza de retención con `find … -mtime +N -delete` para evitar el agotamiento del disco.
• Prueba las restauraciones de forma programada — verifica el recuento de filas y comprueba puntualmente la integridad de los datos frente a producción.
• Para esquemas de motor mixto, usa `–lock-all-tables` en lugar de `–single-transaction` para garantizar la consistencia.

Preguntas frecuentes

¿mysqldump bloquea las tablas durante la copia de seguridad?

Con `–single-transaction` en una base de datos InnoDB pura, no se adquieren bloqueos de tabla más allá de un breve vaciado inicial. Las tablas MyISAM siempre requieren un bloqueo de lectura (`LOCK TABLES`) porque carecen de soporte de transacciones. Los esquemas de motor mixto requieren `–lock-all-tables` para una instantánea consistente, lo que bloquea las escrituras durante la duración del volcado.

¿Cómo hago una copia de seguridad solo del esquema de la base de datos sin ningún dato?

Usa el indicador `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Esto exporta todas las sentencias `CREATE TABLE`, `CREATE VIEW`, procedimientos almacenados y disparadores sin ninguna sentencia `INSERT`.

¿Por qué falla mi mysqldump con errores de “column statistics”?

Esto ocurre cuando un cliente MySQL 8.0 se conecta a un servidor MySQL 5.7 o MariaDB. Añade `–column-statistics=0` a tu comando. Alternativamente, actualiza el servidor a MySQL 8.0 o usa un binario de cliente que coincida con la versión del servidor.

¿Puede mysqldump realizar copias de seguridad incrementales?

No. mysqldump siempre produce un volcado lógico completo del alcance especificado. La capacidad de copia de seguridad incremental requiere el archivado de registros binarios (`mysqlbinlog`) combinado con un mysqldump base tomado con `–flush-logs –source-data=2`. Las verdaderas copias de seguridad físicas incrementales requieren Percona XtraBackup o MySQL Enterprise Backup.

¿Cuál es la forma más segura de automatizar mysqldump sin exponer contraseñas?

Crea un usuario de copia de seguridad MySQL dedicado con los privilegios mínimos requeridos, almacena sus credenciales en una sección `[mysqldump]` de `~/.my.cnf` o en un archivo de opciones separado con `chmod 600`, y referéncialo con `–defaults-file=/path/to/backup.cnf`. Este enfoque mantiene las credenciales completamente fuera de los listados de procesos, el historial de shell y las definiciones de trabajos de cron.