INFN wiki

User Tools

Site Tools

Trace:
progetti:cloud-areapd:mysql_high_availability

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
progetti:cloud-areapd:mysql_high_availability [2014/01/07 12:11] – [How connect to the MySQL Cluster as generic user] dorigoa@infn.itprogetti:cloud-areapd:mysql_high_availability [2015/03/03 09:07] (current) – [How to add users to MySQL Cluster] dorigoa@infn.it
Line 1: Line 1:
 +====== 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:
 +<code bash>
 +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
 +
 +</code>
 +==== Configure Percona ====
 +On all three nodes execute the following commands (substitute IPLIST's value with the actual comma-separated IPs of your cluster):
 +<code bash>
 +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
 +</code>
 +==== Bootstrap the Percona cluster ====
 +Temporary choose a node as primary. In the primary node execute the commands:
 +<code bash>
 +/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;"
 +</code>
 +On the other nodes execute:
 +<code bash>
 +/etc/init.d/mysql start
 +chkconfig mysql on
 +</code>
 +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):
 +<code bash>
 +mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.122.%' IDENTIFIED BY '' WITH GRANT OPTION;"
 +</code>
 +==== Basic checks ====
 +On any node check the correct working of the script /usr/bin/clustercheck:
 +<code bash>
 +[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.
 +</code>
 +
 +Clustercheck, via xinetd, is able to communicate the sync status also to external machines; on your destkop try the following command:
 +<code bash>
 +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.
 +</code>
 +
 +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:
 +<code bash>
 +yum -y install haproxy
 +</code>
 +==== 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):
 +<code bash>
 +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
 +</code>
 +==== 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:
 +<code bash>
 +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
 +</code>
 +Otherwhise, just install the Keepalived package with this command:
 +<code bash>
 +yum install -y keepalived
 +</code> 
 +=== 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):
 +<code bash>
 +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
 +</code>
 +With the command "''ip addr sh eth0''" you can check which node the Virtual IP is associate with:
 +<code bash>
 +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
 +</code>
 +===== 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):
 +<code bash>
 +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> 
 +</code>
 +
 +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 <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'':
 +<code bash>
 +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  | {}    |
 ++----------------------------------+----------------------------------+-----------+-----------+----------------------------------+---------------------------------+-------+
 +</code>
 +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:
 +<code bash>
 +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>';
 +</code>
 +===== 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 '':
 +<code bash>
 +* soft nofile 60000
 +* hard nofile 60000 
 +</code>
 +and the following line in the file ''/etc/sysctl.conf'':
 +<code bash>
 +fs.file-max = 900000 
 +</code>
 +then run 
 +<code bash>
 +sysctl -p
 +</code>
 + --- //[[Alvise.Dorigo@pd.infn.it|Alvise Dorigo]] 2013/11/19 19:40//
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki