CISNS
Home

Local documentation
MySQL dual master
Cable pinouts
X509 certificates

This is relatively straightforward way of getting a dual MySQL master setup up and running. There are many areas which should be tuned for your needs which are not covered, refer to the MySQL documentation for further details. Paths for configs, binaries, and sockets may vary from system to system; always verify.

This example assumes that MySQL up and running on each host and network the MySQL port (default 3306) is accessible from each host to the other.

Setup the my.cnf on each server:
mysqlhost1: /etc/my.cnf

[mysqld]
socket=/tmp/mysql.sock
user=mysql
symbolic-links=0
server-id = 1
log_bin = /usr/local/mysql/data/mysql-bin.log
max_binlog_size = 100M
auto_increment_increment = 2
auto_increment_offset = 1
replicate-do-db = globaldb1
replicate-do-db = globaldb2

Now the other one
mysqlhost2: /etc/my.cnf

[mysqld]
socket=/tmp/mysql.sock
user=mysql
symbolic-links=0
server-id = 2
log_bin = /usr/local/mysql/data/mysql-bin.log
max_binlog_size = 100M
auto_increment_increment = 2
auto_increment_offset = 2
replicate-do-db = globaldb1
replicate-do-db = globaldb2

Create a user for replication on each server: mysqlhost1, mysqlhost2

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to replica identified by 'm4st3rp4$$';
Set the master on mysqlhost1:
mysqlhost1@mysql> CHANGE MASTER TO MASTER_HOST='mysqlhost2',MASTER_USER='replica',MASTER_PASSWORD='m4st3rp4$$';

Now do the exact same thing for mysqlhost2, note the hostname changes

mysqlhost2@mysql> CHANGE MASTER TO MASTER_HOST='mysqlhost1',MASTER_USER='replica',MASTER_PASSWORD='m4st3rp4$$';

Start the slave process on each server: mysqlhost1, mysqlhost2

mysql> slave start;

Check the status:

mysql> show slave status;

On either host, create a table:

mysql> create database globaldb1;
mysql> use globaldb1;
mysql> create table test1 (field1 char(16), field2 char(16), field3 char(16));
mysql> create table test2 (field1 char(16), field2 char(16), field3 char(16));
mysql> create table test3 (field1 char(16), field2 char(16), field3 char(16));

and see them show up on the other host:

mysql> use globaldb1;
mysql> show tables;

That's it, you're done. The magic happens due to auto_increment_increment and auto_increment_offset in /etc/my.cnf. Read the official documentation for a detailed description.