笔记：mysql双主，keepalived 配置

准备工作

1，两台主机,3个IP
mysql-1:192.168.0.1
mysql-2:192.168.0.2
vip 192.168.0.3

2，mysql安装包
下载地址：https://dev.mysql.com/downloads/mysql/

3，keepalived安装包
下载地址：https://www.keepalived.org/download.html

4，已验证的版本兼容
CentOS release 6.7 (Final) 兼容 keepalived-1.2.24

安装mydwl

1，安装mysql软件
略
2，配置my.cnf
两台主机使用不同的 server-id

[client]
port=3306
socket=/data9/mysql/log/mysql.sock
#客户端编码
default-character-set=utf8

[mysqld]
port=3306
basedir=/data9/mysql
datadir=/data9/mysql/data
socket=/data9/mysql/log/mysql.sock
log-error=/data9/mysql/log/mysql.log
pid-file=/data9/mysql/log/mysql.pid

server-id=1
log-bin=mysql-bin
#max_binlog_size = 500M
#expire_logs_days = 15
#主从同步时，需要同步的数据库，多个数据库写多行binlog_do_db配置
#binlog_do_db=test_db
#主从同步时，不需要同步的数据库，多个数据库写多行binlog_ignore_db配置
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
#主从同步方式
binlog_format=row
#服务端编码
character-set-server=utf8

双主配置

mysql-1创建用户并授权

grant replication slave on *.* to slave@'192.168.0.2' identified by 'slave';
flush privileges;

查看mysql-1

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

配置mysql-2对mysql-1的从库
登录mysql-2数据库

mysql> change master to 
    -> master_host='192.168.0.1',
    -> master_port=3306, 
    -> master_user='slave',
    -> master_password='slave', 
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=154; 

mysql> start slave;

相同方式配置mysql-1对mysql-2的从库

keepalived

1，安装keepalived并创建shutdown.sh

[root@localhost ~]## cd /opt
[root@localhost opt]# yum -y install gcc openssl-devel popt-devel psmisc
[root@localhost opt]# tar -zxvf keepalived-2.3.1.tar.gz
[root@localhost opt]# cd keepalived-2.3.1
[root@localhost keepalived-2.3.1]# ./configure --prefix=/opt/keepalived-2.3.1
[root@localhost keepalived-2.3.1]# make && make install

[root@localhost keepalived-2.3.1]mkdir /etc/keepalived
[root@localhost keepalived-2.3.1]cp keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf
[root@localhost keepalived-2.3.1]cp keepalived/etc/init.d/keepalived /etc/init.d/
[root@localhost keepalived-2.3.1]cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@localhost keepalived-2.3.1]cp bin/keepalived /usr/sbin/
[root@localhost keepalived-2.3.1]vi /etc/keepalived/shutdown.sh
#!/bin/bash
#该脚本是在mysql服务出现异常时，将keepalived应用停止，从而使虚拟vip主机自动连接到另一台mysql上
killall keepalived
[root@localhost keepalived-2.3.1]chmod +x /etc/keepalived/shutdown.sh

2，查看网卡

bond0

[root@localhost ~]# ifconfig
bond0: flags=5187<UP,BROADCAST,RUNNING,MASTER,MULTICAST>  mtu 1500

3，配置keepalived
192.168.0.1：/etc/keepalived/keepalived.conf

! Configuration File for keepalived
 
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_MASTER
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
 
vrrp_instance VI_1 {
    state MASTER
    #interface为刚才查到的本机网卡名称
    interface band0
    #同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。
    virtual_router_id 212
    #字数越大，优先级越高，master必须大于backup
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2222
    }
    virtual_ipaddress {
       #该ip为虚拟出来的vip地址
       192.168.0.3
    }
}
 
#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口
virtual_server 192.168.0.3 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
    #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
    real_server 192.168.0.1 3306 {
        #当该ip 端口连接异常时，执行该脚本
        notify_down /etc/keepalived/shutdown.sh
        TCP_CHECK {
            #实际物理机ip地址
            connect_ip 192.168.0.1
            #实际物理机port端口
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

192.168.0.2：/etc/keepalived/keepalived.conf

! Configuration File for keepalived
 
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_MASTER
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
 
vrrp_instance VI_1 {
    state MASTER
    #interface为刚才查到的本机网卡名称
    interface band0
    #同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。
    virtual_router_id 212
    #字数越大，优先级越高，master必须大于backup
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2222
    }
    virtual_ipaddress {
       #该ip为虚拟出来的vip地址
       192.168.0.3
    }
}
 
#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口
virtual_server 192.168.0.3 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
    #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
    real_server 192.168.0.2 3306 {
        #当该ip 端口连接异常时，执行该脚本
        notify_down /etc/keepalived/shutdown.sh
        TCP_CHECK {
            #实际物理机ip地址
            connect_ip 192.168.0.2
            #实际物理机port端口
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

4，开机自启动并启动keepalived

[root@localhost keepalived-2.3.1]# systemctl enable keepalived
[root@localhost keepalived-2.3.1]# systemctl start keepalived