How to reduce the size of ibdata1 file after the database is dropped

Before MySQL versions 5.6, the database stores the data in ibdata1, and the data in this file is not cleaned even after you dropped one of your database in MySQL. This is annoying when you don’t have a lot of disk spaces left and need to free up some disk space. Since this file contains the data from all the databases you have in MySQL, so you can’t just delete the file without deleting all your data. The only way to do it is to
1. make a sqldump of every database(including prodedures and triggers) you may have, or use any database backup methods you use.

2. Drop all the databases, except the default databases that came with MySQL such as mysql, phpmyadmin, etc.

3. Shut down MySQL server. (mysqladmin5 shutdown)

4. Delete ibdata1 and ib_log files, remember ib_log files needed to be deleted, otherwise you will get errors when starting up mysql if you just delete the ibdata1 file.

Before go to the next step to restore the databases you want to keep, if you want to stop the ibdata1 file to increase as you create more data and you want all the data of a database to be erased after you dropped the database, then add the following line in the my.cnf file under [mysqld]. In Windows, it’s my.ini

innodb_file_per_table

5. Start MySQL server. (mysqld_safe)

6. Restore the databases from your backup sqldump files.

Search within Codexpedia

Custom Search

Search the entire web

Custom Search