remove - Cómo reducir/purgar el archivo ibdata1 en MySQL

remove ibdata1

Question

Estoy utilizando 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 a B , envíe una consulta para obtener lo que necesito, y luego suelto B y suelto A.

Funciona 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 excedía los 100 MB.

Estoy usando más o menos la configuración de MySQL 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?

Answer 1

Si usa el motor de almacenamiento InnoDB para (algunas de) sus tablas MySQL, probablemente ya haya encontrado un problema con su configuración predeterminada. Como habrá notado en el directorio de datos de su MySQL (en Debian / Ubuntu - / var / lib / mysql) se encuentra un archivo llamado ''ibdata1''. Contiene 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 DELETEs, TRUNCATEs, DROPs, etc. no recuperará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/

Answer 2

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 elimine 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 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 se modifiquen para cubrir todas las bases de datos que desea mantener.

Answer 3

Como ya se mencionó, no puede reducir ibdata1 (para hacerlo, necesita volcar y reconstruir), pero a menudo tampoco hay necesidad real de hacerlo.

El uso de extensión automática (probablemente la configuración de tamaño más común) ibdata1 preasigna el 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 de que el archivo siga creciendo.

Por lo tanto, 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 reducirla.

Answer 4

Cuando eliminas 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 crear una secuencia de comandos y programar la secuencia de comandos para que se ejecute después de un período de tiempo fijo, pero para la configuración descrita anteriormente, parece que los espacios de tablas múltiples 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 tablas. 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 al eliminar 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

Answer 5

En una nueva versión de mysql-server, las recetas anteriores aplastarán la base de datos "mysql". En la versión antigua funciona. En las nuevas tablas, algunas de ellas 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, agregar my.cnf:

[mysqld]
innodb_file_per_table=1


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

Answer 6

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 (y el directorio binlog ).

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

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;

Answer 7

Si su objetivo es monitorear el espacio libre de MySQL y no puede detener MySQL para reducir su archivo ibdata, hágalo pasar por 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

Answer 8

Que ibdata1 no se esté encogiendo es una característica particularmente molesta de MySQL. El archivo ibdata1 no se puede ibdata1 realmente 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 esa manera, 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.

Hace un tiempo 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 recuperar el espacio de ibdata1 , debe eliminar el archivo:

1. Haga un mysqldump de todas las bases de datos, procedimientos, desencadenadores, 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. Deja de mysql
4. Eliminar archivos ibdata1 y ib_log
5. Iniciar mysql
6. Restaurar desde el vertedero

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

Ahora estás en condiciones de ir. Cuando cree una nueva base de datos para el análisis, las tablas se ibd* archivos ibd* separados, 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 <tablename> ENGINE=innodb o OPTIMIZE TABLE <tablename> 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.

Respecto al esquema de information_schema , no es necesario ni 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 utiliza el motor de memoria db y se suelta y se regenera al detener / reiniciar mysqld. Consulte https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .