Back Up Your MySQL Database with PHP

by Curtiss   |   last updated December 22, 2010   |   PHP Tutorials ·

Obviously the ideal way to back up a MySQL database is to use mysqldump. Failing that, a lot of people will use PHPMyAdmin. Unfortunately, however, not everyone has command-line access to a server; and even fewer are able to execute mysqldump from a script. If PHPMyAdmin isn’t already installed, it can be difficult to get it installed and configured on a shared hosting account. Therefore, it’s sometimes necessary to use a PHP script to back up a MySQL database.

David Walsh has done a nice job of putting together a PHP script to backup your MySQL databases. Below is a copy of the code he provides in his blog article:

backup_tables('localhost','username','password','blog');

/* backup the db OR just a 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);
	}

	//cycle through
	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 file
	$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
	fwrite($handle,$return);
	fclose($handle);
}

I have one minor issue with the way he’s written the script, though. In his script, he’s storing the entire backup in the buffer and only writing it to a file once the whole thing is finished. However, if you’re working with a large database, you’ll most likely overflow your PHP memory before the script is finished, causing it to error out.

Therefore, I’d recommend modifying the script like so, writing to the file each time an action is complete.

<?php
define('DB_NAME', 'database_name');    // The name of the database
define('DB_USER', 'database_user');     // Your MySQL username
define('DB_PASSWORD', 'database_password'); // ...and password
define('DB_HOST', 'localhost');    // 99% chance you won't need to change this value

backup_tables(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

/* backup the db OR just a 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);
	}

	$filename = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
	$handle = fopen($filename,'w');

	//cycle through
	foreach($tables as $table)
	{
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);

		fwrite( $handle, 'DROP TABLE '.$table.';' );
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
		fwrite( $handle, "\n\n".$row2[1].";\n\n" );

		for ($i = 0; $i < $num_fields; $i++)
		{
			while($row = mysql_fetch_row($result))
			{
				fwrite( $handle, '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])) { fwrite( $handle, '"'.$row[$j].'"' ) ; } else { fwrite( $handle, '""' ); }
					if ($j<($num_fields-1)) { fwrite( $handle, ',' ); }
				}
				fwrite( $handle, ");\n" );
			}
		}
		fwrite( $handle, "\n\n\n" );
	}

	//save file

	fclose($handle);
}
?>

As you can see, in my code, I’ve opened/created the file near the beginning of the script, then am dumping into it regularly throughout the script.