阅读量:0
MYSQL双主双从，使用Keepalived双机热备+LVS高可用群集
文档只记录Keepalived+LVS+mysql主从，不包含检验，如需检验，请自行添加web服务器
一、IP规划
| 服务器 | IP | 备注 |
|---|---|---|
| master1 | 192.168.100.131 | master2的从 |
| master2 | 192.168.100.132 | master1的从 |
| slave1 | 192.168.100.134 | master1的从 |
| slave2 | 192.168.100.135 | master2的从 |
| KI1 | 192.168.100.137 | 主 |
| KI2 | 192.168.100.138 | 从 |
| 虚拟ip | 192.168.100.200 | 热备ip |
二、具体配置
1.master1
配置ip：192.168.100.131
关闭防护墙，selinux
# 关闭防火墙 systemctl stop firewalld # 关闭selinux setenforce 0 下载mariadb
yum -y install mariadb mariadb-server 编辑mariadb配置文件
vi /etc/my.cnf 添加以下内容
# 标识（0-65535范围） server-id=1 # 日志文件名称前缀 log-bin=mysql-bin # 排除不复制的库 binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys # 配置中继日志名称 relay-log=mysql-relay-bin # 配置排除的库 replicate-ignore-db=mysql replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=sys # 编码格 init-connect='SET NAMES UTF8' skip-character-set-client-handshake character-set-server=utf8 # 开启联等备份 log_slave_updates=1 修改完成重启服务
# 重启服务 systemctl restart mariadb # 配置mysql密码，123456 mysql_secure_installation 授权
# 进入mysql mysql -uroot -p123456 # 创建用户 create user "backup"@"%" identified by '123456'; # 授权 grant replication slave on *.* to "backup"@"%" identified by "123456"; grant all privileges on *.* to 'root'@'%' identified by '123456'; # 刷新权限 flush privileges; # 查询Log_file，log_pos show master status; +------------------+----------+--------------+-------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+-------------------------------------------------+ | mysql-bin.000003 | 1947 | | mysql,information_schema,performance_schema,sys | +------------------+----------+--------------+-------------------------------------------------+ 1 row in set (0.00 sec) # 从库连接主库，MASTER_LOG_FILE与MASTER_LOG_POS的值由主库信息获得 change master to MASTER_HOST='192.168.100.132', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1947; # 开启复制 start slave; # 查看slave状态，Slave_IO_Running，Slave_SQL_Running的状态要为Yes show slave status \G; 虚拟网卡
# 添加虚拟网卡（回环地址） cat >> /etc/sysconfig/network-scripts/ifcfg-lo:0 << EOF DEVICE=lo:0 IPADDR=192.168.100.200 NETMASK=255.255.255.255 ONBOOT=yes EOF # 重启网络 systemctl restart network # 查看是否有虚拟ip ip a 写入路由指向
# 下载网络工具 yum -y install net-tools # 添加路由信息 route add -host 192.168.100.200 dev lo:0 调整/proc内核参数，关闭ARP响应
cat >> /etc/sysctl.conf << EOF net.ipv4.conf.all.arp_ignore = 1 net.ipv4.conf.all.arp_announce = 2 net.ipv4.conf.default.arp_ignore = 1 net.ipv4.conf.default.arp_announce = 2 net.ipv4.conf.lo.arp_ignore = 1 net.ipv4.conf.lo.arp_announce = 2 EOF sysctl -p 2.master2
配置ip：192.168.100.132
关闭防护墙，selinux
# 关闭防火墙 systemctl stop firewalld # 关闭selinux setenforce 0 下载mariadb
yum -y install mariadb mariadb-server 编辑mariadb配置文件
vi /etc/my.cnf 添加以下内容
# 标识（0-65535范围） server-id=2 # 日志文件名称前缀 log-bin=mysql-bin # 排除不复制的库 binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys # 配置中继日志名称 relay-log=mysql-relay-bin # 配置排除的库 replicate-ignore-db=mysql replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=sys # 编码格 init-connect='SET NAMES UTF8' skip-character-set-client-handshake character-set-server=utf8 # 开启联等备份 log_slave_updates=1 修改完成重启服务
# 重启服务 systemctl restart mariadb # 配置mysql密码，123456 mysql_secure_installation 授权
# 进入mysql mysql -uroot -p123456 # 创建用户 create user "backup"@"%" identified by '123456'; # 授权 grant replication slave on *.* to "backup"@"%" identified by "123456"; grant all privileges on *.* to 'root'@'%' identified by '123456'; # 刷新权限 flush privileges; # 查询Log_file，log_pos show master status; +------------------+----------+--------------+-------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+-------------------------------------------------+ | mysql-bin.000003 | 1947 | | mysql,information_schema,performance_schema,sys | +------------------+----------+--------------+-------------------------------------------------+ 1 row in set (0.00 sec) # 从库连接主库，MASTER_LOG_FILE与MASTER_LOG_POS的值由主库信息获得 change master to MASTER_HOST='192.168.100.131', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1947; # 开启复制 start slave; # 查看slave状态，Slave_IO_Running，Slave_SQL_Running的状态要为Yes show slave status \G; 虚拟网卡
# 添加虚拟网卡（回环地址） cat >> /etc/sysconfig/network-scripts/ifcfg-lo:0 << EOF DEVICE=lo:0 IPADDR=192.168.100.200 NETMASK=255.255.255.255 ONBOOT=yes EOF # 重启网络 systemctl restart network # 查看是否有虚拟ip ip a 写入路由指向
# 下载网络工具 yum -y install net-tools # 添加路由信息 route add -host 192.168.100.200 dev lo:0 调整/proc内核参数，关闭ARP响应
cat >> /etc/sysctl.conf << EOF net.ipv4.conf.all.arp_ignore = 1 net.ipv4.conf.all.arp_announce = 2 net.ipv4.conf.default.arp_ignore = 1 net.ipv4.conf.default.arp_announce = 2 net.ipv4.conf.lo.arp_ignore = 1 net.ipv4.conf.lo.arp_announce = 2 EOF sysctl -p 3.slave1
配置ip：192.168.100.134
关闭防护墙，selinux
# 关闭防火墙 systemctl stop firewalld # 关闭selinux setenforce 0 下载mariadb
yum -y install mariadb mariadb-server 编辑mariadb配置文件
vi /etc/my.cnf 添加以下内容
# 标识（0-65535范围） server-id=3 # 配置中继日志名称 relay-log=mysql-relay-bin # 配置排除的库 replicate-ignore-db=mysql replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=sys # 编码格式 init-connect='SET NAMES UTF8' skip-character-set-client-handshake character-set-server=utf8 修改完成重启服务
# 重启服务 systemctl restart mariadb # 配置mysql密码，123456 mysql_secure_installation 授权
# 连接数据库 mysql -uroot -p123456 # 授权 grant all privileges on *.* to "root"@"%" identified by "123456"; # 刷新权限 flush privileges; # 从库连接主库，MASTER_LOG_FILE与MASTER_LOG_POS的值由主库信息获得 change master to MASTER_HOST='192.168.100.131', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1947; # 开启复制 start slave; # 查看slave状态，Slave_IO_Running，Slave_SQL_Running的状态要为Yes show slave status \G; 4.slave2
配置ip：192.168.100.135
关闭防护墙，selinux
# 关闭防火墙 systemctl stop firewalld # 关闭selinux setenforce 0 下载mariadb
yum -y install mariadb mariadb-server 编辑mariadb配置文件
vi /etc/my.cnf 添加以下内容
# 标识（0-65535范围） server-id=4 # 配置中继日志名称 relay-log=mysql-relay-bin # 配置排除的库 replicate-ignore-db=mysql replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=sys # 编码格 init-connect='SET NAMES UTF8' skip-character-set-client-handshake character-set-server=utf8 修改完成重启服务
# 重启服务 systemctl restart mariadb # 配置mysql密码，123456 mysql_secure_installation 授权
# 连接数据库 mysql -uroot -p123456 # 授权 grant all privileges on *.* to "root"@"%" identified by "123456"; # 刷新权限 flush privileges; # 从库连接主库，MASTER_LOG_FILE与MASTER_LOG_POS的值由主库信息获得 change master to MASTER_HOST='192.168.100.132', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1947; # 开启复制 start slave; # 查看slave状态，Slave_IO_Running，Slave_SQL_Running的状态要为Yes show slave status \G; 5.KI1
配置ip：192.168.100.137
关闭防护墙，selinux
# 关闭防火墙 systemctl stop firewalld # 关闭selinux setenforce 0 下载keepalived和ipvsadm
yum -y install keepalived ipvsadm 备份默认配置文件
cp -p /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak 删除原有文件，创建新内容
# 删除原有文件 rm -rf /etc/keepalived/keepalived.conf # 创建新文件 vi /etc/keepalived/keepalived.conf 添加以下内容
! Configuration File for keepalived # 配置双机热备（主） lobal_defs { router_id 1 } vrrp_instance VI_1 { state MASTER interface ens33 virtual_router_id 1 priority 120 advert_int 1 authentication { auth_type PASS auth_pass 123456 } virtual_ipaddress { 192.168.100.200 } } # 虚拟服务器ip及mysql地址 virtual_server 192.168.100.200 3306 { delay_loop 15 lb_algo rr lb_kind DR protocol TCP real_server 192.168.100.131 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 4 } } real_server 192.168.100.132 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 4 } } } 启动keepalived
systemctl start keepalived 服务模块
# 加载系统内核的服务模块 modprobe ip_vs # 查看运行状态 lsmod | grep ip_vs 下载net网络工具
yum -y install net-tools 查看网卡信息
# 停用物理网卡 ifconfig ens33 down # 启动物理网卡（如在连接工具上，需到虚拟机上启动） ifconfig ens33 up # 查看网卡信息（在ens33网卡下是否有ip：192.168.100.200） ip a LVS-DR群集策略
# 查看策略 ipvsadm -ln 6.KI2
配置ip：192.168.100.138
关闭防护墙，selinux
# 关闭防火墙 systemctl stop firewalld # 关闭selinux setenforce 0 下载keepalived和ipvsadm
yum -y install keepalived ipvsadm 备份默认配置文件
cp -p /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak 添加以下内容
! Configuration File for keepalived # 配置双机热备（备） lobal_defs { router_id 2 } vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id 1 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 123456 } virtual_ipaddress { 192.168.100.200 } } # 虚拟服务器ip及mysql地址 virtual_server 192.168.100.200 3306 { delay_loop 15 lb_algo rr lb_kind DR protocol TCP real_server 192.168.100.131 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 4 } } real_server 192.168.100.132 3306 { weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 3 delay_before_retry 4 } } } 启动keepalived
systemctl start keepalived 服务模块
# 加载系统内核的服务模块 modprobe ip_vs # 查看运行状态 lsmod | grep ip_vs 下载net网络工具
yum -y install net-tools 查看网卡信息
# 停用物理网卡 ifconfig ens33 down # 启动物理网卡（如在连接工具上，需到虚拟机上启动） ifconfig ens33 up # 查看网卡信息（在ens33网卡下是否有ip：192.168.100.200） ip a LVS-DR群集策略
# 查看策略 ipvsadm -ln 三、检验
可自行添加一台web服务器，连接虚拟ip，开启KI1或KI2的防火墙进行检测
或者，开启KI1的防火墙，查看ip是否跳转到KI2上
