ONLamp.com
oreilly.comSafari Books Online.Conferences.

advertisement


Advanced MySQL Replication Techniques
Pages: 1, 2, 3, 4, 5, 6

Adding more nodes

Having just two nodes could be what you need today, but as your application grows and you need to scale your database environment, you will need to add more nodes into the mix. It's easy to extend this array to use four nodes (Figure 2).



Circular replication with four nodes
Figure 2. Circular replication with four nodes

In this broader schema, water is the master of air and slave of earth, which is slave of fire; and this last is in turn slave of air, thus completing the circle. The boxed numbers next to each server indicate the server ID, which must be different for each node; the auto_increment_increment, the same for all the nodes; and the auto_increment_offset, which guarantees the uniqueness of self-generated keys.

Here is the complete setup for all nodes:

# node A - water
[mysqld]
server-id                       = 10
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 1
master-host                     = earth.stardata.it
master-user                     = nodeAuser
master-password                 = nodeApass
report-host                     = nodeA

# Node B - air
[mysqld]
server-id                       = 20
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 2
master-host                     = water.stardata.it
master-user                     = nodeBuser
master-password                 = nodeBpass
report-host                     = nodeB

# Node C - fire
[mysqld]
server-id                       = 30
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 3
master-host                     = air.stardata.it
master-user                     = nodeCuser
master-password                 = nodeCpass
report-host                     = nodeC

# Node D - earth
[mysqld]
server-id                       = 40
log-bin                         = mysql-bin
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 10
auto_increment_offset           = 4
master-host                     = fire.stardata.it
master-user                     = nodeDuser
master-password                 = nodeDpass
report-host                     = nodeD

A few variables are worth noting in these configuration files. The first is log-slave-updates. This option tells each server to write the changes that it receives from its master through the relay binary log to its own binary log. Without it, cascade replication doesn't work. The option replicate-same-server-id has the purpose of avoiding infinite replication loops, effectively telling each node to ignore from its master's binary log any statement that originated with its own server ID.

auto_increment_increment and auto_increment_offset have the appropriate values, as explained earlier. The rest is normal replication administration.

Here's an example of independent usage:

[node A] stop slave;
[node B] stop slave;
[node C] stop slave;
[node D] stop slave;
[node A] insert into test.x values (null, 'a'), (null, 'aa'), (null, 'aaa');
[node B] insert into test.x values (null, 'b'), (null, 'bb'), (null, 'bbb');
[node C] insert into test.x values (null, 'c'), (null, 'cc'), (null, 'ccc');
[node D] insert into test.x values (null, 'd'), (null, 'dd'), (null, 'ddd');

With the replication stopped, enter in each node three records with independently generated keys. The result is a set of nonconflicting records.

[node A] select * from test.x;
+----+------+
| id | c    |
+----+------+
| 1  | a    |
| 11 | aa   |
| 21 | aaa  |
+----+------+
[node B] select * from test.x;
+----+------+
| id | c    |
+----+------+
| 2  | b    |
| 12 | bb   |
| 22 | bbb  |
+----+------+
[node C] select * from test.x;
+----+------+
| id | c    |
+----+------+
| 3  | c    |
| 13 | cc   |
| 23 | ccc  |
+----+------+
[node D] select * from test.x;
+----+------+
| id | c    |
+----+------+
| 4  | d    |
| 14 | dd   |
| 24 | ddd  |
+----+------+

When you resume the replication flow, the table contents replicate everywhere:

[node A] start slave;
[node B] start slave;
[node C] start slave;
[node D] start slave;
[node A] select * from test.x;
+----+------+
| id | c    |
+----+------+
| 1  | a    |
| 11 | aa   |
| 21 | aaa  |
| 4  | d    |
| 14 | dd   |
| 24 | ddd  |
| 3  | c    |
| 13 | cc   |
| 23 | ccc  |
| 2  | b    |
| 12 | bb   |
| 22 | bbb  |
+----+------+

[node B] select count(*) from test.x;
+----------+
| count(*) |
+----------+
| 12       |
+----------+

[node C] select count(*) from test.x;
+----------+
| count(*) |
+----------+
| 12       |
+----------+

[node D] select count(*) from test.x;
+----------+
| count(*) |
+----------+
| 12       |
+----------+

Of course, if you want to break it, circular replication is as fragile as normal replication when comes to conflicting keys. Inserting the same non-auto-generated primary or unique key in two different nodes will disrupt replication just as well as it does it in normal master-slave replication. With asynchronous replication, this can happen, although you need to be particularly unlucky for this fact to occur. A policy of good programming practice will avoid most of the problems (short of communication failure, that is).

The two important issues here are that you can use circular replication almost seamlessly in any application that now uses a single database server, and that performance is adequate for our purposes.

Pages: 1, 2, 3, 4, 5, 6

Next Pagearrow





Sponsored by: