Sitio para la difusión de conocimiento informático. 

Twitter RSS

MySQL – Recuperar espacio libre del ibdata1

Recuperar espacio libre del ibdata1

En caso de que el archivo ibdata esté consumiendo gran cantidad de espacio en disco podemos reducir el tamaño de las bases de datos eliminando información de las mismas, sin embargo ese espacio liberado no será reflejado en el espacio utilizado por el archivo ibdata. Es por esto que debemos realizar un respaldo de las DB, eliminar este archivo y el id_log, y volver a iniciar el servicio procediendo a restaurar los respaldos.

En este articulo también veremos como configurar Mysql para limitar el tamaño de los archivos ibdata.

Primero verificamos el tamaño de las bases de datos:
SELECT table_schema «Data Base Name», sum( data_length + index_length ) / 1024 / 1024 «Data Base Size in MB» FROM information_schema.TABLES GROUP BY table_schema ;

Para reclamar el espacio usado por le ibdata1, actualmente debemos eliminarlo:

1)  Hacemos un respaldo de todas las bases de datos:
/usr/bin/mysqldump –u root -p db_name > db_name.db

2)  Borramos todas las bases de datos excepto la base «mysql» (mysql,  information_schema, performance_schema). Para esto iniciamos sesión en mysql y borramos las bases de datos: drop database db_name

3)  Detenemos el servicio «mysql»: /etc/init.d/mysql stop

4)  Borramos los archivos ibdata e ib_log.

5) Modificando la configuración de MySQL podemos evitar que vuelva a crecer de forma ilimitada. Modificamos/Agregamos estas líneas en el archivo de configuración «my.cnf» para crear 4 archivos ibdata que no crecerán más de 1 Gb cada uno (este valor puede ser modificado según las necesidades de nuestra base de datos):
innodb_data_file_path=ibdata1:1024M;ibdata2:1024M;ibdata3:1024M;ibdata4:1024M
innodb_table_locks=0
innodb_file_per_table=1
innodb_buffer_pool_size=1GB # USE ALL MEMORY AVAILABLE
innodb_log_buffer_size=16M # Lowered from 32M according to [[MySQL]]
innodb_additional_mem_pool_size=20M

Convertimos nuestras tablas a InnoDB con el siguiente comando debiendo reemplazar MY_DATABASE por el nombre de la base de datos que contiene las tablas a convertir, así como la contraseña del usuario de la DB (en este caso root):
for i in $(mysql -u root -pPASSWORD -e «use MY_DATABASE; show tables;» | tr -d «|» | grep -v «Tables_in_»); do mysql -u root -pPASSWORD -e «use MY_DATABASE; ALTER TABLE $i ENGINE=InnoDB;»; done

6)  Iniciamos el servicio «mysql»: /etc/init.d/mysql start
De esta forma cuando iniciemos MySQL los archivos ibdata y ib_log serán recreados.

7)  Creamos la DB nuevamente ingresando a Mysql y ejecutando el siguiente comando:
create database db_name

8) Restauramos el respaldo de las bases de datos.
mysql -u root -p db_name < db_name.db

9) Verificamos el estado de las tablas de las DB y reclamamos el espacio libre:
/usr/bin/mysqlcheck -u root -p –optimize –all-databases

Información adicional respecto a la mejora de performance:

– Permitiendo consultas de cambios pendientes en el log:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

– Converitr la DB de MyISAM to Innodb:
ALTER TABLE table_name ENGINE=InnoDB;

 

 
Home Linux OS MySQL MySQL – Recuperar espacio libre del ibdata1