I took a look at how you would go about setting up a replication topology such as A->B->C (Server A replicating to Server B, Server B replicating to Server C). Specifically how to handle failures in the chain, choose a new master etc. Heres a example using the current replication implementation:
Setup for Server A
drizzled –datadir=/home/jdaly/drizzle/repos/inno_repl/tests/serverA –innodb.replication-log –-server-id=1 –drizzle-protocol.port=9307 –mysql-protocol.port=3307
Setup for Server B
drizzled –datadir=/home/jdaly/drizzle/repos/inno_repl/tests/serverB –server-id=2 –innodb.replication-log –plugin-add=slave –slave.config-file=/home/jdaly/drizzle/repos/inno_repl/tests/serverB/slave.config –drizzle-protocol.port=9308 –mysql-protocol.port=3308
slave.config
master-host=127.0.0.1
master-port=9307
master-user=test
master-pass=me
max-reconnects=1000
seconds-between-reconnects=1
Setup for Server C
drizzled –datadir=/home/jdaly/drizzle/repos/inno_repl/tests/serverC –server-id=3 –innodb.replication-log –plugin-add=slave –slave.config-file=/home/jdaly/drizzle/repos/inno_repl/tests/serverC/slave.config –drizzle-protocol.port=9309 –mysql-protocol.port=3309
slave.config
master-host=127.0.0.1
master-port=9308
master-user=test
master-pass=me
max-reconnects=1000
seconds-between-reconnects=1
Pretty easy to setup, now lets look at the data on Server A compared to Server C in the replication tables.
Server A
drizzle> create database serverA;
drizzle> select ID, SEGID, COMMIT_ID, END_TIMESTAMP from DATA_DICTIONARY.SYS_REPLICATION_LOG;
+------+-------+-----------+------------------+
| ID | SEGID | COMMIT_ID | END_TIMESTAMP |
+------+-------+-----------+------------------+
| 772 | 1 | 1 | 1300842502563167 |
| 818 | 1 | 2 | 1300842824136908 |
+------+-------+-----------+------------------+
Server C
drizzle> select ID, SEGID, COMMIT_ID, END_TIMESTAMP from DATA_DICTIONARY.SYS_REPLICATION_LOG;
+------+-------+-----------+------------------+
| ID | SEGID | COMMIT_ID | END_TIMESTAMP |
+------+-------+-----------+------------------+
| 772 | 1 | 1 | 1300842759914337 |
| 819 | 1 | 2 | 1300842830416566 |
+------+-------+-----------+------------------+
This looks pretty straightforward if Server B were to fail, to point Server C at Server A. They both have two entries and both have a commit_id of 2. However this only works in this very simple example. If a backup was restored and replication enabled, or if Server C was stopped and restarted the entries would not be ordered so nicely. Both these events would throw the two tables out of sync. So you would be back to looking at the actual SQL statement that was last to execute on Server C to find where it matched in Server A.
To remedy this problem I added two new columns to the SYS_REPLICATION_LOG table, ORIGINATING_SERVER_ID and ORIGINATING_ID. These are the ID and SERVER_ID for the server that originally applied the changes. The table now looks like:
drizzle> describe DATA_DICTIONARY.SYS_REPLICATION_LOG;
+-----------------------+---------+------+---------+-----------------+-----------+
| Field | Type | Null | Default | Default_is_NULL | On_Update |
+-----------------------+---------+------+---------+-----------------+-----------+
| ID | BIGINT | YES | | NO | |
| SEGID | INTEGER | YES | | NO | |
| COMMIT_ID | BIGINT | YES | | NO | |
| END_TIMESTAMP | BIGINT | YES | | NO | |
| ORIGINATING_SERVER_ID | INTEGER | YES | | NO | |
| ORIGINATING_ID | BIGINT | YES | | NO | |
| MESSAGE_LEN | INTEGER | YES | | NO | |
| MESSAGE | BLOB | YES | | NO | |
+-----------------------+---------+------+---------+-----------------+-----------+
On Server A this looks like:
drizzle> create database serverA;
drizzle> select ID, SEGID, COMMIT_ID, END_TIMESTAMP, ORIGINATING_SERVER_ID, ORIGINATING_ID from DATA_DICTIONARY.SYS_REPLICATION_LOG;
+------+-------+-----------+------------------+-----------------------+----------------+
| ID | SEGID | COMMIT_ID | END_TIMESTAMP | ORIGINATING_SERVER_ID | ORIGINATING_ID |
+------+-------+-----------+------------------+-----------------------+----------------+
| 772 | 1 | 1 | 1300839966870903 | 1 | 772 |
| 861 | 1 | 2 | 1300840709527015 | 1 | 861 |
+------+-------+-----------+------------------+-----------------------+----------------+
On Server B this looks like:
drizzle> select ID, SEGID, COMMIT_ID, END_TIMESTAMP, ORIGINATING_SERVER_ID, ORIGINATING_ID from DATA_DICTIONARY.SYS_REPLICATION_LOG;
+------+-------+-----------+------------------+-----------------------+----------------+
| ID | SEGID | COMMIT_ID | END_TIMESTAMP | ORIGINATING_SERVER_ID | ORIGINATING_ID |
+------+-------+-----------+------------------+-----------------------+----------------+
| 772 | 1 | 1 | 1300840469433946 | 2 | 772 |
| 855 | 1 | 2 | 1300840715001766 | 1 | 861 |
+------+-------+-----------+------------------+-----------------------+----------------+
On Server C this looks like:
drizzle> select ID, SEGID, COMMIT_ID, END_TIMESTAMP, ORIGINATING_SERVER_ID, ORIGINATING_ID from DATA_DICTIONARY.SYS_REPLICATION_LOG;
+------+-------+-----------+------------------+-----------------------+----------------+
| ID | SEGID | COMMIT_ID | END_TIMESTAMP | ORIGINATING_SERVER_ID | ORIGINATING_ID |
+------+-------+-----------+------------------+-----------------------+----------------+
| 772 | 1 | 1 | 1300840574916708 | 3 | 772 |
| 833 | 1 | 2 | 1300840715511684 | 1 | 861 |
+------+-------+-----------+------------------+-----------------------+----------------+
The last entry in each SYS_REPLICATION_LOG on all three servers has the same values for ORIGINATING_SERVER_ID and ORIGINATING_ID. Note: The first entry is a startup event and is unique to each server. How is this helpful? If Server B were to fail and Server C needed to be pointed to a new master it would be as simple as running this query on Server A:
select commit_id FROM DATA_DICTIONARY.SYS_REPLICATION_LOG WHERE originating_id= 861;
Then restarting Server C with that COMMIT_ID position.
This is still in design heavily, walking through this example it may be better to have the ORIGINATING_COMMIT_ID rather then ORIGINATING_ID in the table. I think that would then allow just reconfiguring the slave to point at the new master. The example also illustrates the need for more on the fly configuration such as configuring a slave to point to a different master without restarting it. The code for this is at:
lp:~skinny.moey/drizzle/hierarch_repl
Comments and design suggestions are more then welcome!