Cómo hacer backups y restaurar una base de datos MySQL o PostgreSQL

D
DanisCh
(Actualizado: ) • 15 min de lectura
Cómo hacer backups y restaurar una base de datos MySQL o PostgreSQL
SQL Conceptos de Base de Datos

No tener backups de tu base de datos no es un riesgo técnico: es una certeza de pérdida eventual. Un disco que falla, una consulta UPDATE sin WHERE, un servidor comprometido o un deploy que salió mal pueden borrar años de datos en segundos. Los backups son la única protección real contra esos escenarios.

En esta guía aprenderás cómo hacer copias de seguridad y restaurarlas en MySQL y PostgreSQL, desde los comandos más básicos hasta estrategias para automatizar el proceso en producción.

Tipos de backup: qué estrategia elegir

Antes de ver los comandos, conviene entender qué tipos de backup existen y cuándo conviene cada uno.

Backup lógico: exporta los datos como instrucciones SQL (CREATE TABLE, INSERT...). Es lo que generan mysqldump y pg_dump. Es portable (funciona en cualquier versión compatible), fácil de inspeccionar y restaurar selectivamente, pero más lento para bases de datos muy grandes y no captura el estado exacto del disco.

Backup físico: copia los archivos del sistema de ficheros de la base de datos tal como están en disco. Es más rápido para bases de datos grandes y permite point-in-time recovery, pero requiere la misma versión del motor para restaurar. Herramientas como xtrabackup (MySQL) o pg_basebackup (PostgreSQL) hacen este tipo de backup.

Para la mayoría de proyectos pequeños y medianos, el backup lógico con mysqldump o pg_dump es suficiente y mucho más sencillo de gestionar.

MySQL: backup con mysqldump

Backup básico de una base de datos

# Exportar una base de datos completa a un archivo SQL
mysqldump -u usuario -p nombre_base_datos > backup.sql

# El -p hace que pida la contraseña de forma interactiva (más seguro que escribirla en el comando)
# Si el usuario es root:
mysqldump -u root -p mi_app_db > backup_mi_app_$(date +%Y%m%d).sql

# Verificar que el archivo se generó y tiene contenido
ls -lh backup_mi_app_$(date +%Y%m%d).sql
head -50 backup_mi_app_$(date +%Y%m%d).sql   # ver las primeras líneas

Opciones importantes de mysqldump

# Backup con datos y estructura (por defecto incluye ambos)
mysqldump -u root -p mi_app_db > backup_completo.sql

# Solo la estructura, sin datos (útil para replicar el esquema)
mysqldump -u root -p --no-data mi_app_db > estructura.sql

# Solo los datos, sin la estructura
mysqldump -u root -p --no-create-info mi_app_db > datos.sql

# Backup de tablas específicas (no toda la base de datos)
mysqldump -u root -p mi_app_db clientes pedidos productos > tablas_principales.sql

# Backup de todas las bases de datos del servidor
mysqldump -u root -p --all-databases > todas_las_bases.sql

# Backup consistente para bases de datos con transacciones (InnoDB)
# --single-transaction garantiza que el backup sea consistente sin bloquear las tablas
mysqldump -u root -p --single-transaction mi_app_db > backup_consistente.sql

# Con compresión: reduce el tamaño del archivo significativamente
mysqldump -u root -p --single-transaction mi_app_db | gzip > backup_$(date +%Y%m%d).sql.gz

# Backup de un servidor remoto
mysqldump -h servidor.ejemplo.com -u usuario -p --single-transaction mi_app_db > backup.sql

Restaurar un backup en MySQL

# Crear la base de datos si no existe
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mi_app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Restaurar el backup
mysql -u root -p mi_app_db < backup.sql

# Restaurar un backup comprimido directamente
gunzip < backup_20260615.sql.gz | mysql -u root -p mi_app_db

# Restaurar en un servidor remoto
mysql -h servidor.ejemplo.com -u usuario -p mi_app_db < backup.sql

# Ver el progreso durante la restauración (útil para archivos grandes)
pv backup.sql | mysql -u root -p mi_app_db
# pv muestra una barra de progreso (instalar con: apt install pv)

