As you work on multiple projects that involve relational databases, you run into the challenge of ensuring that period backup of your systems are done from time to time and copies saved to a location on your server. A backup operation involves running a script or going through the phpMyAdmin panel. The solution below is how I manage multiple database backup using the CRON setup on my web server.
Cron?
A Cron Job is an entry, in the form of scripts, entered into a Cron Table, a Linux daemon, which schedules and runs these entires automatically from-to-time. Setting up cron jobs involves creating a shell script, and referencing this script from the Cron Table, specified the frequency for the script to be run.
The latter operation differs across system configurations. On popular web servers with CPanel installed, you can locate the CRON Jobs tab, and create a new entry in the table pointing to the script you would like to run, specifying the run frequency. Here is a comprehensive tutorial on Managing Cron Jobs. This tutorial focus on setting up the PHP code that performs the backup.
Database backup: We are going to create a function that backs up all the tables in a MySQL database. The function simply generates an SQL string that backs-up all the creation of the database sent in the parameters as well as constituent tables.
<?php
/**
*
* function backs up the entire DB, a table at a time
*
* $dbhost is the mysql server host
* $dbuser is the mysql user
* $dbpass is the mysql password
* $dbname is the database name
* $dbtbl is the list of tables in the database. The default value is the wildcard '*' i.e. select all tables
*
*/
function backup_db_tables($dbhost, $dbuser, $dbpass, $dbname, $dbtbl='*')
{
// colored span codes CSS
$span_error = '<span style="color:red;">';
$span_success = '<span style="color:green;">';
$span_close = '</span>';
// initialize the message variable
$msg = '';
// connect to server
$link = mysql_connect($dbhost, $dbuser, $dbpass);
// select the database
if (mysql_select_db($dbname, $link))
{
// success message on selecting database
$msg .= date('Y-m-j H:i:s', time()) . $span_success.'<strong>'.$dbname.'</strong>' . ' Successfully selected database. '.$span_close;
//get all of the tables in the current database
if($dbtbls == '*')
{
// inialise the tables array
$tables = array();
// run sql query to get the tables in the database
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
// put table name the in the array
$tables[] = $row[0];
}
}
else
{
// if the submited $dbtbls is an array use it, else explode the string into an array
$tables = is_array($dbtbls) ? $dbtbls : explode(',',$tables);
}
// cycle through the tables
foreach($tables as $table)
{
// get all the values in th table
$result = mysql_query('SELECT * FROM '.$table);
// get the total number of fields
$num_fields = mysql_num_fields($result);
// append the DROP TABLE query
$return .= 'DROP TABLE '.$table.';';
// show the query that creates this table
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
// append the query : row2[1] contains the value Table 1
$return .= "\n\n".$row2[1].";\n\n";
// for each field...
for ($i = 0; $i < $num_fields; $i++)
{
// ...loop through the records of the table
while($row = mysql_fetch_row($result))
{
// ...genereste the INSERT query for each record
$return .= 'INSERT INTO '.$table.' VALUES(';
// for each field
for($j=0; $j<$num_fields; $j++)
{
// clean up the value
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n","\\n",$row[$j]);
}
// add the column value (cell contents), if valid
if (isset($row[$j])) { $return .= '"'.$row[$j].'"'; } else { $return .= '""'; }
// close out the row with a comma
if ($j < ($num_fields-1)) { $return .= ','; }
}
// close out the INSERT statement
$return .= ");\n";
}
}
$return .="\n\n\n";
}
// return now contains the SQL of a complete database
// append the timestamp
$msg .= date('Y-m-j H:i:s', time()) . ' Generated SQL for: <strong>'.$dbname.'</strong>: ';
// define save file name and path
$filename = 'sql/'.$dbname.'-db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
// save the SQL file
$msg .= save_sql_file($filename, $dbname, $tables);
}
else
{
// there was an error in select the database
$msg .= date('Y-m-j H:i:s', time()) . $span_error.'<strong>'.$dbname.'</strong>' . ' Error selecting the database. Database may not exist. '.$span_close;
}
$res['msg'] = $msg;
// return the message
return $res;
}
?>
Save the SQL: Write the SQL results to a file.
function save_sql_file($filename, $dbname, $tables)
{
// colored span codes CSS
$span_error = '<span style="color:red;">';
$span_success = '<span style="color:green;">';
$span_close = '</span>';
// define save file name and path
$filename = 'sql/'.$dbname.'-db-backup-'.time().'-'.(md5(implode(',', $tables))).'.sql';
// initialize the file copy result string
$msg = '';
// open up a handle to write to named file
if ($handle = fopen($filename, 'w+'))
{
// write to file
if (fwrite($handle, $return))
{
// close the file handle
fclose($handle);
// append success message
$msg .= ' '.$span_success.'SUCCESS'.$span_close.'<br>';
// in addition to this, you can check the sizeof to ensure that file is not a null file
}
else
{
// append error message
$msg .= ' ' . $dbname . ' '.$span_error.'Error writing to file.'.$span_close.'<br>';
}
}
else
{
// error moving the file
$msg .= ' '.$span_error.'Error creating file handle.'.$span_close.'<br>';
}
// return the result
return $msg;
}
Notification: Inform the system administrator of the CRON operation.
<?php
/**
*
* simple function that mails the message msg to an email address
* can be modified to send to many recipients
*
*/
function email_log ($msg)
{
// send email to me
$to = 'unwigwe@blinkwiki.com';
// the subject
$subject = 'System database backup @ ' . date('Y-m-j H:i:s', time());
//define HTML headers
$headers = "From: $from\r\n";
$headers .= "Content-type: text/html\r\n";
// the body of the email
$body = 'Hi Sysadmin,<br>';
$body .= '<br>A scheduled backup was done for the following databases. Below is the report:';
$body .= '<br><br><b>Backup Log: <b><br><hr>';
$body .= $msg;
$body .= '<br>Server Date: ' . date('Y-m-j') .'<br>'. 'Server Time: ' . date('H:i:s', time());
// send the email
if (mail($to, $subject, $body, $headers))
{
echo ('$lt;p>Email successfully sent!</p>');
}
else
{
echo ('<p>Email delivery failed...</p>');
}
}
?>
Putting it together:
Finally, we combine all these functions into a shell script written in PHP. This is done by adding the code
#!/usr/local/bin/php.cli
at the very beginning of the PHP code. This executes the PHP code as a shell script.
#!/usr/local/bin/php.cli
<?php
// set the time zone (timezones are @ http://www.php.net/manual/en/timezones.php)
date_default_timezone_set('America/Los_Angeles');
{
//database array : list all the databases you wish to backup here (in CSV format: var1, var2, var3 ...)
$db_str = ''
.DB_NAME1
.', '.DB_NAME2
.', '.DB_NAME3
.', '.DB_NAME4
.', '.DB_NAME5
.', '.DB_NAME6
.', '.DB_NAME7
;
// convert string to array
$db_arr = explode(', ', $db_str);
// initialize the message variable
$msg = '';
// loop through the db array : backing up each
for ($i=0; $i<count($db_arr); $i++)
{
// loop through all the available tables and create backups in files
$res = backup_db_tables(DB_HOST, DB_NAME, DB_PASS, $db_arr[$i]);
// append the results
$msg .= $res['msg'];
}
// email the results
email_log($msg);
}
?>