Table of Contents

• Database Backup with PHPMyAdmin
• Execute a database backup query from PHP file
• Run mysqldump using system() function

---

Updated 7 December 2007

There are at least three ways to backup your MySQL Database :

1. Use phpMyAdmin to do the backup.
2. Execute a database backup query from PHP file.
3. Run mysqldump using system() function.

Database Backup with PHPMyAdmin

These instructions are written for PHPMyAdmin, a very common MySQL database management tool included with most hosting services using PHP and MySQL. Your database management tool may differ, but the steps should be very similar.

For instructions on how to back up a database with other PHP admin tools, please see

• After logging in, select your database, then click the Export tab.

1. In the Export section, confirm that all tables are selected, then select the SQL radio button.
2. In the SQL options, select:
   • Structure
     • Add AUTO_INCREMENT value
     • Enclose table and file names with backquotes
   • Data
     • Complete inserts
     • Extended inserts
     • Export type : INSERT
3. Mark the Save as file checkbox.
4. Choose a compression file type.
5. Click Go.
6. Download the compressed file to your hard drive.

After downloading the file, please verify it before modifying the original database.

To do this:

1. On your hosting server, create a new database on your hosting server, if allowed. (If you are only allotted one database per account, create a new database locally (on your computer) by using WAMP for Windows, MAMP for Mac, or LAMP) for Linux.)
2. Select the newly created database.
   • Please note: PHPMyAdmin cannot import files larger than 2 MB. If your compressed database backup exceeds 2 MB, to restore the database, use your compression file archiver program (we recommend the free 7-Zip) to unzip the backup SQL database. Then you can split it into chunks of 2 MB using a text-splitter program. When restoring the database via PHPMyAdmin, simply import it piece by piece.
3. Click the Import tab.
4. Browse and select your backup database copy.
5. Select the UTF8 character set.
6. Select SQL as import format.
7. Click Go. The import process is complete.
8. In the PrestaShop Back Office > Tools > Database Configuration, change the database name to the new one.

Execute a database backup query from PHP file

Below is an example of using SELECT INTO OUTFILE query for creating table backup :

<?php
include ‘config.php’;
include ‘opendb.php’;
;
$tableName = ‘mypet’;
$backupFile = ‘backup/mypet.sql’;
$query = “SELECT * INTO OUTFILE ‘$backupFile’ FROM $tableName”;
$result = mysql_query($query);
;
include ‘closedb.php’;
?>

To restore the backup you just need to run LOAD DATA INFILE query like this :

<?php
include ‘config.php’;
include ‘opendb.php’;
;
$tableName = ‘mypet’;
$backupFile = ‘mypet.sql’;
$query = “LOAD DATA INFILE ‘backupFile’ INTO TABLE $tableName”;
$result = mysql_query($query);
;
include ‘closedb.php’;
?>

It’s a good idea to name the backup file as tablename.sql so you’ll know from which table the backup file is taken.

Run mysqldump using system() function

The system() function is used to execute an external program. Because MySQL already have built in tool for creating MySQL database backup (mysqldump) let’s use it from our PHP script

<?php
include ‘config.php’;
include ‘opendb.php’;
;
$backupFile = $dbname . date(“Y-m-d-H-i-s”) . ‘.gz’;
$command = “mysqldump —opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile”;
system($command);
include ‘closedb.php’;
?>