keepalived+mysql 高可用

keepalived是什么

keepalived是一个高可用软件，起初是专为LVS负载均衡软件设计的，还可以通过VRRP实现高可用服务之间的故障切换转移，也是现在用的最多的功能。

keepalived+mysql

架构图如下

keepalived可以让我们通过VIP来访问mysql服务，当master服务挂掉时，秒级切换到slave服务上，保证服务可用性

mysql主主同步

主主同步，其实就是做两次主从，其实并不难

配置修改

修改配置文件/etc/my.cnf，添加如下配置

server-id=1
log-bin=mysql-bin
binlog_format=mixed
auto-increment-increment = 2   
auto-increment-offset = 1
log-slave-updates=on

binlog-ignore-db=mysql
binlog-ignore-db=test
replicate-ignore-db=mysql
replicate-ignore-db=test

以上为master配置，slave配置差不多，注意server-id是唯一的，需要改为2 auto-increment-offset为了防止主键冲突的，slave需要改为2

同步数据

如果master上已经有数据了，需要先导出数据库，然后导入到slave上

先锁表 flush table with read lock;

记录状态 show master status;

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 | 21144745 | zabbix       | mysql,test       |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

导出数据库 mysqldump -u root -p -A -B > my_data.sql
解锁表 unlock tables;

创建同步账户

grant replication slave on *.* to 'rep'@'192.168.124.%' identified by '123456';

导入数据库 mysql -u root -p'123456' < my_data.sql &

创建同步任务

change master to master_host='192.168.124.100', master_user='rep', master_password='123456', master_log_file='mysql-bin.000012',master_log_pos=21144745;

启动slave start slave;

查看同步状态,看到两个Yes就可以了

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.124.100
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000007
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,test
           Replicate_Do_Table:
       Replicate_Ignore_Table:

反过来做一次就可以主主同步了，数据库已经同步了，不需要再导数据了

keepalived配置

安装keepalived

yum -y install keepalived

配置文件

! Configuration File for keepalived
global_defs {
     notification_email {
     luwenju@live.cn
     }
     notification_email_from luwenju@live.cn
     smtp_server 127.0.0.1
     smtp_connect_timeout 30
     router_id mysql_master
     }

vrrp_instance VI_1 {
     state BACKUP   #两台配置此处均是BACKUP
     interface ens33
     virtual_router_id 51
     priority 100   #优先级，另一台改为90
     advert_int 1
     nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设>置
     authentication {
     auth_type PASS
     auth_pass 1111
     }
     virtual_ipaddress {
     192.168.124.102
     }
     }

virtual_server 192.168.124.102 3306 {
     delay_loop 2   #每个2秒检查一次real_server状态
     lb_algo wrr   #LVS算法
     lb_kind DR    #LVS模式
     persistence_timeout 60   #会话保持时间
     protocol TCP
     real_server 192.168.124.100 3306 {
     weight 3
     notify_down /scripts/mysql.sh  #检测到服务down后执行的脚本
     TCP_CHECK {
     connect_timeout 10    #连接超时时间
     nb_get_retry 3       #重连次数
     delay_before_retry 3   #重连间隔时间
     connect_port 3306   #健康检查端口
     }
     }

创建脚本

vim /scripts/mysql.sh

#!/bin/sh
pkill keepalived

赋予执行权限chmod +x mysql.sh

keepalived是什么#

keepalived+mysql#

mysql主主同步#

配置修改#

同步数据#

keepalived配置#

安装keepalived#

配置文件#

创建脚本#