As part of the Amberdms Billing System I needed to add the ability to export the entire MySQL database when logged in as an administrator from the application UI.

This feature was desired to prevent any shoddy hosting companies from preventing users from downloading their data from the application – without it, a hosting provider could refuse to provide the database creating effective vendor lock-in for users, even though the software is open source.

There were a couple different approaches I could use:

• Implement code that reads all the database structure and data rows and writes SQL from that. (this is the phpmyadmin approach)
• Use mysqldump from the CLI

I chose the latter, since it’s much easier to write and maintain than a SQL generator like phpmyadmin uses, however I came across a few challenges:

• I needed to supply a username & password to mysqldump – however, doing this via the CLI would expose the password to anyone with shell access to the server (they could run ps aux to see the password used).
• The databases could be anywhere from 1MB to several hundred, whatever solution was chosen could not require the whole file to be stored in memory.

My approach was to write some code that creates a temporary configuration and export file, then saves the authentication details into the temp file and calls mysqldump and instructs it to use the config file for options.

To provide the file for download, the PHP script then sets the HTTP headers and uses readfile to basically output all the file contents straight to the brower, avoiding any memory issues for the PHP script.

Below is my code, note that there are some support functions used to generate secure, unique temp files as well as perform easier MySQL queries, but it is easy to adapt to whatever framework you are using.

It is important to note that the process that generates your temporary files should make sure the files are readable ONLY by the webserver process, otherwise other users could read the config file and discover the passwords.

/*
	Create temp files for download
*/
$file_config	= file_generate_tmpfile();
$file_export	= file_generate_tmpfile();


/*
	Write authentication information into temp config file 
	this allows us to prevent the exposure of the DB password on the CLI
*/

$fh = fopen($file_config, "w");
fwrite($fh, "[mysqldump]\n");
fwrite($fh, "host=". $config["db_host"] ."\n");
fwrite($fh, "user=". $config["db_user"] ."\n");
fwrite($fh, "password=". $config["db_pass"] ."\n");
fclose($fh);


/*
	Export Database
*/

$dbname = sql_get_singlevalue("SELECT DATABASE() as value");

system("/usr/bin/mysqldump --defaults-file=$file_config $dbname > $file_export");


/*
	Set HTTP headers
*/

$filename = "database_export_". mktime() .".sql";
	
// required for IE, otherwise Content-disposition is ignored
if (ini_get('zlib.output_compression'))
	ini_set('zlib.output_compression', 'Off');

header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers 
header("Content-Type: application/force-download");
	
header("Content-Disposition: attachment; filename=\"".basename($filename)."\";" );
header("Content-Transfer-Encoding: binary");

// tell the browser how big the file is (in bytes)
header("Content-Length: ". filesize($file_export) ."");


/*
	Print out the file contents for browser download
*/
readfile($file_export);


/*
	Cleanup
*/
unlink($file_config);
unlink($file_export);

Feel free to use any of this code royalty-free in your own applications. :-)