Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

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 actionNode 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
 
 

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:

FilePositionBinlog_Do_DBBinlog_Ignore_DB
mysqld-bin.00003068175  

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:

FilePositionBinlog_Do_DBBinlog_Ignore_DB
mysqld-bin.00006098325  

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);

 
  • No labels