Cómo reducir / purgar el archivo ibdata1 en MySQL

Al eliminar tablas innodb, MySQL no libera el espacio dentro del archivo ibdata, por eso sigue creciendo. Estos archivos casi nunca se encogen.

Cómo reducir un archivo ibdata existente:

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

Puede programar esto y programar que el script 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 si utiliza la opción de configuración innodb_file_per_table, creará varios 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 se elimina esta base de datos. Esto debería eliminar la necesidad de reducir/purgar los archivos ibdata en su caso.

Más información sobre múltiples tablespaces:

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 realizar 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 ser nombrado 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 harán que pierda 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 cubrir todas las bases de datos que desee 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) hay un archivo llamado 'ibdata1'. Contiene casi todos los datos InnoDB (no es un registro de transacciones) de la instancia 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 elimina, trunca, cae, etc. no recuperará 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 objetivo es monitorear el espacio libre de MySQL y no puede detener MySQL para reducir su archivo ibdata, entonces 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

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 mysql-las recetas del servidor anteriores aplastarán la base de datos "mysql". En la versión antigua funciona. En new algunas tablas cambian al tipo de tabla INNODB, y al hacerlo las dañará. La forma más fácil es volcar todas sus bases de datos, desinstalar mysql-server, añadir en seguía siendo mi.cnf:

[mysqld]
innodb_file_per_table=1


erase all in /var/lib/mysql
install mysql-server
restore users and databases

Rápidamente escribió 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

Guardar como purge_binlogs.sh y ejecutar como root.

Excluye mysql, information_schema, performance_schema (and binlog directory).

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

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

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

Como ya se ha señalado, no se puede reducir ibdata1 (para hacerlo, es necesario volcar y reconstruir), pero a menudo tampoco hay una necesidad real de hacerlo.

Usando autoextend (probablemente la configuración de tamaño más común) ibdata1 preasigna1 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 nuevos datos, reutilizará el espacio vacío en el archivo antes creciendo el archivo más lejos.

Así que solo seguirá creciendo si realmente necesita esos datos. A menos que realmente necesite el espacio para otra aplicación, probablemente no haya razón para reducirlo.