Mastering MAMP's Metadata-Munching MySQL

Since this post ran a bit long I've included a TL;DR section.

The Problem

MAMP's MysQL uses a shared InnoDB file "ibdata1" which grows over time and never shrinks. Not even when dropping databases. All the metadata and indices for all databases stay in there forever.

The Solution

Tell MySQL to store medata and indices in separate files per database.

  1. Dump all databases in files and drop the databases.
  2. Delete ibdata1, ib_logfile0 and ib_logfile1.
  3. Add innodb_file_per_table to the [mysqld] section of /etc/my.cnf
  4. Restart MySQL and reimport the dumped databases.

The Article

Recently I had to import a MySQL dump of 9GB resulting in a database of 21GB. I didn't have enough space on my hard drive and after continuously deleting applications and music as the import went on I realised I wouldn't make it. I aborted the process and dropped the partial database. Funnily enough I noticed I didn't get all my space back. After a little scooting around on the file system I understood something I had been suspecting for a while already. MAMP's default MySQL settings suck.

jeroen@liver:/Applications/MAMP/db/mysql$ ls -hl | grep ibdata1
-rw-rw---- 1 jeroen admin 14G Nov 4 16:25 ibdata1

I've always been slightly opposed to using MAMP for serious work but the company I work for uses it and their setup scripts expect folder layouts the way MAMP sets them up. Now, after a year of using MAMP on a 128GB MacBook Air I saw the shared InnoDB file MySQL uses had grown to 14GB. That is a lot of wasted space.

Why is it wasted space you might ask? Surely MySQL needs it for something. Yes, MySQL needs it. It stores the _current_ database-indices in there as well as the metadata for them, you can even restore whole databases, after having dropped them, from this InnoDB data file. But, it also stores all that information for databases you have already dropped. See, MySQL's ibdata1 file never shrinks, it only grows.

As a web developer working with Drupal for about a year now, one of the things I do a lot is importing databases of ongoing projects, dropping them when I screw up and reimporting a backup to try again. Therefore I bet not too many web-developers know about MAMP's hungry MySQL or I would've heard about it earlier.

There is a solution and it is called innodb_file_per_table. When you put this in the MySQL configuration file it tells MySQL to store the metadata and indices for each database in a separate file within that database's folder. Then, when you drop a database and MySQL deletes the folder it will also remove the metadata files.

