Cómo liberar espacio ocupado por el fichero ibdata en un servidor de base de datos MySQL. En el tutorial usamos Linux Ubuntu Server y MySQL Server como base de datos, aunque el proceso es similar para equipos Windows y bases de datos MariaDB. Indicamos varios métodos de almacenamiento de datos en ficheros de MySQL y sus ventajas e inconvenientes.

• Escenarios posibles de almacenamiento de datos en ibdata1 o en .frm y .ibd en MySQL y MariaDB.
  • Escenario 1: almacenamiento de todos los datos en fichero ibdata1 de MySQL/MariaDB.
  • Escenario 2: almacenamiento de datos en ficheros separados por cada tabla en MySQL/MariaDB.
• Método 1 para reducir el tamaño del fichero ibdata1 en MySQL cuando contiene todos los datos (escenario 1).
  • Copia de seguridad de los datos con varios métodos.
  • Eliminación de fichero ibdata1 para que se vuelva a regenerar compactado.
• Método 2: optimize table para compactar tablas y liberar espacio.
• Configuración idónea para almacenamiento de datos en MySQL y MariaDB.

Escenarios posibles de almacenamiento de datos en ibdata1 o en .frm y .ibd en MySQL y MariaDB

Existen dos escenarios posibles a la hora de almacenar datos en ficheros por parte de servidores MySQL Server: que guarde todos los datos en un único fichero ibdata1 o bien que los guarde en ficheros separados por cada tabla. La elección de uno de los escenarios se define con el valor del parámetro innodb_file_per_table, en el fichero de configuración de MySQL, que en equipos Linux suele estar en:

/etc/mysql/my.cnf

O bien en:

/etc/my.cnf

Explicamos estos dos escenarios a continuación.

Escenario 1: almacenamiento de todos los datos en fichero ibdata1 de MySQL/MariaDB

MySQL puede albergar en el fichero /var/lib/mysql/ibdata1 información diversa en función de la configuración del servidor. Si tenemos desactivado el parámetro innodb_file_per_table (si no tenemos este parámetro o bien si lo tenemos a valor 0), MySQL Server almacenará todos los datos de todas las tablas e índices en el fichero ibdata1, por lo que puede tener un gran tamaño. Si el valor de innodb_file_per_table = 0, en el fichero ibdata1 almacenará:

• Datos de todas las tablas de todos los catálogos.
• Índices de todas las tablas.
• Datos MVCC, de control de concurrencia.
• Segmentos de reversión.
• Deshacer espacio de tablas.
• Metadatos de tablas.

En la imagen anterior mostramos una estructura de carpetas y ficheros típica cuando todos los datos residen en el fichero ibdata1. En el ejemplo, el fichero ibdata1 contiene todos los datos de todos los catálogos, con 578MB de tamaño. Cada catálogo tendrá su carpeta, pero dentro de ella sólo habrá ficheros .frm (de tamaño reducido), de encabezados de las tablas (no de datos):

En este caso, como hemos indicado, el fichero ibdata1 puede alcanzar un gran tamaño. Y, además, según el tipo de tablas (si son InnoDB) no podrán ser optimizadas con optimize table, por lo que no se podrá compactar para recuperar espacio.

En este escenario, si el fichero ibdata1 crece considerablemente, existen dos métodos (al menos), para compactarlo, pero son más drásticos que si tuviésemos el segundo escenario (que explicamos a continuación).

Este escenario presenta algunos inconvenientes:

• Si una tabla crece desmesuradamente, será mucho más difícil analizar el problema y compactar si fuera necesario. Dado que el fichero para todas las tablas es el mismo.
• Si hay que hacer algún tipo de actuación en el fichero de datos, al ser pesado, el proceso será más lento.
• Si se corrompe el fichero, afectará a todos los datos de todas las tablas.
• No se puede hacer copia de una o varias tablas por separado.

Escenario 2: almacenamiento de datos en ficheros separados por cada tabla en MySQL/MariaDB

Si el parámetro innodb_file_per_table lo tenemos a valor 1 (ó a valor ON), MySQL Server almacenará los datos e índices de las tablas en ficheros separados (dos ficheros al menos por cada tabla):

• *.frm: en estos ficheros guardará los encabezados del motor de almacenamiento.
• *.ibd: en estos ficheros almacenará los datos e índices de las tablas.
• ibdata1: también habrá un fichero ibdata1, pero en este caso sólo contendrá metadatos de las tablas, no los datos reales. Este fichero crecerá pero puede ser purgado con facilidad (por ejemplo ejecutando optimize table o incluso deteniendo el servicio y eliminándolo para que se vuelva a regenerar, previa copia de seguridad y previa certeza de que no contiene los datos de las tablas).

Como podemos observar en la imagen anterior, es una estructura de carpetas y ficheros habitual con el parámetro innodb_file_per_table a valor ON. Con una carpeta por cada catálogo, dentro de la carpeta de un catálogo tendremos los ficheros .frm y .ibd que contienen los datos de cada tabla:

En este escenario, el procedimiento de compactación para liberar espacio es más sencillo. Si tenemos tablas MyISAM será suficiente con ejecutar un optimize table. Si tenemos tablas InnoDB, también podremos compactar incluso eliminando el fichero ibdata1 (dado que no contiene datos de las tabla) y los ficheros ib_logfile.

Este escenario tiene muchas ventajas, alguna de ellas:

• Si se corrompe un fichero, afectará únicamente a los datos de su tabla asignada, pero no al resto.
• Si un fichero crece de tamaño desmesuradamente, se puede analizar qué ocurre con su tabla asociada, quedando identificado el problema de forma rápida.
• Se puede hacer una copia de una o varias tablas por separado, copiando sus ficheros de datos asociados.

En cuanto a rendimiento, no se ha observando ventajas de un escenario sobre otro.

Método 1 para reducir el tamaño del fichero ibdata1 en MySQL cuando contiene todos los datos (escenario 1)

Si tenemos el escenario 1 (todos los datos de todas las tablas se almacenan en un único fichero ibdata1), para compactarlo y recuperar espacio, podremos hacerlo de forma «drástica», de la siguiente manera.

Copia de seguridad de los datos con varios métodos

En primer lugar haremos copia de seguridad (al menos de dos formas diferentes). Por una lado, es recomendable hacer copia de seguridad de la máquina virtual con MySQL/MariaDB, por ejemplo con Veeam Backup o bien con OVF Tool. O incluso creando un snapshot antes de realizar cualquier operación:

Por otro, haremos copia de los ficheros de datos, copiándolos a otra ubicación con el servicio de MySQL detenido, como indicamos a continuación. Para hacer copia de los ficheros de datos (fichero ibdata1 y ficheros de log ib_logfile) de MySQL, que suelen estar en la carpeta:

/var/lib/mysql

Detendremos el servicio de MySQL, teniendo en cuenta que la base de datos se parará y los usuarios no tendrán acceso a ella, con el siguiente comando:

Copiaremos los ficheros ibdata1 y ib_logfile* a otra ubicación:

Volvemos a iniciar el servicio de MySQL con:

Eliminación de fichero ibdata1 para que se vuelva a regenerar compactado

Una vez hecha la copia de seguridad con los métodos anteriores, deberemos hacer una tercera copia, pero en este caso mediante mysqldump. Necesitamos hacer el backup de esta forma para pasar los datos a fichero .sql, para luego recuperarlos y que queden compactados, como indicamos a continuación.

Haremos copia de seguridad de todas las bases de datos de nuestro servidor MySQL, con el comando:

Teniendo en cuenta que en el ejemplo anterior haremos copia de todas las bases de datos en la ubicación /backup/mysql, en el fichero «copia_base_datos.sql».

Si el comando anterior falla por permiso denegado, tendremos que asignar un usuario y contraseña de MySQL al comando mysqldump. Por ejemplo, ejecutando:

Donde «root» será un usuario de MySQL con permisos de administrador para todos los catálogos y «Contraseña_Root» será la la contraseña de dicho usuario.

Nota: la ejecución del comando anterior pasando la contraseña en el propio comando es insegura, es recomendable añadir usuario y contraseña en una sección del fichero de configuración my.cnf, añadiendo este contenido:

Si hemos tenido que añadir usuario y contraseña al fichero my.cnf, ejecutaremos el comando mysqldump sin pasarle usuario ni contraseña, los obtendrá del fichero anterior y esto es más seguro.

Una vez hecha la copia con mysqldump, no aseguraremos de que todas las copias son correctas y eliminaremos todas las bases de datos salvo las del sistema: mysql, performance_schema, information_schema y sys. Si nos conectamos desde consola a MySQL, con el comando:

Con un usuario administrador de MySQL, ejecutando el siguiente comando podremos obtener listado de todas las bases de datos (catálogos):

NO eliminaremos las del sistema (indicadas con una flecha en la imagen):

Para eliminar una base de datos, ejecutaremos el comando (dentro de la consola de MySQL):

En el ejemplo de nuestro servidor, ejecutaremos:

Si queremos aprovechar pasar pasar el método de almacenamiento al escenario 2 (a guardar la información de cada tabla de cada catálogo separada en ficheros), nos saldremos de la consola de MySQL con el comando «exit» y detendremos el servicio de MySQL, con:

O bien:

