MySQL High Availability with Synchronous Replication and Failover

Evidian SafeKit brings high availability to MySQL. This article explains how to implement quickly a MySQL cluster without shared disk and without specific skills. A free trial is offered.

How the Evidian SafeKit software simply implements MySQL high availability with real-time synchronous replication and failover without shared disk

How the Evidian SafeKit mirror cluster implements MySQL high availability with synchronous replication and failover?

On the previous figure, the server 1/PRIM (Windows or Linux) runs MySQL (any edition). Users are connected to the virtual IP address of the mirror cluster. SafeKit replicates files opened by MySQL in real time. Only changes in the files are replicated across the network, thus limiting traffic (byte-level file replication). Names of file directories containing MySQL database are simply configured in SafeKit. There are no pre-requisites on disk organization for the two servers. Directories to replicate may be located in the system disk. SafeKit implements synchronous replication with no data loss on failure contrary to asynchronous replication.

In case of server 1 failure, there is an automatic failover on server 2 with restart of MySQL. Then, when server 1 is restarted, SafeKit implements failback with reintegration of MySQL database without stopping MySQL on server 2. Finally, the system returns to synchronous replication between server 2 and server 1. The administrator can decide to swap the role of primary and secondary and return to a server 1 running MySQL. The swap can also be done automatically by configuration.

Configuration overview of MySQL high availability with synchronous replication and failover

With SafeKit, you can configure either a farm application module or a mirror application module according the high availability architecture suited for an application. For MySQL high availability with synchronous replication and failover, the right module is the mirror module.

Configuration overview of MySQL high availability with synchronous replication and failover

The configuration files for MySQL high availability are given  for Windows here and for Linux here.

They include:

1. the MySQL stop and start scripts,

2. the configuration file userconfig.xml which contains:

Deployment of MySQL high availability with synchronous replication and failover requires no specific IT skills:

    • install MySQL on two standard Windows servers
    • install the SafeKit software on both servers
    • install the mysql.safe module

SafeKit configuration files on Windows for MySQL high availability with synchronous replication and failover

Installation instructions

userconfig.xml

<!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

@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

@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

SafeKit configuration files on Linux for MySQL high availability with synchronous replication and failover

Installation instructions

userconfig.xml

<!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

#!/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

#!/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

Demonstration

This demonstration is made with Microsoft SQL Server Express but the operating mode is the same as MySQL.

More on SafeKit

Other examples of high availability modules:

Mirror modules

Windows

Linux

Microsoft SQL ServerWindows module-
OracleWindows moduleLinux module
MySQLWindows moduleLinux module
PostgreSQLWindows moduleLinux module
FirebirdWindows moduleLinux module
Hyper-VWindows module-
Hanwha SSMWindows module-
Milestone XProtectWindows module-
Generic moduleWindows moduleLinux module

Farm modules

Windows

Linux

IIS moduleWindows module-
Apache moduleWindows moduleLinux module
Generic moduleWindows moduleLinux module
contact
CONTACT
Demonstration

Evidian SafeKit Pricing





White Papers

contact
NEWS

To receive Evidian news, please fill the following form.