使用改密码登陆MySQL，修改成自己想要的密码
mysql> alter user 'root'@'localhost' idnetified by '123456';
在pxc-linux-28MySQL中创建SST同步用户
mysql> GRANT PROCESS,RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'sync_rep'@'192.168.%.%' IDENTIFIED BY 'abc123';
mysql> flush privileges;
State Snapshot Transfer 是可以让one node (donor) 通过数据拷贝升级至joining node (joiner)。
一个新节点加入至集群中，需要同步数据，那么SST可以传输数据，让新节点成为集群的一部分。
SST的三个有效方法：mysqldump, rsync andxtrabackup。不过多解释，目前最好用的是xtrabackup
查看集群状态
集群验证
[root@pxc-linux-28 ~]# mysql -uroot -p
mysql> show variables like 'version';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| version | 5.7.19-17-57 |
+---------------+--------------+
1 row in set (0.00 sec)
mysql> create database pxcdb;
mysql> use pxcdb;
mysql> create table t1(id tinyint,ename varchar(20));
mysql> insert into t1 values(1,'Leshami');
在29上进行验证
[root@pxc-linux-29 ~]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| pxcdb |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 29 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> select * from pxcdb.t1;
+------+---------+
| id | ename |
+------+---------+
| 1 | Leshami |
+------+---------+
1 row in set (0.00 sec)
--查看集群状态
mysql> show status like '%wsrep_clust%';
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 13 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | aeb87793-ebb2-11e7-b33e-eeaf4988bbe4 |
| wsrep_cluster_status | Primary |
+--------------------------+--------------------------------------+
4 rows in set (0.00 sec)
mysql> show status like 'wsrep_connected';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| wsrep_connected | ON |
+-----------------+-------+
1 row in set (0.00 sec)
openssl版本过低导致的错误
2017-12-28T09:23:19.605353Z 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib64/galera3/libgalera_smm.so:
symbol SSL_COMP_free_compression_methods, version libssl.so.10 not defined in file libssl.so.10 with link time reference
2017-12-28T09:23:19.605379Z 0 [ERROR] WSREP: Failed to load wsrep_provider (/usr/lib64/galera3/libgalera_smm.so).
Error: Invalid argument (code: 22). Reverting to no provider.
2017-12-28T09:23:19.605386Z 0 [Note] WSREP: Setting wsrep_ready to false
[root@pxc-linux-29 ~]# rpm -qa|grep openssl
openssl-1.0.1e-42.el7.9.x86_64
openssl-libs-1.0.1e-42.el7.9.x86_64
[root@pxc-linux-29 ~]# yum update openssl -y 需要升级openssl，建议全部升级后再启动集群，这问题在CentOS 7.4.1708不存在即openssl版本较新
简单配置文件：
cat /etc/haproxy/haproxy.cfg
#---------------------------------------------------------------------
# Example configuration for a possible web 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 to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
#
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
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 http
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
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000
#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend pxc-front
bind *:3306
mode tcp
default_backend pxc-back
frontend statas-front
bind *:8088
mode http
default_backend stats-back
#---------------------------------------------------------------------
# static backend for serving up images, stylesheets and such
#---------------------------------------------------------------------
backend pxc-back
mode tcp
balance leastconn
option httpchk
server pxc-linux-28 192.168.253.28:3306 check port 9200 inter 12000 rise 3 fall 3
server pxc-linux-29 192.168.253.29:3306 check port 9200 inter 12000 rise 3 fall 3
server pxc-linux-30 192.168.253.30:3306 check port 9200 inter 12000 rise 3 fall 3
#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth pxcstats:xxwzopop
在cluster的MySQL上创建用户
(一个节点创建，会被复制到其它节点)
监控用帐号：
grant usage on *.* to 'pxc-monitor'@'%' identified by 'testpxc';
服务测试帐号：
grant all privileges on *.* to 'zxw'@'%' identified by 'xxwzopop';
测试
[root@pxc-linux-28 ~]# clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
更改/etc/services添加mysqlchk的端口号：
mysqlchk 9200/tcp #mysqlchk 配置xinetd
vi /etc/xinetd.d/mysqlchk
该文件保持默认即可
测试
#for i in `seq 1 1000`;do mysql -h 192.168.253.31 -P3306 -upxc -pabc123 -e "select @@hostname;";done
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| @@hostname |
+--------------+
| pxc-linux-30 |
+--------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| @@hostname |
+--------------+
| pxc-linux-28 |
+--------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| @@hostname |
+--------------+
| pxc-linux-29 |
+--------------+
可以看到负载了
查看haproxy状态
http://192.168.253.31:8088/haproxy/stats
帐号密码是：pxcstats:xxwzopop
/etc/haproxy/haproxy.cfg 指定的。
QQ群⑧：
窥视卡
雷达卡
发表于 2019-1-1 08:54:04
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