Are you looking for a way to Shrink ibdata1 file in MySQL? Our experts have the answer, Read on to learn more. Our MySQL Support Services team is here to offer a lending hand with your queries and issues.

Shrink ibdata1 file in MySQL

The ibdata1 file can be shrinked if you delete all databases, remove the files and reload the mysqldump.

We can configure MySQL so that each table, including its indexes, is stored as a separate file.

To setup our server to use separate files for each table, we need to change my.cnf in order to enable it.

If your MySQL version is below 5.6.6, then you need to add it in my.cnf file.

[mysqld]
innodb_file_per_table = 1

That ibdata1 isn’t shrinking is a particularly annoying feature of MySQL. The ibdata1 file can’t actually be shrunk unless you delete all databases, remove the files and reload a dump.

But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large.

to setup your server to use separate files for each table you need to change my.cnf in order to enable this:

[mysqld]
innodb_file_per_table=1






As you want to reclaim the space from ibdata1 you actually have to delete the file:

1, Firstly, do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
2. Then, drop all databases except the above 2 databases
3. Next, Stop mysql
4. Delete ibdata1 and ib_log files
5. Start mysql
6. Finally, Restore from dump

When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated.

When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1.

As you usually drop the database soon after, the ibd* files will be deleted.

By using the command ALTER TABLE <tablename> ENGINE=innodb or OPTIMIZE TABLE <tablename> one can extract data and index pages from ibdata1 to separate files.


Conclusion



To sum up, we got a firsthand look at how to shrink ibdata1 file in MySQL with this step-by-step guide by our experts.




PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED




Related posts:

MySQL ibdata1 file too big | Resolved
Plesk Error: innodb-page-size mismatch in tablespace
Innodb_force_recovery in MySQL
Disable MySQL InnoDB free tablespace alert