How to keep databases synchronized between hosting account and a local testing server?

Question

I have several databases hosted on a shared server, and a local testing server which I use for development.

I would like to keep both set of databases somewhat synchronized (more or less daily).

So far, my ideas to solve the problem seem very clumsy. Anyway, for reference, here is what I have considered so far:

1. Make a database dump from online databases, trash local databases, and recreate the databases from the dump. It's a lot of work and requires a lot of download time (which guarantees I won't do it as much as I would like it to be done)

2. Write a small web service to access the new data, and write a small application locally to communicate with said web service, download the newest data, and update the local databases.

Both solutions sound like a lot of work for a problem that is probably already solved a zillion times over. Or maybe it's even an existing feature which I completely overlooked.

Is there an easy way to keep databases more or less in synch? Ideally something that I can set up once, schedule and forget about.

I am using MySQL 5 (MyISAM) databases on both servers.

=============

Edit: I had a look at replication, but it seems that I can't go that route because the shared hosting does not give me enough control on the server itself (I got most permissions on my databases, but not on the MySQL server itself)

I only need to keep the data synchronized, nothing else. Is there any other solution that doesn't require full control on the server?

---

Edit 2: Sorry, I forgot to mention I am running on a LAMP stack on the shared server, so Windows-only solutions won't work.

I am surprised to see that there is no obvious off-the-shelves solution for this problem.

Answer 1

Have you considered replication? It's not to be trifled with but may be what you want. See here for more details... http://dev.mysql.com/doc/refman/5.0/en/replication-configuration.html

Answer 2

Take a look at Microsoft Sync Framework - you will need to code in .net, but it can resolve your issues.

http://msdn.microsoft.com/en-in/sync/default(en-us).aspx

Here is a sample for SQL server, but it can be adapted to mysql as well using ado.net provider for Mysql.

http://code.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=4835

You will need the additional tables for change tracking and anchors (keeping track of last synchronization) for this to work, in your mysql database, but you wont need full control as long as you can access the db.

Replication would have simpler :), but this might just work in your case.