Restaurar una sola tabla de un backup completo

# Si necesitas restaurar solo la tabla 'clientes' de un backup que tiene toda la BD:

# Opción 1: extraer la tabla del backup con grep (para tablas pequeñas)
grep -n "Table structure for table \`clientes\`" backup.sql   # encontrar el número de línea

# Opción 2: usar mysqldump con --tables al hacer el backup en primer lugar
mysqldump -u root -p mi_app_db clientes > backup_clientes.sql
mysql -u root -p mi_app_db < backup_clientes.sql

# Opción 3: herramienta mysqlfrm o extraer con un script de Python/sed
# En general, lo más práctico es restaurar la BD en un servidor de staging
# y luego extraer solo la tabla que necesitas

MySQL: backup con credenciales en archivo de configuración

Escribir la contraseña en el comando o en scripts es un riesgo de seguridad. La forma correcta en MySQL es usar un archivo de opciones.

# Crear el archivo de configuración para backups automáticos
# ~/.my.cnf (solo accesible por el usuario actual)
cat > ~/.my.cnf << 'EOF'
[mysqldump]
user=backup_user
password=tu_contraseña_segura
host=localhost

[mysql]
user=backup_user
password=tu_contraseña_segura
host=localhost
EOF

# Proteger el archivo (solo el propietario puede leerlo)
chmod 600 ~/.my.cnf

# Ahora mysqldump lee las credenciales del archivo automáticamente
mysqldump mi_app_db > backup.sql   # sin -u ni -p

# Crear un usuario específico para backups con los mínimos permisos necesarios
mysql -u root -p << 'EOF'
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'contraseña_muy_segura';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EOF

PostgreSQL: backup con pg_dump

Backup básico de una base de datos

# Backup en formato SQL plano (el más portable)
pg_dump -U usuario -d nombre_base_datos -f backup.sql

# Si el usuario es postgres (el superusuario por defecto):
pg_dump -U postgres -d mi_app_db -f backup_$(date +%Y%m%d).sql

# Pedir contraseña interactivamente
pg_dump -U postgres -W -d mi_app_db -f backup.sql

# Backup de un servidor remoto
pg_dump -h servidor.ejemplo.com -U usuario -d mi_app_db -f backup.sql

# Ver el contenido del backup
head -50 backup.sql

Formatos de salida en pg_dump

A diferencia de mysqldump, pg_dump ofrece múltiples formatos de salida con diferentes características:

# Formato SQL plano (por defecto con -f): portable, legible con cualquier editor
pg_dump -U postgres -d mi_app_db -f backup.sql
# Restaurar con: psql -U postgres -d mi_app_db -f backup.sql

# Formato personalizado (-Fc): comprimido, permite restauración selectiva con pg_restore
pg_dump -U postgres -Fc -d mi_app_db -f backup.dump
# Más compacto y flexible que el SQL plano

# Formato directorio (-Fd): útil para backups paralelos (tablas en paralelo)
pg_dump -U postgres -Fd -d mi_app_db -f directorio_backup/ -j 4
# -j 4: usar 4 trabajos en paralelo (más rápido en bases de datos grandes)

# Formato tar (-Ft): archivado pero sin compresión nativa
pg_dump -U postgres -Ft -d mi_app_db -f backup.tar

Opciones útiles de pg_dump

# Solo la estructura (esquema), sin datos
pg_dump -U postgres -s -d mi_app_db -f estructura.sql

# Solo los datos, sin estructura
pg_dump -U postgres -a -d mi_app_db -f datos.sql

# Excluir tablas específicas del backup
pg_dump -U postgres -d mi_app_db -T logs_sesiones -T cache_temporal -f backup.sql

# Incluir solo ciertas tablas
pg_dump -U postgres -d mi_app_db -t clientes -t pedidos -f tablas_criticas.sql

# Con compresión (formato personalizado incluye compresión, SQL plano no)
pg_dump -U postgres -Fc -Z 9 -d mi_app_db -f backup.dump   # nivel de compresión 9 (máximo)

# Backup de todas las bases de datos (incluye roles y configuración global)
pg_dumpall -U postgres -f backup_completo_servidor.sql

