8.7. El programa de copia de seguridad de base de datos mysqldump

MySQL 5.0

8.7. El programa de copia de seguridad de base de datos mysqldump

El cliente mysqldump puede utilizarse para volcar una base de datos o colección de bases de datos para copia de seguridad o para transferir datos a otro servidor SQL (no necesariamente un servidor MySQL). EL volcado contiene comandos SQL para crear la tabla y/o rellenarla.

Si está haciendo una copia de seguridad del servidor, y las tablas son todas MyISAM, puede considerar usar mysqlhotcopy ya que hace copias de seguridad más rápidas y restauraciones más rápidas, que pueden realizarse con el segundo programa. Consulte Sección 8.8, “El programa de copias de seguridad de base de datos mysqlhotcopy”.

Hay tres formas de invocar mysqldump:

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

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

Para obtener una lista de las opciones que soporta su versión de mysqldump, ejecute mysqldump --help.

Si ejecuta mysqldump sin las opciones --quick o --opt, mysqldump carga el resultado entero en memoria antes de volcarlo. Esto puede ser un problema si está volcando una base de datos grande. En MySQL 4.0, --opt está activado por defecto, pero puede desactivarse con --skip-opt.

Si está utilizando una copia reciente del programa mysqldump para generar un volcado que tiene que ser cargado en un servidor MySQL muy viejo, no debe usar las opciones --opt ni -e .

mysqldump soporta las siguientes opciones:

--help, -?

Muestra un mensaje de error y sale.
--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. Consulte Sección 7.2.14, “Velocidad de la sentencia INSERT”.
--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 comando.
--allow-keywords

Permite la creación de columnas con nombres que son palabras claves. Esto funciona añadiendo un prefijo a cada nombre de columna con el nombre de tabla.
--comments[={0|1}]

Si tiene como valor 0, suprime información adicional en el fichero de volcado como la versión del programa, la versión del servidor, y el equipo. --skip-comments tiene el mismo efecto que --comments=0. El valor por defecto es 1, que incluye la información extra.
--compact

Produce una salida (output) menos explícita. Esta opción suprime comentarios y activa las opciones --skip-add-drop-table, --no-set-names, --skip-disable-keys, y --skip-add-locks .
--compatible=nombre

Produce una salida que es compatible con otros sistemas de bases de datos o con servidores MySQL antiguos. El valor de name puede ser ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, o no_field_options. Para usar varios valores, sepárelos por comas. Estos valores tienen el mismo significado que las opciones correspondientes para asignar el modo SQL del servidor. Consulte Sección 5.3.2, “El modo SQL del servidor”.

--complete-insert, -c

Usa comandos INSERT compuestos que incluyen nombres de columnas.

--compress, -C

Comprime toda la información enviada entre el cliente y el servidor si ambos admiten compresión.

--create-options

Incluye todas las opciones de tabla específicas de MySQL en el comando CREATE TABLE.

--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 argumentos como nombres de tablas. Con esta opción, trata todos los argumentos como nombres de bases de datos. Los comandos CREATE DATABASE IF NOT EXISTSnombre_de_base_de_datos y USEnombre_de_base_de_datos están incluídos en la salida (output) antes de cada nueva base de datos.

--debug[=opciones_de_depuración], -# [opciones_de_depuración]

Escribe un log de depuración. La cadena de caracteres opciones_de_depuración normalmente es 'd:t:o,nombre_de_fichero'.

--default-character-set=conjunto_de_caracteres

Usa conjunto_de_caracteres como el conjunto de caracteres por defecto. Consulte Sección 5.9.1, “El conjunto de caracteres utilizado para datos y ordenación”. Si no se especifica, mysqldump desde MySQL 5.0 utiliza utf8.

--delayed-insert

Inserta registros usando comandos INSERT DELAYED. Esta opción se eliminió en MySQL 5.0.7.

--delete-master-logs

En servidores de replicación maestros, borra los logs binarios tras realizar la operación de volcado. En MySQL 5.0, esta opción se activa automáticamente --master-data.

--disable-keys, -K

Para cada tabla, rodea el comando INSERT con /*!40000 ALTER TABLEtbl_name DISABLE KEYS */; y /*!40000 ALTER TABLEtbl_name ENABLE KEYS */;. Esto hace que la carga del fichero volcado en MySQL 4.0 o posteriores sea más rápida porque los índices se crean sólo tras la inserción de todos los registros. Esta opción es efectiva sólo para tablas MyISAM .

--extended-insert, -e

Usa la sintaxis de INSERT de múltiples registros, que incluyen una lista de varios VALUES. Esto genera un fichero de volcado de menor tamaño y acelera las inserciones cuando se recarga el fichero.

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

Estas opciones se usan con la opción -T y tienen el mismo significado que las cláusulas correspondientes de LOAD DATA INFILE. Consulte Sección 13.2.5, “Sintaxis de LOAD DATA INFILE”.

--first-slave, -x

Obsoleto. Ahora es --lock-all-tables.

--flush-logs, -F

Vuelca los ficheros de log MySQL antes de empezar el volcado. Esta opción necesita el permiso RELOAD. Tenga en cuenta que si utiliza esta opción en combinación con la opción --all-databases (o -A) los logs se vuelcan para cada base de datos volcada. La excepción es cuando se usa --lock-all-tables o --master-data. En estos casos, los logs se vuelcan sólo una vez, en el momento en que todas las tablas están bloqueadas. Si quiere que el volcado de la base de datos y el del log ocurran exactamente en el mismo momento, debe usar --flush-logs junto con --lock-all-tables o --master-data.

--force, -f

Continúa incluso si ocurre un error SQL durante un volcado de tabla.

--host=nombre_de_equipo, -hnombre_de_equipo

Vuelca datos de un servidor MySQL en el equipo dado. Por defecto el equipo es localhost.

--hex-blob

Vuelca columnas de cadenas de caracteres binarios usando notación hexadecimal (por ejemplo, 'abc' es 0x616263). Las columnas sobre las que tiene efecto en MySQL 5.0 son BINARY, VARBINARY, BLOB.

--lock-all-tables, -x

Bloquea todas las tablas de todas las bases de datos. Esto se consigue estableciendo un bloqueo de lectura global que dura durante todo el volcado. Esta opción desactiva automáticamente --single-transaction y --lock-tables.

--lock-tables, -l

Bloquea todas las tablas antes de comenzar el volcado. Las tablas se bloquean con READ LOCAL para permitir inserciones concurrentes en caso de tablas MyISAM . Para tablas transaccionales como InnoDB y BDB, --single-transaction es una opción mucho mejor, ya que no necesita bloquear las tablas.

Tenga en cuenta que al volcar múltiples bases de datos, --lock-tables bloquea tablas para cada base de datos separadamente. Así, esta opción no garantiza que las tablas en el fichero volcado sean lógicamente consistentes entre bases de datos. Tablas en bases de datos distintas pueden volcarse en estados completamente distintos.

--master-data[=valor]

Esta opción causa que se escriba en la salida (output) la posición y el nombre de fichero del log binario. Esta opción necesita el permiso RELOAD y el log binario debe estar activado. Si el valor de la opción es igual a 1, la posición y nombre de fichero se escriben en la salida del volcado en forma de comando CHANGE MASTER que hace que un servidor esclavo empiece desde la posición correcta en el log binario del maestro si usa este volcado SQL del maestro para preparar un esclavo. Si el valor de la opción es igual a 2, el comando CHANGE MASTER se escribe como un comentario SQL. Ésta es la acción por defecto si se omite valor .

