Tag Archives: phpMyAdmin

WordPress site backup

Whenever I begin using a new development tool, one of my first tasks is to understand how to backup my work and then actually test the backup and restore procedures.  Of course, our web hosting service performs its own regular backups, but I am not content to rely exclusively upon that.  I want to be in control of my own, deliberate backups as well.

The HSphere Control Panel (version 3.5.1) provided by our web hosting service includes a button for launching phpMyAdmin (version 3.5.3), which can be used to generate a backup of the entire MySQL database supporting our WordPress web site.  There is a WordPress Codex page that describes how to use the phpMyAdmin facility, but this documentation is somewhat outdated.  Here are the steps I’ve been using, attempting to reconcile outdated WordPress Codex information with phpMyAdmin 3.5.3:

  1. Launch the HSphere Control Panel for my web site.
  2. Click on the phpMyAdmin icon – this launches in a new browser window.
  3. Select the site’s WordPress database, listed at left on the phpMyAdmin screen.
  4. Click the Export tab.
  5. Choose the option “Custom – display all possible options”.
  6. Under “Output:”, choose “Save output to a file”.
  7. Under “Object creation options, Add statements:”, check the option to “Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement”.
  8. Also under “CREATE TABLE options:”, check the “IF NOT EXISTS” option.
  9. Leaving other options at their default settings, click the Go button.
  10. In the Save As dialog, navigate to the desired local directory and save the generated .SQL file.

The resulting .SQL file is just a type of text file, more readable in WORDPAD than in NOTEPAD, because of line termination issues.  The initial size of this file, uncompressed, was 267KB, i.e. well under 1MB.  As the size of this file increases, I will use one of the available compression options that phpMyAdmin supports, e.g. to produce a much smaller ZIP file.

WordPress supports its own administrative facilities for Export and Import (under the Dashboard’s Tools menu), providing a convenient sort of backup/restore capability, although that is not their primary intent.  These commands are mainly intended for moving content from one site to another, including support of many non-WordPress blogging systems, using a special type of XML file representation.  The resulting files are much more compact (initially just 7KB) and readable than the MySQL database dumps generated by phpMyAdmin, but they constitute a less complete form of backup.  Here’s an excerpt from Export screen about WordPress’ XML file format:

This format, which we call WordPress eXtended RSS or WXR, will contain your posts, pages, comments, custom fields, categories, and tags.

Aside from backing up the site’s WordPress data, there is also the matter of backing up its installed programs, plugins, etc.  While our hosting service handled the installation, and they may take responsibility for WordPress updates, I thought it advisable to make a backup of the entire initial WordPress installation.  This was easy to do via FTP in one fell swoop, although it took a fair amount of time to work its way through the entire WordPress sub-tree, which contained 1,755 files in 118 folders, totaling 13.1 MB.  (With ZIP compression this reduced to 5.49 MB.)  I don’t expect to perform this type of FTP-based backup on a regular basis, but it seems like a good idea to do it again after installing any new WordPress plugins.

Related references: