This is a simple mysql circular replication implementation on a single machine (just a proof of concept) which can easily be done on a broader scale. Just be aware of the cons of circular replication which mainly gets down to: once a node freaks out or stops for one reason or the other, it’s a bitch and you need to take care of IMMEDIATELY.

Download Sandbox from https://launchpad.net/mysql-sandbox/+download
Download MySQL from http://dev.mysql.com/downloads

Copy the downloaded software onto the your *nix box onto any folder of your preference called $BASEDIR

run:

cd /$BASEDIR

gunzip mysql_sandbox_X.X.XX.tar.gz
tar -xf mysql_sandbox_X.X.XX.tar

ln -s mysql_sandbox_X.X.XX sandbox

time /$BASEDIR/sandbox/make_replication_sandbox –circular=4 –topology=circular /$BASEDIR/mysql-5.1.30-linux-x86_64-glibc23.tar.gz


user@hostname $ time /$BASEDIR/sandbox/make_replication_sandbox --circular=4 --topology=circular /$BASEDIR/mysql-5.1.30-linux-x86_64-glibc23.tar.gz
installing node 1
installing node 2
installing node 3
installing node 4
# server: 1:
# server: 2:
# server: 3:
# server: 4:
# server: 1:
# server: 2:
# server: 3:
# server: 4:
Circular replication activated
group directory installed on /$BASEDIR/sandboxes/rcsandbox_5_1_30

real 1m47.913s
user 0m6.364s
sys 0m2.445s

cd /$BASEDIR/sandboxes/rcsandbox_5_1_30

user@hostname $ ./n1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node1 [localhost] {msandbox} ((none)) > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)

node1 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17001 |
+---------------+-------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} ((none)) > exit
Bye
user@hostname $ ./n2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node2 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17002 |
+---------------+-------+
1 row in set (0.00 sec)

node2 [localhost] {msandbox} ((none)) > exit
Bye
user@hostname $ ./n3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node3 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17003 |
+---------------+-------+
1 row in set (0.00 sec)

node3 [localhost] {msandbox} ((none)) > exit
Bye
user@hostname $ ./n4
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node4 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17004 |
+---------------+-------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} ((none)) > exit
Bye

user@hostname $ cd ..
user@hostname $ ./n1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node1 [localhost] {msandbox} ((none)) > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

node1 [localhost] {msandbox} ((none)) > create database dba;
Query OK, 1 row affected (0.04 sec)

node1 [localhost] {msandbox} ((none)) > USE dba;
Database changed
node1 [localhost] {msandbox} (dba) > CREATE TABLE `instance` (
-> `dbid` tinyint unsigned NOT NULL,
-> `hostname` varchar(255) NOT NULL,
-> `port` smallint unsigned NOT NULL,
-> PRIMARY KEY (`dbid`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

node1 [localhost] {msandbox} (dba) > CREATE TABLE `backups` (
-> `instance` tinyint unsigned NOT NULL,
-> `dbname` varchar(255) NOT NULL,
-> `date_time` datetime NOT NULL,
-> `dumpsize` varchar(16) NOT NULL,
-> `checksum` varchar(20) NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

node1 [localhost] {msandbox} (dba) > CREATE TABLE `table_size` (
-> `instance` tinyint unsigned NOT NULL,
-> `dbname` varchar(255) NOT NULL,
-> `tbname` varchar(255) NOT NULL,
-> `size_of_data` decimal(18,0) NOT NULL,
-> `size_of_index` decimal(18,0) NOT NULL,
-> `total` decimal(18,0) NOT NULL,
-> `date_time` datetime NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

node1 [localhost] {msandbox} (dba) > CREATE TABLE `memory` (
-> `instance` tinyint unsigned NOT NULL,
-> `date_time` datetime NOT NULL,
-> `size` decimal(18,0) NOT NULL,
-> `rss` decimal(18,0) NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

node1 [localhost] {msandbox} (dba) > exit
Bye
user@hostname $ ./n4
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node4 [localhost] {msandbox} ((none)) > use dba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
node4 [localhost] {msandbox} (dba) > CREATE TABLE `connectivity` (
-> `instance` tinyint unsigned NOT NULL,
-> `date_time` datetime NOT NULL,
-> `established` smallint unsigned NOT NULL,
-> `listen` smallint unsigned NOT NULL,
-> `close` smallint unsigned NOT NULL,
-> `time_wait` smallint unsigned NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

node4 [localhost] {msandbox} (dba) > show tables;
+---------------+
| Tables_in_dba |
+---------------+
| backups |
| connectivity |
| instance |
| memory |
| table_size |
+---------------+
5 rows in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > select * from memory;
Empty set (0.00 sec)

node4 [localhost] {msandbox} (dba) > show create table memory;
+--------+---------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------+
| memory | CREATE TABLE `memory` (
`instance` tinyint(3) unsigned NOT NULL,
`date_time` datetime NOT NULL,
`size` decimal(18,0) NOT NULL,
`rss` decimal(18,0) NOT NULL,
KEY `instance` (`instance`),
CONSTRAINT `memory_ibfk_1` FOREIGN KEY (`instance`) REFERENCES `instance` (`dbid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > insert into instance (dbid,hostname,port) values ('1','machine1','17001');
Query OK, 1 row affected (0.00 sec)

node4 [localhost] {msandbox} (dba) > select * from instance;
+------+----------+-------+
| dbid | hostname | port |
+------+----------+-------+
| 1 | machine1 | 17001 |
+------+----------+-------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > insert into memory (instance,date_time,size,rss) values('1',now(),'42252431','2543234');
Query OK, 1 row affected (0.00 sec)

node4 [localhost] {msandbox} (dba) > select * from memory;
+----------+---------------------+----------+---------+
| instance | date_time | size | rss |
+----------+---------------------+----------+---------+
| 1 | 2009-01-28 16:27:00 | 42252431 | 2543234 |
+----------+---------------------+----------+---------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > exit
Bye
user@hostname $ ./n2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node2 [localhost] {msandbox} ((none)) > use dba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
node2 [localhost] {msandbox} (dba) > select * from memory;
+----------+---------------------+----------+---------+
| instance | date_time | size | rss |
+----------+---------------------+----------+---------+
| 1 | 2009-01-28 16:27:00 | 42252431 | 2543234 |
+----------+---------------------+----------+---------+
1 row in set (0.00 sec)

node2 [localhost] {msandbox} (dba) > show tables;
+---------------+
| Tables_in_dba |
+---------------+
| backups |
| connectivity |
| instance |
| memory |
| table_size |
+---------------+
5 rows in set (0.00 sec)

node2 [localhost] {msandbox} (dba) > exit
Bye
user@hostname $ ./check_slaves
node # 1
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
node # 2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
node # 3
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
node # 4
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

VN:F [1.9.22_1171]
Rating: 4.5/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Circular Replication Implementation / Testing using MySQL Sandbox, 4.5 out of 10 based on 2 ratings

*