Cómo reducir el tamaño del fichero ibdata1 en MySQL

*el mio ocupa:

-rw-rw---- 1 mysql mysql 12333350912 nov 18 09:36 /var/lib/mysql/ibdata1

Desde la primera versión de MySQL hasta la más reciente, los administradores de base de datos que trabajan con este SGBD se han tropezado antes o después con un problema que no acaba de solucionarse: el crecimiento indefinido del fichero ibdata1.

En este artículo se presentan algunas posibles acciones para eliminar o reducir este problema.

Introducción

Cada tabla de MySQL utiliza un determinado “motor de almacenamiento” (storage engine) . Los más conocidos son MyISAM e InnoDB.

Los datos de tablas que utilizan el engine MyISAM se almacenan como ficheros independientes, normalmente bajo el directorio “/var/lib/mysql/NOMBRE-DE-LA-BASE-DE-DATOS”. Sin embargo los datos de las tablas que utilizanel 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.

Para solucionar este problema, se puede optar por dos procedimientos:

• Cambiar a MyISAM el engine de las tablas que utilizan InnoDB, y prescindir completamente de este último

o bien:

• Realizar un volcado completo de todas las bases de datos, reiniciar el servidor y recuperar el volcado. Opcionalmente, podemos reconfigurar el servidor para que las tablas innodb se almacenen en ficheros independientes, evitando de este modo que vuelva a aparecer el problema.

En cualquiera de los dos casos, conviene hacerse primero una idea de la situación, ejecutando una sentencia que nos indique el número de tablas  que utilizan cada uno de estos engines:

SELECT COUNT(1) EngineCount,engine FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') GROUP BY engine;

Si el resultado de la consulta anterior nos indica que hay un cierto número de tablas que utilizan InnoDB, podemos ejecutar otra consulta para saber en detalle los nombres de los esquemas y tablas que lo utilizan:

SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') and engine='InnoDB';

Método 1: Convertir a MySAM las tablas que utilizan InnoDB

A menos que por razones de rendimiento u otros motivos sea imprescindible mantener el uso de InnoDB, podemos prescindir completamente del mismo. Para ello, debemos convertir cada tabla que utiliza InnoDB para que utilice MyISAM, con comandos de la forma:

alter table NOMBRE-DE-TABLA engine MyISAM;

una vez hecho esto, editamos el fichero de configuración de mysql “my.cnf “(que en un sistema linux se encuentra normalmente en la ruta /etc/mysql/my.cnf), para incluir en la sección “[mysqld]” las directrices:

[mysqld] innodb=OFF default_storage_engine=MyISAM

Dado que el motor de almacenamiento por defecto desde MySQL 5.5 ha cambiado a InnoDB, la segunda directriz es necesaria para establecer MyISAM como motor de almacenamiento por defecto.

Nota: Para versiones de MySQL 5.4 o anteriores, para inhabilitar InnoDB sólo era necesario incluir una directriz:

[mysqld] skip-innodb

A continuación, reiniciamos la base de datos:

$ sudo /etc/init.d/mysql restart

Podemos comprobar que InnoDB ha sido inhabilitado con el comando:

mysqladmin -u root -p var | grep have_innodb

y verificando que la salida del comando es:

| have_innodb                          | DISABLED                     |

y ya podemos eliminar el fichero “ibdata1” y los ficheros de log “ib_logfile0”, “ib_logfile1”

Método 2: Volcar las bases de datos, reiniciar el servidor y recuperar los volcados

1. Realizar un volcado (por ejemplo, con mysqldump) de todas las bases de datos en un ficherodatos.sql
2. Eliminar todas las bases de datos excepto mysql e information_schema
   Adicionalmente, se puede hacer una copia de los ficheros de mysql ejecutando como root los comandos:

   mkdir /var/lib/mysql_grants cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/. chown -R mysql:mysql /var/lib/mysql_grants

   Opcionalmente, se puede utilizar el procedimiento explicado en “Cómo copiar y restaurar los permisos de usuario de MySQL” para realizar una copia de seguridad de los privilegios de usuario.
3. Conectarse a mysql y ejecutar el comando SET GLOBAL innodb_fast_shutdown = 0; Con esto, se completa la ejecución de cualquier cambio transaccional que estuviera pendiente en los ficheros de los de transacciones ib_logfile0 e ib_logfile1
4. Parar MySQL (/etc/init.d/mysql stop)
5. Añadir las siguientes líneas al fichero /etc/my.cnf (o my.ini en Windows)

   [mysqld]
   innodb_file_per_table
   innodb_flush_method=O_DIRECT
   innodb_log_file_size=1G
   innodb_buffer_pool_size=4G

   (Nota: Los valores de los parámetros innodb_log_file_size e innodb_bufer_pool_size que se muestran son sólo ejemplos. En cualquier caso, hay que asegurarse de que el valor que se especifique para innodb_log_file_size sea un 25% del valor de innodb_buffer_pool_size.)
   Con el parámetro innodb_file_per_table, los datos de las tablas innodb se crearan en ficheros independientes, evitando que se reproduzca el problema del crecimiento del fichero ibdata1.
6. Borrar los ficheros ibdata* e ib_logfile*.
7. Arrancar MySQL (/etc/init.d/mysql start). Esto creará de nuevo el fichero ibdata1 [10MB por defecto] y ib_logfile0, ib_logfile1 con 1 GB cada uno).
8. Importar el volcado datos.sql