Article
The PHP Anthology Volume I, Chapter 3 - PHP and MySQL
How do I back up my database?
The bigger a database becomes, the more nerve wracking it can be not to have a backup of the data it contains. What if your server crashes and everything is lost? Thankfully, MySQL comes with two alternatives: a command line utility called mysqldump, and a query syntax for backing up tables.
Here's how you can export the contents of a database from the command line with mysqldump:
mysqldump -uharryf -psecret sitepoint > sitepoint.sql
This command will log in to MySQL as user "harryf" (-uharryf) with the password "secret" (-psecret) and output the contents of the sitepoint database to a file called sitepoint.sql. The contents of sitepoint.sql will be a series of queries that can be run against MySQL, perhaps using the mysql utility to perform the reverse operation from the command line:
mysql -uharryf -psecret sitepoint < sitepoint.sql
Using the PHP function system, you can execute the above command from within a PHP script (this requires you to be logged in and able to execute PHP scripts from the command line). The following class puts all this together in a handy PHP form that you can use to keep regular backups of your site.
Example 3.27. Database/MySQLDump.php (in SPLIB)
/**
* MySQLDump Class<br />
* Backs up a database, creating a file for each day of the week,
* using the mysqldump utility.<br />
* Can compress backup file with gzip of bzip2<br />
* Intended for command line execution in conjunction with
* cron<br />
* Requires the user executing the script has permission to execute
* mysqldump.
* <code>
* $mysqlDump = new MySQLDump('harryf', 'secret', 'sitepoint',
* '/backups');
* $mysqlDump->backup();
* </code>
* @access public
* @package SPLIB
*/
class MySQLDump {
/**
* The backup command to execute
* @private
* @var string
*/
var $cmd;
/**
* MySQLDump constructor
* @param string dbUser (MySQL User Name)
* @param string dbPass (MySQL User Password)
* @param string dbName (Database to select)
* @param string dest (Full dest. directory for backup file)
* @param string zip (Zip type; gz - gzip [default], bz2 - bzip)
* @access public
*/
function MySQLDump($dbUser, $dbPass, $dbName, $dest,
$zip = 'gz')
{
$zip_util = array('gz'=>'gzip','bz2'=>'bzip2');
if (array_key_exists($zip, $zip_util)) {
$fname = $dbName . '.' . date("w") . '.sql.' . $zip;
$this->cmd = 'mysqldump -u' . $dbUser . ' -p' . $dbPass .
' ' . $dbName . '| ' . $zip_util[$zip] . ' >' .
$dest . '/' . $fname;
} else {
$fname = $dbName . '.' . date("w") . '.sql';
$this->cmd = 'mysqldump -u' . $dbUser . ' -p' . $dbPass .
' ' . $dbName . ' >' . $dest . '/' . $fname;
}
}
/**
* Runs the constructed command
* @access public
* @return void
*/
function backup()
{
system($this->cmd, $error);
if ($error) {
trigger_error('Backup failed: ' . $error);
}
}
}
Note
The MySQLDump class makes some assumptions about your operating system configuration. It assumes the mysqldump utility is available in the path of the user that executes this script. If the gzip or bzip2 utilities are used, they also need to be present in the path of the user who executes this script. bzip2 provides better compression than gzip, helping save disk space.
The following code demonstrates how this class can be used:
Example 3.28. 20.php
<?php
// Include the MySQLDump class
require_once 'Database/MySQLDump.php';
$dbUser = 'harryf'; // db User
$dbPass = 'secret'; // db User Password
$dbName = 'sitepoint'; // db name
$dest = '/home/harryf/backups'; // Path to directory
$zip = 'bz2'; // ZIP utility to compress with
// Instantiate MySQLDump
$mysqlDump = new MySQLDump($dbUser, $dbPass, $dbName, $dest,
$zip);
// Perform the backup
$mysqlDump->backup();
?>
The $dest variable specifies the path to the directory in which the backup file should be placed. The filename that's created will be in this format:
databaseName.dayOfWeek.sql.zipExtension
For example:
sitepoint.1.sql.bz2
The dayOfWeek element can be any number from 0 to 6 (0 being Sunday and 6 being Saturday). This provides a weekly "rolling" backup, the files for the following week overwriting those from the previous week. This should provide adequate backups, giving you a week to discover any serious problems, and without requiring excessive disk space to store the files.
The use of a ZIP utility is optional. If the value of the $zip variable is not one of gz or bz2, then no compression will be made, although for large databases it's obviously a good idea to use a compression tool to minimize the amount of disk space required.
This class is intended for use with the crontab utility, which is a Unix feature that allows you to execute scripts on a regular (for example, daily) basis.
MySQL also provides the SQL statements BACKUP TABLE and RESTORE TABLE, which allow you to copy the contents of a table to another location on your file system. Unlike the mysqldump utility, tables backed up in this way preserve their original format (which is not human-readable) but this mechanism does not require access to a command line utility, so it could be executed via a Web page.
The general syntax for these statements is as follows:
BACKUP TABLE tbl_name[, tbl_name ...]
TO '/path/to/backup/directory'
RESTORE TABLE tbl_name[, tbl_name ...]
FROM '/path/to/backup/directory'
Note that on Windows systems it's best to specify paths using forward slashes (e.g. C:/backups).
By combining these with some of the "introspection" statements MySQL provides, we can backup our database using the MySQL class we built in this chapter. To start with, we need to get a list of tables in the database, which is quickly achieved using the SHOW TABLES query syntax:
Example 3.29. 21.php (excerpt)
<?php
// Include the MySQL class
require_once 'Database/MySQL.php';
$host = 'localhost'; // Hostname of MySQL server
$dbUser = 'harryf'; // Username for MySQL
$dbPass = 'secret'; // Password for user
$dbName = 'sitepoint'; // Database name
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);
// A query to show the tables in the database
$sql = "SHOW TABLES FROM sitepoint";
// Execute query
$result = $db->query($sql);
We also store the number of rows returned by this query to help us format the string we'll use to build the BACKUP query:
Example 3.30. 21.php (excerpt)
// Get the number of tables found
$numTables = $result->size();
Next, we loop through the results, building a comma-separated list of tables to back up:
Example 3.31. 21.php (excerpt)
// Build a string of table names
$tables = '';
$i = 1;
while ($table = $result->fetch()) {
$tables .= $table['Tables_in_sitepoint'];
if ($i < $numTables) {
$tables .= ', ';
}
$i++;
}
Finally, we use the BACKUP TABLE query syntax to copy the tables to a directory of our choice (to which, of course, the script that executes this query needs permission to write):
Example 3.32. 21.php (excerpt)
// Build the backup query
$sql = "BACKUP TABLE $tables TO '/home/harryf/backup'";
// Perform the query
$db->query($sql);
if (!$db->isError()) {
echo 'Backup succeeded';
} else {
echo 'Backup failed';
}
?>