使用Keepalived，Haproxy，Percona-XtraDB-Cluster构建mysql集群

一、架构图

二、机器分部 centos7

pxc1.node1.com       192.168.80.130

pxc2.node2.com       192.168.80.131

pxc3.node3.com       192.168.80.132

keepalived：192.168.80.131    192.168.80.132     虚拟ip(ip)：192.168.80.200 #对外提供的ip

haproxy：192.168.80.131    192.168.80.132 

三、安装部署

前提准备：

在安装之前，建议关闭三台机的selinux和防火墙，或者在防火墙中开放以下端口：3306、4444、4567、4568，否则可能会导致服务启动失败。这四个端口在PXC集群中的作用如下：

3306    数据库对外提供服务的端口
4444    镜像数据传输SST，集群数据同步端口，全量同步，新节点加入时起作用
4567    集群节点间相互通信的端口
4568    增量数据同步IST，节点下线、重启后使用该端口，增量同步数据。

 

关闭selinux:

临时关闭：

[root@localhost ~]# getenforce
Enforcing

[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Permissive

 

永久关闭：

[root@localhost ~]# vim /etc/sysconfig/selinux

SELINUX=enforcing 改为 SELINUX=disabled

重启服务reboot

 

1.在三个节点上安装官方yum源repo配置文件

官方yum源地址：http://repo.percona.com/release/centos/latest/RPMS/x86_64/ 
或者执行以下命令：
[root@localhost /]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

2.在三个节点上配置好yum源后，分别执行以下命令安装PXC：

[root@localhost /]# yum install Percona-XtraDB-Cluster-57

3.启动三个节点的mysql服务并修改三个节点的mysql默认密码

启动mysql服务：
[root@localhost /]#  systemctl start mysql

修改mysql默认密码(mysql默认密码可在/var/log/mysqld.log)中查看：

[root@localhost /]#  vi  /var/log/mysqld.log

 [Note] A temporary password is generated for root@localhost: rkMXb7doO>Pu

使用该默认密码登录mysql:

[root@localhost /]#  mysql -uroot -prkMXb7doO>Pu  -P3306
mysql> alter user 'root'@'localhost' identified by 'admin';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> quit;

 

修改完三个节点的mysql默认密码后停止mysql服务：

[root@localhost /]#  systemctl stop mysql

4.修改三个节点的pcx配置文件

(1)192.168.80.130配置文件如下：

[root@localhost /]# vim /etc/my.cnf

[mysqld]
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.80.130,192.168.80.131,192.168.80.132"
wsrep_sst_auth=root:root123456
wsrep_cluster_name=Percona
wsrep_sst_method=xtrabackup-v2
wsrep_node_address=192.168.80.130
wsrep_slave_threads=2
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

启动服务：

[root@localhost /]#systemctl start mysql@bootstrap.service

注意：该节点作为初始化PXC集群的节点要是以systemctl mysql@bootstrap.service 方式启动，另外两个节点以普通方式启动即可 systemctl start mysql

在初始化启动节点1的mysql后，需要创建用于节点直接同步数据的账户。

登录mysql：

[root@localhost /]#mysql -uroot -proot123456 -P3306

mysql> GRANT RELOAD,LOCK TABLES,PROCESS,REPLICATION CLIENT ON *.* TO 'syncUser'@'localhost' IDENTIFIED BY 'root123456';

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.02 sec)

此处应注意，该账户只需要在节点1上创建即可，且账户的host为localhost。 

查看状态信息：

[root@localhost /]#systemctl status mysql@bootstrap.service

● mysql@bootstrap.service - Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap
   Loaded: loaded (/usr/lib/systemd/system/mysql@.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2018-09-06 03:13:55 EDT; 23h ago
  Process: 5240 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS)
  Process: 5199 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
 Main PID: 5239 (mysqld_safe)
   CGroup: /system.slice/system-mysql.slice/mysql@bootstrap.service
           ├─5239 /bin/sh /usr/bin/mysqld_safe --basedir=/usr  --wsrep-new-cluster 
           └─5776 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera3/libgalera_smm.so --wsrep-provider=/usr/lib64/libgalera_smm.so --wsrep-new-cluster --log-error=/var/log/mys...

