Create MySQL Backup

There are a number of ways to backup a MySQL database. For my local environment I like a quick way to take a backup before trying something new. Rather than go through the cumbersome process of logging into Terminal, navigating to the MAMP /bin directory and then entering a very long command, which I would easily forget, it’s simpler to create a shell script and then invoke it as an Application by using AppleScript.

First, create your shell script by opening a text editor, eg TextWrangler (and not an RTF editor), and type:

#!/bin/bash

On the next line type in the commands you would normally do in Terminal. In my case, I wanted to navigate to the MAMP /bin where mysqldump lives. Then, I wanted to use this to dump a sql file of a specific database within my sites directory.

cd /Applications/MAMP/Library/bin/
./mysqldump -a -uroot -pPASSWORD mydb1 > ../../../../Users/[username]/Sites/Backups/db1.sql;

Finally, close it with

exit

You then save this in a logical place (ie somewhere you can find it!), in my case /Users/[username]/Sites/Backups/scripts/. Next go to Application > Utilities and launch AppeScript Editor. Type the following into the edit pane.

do shell script "/Users/[username]/Sites/Backups/scripts/backup_nsdaukcom_263323_db1.sh"

Save this to either Applications or a sub-directory within Applications. When you change the File Format to Applications before saving.

Once done, all you need to do to take a backup is double-click the new application you’ve created and your previous backup is overwritten with a new one.

3 thoughts on “Create MySQL Backup”

  1. Great job done by author. Information helps me a lot to improve my skills. Thanks for the author for sharing. Please keep posting.

  2. Very good. Almost worked for my Mamp/MySql Problem which is the terrible message. —-Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: YES) when trying to connect— Essentially I’m locked out if my sites db. Thought I might get around this with your clever app, but alas, no. I’m going to try another password change, but my Mamp user table seems to have disappeared. If you know any ways of creating a new localhost table without opening myPHPadmin please send it on. Thanks, Noah

    1. Posting a bit late I know (ahem!). Did you manage to sort this out? I use Navicat Lite to sort out permissions.

Leave a Reply

Your email address will not be published. Required fields are marked *