Thread

Hi all,

I’m trying to backup mySQL databases (MAMP Pro) on the Mac but the only way seems to be to go into phpMyAdmin and export each one individually.

Is there a way of just backing up a folder(s) that contains all this stuff including program settings?

I can’t find any details on the MAMP forums.

Everything is stored in the MAMP directory (not MAMP Pro directory) in your Applications directory. So, MAMP > db > mysql will have the data for your databases. I would just back up the entire MAMP applications directory.

Excellent thanks! Why this isn’t mentioned anywhere is a mystery to me.

you have to be very careful doing this. If ANY of the tables are open when you copy the db files you’ll corrupt the table. the best way to do this would be to use the command line and export the databases that way.

My guess is it’s not mentioned anywhere because it’s a really bad way to backup databases

You can use the following to dump all your databases and save all the hassle of using phpmyadmin. Also, using the—lock-tables option you won’t corrupt them.

mysqldump [options]—all-databases

You can also write a short script that will backup all your databases, then just cron it

#!/bin/sh
date=`date -I`
mysqldump --opt --all-databases | bzip2 -c > /path/to/yourbackup-$date.sql.bz2

Basic single db dump:
mysql—user=username—password database < dumpfile.sql

Or just use the PHPMyAdmin part of MAMP and then just back up your htdocs folder instead.

Best not to mess with all the files that MAMP creates unless you really know what you are doing. It can become very messy very quickly. Trust me I’ve been there! 😉

Best wishes,

Mark

You can also write a short script that will backup all your databases, then just cron it

#!/bin/sh
date=`date -I`
mysqldump --opt --all-databases | bzip2 -c > /path/to/yourbackup-$date.sql.bz2

 

This is excellent thank you 😊

Now how do you write a cron and where do I write the above code?

Unfortunately i’m not familiar with the environment you’re referring to. I think someone that knows MAMP should answer this. A cron is just a job that schedules running scripts or events and specific times. Since i don’t know your environment I can’t really answer.

As for the script itself, you can just paste that into a file on your server and set it to executable.

You can still use the regular crontab in leopard, but there are native alternatives as well.  This is a pretty nice primer on how to use those.

The script would then go into the directory you specified in the local_periodic setting, with executable permissions (sudo chmod a+x filename).

One possible gotcha might be the socket.  You may need to add that (/Applications/MAMP/tmp/mysql/mysql.sock) if it can’t find it by itself:

mysqldump -S /Applications/MAMP/tmp/mysql/mysql.sock --opt --all-databases | bzip2 -c > /path/to/yourbackup-$date.sql.bz2

Steven if you go to :

http://localhost:8888/MAMP and then click on PHPMyAdmin at the top of the screen and then on the page that comes up click on Export on the right hand pane you will see the familiar database backup pane.

You can use this to backup all the databases at the same time if you wish. Personally myself I always back them up one at a time as it is (in my mind) easier that way and also better if you need to then use the export on a site online.

Best wishes,

Mark

I do the Mark method with phpmyadmin, always work BUT! I dont know which options check or not… I always use (see the screenshot attached) default options more Add Drop Table but I really dont know which check or not, anybody knows?

I see to this great app http://www.mysqldumper.net/ but I never use it so… I dont know but looks great!

they say:

What is MySQLDumper ?
MySQLDumper is a PHP and Perl based tool for backing up MySQL databases. You can easily dump your data into a backup file and - if needed - restore it. It is especially suited for shared hosting webspaces, where you don’t have shell access. MySQLDumper is an open source project and released under the GNU-license.

The problem …
A PHP script has a maximum execution time that is usually set to 30 seconds on most server installations. A script running longer than this limit will simply stop working. This behavior makes backing up large databases impossible. Maybe you already had this specific problem when using other tools.

Try out Sypex Dumper for by far the fastest MySQL backup application I’ve found so far.

A database that used to take around 40 minutes using any other backup method only takes around 14 seconds with this backup programme!!

Best wishes,

Mark

thanks mark I try it, by the way I import the database that I export with phpmyadmin with default settings and everything works perfect, I dont have issues.