Backup MySQL website databases without mysqldump

To backup the underlying MySQL databases for websites (Joomla! WordPress etc) I usually ssh into the hosting server, run mysqldump, then use rsync to copy the resulting sql file to the backup server. However, I’ve recently had to set up a site on a host without ssh or rsync available.

Instead, I wrote a php script to generate the same sort of SQL as mysqldump. I put the script on the hosting server (in a location that requires basic authentication), and I use cron to launch a
bash script which runs wget:

Fortunately MySQL has the “show create table” statement to generate the “create table” SQL to recreate an existing table. The other trick was to use an array of the numeric data types to determine if the content should be enclosed in
single quotes within the INSERT statement.

Leave a Reply