Backup MySQL con mysqldump

El comando mysqldump del sistema gestor de base de datos MySQL sirve para hacer copias de seguridad. Lo explicamos con detenimiento y vemos sus opciones más importantes.
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.

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.
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!

Algunas de las opciónes que tiene 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 bloqueará 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 inluirá en la salida /*!40000 ALTER TABLE tb_name DISABLE KEYS */; y /*!40000 ALTER TABLE tb_name ENABLE KEYS */; Esto hará que 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ásquina 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

Ejemplos de comandos 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.

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.

Compartir

Comentarios

Andrés Rivera Montoya

22/4/2009
repado de la base de datos
apliqué este comando como lo indicas, pero no crea el respaldo. lo hice así:

mysqldump --u root mibase > archivo1.sql;

el usuario es: root

y la versión del mysql es: 5.0.24a-community-nt

¿cual es el error si existe mibase?

JMPerro

19/5/2009
Faltó una cosa
Hola, queria comentar que el articulo me sirvió para salir del paso, sólamente faltó agregar que existe una opción que permite incluir en el backup los procedimientos y funciones creados. Dicha opción es la siguiente:
--routines o -R

Saludos

yonatan

22/6/2009
se pueden crear mysqldump desde delphi
mi pregunta es si se pueden crear mysqldump desde delphi y como se ejecutan

Carlos Aguilar Sol

17/9/2009
Migracion de tablas de la version 4 a la 5
Muy buen articulo, muy claro y conciso.
Me ayudo muchisimo para migrar las tablas en la version 4 e incorporarlos en la version 5.
Muchas gracias, ya tenia ratos de estar buscando esta solucion

miguel Téllez

29/4/2010
Backup MySQL con mysqldump
Mi base de datos está en un servidor web, de donde obtengo el comando para instalarlo en éste servidor que no es windows.

Saludos

bug273

11/6/2010
backups
en la recuperacion de la base de datos esta el comando como:
mysql -password=*** -usuario=usuario mibase < copia.sql

el asunto es que hay que añadir mysqldump si no el comando da un error al ejecutarlo

Un saludo y gracias por el post

Lic. Juan Montiel

08/9/2010
Como Utilizar el mysqldump desde vfp.9
Hola me ha sido de gran utilidad el articulo puplicado, podria espesificar como debe ser llamado desde una aplicacion de vfp de tal forma a que se realize el backup gracias

Rafael

06/4/2011
Como descargar archivo
Muy buena explicacion... ahora mi duda es... como descargo el archivo generado? En mi caso mi base mysql se encuentra en un servidor dedicado en una empresa de hosting. Gracias.

ismael_leon

28/4/2011
hacer el backup de un mes determinado? MySql
Buenas,

Me gustaría realizar el backup de una bd, pero únicamente de un mes determinado, es decir, que si tengo una base de datos de 2010, me gustaría hacer el backup de los datos del mes de junio (por ejemplo) no de todo el año.

O por ejemplo del mes anterior al que estoy.

¿esto es posible?

Si es posible, posteriormente podré realizar el insert en la base de datos de este mes unicamente?

Desconozco si mysqldump tiene una opción para esta fecha.

Muchas gracias.

Un saludo,

Gerardo Lopez

04/3/2013
nombre de base de datos
buen dia lo que pasa es que tengo un respaldo que hice con mysqldump asi: mysqldump --opt --password=pass --user=user datos > datosBackup.sql de mi servidor pero quisiera hacer una copia de este dump en el mismo servidor con el nombre de datoscopia o algo asi y pues que no se sobre escriban los datos o se dupliquen si no que esten en una nueva base como seria la sentencia para hacerlo??