mySQL DB replication?

[ankursaxena]

Hi, I want to know a little more about replication and the stuff on mysql.com is a little more detailed than I would like to know for now. I wanted a little overview, the capabilities and whats the reason for replication.
I have a distributed IP cellular system with a DB, it all works with individual DBs, but I would like to setup many distributed systems with couple of DB servers which these systems can access and also have a fall back system.

How do I acieve this? I need replication becuase all these systems are part of the same larger network and hence need to share data, but not all will be accessing the same DB server hence I need to have seperate DB servers.. How do i best do load balancing?

Thanks a ton for any titbits you can give me.

Thanx,
Ankur

 

[lgarner]

Pick up a copy of O'Reilly's "High Performance MySQL". It has a good chapter on replication. I think parts of it are even available on mysql.com.

There are issues relating to potential conflicts if you are writing to two servers at the same time. I think that a master-master setup is safe as long as you can be certain this will never happen. MySQL replication is not inherently "safe", and you risk data corruption if it ever gets out of sync. One way you could do it is to have separate databases and split the read/write functions between servers.

 

[Bastien]

It is available on mysql.com (well, parts of it anyway)

The companion site is

http://www.highperformancemysql.com

Bastien

Cat, the other other white meat

 

[ericbrunson]

I've never found good documentation of how to set up master-master replication for the same databases, nor have I found anything to indicate that it is reliable or recommended.

I have a server farm with a master, a backup master and many slaves. The slaves are all slaves to the backup master and the backup master is a slave of the master. All queries are done against the local slave database and all writes are done against the master.

If the backup master goes down, I simply move the backup's IP to the master and replication continues. When the backup is restored to function I sync it with the master, then give it its IP back.

If the master goes down, I move the IP of the master to the backup and everything continues until I get the master back up, then I make it a slave of the old backup, move the backup's ip to the old master and their roles are reversed.

It's a little more involved than that, but that's the gist.