Using PHP to Back Up MySQL

Russell J.T. Dyer

The importance of backing up data can't be stressed enough, and this also applies to backing up MySQL databases. One of the keys to making regular backups is to automate the process. You could buy a backup program that will copy the data files from a filesystem to archive media. However, for more dependable backups, MySQL would have to be shut down before a backup program were run, which could make MySQL unavailable to users for quite a while. A better alternative is to write a PHP script that will use a utility like mysqldump.

In this article, I will work through the development of a few PHP scripts used for backing up MySQL -- one that will accumulate statistics on the usage of MySQL tables to determine which ones should be backed up daily, another that will make weekly backups of all of the databases on a server, and a third script to perform daily backups based on the statistics from the first.

Table Usage Statistics

When developing a backup script, it's important to decide what you want to back up and how frequently. For the purposes of this article, I will assume that only tables in which the data is changed a few times a day are to be backed up. To determine this information, I will keep a log in MySQL of the usage of tables. To set up a statistics log, I'll create a new database and a table with the mysql client like so:

CREATE DATABASE mysql_stats;
USE mysql_stats;

CREATE TABLE table_stats
  (stat_id INT AUTO_INCREMENT PRIMARY KEY,
  stat_time DATETIME, db_name VARCHAR(50),
  table_name VARCHAR(50),
  file_size INT, file_modify DATE);

#!/usr/bin/php -q
<?
mysql_connect('localhost', 'root', 'password');

$databases = mysql_list_dbs();
$db_count = mysql_num_rows($databases);
$db_ignore = array('mysql','test','mysql_stat');
$today = date('Y-m-d');
$hour = date('h');

for($cnt = 0; $cnt < $db_count; $cnt++) {
  $db = mysql_db_name($databases, $cnt);
  if(in_array($db, $db_ignore)){ continue; }

  $tables = mysql_list_tables($db);
  $tb_count = mysql_num_rows($tables);

  for($c = 0; $c < $tb_count; $c++) {
    $table = mysql_tablename($tables, $c);
    $file_name = "/var/mysql/$db/$table.MYD";

    if(file_exists($file_name)) {
     $file_size = filesize($file_name);
     $file_modify = date('Y-m-d', filemtime($file_name));
     $file_hour = date('h', filemtime($file_name));

    if($file_modify == $today & $file_hour == $hour) {
       $sql_stmnt = "INSERT INTO table_stats
                     (db_name, table_name, stat_time,
                     file_size, file_modify)
                     VALUES('$db', '$table', NOW(),
                     '$file_size', '$file_modify')";

       $results = mysql_db_query('mysql_stats', $sql_stmnt)
                or die(mysql_error());
     }
    }
  }
}

mysql_close();
?>

Using the function file_exists(), PHP tests whether the data file for the table exists. On the first line of this if statement, PHP gets the table's data file size. It then captures the date it was last modified on the next line. The function filemtime() returns Unix time. To reformat that value to the MySQL date format (i.e., yyyy-mm-dd), the function date() is used with the desired format given. Using the same function, we get the hour to compare to the current hour ($hour).

If the modification date is the same as today's date and hour, then PHP will query MySQL to insert the data with mysql_db_query() in which the database name is given as the first argument and the SQL statement as the second. Since this concludes the script, the connection to MySQL is closed. Incidentally, the trailing closing curly braces close the if statements and the embedded for statement in this code excerpt, as well as the for statement from the previous code excerpt.

A Complete Backup

Before going through the script for selectively backing up tables based on statistics collected by the previous script, I'll look at a PHP script that can be used to back up all of the databases in MySQL. Such a script might be set to run once a week by cron. It will back up each database to a separate tar file using mysqldump. With mysqldump, the backups are performed without shutting down MySQL. In addition to backing up the data, this script will also back up the configuration file (my.cnf) and MySQL itself.

To prepare for this script, I'll first create another table in MySQL. This one will be used to log information on each backup. I'll do this from the mysql client:

CREATE TABLE backup_log
  (log_id INT AUTO_INCREMENT PRIMARY KEY,
  backup_date DATETIME, type ENUM('database','table'),
  cycle ENUM('weekly','daily'),
  tar_file VARCHAR(50), file_path VARCHAR(50));

#!/usr/bin/php -q

<?
mysql_connect('localhost', 'root', 'password');

$databases = mysql_list_dbs();
$db_count = mysql_num_rows($databases);

$dir = '/mnt/backup';
$date_prefix = date('Ymd');

for($cnt = 0; $cnt < $db_count; $cnt++) {
  $db = mysql_db_name($databases, $cnt);

  system("mysqldump -u root -ppassword -h localhost
      -x -e -A $db > $dir/$date_prefix-$db.sql");

  $sql_stmnt = "INSERT INTO backup_log
                (backup_date, type, cycle, tar_file, file_path)
                VALUES(NOW(), 'database', 'weekly',
                '$date_prefix-$db.sql', '$dir')";
  mysql_db_query('mysql_stats', $sql_stmnt);
}
mysql_close();

  system("tar cfz mysql.tar.gz /etc/my.cnf /usr/local/mysql
      /usr/lib/mysql /usr/bin/mysql /usr/sbin/mysqld");
?>

Next, an SQL statement is set up to insert information regarding the backup. After closing out the MySQL session, I end the script by creating another tar file containing the MySQL configuration file and all of the various libraries and binaries. Instead of doing this, though, it might be better to just keep a clean copy (from mysql.com) of the installation file that you used to install MySQL. Just be sure to back up my.cnf if you've modified it.

Selective Backups

Which statistics should be kept on databases to determine which tables to back up is debatable and should be determined by the sys admin. As a simple example, I've chosen to determine daily backups based on whether a table's data file is modified at least four times during the day. Based on this policy, I've put together an SQL statement that will check for records where the value of the column file_modify is the same as today's date and where there are more than four records. Let's look at the script, mysql_backup_daily.php, which will be run late each night during a low-traffic time for the fictitious databases:

#!/usr/bin/php -q

<?
mysql_connect('localhost', 'root', 'password');
$dir = '/mnt/backup';

$sql_stmnt = "SELECT db_name, table_name, stat_date
       FROM (
          SELECT db_name, table_name,
          DATE_FORMAT(file_modify, '%Y-%m-%d') AS stat_date,
          COUNT(table_name) AS recs
          FROM table_stats
          WHERE file_modify = CURDATE()
          GROUP BY table_name
          ) AS frequency
       WHERE recs > 4;";

$results = mysql_db_query('mysql_stats', $sql_stmnt);

http://www.unixreview.com/documents/s=8969/ur0407e/

while($row = mysql_fetch_object($results)) {
 $db = $row->db_name;
 $table = $row->table_name;
 system("mysqldump -u root -ppassword -h localhost
     -x -e $db $table > $dir/$db.$table.sql");

 $sql_stmnt = "INSERT INTO backup_log
        (backup_date, type, cycle, tar_file, file_path)
        VALUES(NOW(), 'table', 'daily', '$db.$table.sql',
        '$dir')";
 mysql_db_query('mysql_stats', $sql_stmnt);
}
mysql_close();
?>

Conclusion

To construct a backup system like the one discussed here, you may want to consider the criteria by which you will decide whether a table will be backed up daily. If your databases are small enough and their usage is low, then you may want to perform a complete backup daily regardless of a table's file modification date. The important thing is to back up the data regularly and appropriately.

Russell Dyer is a Perl programmer, a MySQL developer, and a Web designer living and working on a consulting basis in New Orleans. He is also an adjunct instructor at a technical college where he teaches Linux and other open source software. He can be reached at russell@dyerhouse.com.