MySQL: Respaldos con mysqldump

Uso Básico

#

mysqldump es una utilidad del lado del cliente que puede usarse para hacer respaldos lógicos. Generará las sentencias SQL necesarias para reproducir la base de datos original.

Las siguientes sentencias representan algunos usos comunes de mysqldump:

1. mysqldump -u username -p database_name [table_name] > dump.sql
2. mysqldump -u username -p --databases db1_name db2_name > dump.sql
3. mysqldump -u username -p --all-databases > dump.sql

El primer ejemplo es para respaldar una sola base de datos. Si necesitas respaldar tablas específicas en lugar de toda la base de datos, escribe sus nombres, separados por un espacio.

Con la opción --databases, puedes respaldar dos o más bases de datos, sus nombres deben estar separados por un espacio.

Para respaldar todas las bases de datos en tu servidor MySQL, solo agrega la opción --all-databases.

El archivo dump.sql no contiene la sentencia SQL create database. Si lo necesitas, puedes agregarlo con la opción -B. No es necesario si ejecutas mysqldump con las opciones --databases y --all-databases.

Ignorar tablas también es posible cuando estás respaldando una base de datos. Para ello debes agregar la opción --ignore-tables.

$ mysqldump -u username -p database_name --ignore-tables=database_name.table1 > database_name.sql

Si necesitas ignorar más de una tabla, solo usa la opción tantas veces como sea necesario.

$ mysqldump -u root -p database_name --ignore-table=database_name.table1 --ignore-table=database_name.table2 > database_name.sql

Respaldo del Esquema

#

En caso de que necesites respaldar solo el esquema de tu base de datos, sin ningún registro, ejecuta mysqldump con la opción --no-data:

$ mysqldump -u username -p database_name --no-data > dump.sql

También puedes respaldar el esquema cuando ejecutes mysqldump con las opciones --databases y --all-databases.

$ mysqldump -u username -p --all-databases --no-data > dump.sql

$ mysqldump -u username -p --databases db1_name db2_name --no-data > dump.sql

Restaurar Datos

#

Para restaurar las bases de datos en tu archivo dump.sql, ejecuta el siguiente comando:

$ mysqldump -u root -p < dump.sql

Si necesitas restaurar solo una base de datos del respaldo completo, lo puedes hacer ejecutando cualquiera de las siguientes sentencias:

$ mysqldump -u root -p -o database_name < dump.sql

$ mysqldump -u root -p --one-database database_name < dump.sql

En ambos casos, la base de datos debe existir en tu servidor MySQL, ya que solo restaurara el esquema y los datos.

Respaldo Condicional

#

Si necesitas crear un respaldo que contenga datos que coincidan con una condición, puedes usar una cláusula WHERE con mysqldump.

Puedes usar una sola condición WHERE:

$ mysqldump database_name table_name --where="id > 500" > dump.sql

O multiples condiciones:

$ mysqldump database_name users --where="id > 500 and disabled = 0" > dump.sql

Como se explica aquí en el sitio mysqldump.guru.

Por ejemplo, en una base de datos con el siguiente esquema, construido a partir del conjunto de datos de Movienet:

Si quieres respaldar las películas producidas en un país específico, como México, una forma de hacerlo es ejecutando mysqldump con una cláusula WHERE.

$ mysqldump -u root -p movienet movies --where=”country = 22” > dump.sql

22 es el country_id de México en esta particular base de datos, creada usando este script de Python.

También puedes obtener esos valores ejecutando la siguiente sentencia SQL:

select movies.movie_id, movies.title, countries.name as country from movies inner join countries on movies.country = countrie
s.country_id and movies.country = '22';

+-----------+-----------------------------------------------------------+---------+
| movie_id  | title                                                     | country |
+-----------+-----------------------------------------------------------+---------+
| tt0047501 | Sitting Bull (1954)                                       | Mexico  |
| tt0049046 | Canasta de cuentos mexicanos (1956)                       | Mexico  |
| tt0076336 | Hell Without Limits (1978)                                | Mexico  |
| tt0082048 | El barrendero (1982)                                      | Mexico  |
| tt0082080 | Blanca Nieves y sus 7 amantes (1980)                      | Mexico  |
| tt0083057 | El sexo de los pobres (1983)                              | Mexico  |
| tt0110185 | El jardín del Edén (1994)                                 | Mexico  |
| tt0116043 | De jazmín en flor (1996)                                  | Mexico  |
| tt0121322 | El giro, el pinto, y el Colorado (1979)                   | Mexico  |
| tt0133354 | Algunas nubes (1995)                                      | Mexico  |
| tt0207055 | La risa en vacaciones 4 (TV Movie 1994)                   | Mexico  |
| tt0208889 | To and Fro (2000)                                         | Mexico  |
| tt0211878 | La usurpadora (TV Series 1998– )                          | Mexico  |
| tt0220306 | El amarrador 3 (1995)                                     | Mexico  |
| tt0229008 | El vampiro teporocho (1989)                               | Mexico  |

Omitir Bases de Datos

#

No hay una opción para mysqldump para omitir bases de datos al generar el respaldo, pero aquí hay una solución que puede funcionar:

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -u username -p -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump -u username -p ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

El script de Bash anterior geneará el respaldo de tu servidor MySQL, excluyendo las bases de datos information_schema y mysql, listadas en la variable EXCLUSION_LIST, así como las bases de datos de tu elección en la variable DATABASES_TO_EXCLUDE.

No olvides agregar las bases de datos que quieres excluir a la variable DATABASES_TO_EXCLUDE, reemplazando username, en los comandos mysql y mysqldump, y agrega las opciones necesarias a la variable MYSQLDUMP_OPTIONS.

Consideraciones de Seguridad

#

Si solo necesitas el esquema de una base de datos, puedes ejecutar mysqldump con la opción --no-data. Pero si necesitas restaurar el esquema de una base de datos específica de un respaldo completo, encontré una solución interesante:

cat dump.sql | grep -v ^INSERT | mysql -u username -p

El comando anterior va a restaurar el esquema de la base de datos, omitiendo las sentencias SQL correspondientes a la inserción de datos. Funciona bien cuando respaldas una sola base de datos, pero no hay razón para usarlo ya que puedes obtener el esquema con la opción --no-data, en lugar de remover las sentencias INSERT.

¿Qué pasa si intentas ejecutar este comando con un respaldo que incluye todas las bases de datos en tu servidor? Este comando intentará sobreescribir el esquema del sistema en la base de datos mysql, lo cual es peligroso. Esta base de datos almacena los detalles de autenticación, y al sobreescribir los datos perderás el acceso al servidor.

Si no necesitas respaldar la base de datos mysql, ejecuta mysqldump con la opción --databases para especificar que bases de datos necesitas o usa el script compartido en la sección Skipping Databases.

Conclusión

#

A través de este artículo aprendiste como usar mysqldump para respaldar las bases de datos en tu servidor MySQL, así como algunas recomendaciones para usar esta herramienta.

Artículo original publicado en inglés en percona.community