**This is an old revision of the document!**

File Fabric HA Master - Master Database with Automatic Failover

last updated Nov. 22, 2019

Disclaimer

The information in this document is provided on an as-is basis. You use it at your own risk. We accept no responsibility for errors or omissions, nor do we have any obligation to provide support for implementing or maintaining the configuration described here. Furthermore, we do not warrant that the design presented here is appropriate for your requirements.

SME designs, implements and supports HA File Fabric solutions for customers on a paid professional services basis. For more information please contact sales@storagemadeeasy.com

This setup is suitable if you have a reliable network between mysql nodes and are deploying in a single data centre.

If the network is partitioned there is a chance that both masters can become active at the same time(split brain) and you will have corrupt data. In case of a split brain you will have to restore the data from a backup.

If you are deploying across multiple data centers or network is not reliable please follow highavailabilitysetup

Introduction

The SME Cloud Control appliance as shipped is configured for deployment on a single virtual machine. However, a common deployment scenario for production deployments are redundant web frontends in front of a Master-Master MySQL deployment.

By deploying multiple web frontends and a Master-Master database your SME Cloud will increase availability and number of concurrent users. This is accomplished by reducing points of failure and allowing for native MySQL backups without downtime.

Unlike the SME HA Setup "2x", this setup will use a Master-Master replication methodology to remove the need to perform manual resyncing of data in a failback scenario, as well as provide automation for the failover.

Part 1

Assumptions

This guide assumes you have working knowledge and an understanding of Linux operating systems, databases, etc. If any questions come up, please contact your account manager or SME support.

For this guide we are using the following hostnames: smeweb01, smeweb02, smesql01, smesql02, smemem-vip, and smesql-vip. You are of course free to select your own names that matches your naming schema.

In addition, you should have DNS configured and verified for the above 6 DNS records and ip addresses, as well as opened up any internal firewalls that can restrict necessary traffic between the systems, including multicast traffic for keepalived.

Initial State

This guide assumes you set up the four appliance following the instructions in the Appliance Installation guide here Keepalived will need two additional Virtual IPs, one for the VIP for the database, and one for memcache. This will mean that you can change the primary DB server without changing the configuration on the application servers.

Preparation

Before you start, please be sure to collect / prepare the necessary information.

  • 4 SME Appliances deployed
  • SME linux root password
  • SME linux smeconfiguser password
  • 6 IP addresses for your LAN - 2 for application servers, 2 for database/memcache servers, and 2 virtual IPs (VIPS)
  • 6 DNS names for the IPs
  • New DB user for app server and password, in this guide we use smestoreremote.
  • New DB user for DB server replication, in this guide we use repl_user.
Linux Login

For Linux command line operations, you must run the commands shown in this document as the root user unless otherwise specified. However, for security reasons you cannot connect with ssh to the machine directly. Instead, you should ssh to the box using smeconfiguser and then su to root:

ssh smeconfiguser@smeweb01

Enter the smeconfiguser password at the prompt. Once logged in, elevate your privileges to root.

su -

Database Login

For database operations, to login to the smestorage database, you do not need a password. As root from the command line, type:

mysql smestorage

Note: This command only works if you are root, smeconfiguser does not have privilege to run this command.

Stop services

Before we start the configuration, ensure that all services are stopped, and nothing is connecting to the databases. The below commands should be executed on all four servers as the root user:

systemctl stop httpd
systemctl stop cloudftp
systemctl stop vsftpd

Part II

Configuring the Database Servers

You must perform these steps to create a specialized database server from the standard SME appliance distribution. In this guide we also run memcached as a part of the DB server.

Restrict external access

The database server does not serve web pages and does not need to be accessible from outside WAN. The only traffic you need to allow is TCP port 3306 and 11211 between the four SME appliances.

Disable unnecessary services

The DB server will only be used for memcache and database services, so the following services, are unnecessary and can be disabled. The below commands should be executed on both smesql01 and smesql02 as the root user.

systemctl disable httpd
systemctl disable cloudftp
systemctl disable vsftpd
crontab

You must also disable some jobs in crontab. As root on smesql01 and smesql02:

crontab -e -u smestorage

Place a “#” infront of the seven jobs listed, when you are done, it should look like this:

iptables for dbservers

On both smesql01 and smesql02, you must update iptables to allow incoming connections to mariadb, do the following.

As root:

iptables-save > /var/tmp/iptables_backup_`date -I`
ipt_line=`iptables -L RH-Firewall-1-INPUT -n --line-numbers | grep REJECT | awk '{print $1}'`
insert_line=`expr $ipt_line - 1`
iptables -I RH-Firewall-1-INPUT $insert_line -p tcp -m state --state NEW -m tcp --dport 11211 -j ACCEPT

iptables -I RH-Firewall-1-INPUT $insert_line -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

iptables-save > /etc/sysconfig/iptables

Memcached configuration

By default, the appliance memcached service listens only for connections from localhost, in order to share a memcache between the two application servers, we must change that. For redundancy we will do this on both smesql01 and smesql02.

To allow other machines to connect, we will update the configuration file

 
cp /etc/sysconfig/memcached /var/tmp/memcached_backup_`date -I`
cat /var/tmp/memcached_backup_`date -I`| sed s/127.0.0.1/0.0.0.0/g > /etc/sysconfig/memcached
systemctl restart memcached

MySQL configuration for HA

smesql01 MySQL Database

The settings for MySQL are stored in /etc/my.cnf.d/localhost.cnf. By default there are minimal settings in this file, so we will replace all the content with this new file

[mysqld]
# Allow remote addresses to connect to this server.
 bind-address = 0.0.0.0
# # Avoid sysdate() ambiguity.
 sysdate-is-now=1
#
# #+REPLICATION MASTER
# #============
# # server-id is 1 for smesql01. (The smesql02 has server-id=2.)
 server-id=1
# # Transaction logs on the master are written to
# # /var/lib/mysql/master-tx-log-bin directory
 log-bin = /var/lib/mysql/master-tx-log-bin
 log-bin-index = /var/lib/mysql/master-tx-log-bin-index
 expire_logs_days = 10
 max_binlog_size = 100M
 binlog_format=row
performance_schema=ON

#
# #============
# #-REPLICATION

Then restart the database for this change to take effect

systemctl restart mariadb
smesql02 MySQL Database

The settings for MySQL are stored in /etc/my.cnf.d/localhost.cnf. By default there are minimal settings in this file, so we will replace all the content with this new file

[mysqld]
# Allow remote addresses to connect to this server.
bind-address = 0.0.0.0
# # Avoid sysdate() ambiguity.
sysdate-is-now=1
#
# #+REPLICATION SLAVE
# #============
# # server-id is 2 for smesql02. (The smesql01 has server-id=1.)
server-id=2
# #Transaction logs from the master are replicated/relayed to the
# #slave in the /var/lib/mysql/slave-relay-log-bin/ directory

 log-bin = /var/lib/mysql/master-tx-log-bin
 log-bin-index = /var/lib/mysql/master-tx-log-bin-index
 expire_logs_days = 10
 max_binlog_size = 100M
 binlog_format=row
performance_schema=ON


#relay-log = /var/lib/mysql/slave-relay-log-bin
#relay-log-index = /var/lib/mysql/slave-relay-log-bin-index
# #============
# #-REPLICATION

Then restart the database for this change to take effect

systemctl restart mariadb

Configure Database Replication

Create Replication User

From smesql01 we will run the following commands:

mysql -e "CREATE USER repl_user IDENTIFIED by '<complex, secure password>';"
mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';"
mysql -e "FLUSH PRIVILEGES;"
mysql -e "FLUSH TABLES WITH READ LOCK;"

We will then gather some information from the smesql01 master that we will use on smesql02

mysql -e "SHOW MASTER STATUS;"

The output will look something like:

+--------------------------+----------+--------------+------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------+----------+--------------+------------------+
| master-tx-log-bin.000012 |      245 |              |                  |
+--------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

So we will record and use the following values: File: master-tx-log-bin.000012 Position: 245

Copy the database to smesql02

Now as root on smesql02 we will run the following commands to copy over the database from smesql01 to smesql02.

Note: We will enable a system variable to allow the stored procedures to be restored. After the database import we will turn that variable back to the default state.

mysql -e "set GLOBAL log_bin_trust_function_creators = 1;"
ssh smeconfiguser@<smesql01> mysqldump -u smestore -pbesp5fyx smestorage | mysql -u smestore -pbesp5fyx smestorage
mysql -e "set GLOBAL log_bin_trust_function_creators = 0;"
Enable slave replication on smesql02

Now that we've got both databases in sync, we can take the data we gathered in the master status of smesql01 to setup the slave relationship from smesql01 → smesql02.

Note: Update the first line below to replace all items in < > with your environment specific entries

mysql -e "CHANGE MASTER TO MASTER_HOST='smesql01', MASTER_USER='repl_user', MASTER_PASSWORD='<complex, secure password>', MASTER_LOG_FILE='<file from show master status on smesql01>', MASTER_LOG_POS=<position from show master status on smesql01>;"
mysql -e "START SLAVE;"
mysql -e "SHOW SLAVE STATUS \G"

You will be looking for the following two lines to verify you have setup replication correctly.

            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes       

Next we will setup the replication user on smesql02 as well.

mysql -e "CREATE USER repl_user IDENTIFIED by '<complex, secure password>';"
mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';"
mysql -e "FLUSH PRIVILEGES;"
mysql -e "FLUSH TABLES WITH READ LOCK;"

Now we will get the master information for smesql02, just like we did for smesql01

mysql -e "SHOW MASTER STATUS;"

The output will look something like:

+--------------------------+----------+--------------+------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------+----------+--------------+------------------+
| master-tx-log-bin.000002 |      245 |              |                  |
+--------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

So we will record and use the following values: File: master-tx-log-bin.000002 Position: 245

Enable slave replication on smesql01

Back on the smesql01 host, we will now setup replication back from smesql02.

The commands will be similar to the ones above, but make note of the different host and make sure the file and positions match the output from the smesql02 show master status.

Note: Update the first line below to replace all items in < > with your environment specific entries

mysql -e "CHANGE MASTER TO MASTER_HOST='smesql02', MASTER_USER='repl_user', MASTER_PASSWORD='<complex, secure password>', MASTER_LOG_FILE='<file from show master status on smesql02>', MASTER_LOG_POS=<position from show master status on smesql02>;"
mysql -e "START SLAVE;"
mysql -e "SHOW SLAVE STATUS \G"

You will be looking for the following two lines to verify you have setup replication correctly.

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
Replication Setup Completed

Now you have bi-directional Master-Master replication setup. Both database servers will accept writes, and will replicate those changes back to the other database.

As you will see lower in the guide, we will be using a VIP and keepalived to set it up so that only 1 db server is being read or written to at any given time. This is done to ensure consistency of the SME application, and having SME Application read and writes go to multiple database backends is currently not supported.

Unlock Database and Create Users

On smesql02 we will complete the last database step there, to unlock the database.

 
mysql -e "UNLOCK TABLES;"

Next, on smesql01, we will complete the last database steps, which are to unlock the database, and create our database user we will be using for our application servers.

mysql -e "UNLOCK TABLES;"
mysql -e "CREATE USER 'smestoreremote'@'%' IDENTIFIED BY '<smestoreremote_password>';"
mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'smestoreremote'@'%' WITH GRANT OPTION;"
mysql -e "FLUSH PRIVILEGES;"

Part III

Using Keepalived to manage VIP and automatic failover

We will use the opensource application keepalived to provide management of the VIP, as well as provide automated failover in the case of an outage of the server, or even of the database or memcache service dying.

These commands will be run on both smesql01 as well as smesql02.

The first step is to install keepalived from the rpm repos.

yum install -y keepalived

We will also need to allow multicast traffic (244.0.0.0) through our iptables firewall, as well as adjust selinux to allow keepalived to operate. Finally, we will add a user (keepalived_script) which keepalived will use.

iptables -I INPUT -i eth0 -d 224.0.0.0/8 -p vrrp -j ACCEPT
iptables-save > /etc/sysconfig/iptables
semanage permissive -a keepalived_t
groupadd keepalived_script
useradd -s /sbin/nologin -g keepalived_script -M keepalived_script

Now we will create a new keepalived configuration file for our two VIPs.

create /etc/keepalived/keepalived.conf like so:

Note: Update the first line below to replace all items in < > with your environment specific entries

smesql01 keepalived.conf
! Configuration File for keepalived

global_defs {
  notification_email {
  }
  vrrp_skip_check_adv_addr
  vrrp_strict
  vrrp_garp_interval 0
  vrrp_gna_interval 0
  enable_script_security
}

vrrp_script chk_mariadb {
  script "/sbin/pidof mysqld"
  interval 2
  rise 5
  fall 5
}

vrrp_script chk_memcache {
  script "/sbin/pidof memcached"
  interval 2
  rise 5
  fall 5
}

vrrp_instance DB {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 105
  nopreempt
  virtual_ipaddress {
    <db VIP address - ex: '10.10.10.1'>
  }
  track_script {
    chk_mariadb
  }
  authentication {
    auth_type PASS
    auth_pass <8 character password> 
  }
  notify "/usr/libexec/keepalived/keepalived_state.sh" root
}

vrrp_instance MEMCACHE {
  state MASTER
  interface eth0
  virtual_router_id 61
  priority 105
  nopreempt
  virtual_ipaddress {
    <memcached VIP address - ex: '10.10.10.2'>
  }
  track_script {
    chk_memcache
  }
  authentication {
    auth_type PASS
    auth_pass <8 character password> 
  }
  notify "/usr/libexec/keepalived/keepalived_state.sh" root
}

Note: Update the first line below to replace all items in < > with your environment specific entries

smesql02 keepalived.conf
! Configuration File for keepalived

global_defs {
  notification_email {
  }
  vrrp_skip_check_adv_addr
  vrrp_strict
  vrrp_garp_interval 0
  vrrp_gna_interval 0
  enable_script_security
}

vrrp_script chk_mariadb {
  script "/sbin/pidof mysqld"
  interval 2
  rise 5
  fall 5
}

vrrp_script chk_memcache {
  script "/sbin/pidof memcached"
  interval 2
  rise 5
  fall 5
}

vrrp_instance DB {
  state BACKUP
  interface eth0
  virtual_router_id 51
  priority 100
  nopreempt
  virtual_ipaddress {
    <db VIP address - ex: '10.10.10.1'>
  }
  track_script {
    chk_mariadb
  }
  authentication {
    auth_type PASS
    auth_pass <8 character password> 
  }
	notify "/usr/libexec/keepalived/keepalived_state.sh" root
}
vrrp_instance MEMCACHE {
  state BACKUP
  interface eth0
  virtual_router_id 61
  priority 100
  nopreempt
  virtual_ipaddress {
    <memcached VIP address - ex: '10.10.10.2'>
  }
  track_script {
    chk_memcache
  }
  authentication {
    auth_type PASS
    auth_pass <8 character password> 
  }
  notify "/usr/libexec/keepalived/keepalived_state.sh" root
}
Deploy our notification script on both smesql01 and smesql02

