Creating a multimaster MariaDB replica

This topic will cover the process to create a two node Maria DB cluster. The cluster will be configured to allow Soffid console to use either database node, which in turn will replicate data changes to the other one.

Node 1 action

Node 2 action

Create and setup a Maria DB in node 1.

Configure Maria DB to generate binary log files. Add the following lines to /etc/mysql/my.cnf:

server-id = 1
log-bin
binlog-format=row
expire_logs_days = 15
max_binlog_size = 1000M
replicate-ignore-table = soffid.SC_SEQUENCE
slave-skip-errors = 1032,1053,1062

Restart MariaDB:

service mysql restart

Create and setup a Maria DB in node 2.

Configure Maria DB to generate binary log files. Add the following lines to /etc/mysql/my.conf:

server-id = 2
log-bin
binlog-format=row
expire_logs_days = 15
max_binlog_size = 1000M
replicate-ignore-table = soffid.SC_SEQUENCE
slave-skip-errors = 1032,1053,1062

Restart MariaDB:

service mysql restart

Dump current database contents:

mysqldump soffid -u soffid -p >soffid.data

Load current database contents

mysql -u soffid -p < soffid.data

Create a user for node 1 to fetch data from node 2. From mysql, execute:

grant replication slave on *.* to replication_user@<NODE1-IP>

set password for replication_user@1<NODE1-IP> = password('<NODE1-PASS>')

Create a user for node 2 to fetch data from node 1. From mysql, execute:

grant replication slave on *.* to replication_user@<NODE2-IP>

set password for replication_user@1<NODE2-IP> = password('<NODE2-PASS>')

Query current binary log position:

MariaDB [(none)]> show master status;

The result should look like this:

File	Position	Binlog_Do_DB	Binlog_Ignore_DB
mysqld-bin.000030	68175

The got values will be used on node 2 to start replica process.

Start replication from node 1 to node 2. From mysql, execute the following sentence, replacing proper values:

CHANGE MASTER TO
MASTER_HOST='<NODE1-IP>',
MASTER_USER='replication_user',
MASTER_PASSWORD='<NODE2-PASS>',
MASTER_PORT=3306,
MASTER_LOG_FILE='<NODE1-FILE>' , /** i.e. mysql-bin.000030 **/
MASTER_LOG_POS=<NODE1-POSITION>, /** i.e. 68175 **/
MASTER_CONNECT_RETRY=10;

Verify replica is working right, by executing

SHOW SLAVE STATUS \G

Check following lines:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Query current binary log position:

MariaDB [(none)]> show master status;

The result should look like this:

File	Position	Binlog_Do_DB	Binlog_Ignore_DB
mysqld-bin.000060	98325

The got values will be used on node 1 to start replica process.

Now, start replication from node 2 to node 1. From mysql, execute the following sentence, replacing proper values:

CHANGE MASTER TO
MASTER_HOST='<NODE2-IP>',
MASTER_USER='replication_user',
MASTER_PASSWORD='<NODE1-PASS>',
MASTER_PORT=3306,
MASTER_LOG_FILE='<NODE2-FILE>', /** i.e. mysql-bin.000060 **/
MASTER_LOG_POS=<NODE2-POSITION>, /** i.e. 98325 **/
MASTER_CONNECT_RETRY=10;

Verify replica is working right, by executing

SHOW SLAVE STATUS \G

Check following lines:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Now, create and start SC_SEQUENCE table in node 1. This sequence will generate values 1, 11, 21, 31, 41, and so on:

CREATE TABLE `SC_SEQUENCE` (
`SEQ_NEXT` bigint(20) NOT NULL,
`SEQ_CACHE` bigint(20) NOT NULL,
`SEQ_INCREMENT` bigint(20) NOT NULL

);

INSERT INTO SC_SEQUENCE VALUES (1, 100, 10);

Now, create and start SC_SEQUENCE table in node 2. This sequence will generate values 2, 12, 22, 32, 42, and so on::

CREATE TABLE `SC_SEQUENCE` (
`SEQ_NEXT` bigint(20) NOT NULL,
`SEQ_CACHE` bigint(20) NOT NULL,
`SEQ_INCREMENT` bigint(20) NOT NULL

);

INSERT INTO SC_SEQUENCE VALUES (2, 100, 10);

Now, configure the Console to use the following jdbc URL:

jdbc:mariadb:sequential://mariadb-host-1,mariadb-host-2/soffid