MySQL复制



能够保证数据的冗余的同时可以做读写分离来分担系统压力,如果是主主复制还可以很好的避免主节点的单点故障。但是MySQL主主复制存在一些问题无法满足我们的实际需要:未提供统一访问入口来实现负载均衡,如果其中master宕掉的话需要手动切换到另外一个master,而不能自动进行切换。

这篇文章下面要介绍如何通过LVS+Keepalived的方式来是实现MySQL的高可用性,同时解决以上问题。

Keepalived和LVS介绍

Keepalived

是一个基于VRRP(虚拟路由冗余协议)可用来实现服务高可用性的软件方案,避免出现单点故障。Keepalived一般用来实现轻量级高可用性,且不需要共享存储,一般用于两个节点之间,常见有LVS+Keepalived、Nginx+Keepalived组合。

LVS

(Linux Virtual Server)是一个高可用性虚拟的服务器集群系统。本项目在1998年5月由章文嵩博士成立,是中国国内最早出现的自由软件项目之一。LVS主要用于多服务器的负载均衡,作用于网络层。LVS构建的服务器集群系统中,前端的负载均衡层被称为Director Server;后端提供服务的服务器组层被称为Real Server。通过下图可以大致了解LVS的基础架构。



keepalived配置mysql双主复制_Server



LVS有三种工作模式,分别是DR(Direct Routing 直接路由)、TUN(Tunneling IP隧道)、NAT(Network Address Translation 网络地址转换)。其中TUN模式能够支持更多的Real Server,但需要所有服务器支持IP隧道协议;DR也可以支持相当的Real Server,但需要保证Director Server虚拟网卡与物理网卡在同一网段;NAT扩展性有限,无法支持更多的Real Server,因为所有的请求包和应答包都需要Director Server进行解析再生,影响效率。 同时,LVS负载均衡有10中调度算法,分别是rr、wrr、lc、wlc、lblc、lblcr、dh、sh、sed、nq(详细介绍本文不在说明)

本文中将利用LVS实现MySQL的读写负载均衡,Keepalived避免节点出现单点故障。

LVS+Keepalived配置

环境准备

LVS1:172.30.8.192

LVS2:172.30.8.193

MySQL Server1:172.30.8.190

MySQL Server2:172.30.8.191

VIP:172.30.8.200

OS: CentOS 6.5

 

keepalived配置mysql双主复制_mysql_02

 

Mysql安装及双主配置

MySQL:5.1.73

本文为了方便直接采用的yum安装方式安装将不在介绍

全局配置(即MAster,Slave都需配置)

1)修改配置文件



#vim /etc/my.cnf
添加
log-bin=mysql-bin
server-id=190



 修改完需要重启数据库

 注:server-id是唯一id一般都采用ip末尾

2)分配复制权限,主库和从库均需要执行



mysql> grant replication client,replication slave on *.* to root@'172.30.8.%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)



2)清空日志文件,主从库都是默认开启二进制日志文件



mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     26636 |
| mysql-bin.000002 |   1069399 |
| mysql-bin.000003 |     26636 |
| mysql-bin.000004 |   1069399 |
| mysql-bin.000005 |       536 |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)



 需要注意的是,如果不想清空日志文件的话,需要记录当前master的log_file和log_pos,并在下面启用复制操作时指定这两个参数或者在slave的配置文件指定。

Slave配置

1)启用复制
让slave连接master并开始重做master二进制日志中的事件



mysql> change master to  master_host='172.30.8.190',master_user='root',master_password='1231234',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=107;



master_log_pos如果是日志的开始位置值可以为0;master_log_file是初始日志文件。如果master日志没有被清空,这里就是当前master的日志信息

要注意的是,默认情况下,会同步该用户下所有的DB,如果想限定哪些DB,有3种思路

  1. 在master上的/etc/my.inf中通过参数binlog-do-db、binlog-ignore-db设置需要同步的数据库。
  2. 在执行grant分配权限操作的时候,限定数据库
  3. 在slave上限定数据库使用replicate-do-db=dbname

2)开启slave



mysql> start slave;
Query OK, 0 rows affected (0.00 sec)



 3)确认Slave是否和Mater成功通信。如果 Slave_IO_Running和Slave_SQL_Running都是yes,则证明配置成功



mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.8.190
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1042
               Relay_Log_File: mysqld-relay-bin.000020
                Relay_Log_Pos: 510
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            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: 1042
              Relay_Log_Space: 811
              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



 至此mysql主从配置完毕,如果双主则操作相反即可。

