====== Install and setup MySQL HA using Percona XtraDB Multi-Master and HAProxy ====== 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 ===== Reference links ===== [[http://www.percona.com|Percona main website]] [[http://www.percona.com/doc/percona-xtradb-cluster/howtos/cenots_howto.html|Installing Percona XtraDB Cluster on CentOS]] [[http://www.percona.com/doc/percona-xtradb-cluster/howtos/haproxy.html|Load balancing with HAProxy]] [[http://support.severalnines.com/entries/23612682-Install-HAProxy-and-Keepalived-Virtual-IP-|Install HAProxy and Keepalived]] [[http://www.sebastien-han.fr/blog/2012/04/08/mysql-galera-cluster-with-haproxy|MySQL-Galera Cluster With HAproxy]] [[http://www.openstack.org/summit/openstack-summit-hong-kong-2013/session-videos/presentation/highly-available-mysql-for-openstack|MySQL HA - Video from OpenStack Summit in HongKong, Nov 2013]] ===== Percona XtraDB MySQL Multi-Master ===== ==== Prerequisites ==== 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 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 ==== 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="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 ==== 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 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. ==== 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). ===== HAProxy ===== ==== Prerequisites ==== 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 ''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 ==== 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: 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 === 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 { 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: 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 ===== 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 (''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. ===== 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 ' 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. ===== How to add users to MySQL Cluster ===== 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 ''; GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-01' IDENTIFIED BY ''; GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-02.cloud.pd.infn.it' IDENTIFIED BY ''; GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-02' IDENTIFIED BY ''; GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-03.cloud.pd.infn.it' IDENTIFIED BY ''; GRANT ALL ON mydatabase.* TO 'myuser'@'ha-proxy-03' IDENTIFIED BY ''; GRANT ALL ON mydatabase.* TO 'myuser'@'localhost' IDENTIFIED BY ''; ===== 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@pd.infn.it|Alvise Dorigo]] 2013/11/19 19:40//