Sep 06 03:13:44 localhost.localdomain systemd[1]: Starting Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap...
Sep 06 03:13:45 localhost.localdomain mysql-systemd[5240]: State transfer in progress, setting sleep higher
Sep 06 03:13:45 localhost.localdomain mysqld_safe[5239]: 2018-09-06T07:13:45.284716Z mysqld_safe Logging to '/var/log/mysqld.log'.
Sep 06 03:13:45 localhost.localdomain mysqld_safe[5239]: 2018-09-06T07:13:45.288687Z mysqld_safe Logging to '/var/log/mysqld.log'.
Sep 06 03:13:45 localhost.localdomain mysqld_safe[5239]: 2018-09-06T07:13:45.310588Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Sep 06 03:13:45 localhost.localdomain mysqld_safe[5239]: 2018-09-06T07:13:45.319956Z mysqld_safe Skipping wsrep-recover for cec911a0-ac1c-11e8-826d-3beb27d030d4:18 pair
Sep 06 03:13:45 localhost.localdomain mysqld_safe[5239]: 2018-09-06T07:13:45.321677Z mysqld_safe Assigning cec911a0-ac1c-11e8-826d-3beb27d030d4:18 to wsrep_start_position
Sep 06 03:13:55 localhost.localdomain mysql-systemd[5240]: SUCCESS!
Sep 06 03:13:55 localhost.localdomain systemd[1]: Started Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap.

(2)192.168.80.131配置文件如下：

[root@localhost /]# vim /etc/my.cnf

[mysqld]
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.80.130,192.168.80.131,192.168.80.132"
wsrep_sst_auth=root:root123456
wsrep_cluster_name=Percona
wsrep_sst_method=xtrabackup-v2
wsrep_node_address=192.168.80.131
wsrep_slave_threads=2
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

启动服务：

[root@localhost /]#systemctl start mysql

(3)192.168.80.132配置文件如下：

[root@localhost /]# vim /etc/my.cnf

[mysqld]
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.80.130,192.168.80.131,192.168.80.132"
wsrep_sst_auth=root:root123456
wsrep_cluster_name=Percona
wsrep_sst_method=xtrabackup-v2
wsrep_node_address=192.168.80.132
wsrep_slave_threads=2
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

启动服务：

[root@localhost /]#systemctl start mysql

 

到这里mysql安装完成，并服务已经启动。如果安装过程中有出现什么错误，可查看mysql日志文件，再做相应排查。

如果一切顺利，没有问题。可登录mysql查看集群几点信息

 

[root@localhost /]# mysql -uroot -proot123456 -P3306

wsrep_cluster_size 表示当前有多少节点

安装完成测试一下三个节点是否能同步数据

首先在130节点登录并创建库和表

mysql> CREATE DATABASE percona;
Query OK, 1 row affected (0.02 sec)

mysql> USE percona;
Database changed
mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.00 sec)

 

在节点131和节点132上验证数据是否同步：

mysql> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.00 sec)

 

还可以接着在 131  、132 上新增 删除 修改数据，验证是否同步

 

五、分别在 131  、132 上安装keepalived

[root@localhost /]# yum install keepalived -y

1、在131下配置keepalived.conf

备份keepalived.cnf默认的配置文件

[root@localhost /]# cd /etc/keepalived

[root@localhost /]# cp keepalived.conf keepalived.conf.bak

[root@localhost /]# vim  keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id LVS_DEVEL
}
vrrp_script chk_http_port {

   #check_haproxy.sh 根据自己的路径填写
   script "/etc/keepalived/check_haproxy.sh"
   interval 2
   weight 3

  }

vrrp_instance VI_1 {
    state MASTER

    #interface 后面的值请填写 自己机器 ifconfig 出来的网卡信息
    interface ens33
    virtual_router_id 55
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.80.200/24
}
track_script {
        chk_http_port
    }
}

 

定义haproxy监控脚本：

[root@localhost /]# vim /etc/keepalived/check_haproxy.sh
#!/bin/bash
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then

     #填写自己服务器的haproxy执行文件的位置
     /usr/sbin/haproxy start
fi
sleep 2
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then

        #填写自己服务器的keepalived 执行文件的位置
       /usr/sbin/keepalived stop
Fi

 

给脚本赋予执行权限

[root@localhost /]#  chmod +x check_haproxy.sh

把keepalived.conf和check_haproxy.sh文件复制到pxc3节点的对应目录下

[root@localhost /]# scp keepalived.conf check_haproxy.sh  root@192.168.80.132:/etc/keepalived

2、在132上编辑keepalived.conf

[root@localhost /]#  vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id LVS_DEVEL
}
vrrp_script chk_http_port {
   script "/etc/keepalived/check_haproxy.sh"
   interval 2
   weight 3

  }

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 55
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.80.200/24
}
track_script {
        chk_http_port
    }
}
 

修改state为BACKUP，并修改priority优先级要低于主节点

 

六、分别在 131 、132 节点上安装haproxy

[root@localhost /]#  yum install haproxy –y

1、在131上配置 haproxy.cfg

[root@localhost /]# cd /etc/haproxy/

 [root@localhost /]# cp haproxy.cfg haproxy.cfg.bak

 [root@localhost /]# cat haproxy.cfg

 [root@localhost /]# vim 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

    stats socket /var/lib/haproxy/stats

defaults
    mode                    tcp
    log                     global
    option                  dontlognull
    option http-server-close
    option                  redispatch
    retries                 3
    timeout connect         5000ms
    timeout client          5000ms
    timeout server          5000ms
    maxconn                 3000

listen  mysql
        bind 0.0.0.0:3307
        mode tcp
        balance leastconn
        timeout client  10800s     #这个参数配置程序与haproxy的链接超时时间
        timeout server  10800s     #这个参数配置haproxy与mysql链接超时时间
                                   #这里的超时时间不是指连接过程的超时时间，而是指连接上以后
#多少时间内没有心跳，操作这个时间就认为超时，然后断开连接。
        server mysql1 192.168.80.130:3306 weight 1 check  inter 1s rise 2 fall 2
        server mysql2 192.168.80.131:3306 weight 1 check  inter 1s rise 2 fall 2
        server mysql3 192.168.80.132:3306 weight 1 check  inter 1s rise 2 fall 2
listen stats_auth 0.0.0.0:8081
        mode http
        stats enable
        stats uri /admin-status
        stats auth      admin:password
 

 

拷贝haproxy配置文件到pxc3节点的对应路径下

[root@localhost /]# scp haproxy.cfg root@192.168.80.132:/etc/haproxy

分别在131  、132上启动haproxy和keepalived

[root@localhost /]# systemctl start haproxy

[root@localhost /]# systemctl start keepalived

查看是否都有进程了

keepalived日志默认在/var/log/messages

haproxy在centos7上默认没有日志，需要修改配置：参考https://www.cnblogs.com/yangxiaoyi/p/6919163.html

七、测试

由于前面已经测试了pcx同步，这里不再叙述。这里测试keepalived虚拟出来的ip是否可用

客户端连接只需要连接VIP地址即可，端口为3307

[root@localhost /]#  mysql -uroot -proot123456 -P 3307 -h 192.168.1.200

[root@localhost sbin]# mysql -uroot -proot123456 -P3306 -h192.168.80.200
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.22-22-57-log Percona XtraDB Cluster (GPL), Release rel22, Revision da86071, WSREP version 29.26, wsrep_29.26

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, 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> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
 

证明可以登录，大功告成。

 

这次主要参考了以下几篇文章：

https://www.cnblogs.com/ivictor/p/5584398.html

https://blog.youkuaiyun.com/geoffreychan/article/details/80499956

https://www.cnblogs.com/zzzhfo/p/6070575.html

https://www.cnblogs.com/clsn/p/8052649.html

https://www.cnblogs.com/jefflee168/p/7442127.html