Cambiar configuración de MySQL cuando el fichero ibdata es demasiado grande (innodb_file_per_table)
Cada tabla de MySQL utiliza un determinado motor de almacenamiento (storage engine). Los más conocidos son MyISAM e InnoDB. Dos recursos basados en disco muy importantes que gestiona el motor de almacenamiento InnoDB son sus ficheros de datos de espacios de tablas y sus ficheros de registro (log).
Los datos de tablas que utilizan el engine MyISAM se almacenan como ficheros independientes, normalmente bajo el directorio “/var/lib/mysql/nombre”. Sin embargo los datos de las tablas que utilizan el engine InnoDB se almacenan todos en un único fichero “/var/lib/mysql/ibdata1”.
Si la base de datos contiene un gran volumen de datos, este fichero puede llegar a hacerse muy grande. Pero además, cuando se elimina una tabla o toda una base de datos, el espacio que ocupaban en el fichero ibdata1 no se recupera. En el caso de utilizar phpunit o cualquier otro software similar para realizar builds que generen un gran número de tablas, se acelera mucho más el crecimiento del fichero ibdata.
Problema discutido por los desarrolladores: http://bugs.mysql.com/bug.php?id=1341
Lecturas recomendadas:
Averiguar qué tablas utilizan el motor InnoDB.
SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') and engine='InnoDB';
La solución, si se quiere seguir utilizando el motor InnoDB, pasa por cambiar la configuración del servidor MySQL, pero no sin antes, borrar todas las bases de datos. Como este cambio de configuración alberga ciertos riesgos, se recomienda tener siempre backups del sistema o realizar snapshots del sistema de ficheros o máquina virtual si es el caso. Veamos los pasos a realizar.
Parar todos los servicios que estén utilizando el servidor de MySQL.
Realizar un backup de las distintas bases de datos para después reponerlos con la nueva configuración.
mysqldump --max_allowed_packet=100M --single-transaction --routines -u XXX -pXXX bamboo > /root/bamboo.sql mysqldump --max_allowed_packet=100M --single-transaction --routines -u XXX -pXXX zend_dev > /root/zend_dev.sql
Borrar las bases de datos (No borrar mysql e information_schema).
mysql> DROP DATABASE bamboo mysql> DROP DATABASE zend_dev
Terminar posibles transacciones en curso y parar el servicio MySQL.
mysql> SET GLOBAL innodb_fast_shutdown = 0 service mysqld stop
Borrar los ficheros de espacio de tabla y logs.
cd /var/lib/mysql rm ibdata1 ib_logfile*
Editar el fichero de configuración /etc/my.cnf y configurar la opcióninnodb_file_per_table.
[mysqld] innodb_file_per_table
Crear las bases de datos.
mysql> SET storage_engine=INNODB; mysql> create database zend_dev; mysql> CREATE DATABASE bamboo CHARACTER SET utf8 COLLATE utf8_bin;
Reponer los volcados.
mysql -u XXX -p bamboo < /root/bamboo.sql mysql -u XXX -p zend_dev < /root/zend_dev.sql
Arrancar de nuevo los servicios que utilizaban el servidor MySQL y probar.