lvs(DR)+keepalived+mysql主从

  1. 云栖社区>
  2. 博客>
  3. 正文

lvs(DR)+keepalived+mysql主从

技术小胖子 2017-11-10 19:27:00 浏览871
展开阅读全文

三台机器:
director(eth0192.168.0.8, vip eth0:0: 192.168.0.101)
real server1(eth0 rip: 192.168.0.140 vip lo:0:192.168.0.101)
real server2(eth0 rip: 192.168.0.141, vip lo:0:192.168.0.101)


1、自己编写的一键源码安装的lnmp脚本

2、安装LVS(DR)

yum install ipvsadm

Director  vim /usr/local/sbin/lvs_dr.sh //增加

vi /usr/local/sbin/lvs_DR.sh  #编辑一个脚本

#!/bin/bash

# directory 服务器开启路由转发功能:

echo 1 > /proc/sys/net/ipv4/ip_forward

ipv='/sbin/ipvsadm'

vip=192.168.0.100

rs1=192.168.0.7
rs2=192.168.0.5
ifconfig eth0:0 $vip broadcast $vip netmask255.255.255.255 up
route add -host $vip dev eth0:0
$ipv -C
$ipv -A -t $vip:80 -s rr 
$ipv -a -t $vip:80 -r $rs1:80 -g -w 1       #-g
代表是DR模式
$ipv -a -t $vip:80 -r $rs2:80 -g -w 1

 

两台rs上:vim/usr/local/sbin/lvs_dr_rs.sh
#! /bin/bash
vip=192.168.0.100
ifconfig lo:0 $vip broadcast $vip netmask255.255.255.255 up 
route add -host $vip 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


3、 分别配置三台机器的keepalived的配置:(配置完毕启动是先从后主)

yum安装keepalived,

 Keeplived(负载均衡+HA合成的二者为一体)

vi /etc/keepalived/keepalived.conf

##########MASTER###########

  global_defs {

  #出现故障时候发邮件给谁#   notification_email {         

  #          xxx@xxx.com

  #}

  #出现故障从哪里发邮件出去##notification_email_from xxx@xxx.com

  ##第三方的smtp##smtp_server mail.qq.com

#smtp_connect_timeout30

router_id LVS1

}

   #vrrp_script chk_http_port {

   #       script "/etc/keepalived/nginx_check.sh"

   #       interval 2

   #       weight 2

 

 vrrp_sync_group VS_group {

    group {

      VS_1

   }

   }

 

    vrrp_instance VS_1 {

        state MASTER   #备用服务器上为 BACKUP,主是MASTER

        interface eth0

        lvs_sync_daemon_interface eth0

        virtual_router_id  60

        priority 150     #备用服务器上为100,主是150

      advert_int 1

        authentication {

            auth_type PASS

            auth_pass 1111

        }

        virtual_ipaddress {

            192.168.0.101

        }

    }

    virtual_server 192.168.0.101 80 {

        delay_loop 6                  #(每隔10秒查询realserver状态)

        lb_algo rr                  #(lvs 算法)

        lb_kind DR                  #(Direct Route)

       persistence_timeout 20    #(同一IP的连接60秒内被分配到同一台realserver)

        protocol TCP                #(TCP协议检查realserver状态)

  

        real_server 192.168.0.140 80 {

            weight 100               #(权重)

            TCP_CHECK {

            connect_timeout 10       #(10秒无响应超时)

            nb_get_retry 3

            delay_before_retry 3

            connect_port 80

          }

}

             real_server192.168.0.141 80 {

             weight 100

             TCP_CHECK {

             connect_timeout 10

             nb_get_retry 3

             delay_before_retry 3

             connect_port 80

             }

          }

    }

至此,lvs(DR)+keepalived已完成

 

4、添加防火墙规则后并保存:

iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 25 -j ACCEPT

iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 80 -jACCEPT #允许80端口对外提供服务

iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 10050 -jACCEPT

iptables -A INPUT -d 192.168.2.0/24 -j ACCEPT

iptables -A INPUT -p vrrp -j ACCEPT    #LVS DR模式。当用户请求LVS-DR的VIP时,只有DR响应客户端的ARP广播包,允许vrrp虚拟路由器冗余协议

iptables -A INPUT -p icmp -j ACCEPT

iptables -A INPUT -jREJECT --reject-with icmp-host-prohibited

 iptables -A FORWARD -j REJECT --reject-withicmp-host-prohibited

 

  service iptables save


5mysql主从配置

理论:主(A)的数据库数据有变化记录一个bin_log,再推给从(B),B数据库的bin_log会记录去更改自己数据库(数据同步)

(1)打开主的mysql配置(my.cnf)添加或打开注释如下配置:

[nysqld]

log-bin=mysql-bin            #打开这两个注释,log-bin的值可以自定义

server  id      = 1

binlog-do-db=db1     #只针对指定数据库做主从

#binlog-ignore-db=mysql      #打开注释是不去同步指定的库(黑名单)

#log-slave-updates = true        #是否继续传递下去为“是”,这个是用于主主,若是多个主的server id


(2)从的my.cnf将原来的配置改为以下:   #(用于多实例)

[mysqld]

port            = 3307

socket          =/tmp/mysql_slave.sock

datadir         =/data/mysql_slave

server id  =1 改成server id   =2

replicate-do-db=db1     #只针对指定数据库做主从

#replicate-ignore-db=mysql      #不去同步指定的库(黑名单)

(2)<1>cp /etc/init.d/mysqld  /etc/init.d/mysqldslave

     <2>vim /etc/init.d/mysqldslave将原来的配置改为以下:

   找到第一个basedir:

      basedir=/usr/local/mysql_slave

      datadir=/data/mysql_slave

      conf=$basedir/my.cnf

PS:上面mysql的从是用于多实例


(2)从的mysql配置:

[mysqld]

server id =2     #server id的变更

replicate-do-db=db1     #只针对指定数据库做主从

#replicate-ignore-db=mysql      #不去同步指定的库(黑名单)


6、测试能否进入数据库(前提在/etc/profile.d/path.sh里加入PATH=$PATH:/usr/local/mysql/binsource /etc/profile.d/path.sh通过sock登录:mysql -S sock所在位置

(想添加多一个mysql,按照slave的做法即可)


7、做主从前的最后一个主要配置(主从数据库库信息一致):

mysqldump  -S  /tmp/mysql.sock  mysql > 123.sql   #备份mysql数据库

mysql -S /tmp/myslq.sock db1 < 123.sql        #mysql数据库里的东西导入db1数据库


8、进入主的mysql:

mysql>grant replication slave on *.* to ‘repl’@’127.0.0.1’ identifiedby ‘123456’;       #针对本地做主从,只赋予replication权限

mysql>grant replication slave on *.* to 'repl'@'从ip’ identifiedby ‘密码’; 

mysql> flush privileges;

mysql> flush tables with read lock;       #对表的读锁死read lock)的作用是让show master statusfileposition不变

mysql> show master status;           #查看master数据信息

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

| File                            | Position| Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000009  |         106 |                db1      |                                  |

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

1 row in set (0.00 sec)


9、在从的将主传过来的123.sql导入本地数据库

主的操作:scp 123.sql  从ip:123.sql


[root@user16 ~]# mysql -S /tmp/mysql_slave.sock -e "createdatabase db1"    #在从上新建db1数据库

[root@user16 ~]# mysql -S /tmp/mysql_slave.sock db1 < 123.sql     #导入备份的sql去从的db1,实现主从相同

mysql> slave stop;                    #先停掉从的

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>change master tomaster_host='主ip',master_port=3306,master_user='repl',master_password='',master_log_file='mysql-bin.000009',master_log_pos=106;          #这一步很重要,核心命令

mysql>flush privileges;

mysql>slave start;

mysql> show slave status\G;      #查看从库的配置,是否成功看Slave_IO_RunningSlave_SQL_Running是否为 Yes


至此,没有出现任何错误,mysql主从已基本完成


以下是mysql主从过程中出现的一些错误或解决方法:

mysql> show slave status\G;

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

               Slave_IO_State: Connecting tomaster

                  Master_Host: 192.168.0.140

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000048

         Read_Master_Log_Pos: 106

               Relay_Log_File:user14-relay-bin.000003

                Relay_Log_Pos: 4

       Relay_Master_Log_File: mysql-bin.000048

             Slave_IO_Running: No

           Slave_SQL_Running: Yes

              Replicate_Do_DB: test

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

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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 2013

                Last_IO_Error: error connectingto master 'repl@192.168.0.140:3306' - retry-time: 60  retries: 86400

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

查看mysqlslave错误日志

[root@user14 ~]# tail -f/data/mysql/user14.err

160227 21:58:18 [Note] Slave I/O threadexiting, read up to log 'mysql-bin.000048', position 106

160227 21:58:18 [Note] Error reading relaylog event: slave SQL thread was killed

160227 22:01:11 [Note] 'CHANGE MASTER TOexecuted'. Previous state master_host='192.168.0.140', master_port='3306',master_log_file='mysql-bin.000048', master_log_pos='106'. New statemaster_host='192.168.0.140', master_port='3306',master_log_file='mysql-bin.000048', master_log_pos='106'.

160227 22:01:33 [Note] Slave SQL threadinitialized, starting replication in log 'mysql-bin.000048' at position 106,relay log './user14-relay-bin.000001' position: 4

160227 22:01:33 [ERROR] Slave I/O: errorconnecting to master 'repl@192.168.0.140:3306' - retry-time: 60  retries: 86400, Error_code: 2013

160227 22:01:41 [Note] Slave I/O threadkilled while connecting to master

160227 22:01:41 [Note] Slave I/O threadexiting, read up to log 'mysql-bin.000048', position 106

160227 22:01:41 [Note] Error reading relaylog event: slave SQL thread was killed

160227 22:01:47 [Note] Slave SQL threadinitialized, starting replication in log 'mysql-bin.000048' at position 106,relay log './user14-relay-bin.000001' position: 4

160227 22:01:47 [ERROR] Slave I/O: errorconnecting to master 'repl@192.168.0.140:3306' - retry-time: 60  retries: 86400, Error_code: 2013

 

Last_IO_Errno: 1593

                Last_IO_Error: Fatal error: Theslave I/O thread stops because master and slave have equal MySQL server ids;these ids must be different for replication to work (or the--replicate-same-server-id option must be used on slave but this does notalways make sense; please check the manual before using it).

配置完Error_code: 2013从没重启mysql服务,Last_IO_Errno: 1593是与主的id号冲突

 

[root@directory ~]# mysqldump -S/tmp/mysql.sock -p123456  mysql >aaa.sql

-- Warning: Skipping the data of tablemysql.event. Specify the --events option explicitly.

mysqldump-uroot -pxxxxx --events --ignore-table=mysql.events  需要备份库 > 自定义备份库名字

 

ERROR 1201 (HY000): Could not initializemaster info structure; more error messages can be found in the MySQL error log

reset slave后再重新授权




     本文转自wsw26 51CTO博客,原文链接:http://blog.51cto.com/wsw26/1752975,如需转载请自行联系原作者


网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注