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

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?

Adding to John P's answer,

For a linux system, steps 1-6 can be accomplished with these commands:

1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
2. mysqladmin -u [username] -p[root_password] drop [database_name]
3. sudo /etc/init.d/mysqld stop
4. sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile*
5. sudo /etc/init.d/mysqld start
6. mysqladmin -u [username] -p[root_password] create [database_name]
7. mysql -u [username] -p[root_password] [database_name] < 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.

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

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 && 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

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;

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 < 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

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

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.

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/