How to purge or recreate ibdata1 file in MySQL

I am using MySQL on my hosting and after a check on the folder /var/lib/mysql and I realized that the ibdata file size is increasing rapidly, over 300MB, I don't know which causes this, can I purge ibdata1 file content or recreate it with a empty file?

Hi Maxoq,

This is a common problem with MySQL using InnoDB.

ibdata1, holds ALL the data from EVERY InnoDB table you have on your MySQL server.
This file never shrinks in size, it only expands to contain new data.


One way of fixing this issue is having MySQL keep data tables in their own files.

Export/dump all your databases.
Drop all databases, except for the mysql database, and information_schema, if it exists.
Shut down MySQL.
Delete the ibdata1 file and any ib_logfile log files.
Add innodb_file_per_table under the [mysqld] heading in your my.cnf file.
Start MySQL.
Import all your databases.


This should result in less disk usage with the new configuration.

Warning: As you can see this a major change and I would advise you are sure about making any changes before doing so.

Hi LJSHost,

I am afraid of delete this file can cause damage of Mysql server or databases. My way is change its name and create an empty file and replace with existing file on the server hence I will have a new file ibdata1 with 0 KB filze size.

Have you already done this ? did it work ?

Just Replacing the ibdata1 will cause huge problems instead of letting it rebuild into separate files.

Maxoq,

Per LJSHost message, you can't just create a new empty ibdata1 file, see his message. First question is why is a 300MB file a concern to you. Are you running out of disk space? What is the underlying issue you are trying to resolve?