Multimaster DB configaration

shafeeq wrote this on 26 Oct 2011

Tungsten Replicator is a high performance, open source, data replication engine for MySQL. It offers a set of features that surpass any open source replicator available today: global transaction IDs to support fail-over, flexible transaction filtering, extensible transaction meta data, sharding, multiple replication services per process, high performance, and simple, well-documented operation.

Tungsten Replicator helps technically focused users solve problems like promoting masters easily from pools of slaves and masters, replicating data between different database versions, replicating efficiently across sites, building complex topologies, like two,three and four master-master and parallelizing data flow between servers. Tungsten Replicator runs equally well in cloud as well as locally hosted environments.

Operations like master switch can be performed from any node, and quite easily. More notable features are:

Parallel replication;
Multiple source replication;
Bi-directional replication;
Filters.Let us see how to configure multi-master installation

Steps to install four master replications using Tungsten Replicator

Four nodes, in such a way that each node is a master, and each one receives changes from all other

Tungsten replicator does not take care conflicts, we need to take conflicts to avoid data conflicts,
In MySQL Server use auto_increment_increment , auto_increment_offset to avoid conflicts, set these values in all servers as follows,

# MASTER1
# add in my.cnf for global scope, 100 is because DB scaling capacity can go up to 100 DB servers
[mysqld]
# Master replication settings.
server-id=1
auto_increment_increment = 100
auto_increment_offset = 1

# MASTER2
# add in my.cnf
[mysqld]
# MAster replication settings
server-id=2
auto_increment_increment = 100
auto_increment_offset = 2

# MASTER3
# add in my.cnf
[mysqld]
# MAster replication settings.
server-id=3
auto_increment_increment = 100
auto_increment_offset = 3

# MASTER4
# add in my.cnf
[mysqld]
# MAster replication settings.
server-id=4
auto_increment_increment = 100
auto_increment_offset = 4

here auto_increment_increment = 100 is used because up to 100 paraller databases can be added, master server are scalabe up to 100 MASTER-MASTER servers.

As in bi-directional replication, you install the master services first.

TUNGSTEN_HOME=$HOME/replication
MASTER1=m1.mynetwork.com
MASTER2=m2.mynetwork.com
MASTER3=m3.mynetwork.com
MASTER4=m4.mynetwork.com

./tools/tungsten-installer \
-master-slave \
-master-host=$MASTER1 \
-datasource-user=tungsten \
-datasource-password=secret \
-service-name=alpha \
-home-directory=/home/tungsten/installs/four_masters \
-cluster-hosts=$MASTER1 -start-and-report

./tools/tungsten-installer \
-master-slave \
-master-host=$MASTER2 \
-datasource-user=tungsten \
-datasource-password=secret \
-service-name=alpha \
-home-directory=/home/tungsten/installs/four_masters \
-cluster-hosts=$MASTER2 -start-and-report

./tools/tungsten-installer \
-master-slave \
-master-host=$MASTER3 \
-datasource-user=tungsten \
-datasource-password=secret \
-service-name=alpha \
-home-directory=/home/tungsten/installs/four_masters \
-cluster-hosts=$MASTER3 -start-and-report

./tools/tungsten-installer \
-master-slave \
-master-host=$MASTER4 \
-datasource-user=tungsten \
-datasource-password=secret \
-service-name=alpha \
-home-directory=/home/tungsten/installs/four_masters \
-cluster-hosts=$MASTER4 –start-and-report

Next, you will install three slave services for each master.

In Master 1

TUNGSTEN_TOOLS=$TUNGSTEN_HOME/tungsten/tools

# MASTER 1

$TUNGSTEN_TOOLS/configure-service \
-C -quiet \
-host=$MASTER1 \
-datasource=m1_mynetwork_com \
-local-service-name=alpha \
-role=slave \
-service-type=remote \
-release-directory=$TUNGSTEN_HOME/tungsten \
-master-thl-host=$MASTER2 \
-svc-start bravo

In Master2

# MASTER 2

$TUNGSTEN_TOOLS/configure-service \
-C -quiet \
-host=$MASTER2 \
-datasource=m2_mynetwork_com \
-local-service-name=bravo \
-role=slave \
-service-type=remote \
-release-directory=$TUNGSTEN_HOME/tungsten \
-master-thl-host=$MASTER1 \
-svc-start alpha

And master 3:

# MASTER 3

$TUNGSTEN_TOOLS/configure-service \
-C -quiet \
-host=$MASTER3 \
-datasource=m3_mynetwork_com \
-local-service-name=charlie \
-role=slave \
-service-type=remote \
-release-directory=$TUNGSTEN_HOME/tungsten \
-master-thl-host=$MASTER1 \
-svc-start alpha

$TUNGSTEN_TOOLS/configure-service \
-C -quiet \
-host=$MASTER4 \
-datasource=m3_mynetwork_com \
-local-service-name=bravo \
-role=slave \
-service-type=remote \
-release-directory=$TUNGSTEN_HOME/tungsten \
-master-thl-host=$MASTER4 \
-svc-start delta

And finally master 4:

# MASTER 4

$TUNGSTEN_TOOLS/configure-service \
-C -quiet \
-host=$MASTER4 \
-datasource=m4_mynetwork_com \
-local-service-name=delta \
-role=slave \
-service-type=remote \
-release-directory=$TUNGSTEN_HOME/tungsten \
-master-thl-host=$MASTER1 \
-svc-start alpha

$TUNGSTEN_TOOLS/configure-service \
-C -quiet \
-host=$MASTER4 \
-datasource=m4_mynetwork_com \
-local-service-name=bravo \
-role=slave \
-service-type=remote \
-release-directory=$TUNGSTEN_HOME/tungsten \
-master-thl-host=$MASTER2 \
-svc-start bravo

The procedure is longish, but once you get the gist of it you will be able to make a loop instead of coding these commands manually.

# create sample table in any one of the DB, it will propagate to all MASTER DBs.
CREATE TABLE `products` (
`id` bigint(15) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`quantity` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=latin1;
insert into products (name,quantity) values(‘master1′,’100′);
insert into products (name,quantity) values(‘master2′,’200′);
insert into products (name,quantity) values(‘master3′,’300′);

CREATE TABLE `items` (
`id` bigint(15) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`quantity` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=latin1;

insert into items (name,quantity) values(‘master1′,’100′);
insert into items (name,quantity) values(‘master2′,’200′);
insert into items (name,quantity) values(‘master3′,’300′);

CREATE TABLE `employees` (
`id` bigint(15) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`salary` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=latin1;

insert into employees (name,salary) values(‘master1′,’100000′);
insert into employees (name,salary) values(‘master2′,’200000′);
insert into employees (name,salary) values(‘master3′,’300000′);

Visit us at Neevtech.com to know more about our offerings.

facebook comments:

Multimaster DB configaration

Leave a Comment