笔记：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.pidserver-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 keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 127.0.0.1smtp_connect_timeout 30router_id LVS_MASTERvrrp_skip_check_adv_addrvrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 {state MASTER#interface为刚才查到的本机网卡名称interface band0#同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。virtual_router_id 212#字数越大，优先级越高，master必须大于backuppriority 100advert_int 1authentication {auth_type PASSauth_pass 2222}virtual_ipaddress {#该ip为虚拟出来的vip地址192.168.0.3}
}#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口
virtual_server 192.168.0.3 3306 {delay_loop 6lb_algo rrlb_kind NATpersistence_timeout 50protocol TCP#real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口real_server 192.168.0.1 3306 {#当该ip 端口连接异常时，执行该脚本notify_down /etc/keepalived/shutdown.shTCP_CHECK {#实际物理机ip地址connect_ip 192.168.0.1#实际物理机port端口connect_port 3306connect_timeout 3nb_get_retry 3delay_before_retry 3}}
}

192.168.0.2：/etc/keepalived/keepalived.conf

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

4，开机自启动并启动keepalived

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