A Smaller IBDATA file

I have seen the desire for a smaller ibdata file come up lately on the forums.mysql.com

The innodb database uses the ibdata file(s) to store the database data to disk. Configuring your system correctly is key and you can learn more about such options here: http://dev.mysql.com/doc/refman/5.6/en/innodb-configuration.html

InnoDB provides an ACID compliant and transaction safe storage engine; it is very productive but if you are deleting and/or replacing data often, you will need to recover the lost space over time. How much time is dependent on your system and use. You cannot run a single command and recover space in an ibdata file. It will take a few steps and it is not a behind the scenes job, unless done on a slave server. If you have a slave it is best to do this work on the slaved database first then plan to rotate that database server to become the master database.

So two different situations; these are not the only solutions but some solutions :

• You want to keep the ibdata file the same size but you want to just clear out the wasted space

The best way to recover lost space is to dump the data and reload it. Yes, not the first choice for a DBA I know. This is more troublesome the bigger your database is. I would hope you have a slave database and can do this off the slave then make it a master later.

1. Backup the database 
1. mysqldump --user=<username> --password=<> --add-drop-database   --master-data=2  --triggers --routines --events --databases (list database names and do not add mysql to this list) > /Just_AN_example/mysqldump_<DATEHERE>_.sql 
1. This gives you an ASCII copy just in case of binary corruption. 
2. It also has master data via a comment if needed.
3. This will keep your mysql authentication in tact as well. 
1. I would save the mysql database as a dump separately. 
2. You  also can create a backup with MySQL Enterprise Backup or Percona XtraBackup, if the system was a bigger db and needed online backups these are good choices. Up to you which you use for various reasons. 
2. Checksum your database. 
1. Gather some numbers on what you have so you can compare it when you load it back. 
1. This can be done with Percona Toolkit
1. 
   
   # ./pt-table-checksum --password=<Password>   > checksum_before_dump.txt
2. A query you can write yourself.
1. I have a blog post on this as well 
1. http://anothermysqldba.blogspot.com/2013/05/mysql-checksum.html
3. Stop/Start the database and take advantage of this downtime for any read only variables you would like to adjust 
4. Load the database back 

• You want to move to innodb_file_per_table and shrink the ibdata file 

1. Follow steps 1 through 2 in the process listed above. 
2. In the step 4 of the above process you will want to add the following to your my.cnf file.
1. innodb_file_format=Barracuda
2. innodb_file_per_table=1
3. Remove the ibdata file and logs.
1. No coming back from this point 
4. Start the database 
5. Confirm it is up and running
6. Load the database from backup. 

This of course would be best to do on a non production/slave server so you can confirm all the steps and get yourself to a workable situation then rotate the slave to be the new master.