Export script for mysql export over web

For auto-update scripts on a different server, etc. it’s nice to have a web-accessible database export. Make sure you set up adequate security! (both for access as well as php exec). You may not want to store db password in this file and you also would want to use a read-only mysql account. You can pass additional parameters as needed to the mysqldump command.

<?php
header("Content-type: text/txt; charset=utf-8");
header('Content-Description: data download');
header('Content-Disposition: attachment; filename=' . date('Ymd-') . 'dbname_data.sql');
header('Content-Transfer-Encoding: 8bit');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
system("mysqldump dbname -u dbuser -pdbpass");
exit;

example of script using above

echo "update db"
curl http://host.domain.com/_util/exportdb.php | \
	/usr/local/bin/mysql dbname -u mysqluser
echo
echo "svn update svnrepo reponame"
svn update /path/to/local/svnrepo

You could also compress and decompress the data if it’s a large file:

to compress on the server, modify these lines:

<?php
system("mysqldump dbname -u dbuser -pdbpass | gzip");
header('Content-Encoding: gzip');

and change the script to decode:

curl --header 'accept-encoding: gzip' http://host.domain.com/_util/exportdb.php | \
	gunzip | /usr/local/bin/mysql -u mysqluser dbname

I found this method more compatible than trying to use php’s compression.

Leave a Reply