Mysql双主+keepalived（亲测有效）

版本：

CentOS Linux release 7.5.1804 (Core) 

keepalived-1.3.5.tar.gz  

mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

节点1 IP：192.168.52.187

节点2 IP:   192.168.52.188

VIP:192.168.52.189

初始化

#关闭防火墙
sed -i '/SELINUX/s/enforcing/disabled/' /etc/selinux/config & setenforce 0&& systemctl disable firewalld.service && systemctl stop firewalld.service
#安装常用工具
yum install gcc gcc-c++ vim-enhanced glibc make unzip openssl openssl-devel openssh-server openssh-clients wget -y

安装mysql可以参考https://blog.csdn.net/weixin_43885834/article/details/104412099

配置mysql主主

节点1

#更改数据库配置
[root@localhost mysql]# cat /etc/my.cnf
[mysql]
default-character-set=utf8
[mysqld]
skip-name-resolve
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1 #不区分大小写
max_allowed_packet=100M
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps=SYSTEM 
log-error=/var/log/mysqld.log

server-id = 1         
log-bin = mysql-bin     
#sync_binlog = 1
#binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 1 

重启mysql  不要使用restart
[root@localhost local]# systemctl stop mysqld
[root@localhost local]# systemctl start mysqld
登陆mysql 创建同步用户
[root@localhost mysql]# mysql  -uroot -p
mysql> grant replication slave,replication client on *.* to repl@'192.168.52.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)
#锁表，查看binlog日志节点 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下 mysql-bin.000001 和154 

节点2

更改数据库配置
[root@localhost ~]# cat /etc/my.cnf
[mysql]
default-character-set=utf8
[mysqld]
skip-name-resolve
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# # 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1 #不区分大小写
max_allowed_packet=100M
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps=SYSTEM 
log-error=/var/log/mysqld.log

server-id = 2        
log-bin = mysql-bin    
#sync_binlog = 1
#binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 2    
slave-skip-errors = all

重启mysql  不要使用restart
[root@localhost local]# systemctl stop mysqld
[root@localhost local]# systemctl start mysqld
登陆mysql 创建同步用户
[root@localhost mysql]# mysql  -uroot -p
mysql> grant replication slave,replication client on *.* to repl@'192.168.52.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

#锁表，查看binlog日志节点 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      622 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下 mysql-bin.000001 和622

分别开启同步，先开节点1

#解锁
mysql> unlock tables;  
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change  master to master_host='192.168.52.188',master_user='repl',master_password='123.com',master_log_file='mysql-bin.000001',master_log_pos=622;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#查看主从状态 如上图两个Yes表示主从正常
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql>  show slave status G;

节点2

#解锁
mysql> unlock tables;  
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change  master to master_host='192.168.52.187',master_user='repl',master_password='123.com',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#查看主从状态  两个Yes表示主从正常
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql>  show slave status G;

方已经实现了mysql主主同步，如果不放心，可以去两个数据库里创建表来验证一下是否相互同步，过程略。

配置mysql+keepalived 高可用环境

安装keepalived并将其配置成系统服务，两台机器上同样进行如下操作：

[root@localhost ~]# cd /usr/local/src/
[root@localhost src]# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
[root@localhost src]# tar -zvxf keepalived-1.3.5.tar.gz
[root@localhost src]# cd keepalived-1.3.5
[root@localhost keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived
[root@localhost keepalived-1.3.5]# make && make install
     
[root@localhost keepalived-1.3.5]# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
[root@localhost keepalived-1.3.5]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@localhost keepalived-1.3.5]# mkdir /etc/keepalived/
[root@localhost keepalived-1.3.5]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@localhost keepalived-1.3.5]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@localhost keepalived-1.3.5]# echo "/etc/init.d/keepalived start" >> /etc/rc.local

更改PIDFile地址，不然启动会报错
[root@localhost keepalived-1.3.5]#vi /lib/systemd/system/keepalived.service
[Unit]
Description=LVS and VRRP High Availability Monitor
After=syslog.target network-online.target

[Service]
Type=forking
PIDFile=/var/run/keepalived.pid
KillMode=process
EnvironmentFile=-/usr/local/keepalived/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

节点1 keepalived.conf配置

[root@localhost ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@localhost local]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
       
global_defs {
notification_email {
csdn@126.com   #邮箱随便写，这里没配置发送邮件
}
       
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 "/opt/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        
    state BACKUP     
    nopreempt    #非抢占模式
    interface ens33      #指定虚拟ip的网卡接口
    mcast_src_ip 192.168.52.187 
    virtual_router_id 51    #路由器标识，MASTER和BACKUP必须是一致的
    priority 101            #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级 
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        192.168.52.189
    }
      
track_script {               
   chk_mysql_port             
}
}

#编写心跳检测脚本
[root@master1 ~]# vim /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi

[root@localhost ~]# chmod 755 /opt/chk_mysql.sh
     
启动keepalived服务
[root@localhost ~]# /etc/init.d/keepalived start
正在启动 keepalived：                                      [确定]

#备注：如果启动不成功，请查看日志（ tail -20f /var/log/message），如果完全按我上面的操作肯定可以启动成功，启动不成功后需要先kill -9杀点原来的keepalived进程，使用/etc/init.d/keepalived stop命令无效，进程依然存在，然后再启动/etc/init.d/keepalived start  

节点2 keepalived.conf配置

[root@localhost ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@localhost ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
       
global_defs {
notification_email {
csdn@126.com
}
       
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 "/opt/chk_mysql.sh"
    interval 2            
    weight -5                 
    fall 2                 
    rise 1               
}
       
vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface ens33
    mcast_src_ip 192.168.52.188
    virtual_router_id 51    
    priority 99          
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        192.168.52.189
    }
      
track_script {               
   chk_mysql_port             
}
}

#编写心跳检测脚本
[root@master1 ~]# vim /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi

[root@localhost ~]# chmod 755 /opt/chk_mysql.sh
     
启动keepalived服务
[root@localhost ~]# /etc/init.d/keepalived start
正在启动 keepalived：                                      [确定]

上面已经部署完成，下面是测试环节

1.测试vip的mysql是否可以连接

2.关闭vip机器所在的mysql服务，观察vip是否转移

3.重新开启mysql和keepalived 需要先开启mysql,keepalived才能顺利启动

---------------------------------温馨提示（Keepalived的抢占和非抢占模式）---------------------------------------

keepalive是基于vrrp协议在linux主机上以守护进程方式，根据配置文件实现健康检查。

VRRP是一种选择协议，它可以把一个虚拟路由器的责任动态分配到局域网上的VRRP路由器中的一台。

控制虚拟路由器IP地址的VRRP路由器称为主路由器，它负责转发数据包到这些虚拟IP地址。

一旦主路由器不可用，这种选择过程就提供了动态的故障转移机制，这就允许虚拟路由器的IP地址可以作为终端主机的默认第一跳路由器。

keepalive通过组播，单播等方式（自定义），实现keepalive主备推选。工作模式分为抢占和非抢占（通过参数nopreempt来控制）。

1）抢占模式：

主服务正常工作时，虚拟IP会在主上，备不提供服务，当主服务优先级低于备的时候，备会自动抢占虚拟IP，这时，主不提供服务，备提供服务。

也就是说，工作在抢占模式下，不分主备，只管优先级。

如上配置，不管keepalived.conf里的state配置成master还是backup，只看谁的priority优先级高（一般而言，state为MASTER的优先级要高于BACKUP）。

priority优先级高的那一个在故障恢复后，会自动将VIP资源再次抢占回来！！

2）非抢占模式：

这种方式通过参数nopreempt（一般设置在advert_int的那一行下面）来控制。不管priority优先级，只要MASTER机器发生故障，VIP资源就会被切换到BACKUP上。

并且当MASTER机器恢复后，也不会去将VIP资源抢占回来，直至BACKUP机器发生故障时，才能自动切换回来。

千万注意：

nopreempt这个参数只能用于state为backup的情况，所以在配置的时候要把master和backup的state都设置成backup，这样才会实现keepalived的非抢占模式！

也就是说：

a）当state状态一个为master，一个为backup的时候，加不加nopreempt这个参数都是一样的效果。即都是根据priority优先级来决定谁抢占vip资源的，是抢占模式！

b）当state状态都设置成backup，如果不配置nopreempt参数，那么也是看priority优先级决定谁抢占vip资源，即也是抢占模式。

c）当state状态都设置成backup，如果配置nopreempt参数，那么就不会去考虑priority优先级了，是非抢占模式！即只有vip当前所在机器发生故障，另一台机器才能接管vip。即使优先级高的那一台机器恢复  后也不会主动抢回vip，只能等到对方发生故障，才会将vip切回来。

最后

以上就是大意汉堡为你收集整理的Mysql双主+keepalived（亲测有效）的全部内容，希望文章能够帮你解决Mysql双主+keepalived（亲测有效）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。