Respaldo de Base de Datos con mysqldump y pg_dump: Guía Completa

Introducción

Los respaldos de bases de datos son la piedra angular de cualquier estrategia de recuperación ante desastres. Ya sea enfrentando fallas de hardware, errores humanos, brechas de seguridad o desastres naturales, tener respaldos confiables y probados significa la diferencia entre un inconveniente menor y una pérdida catastrófica de datos que podría terminar con un negocio.

mysqldump (para MySQL/MariaDB) y pg_dump (para PostgreSQL) son las herramientas estándar de respaldo lógico que crean archivos SQL portables y legibles, o formatos binarios comprimidos. Estas herramientas son esenciales para administradores de bases de datos, proporcionando flexibilidad en estrategias de respaldo, fácil restauración y la capacidad de migrar datos entre servidores o versiones.

Esta guía integral cubre todo, desde comandos básicos de respaldo hasta técnicas avanzadas, estrategias de automatización, procedimientos de restauración y mejores prácticas para bases de datos MySQL/MariaDB y PostgreSQL.

Por qué Importan los Respaldos Lógicos

Los respaldos lógicos ofrecen varias ventajas:

Portabilidad: Funciona en diferentes plataformas y versiones
Selectividad: Respaldar bases de datos específicas, tablas o incluso filas
Legible: El formato SQL puede ser inspeccionado y editado
Compresión: Reduce significativamente el tamaño del respaldo
Consistencia: Instantáneas consistentes en un punto en el tiempo
Restauración fácil: Simple de restaurar en cualquier servidor compatible

Prerrequisitos

Antes de proceder:

MySQL/MariaDB o PostgreSQL instalado y en ejecución
Espacio en disco suficiente para respaldos (estimar ratio 1:1, más con compresión)
Acceso administrativo a las bases de datos
Directorio de destino de respaldo con permisos apropiados
Comprensión de los requisitos de tamaño de base de datos y tiempo de respaldo

Verificación de Tamaños de Base de Datos

MySQL/MariaDB:

# Conectar a MySQL
mysql -u root -p

# Verificar tamaños de todas las bases de datos
SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

# Verificar tamaño de base de datos específica
SELECT
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

PostgreSQL:

# Conectar a PostgreSQL
sudo -u postgres psql

# Verificar tamaños de todas las bases de datos
SELECT
    datname AS database,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

# Verificar tamaños de tablas en base de datos
\c your_database
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

Respaldos MySQL/MariaDB con mysqldump

Comandos Básicos de mysqldump

# Respaldar una sola base de datos
mysqldump -u root -p database_name > backup_$(date +%Y%m%d).sql

# Respaldar con marca de tiempo
mysqldump -u root -p myapp_db > myapp_backup_$(date +%Y%m%d_%H%M%S).sql

# Respaldar todas las bases de datos
mysqldump -u root -p --all-databases > all_databases_$(date +%Y%m%d).sql

# Respaldar múltiples bases de datos específicas
mysqldump -u root -p --databases db1 db2 db3 > multiple_dbs_$(date +%Y%m%d).sql

# Respaldar tablas específicas
mysqldump -u root -p database_name table1 table2 > tables_backup_$(date +%Y%m%d).sql

Opciones Esenciales de mysqldump

# Respaldo completo con todos los objetos de base de datos
mysqldump -u root -p \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --quick \
    --add-drop-database \
    --databases myapp_db > complete_backup_$(date +%Y%m%d).sql

# Opciones explicadas:
# --single-transaction: Instantánea consistente para tablas InnoDB
# --routines: Incluir procedimientos almacenados y funciones
# --triggers: Incluir triggers
# --events: Incluir eventos programados
# --quick: Recuperar filas una a la vez (menos memoria)
# --add-drop-database: Agregar DROP DATABASE antes de CREATE DATABASE

Respaldos Comprimidos

# Comprimir con gzip
mysqldump -u root -p --all-databases | gzip > all_dbs_$(date +%Y%m%d).sql.gz

