Tutorial SQL: Copias de seguridad y restauración de bases de datos

Crear backup exportando datos

Realizar copias de seguridad de nuestras bases de datos es una práctica fundamental para proteger la información contra pérdidas accidentales, fallos de hardware o errores humanos. La exportación de datos es uno de los métodos más comunes y efectivos para crear backups en sistemas de bases de datos relacionales.

En este apartado nos centraremos en las técnicas para exportar datos desde MySQL y PostgreSQL, generando archivos que contienen tanto la estructura como los datos de nuestras tablas.

Exportación de datos en MySQL

MySQL ofrece la herramienta mysqldump, un programa de línea de comandos que permite crear copias de seguridad completas o parciales de nuestras bases de datos. Esta utilidad genera un archivo SQL con todas las instrucciones necesarias para recrear la base de datos.

Sintaxis básica de mysqldump

La sintaxis general para usar mysqldump es:

mysqldump -u [usuario] -p [opciones] [base_de_datos] > archivo_backup.sql

Algunos ejemplos prácticos:

• Exportar una base de datos completa:

mysqldump -u root -p tienda > tienda_backup.sql

• Exportar tablas específicas:

mysqldump -u root -p tienda clientes pedidos > tienda_clientes_pedidos.sql

• Exportar solo la estructura (sin datos):

mysqldump -u root -p --no-data tienda > tienda_estructura.sql

• Exportar solo los datos (sin estructura):

mysqldump -u root -p --no-create-info tienda > tienda_datos.sql

Opciones avanzadas de mysqldump

MySQL ofrece opciones adicionales para personalizar nuestros backups:

• Incluir procedimientos almacenados y funciones:

mysqldump -u root -p --routines tienda > tienda_con_rutinas.sql

• Incluir eventos programados:

mysqldump -u root -p --events tienda > tienda_con_eventos.sql

• Exportar múltiples bases de datos:

mysqldump -u root -p --databases tienda inventario > multiples_dbs.sql

• Exportar todas las bases de datos:

mysqldump -u root -p --all-databases > todas_las_dbs.sql

Compresión de backups en MySQL

Para bases de datos grandes, es recomendable comprimir los backups para ahorrar espacio:

mysqldump -u root -p tienda | gzip > tienda_backup.sql.gz

Para descomprimir posteriormente:

gunzip < tienda_backup.sql.gz | mysql -u root -p tienda

Exportación de datos en PostgreSQL

PostgreSQL proporciona la herramienta pg_dump para crear copias de seguridad. Similar a mysqldump, genera archivos que contienen los comandos SQL necesarios para reconstruir la base de datos.

Sintaxis básica de pg_dump

La sintaxis general para usar pg_dump es:

pg_dump -U [usuario] [opciones] [base_de_datos] > archivo_backup.sql

Ejemplos prácticos:

• Exportar una base de datos completa:

pg_dump -U postgres tienda > tienda_backup.sql

• Exportar en formato comprimido:

pg_dump -U postgres -Fc tienda > tienda_backup.dump

• Exportar solo la estructura:

pg_dump -U postgres --schema-only tienda > tienda_estructura.sql

• Exportar solo los datos:

pg_dump -U postgres --data-only tienda > tienda_datos.sql

Formatos de salida en PostgreSQL

PostgreSQL permite exportar en diferentes formatos, cada uno con ventajas específicas:

• Formato de texto plano (por defecto):

pg_dump -U postgres -F p tienda > tienda_backup.sql

• Formato personalizado (comprimido y permite restauración selectiva):

pg_dump -U postgres -F c tienda > tienda_backup.dump

• Formato de directorio (crea un directorio con archivos para cada tabla):

pg_dump -U postgres -F d tienda -f directorio_backup

• Formato de archivo tar (similar al formato de directorio pero en un solo archivo):

pg_dump -U postgres -F t tienda > tienda_backup.tar

Exportación selectiva en PostgreSQL

PostgreSQL ofrece opciones para exportar partes específicas de la base de datos:

• Exportar tablas específicas:

pg_dump -U postgres -t clientes -t pedidos tienda > tablas_especificas.sql

• Exportar un esquema específico:

pg_dump -U postgres -n public tienda > esquema_public.sql

• Excluir tablas específicas:

pg_dump -U postgres -T logs -T temp_data tienda > sin_tablas_temporales.sql