Restaurar un backup en PostgreSQL

# Crear la base de datos si no existe
createdb -U postgres mi_app_db

# O desde psql:
psql -U postgres -c "CREATE DATABASE mi_app_db;"

# Restaurar formato SQL plano con psql
psql -U postgres -d mi_app_db -f backup.sql

# Restaurar formato personalizado con pg_restore
pg_restore -U postgres -d mi_app_db backup.dump

# Restaurar formato personalizado con opciones adicionales
pg_restore -U postgres \
  -d mi_app_db \
  --clean \           # eliminar objetos existentes antes de restaurar
  --if-exists \       # no fallar si el objeto no existe al intentar eliminar
  backup.dump

# Ver el contenido de un backup formato personalizado sin restaurar
pg_restore -l backup.dump

# Restaurar solo una tabla específica del backup
pg_restore -U postgres -d mi_app_db -t clientes backup.dump

# Restaurar solo el esquema (sin datos)
pg_restore -U postgres -d mi_app_db -s backup.dump

# Restaurar en paralelo (para bases de datos grandes)
pg_restore -U postgres -d mi_app_db -j 4 directorio_backup/

# Ver errores sin abortar (útil cuando hay conflictos parciales)
pg_restore -U postgres -d mi_app_db --no-privileges backup.dump 2> errores.log

Credenciales sin contraseña en PostgreSQL

# Archivo .pgpass: guarda credenciales de forma segura
# Formato: host:puerto:base_datos:usuario:contraseña
cat > ~/.pgpass << 'EOF'
localhost:5432:mi_app_db:backup_user:contraseña_segura
localhost:5432:*:postgres:contraseña_root
EOF

chmod 600 ~/.pgpass   # solo el propietario puede leerlo

# Ahora pg_dump no pide contraseña cuando las credenciales coinciden
pg_dump -U backup_user -d mi_app_db -f backup.sql

# Variable de entorno PGPASSWORD (menos seguro pero útil en scripts)
PGPASSWORD='mi_contraseña' pg_dump -U postgres -d mi_app_db -f backup.sql

Automatizar los backups con cron

Script de backup para MySQL

#!/bin/bash
# /usr/local/bin/backup_mysql.sh

# Configuración
DB_USER="backup_user"
DB_NAME="mi_app_db"
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=30   # mantener backups de los últimos 30 días
FECHA=$(date +%Y%m%d_%H%M%S)
ARCHIVO="$BACKUP_DIR/${DB_NAME}_${FECHA}.sql.gz"
LOG_FILE="/var/log/backup_mysql.log"

# Crear el directorio si no existe
mkdir -p "$BACKUP_DIR"

echo "[$(date)] Iniciando backup de $DB_NAME..." >> "$LOG_FILE"

# Realizar el backup con compresión
mysqldump \
  --single-transaction \
  --routines \       # incluir procedimientos almacenados y funciones
  --triggers \       # incluir triggers
  --events \         # incluir eventos programados
  "$DB_NAME" | gzip > "$ARCHIVO"

# Verificar que el backup se generó correctamente
if [ $? -eq 0 ] && [ -s "$ARCHIVO" ]; then
    TAMANO=$(du -sh "$ARCHIVO" | cut -f1)
    echo "[$(date)] Backup completado: $ARCHIVO ($TAMANO)" >> "$LOG_FILE"
else
    echo "[$(date)] ERROR: el backup falló" >> "$LOG_FILE"
    exit 1
fi

# Eliminar backups más antiguos que RETENTION_DAYS días
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] Backups antiguos eliminados (más de $RETENTION_DAYS días)" >> "$LOG_FILE"

# Dar permisos de ejecución al script
chmod +x /usr/local/bin/backup_mysql.sh

# Programar con cron: backup diario a las 2:00 AM
crontab -e
# Añadir esta línea:
# 0 2 * * * /usr/local/bin/backup_mysql.sh

# Backup semanal completo los domingos a las 3:00 AM, diario incremental el resto
# 0 3 * * 0 /usr/local/bin/backup_mysql_completo.sh   # domingos
# 0 2 * * 1-6 /usr/local/bin/backup_mysql_incremental.sh  # lunes a sábado

