How to purge or recreate ibdata1 file in MySQL

Maxoq

Well-known member
Registered
Joined
Feb 25, 2015
Messages
520
Points
28
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?
 

LJSHost

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
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.
 

Maxoq

Well-known member
Registered
Joined
Feb 25, 2015
Messages
520
Points
28
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.
 

LJSHost

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
Have you already done this ? did it work ?

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

energizedit

Well-known member
Registered
Joined
Dec 13, 2016
Messages
259
Points
18
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?
 
Newer Threads
Latest Threads
Replies
0
Views
123
Replies
1
Views
79
Similar Threads

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top