2. PHPRunner Tips and Tricks
  3. How to create a dump of MySQL database in one click
This topic is locked

How to create a dump of MySQL database in one click

 		4/13/2012 1:50:41 PM
PHPRunner Tips and Tricks
Admin

Your users probably asked you to add an option to export the whole database to SQL file. This can be done in phpMyAdmin or Navicat of course but your users want something even more simple than that.
Create a new file named backup.php and paste the following code snippet there. Edit database connection settings and path to mysqldump if required. Add a link to backup.php to your application:

<a href="backup.php">Export the whole database</a>


Please note that this script requires write permissions on scripts folder in order to save dump to file and zip it. If your scripts do not have such permissions use the second version of script. The only downside is that dump file won't be zipped.
Version with zipping. Requires write permissions on the folder where scripts are located.

<?php
ob_start();
$username = "root";

$password = "";

$hostname = "localhost";

$dbname = "cars";
// if mysqldump is on the system path you do not need to specify the full path

// simply use "mysqldump --add-drop-table ..." in this case

$command = "C:\\xampp\\mysql\\bin\\mysqldump --add-drop-table --host=$hostname --user=$username ";

if ($password)

$command.= "--password=". $password ." ";

$command.= $dbname;

system($command);
$dump = ob_get_contents();

ob_end_clean();
// the database dump now exists in the $dump variable

// saving it to the file

$dumpfname = $dbname . "_" . date("Y-m-d_H-i-s").".sql";

$fp = fopen($dumpfname, "w");

fputs($fp, $dump);

fclose($fp);
// zip the dump file

$zipfname = $dbname . "_" . date("Y-m-d_H-i-s").".zip";

$zip = new ZipArchive();

if($zip->open($zipfname,ZIPARCHIVE::CREATE))

{

$zip->addFile($dumpfname,$dumpfname);

$zip->close();

}
// read zip file and send it to standard output

if (file_exists($zipfname)) {

header('Content-Description: File Transfer');

header('Content-Type: application/octet-stream');

header('Content-Disposition: attachment; filename='.basename($zipfname));

flush();

readfile($zipfname);

exit;

}

?>


Version without zipping. Write permissions are not required.

<?php
ob_start();
$username = "root";

$password = "";

$hostname = "localhost";

$dbname = "cars";
// if mysqldump is on the system path you do not need to specify the full path

// simply use "mysqldump --add-drop-table ..." in this case

$command = "C:\\xampp\\mysql\\bin\\mysqldump --add-drop-table --host=$hostname --user=$username ";

if ($password)

$command.= "--password=". $password ." ";

$command.= $dbname;

system($command);
$dump = ob_get_contents();

ob_end_clean();
// send dump file to the output

header('Content-Description: File Transfer');

header('Content-Type: application/octet-stream');

header('Content-Disposition: attachment; filename='.basename($dbname . "_" . date("Y-m-d_H-i-s").".sql"));

flush();

echo $dump;

exit();
?>


Enjoy!
S
 		stiven 5/6/2012

Thanks for this great tip. however it isn't working for me, the sql file that is being downloaded is blank. 0 bytes. it has no content. am i doing something wrong? i created a folder backup on the server and i put the file backup.php there. the permissions for this folder are set as 755 when I click on the link the file downloads but it has no content. if i set the backup folder permissions to 777 I get a 500 Internal Server Error when i click the link.
Thanks
Admin 5/7/2012

You need to explain which version of script you use. They require different setup.
Another thing is to enable detailed error messages to be sent to the browser. This can point you in the right direction.
S
 		stiven 5/7/2012

Thanks for the help. I used the second one no zipping and i found the problem the user, host, and password needed to be in single quotes, at least that worked for me



$command = "mysqldump --add-drop-table --host='$hostname' --user='$username' ";

if ($password)

$command.= "--password='". $password ."' ";

$command.= $dbname;

system($command);
S
 		swanside 9/17/2012

Hi,

I use wamp for my sql things

and I have this

<?php

ob_start();
$username = "root";

$password = "";

$hostname = "localhost";

$dbname = "rigwork";
// if mysqldump is on the system path you do not need to specify the full path

// simply use "mysqldump --add-drop-table ..." in this case

$command = "C:\\xampp\\mysql\\bin\\mysqldump --add-drop-table --host=$hostname --user=$username ";

if ($password)

$command.= "--password=". $password ." ";

$command.= $dbname;

system($command);
$dump = ob_get_contents();

ob_endclean();
// the database dump now exists in the $dump variable

// saving it to the file

$dumpfname = $dbname . "" . date("Y-m-dH-i-s").".sql";

$fp = fopen($dumpfname, "w");

fputs($fp, $dump);

fclose($fp);
// zip the dump file

$zipfname = $dbname . "" . date("Y-m-d_H-i-s").".zip";

$zip = new ZipArchive();

if($zip->open($zipfname,ZIPARCHIVE::CREATE))

{

$zip->addFile($dumpfname,$dumpfname);

$zip->close();

}
// read zip file and send it to standard output

if (file_exists($zipfname)) {

header('Content-Description: File Transfer');

header('Content-Type: application/octet-stream');

header('Content-Disposition: attachment; filename='.basename($zipfname));

flush();

readfile($zipfname);

exit;

}

?>


WOuld I change this line

$command = "C:\\xampp\\mysql\\bin\\mysqldump --add-drop-table --host=$hostname --user=$username ";



to be

$command = "C:\wamp\bin\mysql\mysql5.5.24\mysqldump --add-drop-table --host=$localhost --user=$root ";


Cheers

Paul
mbintex 6/21/2017

I´d like to make this script more versatile by getting the database credentials from variables like this:

$username=$user;

$password=$pwd;

$hostname=$host;

$dbname=$sys_dbname;


But it doesn´t work although $user and so on should be global.
Then I tried to fill $_SESSION["user"] and so on with $user etc. in the After App init event and use these by

$username=$_SESSION["user"];


This didn´t work either - any ideas?