澳门太阳娱乐集团官网-太阳集团太阳娱乐登录

搭建MySQL的主导、半同步、主主复制架构
分类:操作系统

实例拓扑图:

复制其最终指标是让一台服务器的数据和其它的服务器的数量保持同步,已到达多少冗余恐怕服务的载重均衡。一台主服务器能够接连多台从服务器,况兼从服务器也得以反过来作为主服务器。主从服务器能够献身差别的网络拓扑中,由于mysql的强大复制作而成效,其复制指标能够是具备的数据库,也得以是一些数据库,以致是有些数据库中的某个表打开复制。

keepalived+MySQL双主搭建进程

图片 1

MySQL辅助的二种复制方案:基于语句复制,基于行复制
依据语句复制基于行复制,那二种复制方式都以透过记录主服务器的二进制日志中任何有异常的大可能率导致数据库内数据产生更换的SQL语句到联网日志,何况在从服务器上推行以下中继日志内的SQL语句,而达标与主服务器的数码同步。差异的是,当主服务器上实行了贰个依据变量的数额并将其履新到数据库中,如now()函数,而此时根据语句复制时记下的正是该SQL语句的全数语法,而据书上说行复制正是将now()更新到数据库的数值记录下来。
举个例子:在主服务器上实践以下语句:
mysql>update user set createtime=now() where sid=16;
若是此时now()重临的值是:2011-04-16 20:46:35
依靠语句的复制就能够将其记录为:update user set createtime=now() where sid=16;
依赖行复制的就能够将其记录为:update user set createtime='二零一一-04-16 20:46:35' where sid=16;

率先要简明询问一下keepalived:

DENVISION1和D奥迪Q52布署keepalived和lvs作主从架构或主主架构,RS1和TiguanS2配备nginx搭建web站点。

进行主从复制运营的多个线程
Binlog dump线程:将二进制日志的内容发送给从服务器
I/O从线程:将收受的的数据写入到衔接日志
SQL线程:三次从当中继日志中读出一句SQL语句在从服务器上推行

Keepalived是Linux下二个轻量品级的高可用建设方案。高可用(High Avalilability,HA),其实二种差别的意思:广义来说,是指任何系列的高可用行,狭义的来说正是之主机的冗余和接管,

专心:各节点的时日必要一块(ntpdate ntp1.aliyun.com);关闭firewalld(systemctl stop firewalld.service,systemctl disable firewalld.service),设置selinux为permissive(setenforce 0);同一时间确定保证各网卡协理MULTICAST(多播)通讯。

一、主从复制:
绸缪工作:
1.修改配置文件(server_id必须要修改)
2.起家复制客商
3.开发银行从服务器的从劳动进程

它与HeartBeat 罗丝HA 完毕平等类似的职能,都足以兑现劳务还是互联网的高可用,不过又有差别,HeartBeat是多少个规范的、功效完善的高可用软件,它提供了HA 软件钻探所需的基本功能,比方:心跳检查测量检验、能源接管,检查评定集群中的服务,在集群节点转移分享IP地址的全数者等等。HeartBeat作用庞大,不过配置和利用相对相比费心,

因此命令ifconfig能够查看见是不是展开了MULTICAST:

规划:
Master:IP地址:172.16.4.11    版本:mysql-5.5.20
Slave:IP地址:172.16.4.12    版本:mysql-5.5.20
那边需注意,mysql复制超越53%皆今后向包容,所以,从服务器的本子必须要超越或等于主服务器的版本。
1、Master
修改配置文件,将其设为mysql主服务器
#vim /etc/my.cnf
server_id=11                #修改server_id=11
log_bin=mysql-bin            #展开二进制日志
sync_binlog=1               #另外一个政工提交之后就随即写入到磁盘中的二进制文件
innodb_flush_logs_at_trx_commit=1       #其他一个东西提交将来就应声写入到磁盘中的日志文件

与HeartBeat相比较,Keepalived重若是经过设想路由冗余来促成高可用成效,就算它从未HeartBeat作用庞大,可是Keepalived布署和采取十分的粗略,全部配置只要求七个计划文件就能够以成功。

       图片 2

