How to shrink ibdata1 file size after moving to innodb_file_per_table?

Question

I've followed the instruction in this thread ( https://dba.stackexchange.com/questions/8982/what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql ) successfully but after 2 months the ibdata1 continues to grow. I've tried optimizing tables but it didn't reclaimed lost storage space. How do i shrink ibdata1 file size? Is there any way to delete it safely?

There are several uses for ibdata1 other than tables. How big is it now? If it is a few megabytes, I would not worry. Also, what is the value in my.cnf for its initial size? — Rick James, Dec 01 '15 at 21:45

score 3 · Accepted Answer · edited May 23 '17 at 12:17

3

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

Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases

Drop all databases except the above 2 databases

Stop mysql

Delete ibdata1 and ib_log files

Start mysql

Restore from dump

Source

edited May 23 '17 at 12:17

Community

1
1

answered Nov 09 '15 at 08:01

Rahul Tripathi

168,305
31
280
331

do i have to do this again? i have done this 2 months ago. or is this the only way? – Christian Burgos Nov 09 '15 at 08:03
@ChristianBurgos:- You have to repeat process again :) – Rahul Tripathi Nov 09 '15 at 08:07

How to shrink ibdata1 file size after moving to innodb_file_per_table?

1 Answers1