MySQL: the simplest high availability cluster between two redundant servers
With the synchronous replication and automatic failover provided by Evidian SafeKit
The solution for MySQL
Evidian SafeKit brings high availability to MySQL between two redundant servers with real-time replication of data and automatic failover.
This article explains how to implement quickly a MySQL cluster without shared disk and without specific skills.
A generic product
Note that SafeKit is a generic product on Windows and Linux.
You can implement with the SafeKit product real-time replication and failover of any file directory and service, database, complete Hyper-V or KVM virtual machines, Docker, Podman, K3S, Cloud applications (see the module list).
A complete solution
SafeKit solves:
- hardware failures (20% of problems), including the complete failure of a computer room,
- software failures (40% of problems), including restart of critical processes,
- and human errors (40% of problems) thanks to its ease of use and its web console.
Partners, the success with SafeKit
This platform agnostic solution is ideal for a partner reselling a critical application and who wants to provide a redundancy and high availability option easy to deploy to many customers.
With many references in many countries won by partners, SafeKit has proven to be the easiest solution to implement for redundancy and high availability of building management, video management, access control, SCADA software...
Building Management Software (BMS)
Video Management Software (VMS)
Electronic Access Control Software (EACS)
SCADA Software (Industry)
Step 1. Real-time replication
Server 1 (PRIM) runs the MySQL application. Clients are connected to a virtual IP address. SafeKit replicates in real time modifications made inside files through the network.
The replication is synchronous with no data loss on failure contrary to asynchronous replication.
You just have to configure the names of directories to replicate in SafeKit. There are no pre-requisites on disk organization. Directories may be located in the system disk.
Step 2. Automatic failover
When Server 1 fails, Server 2 takes over. SafeKit switches the virtual IP address and restarts the MySQL application automatically on Server 2.
The application finds the files replicated by SafeKit uptodate on Server 2. The application continues to run on Server 2 by locally modifying its files that are no longer replicated to Server 1.
The failover time is equal to the fault-detection time (30 seconds by default) plus the application start-up time.
Step 3. Automatic failback
Failback involves restarting Server 1 after fixing the problem that caused it to fail.
SafeKit automatically resynchronizes the files, updating only the files modified on Server 2 while Server 1 was halted.
Failback takes place without disturbing the MySQL application, which can continue running on Server 2.
Step 4. Back to normal
After reintegration, the files are once again in mirror mode, as in step 1. The system is back in high-availability mode, with the MySQL application running on Server 2 and SafeKit replicating file updates to Server 1.
If the administrator wishes the application to run on Server 1, he/she can execute a "swap" command either manually at an appropriate time, or automatically through configuration.
More information on power outage and network isolation in a cluster.
Redundancy at the application level
In this type of solution, only application data are replicated. And only the application is restared in case of failure.
With this solution, restart scripts must be written to restart the application.
We deliver application modules to implement redundancy at the application level (like the MySQL module provided in the free trial below). They are preconfigured for well known applications and databases. You can customize them with your own services, data to replicate, application checkers. And you can combine application modules to build advanced multi-level architectures.
This solution is platform agnostic and works with applications inside physical machines, virtual machines, in the Cloud. Any hypervisor is supported (VMware, Hyper-V...).
Redundancy at the virtual machine level
In this type of solution, the full Virtual Machine (VM) is replicated (Application + OS). And the full VM is restarted in case of failure.
The advantage is that there is no restart scripts to write per application and no virtual IP address to define. If you do not know how the application works, this is the best solution.
This solution works with Windows/Hyper-V and Linux/KVM but not with VMware. This is an active/active solution with several virtual machines replicated and restarted between two nodes.
- Solution for a new application (no restart script to write): Windows/Hyper-V, Linux/KVM
More comparison between VM HA vs Application HA
Why a replication of a few Tera-bytes?
Resynchronization time after a failure (step 3)
- 1 Gb/s network ≈ 3 Hours for 1 Tera-bytes.
- 10 Gb/s network ≈ 1 Hour for 1 Tera-bytes or less depending on disk write performances.
Alternative
- For a large volume of data, use external shared storage.
- More expensive, more complex.
Why a replication < 1,000,000 files?
- Resynchronization time performance after a failure (step 3).
- Time to check each file between both nodes.
Alternative
- Put the many files to replicate in a virtual hard disk / virtual machine.
- Only the files representing the virtual hard disk / virtual machine will be replicated and resynchronized in this case.
Why a failover < 25 replicated VMs?
- Each VM runs in an independent mirror module.
- Maximum of 25 mirror modules running on the same cluster.
Alternative
- Use an external shared storage and another VM clustering solution.
- More expensive, more complex.
Why a LAN/VLAN network between remote sites?
- Automatic failover of the virtual IP address with 2 nodes in the same subnet.
- Good bandwidth for resynchronization (step 3) and good latency for synchronous replication (typically a round-trip of less than 2ms).
Alternative
- Use a load balancer for the virtual IP address if the 2 nodes are in 2 subnets (supported by SafeKit, especially in the cloud).
- Use backup solutions with asynchronous replication for high latency network.
Prerequisites
- You need MySQL installed on 2 nodes (virtual machines or physical servers).
- On Windows, with Windows services manager, put MySQL services with Boot Startup Type = Manual on both nodes.
SafeKit controls start of MySQL services in the restart scripts. Edit the restart scripts during the configuration to check if you have put all services in Manual boot including the new ones that you can add.
Package installation on Windows
-
Download the free version of SafeKit on 2 Windows nodes.
Note: the free version includes all SafeKit features. At the end of the trial, you can activate permanent license keys without uninstalling the package.
-
To open the Windows firewall, on both nodes start a powershell as administrator, and type
c:/safekit/private/bin/firewallcfg add
-
To initialize the password for the default admin user of the web console, on both nodes start a powershell as administrator, and type
c:/safekit/private/bin/webservercfg.ps1 -passwd pwd
- Use aphanumeric characters for the password (no special characters).
- pwd must be the same on both nodes.
-
Exclude from antivirus scans C:\safekit\ (the default installation directory) and all replicated folders that you are going to define.
Antiviruses may face detection challenges with SafeKit due to its close integration with the OS, virtual IP mechanisms, real-time replication and restart of critical services.
Module installation on Windows
-
Download the mysql.safe module.
The module is free. It contains the files userconfig.xml and the restart scripts.
- Put mysql.safe under C:\safekit\Application_Modules\demo (create the demo directory if it does not exist).
Package installation on Linux
-
Install the free version of SafeKit on 2 Linux nodes.
Note: the free trial includes all SafeKit features. At the end of the trial, you can activate permanent license keys without uninstalling the package.
-
After the download of safekit_xx.bin package, execute it to extract the rpm and the safekitinstall script and then execute the safekitinstall script
-
Answer yes to firewall automatic configuration
-
Set the password for the web console and the default user admin.
- Use aphanumeric characters for the password (no special characters).
- The password must be the same on both nodes.
Module installation on Linux
-
Download the mysql.safe module.
The module is free. It contains the files userconfig.xml and the restart scripts.
- Put mysql.safe under /opt/safekit/Application_Modules/demo/ (create the demo directory if it does not exist).
1. Launch the SafeKit console
- Launch the web console in a browser on one cluster node by connecting to
http://localhost:9010
. - Enter
admin
as user name and the password defined during installation.
You can also run the console in a browser on a workstation external to the cluster.
The configuration of SafeKit is done on both nodes from a single browser.
To secure the web console, see 11. Securing the SafeKit web console in the User's Guide.
2. Configure node addresses
- Enter the node IP addresses.
- Then, click on
Apply
to save the configuration.
If node1 or node2 background color is red, check connectivity of the browser to both nodes and check firewall on both nodes for troubleshooting.
This operation will place the IP addresses in the cluster.xml
file on both nodes (more information in the training with the command line).
4. Configure the module
- Choose an automatic start of the module at boot without delay.
- Normally, you have a single heartbeat network on which the replication is made. But, you can define a private network if necessary.
- Check that the replicated directories are installed on both nodes and contain the application data.
Replication of data and also logs is required for a database.
You can add new replicated directories as needed. - Enter a virtual IP address. A virtual IP address is a standard IP address in the same IP network (same subnet) as the IP addresses of both nodes.
Application clients must be configured with the virtual IP address (or the DNS name associated with the virtual IP address).
The virtual IP address is automatically switched in the event of a failure. start_prim
andstop_prim
must contain starting and stopping of the MySQL application.
You can add new services in these scripts.
Check that the names of the services in these scripts are those installed on both nodes, otherwise modify them in the scripts.- Stop the services configured in
start_prim
on both nodes. - On Windows and on both nodes, with the Windows services manager, set
Boot Startup Type = Manual
for all the services registered instart_prim
(SafeKit controls the start of services instart_prim
).
Note that if a process name is displayed in Process Checker, it will be monitored with a restart action in case of failure. Configuring a wrong process name will cause the module to stop right after its start.
This operation will report the configuration in the userconfig.xml
, start_prim
, stop_prim
files on both nodes (more information in the training with the command line).
5. Verify successful configuration
- Check the success message (green) on both nodes and click Next.
On Linux, you may get an error at this step if the replicated directories are mount points. See this article to solve the problem.
6. Start the node with up-to-date data
- If node 1 has the up-to-date replicated directories, select it and start it.
When node 2 will be started, all data will be copied from node 1 to node 2.
If you make the wrong choice, you run the risk of synchronizing outdated data on both nodes.
It is also assumed that the MySQL application is stopped on node 1 so that SafeKit installs the replication mechanisms and then starts the application in the start_prim
script.
7. Wait for the transition to ALONE (green)
- Node 1 should reach the ALONE (green) state, which means that the
start_prim
script has been executed on node 1.
If the status is ALONE (green) and the application is not started, check output messages of start_prim
in the Application Log of node 1.
If node 1 does not reach ALONE (green) state, analyze why with the Module Log of node 1.
If the cluster is in WAIT (red) not uptodate, STOP (red) not uptodate
state, stop the WAIT node and force its start as primary.
8. Start node 2
- Start node 2 with its contextual menu.
- Wait for the SECOND (green) state.
Node 2 stays in the SECOND (magenta) state while resynchronizing the replicated directories (copy from node 1 to node 2).
This may take a while depending on the size of files to resynchronize in replicated directories and the network bandwidth.
To see the progress of the copy, see the Module Log of node 2 with the verbose option without forgetting to refresh the window.
9. Verify that the cluster is operational
- Check that the cluster is green/green with MySQL services running on the PRIM node and not running on the SECOND node.
Only changes inside files are replicated in real time in this state.
Components that are clients of MySQL services must be configured with the virtual IP address. The configuration can be done with a DNS name (if a DNS name has been created and associated with the virtual IP address).
10. Testing
- Stop the PRIM node by scrolling down its contextual menu and clicking Stop.
- Verify that there is a failover on the SECOND node which should become ALONE (green).
- And with Microsoft Management Console (MMC) on Windows or with command lines on Linux, check the failover of MySQL services (stopped on node 1 in the
stop_prim
script and started on node 2 in thestart_prim
script).
If the MySQL application is not started on node 2 while the state is ALONE (green), check output messages of the start_prim
script in the Application Log of node 2.
If ALONE (green) is not reached, analyze why with the Module Log of node 2.
Module log
- Read the module log to understand the reasons of a failover, of a waiting state etc...
To see the module log of node 1 (image):
- click on the Control tab
- click on node 1/PRIM on the left side to select the server (it becomes blue)
- click on Module Log
- click on the Refresh icon (green arrows) to update the console
- click on the floppy disk to save the module log in a .txt file and to analyze in a text editor
Click on node2 to see the module log of the secondary server.
Application log
- Read the application log to see the output messages of the start_prim and stop_prim restart scripts.
To see the application log of node1 (image):
- click on the Control tab
- click on node 1/PRIM on the left side to select the server (it becomes blue)
- click on Application Log to see messages when starting and stopping MySQL services
- click on the Refresh icon (green arrows) to update the console
- click on the floppy disk to save the application log in a .txt file and to analyze in a text editor
Click on node 2 to see the application log of the secondary server.
Advanced configuration
- In Advanced Configuration tab, you can edit internal files of the module: bin/start_prim and bin/stop_prim and conf/userconfig.xml .
If you make change in the internal files here, you must apply the new configuration by a right click on the icon/xxx on the left side (see image): the interface will allow you to redeploy the modified files on both servers.
Support
- For getting support, take 2 SafeKit Snapshots (2 .zip files), one for each server.
If you have an account on https://support.evidian.com, upload them in the call desk tool.
Internals of a SafeKit / MySQL high availability cluster with synchronous replication and failover
Go to the Advanced Configuration tab in the console, for editing these filesInternal files of the Windows mysql.safe module
userconfig.xml on Windows (description in the User's Guide)
<!DOCTYPE safe>
<safe>
<service mode="mirror" defaultprim="alone" maxloop="3" loop_interval="24" failover="on">
<!-- Heartbeat Configuration -->
<!-- Names or IP addresses on the default network are set during initialization in the console -->
<heart pulse="700" timeout="30000">
<heartbeat name="default" ident="flow">
</heartbeat>
</heart>
<!-- Virtual IP Configuration -->
<!-- Replace
* VIRTUAL_TO_BE_DEFINED by the name of your virtual server
-->
<vip>
<interface_list>
<interface check="on" arpreroute="on">
<real_interface>
<virtual_addr addr="VIRTUAL_TO_BE_DEFINED" where="one_side_alias" />
</real_interface>
</interface>
</interface_list>
</vip>
<!-- Software Error Detection Configuration -->
<errd polltimer="10">
<!-- MySQL Server -->
<proc name="mysqld.exe" atleast="1" action="restart" class="prim" />
</errd>
<!-- File Replication Configuration -->
<!--
* C:\ProgramData\MySQL\MySQL Server 5.7: default directory containing MySQL database and logs
-->
<rfs async="second" acl="off" nbrei="3">
<replicated dir="C:\ProgramData\MySQL\MySQL Server 5.7" mode="read_only" />
</rfs>
<!-- User scripts activation -->
<user nicestoptimeout="300" forcestoptimeout="300" logging="userlog" />
</service>
</safe>
start_prim.cmd on Windows
@echo off
rem Script called on the primary server for starting application services
rem For logging into SafeKit log use:
rem "%SAFE%\safekit" printi | printe "message"
rem stdout goes into Application log
echo "Running start_prim %*"
set res=0
net start mysql > nul
if not %errorlevel% == 0 (
%SAFE%\safekit printi "MySQL start failed"
) else (
%SAFE%\safekit printi "MySQL started"
)
if %res% == 0 goto end
:stop
set res=%errorlevel%
"%SAFE%\safekit" printe "start_prim failed"
rem uncomment to stop SafeKit when critical
rem "%SAFE%\safekit" stop -i "start_prim"
:end
stop_prim.cmd on Windows
@echo off
rem Script called on the primary server for stopping application services
rem ----------------------------------------------------------
rem
rem 2 stop modes:
rem
rem - graceful stop
rem call standard application stop with net stop
rem
rem - force stop (%1=force)
rem kill application's processes
rem
rem ----------------------------------------------------------
rem For logging into SafeKit log use:
rem "%SAFE%\safekit" printi | printe "message"
rem stdout goes into Application log
echo "Running stop_prim %*"
set res=0
rem default: no action on forcestop
if "%1" == "force" goto end
%SAFE%\safekit printi "Stopping MySQL..."
net stop mysql > nul
rem wait a little for a real stop of services
%SAFEBIN%\sleep 10
:end
Internal files of the Linux mysql.safe module
userconfig.xml on Linux (description in the User's Guide)
<!DOCTYPE safe>
<safe>
<service mode="mirror" defaultprim="alone" maxloop="3" loop_interval="24" failover="on">
<!-- Heartbeat Configuration -->
<!-- Names or IP addresses on the default network are set during initialization in the console -->
<heart pulse="700" timeout="30000">
<heartbeat name="default" ident="flow">
</heartbeat>
</heart>
<!-- Virtual IP Configuration -->
<!-- Replace
* VIRTUAL_TO_BE_DEFINED by the name of your virtual server
-->
<vip>
<interface_list>
<interface check="on" arpreroute="on">
<real_interface>
<virtual_addr addr="VIRTUAL_TO_BE_DEFINED" where="one_side_alias"/>
</real_interface>
</interface>
</interface_list>
</vip>
<!-- Software Error Detection Configuration -->
<errd polltimer="10">
<!-- MySQL Server -->
<proc name="mysqld" atleast="1" action="restart" class="prim" />
</errd>
<!-- File Replication Configuration -->
<!--
* /var/lib/mysql: default directory of MySQL database and logs
-->
<rfs mountover="off" async="second" acl="off" nbrei="3">
<replicated dir="/var/lib/mysql" mode="read_only" />
</rfs>
<!-- User scripts activation -->
<user nicestoptimeout="300" forcestoptimeout="300" logging="userlog" />
</service>
</safe>
start_prim on Linux
#!/bin/sh
# Script called on the primary server for starting applications
# For logging into SafeKit log use:
# $SAFE/safekit printi | printe "message"
#---------- Clean MYSQL residual processes
# Call this function before starting any MySQL databases
# to clean eventual resual MySQL processes
clean_mysql()
{
retval=0
$SAFE/safekit printw "Cleaning MySQL processes"
# kill started MySQL processes
ps -e -o pid,comm | grep mysql | $AWK '{print "kill " $1}'| sh >/dev/null 2>&1
return $retval
}
#---------- MySQL Databases
# Call this function for starting MySQL Server
start_MySQL()
{
retval=0
$SAFE/safekit printw "Starting MySQL Server"
# MySQL - Database Starting
service mysqld start
if [ $? -ne 0 ] ; then
$SAFE/safekit printw "MySQL server start failed"
else
$SAFE/safekit printw "MySQL server started"
fi
return $retval
}
# stdout goes into Application log
echo "Running start_prim $*"
res=0
[ -z "$OSNAME" ] && OSNAME=`uname -s`
OSNAME=`uname -s`
case "$OSNAME" in
Linux)
AWK=/bin/awk
;;
*)
AWK=/usr/bin/awk
;;
esac
# TODO
# remove MySQL boot start
# Clean MySQL residual processes
clean_mysql || res=$?
# Start MySQL databases
start_MySQL || res=$?
if [ $res -ne 0 ] ; then
$SAFE/safekit printi "start_prim failed"
# uncomment to stop SafeKit when critical
# $SAFE/safekit stop -i "start_prim"
fi
exit 0
stop_prim on Linux
#!/bin/sh
# Script called on the primary server for stopping application services
# For logging into SafeKit log use:
# $SAFE/safekit printi | printe "message"
#----------------------------------------------------------
#
# 2 stop modes:
#
# - graceful stop
# call standard application stop
#
# - force stop ($1=force)
# kill application's processes
#
#----------------------------------------------------------
#---------- Clean MySQL residual processes
# Call this function on force stop
# to clean eventual resual MySQL processes
clean_mysql()
{
retval=0
$SAFE/safekit printw "Cleaning MySQL processes "
# kill started MySQL
ps -e -o pid,comm | grep mysql | $AWK '{print "kill -9 " $1}'| sh >/dev/null 2>&1
return $retval
}
#---------- MySQL databases
# Call this function for stopping MySQL databases
stop_MySQL()
{
retval=0
if [ "$1" = "force" ] ; then
# MySQL databases force stop
clean_mysql
return $retval
fi
# MySQL databases graceful stop
$SAFE/safekit printw "Stopping MySQL server"
service mysqld stop
if [ $? -ne 0 ] ; then
$SAFE/safekit printw "MySQL server stop failed"
else
$SAFE/safekit printw "MySQL server stopped"
fi
return $retval
}
# stdout goes into Application log
echo "Running stop_prim $*"
res=0
[ -z "$OSNAME" ] && OSNAME=`uname -s`
case "$OSNAME" in
Linux)
AWK=/bin/awk
;;
*)
AWK=/usr/bin/awk
;;
esac
mode=
if [ "$1" = "force" ] ; then
mode=force
shift
fi
# Stop MySQL server
stop_MySQL $mode || res=$?
[ $res -ne 0 ] && $SAFE/safekit printi "stop_prim failed"
exit 0
Network load balancing and failover |
|
Windows farm | Linux farm |
Generic Windows farm > | Generic Linux farm > |
Microsoft IIS > | - |
NGINX > | |
Apache > | |
Amazon AWS farm > | |
Microsoft Azure farm > | |
Google GCP farm > | |
Other cloud > |
Advanced clustering architectures
Several modules can be deployed on the same cluster. Thus, advanced clustering architectures can be implemented:
- the farm+mirror cluster built by deploying a farm module and a mirror module on the same cluster,
- the active/active cluster with replication built by deploying several mirror modules on 2 servers,
- the Hyper-V cluster or KVM cluster with real-time replication and failover of full virtual machines between 2 active hypervisors,
- the N-1 cluster built by deploying N mirror modules on N+1 servers.
Evidian SafeKit mirror cluster with real-time file replication and failover |
|
3 products in 1 More info > |
|
Very simple configuration More info > |
|
Synchronous replication More info > |
|
Fully automated failback More info > |
|
Replication of any type of data More info > |
|
File replication vs disk replication More info > |
|
File replication vs shared disk More info > |
|
Remote sites and virtual IP address More info > |
|
Quorum and split brain More info > |
|
Active/active cluster More info > |
|
Uniform high availability solution More info > |
|
RTO / RPO More info > |
|
Evidian SafeKit farm cluster with load balancing and failover |
|
No load balancer or dedicated proxy servers or special multicast Ethernet address |
|
All clustering features |
|
Remote sites and virtual IP address |
|
Uniform high availability solution |
|
Software clustering vs hardware clustering
|
|
|
|
Shared nothing vs a shared disk cluster |
|
|
|
Application High Availability vs Full Virtual Machine High Availability
|
|
|
|
High availability vs fault tolerance
|
|
|
|
Synchronous replication vs asynchronous replication
|
|
|
|
Byte-level file replication vs block-level disk replication
|
|
|
|
Heartbeat, failover and quorum to avoid 2 master nodes
|
|
|
|
Virtual IP address primary/secondary, network load balancing, failover
|
|
|
|
User's Guide
Application Modules
Release Notes
Presales documentation
Introduction
-
- Demonstration
- Examples of redundancy and high availability solution
- Evidian SafeKit sold in many different countries with Milestone
- 2 solutions: virtual machine or application cluster
- Distinctive advantages
- More information on the web site
- SafeKit training
-
- Cluster of virtual machines
- Mirror cluster
- Farm cluster
Installation, Console, CLI
- Install and setup / pptx
- Package installation
- Nodes setup
- Upgrade
- Web console / pptx
- Cluster configuration
- Configuration tab
- Control tab
- Monitor tab
- Advanced Configuration tab
- Troubleshooting
- Command line / pptx
- Cluster administration
- Module administration
- Control commands
- Troubleshooting
Advanced configuration
- Mirror module / pptx
- Mirror's states in action
- start_prim / stop_prim scripts
- userconfig.xml
- Heartbeat (<hearbeat>)
- Virtual IP address (<vip>)
- Real-time file replication (<rfs>)
- How real-time file replication works?
- Troubleshooting
- Farm module / pptx
- Farm's states in action
- start_both / stop_both scripts
- userconfig.xml
- Farm heartbeats (<farm>)
- Virtual IP address (<vip>)
- Troubleshooting
Advanced configuration
- Checkers / pptx
- Checkers in action
- userconfig.xml
- errd checker
- intf and ip checkers
- custom checker
- splitbrain checker for a mirror module
- tcp, ping, module checkers
- Troubleshooting
Support
- Support tools / pptx
- How to analyze snapshots?
- Best practises
- Evidian support / pptx
- Get permanent license key
- Register on support.evidian.com
- Call desk