背景
MySQL 的高可用方案一般有如下几种:
keepalived + 双主, MHA,MMM,Heartbeat+DRBD,PXC,Galera Cluster
比较常用的是 keepalived + 双主, MHA 和 PXC.
对于小公司, 一般推荐使用 keepalived + 双主, 便于维护.
环境
MySQL 双主配置
1. 修改配置文件
master1 中有关复制的配置如下:
- [mysqld]
- log-bin=MySQL-bin
- server-id=1
- log_slave_updates=1
master2 中有关复制的配置如下:
- [mysqld]
- log-bin=MySQL-bin
- server-id=2
- log_slave_updates=1
- read_only=1
改完之后把两个数据库都重启了
2. 创建复制用户
master1 中创建:
- CREATE USER 'repl'@'10.1.80.114' IDENTIFIED BY 'Mysql@2019';
- GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.80.114';
master2 中创建:
- CREATE USER 'repl'@'10.1.80.113' IDENTIFIED BY 'Mysql@2019';
- GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.80.113';
3. 执行 CHANGE MASTER TO 语句
因是从头搭建 MySQL 主从复制集群, 所以不需要获取全局读锁来得到二进制日志文件的位置, 直接根据 show master status 的输出来确认.
master1 上执行:
- CHANGE MASTER TO
- MASTER_HOST='10.1.80.114',
- MASTER_USER='repl',
- MASTER_PASSWORD='Mysql@2019',
- MASTER_LOG_FILE='mysql-bin.000001',
- MASTER_LOG_POS=154;
master2 上执行:
- CHANGE MASTER TO
- MASTER_HOST='10.1.80.113',
- MASTER_USER='repl',
- MASTER_PASSWORD='Mysql@2019',
- MASTER_LOG_FILE='mysql-bin.000001',
- MASTER_LOG_POS=154;
4. 分别在两个节点上执行 start slave; 语句
并通过 show slave status\G 查看复制是否搭建成功.
出现如下内容说明成功.
5, 数据同步测试
在两侧插入数据发现正常同步.
keepalived 配置
1, 安装依赖包
- yum install gcc
- yum install openssl*
2, 下载软件, 解压编译
- # 下载
- wget http://www.keepalived.org/software/keepalived-2.0.10.tar.gz
- # 解压
- tar -zxvf keepalived-2.0.10.tar.gz
- # 编译
- ./configure --prefix=/keepalived
3, 初始化以及启动
- # keepalived 启动脚本变量引用文件, 默认文件路径是 / etc/sysconfig/, 也可以不做软链接, 直接修改启动脚本中文件路径即可(安装目录下)
- [root@localhost /]# cp /keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
- # 将 keepalived 主程序加入到环境变量(安装目录下)
- [root@localhost /]# cp /keepalived/sbin/keepalived /usr/sbin/keepalived
- # keepalived 启动脚本(源码目录下), 放到 / etc/init.d / 目录下就可以使用 service 命令便捷调用
- [root@localhost /]# cp /tmp/keepalived-2.0.10/keepalived/etc/init.d/keepalived /etc/init.d/keepalived
- # 将配置文件放到默认路径下
- [root@localhost /]# mkdir /etc/keepalived
- [root@localhost /]# cp /keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
- # 加为系统服务:
- chkconfig --add keepalived
- # 开机启动:
- chkconfig keepalived on
- # 查看开机启动的服务:
- chkconfig --list
- # 启动, 关闭, 重启
- service keepalived start|stop|restart
4, 修改配置文件
- master1
- vi /etc/keepalived/keepalived.conf
- ! Configuration File for keepalived
- global_defs {
- notification_email {
- ops@wangshibo.cn
- tech@wangshibo.cn
- }
- notification_email_from ops@wangshibo.cn
- smtp_server 127.0.0.1
- smtp_connect_timeout 30
- router_id MASTER-HA
- }
- vrrp_script chk_mysql_port { #检测 MySQL 服务是否在运行. 有很多方式, 比如进程, 用脚本检测等等
- script "/keepalived/chk_mysql.sh" #这里通过脚本监测
- interval 2 #脚本执行间隔, 每 2s 检测一次
- weight -5 #脚本结果导致的优先级变更, 检测失败 (脚本返回非 0) 则优先级 -5
- fall 2 #检测连续 2 次失败才算确定是真失败. 会用 weight 减少优先级(1-255 之间)
- rise 1 #检测 1 次成功就算成功. 但不修改优先级
- }
- vrrp_instance VI_1 {
- state MASTER
- interface eth2 #指定虚拟 ip 的网卡接口
- mcast_src_ip 10.1.80.113
- virtual_router_id 51 #路由器标识, MASTER 和 BACKUP 必须是一致的
- priority 101 #数字越大, 优先级越高, 同一个 vrrp_instance 下, MASTER 的优先级必须大于 BACKUP 的优先级. 这样 MASTER 故障恢复后, 就可以将 VIP 资源再次抢回来
- advert_int 1
- authentication {
- auth_type PASS
- auth_pass 1111
- }
- virtual_ipaddress {
- 10.1.80.119
- }
- track_script {
- chk_mysql_port
- }
- }
编写切换脚本. KeepAlived 做心跳检测, 如果 Master 的 MySQL 服务挂了(3306 端口挂了), 那么它就会选择自杀.
Slave 的 KeepAlived 通过心跳检测发现这个情况, 就会将 VIP 的请求接管
- vi chk_mysql.sh
- #!/bin/bash
- counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
- if [ "${counter}" -eq 0 ]; then
- service keepalived stop
- fi
- chmod 755 chk_mysql.sh
启动 keepalived 服务.
- master2
- vi /etc/keepalived/keepalived.conf
- ! Configuration File for keepalived
- global_defs {
- notification_email {
- ops@wangshibo.cn
- tech@wangshibo.cn
- }
- notification_email_from ops@wangshibo.cn
- smtp_server 127.0.0.1
- smtp_connect_timeout 30
- router_id MASTER-HA
- }
- vrrp_script chk_mysql_port {
- script "/keepalived/chk_mysql.sh"
- interval 2
- weight -5
- fall 2
- rise 1
- }
- vrrp_instance VI_1 {
- state BACKUP
- interface eth2
- mcast_src_ip 10.1.80.114
- virtual_router_id 51
- priority 99
- advert_int 1
- authentication {
- auth_type PASS
- auth_pass 1111
- }
- virtual_ipaddress {
- 10.1.80.119
- }
- track_script {
- chk_mysql_port
- }
- }
- vi chk_mysql.sh
- #!/bin/bash
- counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
- if [ "${counter}" -eq 0 ]; then
- service keepalived stop
- fi
- chmod 755 chk_mysql.sh
查看网卡
来源: https://www.cnblogs.com/jinyuanliu/p/10523648.html