MySQL Backup Script

Here is a simple PHP backup script that I run from the commandline to backup mysql. Basically it uses mysql_hotcopy which basically locks a table that copies the physical mysql directoy database folder. All my script does is loop through all the databases to a backup directory, then it tar.gz the folder then deletes the copied folder. You could of couse customize this very easily.

<?php
$username = "dbusername";
$password = "password";
$backupdir= "/backup";

$link = mysql_connect($s, $username, $password);
$db_list = mysql_list_dbs($link);
while ($row = mysql_fetch_object($db_list))
{
$dbname = $row->Database;

# Hotcopy
$script = "mysqlhotcopy $dbname -u $username --password=\"$password\" --allowold $backupdir";
$results = system($script,$retval);
echo "RESULTS: $results\n RETURN VALUE: $retval\n";

# Tar files up
$script = "tar -czf $backupdir/$dbname.tgz $backupdir/$dbname";
$results = system($script,$retval);
echo "\n CREATE TAR FILE:\n $results\n RETURN VALUE: $retval\n";

# Delete dir
$script = "rm -Rf $backupdir/$dbname";
$results = system($script,$retval);
echo "\n DELETE DIR:\n $results\n RETURN VALUE: $retval\n";
}
?>

Leave a Reply