I ran across this and figured I would share, since it seems to work quite well. Hope these help someone, and would be great if someone modified it to email the backup to you. This script lets you backup your mysql database to the directory the script is installed in. <?php // +------------------------------------------------------------------+ // | Usage : // | 1. Fill in the dbuser, dbname, and dbpwd fields below. // | 2. set up a cron job to call this file. // | this will create a backup of the db, in an .sql file, which can be restored via phpmyadmin. // | // | you can test it via http://www.domain.com/[folder]/cron_backupdb.php?verbose=1 // | // | Referrences :http://www.interspire.com/content/articles/5/1/An-Introduction-to-Cron // +------------------------------------------------------------------+ $dbuser = ''; $dbpwd = ''; $dbname = ''; $dbhost = 'localhost'; $dbprefix = ''; if(!$mycon = @mysql_connect($dbhost,$dbuser,$dbpwd)) die('Database Error: '.mysql_error()); elseif(!@mysql_select_db($dbname,$mycon)) die ('Database Error: '.mysql_error()); error_reporting(E_ALL & ~E_NOTICE); $verbose = !empty($_REQUEST['verbose']) ? 1 : 0; @set_time_limit(600); // try to give ourselves plenty of time to run function backup_mysql() { global $dbname, $verbose; $backticks =1; $ticks = ($backticks == 1)? '`' : "'"; $set['data'] = 1; //with data; $set['endit'] = 1; $set['usedrop']= 1; $tables = mysql_list_tables($dbname) or die(mysql_error()); $gettables = @mysql_num_rows($tables); $a .= "##--------------------------------------------\r\n"; $a .= "##--------------------------------------------\r\n"; $a .= "##--".$_SERVER['SERVER_NAME']." mySQL Database : ".$dbname."\r\n"; $a .= "##--Total Tables : ".$gettables." Saved On :".date("Y-m-d H:i:s",time())." \r\n"; $a .= "##--------------------------------------------\r\n"; $a .= "##--------------------------------------------\r\n\r\n"; if($verbose) echo "<pre>".$a."</pre>"; $ender = ($set['endit']) ? ';':''; $sql = mysql_query("SHOW TABLES"); while($table1 = mysql_fetch_array($sql)) { $table = $table1[0]; $a .= "\r\n##------------------ ".$table." ----------------------\r\n \r\n"; $drop = "DROP TABLE IF EXISTS $ticks".$table.$ticks.$ender."\r\n"; $a .= ($set['usedrop']== 1) ? $drop:''; $row1 = mysql_query("SHOW CREATE TABLE ".$table) or die(mysql_error()); $row2 = mysql_fetch_array($row1); $row2 = ($backticks == 1) ? $row2[1] : str_replace("`","'",$row2[1]); $a .= $row2.$ender."\r\n"; //data $a .= ($set['data'] == 1) ? "\r\n".backup_table($table,$set['endit']):''; $a .= "\r\n\r\n\r\n\r\n"; } $a .= "##------------ END OF FILE ----------------------\r\n \r\n"; define( '_MSOL_PATH', dirname(__FILE__) ); $filename = _MSOL_PATH.'/sql_'.$dbname.'_'.date("dmy",time()).'.sql'; $filelength = strlen($a); $filedata = $a; if($verbose) echo "<pre> Path : ".$filename."</pre>"; if (!$handle = fopen($filename, 'w+')) { die("Failed to open stream : ".$filename); } if (!fwrite($handle,$filedata)){ die("Fail - Not able to right to file : ".$filename); } fclose($handle); exit(); } function backup_table($table,$endit=0) { global $dbname, $verbose; $sql = mysql_query("SELECT * FROM $table") or die(mysql_error()); $count = mysql_num_rows($sql); $backticks = 1; $ticks = ($backticks == 1)? '`' : "'"; $countit = mysql_num_fields($sql); while ($row = mysql_fetch_array($sql)) { $a .= "INSERT INTO $ticks".$table."$ticks SET "; for ($i=0; $i < $countit; $i++) { $get = mysql_fetch_field($sql,$i); $a .= $ticks.$get->name.$ticks."='".addslashes(stripslashes($row[$i]))."'"; if ($i+1 < $countit) { $a .= ", "; } } $a .= ($endit == 1 )? ";":""; $a .= "\r\n"; } return $a; } if($verbose) echo "<br>Starting Backup!"; backup_mysql(); ?> PHP: This second one is modified so instead of saving the database to the server, it allows you to download it to your PC. <?php // +------------------------------------------------------------------+ // | Usage : // | 1. Fill in the dbuser, dbname, and dbpwd fields below. // | 2. set up a cron job to call this file. // | this will create a backup of the db, in an .sql file, which can be restored via phpmyadmin. // | // | you can test it via http://www.domain.com/[folder]/cron_backupdb.php?verbose=1 // | // | Referrences :http://www.interspire.com/content/articles/5/1/An-Introduction-to-Cron // +------------------------------------------------------------------+ $dbuser = ''; $dbpwd = ''; $dbname = ''; $dbhost = ''; $dbprefix = ''; if(!$mycon = @mysql_connect($dbhost,$dbuser,$dbpwd)) die('Database Error: '.mysql_error()); elseif(!@mysql_select_db($dbname,$mycon)) die ('Database Error: '.mysql_error()); error_reporting(E_ALL & ~E_NOTICE); $verbose = !empty($_REQUEST['verbose']) ? 1 : 0; @set_time_limit(600); // try to give ourselves plenty of time to run function backup_mysql() { global $dbname, $verbose; $backticks =1; $ticks = ($backticks == 1)? '`' : "'"; $set['data'] = 1; //with data; $set['endit'] = 1; $set['usedrop']= 1; $tables = mysql_list_tables($dbname) or die(mysql_error()); $gettables = @mysql_num_rows($tables); $a .= "##--------------------------------------------\r\n"; $a .= "##--------------------------------------------\r\n"; $a .= "##--".$_SERVER['SERVER_NAME']." mySQL Database : ".$dbname."\r\n"; $a .= "##--Total Tables : ".$gettables." Saved On :".date("Y-m-d H:i:s",time())." \r\n"; $a .= "##--------------------------------------------\r\n"; $a .= "##--------------------------------------------\r\n\r\n"; if($verbose) echo "<pre>".$a."</pre>"; $ender = ($set['endit']) ? ';':''; $sql = mysql_query("SHOW TABLES"); while($table1 = mysql_fetch_array($sql)) { $table = $table1[0]; $a .= "\r\n##------------------ ".$table." ----------------------\r\n \r\n"; $drop = "DROP TABLE IF EXISTS $ticks".$table.$ticks.$ender."\r\n"; $a .= ($set['usedrop']== 1) ? $drop:''; $row1 = mysql_query("SHOW CREATE TABLE ".$table) or die(mysql_error()); $row2 = mysql_fetch_array($row1); $row2 = ($backticks == 1) ? $row2[1] : str_replace("`","'",$row2[1]); $a .= $row2.$ender."\r\n"; //data $a .= ($set['data'] == 1) ? "\r\n".backup_table($table,$set['endit']):''; $a .= "\r\n\r\n\r\n\r\n"; } $a .= "##------------ END OF FILE ----------------------\r\n \r\n"; $filename = 'sql_'.$dbname.'_'.date("dmy",time()).'.sql'; $filelength = strlen($a); $filedata = $a; header('Pragma: public'); header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT'); header('Cache-Control: no-store, no-cache, must-revalidate'); header('Cache-Control: pre-check=0, post-check=0, max-age=0'); header('Content-Transfer-Encoding: none'); if ( preg_match("/MSIE/i",$_SERVER["HTTP_USER_AGENT"]) || preg_match("/opera/i",$_SERVER["HTTP_USER_AGENT"]) ) { header('Content-Type: application/octetstream; name="'.$filename.'"'); } else { header('Content-Type: application/octet-stream; name="'.$filename.'"'); } header('Content-Disposition: attachment; filename="'.$filename.'"'); header("Content-length: ".$filelength); echo $filedata; exit(); } function backup_table($table,$endit=0) { global $dbname, $verbose; $sql = mysql_query("SELECT * FROM $table") or die(mysql_error()); $count = mysql_num_rows($sql); $backticks = 1; $ticks = ($backticks == 1)? '`' : "'"; $countit = mysql_num_fields($sql); while ($row = mysql_fetch_array($sql)) { $a .= "INSERT INTO $ticks".$table."$ticks SET "; for ($i=0; $i < $countit; $i++) { $get = mysql_fetch_field($sql,$i); $a .= $ticks.$get->name.$ticks."='".addslashes(stripslashes($row[$i]))."'"; if ($i+1 < $countit) { $a .= ", "; } } $a .= ($endit == 1 )? ";":""; $a .= "\r\n"; } return $a; } if($verbose) echo "<br>Starting Backup!"; backup_mysql(); ?> PHP:
I'm pretty sure you could do a single MySQL command through CRON to get the same outcome, although I'm not sure what it is.