# Comprimir con mayor compresión
mysqldump -u root -p --all-databases | gzip -9 > all_dbs_$(date +%Y%m%d).sql.gz

# Comprimir con pigz (gzip paralelo, más rápido)
mysqldump -u root -p --all-databases | pigz > all_dbs_$(date +%Y%m%d).sql.gz

# Comprimir con bzip2 (mejor compresión, más lento)
mysqldump -u root -p --all-databases | bzip2 > all_dbs_$(date +%Y%m%d).sql.bz2

# Comprimir con xz (mejor compresión, más lento)
mysqldump -u root -p --all-databases | xz > all_dbs_$(date +%Y%m%d).sql.xz

Respaldo de Bases de Datos Grandes

# Usar insert extendido para restauración más rápida
mysqldump -u root -p \
    --single-transaction \
    --quick \
    --extended-insert \
    --max_allowed_packet=1G \
    large_database > large_db_$(date +%Y%m%d).sql

# Deshabilitar bloqueos para tablas MyISAM
mysqldump -u root -p \
    --single-transaction \
    --skip-lock-tables \
    database_name > backup_$(date +%Y%m%d).sql

# Dividir base de datos grande por tabla
for table in $(mysql -u root -p -e "SHOW TABLES FROM database_name" | grep -v Tables_in); do
    mysqldump -u root -p database_name $table | gzip > database_${table}_$(date +%Y%m%d).sql.gz
done

Respaldo con Información de Replicación

# Respaldo con datos maestros (para configuración de replicación)
mysqldump -u root -p \
    --master-data=2 \
    --single-transaction \
    --flush-logs \
    --all-databases > master_backup_$(date +%Y%m%d).sql

# Opciones:
# --master-data=1: Incluir declaración CHANGE MASTER (ejecutable)
# --master-data=2: Incluir CHANGE MASTER como comentario (para referencia)
# --flush-logs: Rotar logs binarios después del respaldo

Respaldos Solo de Esquema y Solo de Datos

# Respaldar solo esquema (estructura)
mysqldump -u root -p --no-data database_name > schema_$(date +%Y%m%d).sql

# Respaldar solo datos (sin declaraciones CREATE)
mysqldump -u root -p --no-create-info database_name > data_$(date +%Y%m%d).sql

# Respaldar tablas específicas solo con esquema
mysqldump -u root -p --no-data database_name table1 table2 > tables_schema_$(date +%Y%m%d).sql

Excluir Tablas Específicas

# Excluir tablas específicas
mysqldump -u root -p \
    --single-transaction \
    --ignore-table=database.table1 \
    --ignore-table=database.table2 \
    database_name > backup_without_tables_$(date +%Y%m%d).sql

# Respaldar todo excepto tablas de log
mysqldump -u root -p \
    --single-transaction \
    --ignore-table=myapp.access_logs \
    --ignore-table=myapp.error_logs \
    --ignore-table=myapp.audit_logs \
    myapp_db > myapp_no_logs_$(date +%Y%m%d).sql

Usar Archivo de Configuración para Credenciales

# Crear archivo de credenciales
cat > ~/.my.cnf << EOF
[client]
user=backup_user
password=backup_password
host=localhost
EOF

# Asegurar el archivo
chmod 600 ~/.my.cnf

# Ahora ejecutar mysqldump sin -u y -p
mysqldump --single-transaction --all-databases > backup_$(date +%Y%m%d).sql

Respaldos PostgreSQL con pg_dump

Comandos Básicos de pg_dump

# Respaldar una sola base de datos
pg_dump -U postgres database_name > backup_$(date +%Y%m%d).sql

# Respaldar con nombre de usuario y host
pg_dump -U postgres -h localhost database_name > backup_$(date +%Y%m%d).sql

# Respaldar todas las bases de datos (usar pg_dumpall)
pg_dumpall -U postgres > all_databases_$(date +%Y%m%d).sql

# Respaldar esquema específico
pg_dump -U postgres -n schema_name database_name > schema_backup_$(date +%Y%m%d).sql