封存退出
#service mysql reload             #再也载入mysql的布署文件

keepalived的行事规律如下:

keepalived的着力架构

2、Master上创制顾客,授予复制权限
mysql>grant replication client,replication slave on *.* to repl@172.16.4.12 identified by '135246';
mysql>flush privileges;

Keepalived专门的学业在TCP/IP 参谋模型的 三层、四层、五层,也正是个别为:互连网层,

搭建RS1:

[root@RS1 ~]# yum -y install nginx   #安装nginx
[root@RS1 ~]# vim /usr/share/nginx/html/index.html   #修改主页
    <h1> 192.168.4.118 RS1 server </h1>
[root@RS1 ~]# systemctl start nginx.service   #启动nginx服务
[root@RS1 ~]# vim RS.sh   #配置lvs-dr的脚本文件
    #!/bin/bash
    #
    vip=192.168.4.120
    mask=255.255.255.255
    case $1 in
    start)
        echo 1 > /proc/sys/net/ipv4/conf/all/arp_ignore
        echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
        echo 2 > /proc/sys/net/ipv4/conf/all/arp_announce
        echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce
        ifconfig lo:0 $vip netmask $mask broadcast $vip up
        route add -host $vip dev lo:0
        ;;
    stop)
        ifconfig lo:0 down
        echo 0 > /proc/sys/net/ipv4/conf/all/arp_ignore
        echo 0 > /proc/sys/net/ipv4/conf/lo/arp_ignore
        echo 0 > /proc/sys/net/ipv4/conf/all/arp_announce
        echo 0 > /proc/sys/net/ipv4/conf/lo/arp_announce
        ;;
    *) 
        echo "Usage $(basename $0) start|stop"
        exit 1
        ;;
    esac
[root@RS1 ~]# bash RS.sh start

3、Slave
修改配置文件,将其安装为三个mysql从服务器
#vim /etc/my.cnf
server_id=12                #修改server_id=12
#log-bin                #讲授掉log-bin,从服务器无需二进制日志,因而将其倒闭
relay-log=mysql-relay                #概念中继日志名,开启从服务器中继日志
relay-log-index=mysql-relay.index     #概念中继日志索引名,开启从服务器中继索引
read_only=1                    #设定从服务器只可以进行读操作,无法举办写操作

传输层和应用层,根据TCP、IP参数模型隔层所能达成的效率,Keepalived运行机制如下:

参照奥德赛S1的安插搭建奥迪Q5S2。

封存退出
#service mysql reload             #再一次载入mysql的安插文件

在互连网层:大家领会运转那4个根本的磋商,互联互连网IP合同,互联互连网可决定报中华全国文艺界抗击敌人组织议ICMP、地址转变左券ARP、反向地址调换契约RARP,在网络层Keepalived在互连网层选用最布满的劳作措施是通过ICMP协议向服务器集群中的每一个节点发送一个ICMP数据包(有一点点类似与Ping的功效),借使某些节点未有回到响应数据包,那么以为该节点发生了故障,Keepalived将告诉以此节点失效,并从服务器集群中除去故障节点。

搭建DR1:

[root@DR1 ~]# yum -y install ipvsadm keepalived   #安装ipvsadm和keepalived
[root@DR1 ~]# vim /etc/keepalived/keepalived.conf   #修改keepalived.conf配置文件
    global_defs {
       notification_email {
         root@localhost
       }
       notification_email_from keepalived@localhost
       smtp_server 127.0.0.1
       smtp_connect_timeout 30
       router_id 192.168.4.116
       vrrp_skip_check_adv_addr
       vrrp_mcast_group4 224.0.0.10
    }

    vrrp_instance VIP_1 {
        state MASTER
        interface eno16777736
        virtual_router_id 1
        priority 100
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass %&hhjj99
        }
        virtual_ipaddress {
          192.168.4.120/24 dev eno16777736 label eno16777736:0
        }
    }

    virtual_server 192.168.4.120 80 {
        delay_loop 6
        lb_algo rr
        lb_kind DR
        protocol TCP

        real_server 192.168.4.118 80 {
            weight 1
            HTTP_GET {
                url {
                  path /index.html
                  status_code 200
                }
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }
        }
        real_server 192.168.4.119 80 {
            weight 1
            HTTP_GET {
                url {
                  path /index.html
                  status_code 200
                }
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }
         }
    }
