Reducir o truncar el ibdata1 de MySQL

Si estáis utilizando la configuración por defecto del motor de almacenamiento InnoDB para vuestras tablas MySQL, seguramente os habréis encontrado con un problema bastante molesto: el archivo 'ibdata1', que contiene todos los datos de la instancia MySQL (no es bien bien un log de transacciones) y que crece descontroladamente. Al principio pesa 10Mb, pero eso va creciendo y no se puede partir o cortar de ninguna manera. Ni siquiera con DELETE, TRUNCATE o DROPs a los datos almacenados en la BBDD harán que el archivo sea más pequeño. De hecho, cuando se libera espacio, el sistema lo marca como libre dentro del archivo y se puede reutilizar más adelante. Vamos, que podría llegar a ocupar todo el espacio libre en la partición. Y con lo crítico que es, este parámetro no viene preconfigurado en una instalación por defecto.

Bueno, el caso es que una vez configuras una BBDD para trabajar con InnoDB, pocas cosas puedes hacer con ella, una vez te has encontrado con el problema. Así que hay que empezar de nuevo, y para que no nos pase, vamos a intentar limitar estos parámetros y que no crezca de manera descontrolada.

Éste es el método que he utilizado en varias ocasiones. Al final tenéis el enlace al texto original, donde se comentan otras dos maneras de truncar el ibdata1. Yo utilizaré la más intuitiva.

Antes de nada, haced un backup completo de la BBDD. Es más, si puedes también parar el MySQL y copiar los binarios a otro directorio, mejor. Ya lo borrarás cuando puedas. Así tienes todos los backups habidos y por haber.

Yo mi backup completo lo hago con una query del estilo:

/usr/bin/mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers > /root/all-databases.sql

Tarda, tarda bastante, pero es completo.

Una vez tengo hecho mi backup, paro el servidor MySQL y entonces me dedico a mover o a borrar el directorio de datos MySQL. Yo, en mi caso, siempre me gusta guardar el directorio por si hay que hacer un rollback, y es recomendable que lo hagáis. Si no podéis por problemas de espacio o algo, al menos esperad a comprobar que el proceso funciona.

Nos habíamos quedado en que tenéis que mover lo que hay en el directorio de datos de MySQL si queréis mantener el path (por defecto en Debian es /var/lib/mysql); otra opción es crear una carpeta nueva, por ejemplo si queréis mover el directorio de datos de filesystem a uno más grande. En ese caso, recordad configurar los mismos permisos

mkdir /path/to/mysql
chown mysql:mysql /path/to/mysql
chmod 770 /path/to/mysql

Ya casi estamos. Ahora toca reconfigurar el MySQL; por defecto el archivo en Debian es /etc/mysql/my.cnf pero en algunos sistemas es /etc/my.cnf; sólo tendréis que localizarlo y realizar las modificaciones que consideréis pertinentes. Yo, en mi última configuración, añadí estos parámetros

innodb_log_file_size    = 800M
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_data_file_path=ibdata1:1024M

pero en el último algo andaría mal, porque el sistema ha pasado de mí. Lástima que lo vi cuando ya era demasiado tarde (era demasiado tarde porque había puesto la BBDD en producción y si me 'cargo' el ibdata1, me cargo la BBDD... y queda feo). Si habéis cambiado el path del directorio de datos, recordad que tenéis que modificar la variable 'datadir' y actualizarla.

Ahora que he hecho las modificaciones, inicializo la BBDD. Esto es que como no tengo nada, sólo un directorio vacío, vamos a crear la estructura de datos para que el MySQL pueda arrancar sin nada. Ya me encargaré después de cargarle los datos. Para ello, desde la línea de comandos ejecuto

sudo -u mysqld mysql_install_db
(si estoy como root sólo  mysql_install_db)

Y ya estará inicializada. Comprobad que no devuelva otro error, pero si todo va bien, ya tenéis la BBDD.

Ahora levanto el MySQL. En Debian da 'problemas' con el usuario debian-sys-maint, que es el que Debian utiliza para cualquier tarea de mantenimiento, como comprobar el estado de las tablas al arrancar el proceso. Nada, que da error porque el usuario no existe, así que no le hagáis mucho caso al amigo. Cuando importéis la BBDD y el usuario se cree, se resolverá el problema. El caso es que el servicio se levanta y podréis entrar al MySQL. Dentro de la consola ejecutar

SET FOREIGN_KEY_CHECKS=0;
SOURCE /path/to/backup-databases.sql;
SET FOREIGN_KEY_CHECKS=1;

El segundo paso importará la base de datos, y su duración dependerá de lo grande que sea ésta. Puede durar horas. No desesperéis.

Cuando por fin haya acabado, reiniciad el MySQL para que coja todos los cambios que habéis hecho al importar la base de datos. Comprobad que levanta bien y que todo funciona correctamente.

En el caso que tuviérais que volver atrás porque no ha salido bien y no tenéis tiempo de repetir el proceso, todo es tan sencillo como

1 - parar el MySQL
2 - editar /etc/mysql/my.cnf y deshacer los cambios
En mi caso sería eliminar las líneas añadidas y volver a poner /var/lib/mysql en el 'datadir'
3 - arrancar el MySQL de nuevo. Comprueba que todo funciona correctamente

El proceso sólo requiere mucho tiempo, siempre en función del tamaño de la BBDD, no es tan difícil como puede parecer antes de hacerlo

Y ya está!!