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:
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 :
innodb_file_per_tableDentro de la sección:
[mysqld]Esto hace lo que indica un datafile por tabla, aunque existen dos problemas:
- No es retroactivo, es decir solo las nuevas tablas tendrán un datafile propio.
- 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:
- 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.
- Arrancar MySQL y realizar exportaciones individuales de cada una de las BD, esto lo podemos hacer con:
mysqldump -uroot -ppass --routines --triggers --lock-all-tables nombre_bd > nombre_bd.sql
- Borrar las BDs (excepto las propias de MySQL claro) con:
mysql> DROP DATABASE nombre_bd;
- Parar de nuevo MySQL y eliminar los ficheros del directorio /var/lib/mysql (en mi caso):
ibdata1 ib_logfile0 (log file1) ib_logfile1 (log file1)
- Arrancar MySQL y observar la creación de los nuevos ficheros borrados en el punto anterior. Esta vez ibdata1 ocupará unos 10Mb.
- Crear todas las BD con:
mysql> CREATE DATABASE nombre_bd;
- Importar todas las BDs una a una con:
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
Pingback: MySQL, tuning de parámetros para el motor INNODB | Administrando sistemas
Bueno, para resolver este asunto, de forma simple, podrías hacer lo siguiente:
mysql> create tablespace mytable add datafile ‘carpetadb/mytabla.ibd’;
Mysql 8, pone cada datafile en una carpeta asociando el nombre de la base de datos y la tabla, por lo que en information_schema encontraras que el tablespace, cuando esta activado innodb_file_per_table es:
nombrebd/nombretabla.
Para luego solamente hacer:
mysql> Alter table mytable tablespace mytable;