[root@DR1 ~]# systemctl start keepalived
[root@DR1 ~]# ifconfig
    eno16777736: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
            inet 192.168.4.116  netmask 255.255.255.0  broadcast 192.168.4.255
            inet6 fe80::20c:29ff:fe93:270f  prefixlen 64  scopeid 0x20<link>
            ether 00:0c:29:93:27:0f  txqueuelen 1000  (Ethernet)
            RX packets 14604  bytes 1376647 (1.3 MiB)
            RX errors 0  dropped 0  overruns 0  frame 0
            TX packets 6722  bytes 653961 (638.6 KiB)
            TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

    eno16777736:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
            inet 192.168.4.120  netmask 255.255.255.0  broadcast 0.0.0.0
            ether 00:0c:29:93:27:0f  txqueuelen 1000  (Ethernet)
[root@DR1 ~]# 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.4.120:80 rr
      -> 192.168.4.118:80             Route   1      0          0         
      -> 192.168.4.119:80             Route   1      0          0

4、验证Slave上连片日志以及server_id是或不是均生效
mysql>show variables like 'relay%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| relay_log             | relay-bin       |
| relay_log_index       | relay-bin.index |
| relay_log_info_file   | relay-log.info  |
| relay_log_purge       | ON              |
| relay_log_recovery    | OFF             |
| relay_log_space_limit | 0               |
+-----------------------+-----------------+
mysql>show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 12    |
+---------------+-------+

在传输层:提供了七个至关心重视要的合计:传输调节左券TCP和顾客数量左券UDP,传输调控公约TCP能够提供保障的数额输劳、IP地址和端口,代表TCP的贰个连接端,要取得TCP服务,须要在发送机的一个端口和接收机的八个端口上建构连接,而Keepalived在传输层里选取了TCP左券的端口连接和扫描技艺来剖断集群节点的端口是或不是常常,举个例子对于广大的WEB服务器80端口。恐怕SSH服务22端口,Keepalived一旦在传输层探测到那么些端口号没有数量响应和数码重返,就感觉这么些端口爆发极度,然后强制将这个端口所对应的节点从服务器集群中剔除掉。

DRubicon2的搭建基本同DQashqai1,首要修改一下布局文件中/etc/keepalived/keepalived.conf的state和priority:state BACKUP、priority 90. 同不常间大家开掘作为backup的DR2未有启用eno16777736:0的网口:

图片 3

5、运营从服务器的从服务进程
场景一、尽管主服务器和从服务器都以新创制的,并从未新扩张其余数据,则试行以下命令:
mysql>change master to
master_host='172.16.4.11',
master_user='repl',
master_password='135246';
mysql>show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 25520
Relay_Log_Space: 2565465
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
mysql>start slave;
mysql>show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
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: 360
Relay_Log_Space: 300
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11

在应用层:能够运维FTP,TELNET,SMTP,DNS等各个分歧门类的高层磋商,Keepalived的运作情势也更为周详化和复杂化,客户能够因而自定义Keepalived专门的学问议程,举例:能够通过编写程序可能脚本来运维Keepalived,而Keepalived将基于客商的设定参数检查实验各类程序依旧服务是还是不是允许符合规律,倘使Keepalived的检查实验结果和用户设定的不均等时,Keepalived将把相应的服务器从服务器集群中去除

客商端进行测量试验:

[root@client ~]# for i in {1..20};do curl http://192.168.4.120;done   #客户端正常访问
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>

[root@DR1 ~]# systemctl stop keepalived.service   #关闭DR1的keepalived服务

[root@DR2 ~]# systemctl status keepalived.service   #观察DR2,可以看到DR2已经进入MASTER状态
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2018-09-04 11:33:04 CST; 7min ago
  Process: 12983 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 12985 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─12985 /usr/sbin/keepalived -D
           ├─12988 /usr/sbin/keepalived -D
           └─12989 /usr/sbin/keepalived -D

Sep 04 11:37:41 happiness Keepalived_healthcheckers[12988]: SMTP alert successfully sent.
Sep 04 11:40:22 happiness Keepalived_vrrp[12989]: VRRP_Instance(VIP_1) Transition to MASTER STATE
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: VRRP_Instance(VIP_1) Entering MASTER STATE
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: VRRP_Instance(VIP_1) setting protocol VIPs.
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: Sending gratuitous ARP on eno16777736 for 192.168.4.120
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: VRRP_Instance(VIP_1) Sending/queueing gratuitous ARPs on eno16777736 for 192.168.4.120
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: Sending gratuitous ARP on eno16777736 for 192.168.4.120
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: Sending gratuitous ARP on eno16777736 for 192.168.4.120
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: Sending gratuitous ARP on eno16777736 for 192.168.4.120
Sep 04 11:40:23 happiness Keepalived_vrrp[12989]: Sending gratuitous ARP on eno16777736 for 192.168.4.120

[root@client ~]# for i in {1..20};do curl http://192.168.4.120;done   #可以看到客户端正常访问
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>
<h1> 192.168.4.119 RS2 server</h1>
<h1> 192.168.4.118 RS1 server </h1>

场景二、假诺主服务器已经运维过一段了,从服务器是新加上的,则须求将主服务器在此之前的数量导入到从服务器中:
Master:
#mysqldump -uroot -hlocalhost -p123456 --all-databases --lock-all-tables --flush-logs --master-data=2 > /backup/alldatabase.sql
mysql>flush tables with read lock;
mysql>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      360 |              |                  |
+------------------+----------+--------------+------------------+
mysql>unlock tables;
#scp /backup/alldatabase.sql 172.16.4.12:/tmp

看一下中央条件

keepalived的主主框架结构

Slave:
#mysql -uroot -p123456 < /tmp/alldatabase.sql
mysql>change master to
master_host='172.16.4.11',
master_user='repl',
master_password='135246',
master_log_file='mysql-bin.000004',
master_log_pos=360;
mysql>show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 360
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: No
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: 360
Relay_Log_Space: 107
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
mysql>start slave;

server1:MySQL5.7.14+keepalived1.2+172.16.16.34
server2:MySQL5.7.14+keepalived1.2+172.16.16.35
VIP:172.16.16.20

 修改RAV4S1和凯雷德S2,加多新的VIP:

[root@RS1 ~]# cp RS.sh RS_bak.sh
[root@RS1 ~]# vim RS_bak.sh   #添加新的VIP
    #!/bin/bash
    #
    vip=192.168.4.121
    mask=255.255.255.255
    case $1 in
    start)
        echo 1 > /proc/sys/net/ipv4/conf/all/arp_ignore
        echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
        echo 2 > /proc/sys/net/ipv4/conf/all/arp_announce
        echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce
        ifconfig lo:1 $vip netmask $mask broadcast $vip up
        route add -host $vip dev lo:1
        ;;
    stop)
        ifconfig lo:1 down
        echo 0 > /proc/sys/net/ipv4/conf/all/arp_ignore
        echo 0 > /proc/sys/net/ipv4/conf/lo/arp_ignore
        echo 0 > /proc/sys/net/ipv4/conf/all/arp_announce
        echo 0 > /proc/sys/net/ipv4/conf/lo/arp_announce
        ;;
    *)
        echo "Usage $(basename $0) start|stop"
        exit 1
        ;;
    esac
[root@RS1 ~]# bash RS_bak.sh start
[root@RS1 ~]# ifconfig
    ...
    lo:0: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
            inet 192.168.4.120  netmask 255.255.255.255
            loop  txqueuelen 0  (Local Loopback)

    lo:1: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
            inet 192.168.4.121  netmask 255.255.255.255
            loop  txqueuelen 0  (Local Loopback) 
