Synchronising MySQL Database

We have two computers connected to internet.
let one be called remote and one local.
A mysql server runs in the live remote database .


We want to synchronise the data in remote database tables to the data in local tables.

Current Strategy: we transfer the files from the remote database ( .ISM , .ISD ,.frm )  to the  data directory of local databse at restart the local database.

Problem: There has been instances of table corruption in local database.

Constraint: we don't want to log  updates of remote databse throucg -l option for perfofance concerns. We cannot bring down the remote mysql server.

Any Solutions : ?
Who is Participating?
ventolinConnect With a Mentor Commented:
try one of the following:

1. "update log" method of mirroring

tail -f nfs_mounted_update_log | mysql

2. Copying the file to the new machine

as long as you read-lock all the tables while the files are being copied (and as long as the byte-order and structure alignment is the same on the remote server), then this is perfectly safe. i.e. a trivial perl script would do the trick.

3. mirror daemon

write a small footprint, fast, protocol compliant daemon connected to "n" servers. The daemon could execute
INSERT and UPDATE statements on all servers and perform "round robin" SELECTs using one server at a time.

With that model, data integrity could be mantained and whenever a server goes down, the daemon would stop using it until the whole set is synchronized.

using rsync to synchronise the mysql files maybe, but I m not sure how it would work with mysql. Else apart from dumping the db files and reimporting them on the local machine I don t know... network mirroring is still alpha/beta stage.
Or then maybe sharing a disk between the two machines so they use the same disk, then you ll probably have lock problems with mysql, and it s no more synchronising 2 disks, and the performances will suffer.
I have similar problems with Acess, SQL Server, etc.

We've been looking over Internet and we found a software called PeerDirect that uses a temporal files to syncronyze the databases, it's very good, it can compress to make your sincronization faster.
All Courses

From novice to tech pro — start learning today.