Author:
3 nodes with:
yum install -y http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
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
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
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;"
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.
3 nodes with:
firewall-cmd --zone=public --add-port=3306/tcp firewall-cmd --permanent --zone=public --add-port=3306/tcp
On all 3 nodes execute:
yum -y install haproxy
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 here and here we decided to take a mixed approach:
So, basically, the different Openstack services connect to three different clusters (listening on three different ports):
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
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
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.