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上

  1. 先锁表 flush table with read lock;
  2. 记录状态 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)
  3. 导出数据库 mysqldump -u root -p -A -B > my_data.sql

  4. 解锁表 unlock tables;
  5. 创建同步账户

    grant replication slave on *.* to 'rep'@'192.168.124.%' identified by '123456';
  6. 导入数据库 mysql -u root -p'123456' < my_data.sql &
  7. 创建同步任务

    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;
  8. 启动slave start slave;

  9. 查看同步状态,看到两个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:
  10. 反过来做一次就可以主主同步了,数据库已经同步了,不需要再导数据了

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