La opción --master-data activa --lock-all-tables, a no ser que --single-transaction también esté especificado (en tal caso, se establece un bloqueo de lectura global sólo durante un corto periodo de tiempo al principio del volcado. Consulte la descripción de --single-transaction. En cualquier caso, cualquier acción en logs se realiza en el momento exacto del volcado. Esta opción automáticamente desactiva --lock-tables.

--no-create-db, -n

Esta opción suprime el comando CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name que se incluye de otro modo en la salida si se especifica las opciones --databases o --all-databases.

--no-create-info, -t

No escribe los comandos CREATE TABLE que recrean cada tabla volcada.

--no-data, -d

No escribe ningún registro de la tabla. Esto es muy útil si sólo quiere obtener un volcado de la estructura de una tabla.

--opt

Esta opción es una abreviatura; es lo mismo que especificar --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. Causa una operación de volcado rápida y produce un fichero de volcado que puede recargarse en un servidor MySQL rápidamente. En MySQL 5.0, --opt está activado por defecto, pero puede desactivarse con --skip-opt. Para desactivar sólo algunas de las opciones activadas por --opt, use la forma --skip; por ejemplo --skip-add-drop-table o --skip-quick.

--password[=contraseña], -p[contraseña]

La contraseña a usar al conectar con el servidor. Si usa la opción en forma corta (-p), no puede haber un espacio entre la opción y la contraseña. Si omite el valor de contraseña a continuación de la opción --password o -p en la línea de comandos, aparece un prompt pidiéndola.

--port=número_de_puerto, -Pnúmero_de_puerto

El puerto TCP/IP a usar en la conexión.

--protocol={TCP | SOCKET | PIPE | MEMORY}

Protocolo de conexión a usar.

--quick, -q

Esta opción es útil para volcar tablas grandes. Fuerza mysqldump a recibir los registros de una tabla del servidor uno a uno en lugar de recibir el conjunto completo de registros y guardarlos en memoria antes de escribirlos.

--quote-names, -Q

Limita los nombres de base de datos, tablas, y columnas con caracteres '`' . Si el modo SQL del servidor incluye la opción ANSI_QUOTES, los nombres se ponen entre caracteres '"'. En MySQL 5.0, --quote-names está activado por defecto, pero puede desactivarse con --skip-quote-names.

--result-file=fichero, -r fichero

Redirige la salida a un fichero dado. Esta opción debe usarse en Windows, ya que previene que los caracteres de nueva línea '\n' se conviertan en secuencias de retorno/nueva línea '\r\n'.

--set-charset

Añade SET NAMESconjunto_de_caracteres_por_defecto a la salida (output). Esta opción está activada por defecto. Para suprimir el comando SET NAMES, use --skip-set-charset.

--single-transaction

Esta opción realiza un comando SQL BEGIN antes de volcar los datos del servidor. Es útil sólo con tablas transaccionales tales como las InnoDB y BDB, ya que vuelca el estado consistente de la base de datos cuando se ejecuta BEGIN sin bloquear ninguna aplicación.

Cuando use esta opción, debe tener en cuenta que sólo las tablas InnoDB se vuelcan en un estado consistente. Por ejemplo, cualquier tabla MyISAM o HEAP volcadas mientras se usa esta opción todavía pueden cambiar de estado.

La opción --single-transaction y la opción --lock-tables son mutuamente exclusivas, ya que LOCK TABLES provoca que cualquier transacción pendiente se confirme implícitamente.

Para volcar tablas grandes, debe combinar esta opción con --quick.

--socket=ruta, -S ruta

El fichero socket a usar cuando se conecta con localhost (que es el equipo por defecto).

--skip-comments

Consulte la descripción de la opción --comments.

--tab=ruta, -T ruta

Produce ficheros con datos separados por tabuladores. Para cada tabla volcada mysqldump crea un fichero nombre_de_tabla.sql que contiene el comando CREATE TABLE que crea la tabla, y un fichero nombre_de_tabla.txt que contiene los datos. El valor de esta opción es el directorio en el que escribir los ficheros.

Por defecto, los ficheros de datos .txt se formatean usando tabuladores entre los valores de las columnas y una nueva línea tras cada registro. El formato puede especificarse explícitamente usando las opciones --fields-xxx y --lines--xxx.

Nota: Esta opción debe usarse sólo cuando mysqldump se ejecuta en la misma máquina que el servidor mysqld. Se debe tener el permiso FILE, y el servidor debe tener permisos para escribir ficheros en el directorio que se especifique.

--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, -unombre_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.

--version, -V

Muestra información de versión y sale.

--where='condición_where', -w 'condición_where'

Vuelca sólo registros seleccionados por la condición WHERE dada. Tenga en cuenta que las comillas alrededor de la condición son obligatorias si contienen espacios o caracteres especiales para el intérprete de comandos.

Ejemplos:

"--where=user='jimf'" "-wuserid>1" "-wuserid<1"

--xml, -X

Escribe la salida del volcado como XML bien formado.

Puede cambiar las siguientes variables usando las opciones --nombre_de_variable=valor:

max_allowed_packet

Tamaño máximo del búfer para la comunicación cliente/servidor. En MySQL 5.0, el valor de esta variable puede ser de hasta 1GB.

net_buffer_length

Tamaño inicial del búfer para la comunicación cliente/servidor. Cuando se crean comandos de inserción de múltiples registros (como con las opciones --extended-insert o --opt), mysqldump crea registros de longitud máxima net_buffer_length. Si incrementa esta variable, debe asegurarse también de que la variable net_buffer_length en el servidor MySQL tenga como mínimo esta longitud.

También es posible cambiar variables usando la sintaxis --set-variable=nombre_de_variable=valor o -Onombre_de_variable=valor. Sin embargo, esta sintaxis está obsoleta.

El uso más común de mysqldump es para hacer una copia de seguridad de toda la base de datos:

shell> mysqldump --opt nombre_de_base_de_datos > fichero_de_seguridad.sql

El siguiente ejemplo muestra cómo volcar el fichero de seguridad de nuevo en el servidor:

shell> mysql nombre_de_base_de_datos < fichero_de_seguridad.sql

El siguiente ejemplo obtiene el mismo resultado que el anterior:

shell> mysql -e "source /ruta/fichero_de_seguridad.sql" nombre_de_base_de_datos

mysqldump es muy útil para poblar bases de datos copiando los datos de un servidor MySQL a otro:

shell> mysqldump --opt nombre_de_base_de_datos | mysql --host=nombre_de_equipo_remoto -C nombre_de_base_de_datos

Es posible volcar varias bases de datos con un solo comando:

shell> mysqldump --databases nombre_de_base_de_datos_1 [nombre_de_base_de_datos_2 ...] > mis_bases_de_datos.sql

Si quiere volcar todas las bases de datos, use la opción --all-databases:

shell> mysqldump --all-databases > todas_las_bases_de_datos.sql

Si las tablas se guardan con el motor de almacenamiento InnoDB, mysqldump proporciona una forma de realizar una copia de seguridad de las mismas (consulte los comandos a continuación). Esta copia de seguridad sólo necesita un bloqueo local de todas las tablas (usando FLUSH TABLES WITH READ LOCK) al principio del volcado. En cuanto obtiene el bloqueo, se lee el log binario y se libera el bloqueo. Si y sólo si un comando de actualización largo está en ejecución cuando se ejecuta FLUSH..., el servidor MySQL puede quedar bloqueado hasta que acabe este comando largo, y luego el volcado queda sin ningún bloqueo. Si el servidor MySQL recibe sólo comandos de actualización cortos (en el sentido de "poco tiempo de ejecución"), incluso si son muchos, el periodo inicial de bloqueo no debe ser un problema.

shell> mysqldump --all-databases --single-transaction > todas_las_bases_de_datos.sql

Para una recuperación en un momento dado (también comocido como "roll-forward", cuando necesita restaurar una copia de seguridad antigua y recrear los cambios que han ocurrido desde tal copia de seguridad), es útil rotar el log binario (consulte Sección 5.10.3, “El registro binario (Binary Log)”) o al menos conozca las coordinadas del log binario que se corresponden con el volcado:

shell> mysqldump --all-databases --master-data=2 > todas_las_bases_de_datos.sql or shell> mysqldump --all-databases --flush-logs --master-data=2 > todas_las_bases_de_datos.sql

El uso simultáneo de --master-data y --single-transaction proporciona una forma de hacer copias de seguridad en línea apropiadas para recuperaciones en un momento dado, si las tablas se guardan con el motor de almacenamiento InnoDB.

Para más información sobre copias de seguridad, consulte Sección 5.8.1, “Copias de seguridad de bases de datos”.

previous page start next page