How To Download A Live MySQL Db Into A Local Test Db On Demand, Without SSH?

- 1 answer

I have a fairly small MySQL database (a Textpattern install) on a server that I do not have SSH access to (I have FTP access only). I need to regularly download the live database to my local dev server on demand; i.e., I would like to either run a script and/or have a cron job running. What are some good ways of doing this?

Some points to note:

  • Live server is running Linux, Apache 2.2, PHP 5.2 and MySQL 4.1
  • Local server is running the same (so using PHP is an option), but the OS is Windows
  • Local server has Ruby on it (so using Ruby is a valid option)
  • The live MySQL db can accept remote connections from different IPs
  • I cannot enable replication on the remote server

Update: I've accepted BlaM's answer; it is beautifully simple. Can't believe I didn't think of that. There was one problem, though: I wanted to automate the process, but the proposed solution prompts the user for a password. Here is a slightly modified version of the mysqldump command that passes in the password:

mysqldump -u USER --password=MYPASSWORD DATABASE_TO_DUMP -h HOST > backup.sql



Since you can access your database remotely, you can use mysqldump from your windows machine to fetch the remote database. From commandline:

cd "into mysql directory"
mysqldump -u USERNAME -p -h YOUR_HOST_IP DATABASE_TO_MIRROR >c:\backup\database.sql

The program will ask you for the database password and then generate a file c:\backup\database.sql that you can run on your windows machine to insert the data.

With a small database that should be fairly fast.