Editaremos el fichero de configuración de MySQL:

O bien

Añadiendo los siguientes parámetros en la sección «mysqld» (tal y como hemos indicando en este apartado):

Los valores anteriores son de ejemplo para un servidor MySQL dedicado con 3GB de memoria RAM.

Eliminaremos ahora los ficheros ibdata1 y ficheros de log ib_logfile*, dado que tenemos copia de seguridad con varios métodos y dado que queremos reducir su tamaño y compactarlos:

Volvemos a iniciar el servicio de MySQL/MariaDB, para que se regeneren los ficheros ibdata1 y ib_logfile, aunque ahora se crearán con un tamaño reducido (unos 18MB el fichero ibdata1 y 1GB cada fichero ib_logfile:

Importaremos los datos exportados con mysqldump, con el comando mysql:

Comprobaremos que ahora el fichero ibdata1 no crecerá más de lo indicado y que solo contendrá metadatos de tablas (nunca los datos e índices). Por su parte, cada catálogo tendrá una carpeta y cada tabla de cada catálogo tendrá dos ficheros (.frm y .ibd) que guardan su información completa:

Con este procedimiento no sólo habremos reducido el tamaño del fichero ibdata1, si no que, además, hemos establecido a MySQL que a partir de ahora guarde los datos de cada tabla en un fichero separado y no en el ibdata1.

Método 2: optimize table para compactar tablas y liberar espacio

Otro método para reducir el espacio usado por el fichero ibdata1 es usar el comando MySQL optimize table, aunque este comando no siempre podrá ejecutarse. Por ejemplo, si tenemos tablas de tipo InnoDB, al hacerles un optimize table, nos devolverá el error:

Table does not support optimize, doing recreate + analyze instead

Una solución para reducir y compactar el fichero ibdata1, aunque tenga tablas InnoDB, es convertirlas temporalmente a MyISAM. Por ejemplo, la tabla «accesos_url» del catálogo «bd_practica_3» es de tipo InnoDB, para convertirla a MySQL, accederemos a la consola de MySQL con:

Donde «root» será un usuario con permisos suficientes de MySQL. Nos solicitará la contraseña del usuario establecido, la introduciremos.

Para convertir la tabla «accesos_url» de la base de datos «bd_practica_3» a MyISAM, ejecutaremos el siguiente comando:

Ahora sí nos permitirá hacer un optimize table, con el comando:

Si queremos volver a dejar la tabla a tipo InnoDB, ejecutaremos:

Nota: si la tabla contiene índices de claves foráneas no podrá convertirse a MyISAM.

De esta forma, compactaremos cada tabla reduciendo el tamaño usado en el fichero ibdata1.

Configuración idónea para almacenamiento de datos en MySQL y MariaDB

Lo ideal es optar por el escenario 2 explicado anteriormente, donde los datos se guardarán separados en ficheros por cada tabla, en lugar de todos en el mismo fichero ibdata1. No es la configuración ideal en todos los casos, pero si nuestras bases de datos MySQL/MariaDB van a almacenar mucha información, o incluso si una de nuestras tablas va a tener un crecimiento elevado (por ejemplo una tabla que almacene documentos), lo recomendable es separar los datos en ficheros por tabla, en lugar de todos en el mismo fichero.

Si optamos por el segundo escenario, el de separar los datos, indicamos a continuación la configuración idónea para optimizar el rendimiento de MySQL o MariaDB.

Editaremos el fichero fichero de configuración de MySQL:

Y en la sección mysqld estableceremos los siguientes parámetros y sus valores:

Explicamos los parámetros definidos anteriormente y sus valores:

• innodb_file_per_table: explicado al principio de este tutorial. Con el valor ON ó 1, indicamos que almacene los datos separados en ficheros por cada tabla.
• innodb_flush_method: con valor O_DIRECT, indicamos que el método para vaciar los datos en los archivos de datos sea directo para el tipo de tablas InnoDB.
• innodb_log_file_size y innodb_buffer_pool_size: se recomienda que el valor de innodb_buffer_pool_size sea un porcentaje de la memoria RAM del equipo. Si el equipo es dedicado a MySQL, podría ser un 70% de la memoria RAM. Por ejemplo, si el equipo tiene 8GB de RAM, el valor de innodb_buffer_pool_size podría ser 5GB aproximadamente. El valor de innodb_log_file_size debe ser un 25% del valor de innodb_buffer_pool_size. En el ejemplo, el valor de innodb_log_file_size sería de 1GB aproximadamente.

Esta configuración es la idónea aunque no tiene por qué ser la mejor en todos los escenarios. El DBA de MySQL debe valorar el tipo de datos que contendrá la base de datos y el uso, para establecer y ajustar estos valores.