Automatización de backups

Para garantizar la seguridad de nuestros datos, es recomendable automatizar el proceso de creación de backups mediante scripts programados.

Script básico para MySQL

#!/bin/bash
# Script para backup diario de MySQL

# Variables
FECHA=$(date +%Y-%m-%d)
USUARIO="root"
PASSWORD="tu_contraseña"
BD="tienda"
RUTA_BACKUP="/ruta/backups"

# Crear directorio si no existe
mkdir -p $RUTA_BACKUP

# Realizar backup
mysqldump -u $USUARIO -p$PASSWORD $BD | gzip > $RUTA_BACKUP/$BD-$FECHA.sql.gz

# Eliminar backups antiguos (más de 30 días)
find $RUTA_BACKUP -name "*.sql.gz" -type f -mtime +30 -delete

Script básico para PostgreSQL

#!/bin/bash
# Script para backup diario de PostgreSQL

# Variables
FECHA=$(date +%Y-%m-%d)
USUARIO="postgres"
BD="tienda"
RUTA_BACKUP="/ruta/backups"

# Crear directorio si no existe
mkdir -p $RUTA_BACKUP

# Realizar backup
export PGPASSWORD="tu_contraseña"
pg_dump -U $USUARIO -Fc $BD > $RUTA_BACKUP/$BD-$FECHA.dump

# Eliminar backups antiguos (más de 30 días)
find $RUTA_BACKUP -name "*.dump" -type f -mtime +30 -delete

Buenas prácticas para la exportación de datos

Para maximizar la efectividad de nuestras copias de seguridad, es importante seguir estas recomendaciones:

• Programar backups regulares: Establece una frecuencia adecuada según la criticidad de los datos.
• Verificar la integridad de los backups periódicamente realizando pruebas de restauración.
• Almacenar copias en múltiples ubicaciones, incluyendo servidores remotos o servicios en la nube.
• Documentar el proceso de backup y restauración para que cualquier miembro del equipo pueda ejecutarlo.
• Cifrar los backups que contengan información sensible.
• Monitorizar el espacio disponible en los sistemas de almacenamiento de backups.
• Etiquetar claramente cada backup con información como fecha, versión y contenido.

Exportación de datos con herramientas gráficas

Además de las herramientas de línea de comandos, existen interfaces gráficas que facilitan la creación de backups.

MySQL Workbench

MySQL Workbench ofrece una interfaz intuitiva para exportar datos:

1. Selecciona "Server" > "Data Export" en el menú principal.
2. Elige la base de datos o tablas específicas a exportar.
3. Selecciona entre "Export to Self-Contained File" o "Export to Dump Project Folder".
4. Configura opciones adicionales como incluir rutinas o eventos.
5. Haz clic en "Start Export" para iniciar el proceso.

pgAdmin para PostgreSQL

pgAdmin simplifica la exportación de datos en PostgreSQL:

1. Haz clic derecho en la base de datos que deseas exportar.
2. Selecciona "Backup..." en el menú contextual.
3. Configura el formato de salida, compresión y otras opciones.
4. Especifica la ruta del archivo de salida.
5. Haz clic en "Backup" para iniciar el proceso.

La exportación regular de datos es una parte esencial de cualquier estrategia de administración de bases de datos. Implementando estas técnicas, podrás proteger eficazmente la información almacenada en tus sistemas MySQL y PostgreSQL contra posibles pérdidas o daños.

Restaurar backup

La capacidad de restaurar correctamente una copia de seguridad es tan importante como crearla. Incluso el mejor sistema de backups resulta inútil si no podemos recuperar los datos cuando los necesitamos. En esta sección aprenderemos cómo restaurar backups en MySQL y PostgreSQL, asegurándonos de que nuestros datos puedan ser recuperados eficientemente en caso de emergencia.

Restauración de backups en MySQL

MySQL ofrece varios métodos para restaurar copias de seguridad, dependiendo del formato en que fueron creadas. El proceso más común implica utilizar el cliente de línea de comandos mysql para ejecutar los archivos SQL generados previamente con mysqldump.

Restauración básica desde un archivo SQL

Para restaurar una base de datos completa desde un archivo SQL:

mysql -u root -p [nombre_base_datos] < archivo_backup.sql

