mysql启用keepalive_mysql主从之keepalive+MySQL高可用

一 keepalive介绍

1.1 keepalived 是什么

keepalived 是集群管理中保证集群高可用的一个服务软件，用来防止单点故障。

1.2 keepalived 工作原理

keepalived 是以VRRP 协议为实现基础的，VRRP 全称VirtualRouter Redundancy Protocol，即虚拟路由冗余协议。虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N 台提供相同功能的路由器组成一个路由器组，这个组里面有一个master 和多个backup，master 上面有一个对外提供服务的vip(该路由器所在局域网内其他机器的默认路由为该vip)，master 会发组播，当backup 收不到vrrp 包时就认为master 宕掉了，这时就需要根据VRRP 的优先级来选举一个backup 当master。这样的话就可以保证路由器的高可用了。

keepalived 主要有三个模块，分别是core、check 和vrrp。

core 模块为keepalived 的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。

check 负责健康检查，包括常见的各种检查方式。

vrrp 模块是来实现VRRP 协议的。

1.3 实验环境

master1:192.168.132.121

master2:192.168.132.122

虚拟IP:192.168.132.120

双主环境

master1

master1

mysql>show slave status\G;

*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.132.122Master_User: replication

Master_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000003Read_Master_Log_Pos: 4975Relay_Log_File: relay-log.000003Relay_Log_Pos: 913Relay_Master_Log_File: master-bin.000003Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0Last_Error:

Skip_Counter: 0Exec_Master_Log_Pos: 4975Relay_Log_Space: 2618Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0Last_IO_Error:

Last_SQL_Errno: 0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2Master_UUID: 662134df-a196-11e9-b432-000c2963fd11

Master_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates

Master_Retry_Count: 86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 662134df-a196-11e9-b432-000c2963fd11:1-7Executed_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18,

662134df-a196-11e9-b432-000c2963fd11:1-7Auto_Position: 1Replicate_Rewrite_DB:

Channel_Name:

master2

mysql>show slave status\G;

*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.132.121Master_User: replication

Master_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000008Read_Master_Log_Pos: 650Relay_Log_File: relay-log.000008Relay_Log_Pos: 865Relay_Master_Log_File: master-bin.000008Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Rep