<p>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?</p>

<p>Some points to note:</p>

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

<p><strong><em>Update:</em></strong> 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:</p>

<p><code>mysqldump -u USER --password=MYPASSWORD DATABASE_TO_DUMP -h HOST > backup.sql</code></p>

[{'answer_id': 39545, 'author': 'Mark Biek', 'author_id': 305, 'author_profile': 'https://Stackoverflow.com/users/305', 'pm_score': 1, 'selected': False, 'text': '<p>Is MySQL replication an option?  You could even turn it on and off if you didn\'t want it constantly replicating.</p>\n\n<p>This was a <a href="http://www.howtoforge.com/mysql_database_replication" rel="nofollow noreferrer">good article</a> on replication.</p>\n'}, {'answer_id': 39555, 'author': 'Anders Sandvig', 'author_id': 1709, 'author_profile': 'https://Stackoverflow.com/users/1709', 'pm_score': 0, 'selected': False, 'text': "<p>I would create a (Ruby) script to do a <code>SELECT * FROM ...</code> on all the databases on the server and then do a <code>DROP DATABASE ...</code> followed by a series of new <code>INSERT</code>s on the local copy. You can do a <code>SHOW DATABASES</code> query to list the databases dynamically. Now, this assumes that the table structure doesn't change, but if you want to support table changes also you could add a <code>SHOW CREATE TABLE ...</code> query and a corresponding <code>CREATE TABLE</code> statement for each table in each database. To get a list of all the tables in a database you do a <code>SHOW TABLES</code> query.</p>\n\n<p>Once you have the script you can set it up as a scheduled job to run as often as you need.</p>\n"}, {'answer_id': 39593, 'author': 'Charles Roper', 'author_id': 1944, 'author_profile': 'https://Stackoverflow.com/users/1944', 'pm_score': 0, 'selected': False, 'text': "<p>@Mark Biek</p>\n\n<blockquote>\n  <p>Is MySQL replication an option? You could even turn it on and off if you didn't want it constantly replicating.</p>\n</blockquote>\n\n<p>Thanks for the suggestion, but I cannot enable replication on the server. It is a shared server with very little room for maneuver. I've updated the question to note this.</p>\n"}, {'answer_id': 39660, 'author': 'Brock Boland', 'author_id': 2185, 'author_profile': 'https://Stackoverflow.com/users/2185', 'pm_score': 0, 'selected': False, 'text': '<p>Depending on how often you need to copy down live data and how quickly you need to do it, installing <a href="http://www.phpmyadmin.net/" rel="nofollow noreferrer">phpMyAdmin</a> on both machines might be an option.  You can export and import DBs, but you\'d have to do it manually.  If it\'s a small DB (and it sounds like it is), and you don\'t need live data copied over too often, it might work well for what you need.</p>\n'}, {'answer_id': 39672, 'author': 'BlaM', 'author_id': 999, 'author_profile': 'https://Stackoverflow.com/users/999', 'pm_score': 4, 'selected': True, 'text': '<p>Since you can access your database remotely, you can use mysqldump from your windows machine to fetch the remote database. From commandline:</p>\n\n<pre><code>cd "into mysql directory"\nmysqldump -u USERNAME -p -h YOUR_HOST_IP DATABASE_TO_MIRROR &gt;c:\\backup\\database.sql\n</code></pre>\n\n<p>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.</p>\n\n<p>With a small database that should be fairly fast.</p>\n'}, {'answer_id': 886452, 'author': 'The Disintegrator', 'author_id': 92462, 'author_profile': 'https://Stackoverflow.com/users/92462', 'pm_score': 2, 'selected': False, 'text': "<p>Here's what I use. This dumps the database from the live server while uploads it to the local server.\n<code>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</code></p>\n\n<p>You can run this from a <strong>bat</strong> file. You can ever use a scheduled task.</p>\n"}]
['https://Stackoverflow.com/questions/39536', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1944/']