mariadb star replication

Hello Techrunnr subscribers, this document deals with how to set up mariadb star replication.


This type of topology requires when the application runs in a different region. And our motive is to update data in all the servers on a real-time basis.

How does it work?
So here we are choosing around 9 servers in a different location. Out of these, we have a server which is placed in the center. In the initial configuration, the central server is marked as the slave for all the servers, which retrieves all the data from all the servers which are in different locations.

So any write operation in the any of the servers will be updated to the central server.
Next setup is to setup slave on all the servers except the central server, and the master for all the edge servers will be the central server.

So whatever update happened on the server will be pushed or replicated to all the edge location server.

Basically, if any updated in any of the edge location servers first will get updated to the central server and then pushed to all the other servers in the edge location.

So let’s start the configuration.

Prerequisites
  • ┬áRunning MariaDB server on all the servers
  • ┬áRoot access to all the servers to change the MariaDB configuration.
Configuration.

1. Change your my.cnf configuration accordingly.

On Central server

vi /etc/my.cnf

[mysqld]
server-id = 2
log_slave_updates = 1
log_bin = /var/lib/mysql/mysql-bin.log
binlog-format = row
expire_logs_days = 10
max_binlog_size = 100M
gtid_domain_id = 200
replicate-do-db=db

On Other nodes
vi /etc/my.cnf

[mysqld]
log_slave_updates = 0
bind-address=0.0.0.0
server-id = 3
log_bin = /var/lib/mysql/mysql-bin.log
binlog-format = row
expire_logs_days = 10
max_binlog_size = 100M
gtid_domain_id = 400
replicate-do-db=db

2. Restart the MySQL service

/etc/init.d/mysql restart

3. Create a common user in all your outer nodes for replication

CREATE USER 'newreplica'@'localhost' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'newreplica'@'' IDENTIFIED BY 'password';

4. Once you create the user, connect all your nodes from the central node.

CHANGE MASTER 'maria-master0' TO MASTER_HOST='xxxxxxxxx', MASTER_PORT=3306, MASTER_USER='newreplica', MASTER_PASSWORD='password';
CHANGE MASTER 'maria-master1' TO MASTER_HOST='xxxxxxxxx', MASTER_PORT=3306, MASTER_USER='newreplica', MASTER_PASSWORD='password';
CHANGE MASTER 'maria-master2' TO MASTER_HOST='xxxxxxxxx', MASTER_PORT=3306, MASTER_USER='newreplica', MASTER_PASSWORD='password';
CHANGE MASTER 'maria-master3' TO MASTER_HOST='xxxxxxxxx', MASTER_PORT=3306, MASTER_USER='newreplica', MASTER_PASSWORD='password';
CHANGE MASTER 'maria-master4' TO MASTER_HOST='xxxxxxxxx', MASTER_PORT=3306, MASTER_USER='newreplica', MASTER_PASSWORD='password';

etc

5. In the Central server, start the all the slave connection

start all slaves;

6. Check the slave connection status using the following command,

show all slaves status\G

 

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

7. Stop the slaves

Stop all slaves;

Now you have completed multi-source slave configuration and the topology looks like this,

So let’s do the second phase of the project.

8. Now create a replication user in central Node.

CREATE USER 'newreplica'@'localhost' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'newreplica'@'' IDENTIFIED BY 'password';

9. Now connect all your nodes to the central server, where it pushes all data to other end nodes.


CHANGE MASTER 'maria-master0' TO MASTER_HOST='xxxxxxxx', MASTER_PORT=3306, MASTER_USER='newreplica', MASTER_PASSWORD='password';

10. Start all the slaves in all server

start all slaves;

11. Check the status of replication

show all slaves status\G


Slave_IO_Running: Yes
Slave_SQL_Running: Yes

12. Create a database called DB, check whether its getting replicating in all servers.

create database DB;

Now you have completed the star replication of MariaDB server and Database server topology looks like this.

 

© 2018, Techrunnr. All rights reserved.

#1
#2
#3
Questions Answered
Articles Written
Overall Points

Prabhin Prabharkaran

He is Technical professional. He is a person who loves to share tricks and tips on the Internet. He Posts what he does!!

Leave a Reply

Please wait...

Subscribe to our newsletter

Want to be notified when our article is published? Enter your email address and name below to be the first to know.