How can I automate retrieval of MySQL databases from shared hosting which disallows remote MySQL connections?

Question

my situation is the following:

• I need to do an automated backup of a database
• I have only FTP access, PhpMyAdmin, and PHP

So:

• No SSH means I can't use mysqldump
• No Perl (and no cron) means I cannot call the mysqldumper cron script

Do you know any tool which would do an unassisted backup of a database? By unassisted I mean that I would like to call it using a curl command for example...

I will be setting up a VPS probably in the near future, but for the moment I am stuck with this provider!

Answer 1

This sound like a job for phpMyBackupPro that I used a long time ago in a similar situation. It will let you create a scheduled backup by calling a simple PHP script on your server that you can call by URL. The backups can then be either stored on the server, FTPed to another server or mailed.

In my old setup I even wrote a simple XML-RPC script that would call the phpMBP scheduled backup script (here named 'ping.php') when pinged by e.g. WordPress:

<?xml version="1.0"?>
<?php @include('ping.php'); ?>
<methodResponse>
<params>
  <param>
    <value>
       <struct>
         <member>
           <name>flerror</name>
             <value>
               <boolean>0</boolean>
             </value>
         </member>
         <member>
           <name>message</name>
           <value>Thanks for the ping.</value>
         </member>
       </struct>
     </value>
   </param>
 </params>
</methodResponse>

However, I would recommend moving on to a VPS as soon as possible. With VPSs being cheap and abundant it's a no-brainer today compared to shared hosts.

Answer 2

A quick google search revealed a script that claims to use cURL to access phpMyAdmin and perform a dump, but I have not personally tested it:

http://picoforge.int-evry.fr/websvn/filedetails.php?repname=curlmyback&path=%2Ftrunk%2Fcurl-backup-phpmyadmin.sh&rev=0&sc=0

Answer 3

mysqldump works for remote hosts - use the -h flag to specify the host to connect to (and ensure that your shared hosting account is configured to accept connections from the remote host IP and access credentials).

It's also very likely that mysqldump is installed on the server and it is further likely that PHP's execution operators are enabled (a horrible idea for shared hosting, but sadly quite common) so you may be able to use PHP to execute shell commands and accomplish your goal.

---

Edit: If remote MySQL connections and execution operators are disabled, the cURL script which phrac recommended may be a solution, but even if that script does not meet your needs, you still have options (if you can access the data and run scripts, you can always get a copy).

If mysqldump is installed on your shared hosting server you could create a cron job to dump the database to a web-accessible directory - e.g. mysqldump dbname > /path/to/www/subdir/backup.sql (just make sure the subdirectory blocks access by untrusted IP addresses)

If you don't have mysqldump on the shared hosting server, you could write a PHP script which executes a SELECT ... INTO OUTFILE query (again, under a protected web-accessible directory with lenient write permissions).

If you don't have the option grant a MySQL user sufficient privileges to write to an outfile, you could still write a PHP script which (it's an ugly solution) echoes the MySQL INSERT statements for your data in response to requests and put that script in a web-accessible directory.