Si la base de datos aún no existe, primero debes crearla:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS [nombre_base_datos]"
mysql -u root -p [nombre_base_datos] < archivo_backup.sql

Ejemplos prácticos:

• Restaurar una base de datos existente:

mysql -u root -p tienda < tienda_backup.sql

• Restaurar desde un backup comprimido:

gunzip < tienda_backup.sql.gz | mysql -u root -p tienda

Restauración selectiva en MySQL

A veces necesitamos restaurar solo partes específicas de una base de datos. Aunque MySQL no ofrece una forma directa de restaurar tablas individuales desde un archivo de backup completo, podemos usar algunas técnicas:

• Extraer definiciones de tablas específicas de un archivo de backup:

grep -n "CREATE TABLE \`clientes\`" tienda_backup.sql

Esto nos mostrará los números de línea donde comienza la definición de la tabla, permitiéndonos extraer solo esa parte.

• Restaurar solo la estructura (si tenemos un backup de solo estructura):

mysql -u root -p tienda < tienda_estructura.sql

• Restaurar solo los datos (si tenemos un backup de solo datos):

mysql -u root -p tienda < tienda_datos.sql

Opciones avanzadas de restauración en MySQL

MySQL ofrece opciones adicionales para controlar el proceso de restauración:

• Restaurar con progreso visible (útil para backups grandes):

pv tienda_backup.sql | mysql -u root -p tienda

Nota: Requiere instalar la utilidad pv (pipe viewer).

• Restaurar con control de errores:

mysql -u root -p --force tienda < tienda_backup.sql

La opción --force continúa la restauración incluso si se encuentran errores.

• Restaurar con mayor velocidad (desactivando temporalmente restricciones):

mysql -u root -p --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" tienda < tienda_backup.sql

Restauración de backups en PostgreSQL

PostgreSQL proporciona herramientas específicas para restaurar backups según el formato en que fueron creados. La principal utilidad es pg_restore para formatos personalizados, y el cliente psql para archivos SQL de texto plano.

Restauración desde formato de texto plano

Si el backup se creó en formato de texto plano (SQL):

psql -U postgres -d [nombre_base_datos] -f archivo_backup.sql

Si la base de datos no existe:

createdb -U postgres [nombre_base_datos]
psql -U postgres -d [nombre_base_datos] -f archivo_backup.sql

Ejemplo práctico:

psql -U postgres -d tienda -f tienda_backup.sql

Restauración desde formato personalizado

Para backups creados con el formato personalizado (-Fc), debemos usar pg_restore:

pg_restore -U postgres -d [nombre_base_datos] archivo_backup.dump

Ejemplos prácticos:

• Restaurar en una base de datos existente:

pg_restore -U postgres -d tienda tienda_backup.dump

• Crear la base de datos y restaurar en un solo paso:

pg_restore -U postgres -C -d postgres tienda_backup.dump

La opción -C crea la base de datos antes de restaurar.

Restauración selectiva en PostgreSQL

Una de las ventajas del formato personalizado de PostgreSQL es la capacidad de restaurar selectivamente objetos específicos:

• Listar el contenido de un archivo de backup:

pg_restore -l tienda_backup.dump > lista_contenido.txt

• Editar la lista para seleccionar solo ciertos objetos y luego restaurar:

pg_restore -U postgres -d tienda -L lista_editada.txt tienda_backup.dump

• Restaurar solo tablas específicas:

pg_restore -U postgres -d tienda -t clientes -t pedidos tienda_backup.dump

• Restaurar solo el esquema (estructura) sin datos:

pg_restore -U postgres -d tienda --schema-only tienda_backup.dump

• Restaurar solo los datos sin la estructura:

pg_restore -U postgres -d tienda --data-only tienda_backup.dump

Opciones avanzadas de restauración en PostgreSQL

PostgreSQL ofrece opciones adicionales para personalizar el proceso de restauración:

• Restaurar con mayor paralelismo (más rápido en sistemas multicore):

pg_restore -U postgres -d tienda -j 4 tienda_backup.dump

La opción -j especifica el número de procesos paralelos.

• Restaurar sin propietarios (útil al migrar entre servidores):

pg_restore -U postgres -d tienda --no-owner tienda_backup.dump

• Restaurar sin privilegios:

pg_restore -U postgres -d tienda --no-privileges tienda_backup.dump

