MySQL active-passive cluster

We will use the iSCSI Lun defined in our iSCSI cluster as a shared storage and we will run MySQL in active-passive (fail-over) mode using Pacemaker and Corosync cluster engine.

The cluster will have to connect to the iSCSI target, mount the iSCSI partition on one node and start a MySQL service which has all its data on this partition.

We will need the following resources and resource agents (RA) on this cluster :

  • virtual IP → ocf:heartbeat:IPAddr2

  • iSCSI initiator → ocf:heartbeat:iscsi

  • Mount file-system → ocf:heartbeat:Filesystem

  • MySQL database server → ocf:heartbeat:mysql

The configuration of the resources appears to be like this :

primitive iscsi-mount ocf:heartbeat:Filesystem \
   params device="/dev/sda1" directory="/mnt" fstype="ext4" \
   op start interval="0" timeout="120" \
   op stop interval="0" timeout="120"
primitive iscsi1 ocf:heartbeat:iscsi \
   params portal="192.168.20.122" target="iqn.2011-11.begetest.net" \
   op start interval="0" timeout="20" \
   op stop interval="0" timeout="20" \
   op monitor interval="120" timeout="30" start-delay="0"
primitive mysql ocf:heartbeat:mysql \
   params pid="/var/run/mysqld/mysqld.pid" datadir="/mnt/mysql/data" \
   binary="/usr/bin/mysqld_safe" config="/mnt/mysql/conf/my.cnf" \
   op start interval="0" timeout="30" \
   op stop interval="0" timeout="30"
primitive vip ocf:heartbeat:IPaddr2 \
   params ip="192.168.20.121" \
   op start interval="0" timeout="20" \
   op stop interval="0" timeout="20" \
  op monitor interval="10" timeout="20" start-delay="0"
colocation iscsi-with-mount 100: clone-iscsi1 iscsi-mount
colocation mysql-with-mount 100: iscsi-mount mysql
colocation vip-with-mount 100: vip iscsi-mount
order mount-after-iscsi inf: clone-iscsi1 iscsi-mount
order mysql-after-mount inf: iscsi-mount mysql


Explanations :

  1. iscsi1 is the resource to discover the iSCSI targets and their LUN's. It makes them available to the local system using the standard devices files (cfr. /dev/disk and sub-directories).

  2. The iSCSI LUN is seen here as device /dev/sda (cfr. The /dev/disk/by-path directory to know the device file in use in your case). We partition it as we do usually for standard internal disk using fdisk. The partition we have created, /dev/sda1, is formatted using the Extended4 (ext4) Linux file-system. We mount it under /mnt using the resource iscsi-mount.

  3. In this file-system, we create the following directories : mysql/data, mysql/conf and mysql/log. We create a MySQL configuration file (/mnt/mysql/conf/my.cnf) containing this :

[mysqld]
datadir=/mnt/mysql/data
socket=/mnt/mysql/mysql.sock
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address=192.168.20.121


[mysqld_safe]
log-error=/mnt/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

This configuration allows MySQL to listen only on the VIP address and use the shared file-system to store all his information.