Cómo encoger / purgar el archivo ibdata1 en MySQL

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

Está funcionando bien para mí, 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 la configuración de MySQL más o menos predeterminada para la configuración, ¿hay alguna manera de que pueda reducir / depurar automáticamente el archivo ibdata1 después de un período de tiempo fijo?

Que ibdata1 no se está reduciendo es una característica particularmente molesta de MySQL. El archivo ibdata1 realidad no puede reducirse a menos que elimine todas las bases de datos, elimine los archivos y vuelva a cargar un volcado.

Pero puede configurar MySQL para que cada tabla, incluidos sus índices, se almacene como un archivo separado. De esta forma, ibdata1 no crecerá tan grande. Según el comentario de Bill Karwin, esto está habilitado por defecto a partir de la versión 5.6.6 de MySQL.

Fue hace un tiempo que hice esto. Sin embargo, para configurar su servidor para usar archivos separados para cada tabla, necesita cambiar my.cnf para habilitar esto:

 [mysqld] innodb_file_per_table=1 

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Como desea reclamar el espacio de ibdata1 , en realidad tiene que eliminar el archivo:

  1. Realice un mysqldump de todas las bases de datos, procedimientos, disparadores, etc., excepto las bases de datos mysql y performance_schema
  2. Eliminar todas las bases de datos, excepto las 2 bases de datos anteriores
  3. Detener mysql
  4. Eliminar archivos ibdata1 e ib_log
  5. Comience mysql
  6. Restaurar desde el volcado

Cuando inicie MySQL en el paso 5, se volverán a crear los archivos ibdata1 e ib_log .

Ahora estás listo para ir. Cuando crea una nueva base de datos para el análisis, las tablas se ibd* archivos separados ibd* , no en ibdata1 . Como suele soltar la base de datos poco después, los archivos ibd* se eliminarán.

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

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

Al usar el comando ALTER TABLE ENGINE=innodb u OPTIMIZE TABLE uno puede extraer datos e indexar páginas de ibdata1 en archivos separados. Sin embargo, ibdata1 no se reducirá a menos que realice los pasos anteriores.

Con respecto al information_schema , eso no es necesario ni es posible descartarlo. De hecho, es 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. El informations_schema está utilizando la memoria db-engine y se descarta y regenera al detener / reiniciar mysqld. Ver https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .

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

Cómo reducir un archivo ibdata existente:

http://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html

Puede hacer una secuencia de comandos y progtwigr 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 innodb_file_per_table 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:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Añadiendo a la respuesta de John P ,

Para un sistema Linux, los pasos 1-6 se pueden lograr con estos comandos:

  1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
  2. DROP DATABASE [database_name];
  3. sudo /etc/init.d/mysqld stop
  4. sudo rm /var/lib/mysql/ibdata1
    sudo rm /var/lib/mysql/ib_logfile (y eliminar cualquier otro ib_logfile que pueda llamarse ib_logfile0 , ib_logfile1 etc …)
  5. sudo /etc/init.d/mysqld start
  6. create database [database_name];
  7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

Advertencia: estas instrucciones le harán perder otras bases de datos si tiene otras bases de datos en esta instancia de MySQL. Asegúrese de que los pasos 1, 2 y 6, 7 estén modificados para abarcar todas las bases de datos que desea conservar.

Si utiliza el motor de almacenamiento InnoDB para (algunas de) sus tablas MySQL, probablemente ya haya encontrado un problema con su configuración predeterminada. Como habrás notado en el directorio de datos de tu MySQL (en Debian / Ubuntu – / var / lib / mysql) se encuentra un archivo llamado ‘ibdata1’. Tiene casi todos los datos de InnoDB (no es un registro de transacciones) de la instancia de MySQL y podría ser bastante grande. Por defecto, este archivo tiene un tamaño inicial de 10Mb y se extiende automáticamente. Desafortunadamente, por diseño, los archivos de datos InnoDB no se pueden reducir. Es por eso que DELETE, TRUNCATE, DROPs, etc. no reclamarán el espacio utilizado por el archivo.

Creo que puedes encontrar una buena explicación y solución allí:

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

Si su objective es monitorear el espacio libre de MySQL y no puede detener a MySQL para reducir su archivo ibdata, hágalo a través de los comandos de estado de la tabla. Ejemplo:

MySQL> 5.1.24:

 mysqlshow --status myInnodbDatabase myTable | awk '{print $20}' 

MySQL <5.1.24:

 mysqlshow --status myInnodbDatabase myTable | awk '{print $35}' 

Luego compare este valor con su archivo ibdata:

 du -b ibdata1 

Fuente: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

En una nueva versión de las recetas de mysql-server anteriores, se destruirá la base de datos “mysql”. En la versión anterior, funciona. En las nuevas tablas algunas tablas cambian a la tabla tipo INNODB, y al hacerlo las dañarás. La forma más fácil es volcar todas las bases de datos, desinstalar mysql-server, agregar sigue siendo my.cnf:

 [mysqld] innodb_file_per_table=1 erase all in /var/lib/mysql install mysql-server restre users and databases 

Guionó rápidamente el procedimiento de la respuesta aceptada en bash:

 #!/usr/bin/env bash DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')" mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do mysql -e "drop database \`$DB\`" done && \ /etc/init.d/mysql stop && \ find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \ /etc/init.d/mysql start && \ mysql < alldatabases.sql && \ rm -f alldatabases.sql 

purge_binlogs.sh como purge_binlogs.sh y ejecútelo como root .

Excluye mysql , information_schema , performance_schema (y el directorio binlog ).

Asume que tiene /root/.my.cnf administrador en /root/.my.cnf y que su base de datos vive en el directorio predeterminado /var/lib/mysql .

También puede purgar registros binarios después de ejecutar este script para recuperar más espacio en el disco con:

 PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP; 

Como ya se señaló, no puede reducir ibdata1 (para hacerlo, debe volcar y reconstruir), pero a menudo tampoco es necesario.

Usando autoextend (probablemente la configuración de tamaño más común) ibdata1 preasigna almacenamiento, creciendo cada vez que está casi lleno. Eso hace que las escrituras sean más rápidas ya que el espacio ya está asignado.

Cuando elimina datos, no se reduce, pero el espacio dentro del archivo se marca como no utilizado. Ahora cuando inserte datos nuevos, reutilizará el espacio vacío en el archivo antes de hacer crecer el archivo más.

Por lo tanto, solo continuará creciendo si realmente necesita esos datos. A menos que realmente necesite el espacio para otra aplicación, probablemente no haya razón para reducirla.