Percona XtraDB Cluster+HAProxy配置

2015-01-06

数据库

最近公司有个异地多机房数据同步需求，mysql原生支持双主同步，所以只能另寻他法，于是找到了Percona XtraDB Cluster。这个可以理解为给Mysql打了个补丁，以便支持多主同步。

测试环境：centos 6.5

IP分配:

192.168.0.154(DB)
192.168.0.152(DB)
192.168.0.153(DB)
192.168.0.151(HA) 首先安装Percona XtraDB Cluster的源： yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

然后 yum install Percona-XtraDB-Cluster-56

安装完毕后，修改/etc/my.cnf：

[mysqld]
server_id=4
datadir=/var/lib/mysql
user=mysql
# Path to Galeralibrary
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connectionURL contains the IPs of node#1, node#2 and node#3----所有节点的ip
wsrep_cluster_address=gcomm://192.168.0.152,192.168.0.154,192.168.0.153
# In order for Galerato work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storageengine has only experimental support
default_storage_engine=InnoDB
# This changes howInnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address----本机ip
wsrep_node_address=192.168.0.154
# SST method----节点间同步的方式
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authentication forSST method----来做节点间数据同步的账号密码
wsrep_sst_auth="root:asdasd"
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log

注意，修改不同节点的server_id以及 wsrep_node_address。

然后在192.168.0.152上执行/etc/init.d/mysql bootstrap-pxc

网上有些文章说需要修改 wsrep_cluster_address=gcomm://，在新版本中不需要了，上面这句就是初始化集群。结果如下：

[root@localhost ~]# /etc/init.d/mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).. SUCCESS!

然后根据配置文件修改用于同步的用户名和密码，由于是实验环境我偷懒直接使用root了：

mysqladmin -u root password asdasd

进入mysql终端后可以看当前信息：

mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
...
| wsrep_incoming_addresses   | 192.168.0.152:3306                   |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
...
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

当第一个节点成功启动后，启动其他节点，注意此时命令是

/etc/init.d/mysql start

正常情况下很快就会启动完成，如果启动了很长时间后出现如下提示：

Shutting down MySQL (Percona XtraDB Cluster)..... SUCCESS!
Starting MySQL (Percona XtraDB Cluster).................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... ERROR!
 ERROR! MySQL (Percona XtraDB Cluster) server startup failed!
 ERROR! Failed to restart server.

但日志里没相关错误信息，那么请 检查selinux是否关闭以及防火墙4444和4567端口 ！！！（我就忘了防火墙的原因纠结了好久）

启动成功提示如下：

[root@test4 ~]# service mysql start
Starting MySQL (Percona XtraDB Cluster)...... SUCCESS!
[root@test4 ~]# netstat -anp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      963/sshd            
tcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LISTEN      5184/mysqld         
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      1041/master         
tcp        0      0 192.168.0.154:22            192.168.0.37:38500          ESTABLISHED 1080/sshd           
tcp        0      0 192.168.0.154:4567          192.168.0.153:53681         ESTABLISHED 5184/mysqld         
tcp        0      0 192.168.0.154:59348         192.168.0.152:4567          ESTABLISHED 5184/mysqld

可以看出，4567端口也处于监听状态。此时，在任意机器上进行数据库操作其他2个也会自动同步了。

此时，手动关闭152,然后在154上插入数据，153也同步了，同时自动把152从集群中删除了：

| wsrep_incoming_addresses     | 192.168.0.154:3306,192.168.0.153:3306 |

再启动152后，数据也自动同步了。

如果非常非常不幸，集群中所有节点都挂掉了，修复后需要在最后挂掉的节点上执行bootstrap-pxc命令，这样才能拯救多一些的数据。

如果重启时候报错：

[root@test3 ~]# service mysql start
 ERROR! MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists
Stale sst_in_progress file in datadir
Starting MySQL (Percona XtraDB Cluster)State transfer in progress, setting sleep higher
.. ERROR! The server quit without updating PID file (/var/lib/mysql/test3.pid).
 ERROR! MySQL (Percona XtraDB Cluster) server startup failed!

直接删除/var/lock/subsys/mysql即可。还有一点需要注意的，数据库表需要使用INNODB而不是MYISAM引擎，否则会出现表结构同步了而数据无法同步的情况。

安装HAproxy：yum install haproxy

修改配置文件，位于/etc/haproxy/haproxy.cfg:

#---------------------------------------------------------------------
# Example configuration for a possibleweb application.  See the
# full configuration options online.
#  http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
   # to have these messages end up in /var/log/haproxy.log you will
   # need to:
   # 1) configure syslog to accept network log events.  This is done
   #    by adding the '-r' option tothe SYSLOGD_OPTIONS in
   #    /etc/sysconfig/syslog
   # 2) configure local2 events to go to the /var/log/haproxy.log
   #   file. A line like thefollowing can be added to
   #   /etc/sysconfig/syslog
   #
   #    local2.*                       /var/log/haproxy.log
   #
   log         127.0.0.1      local0
   log         127.0.0.1      local1 notice
   chroot      /var/lib/haproxy
   pidfile     /var/run/haproxy.pid
   maxconn     4000
   user        haproxy
   group       haproxy
   daemon
   # turn on stats unix socket
   stats socket /var/lib/haproxy/stats
#---------------------------------------------------------------------
# common defaults that all the'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
   mode                    http
   log                     global
   option                  tcplog
   option                 dontlognull
#  option http-server-close
#  option forwardfor       except127.0.0.0/8
   option                  redispatch
   retries                 3
   maxconn                 2000
   timeout connect         5s
   timeout client          50s
   timeout server          50s
#  timeout http-keep-alive 10s
   timeout check           10s
listen mysql-cluster 0.0.0.0:3306
    mode tcp
    balance roundrobin
    server node1 192.168.0.152:3306 check
    server node2 192.168.0.153:3306 check
    server node3 192.168.0.154:3306 check
listen status 192.168.0.151:8080
     stats enable
     stats uri /status
     stats auth admin:admin
     stats realm (haproxy\ statistic)

启动服务后，访问192.168.0.151：8080/status登录即可看到界面。对外则使用192.168.0.151：3306访问数据库即可。

—-20150120更新—-

对于上面的HA配置，默认是监控第4层，换言之如果由于某情况下3306端口开放而MYSQL实际并没提供服务时，HA就无法解决这种情况。为了模拟这种情况，停止某个节点的MYSQL服务后，使用NC监听3306端口，成功欺骗了HA。为了解决这中情况，我们就需要针对应用层进行监控。

首先在节点上安装xinetd:yum install -y xinetd

然后编辑/etc/services，添加

mysqlchk        9200/tcp

然后编辑/usr/bin/clustercheck，修改

MYSQL_USERNAME="${1-root}"
MYSQL_PASSWORD="${2-asdasd}"

这里我偷懒使用root，大家根据实际情况修改。保存后启动xinetd服务

[root@test4 ~]# /etc/init.d/xinetd start
Starting xinetd:                                           [  OK  ]

此时9200端口应该已经处于了监听状态，执行检测命令:

[root@test5 ~]# clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.

接下来修改HA节点的配置文件，修改成：

    listen mysql-cluster 0.0.0.0:3306
    mode tcp
    balance roundrobin
    option httpchk
    server node1 192.168.0.152:3306 check port 9200 inter 12000 rise 3 fall 3
    server node2 192.168.0.153:3306 check port 9200 inter 12000 rise 3 fall 3
    server node3 192.168.0.154:3306 check port 9200 inter 12000 rise 3 fall 3

保存后重启HAPROXY即可，如果使用了KEEPALIVE(这里)把备份HA配置也修改成一样的。此时就是基于应用层的监控了。

Percona XtraDB Cluster+HAProxy配置

Percona XtraDB Cluster+HAProxy配置

Recommend

Colorful Websites for your Inspiration

My worst nightmare...

Rev1 spots investment opportunities sooner while cutting research time in half

django自带的评论模块

Great IT in education website

The great domain telephone test

Are operating systems important anymore?

How not to do XBox 360 support

Node.js 版本管理工具 nvm

社区和商业

About Joyk