附加:

1)mysql修改密码



mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=password("1231234") where user="root";
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)



 2)授权远程连接



mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1231234' WITH GRANT OPTION;



 同步测试

1)Master创建数据库



mysql> create database jingzi;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jingzi             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)



 2)slave查看



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jingzi             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)



 通过以上验证,可以看到主服务器上的修改能够正常同步到从服务器。

 

Keepalived安装及LVS安装(以下操作在lvs服务器主+从上操作)

Keepalived安装

keepalived下载地址

需要安装以下软件包


#yum install -y kernel-devel openssl openssl-devel gcc* (如果后边编译报错缺少什么包安装什么包就ok了)


 安装



#wget http://www.keepalived.org/software/keepalived-1.2.13.tar.gz

#tar -zxvf keepalived-1.2.13.tar.gz

#cd keepalived-1.2.13

#./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-431.5.1.el6.x86_64/



默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件,将需要的配置文件拷贝到指定位置



# mkdir /etc/keepalived

#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived  /etc/rc.d/init.d/

#cp /usr/local/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/

#cp /usr/local/keepalived/etc/keepalived/keepalived.conf  /etc/keepalived/

#cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

#chkconfig keepalived on



LVS安装

ipvsadm下载地址

需要安装以下软件包



#yum install -y libnl* popt* kernel-headers   popt-static



 查看是否加载lvs模块



#modprobe -l |grep ipvs



 解压安装



#ln -s /usr/src/kernels/2.6.32-431.5.1.el6.x86_64/ /usr/src/linux

#tar -zxvf ipvsadm-1.26.tar.gz

#make && make install



 LVS安装完成,查看当前LVS集群



#ipvsadm -ln



至此keppalived+lvs安装完成接下来我们进行配置

 

LVS+Keepalived配置(以下操作是在lvs-1上执行lvs-2类似)

配置Keepalived

修改keepalived配置文件并添加以下代码



[root@lvs-1 ~]# vim /etc/keepalived/keepalived.conf 

global_defs {
    router_id LVS_1  # 设置lvs的id,在一个网络内应该是唯一的
}
vrrp_instance VI_1 {
    state MASTER   #指定Keepalived的角色,MASTER为主,BACKUP为备
    interface eth0  #虚拟ip所在网
    virtual_router_id 51  #虚拟路由编号,主备要一致
    priority 100  #定义优先级,数字越大,优先级越高,主DR必须大于备用DR
    advert_int 1  #检查间隔,默认为1s
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.30.8.200  #定义虚拟IP(VIP)为172.30.8.200,可多设,每行一个
    }
}
# 定义对外提供服务的LVS的VIP以及port
virtual_server 172.30.8.200 3306 {
    delay_loop 6 # 设置健康检查时间,单位是秒
    lb_algo wlc # 设置负载调度的算法为wlc 基于权重的调度算法
    lb_kind DR # 设置LVS实现负载的机制,有NAT、TUN、DR三个模式
    nat_mask 255.255.255.0
    persistence_timeout 50 会话保持时间   (为了实验效果可以注释掉该选项)
    protocol TCP
    real_server 172.30.8.190 3306 {  # 指定real server1的IP地址
        weight 3   # 配置节点权值,数字越大权重越高
        TCP_CHECK {
        connect_timeout 10
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
        }
    }
    real_server 172.30.8.191 3306{  # 指定real server2的IP地址
        weight 3  # 配置节点权值,数字越大权重越高
        TCP_CHECK {
        connect_timeout 10
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
        }
     }
 }



 注意lvs-2操作类似只需修改以下选项即可

 



1.state MASTER   #指定Keepalived的角色,MASTER为主,BACKUP为备   
改为

state BACKUP   #指定Keepalived的角色,MASTER为主,BACKUP为备
2.priority 100  #定义优先级,数字越大,优先级越高,主DR必须大于备用DR
改为
priority 99  #定义优先级,数字越大,优先级越高,主DR必须大于备用DR



 

 配置LVS(此脚本需要添加到lvs-1,Mysql-master,Mysql-slave)

编写LVS启动脚本/etc/init.d/realserver



[root@lvs-1 ~]# vim /etc/init.d/realserver 

SNS_VIP=172.30.8.200
/etc/rc.d/init.d/functions
case "$1" in
start)
       ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
       /sbin/route add -host $SNS_VIP dev lo:0
       echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
       sysctl -p >/dev/null 2>&1
       echo "RealServer Start OK"
       ;;
stop)
       ifconfig lo:0 down
       route del $SNS_VIP >/dev/null 2>&1
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
       echo "RealServer Stoped"
       ;;
*)
       echo "Usage: $0 {start|stop}"
       exit 1
esac
exit 0



 

 将lvs脚本加入开机自启动



#chmod +x /etc/init.d/realserver
#echo "/etc/init.d/realserver" >> /etc/rc.d/rc.local



 分别启动LVS和keepalived



# service realserver start
# service keepalived start



注意此时网卡的变化,可以看到虚拟网卡已经分配到了realserver上。

此时查看LVS集群状态,可以看到集群下有两个Real Server,调度算法,权重等信息。ActiveConn代表当前Real Server的活跃连接数



[root@lvs-1 ~]# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  172.30.8.200:3306 wlc
  -> 172.30.8.190:3306            Route   3      0          0         
  -> 172.30.8.191:3306            Route   3      0



 测试验证

关闭MySQL Server2



[root@mysql-2 ~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]



 在LVS1查看/var/log/messages中关于keepalived日志,LVS1检测到了MySQL Server2宕机,同时LVS集群自动剔除了故障节点



May 18 23:52:26 localhost Keepalived_healthcheckers[5909]: TCP connection to [172.30.8.191]:3306 failed !!!
May 18 23:52:26 localhost Keepalived_healthcheckers[5909]: Removing service [172.30.8.191]:3306 from VS [172.30.8.200]:3306



 从新启动MySQL Server2后自动将故障节点自动加入LVS集群



May 18 23:53:50 localhost Keepalived_healthcheckers[5909]: TCP connection to [172.30.8.191]:3306 success.
May 18 23:53:50 localhost Keepalived_healthcheckers[5909]: Adding service [172.30.8.191]:3306 to VS [172.30.8.200]:3306



 关闭LVS1上的Keepalived(模拟宕机操作),查看LVS1上的日志,可以看到Keepalived移出了LVS1上的VIP



May 18 23:54:28 localhost Keepalived[5908]: Stopping Keepalived v1.2.13 (05/17,2017)
May 18 23:54:28 localhost Keepalived_vrrp[5910]: VRRP_Instance(VI_1) sending 0 priority
May 18 23:54:28 localhost Keepalived_vrrp[5910]: VRRP_Instance(VI_1) removing protocol VIPs.
May 18 23:54:28 localhost Keepalived_healthcheckers[5909]: Netlink reflector reports IP 172.30.8.200 removed
May 18 23:54:28 localhost Keepalived_healthcheckers[5909]: Removing service [172.30.8.190]:3306 from VS [172.30.8.200]:3306
May 18 23:54:28 localhost Keepalived_healthcheckers[5909]: Removing service [172.30.8.191]:3306 from VS [172.30.8.200]:3306



 同时查看LVS2上日志,可以看到LVS2成为了Master,并接管了VIP



May 18 23:54:29 localhost Keepalived_vrrp[5187]: VRRP_Instance(VI_1) Transition to MASTER STATE
May 18 23:54:30 localhost Keepalived_vrrp[5187]: VRRP_Instance(VI_1) Entering MASTER STATE
May 18 23:54:30 localhost Keepalived_vrrp[5187]: VRRP_Instance(VI_1) setting protocol VIPs.
May 18 23:54:30 localhost Keepalived_healthcheckers[5186]: Netlink reflector reports IP 172.30.8.200 added
May 18 23:54:30 localhost Keepalived_vrrp[5187]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.30.8.200
May 18 23:54:35 localhost Keepalived_vrrp[5187]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.30.8.200



在LVS2上查看LVS集群状态,一切正常



[root@lvs-2 ~]# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  172.30.8.200:3306 wlc
  -> 172.30.8.190:3306            Route   3      0          0         
  -> 172.30.8.191:3306            Route   3      1          0



 

总结

  • MySQL主主复制是集群的基础,组成Server Array,其中每个节点作为Real Server。
  • LVS服务器提供了负载均衡的作用,将用户请求分发到Real Server,一台Real Server故障并不会影响整个集群的。
  • Keepalived搭建主备LVS服务器,避免了LVS服务器的单点故障,出现故障时可以自动切换到正常的节点。