# Respaldar tablas específicas
pg_dump -U postgres -t table1 -t table2 database_name > tables_$(date +%Y%m%d).sql

Formatos de Salida de pg_dump

# Formato SQL plano (por defecto, legible)
pg_dump -U postgres -F p database_name > backup_$(date +%Y%m%d).sql

# Formato personalizado (comprimido, permite restauración paralela)
pg_dump -U postgres -F c database_name -f backup_$(date +%Y%m%d).dump

# Formato directorio (respaldo y restauración paralelos)
pg_dump -U postgres -F d -j 4 database_name -f backup_$(date +%Y%m%d)

# Formato tar
pg_dump -U postgres -F t database_name -f backup_$(date +%Y%m%d).tar

# Opciones de formato:
# -F p: SQL plano (por defecto)
# -F c: personalizado (comprimido, recomendado)
# -F d: directorio (operaciones paralelas)
# -F t: archivo tar

Respaldos Comprimidos

# Comprimir formato SQL plano
pg_dump -U postgres database_name | gzip > backup_$(date +%Y%m%d).sql.gz

# El formato personalizado ya está comprimido
pg_dump -U postgres -F c database_name -f backup_$(date +%Y%m%d).dump

# Ajustar nivel de compresión (0-9)
pg_dump -U postgres -F c -Z 9 database_name -f backup_$(date +%Y%m%d).dump

Respaldo Paralelo

# Respaldo paralelo con 4 trabajos (formato directorio)
pg_dump -U postgres -F d -j 4 database_name -f backup_$(date +%Y%m%d)

# La opción -j especifica el número de trabajos paralelos
# Útil para bases de datos grandes
# Requiere formato directorio (-F d)

# Ejemplo con 8 trabajos paralelos
pg_dump -U postgres -F d -j 8 large_database -f large_db_backup_$(date +%Y%m%d)

Respaldo de Bases de Datos Grandes

# Formato personalizado con salida verbosa
pg_dump -U postgres -F c -v database_name -f backup_$(date +%Y%m%d).dump

# Formato directorio con trabajos paralelos y verboso
pg_dump -U postgres -F d -j 4 -v database_name -f backup_$(date +%Y%m%d)

# Excluir tablas grandes
pg_dump -U postgres \
    --exclude-table=large_table1 \
    --exclude-table=large_table2 \
    database_name -f backup_$(date +%Y%m%d).dump

Respaldos Solo de Esquema y Solo de Datos

# Solo esquema (estructura)
pg_dump -U postgres --schema-only database_name > schema_$(date +%Y%m%d).sql

# Solo datos
pg_dump -U postgres --data-only database_name > data_$(date +%Y%m%d).sql

# Esquema de tabla específica
pg_dump -U postgres --schema-only -t table_name database_name > table_schema_$(date +%Y%m%d).sql

Excluir Tablas y Esquemas

# Excluir tablas específicas
pg_dump -U postgres \
    --exclude-table=logs \
    --exclude-table=temp_data \
    database_name -f backup_$(date +%Y%m%d).dump

# Excluir patrón de tabla
pg_dump -U postgres \
    --exclude-table='log_*' \
    database_name -f backup_$(date +%Y%m%d).dump

# Excluir esquema
pg_dump -U postgres \
    --exclude-schema=test_schema \
    database_name -f backup_$(date +%Y%m%d).dump

Respaldar Objetos Globales con pg_dumpall

# Respaldar todas las bases de datos y objetos globales
pg_dumpall -U postgres > all_databases_$(date +%Y%m%d).sql

# Respaldar solo objetos globales (roles, tablespaces)
pg_dumpall -U postgres --globals-only > globals_$(date +%Y%m%d).sql

# Respaldar solo roles
pg_dumpall -U postgres --roles-only > roles_$(date +%Y%m%d).sql

# Respaldar solo tablespaces
pg_dumpall -U postgres --tablespaces-only > tablespaces_$(date +%Y%m%d).sql

Usar .pgpass para Contraseña

# Crear archivo .pgpass
cat > ~/.pgpass << EOF
localhost:5432:*:postgres:your_password
localhost:5432:database_name:backup_user:backup_password
EOF

# Asegurar el archivo (requerido)
chmod 600 ~/.pgpass

# Ahora ejecutar pg_dump sin solicitud de contraseña
pg_dump -U postgres database_name > backup_$(date +%Y%m%d).sql

Restauración de Respaldos

Restauración de Respaldos MySQL/MariaDB

# Restaurar base de datos única desde archivo SQL
mysql -u root -p database_name < backup_20260111.sql

# Restaurar todas las bases de datos
mysql -u root -p < all_databases_20260111.sql

# Restaurar respaldo comprimido
gunzip < backup_20260111.sql.gz | mysql -u root -p database_name

# Restaurar con indicador de progreso
pv backup_20260111.sql | mysql -u root -p database_name

# Crear base de datos antes de restaurar
mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p database_name < backup_20260111.sql

# Restaurar tabla específica
mysql -u root -p database_name < table_backup_20260111.sql

# Restaurar y mostrar progreso con pipe viewer
pv backup_20260111.sql.gz | gunzip | mysql -u root -p database_name

Restauración de Respaldos PostgreSQL

# Restaurar formato SQL plano
psql -U postgres -d database_name -f backup_20260111.sql

# Crear base de datos primero
createdb -U postgres database_name
psql -U postgres -d database_name -f backup_20260111.sql

# Restaurar SQL comprimido
gunzip < backup_20260111.sql.gz | psql -U postgres -d database_name

# Restaurar formato personalizado
pg_restore -U postgres -d database_name backup_20260111.dump

# Restaurar con trabajos paralelos
pg_restore -U postgres -d database_name -j 4 backup_20260111.dump

# Restaurar formato directorio con trabajos paralelos
pg_restore -U postgres -d database_name -j 4 backup_20260111/

# Restaurar con limpieza (eliminar objetos existentes primero)
pg_restore -U postgres -d database_name --clean backup_20260111.dump

# Restaurar con creación de base de datos
pg_restore -U postgres --create -d postgres backup_20260111.dump

# Restaurar tabla específica
pg_restore -U postgres -d database_name -t table_name backup_20260111.dump

# Restaurar todas las bases de datos
psql -U postgres -f all_databases_20260111.sql

Opciones y Consideraciones de Restauración

# MySQL: Detener aplicación antes de restaurar
sudo systemctl stop apache2

# Restaurar con salida verbosa
mysql -u root -p -v database_name < backup_20260111.sql

# PostgreSQL: Crear base de datos con codificación específica
createdb -U postgres -E UTF8 -T template0 database_name
pg_restore -U postgres -d database_name backup_20260111.dump

# Manejar errores durante la restauración
# MySQL
mysql -u root -p --force database_name < backup_20260111.sql

# PostgreSQL
pg_restore -U postgres -d database_name --exit-on-error backup_20260111.dump

Verificación de Respaldo

Verificación de Respaldos MySQL/MariaDB

# Verificar integridad del archivo de respaldo
gunzip -t backup_20260111.sql.gz

# Verificar sintaxis SQL
mysql -u root -p --force < backup_20260111.sql 2>&1 | grep -i error

# Probar restauración en base de datos temporal
mysql -u root -p -e "CREATE DATABASE temp_restore_test;"
mysql -u root -p temp_restore_test < backup_20260111.sql
mysql -u root -p -e "DROP DATABASE temp_restore_test;"

# Contar tablas en respaldo
grep "CREATE TABLE" backup_20260111.sql | wc -l

# Verificar tamaño del archivo de respaldo
ls -lh backup_20260111.sql

Verificación de Respaldos PostgreSQL

# Verificar respaldo de formato personalizado
pg_restore --list backup_20260111.dump | head -20

# Verificar estructura de respaldo
pg_restore --list backup_20260111.dump

# Probar restauración en base de datos temporal
createdb -U postgres temp_restore_test
pg_restore -U postgres -d temp_restore_test backup_20260111.dump
dropdb -U postgres temp_restore_test

# Verificar integridad del archivo de respaldo
file backup_20260111.dump

# Para formato directorio
ls -lh backup_20260111/

Mejores Prácticas

Estrategia de Respaldo

# Implementar estrategia de respaldo 3-2-1:
# 3 copias de datos
# 2 tipos de medios diferentes
# 1 respaldo fuera del sitio

# Respaldos diarios con retención
# Mantener 7 días de respaldos diarios
# Mantener 4 semanas de respaldos semanales
# Mantener 12 meses de respaldos mensuales

# Ejemplo de estructura de directorios
/backups/
├── daily/
├── weekly/
├── monthly/
└── offsite/

Convenciones de Nomenclatura

# Incluir fecha, hora y tipo en el nombre del archivo
# Formato: database_type_YYYYMMDD_HHMMSS.extension

# Ejemplos:
myapp_full_20260111_020000.sql.gz
postgres_schema_20260111_030000.dump
mysql_data_20260111_040000.sql.gz

# Función para nomenclatura consistente
backup_name() {
    local db=$1
    local type=$2
    echo "${db}_${type}_$(date +%Y%m%d_%H%M%S)"
}

# Uso
mysqldump -u root -p myapp_db > $(backup_name "myapp" "full").sql

Seguridad de Respaldos

# Encriptar respaldos
# Respaldo MySQL con encriptación
mysqldump -u root -p --all-databases | gzip | openssl enc -aes-256-cbc -salt -out backup_$(date +%Y%m%d).sql.gz.enc

# Desencriptar respaldo
openssl enc -d -aes-256-cbc -in backup_20260111.sql.gz.enc | gunzip | mysql -u root -p

# Respaldo PostgreSQL con encriptación
pg_dump -U postgres database_name | gzip | gpg --encrypt --recipient [email protected] > backup_$(date +%Y%m%d).sql.gz.gpg

# Desencriptar
gpg --decrypt backup_20260111.sql.gz.gpg | gunzip | psql -U postgres -d database_name

# Establecer permisos seguros
chmod 600 backup_*.sql
chown backup_user:backup_group backup_*.sql

Transferencia de Respaldo Fuera del Sitio

# Transferir a servidor remoto con rsync
rsync -avz --progress /backup/mysql/ user@backup-server:/remote/backup/mysql/

# Transferir con compresión SSH
rsync -avz -e "ssh -C" /backup/postgresql/ user@backup-server:/remote/backup/postgresql/

# Subir a S3
aws s3 cp backup_20260111.sql.gz s3://my-backup-bucket/mysql/

# Subir a servidor remoto con scp
scp backup_20260111.sql.gz user@backup-server:/remote/backup/

Monitoreo de Tamaño y Tiempo de Respaldo

# Registrar métricas de respaldo
echo "$(date): Backup started" >> /var/log/backup.log
START_TIME=$(date +%s)

# Realizar respaldo
mysqldump -u root -p --all-databases | gzip > backup_$(date +%Y%m%d).sql.gz

# Calcular duración y tamaño
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
SIZE=$(du -h backup_$(date +%Y%m%d).sql.gz | cut -f1)

echo "$(date): Backup completed in ${DURATION}s, size: ${SIZE}" >> /var/log/backup.log

Solución de Problemas

Problemas Comunes de mysqldump

# Error: Acceso denegado
# Solución: Verificar privilegios de usuario
GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup_user'@'localhost';

# Error: mysqldump: Got error: 1044: Access denied for user
# Solución: Agregar opción --no-tablespaces
mysqldump -u root -p --no-tablespaces --all-databases > backup.sql

# Error: Sin memoria
# Solución: Usar opción --quick
mysqldump -u root -p --quick --all-databases > backup.sql

# Error: Tiempo de espera de bloqueo excedido
# Solución: Aumentar lock_wait_timeout
SET GLOBAL lock_wait_timeout = 600;

