HYGEN

Use PHP To Backup All MySQL Databases On A Server

December 10th, 2009 | by admin |

Here is a useful PHP script I worte for doing web based MySQL backups.  You could integrate this code into some kind of admin web app.  As it is, keep in mind security - don’t put this code up publicly on your server unless you want to share all of your MySQL data!

First we need to increase the max execution time for PHP because depending how many and how large your MySQL databases are - it could take quite a while for the backups to execute. For my usage 240 seconds was enough for the script to run. Increase this if you find the page hanging before your backups are completed.

ini_set(max_execution_time,240);
$b = ini_get(max_execution_time);
echo "$b";

Next we define some white space and new lines and connect to MySQL…

define( 'NL', "\n" );
define( 'TB', '  ' );
// connecting to MySQL.
$conn = @mysql_connect( 'localhost', username', 'password' )
        or die( mysql_errno().': '.mysql_error().NL );
//function to get a list of mysql databases...
$result = mysql_list_dbs( $conn );

Now for the output and execution…

// Output the list
echo '<ul>'.NL;
while( $row = mysql_fetch_object( $result ) ):
	if ($row->Database != "mysql" && $row->Database != "information_schema") {
	$backupFile = $row->Database . '.sql.txt';
	$command = "mysqldump --opt -hlocalhost -uusername -ppassword $row->Database > $backupFile";
	system($command);
	echo TB.'<li>'.$row->Database.' backed up to:  <a href="'.$backupFile.'">'.$row->Database.'.sql.txt</a> </li>'.NL;
	}
endwhile;
 
echo '</ul>'.NL;
 
mysql_free_result ($result);
mysql_close ($conn);

Finally glue all of the above code together, edit the connection information at two points - for the initial connection and for mysqldump, then upload to your web server.

Calling the file from your browser will generate a list of all of your MySQL databases with links to download their backups! Nice and easy