Creating Automatic MySQL Database backups

Every database needs to be backed up and if you're currently doing it manually you can stop because there's a simple solution using PHP. With this script you can either pass a specific table to backup or choose to backup the whole database.
How to implement

The best way to use the script below is to copy the code and put it in a folder called ‘database_backups' as shown below. Then whenever you call the backup.php file it will create a database backup which is stored on your server. I'd recommend downloading these database backups periodically so you have a copy stored safely elsewhere incase you lose your hosting.

Using CRON

Cron is a time-based job scheduler which allows you to run certain scripts at specific times. For example I have my database backups running every night at a time when traffic levels are low. You could setup a CRON job if your web host permits by running the following command. Just change the link to point to the backup file in your server.

wget -O /dev/null http://your-site.com/database_backup/backup.php

The Code

<?php
backup_database_tables('HOST','USERNAME','PASSWORD','DATABASE', '*');

// backup the db function
function backup_database_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);
	}

	//cycle through each table and format the data
	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";
	}

	//save the file
	$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
	fwrite($handle,$return);
	fclose($handle);
}
?>