Estoy usando MySQL en localhost como una «herramienta de consulta» para la realización de estadísticas en R, es decir, cada vez que ejecute una secuencia de comandos de R, puedo crear una nueva base de datos (Una), crear una nueva tabla (B), la importación de los datos en B, enviar una consulta para obtener lo que necesito, y luego me deje caer B y soltar A.

Funciona muy bien para mí, pero me doy cuenta de que el ibdata tamaño de archivo está aumentando rápidamente, que almacena nada en MySQL, pero el ibdata1 archivo ya superó los 100 MB.

Estoy utilizando más o menos por defecto de MySQL configuración para la configuración, hay una manera para que yo pueda automáticamente, reducir/purgar el ibdata1 archivo después de un período fijo de tiempo?

InformationsquelleAutor lokheart | 2010-08-11

8 Comentarios

  1. 752

    Que ibdata1 no deja de ganar terreno es particularmente molesto característica de MySQL. El ibdata1 archivo en realidad no puede ser reducido a menos de eliminar todas las bases de datos, eliminar los archivos y volver a cargar un basurero.

    Pero se puede configurar MySQL para que cada tabla, incluyendo sus índices, se almacena como un archivo independiente. De esa manera ibdata1 no crecen tan grandes. De acuerdo a Proyecto de ley Karwin comentario esta opción está activada por defecto de la versión 5.6.6 de MySQL.

    Fue hace un tiempo hice esto. Sin embargo, para la configuración de su servidor para que utilice archivos independientes para cada tabla debe cambiar my.cnf con el fin de habilitar esta:

    [mysqld]
    innodb_file_per_table=1

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

    Como quiera recuperar el espacio de ibdata1, de hecho tienes que eliminar el archivo:

    1. Hacer un mysqldump de todas las bases de datos, procedimientos, triggers, etc excepto el mysql y performance_schema bases de datos
    2. Caída de todas las bases de datos excepto los anteriores 2 bases de datos
    3. Dejar de mysql
    4. Eliminar ibdata1 y ib_log archivos
    5. Iniciar mysql
    6. Restaurar desde el vertedero de

    Al iniciar MySQL en el paso 5 de la ibdata1 y ib_log archivos se volverán a crear.

    Ahora estés en condiciones de ir. Cuando se crea una nueva base de datos para el análisis, las tablas se encuentran en diferentes ibd* archivos, no en ibdata1. Como se suele colocar la base de datos de pronto después de, la ibd* archivos serán eliminados.

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

    Usted probablemente ha visto esto:

    http://bugs.mysql.com/bug.php?id=1341

    Mediante el comando ALTER TABLE <tablename> ENGINE=innodb o OPTIMIZE TABLE <tablename> uno puede extraer de los datos y las páginas de índice de ibdata1 como archivos separados. Sin embargo, ibdata1 no se reduce a menos de hacer los pasos anteriores.

    Con respecto a la information_schema, que no es necesario ni posible caída. De hecho, es sólo 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á usando la memoria db-motor y se quita y se regenera después de la parada/reinicio de mysqld. Ver https://dev.mysql.com/doc/refman/5.7/en/information-schema.html.

    • No hay necesidad para que el «=1», según el dev.mysql.com/doc/refman/5.1/en/…
    • ¿Qué acerca de information_schema y performance_schema?
    • Traté de que en un servidor de prueba, no me deja (conectado como root de mysql administrador/usuario) caída de information_schema. Así que, no.
    • No te molestes en dejar caer information_schema. De hecho, es sólo un montón de vistas de solo lectura, no tablas. Y no hay archivos asociados con ellos. Ni siquiera hay un directorio de la base de datos. El informations_schema está usando la memoria db-motor y se quita y se regenera después de la parada/reinicio de mysqld. Consulte dev.mysql.com/doc/refman/5.5/en/information-schema.html. Con respecto a performance_schema no he utilizado el esquema de mí mismo.
    • un poco de cross-link: stackoverflow.com/questions/3927690/…
    • No sé si este es uno de los últimos cosa, pero una vez que el innodb_file_per_table opción está habilitada, usted puede simplemente ejecutar el comando «ALTER TABLE <tablename> ENGINE=InnoDB» (incluso si ya InnoDB) y mover la mesa en su expediente individual. No hay necesidad de eliminar bases de datos y tal.
    • Que puede ser útil, pero no creo que se encoge el ibdata1 archivo. El comando mover los datos y el índice de las páginas en un archivo separado, pero ibdata1 mantenga los metadatos y los buffers y aún reclamar el espacio que antes.
    • +1 por lo que vale, MySQL 5.6 permite innodb_file_per_table por defecto.
    • Por la razón que sea, tengo la ibdata1 archivo (~1 GB de tamaño) Y tengo el .eii archivos que representan las tablas en cada uno de mis base de datos de carpetas. No estoy seguro de por qué tengo tanto, ya tengo el «innodb_file_per_table» conjunto de opciones. Supongo que no sería la creación de los archivos individuales si no se ha establecido. Por lo tanto debe la ibdata1 archivo de estar allí todavía, incluso con la opción de configurar?
    • Sí, ibdata1 se espera que se presente junto con los otros archivos. El ibdata1 archivo mantenga metadatos acerca de las tablas, la anulación de registro y de los buffers.
    • MySQL sólo me causó serios problemas. He editado tu respuesta en la esperanza de que esto no le suceda a alguien más. i.stack.imgur.com/kNXoo.png que me dejó todas mis bases de datos, excepto para mysql. Observe que el error en el medio de la misma en performance_schema? Sí, bueno, mi BIG DUMP.SQL archivo sólo contiene las bases de datos después que. Yo no me di cuenta hasta después de que yo había abandonado mi db… acabo de perder ~15 bases de datos!
    • Véase también ¿Cuál es la mejor manera de reducir el tamaño de ibdata en mysql? más en Administradores de bases de datos.
    • pero si me optimizar todas las tablas (de ahí la extracción de los datos de la ibdata archivo) – puedo dejar de mysql, eliminar la ibdata, iniciar mysql (que reconstruirá la ibdata archivo)? O las tablas ser corruptos?
    • Me he quedado sin espacio en mi servidor debido a la ibdata1 archivo, así que no puedo incluso el volcado de bases de datos. Sería el mismo de mover los archivos en /var/lib/mysql (a excepción de «mysql», «ibdata1», «ib_logfile0» y «ib_logfile1») y, a continuación, siga los pasos? Consulte stackoverflow.com/questions/2482491/…
    • Como algunos dijeron que no es necesario borrar nada. Usted puede simplemente ejecutar mysqlcheck -op database_name como yo lo entiendo, que es básicamente sólo otra manera de ejecutar alter table de comandos de mysql. Cuando usted necesita para eliminar cosas es que si usted quiere deshacerse de ese gran ibdata1 archivo. Si usted no soltar la base de datos y volver a crear, como se explica usted todavía tendrá que gran archivo sentado ahí, ocupando espacio innecesariamente.
    • Esto no funciona para mí. Después de la copia de seguridad de mis tablas, colocándolos y la eliminación de los archivos especificados anteriormente, yo era incapaz de llevar a mysql, el servidor de copia de seguridad (tengo: InnoDB: Error: space header page consists of zero bytes in data file ./ibdata1 entre otras cosas). Incluso desinstalar y purgar el mysql paquetes no consigo que funcione. Tuve que desinstalar y eliminar manualmente el archivo /var/lib/mysql directorio completo y volver a instalar mysql.
    • Realice la copia de seguridad .volcado sql y la restauración es completamente inviable para un 200GB de la base de datos. ¿Tiene una solución mejor? No puedo convertir mis tablas MyISAM y volver?
    • Solo para aclarar: que Usted necesita para poner «innodb_file_per_table=1″ en el archivo». innodb_file_per_table» no es suficiente.
    • El uso de la versión del Servidor: 5.7.14 Comunidad de MySQL Server (GPL) en Windows 10 como parte de WampServer. innodb_file_per_table no está establecido de forma predeterminada. Solo tengo una gran ibdata1 archivo. Tal vez el ajuste predeterminado es sólo para la versión 5.6.x solamente.
    • Gracias por esta respuesta. En realidad information_schema no se puede soltar. Así que tenemos que ignorarlo.
    • Con respecto a el paso 1, aquí una buena y rápida manera de hacerlo: stackoverflow.com/a/19204404/925058. También, se puede adoptar el mismo criterio a la hora de re-importación de volcado
    • Rápidamente guión de esta respuesta en Bash, vea a continuación: stackoverflow.com/a/51543987/4904695

  2. 42

    Añadir a Juan P la respuesta,

    Para un sistema linux, los pasos 1 a 6 se puede 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 puede ser llamado 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 podrá causar la perdida de otras bases de datos si usted tiene otras bases de datos en esta instancia de mysql. Asegúrese de que los pasos 1,2 y 6,7 son modificados para cubrir todas las bases de datos que desea mantener.

    • Es necesario repetir los 1, 2, y 6 para cada base de datos que ha de tablas InnoDB.
    • Usted necesita un par de pasos más en la entre #5 y #6. Tiene que volver a crear la base de datos y re-asignar permisos. Así que a partir de cliente de mysql símbolo del sistemacreate database database_name; y, a continuación, grant all privileges on database_name.* to 'username'@'localhost' identified by 'password';
    • No tenía la necesidad de otorgar privilegios al hacer esto. Posiblemente porque me vuelven a crear la base de datos con el mismo nombre?
    • Escriba la contraseña en un Password: símbolo del sistema (lo cual es una práctica segura), acaba de poner -p sin ningún tipo de contraseña real.
    • Desencadenadores de eventos y rutinas, funciones que no son objeto de dumping, a menos que le digas mysqldump para hacerlo. Añada la opción –desencadenantes, –eventos y –rutinas de si alguno de sus bases de datos contienen. También, acaba de volcar con –todas las bases de datos para el volcado de todas las bases de datos a la vez en lugar de uno por uno.
    • Ahora esta causa el error InnoDB: File ./ibdata1: 'open' returned OS error 71. Cannot continue operation, por lo que no se reinicie el servidor!

  3. 33

    Al eliminar tablas innodb, MySQL no es libre el espacio interior de la ibdata archivo, por eso es que sigue creciendo. Estos archivos casi nunca se encogen.

    Cómo reducir existente ibdata de archivo:

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

    Puede guión de esta y programar el script para que se ejecute después de un período fijo de tiempo, pero para la instalación descrita anteriormente parece que varios tablespaces son una solución más fácil.

    Si utiliza la opción de configuración innodb_file_per_table, crear múltiples espacios de tablas. Es decir, MySQL crea archivos independientes para cada tabla en lugar de un archivo compartido. Estos por separado los archivos almacenados en el directorio de la base de datos, y que cuando se elimina esta base de datos. Esto debería eliminar la necesidad de reducir/purga ibdata archivos en su caso.

    Obtener más información acerca de varios tablespaces:

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

  4. 14

    Si utiliza el motor de almacenamiento InnoDB para (algunos de) sus tablas de MySQL, es probable que ya hayas encontré con un problema con su configuración por defecto. Como te habrás dado cuenta en su directorio de datos de MySQL (en Debian/Ubuntu /var/lib/mysql) se encuentra un archivo llamado ‘ibdata1’. Tiene casi todos los InnoDB datos (no es un registro de transacciones) de la instancia de MySQL y podría llegar a ser muy grande. Por defecto, este archivo tiene un tamaño inicial de 10 mb y se extiende automáticamente. Lamentablemente, por el diseño de InnoDB archivos de datos no se reduce. Por eso se Elimina, Trunca, Gotas, etc. no va a recuperar el espacio utilizado por el archivo.

    Creo que se puede encontrar una buena explicación y solución :

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

  5. 7

    Rápidamente el guión que la aceptan respuesta del procedimiento 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 binlog directorio).

    Se supone que tiene el administrador de la credendials en /root/.my.cnf y que su base de datos de vidas en defecto /var/lib/mysql directorio.

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

    PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;
  6. 6

    Si su objetivo es el monitor de MySQL espacio libre y que no puede dejar de MySQL para reducir el tamaño de su ibdata archivo, a continuación, obtener a través de la tabla de estado de los comandos. Ejemplo:

    MySQL > 5.1.24:

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

    MySQL < 5.1.24:

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

    A continuación, compare este valor con su ibdata de archivo:

    du -b ibdata1

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

  7. 4

    En una nueva versión de mysql-server recetas por encima de aplastará «mysql» de la base de datos.
    En la versión anterior funciona. En el nuevo algunas tablas cambia a tipo de tabla INNODB, y por ello se va a dañar.
    La forma más fácil es para volcar todas sus bases de datos, desinstalar mysql-server,
    agregar en permanecido a mi.cnf:

    [mysqld]
    innodb_file_per_table=1
    
    
    erase all in /var/lib/mysql
    install mysql-server
    restore users and databases
  8. 0

    Como ya se ha señalado que no se puede encoger ibdata1 (para ello necesita de volcado y reconstrucción), pero también hay a menudo no hay necesidad real para.

    Utilizando autoextend (probablemente el más común de ajuste de tamaño) ibdata1 preasigna de almacenamiento, creciendo cada vez es casi completa. Que hace que escribe más rápido ya que el espacio es ya asignados.

    Al eliminar los datos que no, sino reducir el espacio dentro de el archivo está marcado como no utilizados. Ahora al insertar nuevos datos que va a reutilizar el espacio vacío en el archivo antes de hacerse el archivo más.

    Así que sólo voy a seguir creciendo si usted está realmente en necesidad de que los datos. A menos que usted realmente necesita el espacio para otra aplicación probablemente no hay razón para reducirlo.

    • Creo que eres un poco demasiado desdeñoso de la necesidad de liberar el espacio.
    • Tengo un 60Gig de Estado Sólido de la partición. Se me acaba el espacio rápido, ya que yo trabajo con 4+concierto de bases de datos. Estoy buscando para pasar de mysql a otra partición pronto, pero esta pregunta y las respuestas me ayudarán en el ínterin
    • Gracias por esta respuesta, es muy útil. Me han aclarado algunas tablas de legado de datos… es bueno saber que el tamaño en el disco no va a crecer de nuevo en cualquier momento pronto.
    • Tengo un 500G ibdata1 archivo – pero casi todos los datos que se almacenan en ella se almacena ahora en per-archivos de base de datos. Tengo la necesidad de reducir esta collossal desperdicio de espacio!
    • Una completa tontería! Un archivo que mantiene abusar de necesita ser podado si se está quedando sin espacio de o de. Yo diría que es un storage leak.

Dejar respuesta

Please enter your comment!
Please enter your name here