基于haproxy +keepalived 的 percona xtradb cluster 安装配置

Percona XtraDB cluster 安装与运维维护

安装准备工作 


[root@pxc3 ~]# cat /etc/hosts 
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.151 pxc1
192.168.137.152 pxc2
192.168.137.153 pxc3


systemctl disable firewalld.service 




[root@pxc3 ~]# cat /etc/sysconfig/selinux | grep disabled 
#     disabled - No SELinux policy is loaded.
SELINUX=disabled


3个节点 都要做 




安装配置
yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm 
yum install -y Percona-XtraDB-Cluster-57.x86_64




service mysql restart
grep 'temporary password' /var/log/mysqld.log
ALTER USER 'root'@'localhost' IDENTIFIED BY 'oracle';
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'oracle';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;


service mysql stop
节点2/3 同样执行上面的操作 



vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.137.151,192.168.137.152,192.168.137.153
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads=20
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.137.151
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:oracle



第 2 个 Node,除了 wsrep_node_name， wsrep_node_address 外，其它配置保持一样
wsrep_node_name=pxc2 wsrep_node_address=10.6.1.149
第 3 个 Node,除了 wsrep_node_name， wsrep_node_address 外，其它配置保持一样
wsrep_node_name=pxc3
wsrep_node_address=10.6.1.150


systemctl start mysql@bootstrap.service    该命令只能在第一个PXC 节点启动,其他节点正常启动(service mysql stop)即可 



mysql@pxc2> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)
Create a table on the third node:
mysql@pxc3> USE percona;
Database changed
mysql@pxc3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)
Insert records on the first node:
mysql@pxc1> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)
Retrieve rows from that table on the second node:
mysql@pxc2> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
+---------+-----------+
1 row in set (0.00 sec)






[root@pxc1 ~]# clustercheck  
HTTP/1.1 503 Service Unavailable
Content-Type: text/plain
Connection: close
Content-Length: 57


Percona XtraDB Cluster Node is not synced or non-PRIM. 


vim /usr/bin/clustercheck 


GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!'; 
flush privileges ;

每个节点都要执行

[root@pxc1 ~]# clustercheck  

HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40


=====Haproxy配置=====
echo 'mysqlchk      9200/tcp    # MySQL check' >> /etc/services
yum -y install xinetd


以上操作在 pxc1/pxc2 操作即可 


vi /etc/xinetd.d/mysqlchk  --看看就行 不需要修改
yum install psmisc -y

keeplived 基于haproxy 切换

xinetd  -d -f /etc/xinetd.conf  -filelog /var/log/xinetd.log -cc 1

[root@pxc1 ~]# netstat  -ntlp | grep 9200
tcp6      0      0 :::9200                :::*                    LISTEN      29722/xinetd






以上操作在 pxc1/pxc2/pxc3操作即可




yum install -y haproxy  pxc1/pxc2操作即可


cd /etc/haproxy/
cp -rp haproxy.cfg haproxy.cfg.bak
> haproxy.cfg


vim /etc/haproxy/haproxy.cfg
global
    log        127.0.0.1 local2
    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                    tcp
    log                    global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor      except 127.0.0.0/8
    option                  redispatch
    retries                3
    timeout http-request    10s
    timeout queue          1m
    timeout connect        10s
    timeout client          1m
    timeout server          1m
    maxconn                3000


listen  admin_stats 0.0.0.0:8888 
        mode        http 
        stats uri  /dbs 
        stats realm    Global\ statistics 
        s