En este artículo vamos a conocer una herramienta esencial de MySQL, para realizar copias de bases de datos, o backups, en el lenguaje SQL. Se trata de mysqldump, un comando que funciona en el terminal, compatible con cualquier sistema operativo donde corra MySQL.

Comenzaremos analizando las opciones disponibles en MySQLdump, pero si lo que buscas son soluciones rápidas te recomiendo que vayas al final, donde encontrarás una serie de ejemplos de uso para crear backups y ejemplos de uso para restaurar los respaldos realizados. Seguramente que resultarán muy útiles para situaciones variadas.

Este es el guión de contenidos del artículo Backup MySQL con mysqldump.

• Qué es MySQL Dump
• Opciones disponibles en el comando
• Ejemplos de comandos mysqldump:
• Restaurar la base de datos
• Ejemplo práctico para restaurar un backup de una base de datos MySQL

Qué es MySQL Dump

Este comando permite hacer la copia de seguridad de una o múltiples bases de datos. Además permite que estas copias de seguridad se puedan restaurar en distintos tipos de gestores de bases de datos, sin la necesidad de que se trate de un gestor de MySQL. Esto lo consigue creando unos ficheros, que contienen todas las sentencias SQL necesarias para poder restaurar la tabla, que incluyen desde la sentencia de creación de la tabla, hasta una sentencia insert por cada uno de los registros que forman parte de la misma.

Para poder restaurar la copia de seguridad, bastará con ejecutar todas las sentencias SQL que se encuentran dentro del fichero, bien desde la línea de comandos de mysql, o desde la pantalla de creación de sentencias sql de cualquier entorno gráfico como puede ser el Mysql Control Center.

Las limitaciones de la restauración dependerán de las opciones que se han especificado a la hora de hacer la copia de seguridad, por ejemplo, si se incluye la opción --add-drop-table al hacer la copia de seguridad, se podrán restauran tablas que existen actualmente en el servidor (borrándolas primero). Por lo que es necesario estudiar primero los procedimientos que se utilizarán tanto en la copia como en la restauración, para que todo salga correcto!

Opciones disponibles en el comando mysqldump

El comando dispone de una amplia variedad de opciones que nos permitirá realizar la copia de la forma más conveniente para el propósito de la misma. Algunas de las opciónes más importantes son:

--add-locks

Añade LOCK TABLES antes, y UNLOCK TABLE despues de la copia de cada tabla.

--add-drop-table

Añade un drop table antes de cada sentencia create

-A, --all-databases

Copia todas las bases de datos. Es lo mismo que utilizar --databases seleccionando todas.

-a, --all

Incluye todas las opciones de creación específicas de Mysql.

--allow-keywords

Permite la creación de nombes de columnas que son palabras clave, esto se realiza poniendo de prefijo a cada nombre de columna, el nombre de la tabla

-c, --complete-insert

Utiliza inserts incluyendo los nombres de columna en cada sentencia (incrementa bastante el tamaño del fichero)

-C, --compress

Comprime la información entre el cliente y el servidor, si ambos soportan compresión.

-B, --databases

Para copiar varias bases de datos. En este caso, no se especifican tablas. El nombre de los argumentos se refiere a los nombres de las bases de datos. Se incluirá USE db_name en la salida antes de cada base de datos.

--delayed

Inserta las filas con el comando INSERT DELAYED.

-e, --extended-insert

Utiliza la sintaxis de INSERT multilinea. (Proporciona sentencias de insert más compactas y rápidas.)

-#, --debug[=option_string]

Utilización de la traza del programa (para depuración).

--help

Muestra mensaje de ayuda y termina.

--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...

Estas opciones se utilizan con la opción -T y tienen el mismo significado que la correspondiente cláusula LOAD DATA INFILE.

-F, --flush-logs

Escribe en disco todos los logs antes de comenzar con la copia.

-f, --force,

Continúa aunque se produzca un error de SQL durante la copia.

-h, --host=..

Copia los datos del servidor de Mysql especificado. El servidor por defecto es localhost.

