Backup all MySQL databases automagicly with the PHP admin script

Submitted by n8coder on Thu, 03/31/2011 - 14:44

Server administration software like Cpanel, Plesl, Webmin etc have already their backup solutions. But they are runned mostly once a week or max a day because of performance reasons. Let say we just want to backup all of our mysql-databases every 2 hours.

To do this:

  • Create a directory like /backup/mysql
  • Save code below under something like backup_mysql.php
  • Chmod it to 755
  • Run it manually or with cronjobs /root/scripts/backup_mysql.php

// use this if your php.ini does not allow shell etc #!/usr/bin/php -n

$mysql_root_pwd = "YOUR-MYSQL-ROOT-PASSWORD";

function formattime() { return date("Y-m-d H:i:s"); }

echo "Starttime: ". formattime();

$r = mysql_query("show databases");

while( $arr = mysql_fetch_array($r) ){
$db = $arr[0];
echo "\n\nExporting $db\n" . formattime() . "\n";

echo shell_exec("/usr/bin/mysqldump -u root --password='$mysql_root_pwd' $db > /backup/mysql/".$db.".sql");

echo "\n\nEndtime: ". formattime() . "\n\n";

echo mysql_error();