Copias de seguridad físicas y lógicas en MySQL: mysqldump, SELECT INTO y restauración

Copias de seguridad físicas y lógicas

Concepto general

Física: realizar un respaldo de todos los ficheros de la base de datos; generalmente se realiza en frío, es decir, hay que parar el motor de la base de datos.

Lógica: se exportan los datos del esquema de la base de datos a un fichero SQL o a un fichero de texto. Es bastante lenta, pero tiene la ventaja de que se pueden intercambiar datos entre los diferentes sistemas gestores de bases de datos. Tiene el nombre de exportación de datos. Cuando restauramos una copia de seguridad lógica en un SGBD, el proceso se conoce como importación de datos. Las copias lógicas generalmente se hacen en caliente; no es necesario parar el motor de la base de datos.

Mysqldump

mysqldump realiza un volcado de las instrucciones SQL necesarias para crear las tablas o las bases de datos que se quieran copiar. El archivo creado se puede ver en cualquier editor de texto. Las copias de seguridad se almacenan en bin si no se indica lo contrario.

Tres maneras de usar mysqldump

• mysqldump [opciones] nombre_de_base_de_datos [tablas]
• mysqldump [opciones] --databases DB1 [DB2 DB3 ...]
• mysqldump [opciones] --all-databases

Si no se nombra ninguna tabla o se utiliza la opción --databases o --all-databases, se vuelcan bases de datos enteras.

Opciones a destacar

• --add-drop-table: añade un comando DROP TABLE antes de cada comando CREATE TABLE.
• --add-locks: rodea cada volcado de tabla con los comandos LOCK TABLES y UNLOCK TABLES. Esto provoca inserciones más rápidas cuando el fichero volcado se recarga.
• --all-databases, -A: vuelca todas las tablas en todas las bases de datos. Es como utilizar la opción --databases y nombrar todas las bases de datos en la línea de comandos.
• --databases, -B: vuelca varias bases de datos. Normalmente, mysqldump trata el primer argumento de la línea de comandos como un nombre de base de datos y los siguientes como nombres de tablas; con esta opción trata todos los argumentos como nombres de bases de datos. Los comandos CREATE DATABASE IF NOT EXISTS nombre_de_base_de_datos y USE nombre_de_base_de_datos están incluidos en la salida.
• --quick, -q: vuelca tablas grandes. Recibe los registros de una tabla del servidor uno a uno.
• --result-file=fichero, -r fichero: redirige la salida a un fichero dado. Debe usarse en Windows.
• --tables: tiene mayor prioridad que --databases o -B. Todos los argumentos que vienen después de esta opción se tratan como nombres de tablas.
• --user=nombre_de_usuario, -u nombre_de_usuario: nombre de usuario MySQL a usar al conectar con el servidor.
• --verbose, -v: modo explícito. Muestra más información sobre lo que hace el programa.
• --where='condición_where', -w 'condición_where': vuelca registros seleccionados por la condición WHERE. Las comillas de la condición son obligatorias si contienen espacios o caracteres especiales.

Ejemplos de uso

• mysqldump -u root -p --where='campo1=juan' concesionario tabla 2 > c:/copia.copia3.sql : Se limita el volcado de registros en la tabla 2 de la BD concesionario a todos aquellos que cumplen que campo1='juan'.
• mysqldump -u root -p concesionario tabla > c:/copia.copia.sql : Copia de seguridad de la tabla tabla de la BD concesionario y se vuelca el archivo copia.sql en el directorio indicado.
• mysqldump -u root -p concesionario > c:/copia.copia2.sql : Copia de seguridad de toda la BD concesionario.

Restaurar copia de seguridad

Restaurar volcando el fichero SQL con el cliente MySQL:

• mysqldump -u root -p concesionario < c:/copia.copia1.sql
• También se puede recuperar mediante: source nombrearchivo.sql (desde el cliente MySQL).

Copias de seguridad lógicas con SELECT INTO

Crear: SELECT ... INTO OUTFILE 'ruta y nombre del archivo'.

Restaurar una copia creada con SELECT INTO:

• LOAD DATA INFILE 'nombredearchivo' INTO TABLE nombre_tabla;

Problemas en la restauración

• No se tienen permisos para leer archivos del servidor.
• No se han utilizado los mismos caracteres y terminadores de cierre en SELECT ... INTO que en LOAD DATA.
• El nombre de la ruta y del archivo no se ha especificado correctamente.
• No es posible sobrescribir un archivo existente.

Notas finales

Las diferencias entre copias físicas y lógicas (y las herramientas para cada una) determinan qué estrategia es más adecuada según el caso: tiempo de inactividad aceptable, tamaño de la base de datos, compatibilidad entre SGBD, y requisitos de recuperación. Las opciones de mysqldump permiten adaptar los volcados a necesidades concretas (tablas específicas, condiciones WHERE, múltiples bases de datos, etc.).