Back ups are important. Like brushing your teeth, it’s something you should do routinely. And, as you’d brush before important events, like going out on a date, so should you too backup before upgrading software.

I’m going to show you how to use phpMyAdmin to back up and restore MySQL databases. In my next post, I’ll show you how to graduate to doing the same thing using the command line.

I use MAMP to develop websites locally. I’ve written about this before here. The examples I’ll be showing come from the phpMyAdmin packaged with MAMP, but apply just as well to any other environment.

phpMyAdmin without the MAMP frame

First things first. Let’s work so we can see what we’re doing.I’m going to assume that you’re able to download and install MAMP or MAMP Pro on your own. The phpMyAdmin URL that you from the MAMP default webstart page gets you to the URL:

http://localhost/MAMP/frame.php?src=%2FphpMyAdmin%2F%3Flang%3Den-iso-8859-1&language=English

which looks gives you a framed version of the phpMyAdmin. The framing takes up a lot of screen real estate.

phpMyAdmin widow with the MAMP framing

Instead, use http://localhost/phpMyAdmin and you’ll get something that looks much more pleasing:

phpMyAdmin window without a frame

dumping a database with phpMyAdmin

Select a database from the drop-down menu on the left hand side of phpMyAdmin. Or, Navigate to “home” by clicking the little house under the phpMyAdmin logo in the top left and hit the blue Databases link in the longish list there.

phpmyadmin_home

Ok, so now we see the tables in our database. Press the Export tab at the top. We’re going to export this database.

phpmyadmin_table

Let’s review the options you should select to get nice, compact, and legible SQL.

phpmyadmin2102_exportdb3

The first column, in the Export fieldset, is pretty straight forward. By default all the tables in your database will be selected. SQL is the format you want too.

“Disable foreign key checks” isn’t always necessary. I do a lot of CiviCRM work. Restoring a CiviCRM database will fail unless this option is enabled in your export.

“Add DROP TABLE / DROP VIEW” will delete your table on import before recreating it.

Extended inserts and Complete insterts gives you a smaller file and more compacted, easy to read view.

Click “Save as file” and use “gzipped” compression for the smallest files.

In the File name template, I add %D, which spits out today’s date. I add a little hypen before to seperate it from the database name and MAMP after, so I know it was a local dump and not from my live server. This format would give me a file like: mysql-06_02_09_MAMP.sql.gz

To restore your databse, click on the import tab, click the browse button, select your file, press go. I’ll spare you the screenshot.

Add to Yahoo Add to Google Save to Del.icio.us Digg IT! Live Bookmarks!

6 Responses to “how to back up databases using phpMyAdmin”  

  1. 1 Professional Drupal Themes

    thanks, this was really helpful – we’ve done lots of drupal themes and many clients ask how to back up their databases..

  2. 2 Web tyrant

    very useful.. get more leads.. but make sure you back up your site!!!

  3. 3 โปรโมทเว็บฟรี

    Thanks very simple.

  4. 4 aljuk

    My own experience with MAMP and phpmyadmin with regard to saving compressed database files is that all types of compression work fine when restoring an already existent database, but when I’ve migrated to a production server (ie. a new database instance) only a raw sql file or a zipped file work.

    I tested that back on my MAMP setup, dropping and recreating a new database for each file type, and sure enough only the raw sql file, and the zipped file would result in the original database there too. In both instances the other compression types returned databases with only a small number of the original tables (the first 16 or so).

    Really nice tip about the local phpmyadmin url!

  5. 5 Andrew

    Aljuk,

    It is possible that you do not have enough memory allocated to MySQL to complete the dump.
    Try editing the MySQL my.cnf configuration file for MAMP. You can do this from the file menu in the application.
    These are the values that I use:

    [mysqld]
    #port = 9999
    socket = /Applications/MAMP/tmp/mysql/mysql.sock
    skip-locking
    key_buffer = 24M #16M
    max_allowed_packet = 48M #1M
    table_cache = 256 #64
    sort_buffer_size = 2048K #512K
    net_buffer_length = 32K #8K
    read_buffer_size = 1024K #256K
    read_rnd_buffer_size = 2880K #512K
    myisam_sort_buffer_size = 72M #8M

    and later:

    [mysqldump]
    quick
    max_allowed_packet = 128M #16M

    If that doesn’t work, increase your PHP memory limit. It is possible that the dump is timing out. These are the values I use in php.ini for php 5:

    ;;;;;;;;;;;;;;;;;;;
    ; Resource Limits ;
    ;;;;;;;;;;;;;;;;;;;
    max_execution_time = 600 ;30 ; Maximum execution time of each script, in seconds
    max_input_time = 300 ;60 ; Maximum amount of time each script may spend parsing request data
    memory_limit = 348M ;8M ; Maximum amount of memory a script may consume (8MB)

    don’t forget to restart MAMP after you’ve edited the document.

  6. 6 Brewsta

    This is really interesting, You are a very skilled blogger. I’ve joined your feed and stay up for in quest of more of your great post. Also, I have shared your website in my social networks!


ABOUT

This is the blog of Andrew Mallis, a Toronto-born, San Francisco-based polymedia artist. I work in new(er) media with code, photography and electronics, and in traditional media by writing, drawing & painting.

Categories