===== Install and setup mysql percona xtradb - haproxy - keepalived ======
Author:
* Paolo E. Mazzon (DEI)
* Matteo Menguzzato (DFA)
* Gianpietro Sella (DISC)
===== Percona XtraDB MySQL Multi-Master =====
==== Prerequisites ====
3 nodes with:
* CentOS 7
* SELinux's enforcing “permissive”
* EPEL 7
yum install -y http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
* ntpd ON
* Each node should mount (usually in /var/lib/mysql) a disk or partition of adequate size (100 or better 200GB)
==== Configure firewalld ====
firewall-cmd --zone=public --add-port=9200/tcp
firewall-cmd --zone=public --add-port=3306/tcp
firewall-cmd --zone=public --add-port=4306/tcp
firewall-cmd --zone=public --add-port=5306/tcp
firewall-cmd --zone=public --add-port=4567/tcp
firewall-cmd --zone=public --add-port=4444/tcp
firewall-cmd --zone=public --add-port=4568/tcp
firewall-cmd --permanent --zone=public --add-port=9200/tcp
firewall-cmd --permanent --zone=public --add-port=3306/tcp
firewall-cmd --permanent --zone=public --add-port=4306/tcp
firewall-cmd --permanent --zone=public --add-port=5306/tcp
firewall-cmd --permanent --zone=public --add-port=4567/tcp
firewall-cmd --permanent --zone=public --add-port=4444/tcp
firewall-cmd --permanent --zone=public --add-port=4568/tcp
==== Install the Percona software ====
On all three nodes execute the commands:
rpm -Uvh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum list | grep percona
rpm -e --nodeps mysql-libs
yum -y install Percona-XtraDB-Cluster-server-56 Percona-XtraDB-Cluster-client-56 Percona-XtraDB-Cluster-galera-3
yum -y install xinetd
Create new /etc/my.cnf with this entry:
[mysqld]
datadir = /var/lib/mysql
user = mysql
binlog_format = ROW
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 2G
wsrep_cluster_address = gcomm://192.168.xxx.yyy,192.168.xxx.zzz,192.168.xxx.kkk #IP of 3 nodes
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_node_address = 192.168.xxx.yyy #IP local node
wsrep_cluster_name = mysql_cluster
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = "sstuser:password_for_sstuser"
[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog
insert in ''/etc/services'' "mysqlchk 9200/tcp" and remove wap-wsp row if exist
Execute
sed -i s'+/usr/bin/clustercheck+/usr/bin/clustercheck\n\tserver_args\t= clustercheckuser password_for_clustercheck_user+' /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
systemctl start xinetd.service
systemctl enable xinetd.service
Reboot all nodes
==== Bootstrap the Percona cluster ====
Temporary choose a node as primary.
In the primary node execute the commands:
systemctl start mysql@bootstrap.service
systemctl enable mysql
Setup privileges for replication and chek of the cluster status; also update mysql root's password:
$ mysql -u root
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'password_for_sstuser';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'password_for_clusterchk_user';
mysql> UPDATE mysql.user SET password=PASSWORD("password_for_root") WHERE user='root';
mysql> FLUSH PRIVILEGES;
On the other nodes execute:
systemctl start mysql.service
systemctl enable mysql.service
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 these commands (substitute 192.168.xxx with your net address):
mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.xxx.%' IDENTIFIED BY '' WITH GRANT OPTION;"
==== Basic checks ====
On any node check the correct working of the script ''/usr/bin/clustercheck'':
[root@..... ~]# /usr/bin/clustercheck clustercheckuser password_for_clusterchk_user
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:
xxxxx@yyyy 15:06:08 ~>telnet 192.168.xxx.yyy 9200 #IP of nodes
Trying 192.168.xxx.yyy...
Connected to 192.168.xxx.yyy.
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.
===== HAProxy =====
==== Prerequisites ====
3 nodes with:
* CentOS 7
* SELinux's enforcing set to “permissive”
* EPEL 7 repository configured
* Configured firewall
firewall-cmd --zone=public --add-port=3306/tcp
firewall-cmd --permanent --zone=public --add-port=3306/tcp
* ntpd ON
==== Install the HAProxy software ====
On all 3 nodes execute:
yum -y install haproxy
==== A glimpse on the future ====
This chapter has been written after the whole cluster was confugured. It is here to justify the technical decisions taken.
MySQL cluster can be configured in two ways: active/active (more performance) or active/backup (more resilient).
Due to potential deadlock problems illustrated [[http://www.gossamer-threads.com/lists/openstack/operators/41332|here]] and
[[http://www.percona.com/blog/2014/09/11/openstack-users-shed-light-on-percona-xtradb-cluster-deadlock-issues/|here]] we decided to take a mixed approach:
* configure the cluster in active/backup mode
* roudrobin the three active servers
* distribute the various OpenStack services between the three different active servers
* configure each other server as backup
So, basically, the different Openstack services connect to three different clusters (listening on three different ports):
- mysql-cluster-one (port 3306)
* cld-blu-08 as primary
* cld-blu-09 and cld-blu-10 as backup
- mysql-cluster-two (port 4306)
* cld-blu-09 as primary
* cld-blu-08 and cld-blu-10 as backup
- mysql-cluster-three (port 5306)
* cld-blu-10 as primary
* cld-blu-08 and cld-blu-09 as backup
{{:progetti:cloud-areapd:ced-c:mysql-three-heads.jpg|}}
==== Configure HAProxy ====
On all 3 nodes execute the following commands (substitute hostnames and IPs with those of your cluster):
mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.orig
Create new ''haproxy.cfg'' with these entries:
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-one
bind 192.168.60.180:3306
mode tcp
balance leastconn
option httpchk
default-server on-marked-down shutdown-sessions on-marked-up shutdown-backup-sessions
server cld-blu-08.cloud.pd.infn.it 192.168.60.157:3306 check port 9200 inter 12000 rise 3 fall 3
server cld-blu-09.cloud.pd.infn.it 192.168.60.158:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server cld-blu-10.cloud.pd.infn.it 192.168.60.159:3306 check port 9200 inter 12000 rise 3 fall 3 backup
listen mysql-cluster-two
bind 192.168.60.180:4306
mode tcp
balance leastconn
option httpchk
default-server on-marked-down shutdown-sessions on-marked-up shutdown-backup-sessions
server cld-blu-08.cloud.pd.infn.it 192.168.60.157:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server cld-blu-09.cloud.pd.infn.it 192.168.60.158:3306 check port 9200 inter 12000 rise 3 fall 3
server cld-blu-10.cloud.pd.infn.it 192.168.60.159:3306 check port 9200 inter 12000 rise 3 fall 3 backup
listen mysql-cluster-three
bind 192.168.60.180:5306
mode tcp
balance leastconn
option httpchk
default-server on-marked-down shutdown-sessions on-marked-up shutdown-backup-sessions
server cld-blu-08.cloud.pd.infn.it 192.168.60.157:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server cld-blu-09.cloud.pd.infn.it 192.168.60.158:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server cld-blu-10.cloud.pd.infn.it 192.168.60.159:3306 check port 9200 inter 12000 rise 3 fall 3
Start the service:
systemctl start haproxy.service
systemctl enable haproxy.service
==== Install and configure Keepalived ====
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
Create keepalived.conf whith following entries:
global_defs {
notification_email {
xxx@yyyy #email
}
notification_email_from noreply-keepalived-gridops@pd.infn.it
smtp_server xxx.xxx.xxx.xxx #server smtp
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.xxx.yyy # this is the other node's IP address
192.168.xxx.zzz
}
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.xxx.kkk dev em1 #private VIP
xxx.xxx.xxx.xxx dev em3 #pubblic VIP
}
}
track_script {
chk_haproxy
}
Finally:
echo "net.ipv4.ip_nonlocal_bind = 1" >> /etc/sysctl.conf
sysctl -p
yum install psmisc # in case killall command is not present
systemctl start keeepalived.service
systemctl enable keepalived.service
With the command ''ip addr sh eth0'' you can check which node is holding the Virtual IP.