Script de backup para PostgreSQL

#!/bin/bash
# /usr/local/bin/backup_postgres.sh

DB_USER="postgres"
DB_NAME="mi_app_db"
BACKUP_DIR="/var/backups/postgres"
RETENTION_DAYS=30
FECHA=$(date +%Y%m%d_%H%M%S)
ARCHIVO="$BACKUP_DIR/${DB_NAME}_${FECHA}.dump"
LOG_FILE="/var/log/backup_postgres.log"

mkdir -p "$BACKUP_DIR"

echo "[$(date)] Iniciando backup de $DB_NAME..." >> "$LOG_FILE"

# Backup en formato personalizado (comprimido y permite restauración selectiva)
pg_dump \
  -U "$DB_USER" \
  -Fc \
  -Z 6 \          # nivel de compresión 6 (balance entre velocidad y tamaño)
  -d "$DB_NAME" \
  -f "$ARCHIVO"

if [ $? -eq 0 ] && [ -s "$ARCHIVO" ]; then
    TAMANO=$(du -sh "$ARCHIVO" | cut -f1)
    echo "[$(date)] Backup completado: $ARCHIVO ($TAMANO)" >> "$LOG_FILE"
else
    echo "[$(date)] ERROR: el backup falló" >> "$LOG_FILE"
    exit 1
fi

# Limpiar backups antiguos
find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] Limpieza completada" >> "$LOG_FILE"

Copiar backups a almacenamiento externo

Un backup en el mismo servidor que la base de datos no te protege si el servidor falla. Los backups deben estar en al menos una ubicación externa.

# Copiar a otro servidor con rsync
rsync -avz --delete /var/backups/mysql/ usuario@backup-server.ejemplo.com:/backups/mysql/

# Subir a Amazon S3 (requiere AWS CLI instalado y configurado)
aws s3 cp "$ARCHIVO" s3://mi-bucket-backups/mysql/
aws s3 sync /var/backups/mysql/ s3://mi-bucket-backups/mysql/ --delete

# Subir a Google Cloud Storage
gsutil cp "$ARCHIVO" gs://mi-bucket-backups/mysql/
gsutil rsync -d /var/backups/mysql/ gs://mi-bucket-backups/mysql/

# Añadir al script de backup después de verificar que se creó correctamente:
if [ $? -eq 0 ]; then
    aws s3 cp "$ARCHIVO" "s3://mi-bucket-backups/${DB_NAME}/" && \
    echo "[$(date)] Backup subido a S3" >> "$LOG_FILE"
fi

Verificar que los backups funcionan: el test de restauración

Un backup que nunca has probado restaurar no es un backup: es un archivo del que no sabes si sirve. Prueba restaurar periódicamente en un entorno de prueba.

# Test de restauración en MySQL: restaurar en una base de datos de prueba
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mi_app_db_test;"
mysql -u root -p mi_app_db_test < backup.sql

# Verificar que los datos están bien
mysql -u root -p mi_app_db_test -e "
  SELECT 'clientes' AS tabla, COUNT(*) AS filas FROM clientes
  UNION ALL
  SELECT 'pedidos', COUNT(*) FROM pedidos
  UNION ALL
  SELECT 'productos', COUNT(*) FROM productos;
"

# Limpiar la base de datos de prueba
mysql -u root -p -e "DROP DATABASE mi_app_db_test;"

# Test de restauración en PostgreSQL
createdb -U postgres mi_app_db_test
pg_restore -U postgres -d mi_app_db_test backup.dump

# Verificar datos
psql -U postgres -d mi_app_db_test -c "
  SELECT 'clientes' AS tabla, COUNT(*) AS filas FROM clientes
  UNION ALL
  SELECT 'pedidos', COUNT(*) FROM pedidos
  UNION ALL
  SELECT 'productos', COUNT(*) FROM productos;
"

# Limpiar
dropdb -U postgres mi_app_db_test

# Script de verificación automática que se puede añadir al cron mensual
#!/bin/bash
# /usr/local/bin/verificar_backup.sh

