Mariadb-HA install guide

Reading instructions

• Replace values in angle brackets with the actual values. For example, when you see <*_USERNAME> and <*_PASSWORD>, enter your actual username and password.
• In the command line and configuration file examples in this guide, you can ignore all annotations that start with #.

Configuring Mariadb Dual Masters and Slaves

0. Prerequisite

Run the following commands on both the master and backup nodes to install and start the MariaDB service.

dnf install -y mariadb-server

mkdir -p /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

systemctl start mysql

1. Create binlogs and relaylogs save directory on the master and backup

mkdir -p /var/lib/mysql/binlogs
mkdir -p /var/lib/mysql/relaylogs

chown -R mysql:mysql /var/lib/mysql/binlogs /var/lib/mysql/relaylogs

2. Edit the /etc/my.cnf file on the master node

[mysqld]
datadir = /var/lib/mysql/
log-bin = /var/lib/mysql/binlogs/master-bin
relay-log = /var/lib/mysql/relaylogs/relay-bin
auto_increment_increment = 2
auto_increment_offset = 1
server-id = 131
log-error = /var/lib/mysql/mariadb.err
log-slave-updates = on
expire-logs-days = 14
sync-binlog = 1
binlog_do_db=slurm_acct_db
binlog_do_db=slurm_jobcomp_db
binlog_do_db=lico

3. Edit the /etc/my.cnf file on the backup node

[mysqld]
datadir = /var/lib/mysql/
log-bin = /var/lib/mysql/binlogs/backup-bin
relay-log = /var/lib/mysql/relaylogs/relay-bin
auto_increment_increment = 2
auto_increment_offset = 2
server-id = 132
log-error = /var/lib/mysql/mariadb.err
log-slave-updates = on
expire-logs-days = 14
sync-binlog = 1
binlog_do_db=slurm_acct_db
binlog_do_db=slurm_jobcomp_db
binlog_do_db=lico

4. Restart mariadb service on the master and backup

systemctl restart mariadb

5. Create users in mariadb in all nodes separately and give them permissions

on master node

mysql
CREATE USER 'rep_master'@'%' IDENTIFIED BY '<PASSWORD>';
grant all privileges on *.* to 'rep_master'@'%' identified by '<PASSWORD>';
flush privileges;
exit

on backup node

mysql
CREATE USER 'rep_backup'@'%' IDENTIFIED BY '<PASSWORD>';
grant all privileges on *.* to 'rep_backup'@'%' identified by '<PASSWORD>';
flush privileges;
exit

6. Check the binlog information of master and backup, record the position value and file name

mysql
show master status;
exit;

7. Backup data on the master that needs to be synchronized

Note: Change the <DB_NAME> to the name of the databases you need to synchronize, each database name needs to be executed once.

mkdir -p /var/lib/mysql/backup
mysqldump -u<USERNAME> -p<PASSWORD> --databases <DB_NAME> > /var/lib/mysql/backup/<DB_NAME>.sql

8. Login the backup node and import data from master database

Note: Change the <DB_NAME> to the name of the databases you need to synchronize, each database name needs to be executed once.

scp -r root@<MASTER_IP>:/var/lib/mysql/backup /var/lib/mysql/
mysql
source /var/lib/mysql/backup/<DB_NAME>.sql;
exit;

9. Authorize the slave’s access user separately

on master node

mysql
grant replication slave on *.* to 'rep_backup'@'<BACKUP_IP>' identified by '<PASSWORD>';
flush privileges;
exit;
systemctl restart mariadb

on backup node

mysql
grant replication slave on *.* to 'rep_master'@'<MASTER_IP>' identified by '<PASSWORD>';
flush privileges;
exit;
systemctl restart mariadb

10. Setting up mariadb as a slave with the corresponding master according to the information saved in step 6

on master node

mysql
stop slave;
change master to
master_host='<BACKUP_IP>',
master_port=3306,
master_user='rep_backup',
master_password='<PASSWORD>',
master_log_file='<BACKUP_BINLOG_FILENAME>',
master_log_pos=<BACKUP_BINLOG_POSITION>;
start slave;
show slave status\G
exit;

on backup node

mysql
stop slave;
change master to 
master_host='<MASTER_IP>',
master_port=3306,
master_user='rep_master',
master_password='<PASSWORD>',
master_log_file='<MASTER_BINLOG_FILENAME>',
master_log_pos=<MASTER_BINLOG_POSITION>;
start slave;
show slave status\G
exit;

11. Check the output in step 10, paying attention to whether the values of the Slave_IO_Running and Slave_SQL_Running fields are YES or not

Install keepalived

1. Install keepalived service on both the master and backup nodes

dnf install -y keepalived

2. Edit the /etc/keepalived/keepalived.conf file on the master node add the following content

vrrp_script check_mariadb {
  script "</dev/tcp/127.0.0.1/3306"
  interval 5
  weight -2
}
vrrp_instance VI_MARIADB {
  state MASTER
  interface <INTERFACE_NAME>
  virtual_router_id 61
  priority 100
  advert_int 1
  virtual_ipaddress {
    <VIRTUAL_IP>
  }
  track_script {
    check_mariadb
  }
}

3. Edit the /etc/keepalived/keepalived.conf file on the backup node add the following content

vrrp_script check_mariadb {
  script "</dev/tcp/127.0.0.1/3306"
  interval 5
  weight -2
}
vrrp_instance VI_MARIADB {
  state BACKUP
  interface <INTERFACE_NAME>
  virtual_router_id 61
  priority 99
  advert_int 1
  virtual_ipaddress {
    <VIRTUAL_IP>
  }
  track_script {
    check_mariadb
  }
}

4. Start the keepalived service on the master and backup

systemctl enable keepalived
systemctl start keepalived
systemctl status keepalived