Implementing Automatic Database Backup and Optimization in PHP

Every computer system has a backup. Nevertheless, the number of problems caused by a lack of a recent backup is huge. One of the reasons for that may be the fact that the backup process is not entirely automated. So, let’s see how to automate the database backup process in PHP.

Creating a Backup

There are many different methods to create a MySQL database backup. Which one you will use depends on which features and commands your web hosting supports. The most popular backup methods are listed below.

Method 1: mysqldump

Mysqldump is a Linux command that creates a database backup – a .sql file with DROP table, CREATE table and INSERT into SQL statements. To use this method, mysqldump must be installed on your host and your hosting must support Linux command execution from PHP. Mysqldump basic syntax is the following:

mysqldump -u [username] -p [password] [database_name] > dumpfilename.sql

Or in PHP:

$command = "mysqldump --opt -h $dbhost -u [username] -p[password] [database_name] > dumpfilename.sql";
shell_exec($command); 

This is just a basic example. For more detailed instructions, check this manual.

Method 2: mysqlhostcopy

Mysqlhostcopy is a PERL script and is faster for MyISAM tables than mysqldump. For this tool you will also need to have shell access to your hosting. The command is the following:

/usr/bin/mysqlhotcopy -u [username] -p [password] [database_name] /path/to/backup

In PHP:

$command = "/usr/bin/mysqlhotcopy -u [username] -p [password] [database_name] /path/to/backup";
shell_exec($command); 

To view mysqlhostcopy documentation, use this command:

perldoc mysqlhotcopy

Method 3: SELECT INTO OUTFILE

This method is a good solution if you do not have shell access on your hosting server. It uses a SQL query to generate a database backup. It requires two types of privileges:

1. Database user must have selection privilege (SELECT_PRIV)  2. MySQL daemon owner must have privileges to write to the target directory. 

SELECT * INTO OUTFILE ‘backup.sql’ FROM my_table

This query would create a backup for one table only, so you must loop through the whole database and create a separate file for each table:

$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       $sql = "SELECT * INTO OUTFILE 'backup/{$tablename}_backup.sql' FROM {$tablename}";
       mysql_query($sql)
       or die(mysql_error());
   }
}

Database Optimization

A MySQL database functions similarly to a hard drive. After many INSERT, UPDATE and DELETE statements, its physical structure gets cluttered, thus causing overhead and drop of performance. That is why database optimization is performed. Optimization is similar to defragmenting a hard drive and includes purging deleted rows, resequencing, compressing, managing index paths and many more operations.

Method 1: mysqlcheck

Mysqlcheck is a command-line tool that can check, repair, optimize, or analyze tables. It can either optimize one table or the whole database.

mysqlcheck -u [username] -p [password] --auto-repair --check --optimize <database_name>

For the whole database:

mysqlcheck -u [username] -p [password] --auto-repair --check --optimize --all-databases

In PHP:

$command = "mysqlcheck -u [username] -p [password] --auto-repair --check --optimize --all-databases";
shell_exec($command); 

Method 2: OPTIMIZE query

OPTIMIZE query can optimize only one table at a time, so it is necessary to loop through the tables:

$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       $sql = "OPTIMIZE TABLE '". $tablename."'";
       mysql_query($sql)
       or die(mysql_error());
   }
}

Making the Database Backup and Optimization Automatic

Automating the backup and optimization process is done by setting up a cron job to run a PHP script at a certain time interval – daily, weekly, biweekly, etc. Cron job can be set up in Cpanel. The time interval is set using a dropdown menu. In the command line field enter:

php /home/your_username/public_html/backup.php?v=my_secret_phrase
backup.php:
<?php
// check access to prevent unauthorized users from running a backup
$v = $_GET['v'];
if($v != 'my_secret_phrase') {
    die('access not allowed');
}

// generate a backup
$command = "/usr/bin/mysqlhotcopy -u [username] -p [password] [database_name] /path/to/backup";
shell_exec($command);

// Do something with the backup

// Optimize the database
$command = "mysqlcheck -u [username] -p [password] --auto-repair --check --optimize --all-databases";
shell_exec($command);

?> 

@to-do

After creating the database backup, you need to do something with it. For example, you can e-mail it to yourself, integrate with Dropbox API and store backups there, etc.

Notes

1. MySQL functions used in this tutorial are for educational purposes. Do not use them in production. Use mysqliinstead.

2. This tutorial covers only .sql backup creation. For creating a .csv backup, check the manuals.

3. Read your host’s terms of service if you intend to keep the backups on the server. Most hosts do not allow you to use your disk space for backups.