Problemas Comunes de pg_dump

# Error: permiso denegado
# Solución: Otorgar privilegios necesarios
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

# Error: no se pudo conectar
# Solución: Verificar pg_hba.conf y parámetros de conexión
# Agregar a pg_hba.conf:
host    all    backup_user    127.0.0.1/32    md5

# Errores de objetos grandes
# Solución: Incluir objetos grandes o excluirlos
pg_dump -U postgres --no-blobs database_name > backup.sql

# Falla el volcado paralelo
# Solución: Asegurar que el directorio tiene permisos de escritura
mkdir -p /backup/pgdump
chown postgres:postgres /backup/pgdump

Conclusión

Los respaldos confiables de bases de datos son esenciales para la continuidad del negocio y la protección de datos. Tanto mysqldump como pg_dump proporcionan herramientas potentes y flexibles para crear respaldos consistentes y portables.

Conclusiones Clave

Respaldos Regulares: Automatizar respaldos diarios con políticas de retención
Probar Restauraciones: Verificar regularmente la integridad del respaldo mediante pruebas de restauración
Múltiples Copias: Implementar estrategia de respaldo 3-2-1
Compresión: Usar compresión para ahorrar espacio de almacenamiento
Seguridad: Encriptar respaldos sensibles
Monitoreo: Rastrear tamaño, duración y éxito del respaldo
Documentación: Mantener procedimientos claros de respaldo y restauración

Lista de Verificación de Respaldo

✓ Respaldos diarios automatizados configurados
✓ Política de retención de respaldos implementada
✓ Respaldos fuera del sitio configurados
✓ Encriptación de respaldo habilitada para datos sensibles
✓ Pruebas de restauración regulares programadas
✓ Monitoreo y alertas de respaldo activos
✓ Documentación actualizada y accesible
✓ Permisos de respaldo asegurados
✓ Capacidad de almacenamiento monitoreada
✓ Objetivo de tiempo de recuperación (RTO) definido y probado

Recursos Adicionales

Recuerde: Un respaldo es tan bueno como su última restauración exitosa. ¡Pruebe sus respaldos regularmente!

Respaldo de Base de Datos con mysqldump y pg_dump: Guía Completa

Respaldo de Base de Datos con mysqldump y pg_dump: Guía Completa

Introducción

Por qué Importan los Respaldos Lógicos

Prerrequisitos

Verificación de Tamaños de Base de Datos

Respaldos MySQL/MariaDB con mysqldump

Comandos Básicos de mysqldump

Opciones Esenciales de mysqldump

Respaldos Comprimidos

Respaldo de Bases de Datos Grandes

Respaldo con Información de Replicación

Respaldos Solo de Esquema y Solo de Datos

Excluir Tablas Específicas

Usar Archivo de Configuración para Credenciales

Respaldos PostgreSQL con pg_dump

Comandos Básicos de pg_dump

Formatos de Salida de pg_dump

Respaldos Comprimidos

Respaldo Paralelo

Respaldo de Bases de Datos Grandes

Respaldos Solo de Esquema y Solo de Datos

Excluir Tablas y Esquemas

Respaldar Objetos Globales con pg_dumpall

Usar .pgpass para Contraseña

Restauración de Respaldos

Restauración de Respaldos MySQL/MariaDB

Restauración de Respaldos PostgreSQL

Opciones y Consideraciones de Restauración

Verificación de Respaldo

Verificación de Respaldos MySQL/MariaDB

Verificación de Respaldos PostgreSQL

Mejores Prácticas

Estrategia de Respaldo

Convenciones de Nomenclatura

Seguridad de Respaldos

Transferencia de Respaldo Fuera del Sitio

Monitoreo de Tamaño y Tiempo de Respaldo

Solución de Problemas

Problemas Comunes de mysqldump

Problemas Comunes de pg_dump

Conclusión

Conclusiones Clave

Lista de Verificación de Respaldo

Recursos Adicionales

Latest Video

Get $20 Free Credit