uniwebsidad

Copias de seguridad avanzadas para bases de datos MySQL

10 de junio de 2013
sistemas mysql

Cuando se realizan copias de seguridad de bases de datos MySQL, resulta habitual utilizar el comando mysqldump sin opciones. Sin embargo, este comando tiene decenas de opciones interesantes que permiten realizar copias de seguridad avanzadas.

Si lo único que quieres es realizar copias de seguridad MySQL sencillas y saber cómo recuperar después la información, puedes leer primero el tutorial Cómo hacer copias de seguridad de una base de datos MySQL.

Incluyendo solamente algunas tablas en la copia de seguridad

Por defecto, el comando mysqldump vuelca todas las tablas de la base de datos indicada. Para incluir solamente una o más tablas, indica sus nombres después del nombre de la base de datos:

# volcando una sola tabla
$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            NOMBRE_BASE_DE_DATOS NOMBRE_TABLA > copia_seguridad.sql

# volcando tres tablas
$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA NOMBRE_BASE_DE_DATOS
            NOMBRE_TABLA_1 NOMBRE_TABLA_2 NOMBRE_TABLA_3 > copia_seguridad.sql

Excluyendo algunas tablas de la copia de seguridad

Por defecto, el comando mysqldump vuelca todas las tablas de la base de datos indicada. Para no incluir en la copia de seguridad alguna tabla concreta, indícalo con la opción --ignore-table, cuyo valor debe indicarse como NOMBRE_BASE_DE_DATOS.NOMBRE_TABLA (si indicas solamente el nombre de la tabla, se producirá un error):

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --ignore-table=NOMBRE_BASE_DE_DATOS.NOMBRE_TABLA
            NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

Si estás haciendo por ejemplo una copia de seguridad de una base de datos llamada tienda pero no quieres que se incluya una tabla llamada ventas, ejecuta el siguiente comando:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --ignore-table=tienda.ventas tienda > copia_seguridad.sql

Para excluir varias tablas, indica sus nombres con otras tantas opciones --ignore-table:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --ignore-table=NOMBRE_BASE_DE_DATOS.NOMBRE_TABLA_1
            --ignore-table=NOMBRE_BASE_DE_DATOS.NOMBRE_TABLA_2
            --ignore-table=NOMBRE_BASE_DE_DATOS.NOMBRE_TABLA_3
            NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

Limitando el número de registros de cada tabla

Por defecto, el comando mysqldump vuelca todos los registros de todas las tablas. Si quieres filtrar previamente los registros, añade la opción --where, que permite indicar la condición de tipo WHERE que se añade a las consultas SELECT que se realizan para extraer todos los registros:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --where="edad > 18 AND edad < 65"
            NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

Si lo que te interesa es simplemente limitar el número de registros volcados para cada tabla, puedes utilizar la opción --where junto con el siguiente truco extraído del sitio StackOverflow:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --where="1 limit 1000"
            NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

La opción --where="1 limit 1000" hace que sólo se extraigan los primeros 1.000 registros de cada tabla. Para ajustar este valor a tus necesidades, reemplaza el valor 1000 pero no cambies la primera parte (1 limit).

Volcando la estructura de las tablas pero no sus datos

Por defecto, el comando mysqldump vuelca tanto la estructura de las tablas como toda su información. Si sólo te interesa volcar la estructura de las tablas y columnas, utiliza la opción --no-data. Así podrás crear otra base de datos exactamente igual pero vacía:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --no-data NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

Creando archivos con líneas más cortas

Por defecto, el comando mysqldump combina cientos de instrucciones INSERT individuales en una sola gran instrucción INSERT para insertar muchos registros a la vez:

INSERT INTO `NOMBRE_DE_TABLA`
       VALUES (1, '...', '...', '...'), (2, '...', '...', '...'),
              (3, '...', '...', '...'), (4, '...', '...', '...'),
              (5, '...', '...', '...'), (6, '...', '...', '...');

Este es el comportamiento recomendado en la mayoría de las situaciones, pero puede provocar errores con sistemas antiguos incapaces de procesar líneas de miles de bytes de longitud. Igualmente, puedes tener problemas con tu editor de textos al intentar abrir un archivo de copia de seguridad que contiene estas líneas tan largas.

Si este es tu caso, añade la opción --extended-insert=false para hacer que cada INSERT se ejecute con su propia instrucción:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --extended-insert=false NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

Si abres ahora el archivo de la copia de seguridad, verás cómo ya no existen instrucciones INSERT múltiples:

INSERT INTO `NOMBRE_DE_TABLA` VALUES (1, '...', '...', '...');
INSERT INTO `NOMBRE_DE_TABLA` VALUES (2, '...', '...', '...');
INSERT INTO `NOMBRE_DE_TABLA` VALUES (3, '...', '...', '...');
INSERT INTO `NOMBRE_DE_TABLA` VALUES (4, '...', '...', '...');
INSERT INTO `NOMBRE_DE_TABLA` VALUES (5, '...', '...', '...');
INSERT INTO `NOMBRE_DE_TABLA` VALUES (6, '...', '...', '...');

El principal problema de esta opción es que la recuperación de datos es varios órdenes de magnitud más lenta que cuando se combinan varios registros en una única instrucción INSERT.

Evitando el bloqueo de las tablas

Por defecto, el comando mysqldump bloquea las tablas de la base de datos antes de hacer el volcado de la información. Este es el comportamiento recomendado para evitar inconsistencias al recuperar la información.

Sin embargo, si estás haciendo una copia de seguridad del servidor de producción y tiene mucha actividad, este comportamiento puede ser inaceptable. Para evitarlo, añade la opción --lock-tables=false:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --lock-tables=false NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

Creando bases de datos con datos binarios

Por defecto, el comando mysqldump vuelca toda la información tal y como se almacena en la base de datos. Si guardas archivos binarios (como imágenes o archivos PDF) en alguna tabla, pueden producirse errores al procesar después la información binaria o al recuperarla.

Para evitar estos problemas, puedes forzar a que MySQL convierta la información binaria a un formato hexadecimal más seguro para transmitirlo y recuperarlo. Para ello, añade la opción --hex-blob:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --hex-blob NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

El principal problema de esta opción es que puede aumentar mucho el tamaño del archivo de la copia de seguridad.

Cambiando el formato de salida

Por defecto, el comando mysqldump hace un volcado de información en formato SQL. Si prefieres utilizar el formato XML, añade la opción --xml:

$ mysqldump --user=TU_USUARIO --password=TU_CONTRASEÑA
            --xml NOMBRE_BASE_DE_DATOS > copia_seguridad.sql

El contenido del archivo generado será algo como lo siguiente:

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <database name="NOMBRE_BASE_DE_DATOS">
        <table_structure name="XXX">
            <field Field="ID" Type="int(11)" Null="NO" Key="PRI"
                   Extra="auto_increment" />
            <!-- ... -->
        </table_structure>
        <table_data name="XXX">
            <row>
                <field name="ID">1</field>
                <!-- ... -->
            </row>
        </table_data>
    </database>
</mysqldump>

Referencias útiles