ULTIMO_BACKUP=$(ls -t /var/backups/postgres/*.dump | head -1)
TEST_DB="verificacion_backup_$(date +%s)"

echo "Verificando backup: $ULTIMO_BACKUP"
createdb -U postgres "$TEST_DB"

pg_restore -U postgres -d "$TEST_DB" "$ULTIMO_BACKUP"

if [ $? -eq 0 ]; then
    FILAS=$(psql -U postgres -d "$TEST_DB" -t -c "SELECT COUNT(*) FROM clientes;")
    echo "Backup verificado: $FILAS clientes encontrados"
else
    echo "ERROR: la restauración del backup falló"
fi

dropdb -U postgres "$TEST_DB"

Opciones para bases de datos grandes

# Para bases de datos de varios GB, las opciones básicas pueden ser lentas
# Estas alternativas mejoran el rendimiento:

# MySQL: mydumper (backup paralelo, mucho más rápido que mysqldump)
# apt install mydumper
mydumper \
  --user=root \
  --password=contraseña \
  --database=mi_app_db \
  --outputdir=/var/backups/mysql/$(date +%Y%m%d)/ \
  --threads=4 \
  --compress \
  --trx-consistency-only

# Restaurar con myloader
myloader \
  --user=root \
  --password=contraseña \
  --database=mi_app_db \
  --directory=/var/backups/mysql/20260615/ \
  --threads=4

# PostgreSQL: pg_dump paralelo con formato directorio
pg_dump -U postgres -Fd -j 8 -d mi_app_db -f /var/backups/postgres/$(date +%Y%m%d)/

# Restaurar en paralelo
pg_restore -U postgres -d mi_app_db -j 8 /var/backups/postgres/20260615/

Point-in-time recovery: recuperar el estado exacto en un momento

Los backups periódicos te permiten restaurar al punto en el que se hizo el backup. Si el backup es de las 2 AM y el desastre ocurre a las 11 AM, pierdes 9 horas de datos. Para recuperar hasta el minuto exacto del fallo, necesitas WAL archiving en PostgreSQL o binary log en MySQL.

-- PostgreSQL: activar WAL archiving (en postgresql.conf)
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

-- MySQL: activar binary log (en my.cnf)
-- [mysqld]
-- log_bin = /var/log/mysql/mysql-bin
-- binlog_format = ROW
-- expire_logs_days = 7

-- Con binary log activo, para restaurar hasta las 11:00:00 del 15 de junio:
-- 1. Restaurar el último backup completo
mysql -u root -p mi_app_db < backup_20260615_020000.sql

-- 2. Aplicar los binary logs desde el backup hasta el momento del fallo
mysqlbinlog \
  --start-datetime="2026-06-15 02:00:00" \
  --stop-datetime="2026-06-15 10:59:59" \
  /var/log/mysql/mysql-bin.000001 \
  /var/log/mysql/mysql-bin.000002 | mysql -u root -p mi_app_db

Resumen

  • En MySQL, mysqldump --single-transaction genera un backup consistente sin bloquear la base de datos. Usa | gzip para comprimir y mysql para restaurar.
  • En PostgreSQL, pg_dump -Fc genera un backup comprimido y flexible que permite restauración selectiva con pg_restore. El formato directorio (-Fd -j N) permite backups paralelos para bases de datos grandes.
  • Guarda las credenciales en ~/.my.cnf (MySQL) o ~/.pgpass (PostgreSQL) con permisos 600, nunca en los scripts directamente.
  • Automatiza los backups con cron y elimina los más antiguos automáticamente con find -mtime.
  • Copia los backups a almacenamiento externo (otro servidor, S3, GCS): un backup en el mismo servidor no te protege si el servidor falla.
  • Prueba la restauración periódicamente en un entorno de prueba. Un backup no verificado no es fiable.
  • Para recuperación punto a punto (sin perder datos hasta el minuto del fallo), activa el binary log en MySQL o el WAL archiving en PostgreSQL.

¿Te ha gustado esta entrada?

Compártela con tus compañeros para que también sigan aprendiendo.

Comunidad y Comentarios

0 COMENTARIOS

No hay comentarios todavía. Sé el primero en compartir tu opinión.

Escribe tu opinión
Respondiendo a