If you're wondering where you can find the MySQL config file your best guess is /etc/my.cnf unless of course you're using MAMP because it just starts MySQL with any meaningful parameters, neither does it install a my.cnf anywhere MySQL looks for one. (More info here: http://bensch.be/mysql-config-with-mamp) You can copy one of MAMP's default MySQL config files to /etc/my.cnf. Look for them under /Applications/MAMP/Library/support-files/

None of MAMP's MySQL config files however include the innodb_file_per_table setting so make sure to add this to the [mysqld] section of the file you chose to copy to the /etc/my.cnf:

innodb_file_per_table

When you restart your MySQL server it will start putting the medatadata in the database folders that will be created from then on, unfortunately this does not get rid of the huge ibdata1 file. All current databases must be dumped and dropped first, then the ibdata1, ib_logfile0 and ib_logfile1 fils can be deleted. MySQL can be restarted with the innodb_file_per_table setting in place and the dumped databases can be reimported.

You'll be saving gigabytes of disk space in your day-to-day development.

While I was writing this post I came across this Stack Overflow answer: http://stackoverflow.com/a/4056261 It tells the same story but with a bit more detail about what's in the ibdata1 file.

Comments

Accordingly, during you fall a database besides MySQL expunges the binder it mind similarly eliminate the metadata columns. air conditioning repair chicago

The re enter values to smart working channels will amplify new turns to big success mark today. And it simply connect all new portions after perfect choice. buy instagram followers fast

I will probably be coming back to your weblog for more soon With support for power unit-GL/2 and horsepower RTL files, customary and HDI printer drivers, and enclosed PosterArtist light coding system, the iPF750 provides the flexibleness and suppleness needed for personal and small workgroup applications. if you need driver printer, you can check at driverresetter is place for download driver printer australia and you can check the driver for Download Canon imagePROGRAF iPF750 Driver Printer herereadmore
Samsung Galaxy Grand a pair of Review and Specifications. Samsung was one among the first companies to primarily run with the idea of giant - screen phones . Others had tried before and unsuccessful , but Samsung managed to indicate its original Galaxy Note into a massive succes if you need more info about gadgets, you can check at update your gadgets info here and you can check for the Samsung Galaxy Grand 2 Review and Specifications herereadmore
Mitsubishi hasn’t had a significant piece of the european market in years, but the japanese complete remains churning out models there. It’s recent one, though, merely might even be what the company has got to get Europeans’ attention. just for the uk market, if you need the last info of autocar, you can check at autocar review update everyday and you can check the Mitsubishi Lancer Evo X FQ-440 MR 2020 herereadmore
Yapenbit College is an institution of Education and Training in the Field of Information Technology / IT under the Educational Development Foundation for Information and Technology (YAPENBIT) Aceh is located at Lhokseumawe. if you need the last info of technology, you can check at update pengetahuan teknologimu di echokita and you can check the Belajar Komputer Gratis 3 Tahun di Yapenbit herejangan ngintip backlink :p

The intention of forming a site can always be professional as well as personal. Web growth today is incredibly convenient to the advanced technology available. Increasing internet sites and world wide web usage in today's world indicate the main advantages of software anatomist towards world wide web development. web development

I appreciated what you have done here. I am always searching for informative information like this. Thanks for sharing with us does the program work

Useful information ..I am very happy to read this.. thanks for giving us this useful information. Fantastic walk-through. plastic tubes packaging

I am always searching online for articles that can help me. There is obviously a lot to know about this. I think you made some good points in Features also. Keep working, great job dieta quema grasa | como quedar embarazada rapidamente

I found this post to be very educational. Thank you for broadening my knowledge of this subject. No doubt its a great piece of writing as well. Thanks how to make boobs larger

Im delighted to find that persons are indeed authoring this anxiety in such a saintly street, showing you sundry parts for it. http://ebookreviews.ning.com/pdf/girlfriend-activation-system-review

I'm simply a first measure of time in site and searching for more noteworthy guide much like your distributed. Vanquish fat reduction

This is a great article thanks for sharing this informative information. HD mxf files conversion software mxf converter | pdf conversion to Office pdf to word fr

when you drop a database and MySQL deletes the folder it will also remove the metadata files.how to block a website

The surprise mode systems discover new plans and items will completely try your subject and charge will commit with search path. buy facebook photo likes

Getting your blood drawn to run lab work feels like taking your car into the mechanic. What is really going on in there? What absolutely must be inspected? The tests have complicated names and do they tell me what is good, bad, or what I need to do next to stay healthy? crp health

What is health Insurance. The main reason why you need an affordable health insurance is to help you plan ahead in case of unexpected costly emergencies. The only way you can accomplish this is to compare prices from most leading insurance companies and also choosing a health insurance plan that best suits you. coimbra health

This is very educational content and written well for a change. It's nice to see that some people still understand how to write a quality post lån

Thanks for the great post.I am grateful for the information given.thanks for your sharing! makeup

I’m extremely glad to see such fantastic information being shared freely out there.garcinia cambogia extract

When looking to buy natural health supplements, chances are you'll find your options are limited in local stores. Online, there are many possibilities. However, an important question is -- how to know which natural tonics for health and wellness are legitimate ones that provide specific health benefits? broad health

A debt of gratitude is in order regarding the instructive written work. Would see any problems with overhauling some great tips about it.twitter.com

When you are planning a wedding, it pays to keep your eyes and ears open. Potential sources of inspiration are everywhere, and you never know when you might stumble across an idea that would be perfect for your wedding. That is why brides in the know are careful to keep up with the latest wedding news. all consuming news

You certainly know how to bring an issue to light and make it essential. Henri

The being of this position is that there are over numerous agents to lobby the consequence. bad credit car financing

this could be one particular of the most supportive online diaries We have ever meet up over on this subject. robotic vacuum

Many businesses periodically send newsletters by postal mail to keep subscribers current on their affairs or industry-related news, for the purpose of maintaining a loyal customer base to whom they can market additional products or services. 7d news

Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work.
https://www.rebelmouse.com/getthegirlcodereviews/

I agree with you it should not have taken that long for them to fix the issue.Best tax lawyer reviews

Wow! this is a estimable novels. It is delightful to own the cause to download it.Bunions

The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface.
http://slideshare.net/gfasreviews/gfas-review-girlfriend-activation-system-obsession-story

The simple were being variety shattered your ancient profession they will formulated. The item absolutely made think to discover them the good qualities. Zakat

Now i am shopping anterior to assist heterogeneous large functionality caused by any person. online slot machines

I think I am quite troubled about this technological getting together with and I want to know the reviews of this getting together with.Ibulimia tips

This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It is the old what goes around comes around routine.
dr. david the desire system ebook reviews

I would like to say that this blog really convinced me to do it! Thanks, very good post.
purchase now

I am very happy to discover your post as it will become on top in my collection of favorite blogs to visit.

fun trick photography

I scan this composition. I reason You put a many of force to cause this column.a board signs

You yes distinguish how to guard a lecturer entertained. I actually enjoyed what you had to talkbuy instagram likes

The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface.
rebelmouse

Thanks for the nice blog. It was very useful for me. I'm happy I found this blog. Thank you for sharing with us,I too always learn something new from your post.
http://www.textyourexback2.com/

Very good points you wrote here..Great stuff...I think you've made some truly interesting points.Keep up the good work.
goseoservice

I can set up my new idea from this post. It gives in depth information. Thanks for this valuable information for all,..

credit card reader

I discovered this real estate market news website, OC Housing News. The news and housing analysis on this site is first-rate, the news stories are entertaining and insightful, and the many user guides are extremely valuable. I endorse seeing this website to take in the great resources available.
Orange County Housing News

Review writing is one of the most difficult things in the field. If you are also looking for someone who could help you in this regard, we can made your search easy. You must visit topreview4you.com and analyse their material that will ensure you about the quality work provided by them.

This is a good post. This post gives truly quality information. I’m definitely going to look into it. Really very useful tips are provided here. Thank you so much. Keep up the good works.
https://www.rebelmouse.com/textyourexbackreview/

Great Article it its really informative and innovative keep us posted with new updates. its was really valuable. thanks a lot.
click here to read more at rebelmouse

These are assembles of much functional info that choose be of huge custom for me in hereafter
Bless you for creating costly announce about the dependent. fire resistant board

The information you have posted is very useful. The sites you have referred was good. Thanks for sharing...

http://rebelmouse.com

Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work.
https://www.rebelmouse.com/the3weekdiet/

I'll take note of this regarding upcoming reference point along with refer it in order to my friends. Extra capacity to your blog. IM Conference

it was a wonderful chance to visit this kind of site and I am happy to know. thank you so much for giving us a chance to have this opportunity..
Top Wholesaler List

Pages