Convert MariaDB/MySQL server into master/master replicated cluster

Published on Mar 30 2022 in Databases Non-Java

Master/master replication is one of the possible replication modes in MySQL/MariaDB servers. In this article we will document converting single instance MySQL server into master/master cluster. Afterwards data can be read/written from/to any cluster member. Local network and IPs can be used for limiting access by replication dedicated user.

Existing setup

The original server is called MASTER1 and the newly added one (to form cluster) will be called MASTER2. MASTER1’s IP is 10.2.0.1. MASTER2’s IP will be 10.2.0.2. Server OS was CentOS 7.

Prerequisites and preparations

Check and repair databases on MASTER1 with mysqlcheck -A --auto-repair.
Enable communication between the 2 IPS in both server firewalls if applicable.
To facilitate the setup place MySQL root’s credentials in ~/.my.cnf.
Enable SSH key-based login from MASTER1 to MASTER2 as root (you can disable it at the end - it is just to ease the setup).
Copy /etc/my.cnf and /etc/my.cnf.d/* contents from MASTER1 to MASTER2 so that the servers have the same config. You may also review the files and only port the differences to MASTER2.

In case you made a mistake and want to repeat the procedure with pristine MASTER2 you can reset MariaDB this way:

systemctl stop mariadb
rm -f /etc/my.cnf.d/replication.cnf
rm -rf /var/lib/mysql/*
mysql_install_db
chown -R mysql: /var/lib/mysql
systemctl start mariadb
/usr/bin/mysql_secure_installation  # you may leave empty root password as it will be replaced in a moment with MASTER1's one.

Replication specific config

Now generate replication config for MASTER1 (we use command line throughout the tutorial). You can uncomment bind-address if the IPs mentioned will be the only ones used to access the servers.

IP1=10.2.0.1
MASTER=1
cat >/etc/my.cnf.d/replication.cnf<<EOF
[mariadb]
#bind-address  = $IP1
server-id        = $MASTER
report_host    = master$MASTER
log_bin          = /var/lib/mysql/mysql-bin.log
relay_log       = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index
expire_logs_days = 14

log-slave-updates
auto_increment_increment=2
auto_increment_offset=$MASTER
EOF
systemctl restart mariadb

Note above we enabled binlogging required also for --master-data argument of consequent mysqldump. Repeat it on MASTER2.

IP2=10.2.0.2
MASTER=2
cat >/etc/my.cnf.d/replication.cnf<<EOF
[mariadb]
#bind-address  = $IP2
server-id        = $MASTER
report_host    = master$MASTER
log_bin          = /var/lib/mysql/mysql-bin.log
relay_log       = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index
expire_logs_days = 14

log-slave-updates
auto_increment_increment=2
auto_increment_offset=$MASTER
EOF
systemctl restart mariadb

User with replication privileges on MASTER1

Both masters are not connected yet. We will need users with replication privileges. Run below on MASTER1.

REPLICAUSER=replicator
REPLICAPASS=a__secret__here
if [[ `mysql -Bse "select count(*) from mysql.user where user='replicator'"` -eq 0 ]]; then
    mysql -e "CREATE USER '$REPLICAUSER'@'%' IDENTIFIED BY '$REPLICAPASS'; GRANT REPLICATION SLAVE ON *.* TO '$REPLICAUSER'@'%'; FLUSH PRIVILEGES; SHOW MASTER STATUS;"
fi
FILE=`mysql -Bse 'show master status' | awk '{print $1}'`
POSITION=`mysql -Bse 'show master status' | awk '{print $2}'`
echo "MASTER1: FILE=$FILE POSITION=$POSITION"

Database sync

Previous commands will create the user unless it already exists. Instead of % you can use $IP2 for improved security. When creating replicator on MASTER2 you would use $IP1. Binlog filename and log position will be listed and we will use these soon. Now dump and sync MySQL databases to MASTER2. The --master-data argument will issue CHANGE MASTER TO when being loaded on peer (MASTER2).

mysql -e "STOP SLAVE"
mysqldump --all-databases --master-data > data.sql
scp data.sql $IP2:
rm -f data.sql

On MASTER2 we load the dump.

mysql -e "STOP SLAVE"
mysql < data.sql
mysqladmin reload
rm -f data.sql

As we now have new users table in mysql database we need to sync credentials. Run on MASTER1:

scp ~/.my.cnf root@$IP2:.my.cnf

User with replication privileges on MASTER2

Note, this is redundant as replicator user has been cloned in previous step. You may skip this if you are using the same replicator user/pass for both servers.

We then create replicator user on MASTER2 using the same password as before.

REPLICAUSER=replicator
REPLICAPASS=a__secret__here
if [[ `mysql -Bse "select count(*) from mysql.user where user='replicator'"` -eq 0 ]]; then
    mysql -e "CREATE USER '$REPLICAUSER'@'%' IDENTIFIED BY '$REPLICAPASS'; GRANT REPLICATION SLAVE ON *.* TO '$REPLICAUSER'@'%'; FLUSH PRIVILEGES; SHOW MASTER STATUS;"
fi
FILE=`mysql -Bse 'show master status' | awk '{print $1}'`
POSITION=`mysql -Bse 'show master status' | awk '{print $2}'`
echo "MASTER2: FILE=$FILE POSITION=$POSITION"

Again binlog filename and position are displayed for consequent use on the peer server (MASTER1). We now have servers configured identically, data synced and replication users ready. Next step is to activate replication from MASTER1 to MASTER2 and the other way.

Enabling replication

On MASTER2 enable replication from MASTER1 to MASTER2 (MASTER2 acts as slave). Here you use $FILE and $POSITION obtained at MASTER1 earlier:

mysql -e "STOP SLAVE;CHANGE MASTER TO MASTER_HOST = '$IP1', MASTER_USER = '$REPLICAUSER', MASTER_PASSWORD = '$REPLICAPASS', MASTER_LOG_FILE = '$FILE', MASTER_LOG_POS = $POSITION;START SLAVE;"

On MASTER1 enable replication from MASTER2 to MASTER1 (MASTER1 acts as slave). Here you use $FILE and $POSITION obtained at MASTER2 earlier:

mysql -e "STOP SLAVE; CHANGE MASTER TO MASTER_HOST = '$IP2', MASTER_USER = '$REPLICAUSER', MASTER_PASSWORD = '$REPLICAPASS', MASTER_LOG_FILE = '$FILE', MASTER_LOG_POS = $POSITION;START SLAVE;"

Crosschecking servers

The results of below commands must match. First we compare MASTER1 MASTER STATUS and MASTER2 SLAVE STATUS, then MASTER2 MASTER STATUS and MASTER1 SLAVE STATUS and finally SLAVE HOSTS for each master. Run everything on MASTER1:

mysql -Bse 'show master status\G' | grep  -P '(File|Position):'
ssh -T $IP2 <<EOF
mysql -Bse 'show slave status\G' | grep  -P '(\s|Read_)Master_Log_(File|Pos):'
EOF

ssh -T -p2017 $IP2 <<EOF
mysql -Bse 'show master status\G' | grep  -P '(File|Position):'
EOF
mysql -Bse 'show slave status\G' | grep  -P '(\s|Read_)Master_Log_(File|Pos):'

mysql -Bse 'show slave hosts\G'
ssh -T -p2017 $IP2 <<EOF
mysql -Bse 'show slave hosts\G'
EOF

A shell script performing all mentioned operations without a need to switch between servers is here.

Articles