---

---

February 12, 2013

Backup Remote MySQL Databases

With the growing popularity of web apps backing up the databases they use is increasing in importance. While a good web host will provide backup services, the responsibility for backing up still rests with the website owner/operator. For security I have my web apps running locally on my laptop and on my remote server. This requires synchronizing the databases. It can be done manually with the help of phpMyAdmin but it is cumbersome, to say the least. It can be done with a Cron-job but sending the backup file as an email attachment is not secure. Besides, I could not find a way to back up just selected tables instead of the whole database. I decided to incorporate a backup feature into my web app with a MySQLbackup php class. For now I'm using phpMyAdmin for the restore function but I expect to write a MySQLrestore php class sometime in the future.

The interface for MySQLbackup is as follows: The __construct() function creates the .sql backup file and the download() function gives you the choice to download the file as is or to compress it into a zip file:

interface Backup {

    // create the backup file (filename.sql)
    public function __construct($username, 
                                $password, 
                                $database, 
                                $tables = "", 
                                $host = "localhost", 
                                $path2files = "");
                                
    // download the backup file
    // as filename.sql
    // or compressed as filename.zip (default)
    public function download($compression = "zip");

}

To use the class you need to create the backup object which places the backup file in the desired directory. The first three parameters are mandatory. If $tables is missing or empty all the database tables will be included in the backup file. The default for $host is 'localhost'. The default for $path2files is the root directory '/home/user/'.

$backup = new Backup( string $username, 
                      string $password, 
                      string $database name [,
                      mixed  $tables [,         // default empty string
                      string $host [,           // default 'localhost'
                      string $path2files ]]] ); // default '/home/user/'

To download the backup file use the download() function which has one optional parameter. If 'zip' or omitted the download will be compressed as a zip file. If 'sql' the download will be a text .sql file.

$backup->download( [ 'sql' | 'zip' ] );         // default 'zip'

Note: The MySQLbackup class uses the phpErrorReporter.php functions which are activated as an auto-prepend-file in htaccess.

Enjoy

Denny Schlesinger


Download Backup (2.0 KB)
The Ultimate PHP Error Reporter

---