User Tools

Site Tools



Install and setup MySQL HA using Percona XtraDB Multi-Master and HAProxy


  • Alvise Dorigo (INFN Padova)


  • 23-01-2015 - Added pre-req about mounted partition size's for /var/lib/mysql
  • 18-06-2014 - Added notes about start/stop/restart of each daemon of Percona cluster
  • 07-01-2014 - Added info for admin and generic user access to MySQL cluster database
  • 10-12-2013 - Added instructions to add new user in MySQL cluster
  • 05-12-2013 - Added instruction to update to latest keepalived version (1.2.9) which supports the unicast trasmission
  • 29-11-2013 - Added command to not allow xinetd logging successful connection (otherwise the logfile would grow too much, because haproxy connects every second to launch mysqlchk)
  • 29-11-2013 - Added command to change xinetd's log type (from syslog to dedicated log file)
  • 27-11-2013 - Added link to HAProxy/Keepalived
  • 27-11-2013 - Fixed the keepalived.conf: on the backup keepalived nodes, priority must be lowered by 1

Percona XtraDB MySQL Multi-Master


At least 3 nodes with:

  • CentOS 6.4 Updated
  • SELinux's enforcing "permissive"
  • EPEL 6-8
  • ip(6)tables disabled
  • ntpd ON
  • Each node should mount (usually in /var/lib/mysql) a disk or partition of adequate size (100 or better 200GB)

Note: if the nodes are virtual, the virtual instances must run on different hardware.

Install the Percona software

On all three nodes execute the commands:

rpm -Uhv
rpm --import
rpm -e --nodeps mysql-libs
yum -y install Percona-XtraDB-Cluster-server-55 Percona-XtraDB-Cluster-client-55 Percona-XtraDB-Cluster-galera-2

Configure Percona

