How to shrink/purge ibdata1 file in mysql

L 9, 9 9 gold badges 57 57 silver badges 91 91 bronze badges. Vik Vik 6, 3 3 gold badges 28 28 silver badges 37 37 bronze badges. Cyno Cyno 2 2 silver badges 8 8 bronze badges. The easiest way is to: dump all you databases uninstall mysql-server, add in remained my. Bahram Ardalan 3 3 silver badges 11 11 bronze badges. I think you're a little too dismissive of the need to free up the space. I have a 60Gig Solid State partition.

I'm looking to move mysql to another partition soon, but this question and it's answers will help me in the meantime — NullVoxPopuli. Thank you for this answer, it's very helpful. I have cleared out some tables from legacy data I have a G ibdata1 file - but almost all of the data that was stored in it is now stored in per-database files. I very much need to shrink this collossal waste of space! Complete nonsense! A file that keeps bloating up needs to be trimmed whether you're running out of space or not.

I would call it a storage leak. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Helping communities build their own LTE networks.

Podcast Making Agile work for data science. Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked 0. See more linked questions. Related Hot Network Questions. Question feed. Stack Overflow works best with JavaScript enabled. Accept all cookies Customize settings.

In case something goes wrong, you can use this cold backup to restore. For details on mysqldump, refer to how to use mysqldump article.

Next, drop all your database one by one. At this stage, the ibdata1 file, which is the MySQL system tablespace will be created from scratch, and in our example, it will not be GB anymore. Now ibdata1 will be only few MB in size.

I have one question. Is it possible or better jet would that work: I have a few databases. Can I restore databases with rsync from that backup? If I restore all of them or just one of the database, would mysql work after such restore? Should it also be deleted at 7? But how it will work with file per table? Save my name, email, and website in this browser for the next time I comment.

Notify me of followup comments via e-mail. All rights reserved Terms of Service. Marko February 18, , am. Your Email required. Describe your problem. Skip to content. Then we decided to use the second option that was reduce system table space. Leave a Reply Cancel reply Your email address will not be published.