Here is another article that deals with this subject.

Backup Process with phpMyAdmin

The screen shots here are from phpMyAdmin 2.11.4, which I have on my local computer. Web hosts using different versions should look similar. They certainly produce exchangeable results.

  1. Log into phpMyAdmin on your server.
  2. Select the name of your database - or your Drupal database if you have several databases.


  3. The next screen will show you all the tables inside your Drupal database. Ignore those, and click the "Export" tab on the top set of tabs.


  4. Look at the left box at the top of the Export section. All the tables in the database you selected are in that box.
  • If you have other programs that use the database, then choose only those tables that correspond to your Drupal installation.
  • If you only have Drupal installed, in the left column, click "Select All."
  • Ensure that the SQL button is selected too.
  • In the Structure section tick the following boxes: Structure, "Add DROP TABLE," "Add IF NOT EXISTS," and "Enclose table and field names with backquotes."
  • In the DATA section leave the boxes inside this section unticked, but make sure to keep the check box next to the "DATA" heading checked. If any of the boxes is pre-checked, uncheck them.
  • Tick the "Save as file" option.
  • For now, select "None" for compression.
  • Now click "Go" and you should be prompted for a file to download. Save the file to your computer. Depending on the database size, this may take a few moments.

  • You have now backed up your database! (Not so bad, was it?)

    Once that download is complete, you may check the "zipped" option, click "Go", and download the next file. If you want, you can download a backup in each of the compression formats. Your choice.

    Potential Issues You May Encounter

    1. PHP Limits Size of File Upload
      In particular when you are restoring the database via upload this limitation may make PHPmyadmin difficult to use. Possible solutions:
      • Use one of the compression options above.
      • Modifying your php settings to allow larger file uploads may also be possible, but discussion of that is beyond the scope of this document
      • Deleting the cache and watchdog table records (leaving the tables) before doing the backup can reduce the file size. [need reference for how to]
      • Splitting the download up by only selecting specific tables may also help

    Comments

    metastate’s picture

    Based on this discussion http://drupal.org/node/581226 it says the "Add AUTO_INCREMENT value" under Structure should also be checked off when doing a backup. But this D5 discussion http://drupal.org/node/183193 says that Drupal doesn't use auto_increment. Has that changed for D6?

    Also, the directions in this handbook page http://drupal.org/node/81993 say to check off "Complete inserts" and "Use hexadecimal for binary fields" under Data. I've always left everything unchecked under Data though, so not sure which is the proper way to be doing it.

    And then finally, this handbook page http://drupal.org/node/53479 also has slightly different backup instructions ("Add AUTO_INCREMENT value" also missing).

    It would be great if we could consolidate all these conflicting instructions into one accurate page. WordPress has a great overview of backup procedures in their codex: http://codex.wordpress.org/Backing_Up_Your_Database A single page like this for Drupal would be great.

    I'm happy to help but not an expert in this area. I'll try to follow up with a list of what I think the right phpMyAdmin settings are once I've done more research.

    (Oh - found another page of instructions - http://drupal.org/upgrade/backing-your-site-gui)

    NancyDru’s picture

    Yes, D6 uses auto increments, thank goodness. Some contribs on D5 do as well.

    Unfortunately PhpMyAdmin changes its default checkboxes often, so yes we could use a standardized page. Your references are a great start. Anyone with a Drupal.org account can edit pages (well, almost all pages).

    If you'd rather just get a page started, I would be happy to clean it up and publish it.

    metastate’s picture

    I am thinking the best place for this info would probably be the Administration Guide handbook, under "Backing up a site": http://drupal.org/node/22281 The description of that handbook says that's where you can find info on how to perform backups and upgrades.

    Maybe I can just work on revising the "Backing up your site (GUI)" page? I could incorporate info from the various pages I mentioned above as best I can.

    Good to know about auto-increments. I now need to check out a few databases that have possibly been throwing errors because of this!

    Any idea on whether it matters if anything is checked under Data? That's what I'm looking into next. Particularly extended inserts, complete inserts and use hexadecimal. Some instructions say to check off nothing, some say to check off one or more of these.

    NancyDru’s picture

    PhpMyAdmin 2.11.2 automatically checks "Complete inserts," "Extended inserts, and "Use hexadecimal for BLOB". So I would say you probably do want to use those.

    Iritscen’s picture

    It doesn't seem that any of the pages I've looked at have been updated since this discussion started. As a less savvy Drupal admin, I'm not entirely comfortable with all these conflicting directions. I would hate to think that my backups are not going to be useable in the case of a server meltdown, so it would be nice to have one backup guide that is definitive.

    NancyDru’s picture

    It's not so much that the directions conflict as that there are several ways that are right. Some of it depends on which version of PhpMyAdmin you use. I don't know that one could write a "definitive" guide that would take all those factors into account.

    If you are on a commercial hosting service, check with them on whether or not they provide backups. Those backups are likely to be the most reliable as they operate at the disk level, not relying on the database.

    The only problem I have seen with the process I use is that occasionally, the "zip" service fails. In virtually all cases that I have further checked, the database was properly restored and the error occurred as the zip service was closing down.

    There is also a Backup module, if that would make you feel more comfortable.

    Iritscen’s picture

    I see... I thought the crucial matter was what options Drupal expects to see in the database backup if one is restoring from it, not how phpMyAdmin happens to show your options when exporting. But maybe it doesn't break the restore process if an option like AUTO_INCREMENT or "Complete inserts" or 'hex BLOB' is on or off? Is that what you're saying?

    NancyDru’s picture

    Actually, I would think that AUTO-INCREMENT would be important to Drupal 6 (but D5 didn't use it). I can't see that HEX BLOB would be that critical as long as COMPLETE INSERT is active. I have changed PhpMyAdmin releases several times since this was originally written, and then each host has a different version. For the most part, when I do this now, my hands just move from muscle memory; I'll have to pay attention the next time.

    Iritscen’s picture

    Well, I've saved another copy of my db with those options on. Sounds like it can't do any harm, at the very least. Thanks for the responses.

    fehin’s picture

    The interface has changed. Needs updating.