vim /etc/xinetd.d/mysqlchk
server = /usr/bin/clustercheck
修改为如果不修过可以cp /usr/local/Percona-XtraDB-Cluster/bin/clustercheck
到 /usr/bin/
server = /usr/sbin/clustercheck
vim /etc/services
添加
mysqlchk 9200/tcp # mysqlchk 4.5my.cnf 配置
192.168.1.55
vim /etc/my.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/apps/data/mysql.sock
port=3306
[mysqld]
user=mysql
server-id=10055
datadir=/apps/data
socket=/apps/data/mysql.sock
log-error=/apps/log/mysqld/mysqld.log
pid-file=/apps/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
port=3306
character_set_server = utf8
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Path to Galera library
wsrep_provider=/usr/local/Percona-XtraDB-Cluster/lib/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address='gcomm://192.168.1.55,192.168.1.56,192.168.1.57'
#wsrep_cluster_address=gcomm://
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
wsrep_sst_receive_address=192.168.1.55:6010
wsrep_node_incoming_address=192.168.1.55
wsrep_provider_options = "gmcast.listen_addr=tcp://192.168.1.55;ist.recv_addr=192.168.1.55:6020;"
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
wsrep_node_address=192.168.1.55
# Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-1
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#wsrep_sst_method=rsync
#Authentication for SST method # 修改自己的创建的账号、密码
wsrep_sst_auth="sstuser:456789"
192.168.1.56
vim /etc/my.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/apps/data/mysql.sock
port=3307
[mysqld]
user=mysql
server-id=10056
datadir=/apps/data
socket=/apps/data/mysql.sock
log-error=/apps/log/mysqld/mysqld.log
pid-file=/apps/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
port=3307
character_set_server = utf8
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Path to Galera library
wsrep_provider=/usr/local/Percona-XtraDB-Cluster/lib/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address='gcomm://192.168.1.55,192.168.1.56,192.168.1.57'
#wsrep_cluster_address=gcomm://
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
wsrep_sst_receive_address=192.168.1.56:6010
wsrep_node_incoming_address=192.168.1.56
wsrep_provider_options = "gmcast.listen_addr=tcp://192.168.1.56;ist.recv_addr=192.168.1.56:6020;"
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
wsrep_node_address=192.168.1.56
# Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-2
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#wsrep_sst_method=rsync
#Authentication for SST method # 修改自己的创建的账号、密码
wsrep_sst_auth="sstuser:456789"192.168.1.57
vim /etc/my.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/apps/data/mysql.sock
port=3307
[mysqld]
user=mysql
server-id=10057
datadir=/apps/data
socket=/apps/data/mysql.sock
log-error=/apps/log/mysqld/mysqld.log
pid-file=/apps/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
port=3307
character_set_server = utf8
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Path to Galera library
wsrep_provider=/usr/local/Percona-XtraDB-Cluster/lib/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address='gcomm://192.168.1.55,192.168.1.56,192.168.1.57'
#wsrep_cluster_address=gcomm://
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
wsrep_sst_receive_address=192.168.1.57:6010
wsrep_node_incoming_address=192.168.1.57
wsrep_provider_options = "gmcast.listen_addr=tcp://192.168.1.57;ist.recv_addr=192.168.1.57:6020;"
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
wsrep_node_address=192.168.1.57
# Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-3
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#wsrep_sst_method=rsync
#Authentication for SST method
wsrep_sst_auth="sstuser:456789" # 修改自己的创建的账号、密码 4.6 初始化数据库
192.168.1.55
初始化数据
mysqld --initialize --user=mysql --datadir="/apps/data"
启动数据库
/etc/init.d/mysqld start --wsrep-cluster-address="gcomm://"
关闭数据库 MySQL5.7以后都要密码才能进去
/etc/init.d/mysqld stop
安全模式期待MySQL修改密码
mysqld_safe --wsrep-cluster-address=gcomm:// --user=mysql --skip-grant-tables --skip-networking &
use mysql；
update user set authentication_string=Password('123456') where user='root';
flush privileges;
shutdown
exit 退出MySQL
ps -ef | grep mysql
直到MySQL完全关闭 再次启动MySQL
/etc/init.d/mysqld start --wsrep-cluster-address="gcomm://"
进入MySQL 创建同步账号及修改root密码
mysql -u root -p123456
创建MySQL同步账号及密码
GRANT ALL PRIVILEGES ON *.* TO sstuser@'%' IDENTIFIED BY '456789'; FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO sstuser@'localhost' IDENTIFIED BY '456789'; FLUSH PRIVILEGES;
修改root 远程能够连接
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;
创建健康检查数据库账号密码
GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
如果使用自己账号密码请修改
vim /usr/sbin/clustercheck 对应的账号密码
192.168.1.56 192.168.1.57 其它节点直接启动
service mysqld start
重启第一个节点 192.168.1.55
service mysqld restart 出现错误
/etc/init.d/mysqld bootstrap-pxc 进行启动
启动完成登陆MySQL
mysql -u root -p123456
查看MySQL 节点数
mysql> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'wsrep_incoming_addresses';
+--------------------------+-------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------+
| wsrep_incoming_addresses | 192.168.1.57:3307,192.168.1.56:3307,192.168.1.55:3306 |
+--------------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
创建测试数据看看是否同步
mysql> create database test;
Query OK, 1 row affected (0.01 sec) 4.7 数据库健康检查启动
systemctl start xinetd.service
查看9200端口是否打开
netstat -anlp | grep 9200
tcp6 0 0 :::9200 :::* LISTEN 73323/xinetd
测试端口是否正常
telnet 192.168.1.55 9200
Escape character is '^]'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.
返回503 首先执行
/usr/sbin/clustercheck
是否返回200 如果不是检查链接数据库的账号密码
返回200正常 就请修改 vim /etc/xinetd.d/mysqlchk
user root
重启systemctl restart xinetd.service
再次测试如果返回200正常就ok 还是报错就请查看系统日志修复 六、编译安装 haproxy (192.168.1.56,192.168.1.57)
安装 haproxy 日志记录支持
yum -y install rsyslog
解压 haproxy-1.7.0.tar.gz
tar -xvf /usr/local/src/haproxy-1.7.0.tar.gz -C /usr/local/src/
cd /usr/local/src/haproxy-1.7.0
make TARGET=linux31 PREFIX=/usr/local/haproxy
make install PREFIX=/usr/local/haproxy
创建文件夹haproxy
mkdir -pv /usr/local/haproxy/{conf,run,log}创建haproxy 启动脚本
vim /etc/init.d/haproxy#! /bin/bash
# chkconfig: - 85 15
# description: haproxy is a World Wide Web server. It is used to serve
PROGDIR=/usr/local/haproxy
PROGNAME=haproxy
DAEMON=$PROGDIR/sbin/$PROGNAME
CONFIG=$PROGDIR/conf/$PROGNAME.conf
PIDFILE=$PROGDIR/run/$PROGNAME.pid
DESC="HAProxy daemon"
SCRIPTNAME=/usr/local/haproxy/init.d/$PROGNAME
# Gracefully exit if the package has been removed.
test -x $DAEMON || exit 0
start()
{
echo -n "Starting $DESC: $PROGNAME"
$DAEMON -f $CONFIG
echo "."
}
stop()
{ echo -n "Stopping $DESC: $PROGNAME"
cat $PIDFILE | xargs kill
echo "."
}
reload()
{ echo -n "reloading $DESC: $PROGNAME"
$DAEMON -f $CONFIG -p $PIDFILE -sf $(cat $PIDFILE)
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
reload)
reload
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart|reload}" >&2
exit 1
;;
esac
exit 0chmod +x /etc/init.d/haproxy # 可执行权限 创建haproxy.conf
vim /usr/local/haproxy/conf/haproxy.conf global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 50000
chroot /usr/local/haproxy
uid 99
gid 99
daemon
nbproc 2
pidfile /usr/local/haproxy/run/haproxy.pid
#debug
#quiet
defaults
log global
mode tcp
option tcplog
option dontlognull
option forwardfor
option redispatch
retries 3
timeout connect 3000
timeout client 50000
timeout server 50000
frontend admin_stat
bind *:8888
mode http
default_backend stats-back
frontend pxc-front
bind *:3306
mode tcp
default_backend pxc-back
frontend pxc-onenode-front
bind *:3308
mode tcp
default_backend pxc-onenode
backend pxc-back
mode tcp
balance roundrobin
option httpchk
server node1 192.168.1.55:3306 check port 9200 inter 12000 rise 3 fall 3
server node2 192.168.1.56:3307 check port 9200 inter 12000 rise 3 fall 3
server node3 192.168.1.57:3307 check port 9200 inter 12000 rise 3 fall 3
backend pxc-onenode
mode tcp
balance roundrobin
option httpchk
server node1 192.168.1.55:3306 check port 9200 inter 12000 rise 3 fall 3
server node2 192.168.1.56:3307 check port 9200 inter 12000 rise 3 fall 3 backup
server node3 192.168.1.57:3307 check port 9200 inter 12000 rise 3 fall 3 backup
backend stats-back
mode http
balance roundrobin
stats uri /admin?stats
stats auth admin:admin
stats realm Haproxy\ StatisticsHaproxy rsyslog 日志配置
vim /etc/rsyslog.conf
#$ModLoad imudp 修改 $ModLoad imudp
#$UDPServerRun 514 修改 $UDPServerRun 514
#添加 Haproxy 日志记录路径
local0.* /usr/local/haproxy/log/haproxy.log重启rsyslog
systemctl restart rsyslog.service
启动Haproxy
service haproxy start
加入开机启动
chkconfig haproxy on
查看haproxy 是否启动
ps -ef | grep haproxy
haproxy 访问端口8888 账号密码 admin:admin
http://192.168.1.56:8888/admin?stats
http://192.168.1.57:8888/admin?stats
QQ群⑧：
窥视卡
雷达卡
发表于 2018-12-28 13:28:22
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