Monday, October 5, 2009

Backup with mysqldump with PHP

Have you ever wanted to create a script a script that will create a mysqldump of a database using PHP? Here is a good starting point. More can be done to extend this to make it more useful, things like emailing the dump, restoring the database on fly and so much more.

There is a minor difference between the script for a server running on MS Windows as compate to the Linux/UNIX

MS Windows: XAMMP or WAMP

/* variable declaration */
$db_user = "yourUsername";
$db_password = "yourPassword";
$db_database = "yourDatabaseName";
$db_server = "yourHostName"; //default: localhost
$backup_path = "C:\\path\\to\\backup\\folder\\";
//e.g $backup_path = "C:\\backups\\sqldumps\\";
$path_to_mysqldump = "C:\\path\\to\\mysql\\bin\\";
//e.g $path_to_mysqldump = "C:\\xampp\\mysql\\bin\\";
$filename = "backup".date("YmdHis").".sql"; //filename with a timestamp

/*construction the command to be run */
$command= "$path_to_mysqldump\\mysqldump.exe --add-drop-table --add-drop-database -h $db_server -u $db_user --password=$db_password $db_database > $filename ";

/* excution of the command */ 
exec($command, $dump, $status); 

/*checking the status of the command after runing */
if($status!=0){
echo "The execution was not successful. An empty file has been created";
}else{
echo "The execution was successful. Backup files is " . $backup_path.$filename ;
}

Linux/UNIX
* variable declaration */
$db_user = "yourUsername";
$db_password = "yourPassword";
$db_database = "yourDatabaseName";
$db_server = "yourHostName"; //default: localhost
$backup_path = "/path/to/backup/folder/"; //you must have permission to write in this folder
//e.g $backup_path = "/servers/backups/sqldumps/"; 
$filename = $backup_path."backup".date("YmdHis").".sql"; //filename with a timestamp

/*construction the command to be run */
$command= "mysqldump.exe --add-drop-table --add-drop-database -h $db_server -u $db_user --password=$db_password $db_database > $filename ";

/* excution of the command */ 
exec($command, $dump, $status); 

/*checking the status of the command after runing */
if($status!=0){
echo "The execution was not successful. An empty file has been created";
}else{
echo "The execution was successful. Backup files is " . $backup_path.$filename ;
}

Differences highlighted:
  • in windows it, unless you have added the mysql bin to the path, you must specify the full path to the mysqldump.exe, as in Linux it accessible with out the full path
  • also the \\ used in the path, as in Linux we use /
  • in Linux remember you mush have write asses to the folder you are

No comments:

Post a Comment