Use PHP to Backup your MySQL Database

Posted by Chris in Web Development   |   October 6th, 2009   |    2 comments

If you don’t backup your databases regularly, shame on you. You should!

The code below will allow you to generate a backup as often as you’d like. It only makes sense to run this via a CRON job. This is pretty easy to do and you can set the frequency as you wish.

The backup files will be stored in the same directory as this script.

Please leave feedback and let me know if this works for you!

Here’s the PHP code:

backup_tables('localhost','username','password','blog');
// Backup the entire database or just a specific table.
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
	$link = mysql_connect($host,$user,$pass);
	mysql_select_db($name,$link);
	//get all of the tables
	if($tables == '*')
	{
		$tables = array();
		$result = mysql_query('SHOW TABLES');
		while($row = mysql_fetch_row($result))
		{
			$tables[] = $row[0];
		}
	}
	else

	{
		$tables = is_array($tables) ? $tables : explode(',',$tables);
	}
	//This method is completed for each table
	foreach($tables as $table)
	{
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);
		$return.= 'DROP TABLE '.$table.';';
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
		$return.= "\n\n".$row2[1].";\n\n";
		for ($i = 0; $i < $num_fields; $i++)
		{
			while($row = mysql_fetch_row($result))
			{
				$return.= 'INSERT INTO '.$table.' VALUES(';
				for($j=0; $j<$num_fields; $j++)
				{
					$row[$j] = addslashes($row[$j]);
					$row[$j] = ereg_replace("\n","\\n",$row[$j]);
					if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
					if ($j<($num_fields-1)) { $return.= ','; }
				}
				$return.= ");\n";
			}
		}
		$return.="\n\n\n";
	}
	//Now, we'll save the file
	$handle = fopen('backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
	fwrite($handle,$return);
	fclose($handle);
}

2 Comments to “Use PHP to Backup your MySQL Database”