-l, --lock-tables.

Bloquea todas las tablas antes de comenzar con la copia. Las tablas se bloquean con READ LOCAL para permitir inserts concurrentes en caso de las tablas MyISAM.

Cuando se realiza la copia de múltiples bases de datos, --lock-tables bloquea la copia de cada base de datos por separado. De forma que esta opción no garantiza que las tables serán consistentes lógicamente entre distintas bases de datos. Las tablas en diferentes bases de datos se copiarán en estados completamente distintos.

-K, --disable-keys

Se incluirá en la salida /*!40000 ALTER TABLE tb_name DISABLE KEYS */; y /*!40000 ALTER TABLE tb_name ENABLE KEYS */; Esto hará que la carga de datos en un servidor MySQL 4.0 se realice más rápido debido a que los índices se crearán después de que todos los datos hayan sido restaurados.

-n, --no-create-db

No se incluirá en la salida CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; Esta línea se incluye si la opción --databases o --all-databases fue seleccionada.

-t, --no-create-info

No incluirá la información de creación de la tabla (sentencia CREATE TABLE).

-d, --no-data

No incluirá ninguna información sobre los registros de la tabla. Esta opción sirve para crear una copia de sólo la estructura de la base de datos.

--opt

Lo mismo que --quick --add-drop-table --add-locks --extended-insert --lock-tables. Esta opción le debería permitir realizar la copia de seguridad de la base de datos de la forma más rápida y efectiva.

-pyour_pass, --password[=your_pass]

Contraseña utilizada cuando se conecta con el servidor. Si no se especifica, `=your_pass', mysqldump preguntará la contraseña.

-P, --port=...

Puerto utilizado para las conexiones TCP/IP

--protocol=(TCP | SOCKET | PIPE | MEMORY)

Especifica el protocolo de conexión que se utilizará.

-q, --quick

No almacena en el buffer la sentencia, la copia directamente a la salida. Utiliza mysql_use_result() para realizarlo.

-Q, --quote-names

Entrecomilla las tablas y nombres de columna con los caracteres ``'.

-r, --result-file=...

Redirecciona la salida al fichero especificado. Esta opción se debería utilizar en MSDOS, porque previene la conversión de nueva línea `\n' en nueva línea y retorno de carro`\n\r'.

--single-transaction

Utiliza el comando BEGIN antes de realizar la copia desde el servidor. Es muy útil con las tables InnoDB y el nivel de transacción READ_COMMITTED, porque en este modo realizará la copia de seguridad en un estado consistente sin necesidad de bloquear las aplicaciones. Consultar el manual para más detalles.

-S /path/to/socket, --socket=/path/to/socket

El fichero de sockets que se especifica al conectar al localhost (que es el host predeterminado).

--tables

sobreescribe la opción --databases (-B).

-T, --tab=path-to-some-directory

Crea un fichero table_name.sql, que contiene la sentencia de creación de SQL, y un fichero table_name.txt, que contiene los datos de cada tabla. El formato del fichero `.txt' se realiza de acuerdo con las opciones --fields-xxx y --lines--xxx options. Nota: Esta opción sólo funciona si el comando mysqldump se ejecuta en la misma máquina que el demonio mysqld, el usuario deberá tener permisos para crear y escribir el fichero en la ubicación especificada

-u nombre_usuario, --user=nombre_usuario

El nombre de usuario que se utilizará cuando se conecte con el servidor, el valor predeterminado es el del usuario actual.

-v, --verbose

Va mostrando información sobre las acciones que se van realizando (más lento)

-w, --where='cláusula where'

Sirve para realizar la copia de determinados registros

-X, --xml

Realiza la copia de seguridad en un documento xml

-x, --first-slave

Bloquea todas las tablas de todas las bases de datos

--no-tablespaces

Esta opción elimina todas las instrucciones CREATE LOGFILE GROUP y CREATE TABLESPACE en la salida de mysqldump.

Ejemplos de comandos mysqldump:

Voy a comenzar por unos comandos completos de mysqldump que he probado y que funcionan en la vida real. Luego podré otros comandos más sencillos que usan pocos parámetros de configuración, más a modo didáctico.

Observarás que en todos los comandos de esta primera sección uso --no-tablespaces esto es porque mi usuario no tiene permisos para hacer algunas cosas de las que hace mysqldump por abajo, si usas otros usuarios con mayores privilegios, o quizás root, no te hará falta ese flag.

mysqldump --no-tablespaces -u miuser -p -h 127.0.0.1 mi_database > ./lalala.sql

Este comando hace el backup completo de una base de datos mi_database, accediendo al host con 127.0.0.1 (localhost) y diciéndole que te solicite la clave. Además el backup de mysql lo colocará en el archivo lalala.sql

mysqldump --no-tablespaces -u miuser -p -h 127.0.0.1 mi_database mi_tabla > ./lalala.sql

Este comando es muy similar al anterior, solo que hace únicamente el backup de una tabla de la base de datos.

Comandos de mysqldump básicos

Ahora vamos a ver comandos con opciones más básicas, a modo didáctico, para que veamos algunos ejemplos que nos puedan aclarar algunas de las funciones del comano mysqldump.

Para realizar la copia se seguridad de la base de datos mibase al fichero copia_seguridad.sql

mysqldump --opt mibase > copia_seguridad.sql

Otro ejemplo más complejo de comando mysqldump para hacer el backup de una base de datos es el siguiente:

mysqldump --opt --password=miclave --user=miuser mibasededatos > archivo.sql

En este último caso estamos indicando un nombre de usuario y una clave para acceder a la base de datos sobre la que se está haciendo el backup: mibasededatos. Las sentencias SQL para reconstruir esa base de datos se volcarán en el fichero archivo.sql.

mysqldump --opt --password=clave --user=usuario Base_de_datos tabla1 tabla2 > backupdostablas.sql

Este comando es prácticamente igual que el anterior, pero en este caso nos realiza la copia de seguridad de únicamente dos tablas "tabla1" y "tabla2", que están en la base de datos "Base_de_datos".

Restaurar la base de datos

Si deseamos recuperar la información de un fichero para restaurar una copia de seguridad de la base de datos lo haremos con el comando mysql. Utilizaremos una sintaxis como esta:

mysql mibase < archivo.sql

En este ejemplo se restauraría la base de de datos mibase con el backup almacenado en el fichero archivo.sql.

Otro ejemplo más complejo de comando para restaurar una base de datos es el siguiente:

mysql --password=miclave --user=miuser mibase < archivo.sql

Es el mismo ejemplo que el anterior, pero indicando un nombre de usuario y una clave con las que acceder a la base de datos mibase.

Ejemplo práctico para restaurar un backup de una base de datos MySQL

Supongamos que tienes un archivo llamado mysql_ddbb.sql y quieres mediante terminal restaurarlo en una bbdd mysql que tienes instalada en local.:

• host es localhost
• DATABASE=default
• USERNAME=defaultuser
• PASSWORD=secret

Entonces, te situas en el terminal en la carpeta donde está el archivo y lanzas este comando:

mysql -h localhost -u defaultuser -p default < mysql_ddbb.sql

Enseguida te pedirá el password y colocas el password señalado.

Alternativa con 127.0.0.1

En algunos sistemas el host "localhost" puede que no te permita la conexión, entonces lanza este comando, intercambiando localhost por la ip de localhost:

mysql -h 127.0.0.1 -u defaultuser -p default < mysql-pae_ddbb.sql

Alternativa indicando el password en el comando

Aunque no es recomendable incluir la contraseña directamente en el comando por razones de seguridad, te vamos a dar la alternativa, que puede ser necesaria si quieres hacer un script que ejecute ese proceso sin que te pida la contraseña cada vez que lo lanzas. En este caso el comando sería:

mysql -h localhost -u defaultuser -psecret default < mysql-pae_ddbb.sql

o bien con la IP de localhost:

mysql -h 127.0.0.1 -u defaultuser -psecret default < mysql-pae_ddbb.sql