Pasar de un solo datafile InnoDB a uno por tabla (MYSQL)

Durante una instalación por defecto de MySQL, un error muy común es no modificar la configuración de como almacena los datos en motor InnoDB. Por defecto toda la información es almacenada en un solo datafile, que normalmente se llama:

Source   
ibdata1

Después tan alegremente importamos la BD que ocupa 500Mb en ese momento y 1 año más tarde puede ocupar 4Gb, pues bien todo está almacenado en este datafile. Si luego añadimos una nueva BD también con tablas InnoDB, el problema se acrecenta la que entonces tenemos todo mezclado, todo está en el mismo datafile.

Una corrupción en ibdata1 implica la restauración de todas las tablas de todas las BD que usen InnoDB. Bastante peligroso la verdad.

Para solucionar esto podemos añadir en el fichero de parámetros :

Source   
innodb_file_per_table

Dentro de la sección:

Source   
[mysqld]

Esto hace lo que indica un datafile por tabla, aunque existen dos problemas:

  1. No es retroactivo, es decir solo las nuevas tablas tendrán un datafile propio.
  2. El datafile ibdata1 no hay forma de reducirlo o compactarlo.

Se han de realizar algunas tareas para conseguir el objetivo de tener un datafile por tabla y un ibdata1 reducido:

  1. Copia fría de todos los ficheros MySQL. Es decir parar el servicio de MySQL y copiar el directorio /var/lib/mysql (en mi caso) donde están almacenadas todas las BD.
  2. Arrancar MySQL y realizar exportaciones individuales de cada una de las BD, esto lo podemos hacer con:
    Source   
    mysqldump -uroot -ppass --routines --triggers --lock-all-tables nombre_bd > nombre_bd.sql
  3. Borrar las BDs (excepto las propias de MySQL claro) con:
    Source   
    mysql> DROP DATABASE nombre_bd;
  4. Parar de nuevo MySQL y eliminar los ficheros del directorio  /var/lib/mysql (en mi caso):
    Source   
    ibdata1
    ib_logfile0 (log file1)
    ib_logfile1 (log file1)
  5. Arrancar MySQL y observar la creación de los nuevos ficheros borrados en el punto anterior. Esta vez ibdata1 ocupará unos 10Mb.
  6. Crear todas las BD con:
    Source   
    mysql> CREATE DATABASE nombre_bd;
  7. Importar todas las BDs una a una con:
    Source   
    mysql -uroot -ppass nombre_bd < nombre_bd

Si no nos hemos salta ningún paso no deberíamos tener ningún problema, ahora una corrupción de un datafile afectará solo a una tabla

One thought on “Pasar de un solo datafile InnoDB a uno por tabla (MYSQL)

  1. Pingback: MySQL, tuning de parámetros para el motor INNODB | Administrando sistemas

Deja un comentario