What to do if you want to use mysqldump in a shared hosting environment without being able to access the shell (SSH…)? Use a Perl CGI script:
#!/usr/bin/perl -w use strict; use CGI::Carp qw(fatalsToBrowser); print "Content-type: text/plain\nContent-disposition: attachment; filename=\"db_backup.sql\"\n\n"; print qx(mysqldump -uUSERNAME -pPASSWORD DATABASE 2>&1);
Replace USERNAME, PASSWORD and DATABASE with the configuration data for your database and drop the script into your cgi-bin directory. Don’t forget to make it executable (chmod +x) and apply some form of access restrictions! Here’s an Apache .htaccess file which serves well for this purpose:
AuthType Basic AuthUserFile /path/to/.htpasswd AuthName "SQL Backup" Order deny,allow Allow from all Require valid-user
Create the .htpasswd file with htpasswd -c .htpasswd USERNAME
To automatically fetch a current backup and compress it, run the following shell script on another machine (probably as a cron job):
#!/bin/bash USER=username PW=password IN=http://example.org/cgi-bin/dbdump.pl OUT=/where/to/store/the/db_backup.sql.bz2 wget -nv --user=$USER --password=$PW -O - $IN | bzip2 > $OUT