lvs dr 链接mysql_Keepalived+LVS(DR)+MySQL

实验环境

主机名

IP

VIP

服务

主备

KA_LV_MYSQL_01

192.168.30.130

192.168.30.100

keepalived、LVS、MySQL

MASTER

KA_LV_MYSQL_02

192.168.30.131

BACKUP

软件版本：

[root@KA_LV_MYSQL_01 ~]# cat /etc/redhat-release

CentOS release 6.9 (Final)

[root@KA_LV_MYSQL_01 ~]# keepalived -v

Keepalived v1.2.13 (03/19,2015)

[root@KA_LV_MYSQL_01 ~]# mysql -V

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

[root@KA_LV_MYSQL_01 ~]# ipvsadm -v

ipvsadm v1.26 2008/5/15 (compiled with popt and IPVS v1.2.1)

安装：

配置好本地YUM源，直接采用光盘镜像中的软件进行安装。

[root@KA_LV_MYSQL_01 ~]# yum install -y mysql mysql-server keepalived ipvsadm

配置MySQL主从：

[root@KA_LV_MYSQL_01 ~]# cp /etc/my.cnf{,.bak}

[root@KA_LV_MYSQL_01 ~]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-bin=mysql-master-bin

server-id=1

binlog-ignore-db=mysql#忽略这些库

binlog-ignore-db=user

binlog-ignore-db=information_schema

值得注意的是：需要忽略哪些数据库时有三种方法：

1.在主节点上的/etc/my.cnf中使用binlog-do-db、binlog-ignore-db参数指定。2.在使用grant指定权限时，指定数据库3.在slave上限定数据库使用replicate-do-db=dbname

[root@KA_LV_MYSQL_01 ~]# mysql -uroot -p123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 754

Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication client,replication slave on *.* to root@'192.168.30.%' identified by '123456';

mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;

mysql> flush privileges;

mysql> show binary logs;

+-------------------------+-----------+

| Log_name | File_size |

+-------------------------+-----------+

| mysql-master-bin.000001 | 19726 |

| mysql-master-bin.000002 | 765307 |

| mysql-master-bin.000003 | 125 |

| mysql-master-bin.000004 | 276 |

+-------------------------+-----------+

4 rows in set (0.00 sec)

mysql> reset master;

Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;

+-------------------------+-----------+

| Log_name | File_size |

+-------------------------+-----------+

| mysql-master-bin.000001 | 106 |

+-------------------------+-----------+

1 row in set (0.00 sec)

配置mysql从节点

[root@KA_LV_MYSQL_02 ~]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-bin=mysql-slave-bin

server-id=2

mysql> change master to

-> master_host='192.168.30.130',

-> master_user='root',

-> master_password='123456',

-> master_log_file='mysql-master-bin.000001',

-> master_port=3306,

-> master_log_pos=106;

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.30.130

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-master-bin.000001

Read_Master_Log_Pos: 106

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 258

Relay_Master_Log_File: mysql-master-bin.000001

Slave_IO_Running: Yes            #这两个Yes说明mysql主从配置成功

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: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 106

Relay_Log_Space: 414

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

ERROR:

No query specified

配置keepalived：

[root@KA_LV_MYSQL_01 ~]# cp /etc/keepalived/keepalived.conf{,.bak}

[root@KA_LV_MYSQL_01 ~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

1255560195@qq.com

}

notification_email_from 1255560195@qq.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id KA_LV_MYSQL_01

}

vrrp_instance VI_1 {

state MASTER

interface eth0

virtual_router_id 51

priority 200

advert_int1

authentication {

auth_type PASS

auth_pass1111}

virtual_ipaddress {192.168.30.100}

}

virtual_server192.168.30.100 3306{

delay_loop6lb_algo wlc

lb_kind DR

nat_mask255.255.255.0persistence_timeout50protocol TCP

real_server192.168.30.130 3306{

weight3TCP_CHECK {

connect_timeout10nb_get_retry3delay_before_retry3connect_port3306}

}

real_server192.168.30.131 3306{

weight3TCP_CHECK {

connect_timeout10nb_get_retry3delay_before_retry3connect_port3306}

}

}

在KA_LV_MYSQL_02上只需修改如下两个地方即可。

router_id KA_LV_MYSQL_02

priority 100

配置LVS

[root@KA_LV_MYSQL_01 network-scripts]# cp ifcfg-lo ifcfg-lo:1

[root@KA_LV_MYSQL_01 network-scripts]# vim ifcfg-lo:1

DEVICE=lo:1

IPADDR=192.168.30.100

NETMASK=255.255.255.255

#NETWORK=

# If you're having problems with gated making 127.0.0.0/8 a martian,

# you can change this to something else (255.255.255.255, for example)

BROADCAST=192.168.30.100

ONBOOT=yes

NAME=loopback

[root@KA_LV_MYSQL_01 network-scripts]# service network restart

[root@KA_LV_MYSQL_01 ~]# service ipvsadm restart && service keepalived restart

[root@KA_LV_MYSQL_01 network-scripts]# chkconfig ipvsadm on

[root@KA_LV_MYSQL_01 network-scripts]# chkconfig keepalived on

[root@KA_LV_MYSQL_01 network-scripts]# ipvsadm -A -t 192.169.30.100:3306 -s wlc

[root@KA_LV_MYSQL_01 network-scripts]# ipvsadm -a -t 192.168.30.100:3306 -r 192.168.30.130 -g

Destination already exists

[root@KA_LV_MYSQL_01 network-scripts]# ipvsadm -a -t 192.168.30.100:3306 -r 192.168.30.131 -g

Destination already exists

[root@KA_LV_MYSQL_01 network-scripts]# ipvsadm -ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

-> RemoteAddress:Port Forward Weight ActiveConn InActConn

TCP 192.168.30.100:3306 wlc persistent 50

-> 192.168.30.130:3306 Local 3 0 0

-> 192.168.30.131:3306 Route 3 0 0

TCP 192.169.30.100:3306 wlc

在KA_LV_MYSQL_02上做相应操作即可。

测试：

在KA_LV_MYSQL_01上登入MySQL

在KA_LV_MYSQL_02上登入MySQL并查看

手动停掉KA_LV_MYSQL_02上的MySQL，去KA_LV_MYSQL_01上查看日志如下，可以看出，当停掉MySQL时，keepalived可以将故障节点自动剔除，当故障节点恢复时，又会重新加入到集群中。

[root@KA_LV_MYSQL_01 network-scripts]# tail /var/log/messages

Apr 25 08:12:43 KA_LV_MYSQL_01 Keepalived_vrrp[70845]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.30.100

Apr 25 08:12:48 KA_LV_MYSQL_01 Keepalived_vrrp[70845]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.30.100

Apr 25 08:16:15 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: TCP connection to [192.168.30.131]:3306 failed !!!

Apr 25 08:16:15 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: Removing service [192.168.30.131]:3306 from VS [192.168.30.100]:3306

Apr 25 08:16:15 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: Remote SMTP server [127.0.0.1]:25 connected.

Apr 25 08:16:15 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: SMTP alert successfully sent.

Apr 25 08:18:45 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: TCP connection to [192.168.30.131]:3306 success.

Apr 25 08:18:45 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]:Adding service [192.168.30.131]:3306 to VS [192.168.30.100]:3306

Apr 25 08:18:45 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: Remote SMTP server [127.0.0.1]:25 connected.

Apr 25 08:18:45 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: SMTP alert successfully sent.

手动关掉KA_LV_MYSQL_01上的keepalived，查看主备节点日志日志，可以看出，KA_LV_MYSQL_02已经接变成master，并接管了VIP。

[root@KA_LV_MYSQL_01 ~]# tail /var/log/messages

Apr 25 09:09:49 KA_LV_MYSQL_01 Keepalived[70843]: Stopping Keepalived v1.2.13 (03/19,2015)

Apr 25 09:09:49 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: Removing service [192.168.30.130]:3306 from VS [192.168.30.100]:3306

Apr 25 09:09:49 KA_LV_MYSQL_01 Keepalived_vrrp[70845]: VRRP_Instance(VI_1) sending 0 priority

Apr 25 09:09:49 KA_LV_MYSQL_01 Keepalived_healthcheckers[70844]: Removing service [192.168.30.131]:3306 from VS [192.168.30.100]:3306

Apr 25 09:09:49 KA_LV_MYSQL_01 Keepalived_vrrp[70845]: VRRP_Instance(VI_1) removing protocol VIPs.

Apr 25 09:09:49 KA_LV_MYSQL_01 kernel: IPVS: __ip_vs_del_service: enter

[root@KA_LV_MYSQL_02 ~]# tail /var/log/messages

Apr 25 09:09:50 KA_LV_MYSQL_02 Keepalived_vrrp[6325]: VRRP_Instance(VI_1) Transition to MASTER STATE

Apr 25 09:09:51 KA_LV_MYSQL_02 Keepalived_vrrp[6325]: VRRP_Instance(VI_1) Entering MASTER STATE

Apr 25 09:09:51 KA_LV_MYSQL_02 Keepalived_vrrp[6325]: VRRP_Instance(VI_1) setting protocol VIPs.

Apr 25 09:09:51 KA_LV_MYSQL_02 Keepalived_vrrp[6325]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.30.100

Apr 25 09:09:51 KA_LV_MYSQL_02 Keepalived_healthcheckers[6324]: Netlink reflector reports IP 192.168.30.100 added

Apr 25 09:09:56 KA_LV_MYSQL_02 Keepalived_vrrp[6325]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.30.100

KA_LV_MYSQL_02上的LVS集群正常。

[root@KA_LV_MYSQL_02 ~]# ipvsadm -ln

IP Virtual Server version 1.2.1 (size=4096)

Prot LocalAddress:Port Scheduler Flags

-> RemoteAddress:Port Forward Weight ActiveConn InActConn

TCP 192.168.30.100:3306 wlc persistent 50

-> 192.168.30.130:3306 Route 3 0 0

-> 192.168.30.131:3306 Local 3 0 0