[root@RS1 ~]# scp RS_bak.sh root@192.168.4.119:~
root@192.168.4.119's password: 
RS_bak.sh                100%  693     0.7KB/s   00:00

[root@RS2 ~]# bash RS_bak.sh   #直接运行脚本添加新的VIP 
[root@RS2 ~]# ifconfig
    ...
    lo:0: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
            inet 192.168.4.120  netmask 255.255.255.255
            loop  txqueuelen 0  (Local Loopback)

    lo:1: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
            inet 192.168.4.121  netmask 255.255.255.255
            loop  txqueuelen 0  (Local Loopback)

mysql>show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 360
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
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: 360
Relay_Log_Space: 300
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11

我们两台机器是搭建的MySQL双主,大家常常只会因而VIP对MySQL实行读写,大家要达成的是,当VIP所在的主机的MySQLDOWN掉未来,VIP可以切换来其它一台机械上还要接二连三提供服务。

修改DR1和DR2:

[root@DR1 ~]# vim /etc/keepalived/keepalived.conf   #修改DR1的配置文件,添加新的实例,配置服务器组
    ...
    vrrp_instance VIP_2 {
        state BACKUP
        interface eno16777736
        virtual_router_id 2
        priority 90
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass UU**99^^
        }
        virtual_ipaddress {
            192.168.4.121/24 dev eno16777736 label eno16777736:1
        }
    }

    virtual_server_group ngxsrvs {
        192.168.4.120 80
        192.168.4.121 80
    }
    virtual_server group ngxsrvs {
        ...
    }
[root@DR1 ~]# systemctl restart keepalived.service   #重启服务
[root@DR1 ~]# ifconfig   #此时可以看到eno16777736:1,因为DR2还未配置
    eno16777736: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
            inet 192.168.4.116  netmask 255.255.255.0  broadcast 192.168.4.255
            inet6 fe80::20c:29ff:fe93:270f  prefixlen 64  scopeid 0x20<link>
            ether 00:0c:29:93:27:0f  txqueuelen 1000  (Ethernet)
            RX packets 54318  bytes 5480463 (5.2 MiB)
            RX errors 0  dropped 0  overruns 0  frame 0
            TX packets 38301  bytes 3274990 (3.1 MiB)
            TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

    eno16777736:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
            inet 192.168.4.120  netmask 255.255.255.0  broadcast 0.0.0.0
            ether 00:0c:29:93:27:0f  txqueuelen 1000  (Ethernet)

    eno16777736:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
            inet 192.168.4.121  netmask 255.255.255.0  broadcast 0.0.0.0
            ether 00:0c:29:93:27:0f  txqueuelen 1000  (Ethernet)
[root@DR1 ~]# 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.4.120:80 rr
      -> 192.168.4.118:80             Route   1      0          0         
      -> 192.168.4.119:80             Route   1      0          0         
    TCP  192.168.4.121:80 rr
      -> 192.168.4.118:80             Route   1      0          0         
      -> 192.168.4.119:80             Route   1      0          0

[root@DR2 ~]# vim /etc/keepalived/keepalived.conf   #修改DR2的配置文件,添加实例,配置服务器组
    ...
    vrrp_instance VIP_2 {
        state MASTER
        interface eno16777736
        virtual_router_id 2
        priority 100
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass UU**99^^
        }
        virtual_ipaddress {
            192.168.4.121/24 dev eno16777736 label eno16777736:1
        }
    }

    virtual_server_group ngxsrvs {
        192.168.4.120 80
        192.168.4.121 80
    }
    virtual_server group ngxsrvs {
        ...
    }
