How to shrink/purge ibdata1 file in MySQL
Solution 1:
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 themysql
andperformance_schema
databases - Drop all databases except the above 2 databases
- Stop mysql
- Delete
ibdata1
andib_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 <tablename> ENGINE=innodb
or OPTIMIZE TABLE <tablename>
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.
Solution 2:
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] > 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] < 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.
Solution 3:
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
Solution 4:
Quickly scripted the accepted answer's procedure in bash:
#!/usr/bin/env bash
dbs=$(mysql -BNe 'show databases' | grep -vE '^mysql$|^(performance|information)_schema$')
mysqldump --events --triggers --databases $dbs > alldatabases.sql && \
echo "$dbs" | while read -r db; do
mysqladmin drop "$db"
done && \
mysql -e 'SET GLOBAL innodb_fast_shutdown = 0' && \
/etc/init.d/mysql stop && \
rm -f /var/lib/mysql/ib{data1,_logfile*} && \
/etc/init.d/mysql start && \
mysql < 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;
Solution 5:
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/