«
»

Backing Up Your Zen Cart Database

You may think that taking your own database backups is unnecessary, your website host does it for you. The backup that your host takes is for their benefit though, not yours. They will usually take a full server backup rather than backing up individual databases. Should the database server go up in flames your host’s backup will allow them to quickly restore the server. Restoring an individual database from a full server backup is tricky and your host may refuse to do it or charge you substantial amounts for the work.

Some website hosts provide an additional service, usually at a price, were they will take a daily individual backup of your database and restore it for you on request. For peace of mind this is probably a service worth considering. There are however still times when you want to take you own backup at a time that is convenient to you.

Many Zen Cart modules will advise you to take a backup of your database as the first step in their installation instructions. If you mess up the module’s installation do you really want to have to restore from the previous days backup that your host took? Better to restore from the backup that was taken just a few minutes ago. In this article three methods of backing up and restoring your database are discussed along with the pro and cons of each.

Backup MySQL Plugin

For convenience you can’t beat the Backup MySQL Plugin available from the Zen Cart site’s Free Add-Ons section. Once installed this module allows you to backup and restore your database from within the Zen Cart admin.

Doing a Backup with the Backup MySQL Plugin

  1. Log in to your Zen Cart admin.
  2. Click on the ‘Tools’ menu then ‘Database Backup – MySQL’.
  3. Click the ‘Backup’ button.
  4. Select whether you want ‘No Compression (Pure SQL)’ or ‘Use GZIP’. Using GZIP compression will greatly reduced the backup file size.
  5. If you want to download the backup file directly to your computer tick the ‘Download without storing on server’ box. If you leave the box blank the file will be saved to your web server in the ‘/admin/backups/’ directory. You can always download a copy via FTP.
  6. Click the ‘Backup’ button.
  7. The database backup will be taken. If you ticked the ‘Download without storing on server’ box you will prompted to save the backup file to your computer. Otherwise, the backup file will appear in the list of available backups.

Restoring a backup with the Backup MySQL Plugin

  1. If the backup file you wish to restore is held on your computer,  FTP it to your web server and put it in your ‘/admin/backups/’ directory.
  2. Log in to your Zen Cart admin.
  3. Click on the ‘Tools’ menu then ‘Database Backup – MySQL’.
  4. Select the backup you wish to restore from by clicking on it in the list of backup files.
  5. Click the ‘Restore’ button in the right hand side panel. (Not the restore button under the file list.)
  6. Double check you are about to restore the correct file.
  7. Click the ‘Restore’ button in the right hand side panel again.
  8. The restore will take place.
  9. If you restored from a GZIP compressed backup file an uncompressed .sql file will appear in the backup file list. These can be very big so it is best to delete it from your web server. To delete the file, click on it to select it, press the ‘Delete’ button, double check you have the right file then press the ‘Delete button again.

Pros:

  • Is integrated into Zen Cart’s admin so is very convenient.
  • Does fast backups and restores.

Cons:

  • Only allows you to backup and restore the entire database. If you have other applications using the same database, such as a forum or blog, that data will also be backed up and restored.
  • Uses the PHP exec() function so will not work with some hosts who disable this functionality.

phpMyAdmin

phpMyAdmin is a popular tool for managing MySQL databases. A version of it comes bundled with just about every hosting package. You should be able to find a link to it somewhere in you website’s hosting control panel.