Verificación post-restauración

Después de restaurar una base de datos, es crucial verificar que la restauración se haya completado correctamente:

Verificación en MySQL

-- Verificar que todas las tablas existen
SHOW TABLES;

-- Comprobar el número de registros en tablas críticas
SELECT COUNT(*) FROM clientes;
SELECT COUNT(*) FROM pedidos;

-- Verificar la integridad referencial
CHECK TABLE clientes, pedidos, productos FOR UPGRADE;

Verificación en PostgreSQL

-- Verificar que todas las tablas existen
\dt

-- Comprobar el número de registros en tablas críticas
SELECT COUNT(*) FROM clientes;
SELECT COUNT(*) FROM pedidos;

-- Verificar la integridad de la base de datos
VACUUM ANALYZE;

Restauración en entornos de producción

Cuando se restaura un backup en un entorno de producción, hay consideraciones adicionales que debemos tener en cuenta:

• Planificar una ventana de mantenimiento para minimizar el impacto en los usuarios.
• Notificar a los usuarios sobre el tiempo de inactividad esperado.
• Crear un backup adicional del estado actual antes de restaurar.
• Probar la restauración en un entorno de pruebas antes de aplicarla en producción.
• Documentar cada paso del proceso de restauración.

Solución de problemas comunes durante la restauración

Es normal encontrar algunos obstáculos durante el proceso de restauración. Estos son algunos problemas frecuentes y sus soluciones:

Problemas en MySQL

• Error de tabla ya existente:

mysql -u root -p --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" tienda < tienda_backup.sql

• Error de codificación de caracteres:

mysql -u root -p --default-character-set=utf8mb4 tienda < tienda_backup.sql

• Timeout en restauraciones grandes:

mysql -u root -p --max_allowed_packet=1G tienda < tienda_backup.sql

Problemas en PostgreSQL

• Error de rol no existente:

pg_restore -U postgres --no-owner -d tienda tienda_backup.dump

• Conflictos con objetos existentes:

pg_restore -U postgres --clean -d tienda tienda_backup.dump

La opción --clean elimina los objetos antes de crearlos nuevamente.

• Errores de codificación:

pg_restore -U postgres --encoding=UTF8 -d tienda tienda_backup.dump

Restauración con herramientas gráficas

Las interfaces gráficas también facilitan el proceso de restauración para usuarios menos familiarizados con la línea de comandos.

MySQL Workbench

1. Selecciona "Server" > "Data Import" en el menú principal.
2. Elige "Import from Self-Contained File" y selecciona el archivo de backup.
3. Selecciona la base de datos de destino o crea una nueva.
4. Configura las opciones de importación según sea necesario.
5. Haz clic en "Start Import" para iniciar el proceso.

pgAdmin para PostgreSQL

1. Haz clic derecho en la base de datos donde deseas restaurar.
2. Selecciona "Restore..." en el menú contextual.
3. Selecciona el archivo de backup y configura las opciones.
4. Ajusta las opciones en las pestañas "Restore options" según necesites.
5. Haz clic en "Restore" para iniciar el proceso.

Estrategias de restauración para diferentes escenarios

La estrategia de restauración debe adaptarse al tipo de incidente que ha provocado la necesidad de recuperar datos:

• Recuperación puntual (después de un error humano):

En MySQL:

  mysql -u root -p tienda < tienda_backup_20230615.sql

En PostgreSQL:

  pg_restore -U postgres -d tienda tienda_backup_20230615.dump

• Migración a un nuevo servidor:

En MySQL:

  mysqldump -u root -p --routines --triggers tienda | mysql -h nuevo_servidor -u root -p tienda

En PostgreSQL:

  pg_dump -U postgres -Fc tienda | pg_restore -U postgres -h nuevo_servidor -d tienda

• Restauración parcial (solo ciertas tablas después de corrupción):

En PostgreSQL (más sencillo con su formato personalizado):

  pg_restore -U postgres -d tienda -t tabla_corrupta --clean tienda_backup.dump

La capacidad de restaurar eficientemente los backups es una habilidad fundamental para cualquier administrador de bases de datos. Dominar estas técnicas te permitirá responder con confianza ante situaciones de emergencia, minimizando el tiempo de inactividad y garantizando la integridad de tus datos.