Re: Replication Newbie

[sharding]

Yes, there has to be a master->slave relationship, however, each laptop can be both provided you run MySQL 4.1 (not sure if 4.0 supports what I am about to describe).

Set up each laptop as a master (bin-log on and unique server-id). Also set up each laptop as a slave with log-slave-updates on. We will name the laptops A, B and C. Set the computers in the following replication:

A -> B -> C -> A (this creates a circular replication scheme)

This way any change A makes goes to B that writes it to its binlog and replicates to C, C writes it to its binlong and A ignores it since it originated from A. The same applies to all other nodes.

The above will only work if the laptops are not removed from the network. By removing a node, replication will break down until the node is placed back into the network. If you remove A, then B cannot see C's changes, if you remove B then C cannot see A's changes, and if you remove C then A cannot see B's changes.

This will technically work, but some coordination on the users of the laptops is needed since if user A makes a change to Table 1, Row 1, then user B or C make a change to Table 1, Row 1; it will overwrite the change user A made (dependant on the timing of the changes of course; user A might well overwrite user B and C's changes).

I currently have 2 servers set up in circular replication so I know it works as long as you don't use things like auto increment fields that more than 1 node is using. If you need a unique ID, I would suggest the UUID function and if you need it as an integer try CRC32(UUID()), although the latter of the two may break down after generating more than 4 billion UUIDs since the return value of a CRC32 is 32-bit unsigned. Not really sure how that works since UUID is suppose to have a very high likelihood of uniqueness until like year 3500 or something like that.

Auto increments would be ok to use if only one node was generating them. So it depends on your setup and use of the database(s).

Hope this helps.