0

I have followed the following steps in order to save space but I am surprised after restarting MySQL ibdata1 gets back to it's original size.

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

  1. Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
  2. Drop all databases except the above 2 databases
  3. Stop mysql
  4. Delete ibdata1 and ib_log files
  5. Start mysql
  6. Restore from dump

Source

  • This question has got nothing to do with sw development, this is a pure admin question, therefore it is off topic here on SO. You may get more help at the DBA site. – Shadow Jul 23 '19 at 23:33
  • Why down vote? At least explain. – H Aßdøµ Jul 23 '19 at 23:52
  • Did you drop/delete any tables/db that you don't want before you mysqldump all the databases? – tcadidot0 Jul 24 '19 at 3:33
1

Did you have innodb_file_per_table = ON?

Regardless of the above setting, ibdata1 will have some stuff in it. Without more specifics (size of that file, the above setting, number of tables, number of .ibd files, etc.), I cannot be more specific.

In my opinion, "tiny" tables may as well be built with file_per_table = OFF.

  • 1
    It takes a lot of attention to manage file_per_table differently per table on the same MySQL instance, because when you ALTER TABLE or OPTIMIZE TABLE, the table will be copied with respect to the current file_per_table setting. It's too easy to slurp a huge table into ibdata1 by accident, which will increase its size irreversibly. – Bill Karwin Jul 24 '19 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.