Reducir el tamaño de ibdata1 en MYSQL
Este fichero crece de tamaño a medida que se hacen transacciones, se realizan cambios en las tablas, etc, pero nunca disminuye de tamaño, por lo que si, por ejemplo, un día añadimos 4 gb en información en una tabla ibdata1 aumentará en consecuencia, y si al día siguiente borramos esos 4 gb, ibdata1 permanecerá con el mismo tamaño, aunque cierto es que de, por ejemplo, añadir tras esto 2 gb más de información a una tabla, ibdata1 no aumentará puesto que “internamente” tiene 4 gb libres.
Esto es un problema, puesto que no podemos purgar este fichero, y en ocasiones tendremos problemas de espacio en disco por situaciones internas de la BBDD que escapan a nuestro control.
¿Como lo solucionamos?
La solución consiste en configurar InnoDB para que el fichero ibdata1 guarde los datos de las tablas en ficheros separados, de esta forma generará ficheros .frm e .ibd por cada tabla, y en caso de querer optimizar una tabla y vaciar datos obsoletos, podremos realizarlo con OPTIMIZE TABLE. Lamentablemente, para configurar InnoDB de esta forma, es necesario primero purgar el fichero ibdata1, y para ello, hay que borrar todas las bases de datos y recrearlas, explico el proceso.
Procedemos a realizar un dump de todas las bases de datos con mysqldump, ya sea una a una:
mysqldump my_database > mydatabasedump.sql
O, en su defecto todas a la vez:
mysqldump –all-databases > alldb.sql
Dropamos/Borramos todas las bases de datos EXCEPTO mysql_schema, mysql y performance_schema (podéis usar el siguiente comando)
mysql -uroot -p -e «show databases» | grep -v Database | grep -v mysql| grep -v information_schema| grep -v performance_schema | gawk ‘{print «drop database » $1 «;select sleep(0.1);»}’ | mysql -uroot -p
Si nos diese error la anterior sentencia por no tener gawk, podemos instalar gawk con un simple apt-get o un yum dependiendo de nuestra distribución.
Paramos MySQL/MariaDB
Editamos my.cnf y añadimos las siguientes líneas
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
(El tamaño de innodb_buffer_pool_size debe asignarse en función de la memoria, si es un servidor sólo dedicado a MySQL, un 80% de la memoria va bien, si es compartido con Apache y correo, etc, con un 30% de memoria debería bastar, pero es importante que innodb_log_file_size sea un 25% de buffer_pool_size)
Eliminamos el fichero ibdata1 y los logfiles:
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*
Arrancamos MySQL de nuevo
Volcamos los dumps de las bbdd a MySQL.
mysql -u root -p < alldb.sql
Con esto, habremos purgado el ibdata1 y tendremos cada tabla en un fichero propio, pudiendo optimizar la misma si así lo queremos.
