First We need to make MySQL Cluster with Master-Master synchronization
Let's Assume mentioned below Server's Details.
MySQL Server1:
Server IP : 192.168.0.1
hostname : mysqldb1
MySQL Server2:
Server IP : 192.168.0.2
hostname : mysqldb2
Add below mentioned configuration Under mysqld context for mariadb server for replication.
mysqldb1:~
[mysqld]
bind-address = 192.168.0.1
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
- And create replicauser for replication from other server under cluster with pre-defined authentication.
mysqldb1:~
mysql -uroot -p -e "GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'192.168.0.2' IDENTIFIED BY 'replica';"
mysql -uroot -p -e "FLUSH PRIVILEGES;"
mysqldb2:~
[mysqld]
bind-address = 192.168.0.2
server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
- And create replicauser for replication from other server under cluster with pre-defined authentication.
mysqldb1:~
mysql -uroot -p -e "GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'192.168.0.1' IDENTIFIED BY 'replica';"
mysql -uroot -p -e "FLUSH PRIVILEGES;"
- And Restart mariadb Service on both server.
mysqldb1:~
systemctl restart mariadb
mysqldb2:~
systemctl restart mariadb
On Server1(mariadb1):
- Stop Mariadb SLAVE and show master status and update the slave configuration on server 2 for authentication and further replication.
mariadb1:~
MariaDB> STOP SLAVE;
MariaDB> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 531 | | |
+------------------+----------+--------------+------------------+
On Server2(mariadb2):
mariadb2:~
MariaDB> STOP SLAVE;
MariaDB> CHANGE MASTER TO master_host='192.168.0.1', master_port=3306, master_user='replicauser', master_password='replica', master_log_file='mysql-bin.000002', master_log_pos=531;
MariaDB> START SLAVE;
MariaDB> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 3532 | | |
+------------------+----------+--------------+------------------+
On Server1(mariadb1):
mariadb1:~
MariaDB> STOP SLAVE;
MariaDB> CHANGE MASTER TO master_host='192.168.0.2', master_port=3306, master_user='replicauser', master_password='replica', master_log_file='mysql-bin.000004', master_log_pos=3532;
MariaDB> START SLAVE;
- Further data created on mariadb1 or mariadb2 data will synchronize accordingly.