Shrinking mysql ibdata1 file
Manual steps overview
- Export all databases except mysql & information_schema
$ mysqldump -u root -p ranger_audit > /backup_directory/db_ranger_audit_<currdate>.sql
- Drop all databases except mysql & information_schema
mysql> drop database ranger_audit;
- Stop mysql
$ service mysqld stop
- 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
- Delete ibdata01 file, delete iblog files
rm /var/lib/mysql/ibdata1 rm /var/lib/mysql/ib_logfile0 rm /var/lib/mysql/ib_logfile1
- Start mysql
$ service mysqld start
- Create empty databases
mysql> create database ranger_audit;
- 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.
References
- http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
- http://thecpaneladmin.com/how-to-convert-innodb-to-innodb_file_per_table-and-shrink-ibdata1/
- http://erikimh.com/how-to-shrink-an-ibdata1-file-with-minimal-mysql-downtime/
- http://www.pc-freak.net/blog/fix-mysql-ibdata-file-size-ibdata1-file-growing-large-preventing-ibdata1-eating-disk-space/