Author:

• Alvise Dorigo (INFN Padova)

Changes:

• 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 main website

Installing Percona XtraDB Cluster on CentOS

Load balancing with HAProxy

Install HAProxy and Keepalived

MySQL-Galera Cluster With HAproxy

MySQL HA - Video from OpenStack Summit in HongKong, Nov 2013

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.

On all three nodes execute the commands:

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
rpm --import http://www.percona.com/redir/downloads/percona-release/RPM-GPG-KEY-percona
rpm -e --nodeps mysql-libs
yum -y install Percona-XtraDB-Cluster-server-55 Percona-XtraDB-Cluster-client-55 Percona-XtraDB-Cluster-galera-2

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

export IPLIST="192.168.122.51,192.168.122.52,192.168.122.53"
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
[mysqld]
#set-variable = max_connect_errors=999999999
datadir=/var/lib/mysql
user=mysql
innodb_log_file_size=64M
innodb_file_per_table=1
innodb_locks_unsafe_for_binlog=1
 
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://$IPLIST
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=$MYIP
# SST method
wsrep_sst_method=xtrabackup
# Cluster name
wsrep_cluster_name=$CLUSTER_NAME
# Authentication for SST method
wsrep_sst_auth="sstuser:SST_PWD"
EOF
 
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

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;"

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 percona1.pd.infn.it 9200
Trying 192.168.122.51...
Connected to percona1.pd.infn.it.
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.

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

On all two nodes execute:

yum -y install haproxy

On both two nodes execute the following commands (substitute the hostnames percona#.pd.infn.it and the IPs with those of your cluster):

mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.orig
cat << EOF >	/etc/haproxy/haproxy.cfg
global
        log 127.0.0.1   local0
        log 127.0.0.1   local1 notice
        maxconn 4096
        uid 188
        gid 188
        daemon
        #debug
        #quiet
 
defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        option redispatch
        maxconn 2000
        contimeout      5000
        clitimeout      50000
        srvtimeout      50000
 
listen mysql-cluster 0.0.0.0:33306
    mode    tcp
    balance roundrobin
    option  httpchk
 
    server percona1.pd.infn.it 192.168.122.51:3306 check port 9200 inter 12000 rise 3 fall 3
    server percona2.pd.infn.it 192.168.122.52:3306 check port 9200 inter 12000 rise 3 fall 3
    server percona3.pd.infn.it 192.168.122.53:3306 check port 9200 inter 12000 rise 3 fall 3
 
listen mysql-cluster-failover :43306
    mode    tcp
    balance leastconn
    option  httpchk
    server  percona1.pd.infn.it 192.168.122.51:3306 check port 9200
    server  percona2.pd.infn.it 192.168.122.52:3306 check port 9200 backup
    server  percona3.pd.infn.it 192.168.122.53:3306 check port 9200 backup
EOF
 
service haproxy start		
chkconfig haproxy on

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:

wget http://vault.centos.org/6.4/os/Source/SPackages/keepalived-1.2.7-3.el6.src.rpm
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 ../SOURCES
wget http://www.keepalived.org/software/keepalived-1.2.9.tar.gz
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

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 {
alvise.dorigo@pd.infn.it
}
notification_email_from noreply-keepalived-gridops@pd.infn.it
smtp_server 192.168.122.1
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
    smtp_alert
    virtual_router_id 51
    priority 101 # on the nodes considered slaves, change 101 -> 100
    unicast_peer {
        192.168.122.53 # 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 {
       192.168.122.56
   } 
 }
 track_script {
    chk_haproxy
 }
 EOF
 
echo << EOF >> /etc/sysctl.conf		
net.ipv4.ip_nonlocal_bind = 1
EOF		
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 192.168.122.54/24 brd 192.168.122.255 scope global eth0
    inet 192.168.122.56/32 scope global eth0
    inet6 fe80::5054:ff:fef6:ed35/64 scope link 
       valid_lft forever preferred_lft forever

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

dorigoa@lxadorigo 11:42:54 ~>ssh mysql-cluster-01.cloud.pd.infn.it -l root
Last login: Fri Dec 20 09:19:36 2013 from mac-grid-01.pd.infn.it
[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
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> 

Please note that the virtual address mysql-cluster.cloud.pd.infn.it 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 (192.168.60.10) 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.

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 mysql-cluster.cloud.pd.infn.it:

dorigoa@lxadorigo 13:08:18 ~>mysql -u keystone -h mysql-cluster.cloud.pd.infn.it keystone -e "select * from endpoint"
+----------------------------------+----------------------------------+-----------+-----------+----------------------------------+---------------------------------+-------+
| id                               | legacy_endpoint_id               | interface | region    | service_id                       | url                             | extra |
+----------------------------------+----------------------------------+-----------+-----------+----------------------------------+---------------------------------+-------+
| 55a3e6cf263446629510a430070e8556 | 399575e2f7804ef283017a15b2f386e6 | admin     | regionOne | 02d1c4d4bb4e494e882be273dd940b9b | http://192.168.60.40:35357/v2.0 | {}    |
| 656621f6586140949d367fb076222d01 | 280045a6b1754b80bdb752ecc2fb3bc1 | public    | regionOne | 2ceadb76cc6f48639893a44e64631b75 | http://192.168.60.40:9292       | {}    |
| 7288cac2c0184563b5d6648918921181 | 399575e2f7804ef283017a15b2f386e6 | internal  | regionOne | 02d1c4d4bb4e494e882be273dd940b9b | http://192.168.60.40:5000/v2.0  | {}    |
| 94a86c5e3bec4ac58b66e19aaface2ab | 280045a6b1754b80bdb752ecc2fb3bc1 | admin     | regionOne | 2ceadb76cc6f48639893a44e64631b75 | http://192.168.60.40:9292       | {}    |
| d5db044832764a4f8953b9589e786dbd | 280045a6b1754b80bdb752ecc2fb3bc1 | internal  | regionOne | 2ceadb76cc6f48639893a44e64631b75 | http://192.168.60.40:9292       | {}    |
| d60b906ed4844f7e8217c6843e9c38e6 | 399575e2f7804ef283017a15b2f386e6 | public    | regionOne | 02d1c4d4bb4e494e882be273dd940b9b | http://192.168.60.40:5000/v2.0  | {}    |
+----------------------------------+----------------------------------+-----------+-----------+----------------------------------+---------------------------------+-------+

Please note that in order to make a generic user able to connect to the MySQL cluster through the alias FQDN mysql-cluster.cloud.pd.infn.it(192.168.60.10), as the corresponding VIP can be among anyone of the three HAProxy machines (ha-proxy-01.cloud.pd.infn.it, ha-proxy-02.cloud.pd.infn.it and ha-proxy-03.cloud.pd.infn.it), the GRANTS of this user have to be set for connections from the three mentioned HAProxy machines as explained in the next section.

Every connection from any host to the mysql cluster's Virtual IP (192.168.60.10, mysql-cluster.cloud.pd.infn.it) 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'@'ha-proxy-01.cloud.pd.infn.it' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-01' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-02.cloud.pd.infn.it' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-02' IDENTIFIED BY '<YOUR_PASSWORD>';
GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-03.cloud.pd.infn.it' 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>';

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