After deleting some tables/databases on my MySQL server, I noticed the ibdata1 file is never shrinking. After almost a year, my MySQL ibdata1 file became really too large.
More information on this issue: http://bugs.mysql.com/bug.php?id=1341.
- Dump (e.g., with mysqldump) all databases into a .sql text file
$ mysqldump -u root -p --all-databases > bigdump.sql
- Drop all databases (except for mysql and information_schema)
- Login to mysql and run 'SET GLOBAL innodb_fast_shutdown = 0;' (This will completely flush all remaining transactional changes from ib_logfile0 and ib_logfile1)
- Shutdown MySQL
- Add the following lines to /etc/my.cnf:
innodb_file_per_table innodb_flush_method=O_DIRECT innodb_log_file_size=1G innodb_buffer_pool_size=4G
- Delete ibdata* and ib_logfile*
- Start MySQL
- Import databases
$ mysql -u root -p < bigdump.sql