基于haproxy +keepalived 的 percona xtradb cluster 安装配置
安装准备工作
[[email protected] ~]# 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
[[email protected] ~]# 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 [email protected] 该命令只能在第一个PXC 节点启动,其他节点正常启动(service mysql stop)即可
[email protected]> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)
Create a table on the third node:
[email protected]> USE percona;
Database changed
[email protected]> 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:
[email protected]> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)
Retrieve rows from that table on the second node:
[email protected]> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
+---------+-----------+
1 row in set (0.00 sec)
[[email protected] ~]# 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 ;
每个节点都要执行
[[email protected] ~]# 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
[[email protected] ~]# 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
stats auth admin:admin
listen percona-cluster 0.0.0.0:3307
balance leastconn
option httpchk
mode tcp
server pxc1 192.168.137.151:3306 check port 9200 inter 5000 fastinter 2000 rise 2 fall 2
#server pxc2 192.168.137.152:3306 check port 9200 inter 5000 fastinter 2000 rise 2 fall 2
server pxc3 192.168.137.153:3306 check port 9200 inter 5000 fastinter 2000 rise 2 fall 2 backup
haproxy -f /etc/haproxy/haproxy.cfg 如果报如下的错误
[[email protected] haproxy]# haproxy -f /etc/haproxy/haproxy.cfg
[WARNING] 002/174731 (58402) : parsing [/etc/haproxy/haproxy.cfg:20] : 'option httplog' not usable with proxy 'percona-cluster' (needs 'mode http'). Falling back to 'option tcplog'.
[WARNING] 002/174731 (58402) : config : 'option forwardfor' ignored for proxy 'percona-cluster' as it requires HTTP mode.
listen percona-cluster 0.0.0.0:3307
balance leastconn
option httpchk
mode http
http://192.168.137.151:8888/dbs --用户和密码都是admin
以下是正常的状态
主节点尝试脱离集群
set global wsrep_desync=ON; 执行该命令的节点脱离集群 不同步
mysql> set global wsrep_desync=ON;
Query OK, 0 rows affected (0.00 sec)
主节点 脱离集群后的 状态
mysql> show status like '%wsrep%';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 3f5426a4-f04a-11e7-aa1d-2f26eb9ea91e |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 14 |
| wsrep_replicated | 10 |
| wsrep_replicated_bytes | 2379 |
| wsrep_repl_keys | 13 |
| wsrep_repl_keys_bytes | 334 |
| wsrep_repl_data_bytes | 1405 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 4 |
| wsrep_received_bytes | 159 |
| wsrep_local_commits | 1 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.500000 |
| wsrep_local_cached_downto | 5 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 100, 100 ] |
| wsrep_flow_control_interval_low | 100 |
| wsrep_flow_control_interval_high | 100 |
| wsrep_flow_control_status | OFF |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 2 |
| wsrep_local_state_comment | Donor/Desynced |
| wsrep_cert_index_size | 4 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 4205 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.137.151:3306 |
| wsrep_desync_count | 1 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 4329ba05-f04c-11e7-84ff-8e7893709287 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 3f5426a4-f04a-11e7-aa1d-2f26eb9ea91e |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 3.22(r8678538) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
67 rows in set (0.00 sec)
wsrep_local_state_comment | Donor/Desynced
============== keeplived 相关配置 =============
yum install keepalived -y pxc1/pxc2 安装即可
mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.sourcebak
vim /etc/keepalived/keepalived.conf
PXC1 主节点 配置
! Configuration File for keepalived
global_defs {
router_id haproxy
}
vrrp_script check_run {
script "killall -0 haproxy"
interval 1
weight 5
rise 1
fall 2
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 92
advert_int 1
authentication {
auth_type PASS
auth_pass haproxy
}
track_script {
check_run
}
virtual_ipaddress {
192.168.137.200/24 pxc eth0
}
}
PXC2 备节点配置
! Configuration File for keepalived
global_defs {
router_id haproxy
}
vrrp_script check_run {
script "killall -0 haproxy"
interval 1
weight 10
rise 1
fall 2
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass haproxy
}
track_script {
check_run
}
virtual_ipaddress {
192.168.137.200/24 pxc eth0
}
}
service keepalived start
chkconfig --level 2345 keepalived on
tail -f /var/log/messages //keepalived的日志
[[email protected] ~]# tail -f /var/log/messages
Feb 2 17:01:05 pxc1 dbus[687]: [system] Activating service name='org.fedoraproject.Setroubleshootd' (using servicehelper)
Feb 2 17:01:05 pxc1 dbus-daemon: dbus[687]: [system] Activating service name='org.fedoraproject.Setroubleshootd' (using servicehelper)
Feb 2 17:01:06 pxc1 dbus[687]: [system] Successfully activated service 'org.fedoraproject.Setroubleshootd'
Feb 2 17:01:06 pxc1 dbus-daemon: dbus[687]: [system] Successfully activated service 'org.fedoraproject.Setroubleshootd'
Feb 2 17:01:06 pxc1 xinetd[9463]: START: mysqlchk pid=18301 from=::ffff:192.168.137.151
Feb 2 17:01:06 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18301 duration=0(sec)
Feb 2 17:01:07 pxc1 setroubleshoot: SELinux is preventing keepalived from using the setpgid access on a process. For complete SELinux messages run: sealert -l eee501fb-c7f9-47f0-a7a0-c71ecb781499
Feb 2 17:01:07 pxc1 python: SELinux is preventing keepalived from using the setpgid access on a process.#012#012***** Plugin catchall (100. confidence) suggests **************************#012#012If you believe that keepalived should be allowed setpgid access
on processes labeled keepalived_t by default.#012Then you should report this as a bug.#012You can generate a local policy module to allow this access.#012Do#012allow this access for now by executing:#012# ausearch -c 'keepalived' --raw | audit2allow -M my-keepalived#012#
semodule -i my-keepalived.pp#012
Feb 2 17:01:07 pxc1 xinetd[9463]: START: mysqlchk pid=18315 from=::ffff:192.168.137.152
Feb 2 17:01:07 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18315 duration=0(sec)
Feb 2 17:01:11 pxc1 xinetd[9463]: START: mysqlchk pid=18331 from=::ffff:192.168.137.151
Feb 2 17:01:11 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18331 duration=0(sec)
Feb 2 17:01:12 pxc1 xinetd[9463]: START: mysqlchk pid=18340 from=::ffff:192.168.137.152
Feb 2 17:01:12 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18340 duration=0(sec)
Feb 2 17:01:16 pxc1 xinetd[9463]: START: mysqlchk pid=18355 from=::ffff:192.168.137.151
Feb 2 17:01:16 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18355 duration=0(sec)
Feb 2 17:01:17 pxc1 xinetd[9463]: START: mysqlchk pid=18364 from=::ffff:192.168.137.152
Feb 2 17:01:18 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18364 duration=1(sec)
Feb 2 17:01:21 pxc1 xinetd[9463]: START: mysqlchk pid=18379 from=::ffff:192.168.137.151
Feb 2 17:01:22 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18379 duration=1(sec)
Feb 2 17:01:22 pxc1 xinetd[9463]: START: mysqlchk pid=18388 from=::ffff:192.168.137.152
Feb 2 17:01:23 pxc1 xinetd[9463]: EXIT: mysqlchk status=0 pid=18388 duration=1(sec)
======模拟主节点监控状态异常,观察后端连接=====
kill haproxy 进程
kill keepalived 进程
set global wsrep_desync=ON;
[[email protected] ~]# ps -ef | grep haproxy
haproxy 9602 1 0 16:15 ? 00:00:00 haproxy -f /etc/haproxy/haproxy.cfg
root 18672 9472 0 17:02 pts/2 00:00:00 grep --color=auto haproxy
root 17300 1 0 16:58 ? 00:00:00 /usr/sbin/keepalived -D
root 17301 17300 0 16:58 ? 00:00:00 /usr/sbin/keepalived -D
root 17302 17300 0 16:58 ? 00:00:00 /usr/sbin/keepalived -D
root 19265 9472 0 17:04 pts/2 00:00:00 grep --color=auto keepalived
[[email protected] ~]# jobs
[[email protected] ~]# killall haproxy
[[email protected] ~]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service
mysql> set global wsrep_desync=ON;
Query OK, 0 rows affected (0.01 sec)
备库 查看变化
VIP 切换过来了
[email protected] ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:e5:29:6f brd ff:ff:ff:ff:ff:ff
inet 192.168.137.152/24 brd 192.168.137.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.137.200/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fee5:296f/64 scope link
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN qlen 1000
link/ether 52:54:00:f3:e7:16 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN qlen 1000
link/ether 52:54:00:f3:e7:16 brd ff:ff:ff:ff:ff:ff
==== 备节点 模拟haproxy keepalived 异常 的过程变化 ==
[[email protected] ~]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]# ps -ef | grep haproxy
haproxy 39712 1 0 17:14 ? 00:00:00 haproxy -f /etc/haproxy/haproxy.cfg
haproxy 42183 1 0 17:33 ? 00:00:00 haproxy -f /etc/haproxy/haproxy.cfg
root 42223 37029 0 17:33 pts/2 00:00:00 grep --color=auto haproxy
[[email protected] ~]# killall haproxy
[[email protected] ~]# ps -ef | grep haproxy
root 42226 37029 0 17:34 pts/2 00:00:00 grep --color=auto haproxy
mysql> set global wsrep_desync=ON;
Query OK, 0 rows affected (0.00 sec)
[[email protected] ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:ad:2a:f5 brd ff:ff:ff:ff:ff:ff
inet 192.168.137.151/24 brd 192.168.137.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.137.200/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fead:2af5/64 scope link
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN qlen 1000
link/ether 52:54:00:f3:e7:16 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN qlen 1000
link/ether 52:54:00:f3:e7:16 brd ff:ff:ff:ff:ff:ff
==== PXC1/PXC2 haproxy/keepalived 状态正常时 ===
[[email protected] ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:e5:29:6f brd ff:ff:ff:ff:ff:ff
inet 192.168.137.152/24 brd 192.168.137.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.137.200/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fee5:296f/64 scope link
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN qlen 1000
link/ether 52:54:00:f3:e7:16 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN qlen 1000
link/ether 52:54:00:f3:e7:16 brd ff:ff:ff:ff:ff:ff