Lvs+keepalived+mysql(主从复制)

1、准备环境

操作系统：centos 6.5

2台机器主机名为node53、node54

 

	Mysql	Lvs	keepalived
node2	Y	Y	Y
node3	Y	Y	Y

 

 

 

 

 

 

2、安装mysql（配置主主复制）

 

 

服务器名称	ip	vip
node2	192.168.0.12	192.168.0.200
node3	192.168.0.13	192.168.0.200

  

 

 

 

 

2.1、安装数据库

      yum install -y mysql

 

2.2、关闭防火墙

    

 service iptables stop

 chkconfig iptables off

 

2.3、修改root用户名/访问权限

use mysql

delete from user where user='';

grant replication slave on *.* to 'root'@'%' identified by '123456';

2.4、修改/etc/my.cnf

在node2机器上配置如下

 

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

 

log-bin=mysql-bin

server-id=1

#需要记录二进制日志的数据库，如果有多个库，写多个binlog-do-db，用逗号隔开，测试时发现不能同步？有空在研究

binlog-do-db=amdb

binlog-do-db=hivedb

binlog-do-db=ooziedb

#不需要记录日志的数据库

binlog-ignore-db=mysql

#需要同步的数据库

replicate-do-db=amdb

replicate-do-db=hivedb

replicate-do-db=ooziedb

#不需要同步的库

replicate-ignore-db=mysql,information_schema

#默认值3600s

slave-net-timeout=60

 

#保证slave挂在任何一台master上都会接收到另一个master的写入信息

log-slave-updates

sync_binlog=1

slave-skip-errors=all

auto_increment_offset=1

auto_increment_increment=2

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

 

在node3机器上配置如下

 

 

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

 

log-bin=mysql-bin

#设置不同的id

server-id=2

#需要记录二进制日志的数据库，如果有多个库，写多个binlog-do-db，用逗号隔开，测试时发现不能同步？有空在研究

binlog-do-db=amdb

binlog-do-db=hivedb

binlog-do-db=ooziedb

#不需要记录日志的数据库

binlog-ignore-db=mysql

#需要同步的数据库

replicate-do-db=amdb

replicate-do-db=hivedb

replicate-do-db=ooziedb

#不需要同步的库

replicate-ignore-db=mysql,information_schema

#默认值3600s

slave-net-timeout=60

 

#保证slave挂在任何一台master上都会接收到另一个master的写入信息

log-slave-updates

sync_binlog=1

slave-skip-errors=all

auto_increment_offset=1

auto_increment_increment=2

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

2.5、重启mysql服务

     service mysqld restart

 

2.6、分别在node2、node3上执行

 

mysql> flush tables with read lock;#防止进入新的数据

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;

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

            File: mysql-bin.000007

        Position: 438

    Binlog_Do_DB: db_rocky

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

 

在node3

 

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;

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

            File: mysql-bin.000008

        Position: 107

    Binlog_Do_DB: db_rocky

Binlog_Ignore_DB: mysql

1 row in set (0.01 sec)

 

2.7、在node2、node3指定同步位置

在node53

mysql> change master to master_host='10.211.55.54',master_user='root',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=107;

Query OK, 0 rows affected (0.05 sec)

 

在node3

 

mysql> change master to master_host='10.211.55.53',master_user='root',master_password='123456',

    -> master_log_file='mysql-bin.000007',master_log_pos=438;

Query OK, 0 rows affected (0.15 sec)

 

注：master_log_file，master_log_pos由上面主服务器查出的状态值中确定

 

2.8、解锁表

 

mysql> unlock tables;

mysql> start slave;

 

2.9、分别在2台机器查看状态

show slave status\G;

如果显示如下，则正常

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

3、Lvs配置

3.1 安装lvs

yum install ipvsadm

3.2 编写启动脚本

 

vim /etc/init.d/realserver.sh

#!/bin/bash

#description: Config realserver lo and apply noarp

  

SNS_VIP=10.211.55.200

/etc/rc.d/init.d/functions

case "$1" in

  

start)

      ifconfig eth0:1 $SNS_VIP/24

      /sbin/route add -host $SNS_VIP dev eth0:1

      echo "vip Start OK"

      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 eth0:1 down

      route del $SNS_VIP >/dev/null 2>&1

      echo "vip Stoped"

      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

 

配置开机启动

 

 echo "/etc/init.d/realserver.sh start" >> /etc/rc.d/rc.local

4、配置keepalived

4.1、安装keepalived

  yum install keepalived

 

4.2、配置keepalived.conf

 

 

  

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

  

        notification_email {

             yangyufans@qq.com

        }

        notification_email_from yangyufans@qq.com

        smtp_server smtp.qq.com

        smtp_connect_timeout 30

        router_id LVS1

}

  

vrrp_sync_group test {

        group {

                loadbalance

        }

}

  

vrrp_instance loadbalance {

        state MASTER

        lvs_sync_daemon_inteface eth0

        virtual_router_id 51

        priority 150

        advert_int 1

  

        authentication {

                auth_type PASS

                auth_pass 1111

        }

  

        virtual_ipaddress {

                10.211.55.200 dev eth0 label eth0:1

        }

}

virtual_server 10.211.55.200 3306 {

        delay_loop 6

        lb_algo rr

        lb_kind DR

        persistence_timeout 20

        protocol TCP

        sorry_server 10.211.55.54 3306

        real_server 10.211.55.53 3306 {

                weight 3

                TCP_CHECK {

                        connect_timeout 3

                        nb_get_retry 3

                        delay_before_retry 3

                        connect_port 3306

                }

        }

}

 

 

在node3上配置keepalived.conf

2个参数不同，其他配置相同

state BACKUP

priority 100

 

chkconfig keepalived on

 

插入图片=============