Shrinking mysql ibdata1 file

Manual steps overview

1. Export all databases except mysql & information_schema

   $ mysqldump -u root -p ranger_audit > /backup_directory/db_ranger_audit_<currdate>.sql
   

    

2. Drop all databases except mysql & information_schema

   mysql> drop database ranger_audit;

    

3. Stop mysql

   $ service mysqld stop

    

4. Edit /etc/my.cnf to enable per table database file

   # below values suited for production environments
   
   [mysqld]
   innodb_file_per_table=1
   innodb_flush_method=O_DIRECT
   innodb_log_file_size=1G
   innodb_buffer_pool_size=4G

    

5. Delete ibdata01 file, delete iblog files

   rm /var/lib/mysql/ibdata1
   rm /var/lib/mysql/ib_logfile0
   rm /var/lib/mysql/ib_logfile1

    

6. Start mysql

   $ service mysqld start

    

7. Create empty databases

   mysql> create database ranger_audit;

    

8. Import database backup

   $ mysql -u root -p ranger_audit < /backup_directory/db_ranger_audit_<currdate>.sql

    

Important scripts

Check database size from mysql shell

-- replace $DB_NAME & $TABLE_NAME with actuals

SELECT 
table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME" 
AND table_name = "$TABLE_NAME"; 

Automation scripts/ Tools

Never tested any of the below tools myself. Use them on your own risk.

1. https://github.com/uberhacker/shrink-ibdata1
2. https://github.com/thecpaneladmin/innodb-tools

References

1. http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
2. http://thecpaneladmin.com/how-to-convert-innodb-to-innodb_file_per_table-and-shrink-ibdata1/
3. http://erikimh.com/how-to-shrink-an-ibdata1-file-with-minimal-mysql-downtime/
4. http://www.pc-freak.net/blog/fix-mysql-ibdata-file-size-ibdata1-file-growing-large-preventing-ibdata1-eating-disk-space/