A MySql ibdata1 Optimization Technique

A MySql database needed to be removed and upon deletion of that single database, the extremely large three gigabite ibdata1 (data) file remained. Here are the steps I took to reduce or shrink the file once the database was removed. Since there was no other use for this MySql installation, there remained the MySql system databases.

Show Databases

  • At the mysql command, the command show databases; is used to determine what databases remain.
    mysql> show databases;
  • The results were information_schema and mysql.

Backup Databases

  • mysqldump -u root -p mysql >mysql.sql
  • mysqldump -u root -p information_schema >info.sql

Delete Databases
In this case the files were located here: D:\MySQL\MySQL Server 5.0\data

  • Stop the MySql server service
  • Delete the ib_logfile0, ib_logfile1, ibdata1, servername.err, servername.pid files (where servername is the name of the server).
  • Start the MySql server service

Restore Databases

  • D:\MySQL\MySQL Server 5.0\bin>mysql -uroot -p mysql < mysql.sql
    Enter password: *********
  • D:\MySQL\MySQL Server 5.0\bin>mysql -uroot -p information_schema < info.sql
    Enter password: *********

Another approach might be uninstall MySql and reinstall it. This was more fun.