本文详细介绍了使用MHA实现MySQL主从复制的高可用性解决方案，以及通过Keepalived和LVS实现web集群和ProxySQL的高可用性配置流程。包括MHA的配置、MySQL主从节点的设置、客户端配置、路由与DNS设置、防火墙规则、NFS服务器配置、Discuz部署、rsync实时同步等关键技术环节。
文章目录
1. mysql的主从复制高可用性解决方案MHA
- 软件包
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
5.5.64-MariaDB - 实现一主两从的架构
- MHA监控一主两从，当主挂了，会自动切换到指定的从节点上
1.1 MHA配置
主机ip：192.168.43.129/24
gateway：192.168.43.139
安装包
yum --enablerepo=aliyun_epel install mariadb-server mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm -y
基于key的证
ssh-keygen
ssh-copy-id 127.0.0.1
scp -r ~/.ssh/ 192.168.43.99:/root/
scp -r ~/.ssh/ 192.168.43.109:/root/
scp -r ~/.ssh/ 192.168.43.119:/root/
配置文件
mkdir /etc/mha/
vim /etc/mha/app1.cnf
[server default]
user=mha
password=mha
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=replication
repl_password=replication
ping_interval=1
[server1]
hostname=192.168.43.119
candidate_master=1
[server2]
hostname=192.168.43.109
[server3]
hostname=192.168.43.99
candidate_master=1
检测
连接检测
masterha_check_ssh --conf=/etc/mha/app1.cnf
复制检测
masterha_check_repl --conf=/etc/mha/app1.cnf
启动MHA
masterha_manager --conf=/etc/mha/app1.cnf
1.2 mysql master 的配置
主机ip：192.168.43.119
gateway：192.168.43.139
安装包
yum install mha4mysql-node-0.56-0.el6.noarch.rpm mariadb-server -y
配置文件 /etc/my.conf
[mysqld]
log-bin
server_id=119
skip_name_resolv=1
systemctl enable --now mariadb
启动服务
systemctl enable --now mariadb
配置相关联的数据库用户
mysq
grant replication slave on *.* to replication@'192.168.43.%' identified by 'replication';
grant all on *.* to mha@'192.168.43.%' identified by 'mha';
grant replication client on *.* to monitor@'192.168.43.%' identified by 'monitor';
grant all on *.* to sqluser@'192.168.43.%' identified by 'sql';
grant all on discuz.* to discuz@'192.168.43.%' identified by 'discuz';
create database discuz;
1.3 mysql slave的配置
主机ip： 192.168.43.109 192.168.43.99
gateway：192.168.43.139
安装包
yum install mha4mysql-node-0.56-0.el6.noarch.rpm mariadb-server -y
配置文件 /etc/my.cnf
[mysqld]
log-bin
server_id=99 // 另一台主机配置 109
read_only
skip_name_resolve=1
relay_log_purge=0
启动服务
systemctl enable --now mariadb
同步信息配置
mysql
CHANGE MASTER TO
MASTER_HOST='192.168.43.119',
MASTER_USER='replication',
MASTER_PASSWORD='replication',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
start slave;
show slave status\G
2. client 的配置
主机ip：192.168.1.8/24
gateway：192.168.1.9
ip、gateway、dns设置
nmcli connection modify ens33 ipv4.addresses 192.168.1.8/24 ipv4.gateway 192.168.1.9 ipv4.dns 192.168.2.200
nmcli connection reload
nmcli connection up ens33
3. route 的配置
ens33:： 192.168.1.9/24
ens37： 192.168.2.254/24
查看ip
开启路由转发功能
echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
sysctl -p
配置防火墙规则
iptables -t nat -A POSTROUTING -s 192.168.1.0/24 ! -d 192.168.1.0/24 -j SNAT --to-source 192.168.2.254
4. 主DNS 的配置
主机ip： 192.168.2.200/24
安装包
yum install bind -y
配置文件
vim /etc/named.conf
options {
listen-on port 53 { localhost; };
allow-query { any; };
}
vim /etc/named.rfc1912.zones
zone "xuepeng.com" IN {
type master;
file "xuepeng.com.zone";
};
区域数据库文件
cd /var/named/
cp -p named.localhost xuepeng.com.zone
vim xuepeng.com.zone
检测语法
named-checkconf
named-checkzone xuepeng.com xuepeng.com.zone
开启dns
systemctl enable --now named
验证dns是否成功
5. firewall 的配置
ip 设置
ens37：192.168.2.100/24
ens33：192.168.43.139/24
防火墙规则
iptables -t nat -A PREROUTING -d 192.168.2.100 -p tcp --dport 80 -j DNAT --to-destination 192.168.43.100
iptables-save > /data/iptables_rule
echo "iptables-restore < /data/iptables_rule" >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local
开启路由转发功能
echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
sysctl -p
6. keepalived + lvs 实现web集群高可用
6.1 keepalived + lvs_01
- ip: 192.168.43.19/24
- gateway： 192.168.43.139
- vip： 192.168.43.100
安装包
yum install keepalived ipvsadm -y
配置文件
cd /etc/keepalived/
cp keepalived.conf keepalived.conf.bak
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from root@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id keepalived01
#vrrp_skip_check_adv_addr
#vrrp_strict
#vrrp_garp_interval 0
#vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.43.100/24 dev ens33 label ens33:0
}
}
virtual_server 192.168.43.100 80 {
delay_loop 6
lb_algo rr
lb_kind DR
protocol TCP
real_server 192.168.43.39 80 {
weight 100
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 80
}
}
real_server 192.168.43.49 80 {
weight 100
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 80
}
}
}
启动服务查看进程
systemctl enable --now keepalived
ps aux|grep keepalived
6.2 keepalived + lvs_02
- ip: 192.168.43.29/24
- gateway： 192.168.43.139
- vip： 192.168.43.200
配置文件
相对 192.168.43.19修改如下：
router_id keepalived02
state BACKUP
priority 80
启动服务
systemctl enable --now keepalived
ps aux|grep keepalived
6.3 nginx + php-fpm_01 的配置
ip： 192.168.43.43.39/24
gateway： 192.168.43.139
安装包，启动服务，查看端口
yum --enablerepo=aliyun_epel install nginx -y
nginx
lsof -i:80
配置vip
cd /etc/sysconfig/network-scripts/
cp ifcfg-lo ifcfg-lo:0
vim ifcfg-lo:0
DEVICE=lo:0
IPADDR=192.168.43.100
NETMASK=255.255.255.255
NETWORK=192.168.43.0
BROADCAST=192.168.43.255
ONBOOT=yes
NAME=lo:0
systemctl restart network
设置arp让vip保持沉默
vim /etc/sysctl.conf
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
net.ipv4.conf.all.arp_announce = 2
sysctl -p
yum 安装新版本php-fpm
yum install https://mirror.tuna.tsinghua.edu.cn/remi/enterprise/remi-release-7.rpm --enablerepo=aliyun_epel -y
yum install php73-php-fpm php73-php-mysql --enablerepo=remi-php73 --enablerepo=aliyun_epel -y
php-fpm相关配置优化
grep "^[a-z]" /etc/opt/remi/php73/php-fpm.conf
include=/etc/opt/remi/php73/php-fpm.d/*.conf
pid = /var/opt/remi/php73/run/php-fpm/php-fpm.pid
error_log = /var/opt/remi/php73/log/php-fpm/error.log
daemonize = yes
vim /etc/opt/remi/php73/php-fpm.d/www.conf
listen = 127.0.0.1:9000 //监听地址及ip
listen.allowed_clients = 127.0.0.1 //允许客户端从哪个源ip地址访问
user = nginx //启动的用户和组
group = nginx
pm = dynamic //动态模式进程管理
pm.max_children = 500 //静态方式下开启的php-fpm进程数量，在动态模式下这个值限定php-fpm的最大进程数
pm.start_servers = 100 //动态模式下的初始进程数，必须大于等于pm.min_spare_servers，小于等于 pm.man_spare_servers
pm.min_spare_servers = 100 //最小空闲进程数
pm.man_spare_servers = 200 // 最大空闲进程数
pm.max_requests = 500000 //进程累计请求回收值，会重启
pm.status_path = /pm_status //状态访问url
ping.path = /ping // 访问动地址
ping.response = pong //ping 返回值
slowlog = /var/opt/remi/php73/log/php-fpm/www-slow.log //慢日志路径
php_admin_value[error_log] = /var/opt/remi/php73/log/php-fpm/www-error.log //错误日志
php_admin_flag[log_errors] = on
php_value[session.save_handler] = files //php session 保存方式方式及路径
php_value[session.save_path] = /var/opt/remi/php73/lib/php/session //当时使用file方式保存session的文件路径
启动 php-fpm
systemctl enable --now php73-php-fpm
设置nginx转发
vim /etc/nginx/conf.d/blogs.xuepeng.com.conf
server {
listen 80;
server_name blogs.xuepeng.com;
index index.php index.html;
location ~* \.php$ {
root /data/blogs;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
#fastcgi_param SCRIPT_FILENAME /data/blogs$fastcgi_script_name;
include fastcgi_params;
}
}
mkdir /data/blogs
nginx -s reload
在dns服务器中添加记录
rndc reload
6.4 nginx + php-fpm_02 的配置
ip： 192.168.43.43.49/24
gateway： 192.168.43.139
配置同 192.168.43.39 一样，不过多重复
7. 测试后端的网站能否访问
- 测试静态页面
[192.168.43.39]#echo 192.168.43.39 nginx01 > /usr/share/nginx/html/index.html
[192.168.43.49]#echo 192.168.43.49 nginx02 > /usr/share/nginx/html/index.html
配置虚拟主机
[192.168.43.39]#
[192.168.43.49]#
vim /etc/nginx/conf.d/discuz.xuepeng.com.conf
server {
listen 80;
server_name discuz.xuepeng.com;
location / {
root /usr/share/nginx/html;
}
}
2. 测试动态页面
[192.168.43.39]#vim /data/blogs/index.php
<h1>blogs.xuepeng.com on 192.168.43.39</h1>
<?php
phpinfo();
?>
[192.168.43.39]#vim /data/blogs/index.php
<h1>blogs.xuepeng.com on 192.168.43.49</h1>
<?php
phpinfo();
?>
指定windows中的hosts文件
192.168.43.100 blogs.xuepeng.com
8. keepalived + lvs 实现 proxysql的高可用
8.1 配置 keepalived + lvs
在 192.168.43.19:/etc/keepalived/keepalived.conf 文件中追加以下代码
vrrp_instance VI_2 {
state BACKUP
interface ens33
virtual_router_id 61
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 2222
}
virtual_ipaddress {
192.168.43.200/24 dev ens33 label ens33:1
}
}
virtual_server 192.168.43.200 6033 {
delay_loop 6
lb_algo rr
lb_kind DR
protocol TCP
real_server 192.168.43.59 6033 {
weight 100
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 6033
}
}
real_server 192.168.43.69 6033 {
weight 100
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 6033
}
}
}
重启，查看ipvsadm规则
killall keepalived
ps aux|grep keepalived
systemctl start keepalived
ps aux|grep keepalived
ipvsadm -Ln
在 192.168.43.29:/etc/keepalived/keepalived.conf 文件中追加以下代码
- 复制上面的代码，作以下修改
- state MASTER
- priority 100
查看keepalived双主有没有成功
关掉一台的keepalived服务，查看
8.2 配置 proxysql_01
ip：192.168.43.59/24
gateway： 192.168.43.139
vip： 192.168.43.200
配置vip
vim /etc/sysconfig/network-scripts/ifcfg-lo:0
DEVICE=lo:0
IPADDR=192.168.43.200
NETMASK=255.255.255.255
NETWORK=192.168.43.0
BROADCAST=192.168.43.255
ONBOOT=yes
NAME=lo:0
设置arp让vip保持沉默
vim /etc/sysctl.conf
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
net.ipv4.conf.all.arp_announce = 2
sysctl -p
准备包
proxysql-1.4.16-1-centos7.x86_64.rpm
安装
yum install *.rpm mariadb -y
启动服务，进入管理接口，向ProxySQL中添加MySQL节点
service proxysql start
chkconfig proxysql on
mysql -uadmin -padmin -P6032 -h127.0.0.1
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.43.99',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.43.119',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.43.109',3306);
load mysql servers to runtime;
save mysql servers to disk;
ProxySQL上配置监控用户
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;
分组信息
insert into mysql_replication_hostgroups values(10,20,"test");
load mysql servers to runtime;
save mysql servers to disk;
查看分组信息
配置发送SQL语句的用户
insert into mysql_users(username,password,default_hostgroup)values('sqluser','sql',10);
load mysql users to runtime;
save mysql users to disk;
创建路由规则
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
授权一个数据库用户用于网站数据写入数据库时用
insert into mysql_users(username,password,default_hostgroup) values('discuz','discuz',10);
load mysql users to runtime;
save mysql users to disk;
8.3 配置 proxysql_02
配置同 proxysql_01 ，不作重复
9. 配置 NFS服务器
安装配置nfs
yum install -y nfs-utils
mkdir /data/discuz
vim /etc/exports
/data/discuz 192.168.43.0/24(rw,all_squash,anonuid=998,anongid=996)
exportfs -arv
systemctl enable --now nfs-server
创建用户和组
groupadd -g 996 nginx
useradd -r -u 998 -g 996 -s /sbin/nologin nginx
准备软件包 Discuz_X3.3_SC_UTF8.zip
解压授权
unzip Discuz_X3.3_SC_UTF8.zip
mv upload/* /data/discuz/
chown -R nginx.nginx /data/discuz/
10. 部署 discuz
[192.168.43.39]#
[192.168.43.49]#
安装
yum install nfs-utils -y
查看共享
挂载
mkdir /data/discuz
mount 192.168.43.79:/data/discuz /data/discuz
配置虚拟主机
vim /etc/nginx/conf.d/discuz.xuepeng.com.conf
server {
listen 80;
server_name discuz.xuepeng.com;
index index.php;
location / {
root /data/discuz;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
#fastcgi_param SCRIPT_FILENAME /data/blogs$fastcgi_script_name;
include fastcgi_params;
}
}
nginx -t
nginx -s reload
绑定hosts文件，浏览器访问
C:\Windows\System32\drivers\etc\hosts
192.168.43.100 discuz.xuepeng.com
http://discuz.xuepeng.com/install/index.php
安装 php73-php-xml ，重启 php-fpm 解决以上问题
yum install php73-php-xml -y
systemctl restart php73-php-fpm.service
11. 配置 rsync + inotify 实现时时同步
rsync 服务器的配置
ip： 192.168.43.89/24
安装
yum install rsync -y
配置文件
vim /etc/rsyncd.conf
uid = root
gid = root
use chroot = no
max connections = 0
ignore errors
exclude = lost+found/
log file = /var/log/rsyncd.log
pid file = /var/run/rsyncd.pid
lock file = /var/run/rsyncd.lock
reverse lookup = no
hosts allow = 192.168.43.0/24
[backup]
path = /backup/
comment = backup
read only = no
auth users = rsyncuser
secrets file = /etc/rsync.pass
验证文件
echo "rsyncuser:123456" > /etc/rsync.pass
chmod 600 /etc/rsync.pass
创建备份目录
mkdir /backup
启动服务
systemctl start rsyncd
systemctl enable rsyncd
rsync 客户端的配置
ip ： 192.168.43.79/24
安装
yum install rsync -y
配置密码文件
echo "123456" > /etc/rsync.pass
chmod 600 /etc/rsync.pass
安装监控软件
yum --enablerepo=aliyun_epel install inotify-tools -y
监控脚本
#!/bin/bash
SRC='/data/'
DEST='rsyncuser@192.168.43.89::backup'
PWDFILE=/etc/rsync.pass
inotifywait -mrq --timefmt '%Y-%m-%d %H:%M' --format '%T %w %f' -e create,delete,moved_to,close_write,attrib ${SRC} |while read DATE TIME DIR FILE;do
FILEPATH=${DIR}${FILE}
rsync -az --delete --password-file=$PWDFILE $SRC $DEST && echo "At ${TIME} on ${DATE}, file $FILEPATH was backuped up via rsync" >> /var/log/changelist.log
done
chmod +x monitor.sh
后台执行
./monitor.sh &
12. 测试
-
用户能够在 client 主机上访问到 discuz.xuepeng.com 这个网站
-
测试后端服务器的存活性
关闭后端一台nginx服务器
在调度器上查看规则
客户端访问，查看日志
- 测试 lvs的高可用性
关闭一台 keepalived的服务
扫一扫
514
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
