准备工作:
主机名 | 操作系统 | IP地址 | 担任角色 |
master1 | CentOS7 | 192.168.1.1 | MySQL-主 |
master2 | 192.168.1.2 | MySQL-主 | |
keep1 | 192.168.1.3 | Keepalived-主,LVS | |
keep2 | 192.168.1.4 | Keepalived-备,LVS | |
client | 192.168.1.5 | 测试客户机 |
一、 部署 MySQL 双主复制
- 注意:未部署 MySQL 数据库需看:CentOS7 安装 MySQL 数据库 来进行安装。
1.配置时间同步
master1 上操作
[root@master1 ~]# yum -y install ntp[root@master1 ~]# sed -i \'/^server/s/^/#/g\' /etc/ntp.conf [root@master1 ~]# cat <<END >> /etc/ntp.confserver 127.127.1.0fudge 127.127.1.0 stratum 8END[root@master1 ~]# systemctl restart ntpd #重启ntpd[root@master1 ~]# systemctl enable ntpd #设置开机自启[root@master1 ~]# netstat -anpu | grep ntp #查看状态
master 2上操作
[root@master2 ~]# yum -y install ntpdate[root@master2 ~]# /usr/sbin/ntpdate 192.168.1.1
2.配置双主复制
1)master1 上操作
[root@master1 ~]# cat <<END >> /etc/my.cnflog-bin=mysql-master1server-id=1log_slave_update=1auto_increment_offset=1auto_increment_increment=2END[root@master1 ~]# systemctl restart mysqld[root@master1 ~]# mysql -uroot -p123456mysql> grant replication slave on *.* to repl@\'192.168.1.%\' identified by \'123456\';mysql> flush privileges;mysql> exit
2)master2 上操作
[root@master2 ~]# cat <<END >> /etc/my.cnflog-bin=mysql-master2server-id=2log_slave_update=1auto_increment_offset=1auto_increment_increment=2END[root@master2 ~]# systemctl restart mysqld[root@master2 ~]# mysql -uroot -p123456mysql> grant replication slave on *.* to repl@\'192.168.1.%\' identified by \'123456\';mysql> flush privileges;mysql> exit
3)建立双主复制
master1 上操作
[root@master1 ~]# mysql -uroot -p123456mysql> change master tomaster_host=\'192.168.1.2\',master_user=\'repl\',master_password=\'123456\';mysql> start slave;mysql> show slave status\\G;
master2 上操作
[root@master2 ~]# mysql -uroot -p123456mysql> change master tomaster_host=\'192.168.1.1\',master_user=\'repl\',master_password=\'123456\';mysql> start slave;mysql> show slave status\\G;
二、部署 LVS+Keepalived 实现 MySQL 双主复制高可用
1.配置主调度器
[root@keep1 ~]# yum -y install keepalived ipvsadm[root@keep1 ~]# vim /etc/keepalived/keepalived.confglobal_defs { router_id 1}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.1.188 }}virtual_server 192.168.1.188 3306 { delay_loop 6 lb_algo rr lb_kind DR persistence_timeout 0 protocol TCP real_server 192.168.1.1 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 3 } } real_server 192.168.1.2 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 3 } }}[root@keep1 ~]# systemctl start keepalived[root@keep2 ~]# ip a
2.配置备调度器
[root@keep2 ~]# yum -y install keepalived ipvsadm[root@keep2 ~]# vim /etc/keepalived/keepalived.confglobal_defs { router_id 2}vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id 51 priority 99 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.188 }}virtual_server 192.168.1.188 3306 { delay_loop 6 lb_algo rr lb_kind DR persistence_timeout 0 protocol TCP real_server 192.168.1.1 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 3 } } real_server 192.168.1.2 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 3 } }}[root@keep2 ~]# systemctl start keepalived
3.查看 LVS 集群状态
[root@keep1 ~]# ipvsadm -ln
4.编写 LVS 启动脚本
在 master1 master2 上操作
[root@master1 ~]# vim /etc/init.d/realserver#!/bin/bashVIP=192.168.1.188. /etc/rc.d/init.d/functionscase \"$1\" instart) /sbin/ifconfig lo down /sbin/ifconfig lo up echo \"1\" >/proc/sys/net/ipv4/conf/lo/arp_ignore echo \"2\" >/proc/sys/net/ipv4/conf/lo/arp_announce echo \"1\" >/proc/sys/net/ipv4/conf/all/arp_ignore echo \"2\" >/proc/sys/net/ipv4/conf/all/arp_announce /sbin/sysctl -p >/dev/null 2>&1 /sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 up /sbin/route add -host $VIP dev lo:0 echo \"LVS-DR real server starts successfully.\\n\" ;;stop) /sbin/ifconfig lo:0 down /sbin/route del $VIP >/dev/null 2>&1 echo \"1\" >/proc/sys/net/ipv4/conf/lo/arp_ignore echo \"2\" >/proc/sys/net/ipv4/conf/lo/arp_announce echo \"1\" >/proc/sys/net/ipv4/conf/all/arp_ignore echo \"2\" >/proc/sys/net/ipv4/conf/all/arp_announceecho \"LVS-DR real server stopped.\\n\" ;;status) isLoOn=`/sbin/ifconfig lo:0 | grep \"$VIP\"` isRoOn=`/bin/netstat -rn | grep \"$VIP\"` if [ \"$isLoON\" == \"\" -a \"$isRoOn\" == \"\" ]; then echo \"LVS-DR real server has run yet.\" else echo \"LVS-DR real server is running.\" fi exit 3 ;;*) echo \"Usage: $0 {start|stop|status}\" exit 1esacexit 0[root@master1 ~]# chmod +x /etc/init.d/realserver #添加可执行权限[root@master1 ~]# /etc/init.d/realserver start #启动脚本[root@master1 ~]# echo \"/etc/init.d/realserver\" >> /etc/rc.d/rc.local #设置开启自启动[root@master1 ~]# ifconfig lo:0
三、验证
在任意一台 master 添加一个测试用户
[root@master1 ~]# mysql -uroot -p123456mysql> grant all on *.* to test@\'%\' identified by \'123123\';mysql> flush privileges;
1.验证 LVS 负载均衡是否正常
[root@client ~]# yum -y install mariadb[root@client ~]# mysql -utest -p123123 -h192.168.1.188 -e \"show variables like \'server_id\'\"[root@client ~]# mysql -utest -p123123 -h192.168.1.188 -e \"show variables like \'server_id\'\"
2.验证 Keepalived 双机热备是否正常
- 关闭 LVS 主调度器,查看客户机是否能够正常访问数据库。
3.验证 MySQL 数据库可用性
[root@master1 ~]# systemctl stop mysqld