¿Requieres de una instalación o configuración de Linux o sus servicios?
¿Un desarrollo WEB empresarial a la medida?
¿Un curso o capacitación a la medida?
Revisa el sitio de SERVICIOS de LinuxTotal

mysqldump - Gu�a pr�ctica 

Copyright © 2005-2019 LinuxTotal.com.mx
Se concede permiso para copiar, distribuir y/o modificar este documento siempre y cuando se cite al autor y la fuente de linuxtotal.com.mx y según los términos de la GNU Free Documentation License, Versión 1.2 o cualquiera posterior publicada por la Free Software Foundation.

Autor: Sergio González D.  (sergio.gonzalez.duran@gmail.com)

---

mysqldump es parte de los "MySQL Client Programs" programas de cliente de MySQL, que puede ser utilizado para generar respaldos de bases de datos y ser usados o incluso para ser transferidos a otro servidor de base datos SQL (No estrictamente tiene que ser un servidor MySQL).

En este guía práctica de LinuxTotal aprenderás (por ejemplos) los usos y opciones más comunes de este potente comando de MySQL.

Uso básico

Tres formas básicas de invocar mysqldump son posibles:

1) mysqldump [opciones] nombre_bd [nombre_tabla1 nombre_tabla2 ...] > respaldo.sql
2) mysqldump [opciones] --databases nombre_bd1 nombre_bd2 > respaldo.sql
3) mysqldump [opciones] --all-databases > respaldo.sql

1) respalda una sola base de datos, indicando su nombre, y opcionalmente una o más tablas de la misma base de datos. Si no se indican tablas, se respaldan todas.
2) respalda una o más bases de datos de forma completa, no se pueden indicar tablas individuales de esta manera.
3) respalda de forma completa todas las bases de datos del servidor MySQL de forma completa, no se pueden indicar tablas individuales de esta manera.

En todos los casos se indica que la salida del comando, es decir, el vaciado del respaldo se guarde en el archivo llamado "respaldo.sql", este archivo es el resultado final, lo que puede utilizarse después para restaurar una base de datos (véase ejemplos más adelante).

Opciones más comunes

mysqldump es un comando con decenas de opciones, para verlas todas utiliza la ayuda en línea que se proporciona a través de --help

mysqldump --help

Lo anterior muestra las posibles opciones específicas de la versión de MySQL que utilices asi que pueden variar con respecto a la siguiente lista:

mysqldump OPCIONES MÁS COMUNES
Opción	Corto	Descripción
--add-drop-database		Añade la sentencia 'DROP DATABASE' antes de cada sentencia 'CREATE DATABASE'
--add-drop-table		Añade la sentencia 'DROP TABLE' antes de cada sentencia 'CREATE TABLE'
--all-databases	-A	Respalda todas las tablas de todas las bases de datos.
--comments	-i	Añade comentarios en el archivo de respaldo. Esta opción está por defecto activada, para desactivar los comentarios utiliza --skip-comments.
--compatible=nombre		Produce una salida más compatible con otros sistemas de bases de datos o versiones anteriores de MySQL. nombre puede tomar estos valores: ansi, mysql323, mysql40, postgresql, mssql, oracle, db2, maxdb.
--complete-insert	-c	Usa sentencias INSERT completas que incluyen nombres de columnas.
--create-options		Incluye todas las opciones específicas de MySQL para la creación de tablas que se usan con la sentencia 'CREATE TABLE'.
--databases	-B	Permite respaldar una o más bases de datos. Después de la opción se indica(n) el(los) nombre(s) de la(s) base de datos a respaldar. Se respalda cada base de datos completa. En la salida se incluye con esta opción las sentencias 'CREATE DATABASE' y 'USE' antes de cada nueva base de datos.
--extended-insert	-e	Usa la sentencia 'INSERT' con el formato de múltiples registros con varias listas de 'VALUES'. Esto produce una salida más reducida y acelera el proceso de INSERTS cuando el archivo es restaurado. Ideal para bases de datos con tablas que contienen miles de registros.
--force	-f	Si se tienen views o vistas en la base de datos a respaldar y la vista hace referencia a una tabla que ya no existe, el vaciado del respaldo terminará con un error sin completarse. Con esta opción se podrá continuar.
--host=nombre	-h nom	Se realiza el respaldo de el servidor MySQL indicado por nombre, puede ser una dirección IP o un nombre de servidor válido. Si no se especifica el default es localhost.
--ignore-table=bd.tabla		Si no se desea respaldar una tabla en específico se utiliza esta opción, es obligario indicar la base de datos un punto y después nombre de la tabla. Si son varias tablas las que se han de ignorar se usa esta opción múltiples veces. También puede usarse para ignorar vistas.
--master-data		Si el respaldo viene de un servidor MySQL que actua como maestro en una esquema de replicación, el uso de esta opción es muy importante ya que incluira sentencias del tipo "CHANGE MASTER TO" que indican la posición en los archivos de bitácora binarios necesarios para que los servidores esclavos se sincronicen adecuadamente.
--no-create-db	-n	Esta opción suprime las sentencias "CREATE DATABASE" que por defecto se incluyen en el respaldo.
--no-data	-d	No escribe o respalda registro alguno solo el esquema de la base de datos.
--opt		Esta opción esta por defecto activada, produce un vaciado rápido y totalmente compatible con otros servidores MySQL. Es un alias que activa las opciones --add-drop-table, --create-options, --extended-insert, entre otras.
--password[=pass]	-p[pass]	La contraseña para conectarse al servidor indicado en la opción --host. Si usas la opción corta p no debe haber un espacio. Es posible no indicar la contraseña y después de presionar la tecla intro se preguntará por esta.
--port=num	-P num	Si servidor indicado en la opción --host utiliza un puerto diferente al default del servicio mysql (3306) debe de indicarse con esta opción.
--quick	-q	Va de la mano con la opción --single-transaction ya que aplica principalmente para tablas grandes y del tipo transaccional. Permite agilizar la escritura al archivo de respaldo al leer registro por registro sin mandarlo a un buffer previo.
--quote-names	-Q	Opción por defecto activada. Pone entre comilla grave "`" a los nombres de los objetos como base de datos, tablas, columnas. Es recomendable dejarla siempre para evitar problemas de compatibilidad, pero si se desea desactivar utiliza la opción --skip-quote-names
--routines	-R	Incluye en el respaldo rutinas almacenadas (procedimientos y funciones). Con esta función se incluyen las sentencias "CREATE PROCEDURE" Y "CREATE FUNCTION" que permiten re-crear completamente procedimientos almacenados y funciones. Esta opción NO esta por defecto en mysqldump asi que es motivo de sorpresa para DBA's nuevos enterarse que el respaldo en el que tanto confiaban no está completo al momento de necesitarlo, sino utilizaron esta opción.
--single-transaction		Opción útil solo con tablas transaccionales como las del tipo InnoDB, ya que garantiza la integridad y la consistencia sin bloquear las tablas. Esto lo logra al emitir una sentencia "START TRANSACTION" antes de vaciar los datos al respaldo.
--skip-opt		Desactiva la opción por defecto --opt, con lo que se tienen que indicar individualmente las opciones a las que --opt hace referencia.
--triggers		Incluye triggers creados en el respaldo. Esta opción es automática, esta por defecto, sino se quiere utilizar el respaldo de triggers utiliza --skip-triggers
--user=nombre	-u nom	El nombre de usuario de MySQL para conectarse al servidor indicado en la opción --host.
--verbose	-v	Modo verboso. Imprime información de lo que está haciendo el respaldo, usa doble -v -v para modo aun más verboso.
--xml	-X	En vez de un respaldo por defecto formateado con sentencias SQL, esta opción ofrece una salida en un formato XML bien formado.

Ejemplos de uso de mysqldump

Respaldo de una sola base de datos completa

mysqldump clientes > clientes.sql   

Respaldo de una sola base de datos con dos tablas

mysqldump clientes saldos facturas  > clientes.sql   

Respaldo completo de base de datos clientes y ventas

mysqldump --databases clientes ventas > respaldo_cli_ven_sep_2011.sql   

Respaldamos la base de datos clientes pero ignoramos las tablas 'temporal' y 'basura' (Obligatorio indicar base.tabla)

mysqldump clientes --ignore-table=clientes.temporal --ignore-table=clientes.basura > respaldo_clientes_2011.sql   

Respaldo completo de todas las bases de datos

mysqldump --all-databases > respaldo_full_sep_2011.sql   

Si se tiene contraseña (como debe ser) se indica usuario y que pregunte por el password

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

No muy buena idea, pero se puede indicar el password directamente, además nos aseguramos que se indiquen las opciones por defecto más comunes

mysqldump -u root -psecreto --all-databases --opt  > respaldo_full_sep_2011.sql   

Respaldo de una base de datos transaccional tipo InnoDB o BDB asegurando su consistencia

mysqldump -u root -p --single-transaction --quick ventas  > respaldo_ventas_2011.sql   

Todas las bases de datos del host 192.168.0.100 y agregamos los procedemientos almacenados que sean respaldados también.

mysqldump -h 192.168.1.100 -u root -p --routines --all-databases  > respaldo_ventas_2011.sql   

Respaldo de las bases de datos clientes y pedidos, con todas las opciones específicas para re-crear las tablas, además añadimos 'drop database' para asegurarnos que en la restauración se creé desde cero el respaldo, además ignoramos errores..

mysqldump -u root -p --create-options --add-drop-database --force --databases clientes pedidos  > respaldo_ven_ped_2011.sql   

Respaldo completo de un servidor MySQL maestro en replicación, indicando en el respaldo la posición para sincronización con servidores esclavos, además añadimos insertar completos que incluyen los nombres de columnas en sentencias INSERT

mysqldump -u root --password=secreto --all-databases --master-data  --complete-insert  > respaldo_2011.sql   

Respaldamos solo el esquema de clientes sin registros

mysqldump --no-data clientes > respaldo_esquema_clientes_2011.sql   

Se produce una salida compatible para restaurar la base de datos en Oracle

mysqldump --compatible=oracle --databases clientes > respaldo_clientes_oracle_2011.sql   

mysqldump con gzip

Al mismo tiempo que realizamos el respaldo podemos comprimirlo para ahorrar espacio en nuestros respaldos.

mysqldump -u root -p --all-databases | gzip > respaldo_2011.sql.gz

Para descomprimir lo anterior y dejar el archivo listo para la restauración en si utiliza gunzip

gunzip respaldo_2011.sql.gz

Restaurando el respaldo

Hay varias maneras de lograr la restauración del respaldo. Con el comando mysql:

mysql -u root -p < respaldo.sql

Si se utilizó gzip para comprimir el respaldo, se puede descomprimir y restaurar en una sola línea:

gunzip < respaldo.sql.gz | mysql -u root -p 

Si el respaldo contiene una sola base de datos y no contiene sentencias 'drop database' y 'create database', se puede entonces indicar la base de datos donde se debe realizar la restauración:

mysql -u root -p clientes < respaldo_clientes.sql

Lo anterior (cuando ya existe la base de datos) también se puede lograr con el comando mysqlimport

mysqlimport -u root -p clientes respaldo_clientes.sql

Es posible también utilizar la opción "-e" (execute) de mysql, que permite ejecutar un archivo con sentencias SQL, y un respaldo es exactamente eso, un largo script con sentencias SQL para recrear esquema y datos:

mysql -u root -p -e "source /ruta/a/respaldo.sql"

Un simple script para respaldos automáticos por medio de cron

El siguiente es un ejemplo de un script muy simple para respaldar todas las bases de datos con las opciones más comppletas de manera autómatica, además se comprime. El archivo que se genera contiene la fecha y la hora al momento del respaldo, por medio de cron se programa su ejecución a las 11:00 de la noche todos los días.

Solo si la ejecución del comando fue correcta y termina, anexa un pequeño mensaje al final de un archivo de bítacora, asi podemos consultarlo y observar que todos los días terminó correctamente

Guardar el respaldo como respaldoMySQL.sh y cambia sus permisos de ejecución que permita ejecución chmod 700 respaldoMySQL.sh (ver artículos relacionados más abajo para enlace de este comando)

#!/bin/bash

#################################################################################################
# Copyright  2005-2019 Sergio González Durán (sergio.gonzalez.duran@gmail.com)             
# Se concede permiso para copiar, distribuir y/o modificar este programa siempre                 
# y cuando se cite al autor y la fuente de linuxtotal.com.mx y según los términos                
# de la GNU General Public License,  
# Versión 3 o cualquiera O Posterior publicada por la Free Software Foundation.                  
#################################################################################################

# fecha y hora, ejemplo 20110920_2300
# archivo termina como 'mysql_20110920_2300.sql.gz'

fecha=`date +%Y%m%d_%H%M`
archivo="mysql_$fecha.sql.gz"

# elimina la opción --master-data si tu servidor no es maestro en replicación

mysqldump --user=root --password=xyz --all-databases --opt --routines \
--add-drop-database --add-drop-table --complete-insert --create-options \
--master-data | gzip > /ruta/a/respaldos/$archivo && \
echo "Respaldo realizado exitosamente el `date`" >> /ruta/a/bitacora.txt

#adicionalmente si se tiene otro servidor linux, copiamos el archivo ya
#generado de respaldo por medio de scp de ssh y asi aseguramos que el 
#respaldo este en otra locación, logrando redundancia (en los artículos
#relacionados chécate "Autentifiación SSH con llave privada")
# El usuario que ejecuta el comando es 'userdba' que existe en ambos servidores

su -c "scp -pq /ruta/a/respaldos/$archivo 192.168.1.100:/ruta/a/respaldosmysql/." userdba \
&& echo "Copia a servidor remoto se completo con exito el `date`" >> /ruta/a/bitacora.txt

La línea cron correspondiente para su ejecución programada quedaría de la siguiente manera en /etc/crontab

0 23 * * * root /ruta/a/respaldoMySQL.sh

Artículos relacionados para comprender el uso del script

Manejo del comando date

Manual de cron

Permisos de archivos y directorios (chmod, etc.)

Autentificación SSH con llave privada (scp)

---

¿Requieres de una instalación o configuración de Linux o sus servicios?
¿Un desarrollo WEB empresarial a la medida?
¿Un curso o capacitación a la medida?
Revisa el sitio de SERVICIOS de LinuxTotal