We will create the following script and give it execute permissions ( # chmod 0755 /usr/libexec/keepalived/keepalived_state.sh )

#!/bin/bash
echo ${3} > /var/run/keepalived_${2}.state
Start and Enable Keepalived

Now that we've deployed keepalived, and setup the correct configuration files we will start and enable the service, and then validate that it's running correctly.

Again on both smesql01 and smesql02

systemctl start keepalived && systemctl enable keepalived

Once keepalived is up and running we have two ways we can validate the state of each server. The first is looking at the ip address information on the virtual machine. The host running master will have the floating ips assigned to the eth0 adapter. The second way is by looking at the state files we are writing out with our notification script.

Running the following commands on each host will show those details

ip a show dev eth0
cat /var/run/keepalived_*.state
Keepalived Notes
State

For the State files: all running instances of keepalived will be in one of 3 states: MASTER = currently responsible for that VIP, and will be actively responding to traffic directed to the VIP BACKUP = in standby, waiting to take over the VIP if the master is no longer in a MASTER state FAULT = after our check scripts have failed (due to the service no longer running), it moves to a fault state and is not eligible to be in a MASTER or BACKUP role.

Check scripts

By default we are using the following details for our regular checks to validate that the services are running

vrrp_script chk_mariadb {
	script "/sbin/pidof mysqld"
	interval 2
	rise 5
	fall 5
}

This means that every 2 seconds (interval 2), we will run a check to see if the mysqld process is running (script “/sbin/pidof mysqld”). If it fails for 5 consecutive checks (fall 5) than that instance will move to a FAULT state. In addition, if it passes for 5 consecutive checks (rise 5), it then moves out of a fault state.

In the vrrp_instance section, we also set the attribute of nopreempt. This means that if smesql01 is in a MASTER state (as defined by the config file), and it moves to a FAULT state, when it exits that FAULT state it will move into BACKUP state.

smesql01 will not become master again until either 1) smesql02 enters a FAULT state (or the machine is no longer running) or 2) You restart keepalived in order to reset the state and force it back to MASTER status (# systemctl restart keepalived).

There is no additional benefit or risk of leaving smesql02 in a MASTER state, so it is recommended you retain these default settings.

Selinux Logging

This default setup will block setuid/setgid permissions for keepalived. After running keepalived, if you are looking to clean this up you can add these as custom selinux policies:

ausearch -c 'keepalived' --raw | audit2allow -M my-keepalived && semodule -i my-keepalived.pp

Part IV

If you will be using a load balancer to distribute traffic to your web servers, configure the load balancer to provide session stickiness.

Configure the application servers

In this section, you change the application server's configuration to the database server by editing the PHP configuration directly from command line. By default, the database host is localhost. These steps are repeated on both smeweb01 and smeweb02.

Make a backup of the configuration file:

cd /var/www/smestorage/public_html/
cp config.inc.php /home/smeconfiguser/config.inc.php.backup_`date -I`

Open the configuration file to edit the settings.

We have to update 5 variables in the file. They are all close to the top under “DB settings”, change the address for the database server by changing the line

var $dbhost='localhost';

to

var $dbhost='smesql-vip';

In addition add a newline to define a remote memcached instance

var $memcachehost='smemem-vip:11211';

Further we need to update the $dbuser and $password to match the <smestoreremote> user and password used when setting up the database in the previous section.

Do not change $dbname.

Disable unused services

As the mariadb and memcached services are not used on the application server, you should disable them:

These steps are repeated on both smeweb01 and smeweb02.

mariadb
systemctl disable mariadb
systemctl stop mariadb
memcached
systemctl disable memcached
systemctl stop memcached
crontab

All of smestorage's cron jobs should run on one of the application servers; only one of smestorage's cron jobs, logroller, should run on the other application server. As root on smeweb02 only:

# crontab -e -u smestorage

Place a # in front of six of the seven jobs listed in the crontab schedule; do not place a # in front of the logroller job.

Restart both servers, so on both smeweb01 and smeweb02, execute a reboot.

Appendix

The following section covers additional topics not necessary to establish HA on a clean pair of new servers.

Custom Branding

When setting up multiple application servers, the branding information is saved at /var/www/smestorage/public_html/templates/company

If branding is changed you should copy this directory manually to other application servers.

On smeweb01, as root:

cd /
tar czf branding.tgz /var/www/smestorage/public_html/templates/company/*
scp branding.tgz smeconfiguser@smeweb02:

On smeweb02, as root:

cd /
mv /home/smeconfiguser/branding.tgz /
tar xzf branding.tgz

Applying Updates

Each Application server will need to be updated separately when a new version is available.

Startup / Shutdown of HA Systems

In the event the environment needs to be brought down for a maintenance cycle, shutdown servers in the following order:

  1. WebServers
  2. MySQL server 1
  3. MySQL server 2

For startup, reverse the order above. Give time between servers starting to ensure all services are running properly.

Database Backup

One advantage of using replication is that you can perform a backup of the database from the BACKUP database at a point in time without stopping the MASTER - so the application is still providing service to users.

You do the backup from the slave as follows:

Once you restart the database server, it will automatically catch up with the MASTER database status from the point where it left off.

Force Failover

To force a failover from the MASTER to the BACKUP, you can simply restart the keepalived service. Due to the no automatic failback (nopreemtp)

 
systemctl restart keepalived

Force Failback

As discussed, this setup will not failback to the previous master due to the keepalived configuration (nopreemtp).

If you want to force the previous server to become the master again, you can manually restart keepalived on the current MASTER.

Before you fail back, you need to ensure that all replication is up to date. Look for the SecondsBehindMaster attribute in the mysql “SHOW SLAVE STATUS\G” command

 
systemctl restart keepalived