Cómo reducir/purgar el archivo ibdata1 en MySQL

Resuelto lokheart asked hace 14 años • 10 respuestas

Estoy usando MySQL en localhost como una "herramienta de consulta" para realizar estadísticas en R, es decir, cada vez que ejecuto un script de R, creo una nueva base de datos (A), creo una nueva tabla (B), importo los datos a B , envío una consulta para obtener lo que necesito y luego descarto B y descarto A.

A mí me funciona bien, pero me doy cuenta de que el tamaño del archivo ibdata está aumentando rápidamente. No almacené nada en MySQL, pero el archivo ibdata1 ya superó los 100 MB.

Estoy usando una configuración MySQL más o menos predeterminada para la configuración, ¿hay alguna manera de poder reducir/purgar automáticamente el archivo ibdata1 después de un período de tiempo fijo?

lokheart avatar Aug 11 '10 14:08 lokheart
Aceptado

Que ibdata1no se reduzca es una característica particularmente molesta de MySQL. En realidad, el ibdata1archivo no se puede reducir a menos que elimine todas las bases de datos, elimine los archivos y vuelva a cargar un volcado.

Pero puedes configurar MySQL para que cada tabla, incluidos sus índices, se almacene como un archivo separado. De esa manera ibdata1no crecerá tanto. Según el comentario de Bill Karwin, esto está habilitado de forma predeterminada a partir de la versión 5.6.6 de MySQL.

Hace un tiempo hice esto. Sin embargo, para configurar su servidor para usar archivos separados para cada tabla, debe realizar cambios my.cnfpara habilitar esto:

[mysqld]
innodb_file_per_table=1

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

Como desea recuperar el espacio, ibdata1debe eliminar el archivo:

  1. Haga una revisión mysqldumpde todas las bases de datos, procedimientos, desencadenantes, etc., excepto las bases de datos mysqlyperformance_schema
  2. Elimine todas las bases de datos excepto las 2 bases de datos anteriores
  3. detener mysql
  4. Eliminar ibdata1y ib_logarchivos
  5. iniciar mysql
  6. Restaurar desde el volcado

Cuando inicie MySQL en el paso 5, se recrearán los archivos ibdata1y .ib_log

Ahora estás en condiciones de partir. Cuando crea una nueva base de datos para su análisis, las tablas se ubicarán en ibd*archivos separados, no en archivos ibdata1. Como normalmente descartas la base de datos poco después, los ibd*archivos se eliminarán.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

Probablemente hayas visto esto:
http://bugs.mysql.com/bug.php?id=1341

Al usar el comando ALTER TABLE <tablename> ENGINE=innodbo OPTIMIZE TABLE <tablename>se pueden extraer datos e indexar páginas de ibdata1 en archivos separados. Sin embargo, ibdata1 no se reducirá a menos que siga los pasos anteriores.

Respecto al information_schema, eso no es necesario ni posible soltarlo. De hecho, son solo un montón de vistas de solo lectura, no tablas. Y no hay archivos asociados con ellos, ni siquiera un directorio de base de datos. Está informations_schemautilizando el motor de base de datos de memoria y se descarta y se regenera al detener/reiniciar mysqld. Consulte https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .

John P avatar Aug 11 '2010 09:08 John P

Agregando a la respuesta de John P ,

Para un sistema Linux, los pasos 1 a 6 se pueden realizar con estos comandos:

  1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
  2. mysqladmin -u [username] -p[root_password] drop [database_name]
  3. sudo /etc/init.d/mysqld stop
  4. sudo rm /var/lib/mysql/ibdata1
    sudo rm /var/lib/mysql/ib_logfile*
  5. sudo /etc/init.d/mysqld start
  6. mysqladmin -u [username] -p[root_password] create [database_name]
  7. mysql -u [username] -p[root_password] [database_name] < dumpfilename.sql

Advertencia: estas instrucciones harán que pierda otras bases de datos si tiene otras bases de datos en esta instancia de MySQL. Asegúrese de modificar los pasos 1,2 y 6,7 para cubrir todas las bases de datos que desea conservar.

Vinay Vemula avatar Oct 31 '2014 07:10 Vinay Vemula

Cuando eliminas tablas innodb, MySQL no libera espacio dentro del archivo ibdata, por eso sigue creciendo. Estos archivos casi nunca se reducen.

Cómo reducir un archivo ibdata existente:

https://dev.mysql.com/doc/refman/5.6/en/innodb-system-tablespace.html#innodb-resize-system-tablespace

Puede crear un script para esto y programar el script para que se ejecute después de un período de tiempo fijo, pero para la configuración descrita anteriormente parece que múltiples espacios de tabla son una solución más fácil.

Si usa la opción de configuración innodb_file_per_table, crea múltiples espacios de tabla. Es decir, MySQL crea archivos separados para cada tabla en lugar de un archivo compartido. Estos archivos separados se almacenan en el directorio de la base de datos y se eliminan cuando elimina esta base de datos. Esto debería eliminar la necesidad de reducir/purgar archivos ibdata en su caso.

Más información sobre múltiples espacios de tabla:

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

titanoboa avatar Aug 11 '2010 08:08 titanoboa