Importar/Exportar Bases de Datos grandes en MySQL

Bg8VC08IQAAl6Hp

No se si alguna vez ha pasado tener que importar una base de datos en MySQL de varias gigas, pues yo ya lo he hecho un par de veces. Lamentablemente la primera vez apenas busqué información y estuvo ejecutándose la importación durante varios días, un disparate jejeje. Cuando he tenido que volver a importar una base de datos enorme, me he puesto a indagar un poco y he dado con este artículo. Y siguiendo sus pasos, me he dado me he dado cuenta de que la anterior vez, hice un disparate completamente.

Una de las claves está a la hora de exportar, tienes que tener en cuenta que por ejemplo los índices en MySQL meten mucha información que complicará la vida a la hora de importar. Normalmente para importar una BBDD por línea de comando usamos el comando mysqldump, así:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Pero para cuando son muy grandes las BBDD te recomiendo que la exportes:

mysqldump -u [USERNAME] -p [DBNAME] –ignore-table=[DBNAME].search_index | gzip > [/path_to_file/DBNAME].sql.gz

 Esta es la parte de exportación, ahora nos queda la importación de los datos, que lo primero que tendríamos que hacer es descomprimir el fichero gz, una vez descomprimido, ejecutamos en la línea de comandos mysql y seguimos estos pasos:

SHOW DATABASES;
DROP DATABASE [DBNAME];
CREATE DATABASE [DBNAME];
USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;

Un último consejo para aumentar la velocidad a la hora de importar muchos datos (> 1 GB), puedes añadir un par de líneas en la sección [mysqld] de tu fichero my.cnf y reiniciar MySQL:

[mysqld]
# Performance settings used for import.
delay_key_write=ALL
bulk_insert_buffer_size=256M

Espero que os haya gustado este artículo, no viene siguiendo la línea de Marketing Online de mis anteriores post, pero la retomo para los siguientes :).

Actualización:

Mi amigo Miguel Angel Ruiz, me hace las siguientes advertencias:

Cuidado con el delay_key_write=ALL, puede producir corrupción de índices en las tablas.

Para casos como el tuyo de base de datos grandes yo pongo estas opciones a mysqldump al exportar:

–delayed-insert: Crea los inserts como “INSERT DELAYED”, esto quiere decir que los procesa en bloque al importarlos a tu base de datos. Vamos, que si tienes 30.000 registros en una tabla te los inserta todos de una vez en bloque, si no lo activas te inserta los 30.000 registros uno a uno que es bastante más lento.
–compress: Comprime la información enviada entre mysqldump y el servidor, menos datos a transmitir, mayor velocidad.
–add-locks: Acelera la importación porque añade un bloqueo a la tabla antes de empezar a insertar y luego la desbloquea al finalizar.
–disable-keys: Desactiva los índices temporalmente, se recrean una vez finalizada la importación, en vez de hacerlo cada vez que insertas un registro. Si ya has activado –delayed-insert esta opción no se nota mucho ya que en ese caso los índices se crean en bloque al añadir los registros de golpe.

Importantísimo:
Si las tablas son de tipo myISAM es necesario añadir –lock-all-tables para que realice un bloqueo de las tablas antes de realizar la copia de seguridad, con esto nos aseguramos la integridad referencial.
Si las tablas son de tipo innoDB es necesario añadir –single-transaction por la misma razón.

–quick: Es un comando que acelera el proceso de mysqldump para tablas grandes, que cosas que tengan un comando tan sencillo. Para tablas con pocos registros baja un poco el rendimiento, pero si en general son tablas grandes, merece la pena.

Es buena idea si tienes tablas con objetos blob y cadenas muy largas de caracteres, aumentar el valor de la variable max_allowed_packet en my.ini, también se nota que va mejor.

 

 

You may also like...