How to download a live MySQL db into a local test db on demand, without SSH?

Question

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

Answer 1

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.

Answer 2

Here's what I use. This dumps the database from the live server while uploads it to the local server. mysqldump -hlive_server_addresss -ulive_server_user -plive_server_password --opt --compress live_server_db | mysql -ulocal_server_user -plocal_server_password local_server_db

You can run this from a bat file. You can ever use a scheduled task.