[root@DR2 ~]# systemctl restart keepalived.service   #重启服务
[root@DR2 ~]# ifconfig
    eno16777736: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
            inet 192.168.4.117  netmask 255.255.255.0  broadcast 192.168.4.255
            inet6 fe80::20c:29ff:fe3d:a31b  prefixlen 64  scopeid 0x20<link>
            ether 00:0c:29:3d:a3:1b  txqueuelen 1000  (Ethernet)
            RX packets 67943  bytes 6314537 (6.0 MiB)
            RX errors 0  dropped 0  overruns 0  frame 0
            TX packets 23250  bytes 2153847 (2.0 MiB)
            TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

    eno16777736:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
            inet 192.168.4.121  netmask 255.255.255.0  broadcast 0.0.0.0
            ether 00:0c:29:3d:a3:1b  txqueuelen 1000  (Ethernet)
[root@DR2 ~]# 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.4.120:80 rr
      -> 192.168.4.118:80             Route   1      0          0         
      -> 192.168.4.119:80             Route   1      0          0         
    TCP  192.168.4.121:80 rr
      -> 192.168.4.118:80             Route   1      0          0         
      -> 192.168.4.119:80             Route   1      0          0 

证实MySQL的主从复制架构成功

大家若是MySQL双主已经搭建成功了,借使还不会的话,能够看作者的此前博客,MySQL二进制安装双主结构:

顾客端测试:

[root@client ~]# for i in {1..20};do curl http://192.168.4.120;done
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
[root@client ~]# for i in {1..20};do curl http://192.168.4.121;done
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>
    <h1> 192.168.4.119 RS2 server</h1>
    <h1> 192.168.4.118 RS1 server </h1>

 

注1:MySQL的复制能够依据有些数据库或库中的默写表实行复制,要想达成该功用,只需在其安插文件中加上以下配置:
Master:
binlog-do-db=db_name        只复制db_name数据库
binlog-ignore-db=db_name    不复制db_name数据库

下边起首搭建大家的情形

注2:在Master上定义过滤法则,意味着,任何不涉及到该数据库相关的写操作都不会被记录到二进制日志中,由此不建议在Master上定义过滤法规,并且不建议binlog-do-db与binlog-ignore-db同期定义。

1:安装以及轻巧安顿keepalived

Slave:
replicate_do_db=db_name            只复制db_name数据库
replicate_ignore_db=db_name        不复制db_name数据库
replicate_do_table=tb_name        只复制tb_name表
replicate_ignore_table=tb_name        只复制tb_name表
replicate_wild_do_table=test%        只复制以test为初步何况前边跟上放肆字符的名字的表
replicate_wild_ignore_table=test_    只复制以test为发端何况前面跟上任性单个字符的名字的表

yum install -y keepalived

注3:假如急需钦赐三个db或table时,则只需将命令数次写入

 

**

二、半联合签名复制**

鉴于Mysql的复制都以基于异步实行的,在特别景况下不可能保险数据的中标复制,由此在mysql 5.5自此采取了来自google补丁,能够将Mysql的复制实现半一只方式。所以须求为主服务器加载对应的插件。在Mysql的安装目录下的lib/plugin/目录中具有相应的插件semisync_master.so,semisync_slave.so

在Master和Slave的mysql命令行运营如下命令:

Master:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled = 1;
mysql> set global rpl_semi_sync_master_timeout = 1000;
mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

Slave:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> stop slave;
mysql> start slave;
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

反省半联袂是还是不是见效:
Master:
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
注脚半联合签名成功。

让半联机成效在MySQL每回运行都活动生效,在Master和Slave的my.cnf中编辑:
Master:
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000     #1秒

Slave:
[mysqld]
rpl_semi_sync_slave_enabled=1

也可因此设置全局变量的点子来设置是不是运营半六只插件:
Master:
mysql> set global rpl_semi_sync_master_enabled=1
裁撤加载插件
mysql> uninstall plugin rpl_semi_sync_master;

Slave:
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> uninstall plugin rpl_semi_sync_slave;

安装以往能够查阅一下设置了那一个文件:

**

三、主主复制架构 1、在两台服务器上各自构建贰个存有复制权限的客户; Master:*
mysql>grant replication client,replication slave on
.* to repl@172.16.4.12 identified by '135246';
mysql>flush privileges;

Slave:
mysql>grant replication client,replication slave on *.* to repl@172.16.4.11 identified by '135246';
mysql>flush privileges;

