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 .

Aim:

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 : ?
LVL 1
mallahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

edicomCommented:
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.
0
crojasCommented:
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.
0
ventolinCommented:
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.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ventolinCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.