On all three nodes execute the following commands (substitute IPLIST's value with the actual comma-separated IPs of your cluster):

export IPLIST=",,"
export MYIP=`ip -f inet addr show eth0 | grep inet | awk '{print $2}'|awk -F"/" '{print $1}'`
export CLUSTER_NAME="mysql_cluster"
cat << EOF > /etc/my.cnf
#set-variable = max_connect_errors=999999999
# Path to Galera library
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# In order for Galera to work correctly binlog format should be ROW
# MyISAM storage engine has only experimental support
# This is a recommended tuning variable for performance
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
# Node #1 address
# SST method
# Cluster name
# Authentication for SST method
yum -y install xinetd
echo "mysqlchk 9200/tcp" >> /etc/services 
chkconfig xinetd on
sed -i s'+/usr/bin/clustercheck+/usr/bin/clustercheck\n\tserver_args\t= clustercheckuser CLUSTERCHECK_PWD+' /etc/xinetd.d/mysqlchk
sed -i 's+log_type.*+log_type = FILE /var/log/xinetd.log+' /etc/xinetd.conf
sed -i 's+log_on_success.*+log_on_success  =+' /etc/xinetd.conf
service xinetd start

Bootstrap the Percona cluster

Temporary choose a node as primary. In the primary node execute the commands:

/etc/init.d/mysql start --wsrep-cluster-address="gcomm://"
chkconfig mysql on
mysql -u root -e "CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'SST_PWD'"
mysql -u root -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';"
mysql -u root -e "GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'CLUSTERCHECK_PWD'; FLUSH PRIVILEGES;"

On the other nodes execute:

/etc/init.d/mysql start
chkconfig mysql on

Now the MySQL-Percona cluster should be up & running (you can check with "ps -ef|grep mysql" that the processes are running on all nodes). On all nodes of the cluster execute this commands (substitute 192.168.122 with your net address):

mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.122.%' IDENTIFIED BY '' WITH GRANT OPTION;"

Basic checks

On any node check the correct working of the script /usr/bin/clustercheck:

[root@percona1 ~]# /usr/bin/clustercheck clustercheckuser CLUSTERCHECK_PWD
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.

Clustercheck, via xinetd, is able to communicate the sync status also to external machines; on your destkop try the following command:

dorigoa@lxadorigo 15:06:08 ~>telnet 9200
Connected to
Escape character is '^]'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.

Note that both commands above, are saying that the cluster is synced. If any problem occurred during installation/configuration, "not synced" is printed instead.

Notes about starting/stopping/restarting the Percona Cluster

Above, it has been showed how to bootstrap the cluster: the primary node must be started with the option --wsrep-cluster-address="gcomm:// and the other nodes without it (just: service mysql start).

When the cluster is running, each node can be simply restarted (without the --wsrep-cluster-address=gcomm:// option).

But be careful: not using the option is allowed only when at least one node is up. If the cluster is starting from a situation in which all nodes are OFF, one of them must be started with the option (the choice of which node is based on the assumption that that node has the last database's data update).



At least 2 nodes with:

  • CentOS 6.4 Updated
  • SELinux's enforcing "permissive"
  • EPEL 6-8
  • ip(6)tables disabled
  • ntpd ON

Install the HAProxy software

On all two nodes execute:

yum -y install haproxy

Configure HAProxy

On both two nodes execute the following commands (substitute the hostnames and the IPs with those of your cluster):

mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.orig
cat << EOF >	/etc/haproxy/haproxy.cfg
        log   local0
        log   local1 notice
        maxconn 4096
        uid 188
        gid 188
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        option redispatch
        maxconn 2000
        contimeout      5000
        clitimeout      50000
        srvtimeout      50000
listen mysql-cluster
    mode    tcp
    balance roundrobin
    option  httpchk
    server check port 9200 inter 12000 rise 3 fall 3
    server check port 9200 inter 12000 rise 3 fall 3
    server check port 9200 inter 12000 rise 3 fall 3
listen mysql-cluster-failover :43306
    mode    tcp
    balance leastconn
    option  httpchk
    server check port 9200
    server check port 9200 backup
    server check port 9200 backup
service haproxy start		
chkconfig haproxy on

Make the two HAProxy nodes redundant with Keepalived

Installing the newest version of Keepalived

We observed that the version of keepalived 1.2.7 coming from the Epel repositories, doesn't handle the unicast advertisement of the master node toward the backup nodes. If you don't want to flood your LAN with a lot of multiscast message, you have to install the 1.2.9 version of Keepalived by following this procedure:

rpm -ivh keepalived-1.2.7-3.el6.src.rpm
cd rpmbuild/SPECS/
mv keepalived.spec keepalived.spec.orig
sed 's/Version: 1.2.7/Version: 1.2.9/' < keepalived.spec.orig >keepalived.spec
cd -
yum -y install net-snmp-devel libnl-devel popt-devel rpm-build rpmlint kernel-devel gcc libtool make
rpmbuild -ba keepalived.spec
rpm -ivh ../RPMS/x86_64/keepalived-1.2.9-3.el6.x86_64.rpm

Otherwhise, just install the Keepalived package with this command:

yum install -y keepalived

Configuring Keepalived

On both two nodes create the configuration file /etc/keepalived/keepalived (you need to substitute the email address, the SMTP's IP address and the Virtual IP address with your values):

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.orig
cat << EOF > /etc/keepalived/keepalived.conf
global_defs {
notification_email {
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_script chk_haproxy {
script "killall -0 haproxy"
interval 1
vrrp_instance VI_1 {
interface eth0
state MASTER
    virtual_router_id 51
    priority 101 # on the nodes considered slaves, change 101 -> 100
    unicast_peer { # this is the other node's IP address
    advert_int 1
       authentication {
          auth_type PASS
          auth_pass 1111
 # check every second
 # add 2 points of prio if OK
 # 101 on master, 100 on slaves
   virtual_ipaddress {
 track_script {
echo << EOF >> /etc/sysctl.conf		
net.ipv4.ip_nonlocal_bind = 1
sysctl -p 
service keepalived start
chkconfig keepalived on

With the command "ip addr sh eth0" you can check which node the Virtual IP is associate with:

ip addr sh eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:f6:ed:35 brd ff:ff:ff:ff:ff:ff
    inet brd scope global eth0
    inet scope global eth0
    inet6 fe80::5054:ff:fef6:ed35/64 scope link 
       valid_lft forever preferred_lft forever

How connect to the MySQL Cluster as Admin

Administration of the clustered database (mysql -u root) is possible only if the user is logged on one node of the cluster (, with X = 1 or 2 or 3):

dorigoa@lxadorigo 11:42:54 ~>ssh -l root
Last login: Fri Dec 20 09:19:36 2013 from
[root@mysql-cluster-01 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4690147
Server version: 5.5.34 Percona XtraDB Cluster (GPL), wsrep_23.7.6.r3936
Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Please note that the virtual address cannot be used for an interactive access to a mysql cluster machine; it is, in fact, the VIP-associated FQDN of the HAProxy (which doesn't run the mysql server, but redirects connections to TCP port 3306 to one node of the cluster basing on roundrobin or failover algorithm); this address and its IP ( is needed when you want to use a non-admin client which wants to exploit the load balancing capability of the HAProxy as explained below.

How connect to the MySQL Cluster as generic user

When a user needs to connect to the MySQL Cluster, it will use the option '-h <HOST>' of the mysql command line. In this case, as only root is not allowed to connect from a remote host, this generic user can exploit the cluster alias

dorigoa@lxadorigo 13:08:18 ~>mysql -u keystone -h keystone -e "select * from endpoint"
| id                               | legacy_endpoint_id               | interface | region    | service_id                       | url                             | extra |
| 55a3e6cf263446629510a430070e8556 | 399575e2f7804ef283017a15b2f386e6 | admin     | regionOne | 02d1c4d4bb4e494e882be273dd940b9b | | {}    |
| 656621f6586140949d367fb076222d01 | 280045a6b1754b80bdb752ecc2fb3bc1 | public    | regionOne | 2ceadb76cc6f48639893a44e64631b75 |       | {}    |
| 7288cac2c0184563b5d6648918921181 | 399575e2f7804ef283017a15b2f386e6 | internal  | regionOne | 02d1c4d4bb4e494e882be273dd940b9b |  | {}    |
| 94a86c5e3bec4ac58b66e19aaface2ab | 280045a6b1754b80bdb752ecc2fb3bc1 | admin     | regionOne | 2ceadb76cc6f48639893a44e64631b75 |       | {}    |
| d5db044832764a4f8953b9589e786dbd | 280045a6b1754b80bdb752ecc2fb3bc1 | internal  | regionOne | 2ceadb76cc6f48639893a44e64631b75 |       | {}    |
| d60b906ed4844f7e8217c6843e9c38e6 | 399575e2f7804ef283017a15b2f386e6 | public    | regionOne | 02d1c4d4bb4e494e882be273dd940b9b |  | {}    |

Please note that in order to make a generic user able to connect to the MySQL cluster through the alias FQDN, as the corresponding VIP can be among anyone of the three HAProxy machines (, and, the GRANTS of this user have to be set for connections from the three mentioned HAProxy machines as explained in the next section.

How to add users to MySQL Cluster

Every connection from any host to the mysql cluster's Virtual IP (, appears to be from one of the 3 HAProxy nodes (ha-proxy-01, ha-proxy-02, ha-proxy-03). So, when adding a new user into the MySQL cluster, the HAProxy's addresses must be specified:

GRANT ALL ON mydatabase.* TO 'myuser'@'' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-01' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-02' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-03' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'localhost' IDENTIFIED BY '<YOUR_PASSWORD>';

Avoid a problem of 'too many open files'

Running the tool xtrabackup can procude the error too many open files. In order to avoid it, put the following lines in the file /etc/security/limits.conf :

* soft nofile 60000
* hard nofile 60000 

and the following line in the file /etc/sysctl.conf:

fs.file-max = 900000 

then run

sysctl -p

Alvise Dorigo 2013/11/19 19:40

progetti/cloud-areapd/mysql_high_availability.txt · Last modified: 2015/03/03 09:07 by