How to Build a MySQL HA Cluster with Pacemaker & Corosync (Step‑by‑Step)

Outline

Preface

Experiment Topology

Experiment Environment

Experiment Steps

Summary

Preface

In the previous article we introduced a simple Nginx HA solution with Pacemaker+Corosync; this article explains how to achieve MySQL high availability using Pacemaker+Corosync, manually editing the Corosync configuration file.

Experiment Topology

Experiment Environment

Host        IP        Purpose node1.anyisalin.com  172.16.1.2  MySQL service, HA node node2.anyisalin.com  172.16.1.3  MySQL service, HA node nfs.anyisalin.com  172.16.1.4  Provides NFS service for database files

Note: In this experiment all hosts have SELinux and iptables disabled.

Experiment Steps

Preparation

High‑availability clusters require mutual trust, consistent hostname resolution, and time synchronization across all nodes.

Configure hosts file synchronization

Configure mutual trust

Time synchronization

Install HA Components and Configure

Install and manually configure Pacemaker+Corosync on both nodes.

yum install corosync pacemaker -y &> /dev/null && echo success
success
ssh node2.anyisalin.com "yum install corosync pacemaker -y &> /dev/null && echo success"
success

Edit configuration file

[root@node1 corosync]# vim corosync.conf

totem {
    version: 2
    crypto_cipher: none
    crypto_hash: none
    interface {
        ringnumber: 0
        bindnetaddr: 172.16.1.0
        mcastaddr: 239.185.1.31
        mcastport: 5405
        ttl: 1
    }
}

nodelist {
    node {
        ring0_addr: 172.16.1.2
        nodeid: 1
    }
    node {
        ring0_addr: 172.16.1.3
        nodeid: 2
    }
}

logging {
    fileline: off
    to_stderr: no
    to_logfile: yes
    logfile: /var/log/cluster/corosync.log
    to_syslog: no
    debug: off
    timestamp: on
    logger_subsys {
        subsys: QUORUM
        debug: off
    }
}

quorum {
    provider: corosync_votequorum
}

Start corosync and pacemaker

scp corosync.conf node2.anyisalin.com:/etc/corosync/
systemctl start corosync pacemkaer
ssh node2.anyisalin.com "systemctl start corosync pacemkaer"

Check cluster node status

crm status   # all nodes online
Last updated: Sun Apr 10 22:23:13 2016      Last change: Sun Apr 10 22:16:43 2016 by root via cibadmin on node1.anyisalin.com
Stack: corosync
Current DC: node1.anyisalin.com (version 1.1.13-10.el7_2.2-44eb2dd) - partition with quorum
2 nodes and 0 resources configured

Online: [ node1.anyisalin.com node2.anyisalin.com ]

Configure NFS

Complex NFS configuration for shared database directory.

[root@nfs ~]# vim /etc/exports
/datadir    172.16.1.0/24(rw,no_root_squash)

[root@nfs ~]# useradd -u 3306 mysql  # create mysql user

[root@nfs ~]# mkdir /datadir
[root@nfs ~]# setfacl -m u:mysql:rwx /datadir/  # grant permissions

[root@nfs ~]# exportfs -rv   # re-export directory
exporting 172.16.1.0/24:/datadir

Configure MySQL

Install MySQL from generic binary package (see previous LAMP article for details).

On node1:

tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/src/
cd /usr/src/mysql-5.5.33-linux2.6-x86_64/
groupadd -g 3306 -r mysql
useradd -u 3306 -g mysql mysql
mkdir /datadir
mount -t nfs 172.16.1.4:/datadir /datadir/
./scripts/mysql_install_db --user=mysql --datadir=/datadir
ls /datadir/
cp support-files/my-large.cnf /etc/my.cnf
vim /etc/my.cnf   # add:
# datadir=/datadir
# innodb_file_per_table = on
# skip_name_resolve = on
cp support-files/mysql.server /etc/init.d/mysqld
ln -sv /usr/src/mysql-5.5.33-linux2.6-x86_64/ /usr/local/mysql
service mysqld start
/usr/local/mysql/bin/mysql   # login success

On node2 (mount NFS, copy config, start MySQL):

tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/src/
mkdir /datadir
mount -t nfs 172.16.1.4:/datadir /datadir/
ls /datadir/
scp node1.anyisalin.com:/etc/my.cnf /etc/
scp node1.anyisalin.com:/etc/init.d/mysqld /etc/init.d/
groupadd -g 3306 -r mysql
useradd -g mysql -u 3306 mysql
ln -sv /usr/src/mysql-5.5.33-linux2.6-x86_64/ /usr/local/mysql
service mysqld start   # fails because node1 is running
ssh node1.anyisalin.com "service mysqld stop"
service mysqld start   # succeeds
/usr/local/mysql/bin/mysql   # login success
# After testing, stop MySQL and unmount NFS
service mysqld stop
umount /datadir/

Configure HA Resources

Define HA resources in Pacemaker.

crm(live)# configure
primitive dataip ocf:heartbeat:IPaddr params ip=172.16.1.8
primitive datastore ocf:heartbeat:Filesystem params device="172.16.1.4:/datadir" fstype="nfs" directory="/datadir" op 
primitive mysql service:mysqld
property stonith-enabled=false
colocation dataip_with_datastore inf: mysql ( dataip datastore )
order dataip_then_webstore Mandatory: dataip datastore
order datastore_then_mysql Mandatory: datastore mysql
commit

Test

Summary

We have built a MySQL HA cluster, though the NFS share remains a single point of failure; future work will replace it with DRBD for true high availability. This series focuses on practical experiments rather than theory.