How to shrink purge ibdata1 file in MySQL

Question

I am using MySQL in localhost as a  query tool  for performing statistics in R  that is  everytime I run a R script  I create a new database  A   create a new table  B   import the data into B  submit a query to get what I need  and then I drop B and drop A   It s working fine for me  but I realize that the ibdata file size is increasing rapidly  I stored nothing in MySQL  but the ibdata1 file already exceeded 100 MB   I am using more or less default MySQL setting for the setup  is there a way for I can automatically shrink purge the ibdata1 file after a fixed period of time

User · Answer

When you delete innodb tables  MySQL does not free the space inside the ibdata file  that s why it keeps growing  These files hardly ever shrink  How to shrink an existing ibdata file  https   dev mysql com doc refman 5 6 en innodb-system-tablespace html innodb-resize-system-tablespace You can script this and schedule the script to run after a fixed period of time  but for the setup described above it seems that multiple tablespaces are an easier solution  If you use the configuration option innodb file per table  you create multiple tablespaces  That is  MySQL creates separate files for each table instead of one shared file  These separate files a stored in the directory of the database  and they are deleted when you delete this database  This should remove the need to shrink purge ibdata files in your case  More information about multiple tablespaces  https   dev mysql com doc refman 5 6 en innodb-file-per-table-tablespaces html

User · Answer

If your goal is to monitor MySQL free space and you can t stop MySQL to shrink your ibdata file  then get it through table status commands  Example   MySQL   5 1 24   mysqlshow --status myInnodbDatabase myTable   awk   print  20     MySQL  lt  5 1 24   mysqlshow --status myInnodbDatabase myTable   awk   print  35     Then compare this value to your ibdata file   du -b ibdata1   Source  http   dev mysql com doc refman 5 1 en show-table-status html

User · Answer

Adding to John P s answer  For a linux system  steps 1-6 can be accomplished with these commands   mysqldump -u  username  -p root password   database name   gt  dumpfilename sql  mysqladmin -u  username  -p root password  drop  database name  sudo  etc init d mysqld stop  sudo rm  var lib mysql ibdata1  sudo rm  var lib mysql ib logfile  sudo  etc init d mysqld start  mysqladmin -u  username  -p root password  create  database name  mysql -u  username  -p root password   database name   lt  dumpfilename sql  Warning  these instructions will cause you to lose other databases if you have other databases on this mysql instance   Make sure that steps 1 2 and 6 7 are modified to cover all databases you wish to keep

User · Answer

Quickly scripted the accepted answer s procedure in 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  amp  amp  echo   DATABASES    while read -r DB  do     mysql -e  drop database    DB    done  amp  amp         etc init d mysql stop  amp  amp        find  var lib mysql -maxdepth 1 -type f    -name  ibdata1  -or -name  ib logfile      -delete  amp  amp         etc init d mysql start  amp  amp        mysql  lt  alldatabases sql  amp  amp        rm -f alldatabases sql   Save as purge binlogs sh and run as root   Excludes mysql  information schema  performance schema  and binlog directory    Assumes you have administrator credendials in  root  my cnf and that your database lives in default  var lib mysql directory   You can also purge binary logs after running this script to regain more disk space with   PURGE BINARY LOGS BEFORE CURRENT TIMESTAMP

User · Answer

In a new version of mysql-server recipes above will crush  mysql  database  In old version it works  In new some tables switches to table type INNODB  and by doing so you will damage them  The easiest way is to    dump all you databases uninstall mysql-server  add in remained my cnf         mysqld      innodb file per table 1    erase all in  var lib mysql install mysql-server restore users and databases

User · Answer

That ibdata1 isn t shrinking is a particularly annoying feature of MySQL  The ibdata1 file can t actually be shrunk unless you delete all databases  remove the files and reload a dump  But you can configure MySQL so that each table  including its indexes  is stored as a separate file  In that way ibdata1 will not grow as large  According to Bill Karwin s comment this is enabled by default as of version 5 6 6 of MySQL  It was a while ago I did this  However  to setup your server to use separate files for each table you need to change my cnf in order to enable this   mysqld  innodb file per table 1  https   dev mysql com doc refman 5 6 en innodb-file-per-table-tablespaces html As you want to reclaim the space from ibdata1 you actually have to delete the file   Do a mysqldump of all databases  procedures  triggers etc except the mysql and performance schema databases Drop all databases except the above 2 databases Stop mysql Delete ibdata1 and ib log files Start mysql Restore from dump  When you start MySQL in step 5 the ibdata1 and ib log files will be recreated  Now you re fit to go  When you create a new database for analysis  the tables will be located in separate ibd  files  not in ibdata1  As you usually drop the database soon after  the ibd  files will be deleted  http   dev mysql com doc refman 5 1 en drop-database html You have probably seen this  http   bugs mysql com bug php id 1341 By using the command ALTER TABLE  lt tablename gt  ENGINE innodb or OPTIMIZE TABLE  lt tablename gt  one can extract data and index pages from ibdata1 to separate files  However  ibdata1 will not shrink unless you do the steps above  Regarding the information schema  that is not necessary nor possible to drop  It is in fact just a bunch of read-only views  not tables  And there are no files associated with the them  not even a database directory  The informations schema is using the memory db-engine and is dropped and regenerated upon stop restart of mysqld  See https   dev mysql com doc refman 5 7 en information-schema html

User · Answer

As already noted you can t shrink ibdata1  to do so you need to dump and rebuild   but there s also often no real need to   Using autoextend  probably the most common size setting  ibdata1 preallocates storage  growing each time it is nearly full  That makes writes faster as space is already allocated   When you delete data it doesn t shrink but the space inside the file is marked as unused  Now when you insert new data it ll reuse empty space in the file before growing the file any further   So it ll only continue to grow if you re actually needing that data  Unless you actually need the space for another application there s probably no reason to shrink it

User · Answer

If you use the InnoDB storage engine for  some of  your MySQL tables  you   ve probably already came across a problem with its default configuration  As you may have noticed in your MySQL   s data directory  in Debian Ubuntu      var lib mysql  lies a file called    ibdata1   It holds almost all the InnoDB data  it   s not a transaction log  of the MySQL instance and could get quite big  By default this file has a initial size of 10Mb and it automatically extends  Unfortunately  by design InnoDB data files cannot be shrinked  That   s why DELETEs  TRUNCATEs  DROPs  etc  will not reclaim the space used by the file    I think you can find good explanation and solution there    http   vdachev net 2007 02 22 mysql-reducing-ibdata1

[mysql] How to shrink/purge ibdata1 file in MySQL

Examples related to mysql

Examples related to database

Examples related to innodb