Shrink ibdata1 file in MySQL

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