Backing up using phpMyAdmin

  1. Log into phpMyAdmin using your MySQL database username and password. If you have forgotten these, they are in the /includes/configure.php file in your Zen Cart installation.
  2. Click the name of your Zen Cart database in the left hand side panel.  If you are unsure of your database name you can find the it in the /includes/configure.php file.
  3. The next screen will show you all the tables inside your Zen Cart database. Ignore those, and click the ‘Export’ tab on the top set of tabs.
  4. In the top left hand box in the Export pane is a list of all the tables in the database. If you have other programs that use the database, then choose only those tables that correspond to your Zen Cart install. They will be the ones that start with ‘zen_’ or whatever Table Prefix you chose during installation. If you only have Zen Cart  installed, leave it as is (or click ‘Select All’ if you changed the selection).
  5. In the Export panel ensure that SQL is checked, this is the default selection.
  6. In the SQL section make sure the following boxes are ticked… (other settings should be left as they are)
    • Structure
    • Add DROP TABLE / VIEW / PROCEDURE / FUNCTION
    • Add AUTO_INCREMENT value
    • Enclose table and field names with backquotes
    • Data
    • Complete inserts
    • Extended inserts
    • Use hexadecimal for BLOB
  7. Tick the ‘Save as file’ option and leave the template name as is.
  8. Select gzipped compression.
  9. Click the ‘Go’ button and you will be prompted to download a file. Save the file to a safe place on your computer.

Restoring a backup using phpMyAdmin

  1. Log into phpMyAdmin using your MySQL database username and password. If you have forgotten these, they are in the /includes/configure.php file in your Zen Cart installation.
  2. Click the name of your Zen Cart database in the left hand side panel.  If you are unsure of your database name you can find it in the /includes/configure.php file.
  3. The next screen will show you all the tables inside your Zen Cart database. Ignore those, and click the ‘Import’ tab on the top set of tabs.
  4. Click the ‘Choose File’ button and select your backup file.
  5. Click the ‘Go’ button. Your backup file will be uploaded to the server and restored.

Pros:

  • Readily available on all good hosting packages.
  • Allows backups of specific tables.

Cons:

  • Can be awkward to use.
  • Restore is limited to maximum upload file size allowed by host (can be as low as 4MB).
  • Does not always compress backup files as well as it should.

phpMyBackupPro

phpMyBackupPro is a free web based MySQL backup and restore application. You can download it from phpMyBackupPro.net . Once you have downloaded it you will need to install it on your website. Full installation instructions are included in the downloaded zip file.

Backing up using phpMyBackupPro

  1. Log into phpMyBackupPro using your MySQL database username and password. If you have forgotten these, they are in the /includes/configure.php file in your Zen Cart installation.
  2. Click ‘Backup’ on the top row of links.
  3. If you have more than one database set up, select your Zen Cart database from the ‘Select databases to backup:’ list.
  4. Ensure the ‘export tables’, ‘export data’ and ‘add drop table’ check boxes are ticked.
  5. Select ‘gzip’ compression.
  6. Click on the ‘Backup’ button.
  7. A backup of the database will then be created and stored in the phpMyBackupPro backups directory. You can download the backup file to your computer either using FTP or by clicking on ‘Import’ and then clicking on ‘Download’ next to the name of the backup file.

Restoring a backup using phpMyBackupPro

  1. If the backup file you wish to restore is held on your computer,  FTP it to your web server and put it in your phpMyBackupPro backups directory.
  2. Log into phpMyBackupPro using your MySQL database username and password. If you have forgotten these, they are in the /includes/configure.php file in your Zen Cart installation.
  3. Click ‘Import’ on the top row of links.
  4. A list of available backup files will be show with a set of links to the right of each backup file name.
  5. Click ‘(fragmented)’ next to the ‘import’ link on the line for the database backup you wish to restore.
  6. A new window will pop up in which progress of the restore will be shown.
  7. Once it has reported the end of file has been reached the restore is complete and you can close the pop up window.

Pros:

  • Fairly easy to use.
  • Allows you to create scheduled backups.
  • Tables can be excluded from the backup.
  • Doesn’t use the PHP exec() function so will work where the Backup MySQL Plugin will not.

Cons:

  • Not easy to select which tables to backup and which to exclude.
  • Doesn’t work properly with all host configurations. Some people have reported more success with the older v1.8 rather than the newer v2.1.

Hopefully theses instructions will make it easy for you to take backups of your Zen Cart database. Remember that you should also regularly use FTP to download a copy of all the Zen Cart files. Having a recent backup of your database and Zen Cart files will allow you to quickly get up and running if problems occur.

Posted on Thursday, October 8th, 2009

No Comments »

No comments yet.

Leave a comment