2、修改配置文件:
Master:
[mysqld]
server-id = 11
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
relay-log=mysql-relay
relay-log-index=mysql-relay.index

Slave:
[mysqld]
server-id = 12
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
relay-log=mysql-relay
relay-log-index=mysql-relay.index

3、假若那时两台服务器均为新确立,且无任何写入操作,各服务器只需记下当前协调二进制日志文件及事件地方,以之作为别的的服务器复制开首地方就能够
Master:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      360 |              |                  |
+------------------+----------+--------------+------------------+

Slave:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+

4、各服务器接下去内定对另一台服务器为团结的主服务器就可以:
Master:
mysql>change master to
master_host='172.16.4.12',
master_user='repl',
master_password='135246',
master_log_file='mysql-bin.000005',
master_log_pos=107;

Slave:
mysql>change master to
master_host='172.16.4.11',
master_user='repl',
master_password='135246',
master_log_file='mysql-bin.000004',
master_log_pos=360;

5、运维从服务器线程:
Master:
mysql>start slave;

Slave:
mysql>start slave;

到此主主架构已经打响!

[root@localhost maxiangqian]# rpm -ql keepalived

 

下一场配置一下最焦点的布局文件:

[root@localhost maxiangqian]# vi /etc/keepalived/keepalived.conf
vrrp_instance VI_20 {
state BACKUP
nopreempt
interface eth0
virtual_router_id 20
priority 100
advert_int 5
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.16.20
}
}

 

此布局文件正是最简便易行的配备设想IP的公文了,接下去大家在172.16.16.34上运维keepalived

/etc/init.d/keepalived start

 

翻开机器IP:

[root@localhost maxiangqian]# ip addr |grep 172.16
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
inet 172.16.16.34/24 brd 172.16.16.255 scope global eth0
inet 172.16.16.20/32 scope global eth0

 

可以见见,VIP已经加多到了server1上,接下去大家在server2安装运转keepalived,运行,全部手续都平等,只但是server2的keepalived作为备机,并不集会场全体VIP。

我们在server1上执行

/etc/init.d/keepalived stop

 

实施今后在ip addr看,VIP现在早就是server2持有了。所以最最中央的功力是早已到位了,但是离大家双主自动切换还是有一点都不小的歧异的,上边大家三番四次去对keepalived的文书举办改建,直到到达大家知足截止:

上边大家修改配置文件,达到大家供给的指标(下边以server2的kp文件为例,server1的和server2同样,只要替换掉相应IP为34就能够了):

vrrp_script checkmysql {
script "/etc/keepalived/checkmysql.sh"
interval 10 #监控脚本,每十秒运行一次
}

vrrp_instance VI_20 {
state BACKUP #状态只有MASTER和BACKUP两种,并且要大写,MASTER为工作状态,BACKUP是备用状态
nopreempt #非抢占模式
interface eth0
virtual_router_id 20
priority 100 #权重,同一个vrrp_instance的MASTER优先级必须比BACKUP高。我们使用非抢占模式,设置相同即可
advert_int 5 #MASTER 与BACKUP 负载均衡器之间同步检查的时间间隔,单位为秒
authentication {
auth_type PASS #验证authentication。包含验证类型和验证密码。类型主要有PASS、AH 两种,通常使用的类型为PASS
auth_pass 1111
}
track_script { #执行定义的监控脚本
checkmysql
}
virtual_ipaddress {
172.16.16.20/24
}
}

 

看一下checkmysql.sh这么些本子:

#!/bin/sh
#isok=$(sed -n '2p' /etc/keepalived/result.txt)
isok=$(/usr/local/mysql/bin/mysql -uroot -p123456 -e 'select 1' |sed -n '2p')
function error_query(){
service keepalived stop
echo "172.16.16.34 mysql down, keepalived 切换" | mail -s "34MySQL+keepalived通知" ma.xiangqian@sf-express.com
}
echo "$isok"
if [ "$isok" != "1" ]
then
#echo 'diaoyong error'
error_query
fi

 

近些日子咱们施行以下语句,从新load以下keepalived的配置文件:

/etc/init.d/keepalived reload
server1和server2都要执行重新load一下新的配置文件,下面我们测试一下当server1 MySQL DOWN掉的话会发生什么:
server1:shutdown MySQL
server1和server2:tail -f /var/log/messages

server1信息:

May 15 15:35:34 localhost Keepalived_healthcheckers[22987]: TCP connection to [172.16.16.34]:3306 failed !!!
May 15 15:35:34 localhost Keepalived_healthcheckers[22987]: Removing service [172.16.16.34]:3306 from VS [172.16.16.20]:3306
May 15 15:35:34 localhost Keepalived_healthcheckers[22987]: IPVS : Virtual service [172.16.16.20]:3306 illegal timeout.
May 15 15:35:34 localhost Keepalived_healthcheckers[22987]: Executing [/etc/keepalived/shutdown.sh  #检测到服务down后执行的脚本] for service [172.16.16.34]:3306 in VS [172.16.16.20]:3306
May 15 15:35:34 localhost Keepalived_healthcheckers[22987]: Lost quorum 1-0=1 > 0 for VS [172.16.16.20]:3306
May 15 15:36:04 localhost Keepalived_vrrp[22988]: VRRP_Script(checkmysql) failed
May 15 15:36:06 localhost Keepalived_vrrp[22988]: VRRP_Instance(VI_20) Entering FAULT STATE
May 15 15:36:06 localhost Keepalived_vrrp[22988]: VRRP_Instance(VI_20) removing protocol VIPs.
May 15 15:36:06 localhost Keepalived_vrrp[22988]: VRRP_Instance(VI_20) Now in FAULT state
May 15 15:36:06 localhost Keepalived_healthcheckers[22987]: Netlink reflector reports IP 172.16.16.20 removed

server2信息:

May 15 15:24:58 mxqmongodb2 Keepalived_healthcheckers[3093]: IPVS : Virtual service [172.16.16.20]:3306 illegal timeout.
May 15 15:24:58 mxqmongodb2 Keepalived_healthcheckers[3093]: Using LinkWatch kernel netlink reflector...
May 15 15:24:58 mxqmongodb2 Keepalived_healthcheckers[3093]: Activating healthchecker for service [172.16.16.35]:3306
May 15 15:24:58 mxqmongodb2 Keepalived_vrrp[3094]: VRRP_Script(checkmysql) succeeded
May 15 15:36:04 mxqmongodb2 Keepalived_vrrp[3094]: VRRP_Instance(VI_20) Transition to MASTER STATE
May 15 15:36:09 mxqmongodb2 Keepalived_vrrp[3094]: VRRP_Instance(VI_20) Entering MASTER STATE
May 15 15:36:09 mxqmongodb2 Keepalived_vrrp[3094]: VRRP_Instance(VI_20) setting protocol VIPs.
May 15 15:36:09 mxqmongodb2 Keepalived_vrrp[3094]: VRRP_Instance(VI_20) Sending gratuitous ARPs on eth0 for 172.16.16.20
May 15 15:36:09 mxqmongodb2 Keepalived_healthcheckers[3093]: Netlink reflector reports IP 172.16.16.20 added
May 15 15:36:14 mxqmongodb2 Keepalived_vrrp[3094]: VRRP_Instance(VI_20) Sending gratuitous ARPs on eth0 for 172.16.16.20

我们能够见到虚IP链接已经切换了,我们从顾客端四个时刻点推行MySQL的操作也足以很显眼看出切换:

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 343306 |
+-------------+
1 row in set

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 353306 |
+-------------+
1 row in set

中等VIP的切换是不会影响到客商端的操作的,不过在切换进程是有那么一段时间是不能够访谈的。

  

keepalived原理仿照效法:

 

本文由澳门太阳娱乐集团官网发布于操作系统,转载请注明出处:搭建MySQL的主导、半同步、主主复制架构

上一篇:没有了 下一篇:【澳门太阳娱乐集团官网】大切诺基HEL6下搭建
猜你喜欢
热门排行
精彩图文