您的位置:新葡亰496net > 网络数据库 > 新葡亰496net名爵RAV4的读写分离,MySQL读写分离最

新葡亰496net名爵RAV4的读写分离,MySQL读写分离最

发布时间:2019-07-28 18:47编辑:网络数据库浏览(127)

    1:现成条件
    机器 MySQL 环境
    172.16.128.240 MGR NODE1 MGR
    172.16.128.241 MGR NODE2 MGR
    172.16.128.242 MGR NODE3 MGR
    172.16.128.239 VIP keepalived

    条件介绍,VIP 172.16.128.239在172.16.128.240上,大家是遵照172.16.128.240和172.16.128.241做的keepalived。MGR为多主方式。大家思虑选用中间件能够落到实处MG中华V的读写分离。中间件选型为Atlas,Atlas是360组织开源的一套基于MySQL-Proxy基础之上的代办,修改了MySQL-Proxy的片段BUG,并且开始展览了优化。

     

    一、境况希图

    新葡亰496net 1

    MHA算是规范相比成熟的MySQL高可用消除方案,在MySQL故障切换进度中,MHA能成功自动达成数据库的故障切换操作,并且在张开故障切换的长河中,MHA能在最大程度上保证数据的一致性,以实现确实意义上的高可用。软件主要有MHA Manager(管理节点)和MHA Node(数据节点)两有的构成,在MHA自动故障切换过程中,MHA试图从宕机的主服务器上保留二进制日志,最大程度的保证数据的不放弃,但这并不总是平价的。比方,假使主服务器硬件故障或无法通过ssh访谈,MHA无法保存二进制日志,只实行故障转移而错过了新式的数目。使用MySQL 5.5的半六只复制,能够大大减弱数据遗失的危机。MHA能够与半一并复制结合起来。即使独有四个slave已经吸收了新星的二进制日志,MHA能够将流行的二进制日志应用于任何兼具的slave服务器上,因而能够有限支撑具有节点的数目一致性。

    2:安装Atlas
    [root@YC-ss1 tmp]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 
    Preparing...                          ################################# [100%]
    Updating / installing...
       1:Atlas-2.2.1-1                    ################################# [100%]
    [root@YC-ss1 mysql-proxy]# pwd
    /usr/local/mysql-proxy
    [root@YC-ss1 mysql-proxy]# ls
    bin  conf  lib  log
    

     

    我们看一下,安装完Atlas 以往会生成多个文件夹。首先大家要陈设密码的加密:

    [root@YC-ss1 bin]# ./encrypt 123456 
    /iZxz 0GRoA=
    

     

    我的root@%账号密码正是123456,比较轻巧。大家先记下那些加密字符串,等会要在配置文件中安排。配置配置文件

    [root@YC-ss1 conf]# cp test.cnf atlas.cnf
    [root@YC-ss1 conf]# cat atlas.cnf 
    [mysql-proxy]
    
    
    #带#号的为非必需的配置项目
    
    #管理接口的用户名
    admin-username = user
    
    #管理接口的密码
    admin-password = pwd
    
    #Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
    proxy-backend-addresses = 172.16.128.239:3306
    
    #Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
    proxy-read-only-backend-addresses = 172.16.128.240:3306,172.16.128.241:3306,172.16.128.242:3306
    
    #用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
    pwds = root:/iZxz 0GRoA=
    
    #设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
    daemon = true
    
    #设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
    keepalive = true
    
    #工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
    event-threads = 16
    
    #日志级别,分为message、warning、critical、error、debug五个级别
    log-level = message
    
    #日志存放的路径
    log-path = /usr/local/mysql-proxy/log
    
    #SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
    #sql-log = OFF
    
    #慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。
    #sql-log-slow = 10
    
    #实例名称,用于同一台机器上多个Atlas实例间的区分
    #instance = test
    
    #Atlas监听的工作接口IP和端口
    proxy-address = 0.0.0.0:6002
    
    #Atlas监听的管理接口IP和端口
    admin-address = 0.0.0.0:6003
    
    #分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
    #tables = person.mt.id.3
    
    #默认字符集,设置该项后客户端不再需要执行SET NAMES语句
    #charset = utf8
    
    #允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
    #client-ips = 127.0.0.1, 192.168.1
    
    #Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
    #lvs-ips = 192.168.1.1
    
     
    

     

    启动Atlas

    [root@YC-ss1 bin]# ./mysql-proxyd  atlas start
    OK: MySQL-Proxy of atlas is started
    

     

    OK,今后大家的Atlas已经运行了。大家登入查看一下音信:

    [root@YC-ss1 conf]#  mysql -h127.0.0.1 -P6003 -uuser -ppwd
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 1
    Server version: 5.0.99-agent-admin
    
    Copyright (c) 2000, 2018, 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.
    (user@127.0.0.1) [(none)]> SELECT * FROM help
        -> ;
     ---------------------------- --------------------------------------------------------- 
    | command                    | description                                             |
     ---------------------------- --------------------------------------------------------- 
    | SELECT * FROM help         | shows this help                                         |
    | SELECT * FROM backends     | lists the backends and their state                      |
    | SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
    | SET ONLINE $backend_id     | online backend server, ...                              |
    | ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
    | ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
    | REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
    | SELECT * FROM clients      | lists the clients                                       |
    | ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
    | REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
    | SELECT * FROM pwds         | lists the pwds                                          |
    | ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
    | ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
    | REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
    | SAVE CONFIG                | save the backends to config file                        |
    | SELECT VERSION             | display the version of Atlas                            |
     ---------------------------- --------------------------------------------------------- 
    16 rows in set (0.00 sec)
    
    (user@127.0.0.1) [(none)]>  SELECT * FROM backends;
     ------------- --------------------- ------- ------ 
    | backend_ndx | address             | state | type |
     ------------- --------------------- ------- ------ 
    |           1 | 172.16.128.239:3306 | up    | rw   |
    |           2 | 172.16.128.240:3306 | up    | ro   |
    |           3 | 172.16.128.241:3306 | up    | ro   |
    |           4 | 172.16.128.242:3306 | up    | ro   |
     ------------- --------------------- ------- ------ 
    4 rows in set (0.00 sec)
    

     

    接下来利用服务端口访谈一下:

    [root@YC-ss1 ~]# mysql -h127.0.0.1 -P6002 -uroot -p123456
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 4
    Server version: 5.0.81-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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.
    
    (root@127.0.0.1) [(none)]> show databases;
     -------------------- 
    | Database           |
     -------------------- 
    | information_schema |
    | mxqteset           |
    | mysql              |
    | performance_schema |
    | sys                |
     -------------------- 
    5 rows in set (0.00 sec)
    
    (root@127.0.0.1) [(none)]> select @@server_id;
     ------------- 
    | @@server_id |
     ------------- 
    |  1282403306 |
     ------------- 
    1 row in set (0.00 sec)
    
    (root@127.0.0.1) [(none)]> select @@server_id;
     ------------- 
    | @@server_id |
     ------------- 
    |  1282413306 |
     ------------- 
    1 row in set (0.00 sec)
    
    (root@127.0.0.1) [(none)]> select @@server_id;
     ------------- 
    | @@server_id |
     ------------- 
    |  1282423306 |
     ------------- 
    1 row in set (0.00 sec)
    

     

    可以观望大家查询了二回。已经路由到了名爵福特Explorer的多少个节点上了。读已经是不曾难点的。写的话都会透过VIP:172.16.128.239来写。而且大家的VIP是会自行故障转移,也正是说有限协理是可写的。那样基本上就兑现的读写分离。倘使我们不想让240那台机器发出读,就足以把优先级配置的低一些,优先路由倒别的的机器。

    目标是尝试altas的读写分离,现存一套搭建好做测量检验的MGRubicon(单主),于是就腿搓绳,在M奇霉素urano基础上搭建altas。

    1.mysql-db01

    1 #系统版本
    2 [root@mysql-db01 ~]# cat /etc/redhat-release 
    3 CentOS release 6.7 (Final)
    4 #内核版本
    5 [root@mysql-db01 ~]# uname -r
    6 2.6.32-573.el6.x86_64
    7 #IP地址
    8 [root@mysql-db01 ~]# hostname -I
    9 10.0.0.51
    

    图表来源互联网

    日前MHA首要协理一主多从的架构,要搭建MHA,须求贰个复制集群中必须至少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,别的一台充当从库,因为至少需求三台服务器。

    3:压力测量试验

    小编们运用tpcc-mysql实行测量检验。先举行设置,下载地址:

    [root@YC-ss1 tpccmysql]# gunzip tpcc-mysql-src.tgz
    [root@YC-ss1 tpccmysql]#  tar xf tpcc-mysql-src.tar
    [root@YC-ss1 tpccmysql]# cd src/
    -bash: cd: src/: No such file or directory
    [root@YC-ss1 tpccmysql]# ls
    tpcc-mysql  tpcc-mysql-src.tar
    [root@YC-ss1 tpccmysql]# cd tpcc-mysql
    [root@YC-ss1 tpcc-mysql]# ls
    add_fkey_idx.sql  count.sql  create_table.sql  drop_cons.sql  load.sh  README  schema2  scripts  src
    [root@YC-ss1 tpcc-mysql]# cd src/
    [root@YC-ss1 src]# make
    cc -w -O2 -g -I. `mysql_config --include`  -c load.c
    cc -w -O2 -g -I. `mysql_config --include`  -c support.c
    cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
    cc -w -O2 -g -I. `mysql_config --include`  -c main.c
    cc -w -O2 -g -I. `mysql_config --include`  -c spt_proc.c
    cc -w -O2 -g -I. `mysql_config --include`  -c driver.c
    cc -w -O2 -g -I. `mysql_config --include`  -c sequence.c
    cc -w -O2 -g -I. `mysql_config --include`  -c rthist.c
    cc -w -O2 -g -I. `mysql_config --include`  -c neword.c
    cc -w -O2 -g -I. `mysql_config --include`  -c payment.c
    cc -w -O2 -g -I. `mysql_config --include`  -c ordstat.c
    cc -w -O2 -g -I. `mysql_config --include`  -c delivery.c
    cc -w -O2 -g -I. `mysql_config --include`  -c slev.c
    cc main.o spt_proc.o driver.o support.o sequence.o rthist.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
    [root@YC-ss1 src]# cd ..
    [root@YC-ss1 tpcc-mysql]# ls
    add_fkey_idx.sql  count.sql  create_table.sql  drop_cons.sql  load.sh  README  schema2  scripts  src  tpcc_load  tpcc_start
    

     

    伊始化压测数据:

    (root@127.0.0.1) [tpccmysql]> create database tpccmysql;
    Query OK, 1 row affected (0.02 sec)
    
    (root@127.0.0.1) [tpccmysql]> use tpccmysql
    Database changed
    (root@127.0.0.1) [tpccmysql]> source /tmp/create_table.sql;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.18 sec)
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    

     

    只是未有外键的话是不可能压测的,及时压测非常多也是失利的。唉,坑爹,万幸有一个TPCC去除此而外键的,这一个理应是叶大大搞的,github地址:

    作者们运用去除此而外键的TPCC举行压测,先进行设置

    [root@YC-ss1 tpccmysql]# cd tpcc-mysql-autoinc-pk-master
    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ls
    add_fkey_idx.sql  create_table-autoinc-pk.sql  load.sh  README  README.md  run_tpcc.sh  schema2  scripts  src  tpcc_load_parallel.sh
    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# cd src/
    [root@YC-ss1 src]# make
    cc -w -O2 -g -I. `mysql_config --include`  -c load.c
    cc -w -O2 -g -I. `mysql_config --include`  -c support.c
    cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
    cc -w -O2 -g -I. `mysql_config --include`  -c main.c
    cc -w -O2 -g -I. `mysql_config --include`  -c spt_proc.c
    cc -w -O2 -g -I. `mysql_config --include`  -c driver.c
    cc -w -O2 -g -I. `mysql_config --include`  -c sequence.c
    cc -w -O2 -g -I. `mysql_config --include`  -c rthist.c
    cc -w -O2 -g -I. `mysql_config --include`  -c neword.c
    cc -w -O2 -g -I. `mysql_config --include`  -c payment.c
    cc -w -O2 -g -I. `mysql_config --include`  -c ordstat.c
    cc -w -O2 -g -I. `mysql_config --include`  -c delivery.c
    cc -w -O2 -g -I. `mysql_config --include`  -c slev.c
    cc main.o spt_proc.o driver.o support.o sequence.o rthist.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
    [root@YC-ss1 src]# ls
    delivery.c  driver.c  load.c       load.o  main.o    neword.c  ordstat.c  parse_port.h  payment.o  rthist.h  sequence.c  sequence.o  slev.o      spt_proc.h  support.c  tpc.h
    delivery.o  driver.o  load.c.orig  main.c  Makefile  neword.o  ordstat.o  payment.c     rthist.c   rthist.o  sequence.h  slev.c      spt_proc.c  spt_proc.o  support.o  trans_if.h
    [root@YC-ss1 src]# cd ..
    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ls
    add_fkey_idx.sql  create_table-autoinc-pk.sql  load.sh  README  README.md  run_tpcc.sh  schema2  scripts  src  tpcc_load  tpcc_load_parallel.sh  tpcc_start
    

     

    初步化数据

    (root@127.0.0.1) [(none)]> create database tpcc;
    Query OK, 1 row affected (0.07 sec)
    
    (root@127.0.0.1) [(none)]> use tpcc
    Database changed
    (root@127.0.0.1) [tpcc]> source /home/tpccmysql/tpcc-mysql-autoinc-pk-master/create_table-autoinc-pk.sql;
    

     

    使用tpcc_load加载数据:

    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_load 127.0.0.1:6002  tpcc root "123456" 30
    *************************************
    *** ###easy### TPC-C Data Loader  ***
    *************************************
    <Parameters>
         [server]: 127.0.0.1
         [port]: 6002
         [DBname]: tpcc
           [user]: root
           [pass]: 123456
      [warehouse]: 30
    TPCC Data Load Started...
    Loading Item 
    .................................................. 50000
    .................................................. 100000
    

     

    其一历程有一点悠久,可以去楼下抽支烟。然后实行压力测量试验,测量检验一个钟头,这几个时辰也是有一点点久,能够下楼买个冰棍了。

    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 6002 -d tpcc -u root -p 123456 -w 30 -c 8 -r 120 -l 3600  -f tpcc_0705
    ***************************************
    *** ###easy### TPC-C Load Generator ***
    ***************************************
    option h with value '127.0.0.1'
    option P with value '6002'
    option d with value 'tpcc'
    option u with value 'root'
    option p with value '123456'
    option w with value '30'
    option c with value '8'
    option r with value '120'
    option l with value '3600'
    option f with value 'tpcc_0705'
    <Parameters>
         [server]: 127.0.0.1
         [port]: 6002
         [DBname]: tpcc
           [user]: root
           [pass]: 123456
      [warehouse]: 30
     [connection]: 8
         [rampup]: 120 (sec.)
        [measure]: 3600 (sec.)
    

     

    伺机擦测验结果

    STOPPING THREADS........
    
    <Raw Results>
      [0] sc:24209  lt:1  rt:0  fl:0 
      [1] sc:24167  lt:0  rt:0  fl:0 
      [2] sc:2421  lt:1  rt:0  fl:0 
      [3] sc:2420  lt:0  rt:0  fl:0 
      [4] sc:2423  lt:0  rt:0  fl:0 
     in 3600 sec.
    
    <Raw Results2(sum ver.)>
      [0] sc:24209  lt:1  rt:0  fl:0 
      [1] sc:24210  lt:0  rt:0  fl:0 
      [2] sc:2421  lt:1  rt:0  fl:0 
      [3] sc:2421  lt:0  rt:0  fl:0 
      [4] sc:2423  lt:0  rt:0  fl:0 
    
    <Constraint Check> (all must be [OK])
     [transaction percentage]
            Payment: 43.43% (>=43.0%) [OK]
       Order-Status: 4.35% (>= 4.0%) [OK]
           Delivery: 4.35% (>= 4.0%) [OK]
        Stock-Level: 4.35% (>= 4.0%) [OK]
     [response time (at least 90% passed)]
          New-Order: 100.00%  [OK]
            Payment: 100.00%  [OK]
       Order-Status: 99.96%  [OK]
           Delivery: 100.00%  [OK]
        Stock-Level: 100.00%  [OK]
    
    <TpmC>
                     403.500 TpmC
    

     

    大家得以见到在自家那些品级压测下,Atlas MG大切诺基仍旧很平稳的。上边我们看下故障转移

     

    2.mysql-db02

    1 #系统版本
    2 [root@mysql-db02 ~]# cat /etc/redhat-release
    3 CentOS release 6.7 (Final)
    4 #内核版本
    5 [root@mysql-db02 ~]# uname -r
    6 2.6.32-573.el6.x86_64
    7 #IP地址
    8 [root@mysql-db02 ~]# hostname -I
    9 10.0.0.52
    

    文/Bruce.Liu1

    上面大家就起来出手布置大家的MHA高可用,因为小编唯有两台虚构机,所以就只可以遵照两台来搞了,中间也踩了点坑,下边看一下大家的骨干条件:

    4:故障转移的测量试验

    往上边回顾一下大家的机器景况

    机器 MySQL 环境
    172.16.128.240 MGR NODE1 MGR
    172.16.128.241 MGR NODE2 MGR
    172.16.128.242 MGR NODE3 MGR
    172.16.128.239 VIP keepalived

    近日大家Atlas提供的服务端口是172.16.128.242:6002,大家模拟NODE1节点故障 ,然后看一下压测的结果最终结出是哪些的。

    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 6002 -d tpcc -u root -p 123456 -w 30 -c 8 -r 120 -l 300  -f tpcc_0706
    ***************************************
    *** ###easy### TPC-C Load Generator ***
    ***************************************
    option h with value '127.0.0.1'
    option P with value '6002'
    option d with value 'tpcc'
    option u with value 'root'
    option p with value '123456'
    option w with value '30'
    option c with value '8'
    option r with value '120'
    option l with value '300'
    option f with value 'tpcc_0706'
    <Parameters>
         [server]: 127.0.0.1
         [port]: 6002
         [DBname]: tpcc
           [user]: root
           [pass]: 123456
      [warehouse]: 30
     [connection]: 8
         [rampup]: 120 (sec.)
        [measure]: 300 (sec.)
    
    RAMP-UP TIME.(120 sec.)
    

     

    第二部关闭NODE1,

    2006, HY000, MySQL server has gone away
    payment 4:1
    2006, HY000, MySQL server has gone away
    payment 4:1
    2006, HY000, MySQL server has gone away
    payment 4:1
    2006, HY000, MySQL server has gone away
    

     

    全副都TM的gone away了,也真是没有办法的,大家在再次开首压测,开掘是没难题的,

    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 6002 -d tpcc -u root -p 123456 -w 30 -c 8 -r 120 -l 300  -f tpcc_0706
    ***************************************
    *** ###easy### TPC-C Load Generator ***
    ***************************************
    option h with value '127.0.0.1'
    option P with value '6002'
    option d with value 'tpcc'
    option u with value 'root'
    option p with value '123456'
    option w with value '30'
    option c with value '8'
    option r with value '120'
    option l with value '300'
    option f with value 'tpcc_0706'
    <Parameters>
         [server]: 127.0.0.1
         [port]: 6002
         [DBname]: tpcc
           [user]: root
           [pass]: 123456
      [warehouse]: 30
     [connection]: 8
         [rampup]: 120 (sec.)
        [measure]: 300 (sec.)
    
    RAMP-UP TIME.(120 sec.)
    
    MEASURING START.
    
      10, 905(0):1.694|4.648, 905(0):0.449|4.042, 91(0):0.217|0.562, 90(0):2.178|3.151, 90(0):5.098|10.983
      20, 724(0):1.750|2.948, 725(0):0.548|1.545, 73(0):0.173|0.230, 72(0):1.947|2.309, 73(0):4.700|5.217
      30, 1050(0):1.746|4.647, 1046(0):0.372|0.746, 104(0):0.163|0.258, 105(0):1.843|1.966, 105(0):5.323|5.616
      40, 917(0):1.754|4.656, 918(0):0.392|0.985, 92(0):0.190|0.191, 91(0):2.042|2.340, 92(0):5.574|6.931
      50, 951(0):1.688|2.792, 950(1):0.440|5.618, 96(0):0.206|0.373, 96(0):2.240|3.440, 95(0):4.809|4.985
      60, 965(0):1.764|2.737, 966(0):0.393|0.836, 96(0):0.222|0.517, 97(0):2.220|2.448, 95(0):4.989|4.990
      70, 940(0):1.589|2.525, 941(0):0.463|1.766, 94(0):0.164|0.263, 94(0):2.446|3.339, 96(0):5.512|5.704
      80, 746(2):1.671|7.135, 742(1):0.445|5.139, 75(0):0.229|0.324, 74(0):2.353|2.653, 74(0):5.342|5.454
      90, 1022(0):1.784|4.030, 1022(0):0.421|1.311, 102(0):0.311|0.479, 102(0):2.093|3.696, 102(0):5.810|6.025
     100, 1012(0):1.809|2.774, 1012(0):0.436|0.961, 101(0):0.207|0.381, 102(0):2.135|2.262, 101(0):5.867|5.931
     110, 928(0):1.651|2.960, 930(0):0.397|1.347, 93(0):0.193|0.371, 92(0):2.157|3.873, 93(0):5.445|6.339
     120, 1000(0):1.670|2.486, 1001(0):0.408|0.888, 99(0):0.257|0.405, 100(0):1.943|2.189, 100(0):5.292|5.770
     130, 992(0):1.749|4.123, 990(0):0.463|1.120, 100(0):0.206|0.277, 99(0):1.742|1.759, 100(0):4.674|5.843
     140, 868(1):1.893|7.145, 864(0):0.447|1.527, 86(0):0.188|0.221, 87(0):2.262|2.292, 86(0):4.586|4.684
     150, 1024(0):1.641|2.351, 1027(0):0.415|0.680, 103(0):0.153|0.195, 102(0):1.936|5.337, 102(0):4.877|5.004
    
    <Raw Results>
      [0] sc:28932  lt:6  rt:0  fl:0 
      [1] sc:28921  lt:2  rt:0  fl:0 
      [2] sc:2894  lt:0  rt:0  fl:0 
      [3] sc:2893  lt:0  rt:0  fl:0 
      [4] sc:2894  lt:0  rt:0  fl:0 
     in 300 sec.
    
    <Raw Results2(sum ver.)>
      [0] sc:28932  lt:6  rt:0  fl:0 
      [1] sc:28934  lt:2  rt:0  fl:0 
      [2] sc:2894  lt:0  rt:0  fl:0 
      [3] sc:2893  lt:0  rt:0  fl:0 
      [4] sc:2894  lt:0  rt:0  fl:0 
    
    <Constraint Check> (all must be [OK])
     [transaction percentage]
            Payment: 43.47% (>=43.0%) [OK]
       Order-Status: 4.35% (>= 4.0%) [OK]
           Delivery: 4.35% (>= 4.0%) [OK]
        Stock-Level: 4.35% (>= 4.0%) [OK]
     [response time (at least 90% passed)]
          New-Order: 99.98%  [OK]
            Payment: 99.99%  [OK]
       Order-Status: 100.00%  [OK]
           Delivery: 100.00%  [OK]
        Stock-Level: 100.00%  [OK]
    
    <TpmC>
                     5787.600 TpmC
    

     

    大家再看一下Atlas的田间管理音讯

    (user@127.0.0.1) [(none)]> SELECT * FROM backends;
     ------------- --------------------- ------- ------ 
    | backend_ndx | address             | state | type |
     ------------- --------------------- ------- ------ 
    |           1 | 172.16.128.239:3306 | up    | rw   |
    |           2 | 172.16.128.240:3306 | up    | ro   |
    |           3 | 172.16.128.241:3306 | up    | ro   |
    |           4 | 172.16.128.242:3306 | up    | ro   |
     ------------- --------------------- ------- ------ 
    4 rows in set (0.00 sec)
    

    察觉不平时的NODE1节点的场合竟然依旧UP,也等于说大家还要和煦想艺术通告Atlas笔者那个节点挂了,要修改配置信息。可是最起码说明读写分离是足以成功了。所以说这些依旧有一点不可相信的啊,大家不能够落实。大家选材mycat试试

    复制情形盘算

    3.mysql-db03

    1 #系统版本
    2 [root@mysql-db03 ~]# cat /etc/redhat-release 
    3 CentOS release 6.7 (Final)
    4 #内核版本
    5 [root@mysql-db03 ~]# uname -r
    6 2.6.32-573.el6.x86_64
    7 #IP地址
    8 [root@mysql-db03 ~]# hostname -I
    9 10.0.0.53
    

    小说大纲

    1. 读写分离简单介绍
      1.1. 大范围高并发场景
      1.2. 读写分离原理
      1.3. 读写分离类型
    2. Maxsacle
      2.1. Maxsacle简介
      2.2. Maxsacle安装
      2.3. Maxsacle配置
    3. Keepalived lvs
      3.1. Keepalived lvs简介
      3.2. Keepalived lvs安装
      3.3. Keepalived lvs高可用验收
      3.4. Keepalived lvs运转注意事项
      3.5. 附录
    MySQL1(master):172.16.16.34:3306  MHA Manager MHA Node
    MySQL2(slave1):172.16.16.35:3306 MHA Node
    MySQL3(slave2):172.16.16.35:3307 MHA Node
    
    MyCat读写分离高可用测量试验

    设置使用mycat,贴一下mycat的配备文件

    [root@YC-ss1 conf]# cat schema.xml 
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
            <table name="customer" primaryKey="aid"  dataNode="tp1" />
            <table name="district" primaryKey="aid"  dataNode="tp1" />
            <table name="history" primaryKey="aid"  dataNode="tp1" />
            <table name="item" primaryKey="aid"  dataNode="tp1" />
            <table name="new_orders" primaryKey="aid"  dataNode="tp1" />
            <table name="order_line" primaryKey="aid"  dataNode="tp1" />
            <table name="orders" primaryKey="aid"  dataNode="tp1" />
            <table name="stock" primaryKey="aid"  dataNode="tp1" />
            <table name="warehouse" primaryKey="aid"  dataNode="tp1" />
        </schema>
    
    
        <dataNode name="tp1" dataHost="dbhost239" database="tpcc" />
    
        <dataHost name="dbhost239" maxCon="1000" minCon="10" balance="1"
                    writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <!-- can have multi write hosts -->
                    <writeHost host="hostM1" url="172.16.128.239:3306" user="root"
                            password="123456">
                    </writeHost>
                    <writeHost host="hostS1" url="172.16.128.240:3306" user="root"
                            password="123456" />
                    <writeHost host="hostS2" url="172.16.128.241:3306" user="root"                      
                            password="123456" />
                    <writeHost host="hostS3" url="172.16.128.242:3306" user="root"                      
                            password="123456" />
        </dataHost>
    
    </mycat:schema>
    

     

    不在赘述,直接通过节点一load数据

    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_load 172.16.128.239:3306  tpcc root "123456" 10
    *************************************
    *** ###easy### TPC-C Data Loader  ***
    *************************************
    <Parameters>
         [server]: 172.16.128.239
         [port]: 3306
         [DBname]: tpcc
           [user]: root
           [pass]: 123456
      [warehouse]: 10
    TPCC Data Load Started...
    Loading Item 
    .................................................. 50000
    .................................................. 100000
    Item Done. 
    Loading Warehouse 
    Loading Stock Wid=1
    .................................................. 50000
    .................................................. 100000
     Stock Done.
    Loading District
    Loading Stock Wid=2
    .................................................. 50000
    .................................................. 100000
    

     

    然后先压测:

    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 8066 -d TESTDB -u root -p 123456 -w 10 -c 8 -r 120 -l 300  -f tpcc_0710
    

     

    看一下压测结果

     250, 1320(0):1.793|4.080, 1320(0):0.370|1.357, 133(0):0.160|0.185, 131(0):1.993|2.155, 132(0):2.476|3.372
     260, 1154(0):1.695|3.316, 1153(0):0.371|1.664, 114(0):0.138|0.144, 116(0):1.936|3.117, 114(0):3.079|3.506
     270, 1076(0):1.780|3.807, 1076(0):0.359|0.706, 108(0):0.155|0.195, 107(0):1.830|3.335, 109(0):2.296|2.368
     280, 1094(0):1.682|2.435, 1092(0):0.362|1.352, 109(0):0.142|0.163, 110(0):1.963|2.259, 109(0):2.760|2.868
     290, 1264(0):1.668|2.191, 1269(0):0.339|0.744, 127(0):0.159|0.188, 127(0):1.847|1.943, 126(0):2.909|3.890
     300, 1169(0):1.605|4.055, 1166(0):0.336|1.421, 117(0):0.160|0.192, 117(0):1.874|3.247, 118(0):2.456|2.595
    
    STOPPING THREADS........
    
    <Raw Results>
      [0] sc:35560  lt:3  rt:0  fl:0 
      [1] sc:35563  lt:1  rt:0  fl:0 
      [2] sc:3557  lt:0  rt:0  fl:0 
      [3] sc:3557  lt:0  rt:0  fl:0 
      [4] sc:3557  lt:0  rt:0  fl:0 
     in 300 sec.
    
    <Raw Results2(sum ver.)>
      [0] sc:35560  lt:3  rt:0  fl:0 
      [1] sc:35565  lt:1  rt:0  fl:0 
      [2] sc:3557  lt:0  rt:0  fl:0 
      [3] sc:3557  lt:0  rt:0  fl:0 
      [4] sc:3557  lt:0  rt:0  fl:0 
    
    <Constraint Check> (all must be [OK])
     [transaction percentage]
            Payment: 43.48% (>=43.0%) [OK]
       Order-Status: 4.35% (>= 4.0%) [OK]
           Delivery: 4.35% (>= 4.0%) [OK]
        Stock-Level: 4.35% (>= 4.0%) [OK]
     [response time (at least 90% passed)]
          New-Order: 99.99%  [OK]
            Payment: 100.00%  [OK]
       Order-Status: 100.00%  [OK]
           Delivery: 100.00%  [OK]
        Stock-Level: 100.00%  [OK]
    
    <TpmC>
                     7112.600 TpmC
    

     

    由于好多压测都以立异数据,所以都路由到了239VIP上。大家能够直接测一下读取

    (root@127.0.0.1) [TESTDB]> select *,@@server_id from item limit 1;
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    | aid | i_id | i_im_id | i_name                | i_price | i_data                                  | @@server_id |
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    |  35 |    1 |    1320 | lUPdQrxNjkFvKSw698smv |   11.14 | jtIe2rjbaByDZp8wXr9QBbNFjV7VOIL8BdmBSTk |  1282403306 |
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    1 row in set (0.37 sec)
    
    (root@127.0.0.1) [TESTDB]> select *,@@server_id from item limit 1;
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    | aid | i_id | i_im_id | i_name                | i_price | i_data                                  | @@server_id |
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    |  35 |    1 |    1320 | lUPdQrxNjkFvKSw698smv |   11.14 | jtIe2rjbaByDZp8wXr9QBbNFjV7VOIL8BdmBSTk |  1282413306 |
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    1 row in set (0.05 sec)
    (root@127.0.0.1) [TESTDB]> select *,@@server_id from item limit 1;
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    | aid | i_id | i_im_id | i_name                | i_price | i_data                                  | @@server_id |
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    |  35 |    1 |    1320 | lUPdQrxNjkFvKSw698smv |   11.14 | jtIe2rjbaByDZp8wXr9QBbNFjV7VOIL8BdmBSTk |  1282423306 |
     ----- ------ --------- ----------------------- --------- ----------------------------------------- ------------- 
    1 row in set (0.07 sec)
    

     

    读的央求会路由倒三台机械上。未来倘若240的主机挂掉,VIP239从240飘到241,大家看一下mycat会不会把连接有没不寻常。 首先给叁个缕缕的压测,

    [root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 8066 -d TESTDB -u root -p 123456 -w 10 -c 8 -r 120 -l 1800
    

     

    在压测的历程中大家停掉240主机,VIP会切换的241上。大家再看压测结果。

    1184, HY000, Connection refused
    payment 7:1
    1184, HY000, Connection refused
    neword 4:1
    1184, HY000, Connection refused
    payment 6:1
    1184, HY000, Connection refused
    payment 5:1
    1184, HY000, Connection refused
    payment 1:1
    1184, HY000, Connection refused
    payment 0:1
    1184, HY000, Connection refused
    neword 3:1
    1184, HY000, Connection refused
    payment 6:1
    1184, HY000, Connection refused
    neword 4:1
    1184, HY000, Connection refused
    payment 7:1
    1184, HY000, Connection refused
      70, 723(2):1.654|47.728, 726(5):0.655|76.966, 72(0):0.164|0.298, 72(0):1.957|2.127, 72(0):2.550|2.571
      80, 1178(0):1.769|2.702, 1182(0):0.416|0.870, 118(0):0.159|0.288, 117(0):1.938|1.971, 118(0):2.769|3.452
      90, 1332(0):1.766|4.103, 1330(0):0.425|1.254, 134(0):0.175|0.178, 133(0):2.207|2.644, 134(0):3.027|3.508
     100, 901(0):1.822|2.738, 902(0):0.431|1.109, 90(0):0.152|0.158, 90(0):2.040|2.049, 90(0):2.991|3.176
     -------
     <Raw Results>
      [0] sc:22875  lt:3  rt:81654  fl:40 
      [1] sc:22879  lt:5  rt:87541  fl:40 
      [2] sc:2288  lt:0  rt:9995  fl:5 
      [3] sc:2287  lt:0  rt:13993  fl:7 
      [4] sc:2291  lt:0  rt:5997  fl:3 
     in 240 sec.
    
    <Raw Results2(sum ver.)>
      [0] sc:22881  lt:3  rt:81655  fl:40 
      [1] sc:22881  lt:5  rt:87552  fl:40 
      [2] sc:2288  lt:0  rt:9995  fl:5 
      [3] sc:2287  lt:0  rt:13993  fl:7 
      [4] sc:2291  lt:0  rt:5997  fl:3 
    
    <Constraint Check> (all must be [OK])
     [transaction percentage]
            Payment: 43.48% (>=43.0%) [OK]
       Order-Status: 4.35% (>= 4.0%) [OK]
           Delivery: 4.35% (>= 4.0%) [OK]
        Stock-Level: 4.35% (>= 4.0%) [OK]
     [response time (at least 90% passed)]
          New-Order: 99.99%  [OK]
            Payment: 99.98%  [OK]
       Order-Status: 100.00%  [OK]
           Delivery: 100.00%  [OK]
        Stock-Level: 100.00%  [OK]
    
    <TpmC>
                     5719.500 TpmC
    

     

    能够见到,在keepalived切换进度中,连接是没戏的,可是最后VIP切换来功之后全数的连天就好像常了。所以说利用MYCAT来落实读写分离是行得通的。但是也是有三个主题素材,session close的时候会有题目,但是新链接能够确定保障是未曾难题的,从最终压测结果也是能够看出来的。然则从核心也知足了我们的中坚必要。

    读写分离理论上讲,跟复制格局尚未提到,atlas负担的是重定向读写,至于复制格局本身选取,这里是测量检验意况,此前测量检验MGEscort的单机多实例,MGPRADO单主形式的复制形式,就顺便借助名爵汉兰达做依照atlas的读写分离。

    二、安装mysql

    1.读写分离简介

    小编们固然一主两从的条件大家早已搭建好了。

    新葡亰496net 2

    1.安装包准备

    1 #创建安装包存放目录
    2 [root@mysql-db01 ~]# mkdir /home/oldboy/tools -p
    3 #进入目录
    4 [root@mysql-db01 ~]# cd /home/oldboy/tools/
    5 #上传mysql安装包(mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz)
    6 [root@mysql-db01 tools]# rz -be
    

    1.1.常见高并发场景

    随着一个网址的业务不停扩展,数据持续扩展,数据库的下压力也会愈加大,对数据库恐怕SQL的中坚优化大概达不到最后的功能,此时得以思索通过抬高数据库节点来使其完结进步品质的指标一般有以下常见三种方案。

    • 读写分离
      开发三个帖子内容页,须要select帖子表,和帖子商议表,各样耗费时间10ms的话。每秒一千次询问正是以此数据库的顶点了。约等于说,那几个论坛只好承载每秒500次访谈。那么大家就能够对那么些数据库做读写分离,来成倍提高数据库的读品质。

    • 水平分表
      交易历史,这种表。因为很少查询1年前的音讯。所以,大家能够按年度来拓展水平分表。将今年的表优先对待。就减弱了要询问的表的轻重缓急。

    • 分库
      设若我们的体系丰盛大了,成效相当多,就能够有许多类别的多寡库表,比如经营发售活动的表,和用户账号的表是未有关系的,那么我们就足以将她们分到七个数据库中,然后放到不一致的独自的数据库服务器,就能够使数据库吞吐量成倍扩大。

    • 垂直分表
      独立的施用场景是在篇章列表那样的景观,一般来讲,我们的篇章表会有title、userId、Content等字段,在那之中的Content字段一般是Text只怕LongText类型,而其他的字段都以一定长度的数据类型。我们领略多个数据库优化法规是:

      • 若是一个表的具有字段都以稳固长度类型的,那么它正是定长表,定长表比动态长度表查询品质要高
      • 这正是说,大家就能够运用垂直分表来将稿子表分成作品表和小说内容表。于是小说列表页面所需的询问,就只须要查询一张定长表了。
    • 引入Cache
      通常来讲就算前面一个的数据库框架结构做得再好,对于定期抢购/抽取奖金/等,这种看似高密度密集会见的境况也是不恐怕,最好的章程仍然在服务层和数目库层增多三个缓存层用,使其让央浼命中至缓存层,数据层只长久化改换的数量就能够。

    独有两台机器,所以说凑合着用起来呢

     

    2.安装

     1 #创建安装目录
     2 [root@mysql-db01 tools]# mkdir /application
     3 #解压mysql二进制包
     4 [root@mysql-db01 tools]# tar xf mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz
     5 #移动安装包
     6 [root@mysql-db01 tools]# mv mysql-5.6.16-linux-glibc2.5-x86_64 /application/mysql-5.6.16
     7 #做软链接
     8 [root@mysql-db01 tools]# ln -s /application/mysql-5.6.16/ /application/mysql
     9 #创建mysql用户
    10 [root@mysql-db01 tools]# useradd mysql -s /sbin/nologin -M
    11 #进入mysql初始化目录
    12 [root@mysql-db01 tools]# cd /application/mysql/scripts/
    13 #初始化mysql
    14 [root@mysql-db01 scripts]# ./mysql_install_db 
    15 --user=mysql 
    16 --datadir=/application/mysql/data/ 
    17 --basedir=/application/mysql/
    18 #注解
    19 --user:   指定mysql用户
    20 --datadir:指定mysql数据存放目录
    21 --basedir:指定mysql base目录
    22 #拷贝mysql配置文件
    23 [root@mysql-db01 ~]# cp /application/mysql/support-files/my-default.cnf /etc/my.cnf
    24 #拷贝mysql启动脚本
    25 [root@mysql-db01 ~]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
    26 #修改mysql默认安装目录(否则无法启动)
    27 [root@mysql-db01 ~]# sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld
    28 [root@mysql-db01 ~]# sed -i 's#/usr/local#/application#g' /application/mysql/bin/mysqld_safe
    29 #配置mysql环境变量
    30 [root@mysql-db01 ~]# echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
    31 #刷新环境变量
    32 [root@mysql-db01 ~]# source /etc/profile
    

    1.2.读写分离原理

    MySQL的主从复制和MySQL的读写分离两个兼有紧凑联系,首先安排主从复制,唯有主从复制完了,才干在此基础上海展览中心开数据的读写分离。轻巧的话,读写分离就是只在主服务器上写,只在从服务器上读,基本的规律是让主数据库处管事人务性查询,而从数据库管理select查询。当业务量非常的大时,一台服务器的习性无法满意急需,就能够通过安插主从复制完结写分离来平均分摊负载,制止因负载太高而形成不能即时响应诉求。

    1:首先大家要安装MHA的安装包,安装MHA从前,要安装一些借助情形

    atlas安装

    3.起步并增多开机自启

    1 #加入开机自启
    2 [root@mysql-db01 ~]# chkconfig mysqld on
    3 #启动mysql
    4 [root@mysql-db01 ~]# /etc/init.d/mysqld start
    5 Starting MySQL........... SUCCESS! #启动成功
    

    1.3.读写分离类型

    • 凭仗程序代码内部贯彻
      在代码中依照select,insert实行路由分类,那类方法也是现阶段添丁意况应用最分布的,优点是性质好,因为在程序代码中曾经将读写的数据源拆分至七个,所以无需相当的MySQL proxy分析SQL报文,在拓展路由至不一样数据库节点。短处是惯常该架构较复杂,运行开支相对较高。

    • 凭借中间代理层完成
      代理层一般位于客户端和服务器之间,代理服务器接到客户端要求后透过剖判SQL文本再将SQL路由至可用的数据库节点中。优点是先后无需退换能够兑现无缝迁移,可移植性较好。劣点是性质相对前面一个略微逊色一些,而且并非具备的读操作都能够被路由至从节点中。

    NODE节点:

    rpm安装,弹指间成功

    4.设置root密码

    1 #配置mysql密码为oldboy123
    2 [root@mysql-db01 ~]# mysqladmin -uroot password oldboy123
    

    2.Maxsacle

    yum install -y perl-DBD-MySQL
    

    新葡亰496net 3

    三、主从复制

    2.1.Maxsacle简介

    新葡亰496net 4

    图表来源互联网

    maxscale是mariadb集团费用的一套数据库中间件,能够很有利的落到实处读写分离方案;並且提供了读写分离的负载均衡和高可用性保险。别的maxscale对于前端选择来说是晶莹剔透的,我们能够很有利的将动用迁移到maxscale中落到实处读写分离方案,来平均分摊主库的压力。maxscale也提供了sql语句的辨析过滤效果。这里大家根本疏解maxscale的装置、配置以及注意事项。

    • Maxsacle特性

    1.带权重的读写分离(负载均衡)
    2.SQL防火墙
    3.出头路由政策(Connection based, Statement based,Schema based)
    4.自动物检疫查评定MySQL master Failover (同盟MHA或许MRM)
    5.检验中央延时
    6.多租户sharding架构

    https://mariadb.com/kb/zh-cn/3028/
    https://mariadb.com/downloads/mariadb-tx/maxscale

     

    atlas的计划文件(私下认可/usr/local/mysql_proxy/conf/test.cnf),个人以为是贰个非常兴高采烈的布置,基本上配置节点的备考都极其明白。
    因为是单机多实例,这里独有经过区别的端口号来分裂从库实例,能够是单从,也足以是多从,依照事先级来拆分。

    1.先决条件

    • 主库和从库都要开启binlog (因为从库有希望被提高为主库,所以必须开启binlog)

    • 主库和从库server-id分化

    • 要有主从复制用户

    2.2.Maxsacle安装

    Manager节点:

    #Atlas后端总是的MySQL从库的IP和端口,@后边的数字代表权重,用来作负载均衡,若省略则默以为1,可设置多项,用逗号分隔
    proxy-read-only-backend-addresses = ***.***.***.***:8002@1, ***.***.***.***::8003@2

    2.主库操作

    2.2.1.软件参谋文书档案

    参谋文档:
    法定文书档案:https://mariadb.com/kb/en/mariadb-enterprise/maxscale-20-installation-guide/

    软件下载:
    maxSacle软件:https://downloads.mariadb.com/MaxScale/

    yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
    

    本条节点必要注意的是,对应的用户名必须在每一个是运用都设有且密码同样,不然谋面世错误

    2.1退换配置文件

    1 #编辑mysql配置文件
    2 [root@mysql-db01 ~]# vim /etc/my.cnf
    3 #在mysqld标签下配置
    4 [mysqld]
    5 #主库server-id为1,从库必须和主库不一样
    6 server_id =1
    7 #开启binlog日志
    8 log_bin=mysql-bin
    
    2.1.2.系统情状介绍
    • 系统版本
      CentOS release 6.7 (Final) x86_64

    • MySQL版本
      mysql-5.7.20.-x86_64(RPM)

    • maxSacle版本
      maxscale-2.0.5

     

    #用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示范,将其替换为您的MySQL的用户名和加密密码!
    pwds = username1:DAJnl8cVzy8=, username2:DAJnl8cVzy8=

    2.2创立主从复制用户

    1 #登录数据库
    2 [root@mysql-db01 ~]# mysql -uroot -poldboy123
    3 #创建rep用户
    4 mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';
    
    2.1.3.装置软件
    # rpm -ivh maxscale-2.0.5-1.rhel.6.x86_64.rpm
    

    但是这一个包我们系统是未曾的,要求大家设置相应的epel第三方财富库,再设置,大家能够先去

    [mysql-proxy]
    
    #带#号的为非必需的配置项目
    
    #管理接口的用户名
    admin-username = user
    
    #管理接口的密码
    admin-password = pwd
    
    #Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
    proxy-backend-addresses = ***.***.***.***:8001
    
    #Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
    proxy-read-only-backend-addresses = proxy-read-only-backend-addresses =***.***.***.***:8002@1,***.***.***.***:8003@2
    
    #用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
    pwds = user1:DAJnl8cVzy8=, user2:DAJnl8cVzy8=
    
    #设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
    daemon = true
    
    #设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
    keepalive = true
    
    #工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
    event-threads = 8
    
    #日志级别,分为message、warning、critical、error、debug五个级别
    log-level = message
    
    #日志存放的路径
    log-path = /usr/local/mysql-proxy/log
    
    #SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
    #sql-log = OFF
    
    #慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。
    #sql-log-slow = 10
    
    #实例名称,用于同一台机器上多个Atlas实例间的区分
    #instance = test
    
    #Atlas监听的工作接口IP和端口
    proxy-address = ***.***.***.***:1234
    
    #Atlas监听的管理接口IP和端口
    admin-address = ***.***.***.***:2345
    
    #分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
    #tables = person.mt.id.3
    
    #默认字符集,设置该项后客户端不再需要执行SET NAMES语句
    #charset = utf8
    
    #允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
    #client-ips = 127.0.0.1, 192.168.1
    
    #Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
    #lvs-ips = 192.168.1.1
    

    3.从库操作

    2.1.4.DB成立用户
    • 创办监察和控制复制状态账号
    mysql> grant replication slave, replication client on *.* to scalemon@'192.168.2.%' identified by 'monitor18';
    
    • 始建筑工程作账号
    mysql> grant all privileges on mycat.* to user_maxscale@'192.168.2.%' identified by 'H2kXvhaJxRSl';
    mysql> grant select on *.* to user_maxscale@'192.168.2.%';
    

    那个网址下载我们须要的包,然后安装:

    启动atlas

    3.1修改配置文件

     1 #修改mysql-db02配置文件
     2 [root@mysql-db02 ~]# vim /etc/my.cnf
     3 #在mysqld标签下配置
     4 [mysqld]
     5 #主库server-id为1,从库必须和主库不一样
     6 server_id =5
     7 #开启binlog日志
     8 log_bin=mysql-bin
     9 #重启mysql
    10 [root@mysql-db02 ~]# /etc/init.d/mysqld restart
    11 
    12 #修改mysql-db03配置文件
    13 [root@mysql-db03 ~]# vim /etc/my.cnf
    14 #在mysqld标签下配置
    15 [mysqld]
    16 #主库server-id为1,从库必须和主库不一样
    17 server_id =10
    18 #开启binlog日志
    19 log_bin=mysql-bin
    20 #重启mysql
    21 [root@mysql-db03 ~]# /etc/init.d/mysqld restart
    

    注:在昔日倘诺是基于binlog日志的主从复制,则必需求铭记主库的master状态音讯。

     

    1 mysql> show master status;
    

    ------------------ ----------

    | File             | Position |

    ------------------ ----------

    | mysql-bin.000002 |      120 |

    ------------------ ----------

     

     


     

    2.1.5.maxscale创办目录
    mkdir -p /data1/maxscale4007/log/
    mkdir -p /data1/maxscale4007/data/
    mkdir -p /data1/maxscale4007/cache/
    mkdir -p /data1/maxscale4007/pid/
    chown -R maxscale:maxscale /data1/maxscale4007
    
    [root@localhost yum.repos.d]# rpm -ivh epel-release-6-8.noarch.rpm
    

    新葡亰496net 5

    4.开启GTID

     1 #没开启之前先看一下GTID的状态
     2 mysql> show global variables like '%gtid%';
     -------------------------- ------- 
    | Variable_name            | Value |
     -------------------------- ------- 
    | enforce_gtid_consistency | OFF   |
    | gtid_executed            |       |
    | gtid_mode                | OFF   |
    | gtid_owned               |       |
    | gtid_purged              |       |
     -------------------------- ------- 
     3 #编辑mysql配置文件(主库从库都需要修改)
     4 [root@mysql-db01 ~]# vim /etc/my.cnf
     5 #在[mysqld]标签下添加
     6 [mysqld]
     7 gtid_mode=ON
     8 log_slave_updates
     9 enforce_gtid_consistency
    10 #重启数据库
    11 [root@mysql-db01 ~]# /etc/init.d/mysqld restart
    12 #检查GTID状态
    13 mysql> show global variables like '%gtid%';
    

      -------------------------- -------

     | Variable_name            | Value |

      -------------------------- -------

     | enforce_gtid_consistency | ON    | #执行GTID一致

     | gtid_executed            |       |

     | gtid_mode                | ON    | #开启GTID模块

     | gtid_owned               |       |

     | gtid_purged              |       |

      -------------------------- -------

     


    注:主库从库都供给开启GTID不然在做主从复制的时候就能报错

    1 [root@mysql-db02 ~]# mysql -uroot -poldboy123
    2 mysql> change master to
    3 -> master_host='10.0.0.51',
    4 -> master_user='rep',
    5 -> master_password='oldboy123',
    6 -> master_auto_position=1;
    7 ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.
    

     

    2.1.6.maxsacle成立秘钥
    # maxkeys /data1/maxscale4007/data/
    # maxpasswd /data1/maxscale4007/data/.secrets monitor18
    80068EB8F16774E59BECBF8EE23AD60D
    # maxpasswd /data1/maxscale4007/data/.secrets H2kXvhaJxRSl
    41A9A7EFD164EF7C2D590F43F405E465
    

    设置完之后试行以下语句查看一下源:

     

    5.布署主从复制

     1 #登录数据库
     2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
     3 #配置复制主机信息
     4 mysql> change master to
     5 #主库IP
     6 -> master_host='10.0.0.51',
     7 #主库复制用户
     8 -> master_user='rep',
     9 #主库复制用户的密码
    10 -> master_password='oldboy123',
    11 #GTID位置点
    12 -> master_auto_position=1;
    13 #开启slave
    14 mysql> start slave;
    15 #查看slave状态
    16 mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.0.0.51                                 #主库IP
                      Master_User: rep
                      Master_Port: 3306                                      #主库端口
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004                          #binlog名
              Read_Master_Log_Pos: 191
                   Relay_Log_File: mysql-db02-relay-bin.000005
                    Relay_Log_Pos: 401
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes                                       #IO线程状态
                Slave_SQL_Running: Yes                                       #SQL线程状态
                  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: 191
                  Relay_Log_Space: 1883
                  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: 1
                      Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
                 Master_Info_File: /application/mysql-5.6.16/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1       #GTID号
                Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1       #GTID号
                    Auto_Position: 1
    
    2.1.6.maxscale创办布局文件
    vim /etc/maxscale4007.cnf 
    
    [maxscale]
    threads=auto    #开启线程个数,默认为1.设置为auto会同cpu核数相同 
    maxlog=1        #将日志写入到maxscale的日志文件中
    log_to_shm=0    #不将日志写入到共享缓存中,开启debug模式时可打开加快速度
    log_warning=1   #记录告警信息 
    log_error=1     #记录错误信息
    log_notice=1    #记录notice
    log_info=0      #不记录info
    log_debug=0     #不记录debug
    #log_augmentation=1
    LimitNOFILE=65536
    logdir=/data1/maxscale4007/log/  
    datadir=/data1/maxscale4007/data/  
    cachedir=/data1/maxscale4007/cache/  
    piddir=/data1/maxscale4007/pid/  
    libdir=/usr/lib64/maxscale/  
    execdir=/usr/bin/  
    
    [server1]
    type=server
    address=192.168.2.120
    port=3389
    protocol=MySQLBackend
    
    [server2]
    type=server
    address=192.168.2.121
    port=3389
    protocol=MySQLBackend
    
    [MySQL Monitor]
    type=monitor
    module=mysqlmon
    servers=server1,server2
    user=scalemon
    passwd=80068EB8F16774E59BECBF8EE23AD60D
    monitor_interval=5000      #监控心跳为5秒
    #detect_replication_lag=true  #监控主从复制延迟
    #max_slave_replication_lag  #控制maxscale运行的最大延迟
    detect_stale_master=true   #当复制slave全部断掉时,maxscale仍然可用,将所有的访问指向master节点
    
    #[Read-Only Service]
    #type=service
    #router=readconnroute
    #servers=server2
    #user=user_maxscale
    #passwd=A622DB34CBF413527DE9048EBC3FE42E66EF97D8DD167887F781C5108213FD21
    #router_options=slave
    
    #读写分离,用户需要有SELECT ON mysql.db;SELECT ON mysql.tables_priv;SHOW DATABASES ON *.*的权限  
    [Read-Write Service]
    type=service
    router=readwritesplit
    servers=server1,server2
    user=user_maxscale
    passwd=41A9A7EFD164EF7C2D590F43F405E465
    max_slave_connections=100%
    use_sql_variables_in=master  #sql语句中的存在变量只指向master中执行
    enable_root_user=1   #允许root用户登录执行
    #master_accept_reads=true  #master节点也可以转发读请求 
    
    [MaxAdmin Service]
    type=service
    router=cli
    
    #[Read-Only Listener]
    #type=listener
    #service=Read-Only Service
    #protocol=MySQLClient
    #port=3300
    
    #读写分离服务端口
    [Read-Write Listener]
    type=listener
    service=Read-Write Service
    protocol=MySQLClient
    port=4007
    
    #管理服务端口
    [MaxAdmin Listener]
    type=listener
    service=MaxAdmin Service
    protocol=maxscaled
    socket=/data1/maxscale4007/maxadmin.sock
    port=6604
    
    [root@localhost yum.repos.d]# yum repolist
    Loaded plugins: fastestmirror, security
    Loading mirror speeds from cached hostfile
    * epel: mirror.lzu.edu.cn
    repo id repo name status
    base CentOS-6 - Base - 163.com 6,706
    *epel Extra Packages for Enterprise Linux 6 - x86_64 12,305
    extras CentOS-6 - Extras - 163.com 45
    updates CentOS-6 - Updates - 163.com 318
    yum yum 6,367
    repolist: 25,741
    

    测量检验读写分离

    6.从库别的配置

     1 #登录从库
     2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
     3 #禁用自动删除relay log 功能
     4 mysql> set global relay_log_purge = 0;
     5 #设置只读
     6 mysql> set global read_only=1;
     7 #编辑配置文件
     8 [root@mysql-db02 ~]# vim /etc/my.cnf
     9 #在mysqld标签下添加
    10 [mysqld]
    11 #禁用自动删除relay log 永久生效
    12 relay_log_purge = 0
    

    2.3.Maxsacle配置

    能够见到已经有epel相关的能源了,所以大家就能够实践实践上边的yum语句安装MHA的借助情状。

    MG福睿斯是一主二从,主节点Server_id是8001,从节点的Server_id分别是8002和8003
    能够发现读新闻重定向到8002节点,写音信重定向到8001节点,完成了读写分离

    四、部署MHA

    2.3.1.maxscale启动
    maxsacle -f /etc/maxscale4007.cnf
    

    设置到位以后在两台机器安装NODE节点在master机器安装Manage:

    新葡亰496net 6

    1.条件希图(全数节点)

     1 #安装依赖包
     2 [root@mysql-db01 ~]# yum install perl-DBD-MySQL -y
     3 #进入安装包存放目录
     4 [root@mysql-db01 ~]# cd /home/oldboy/tools/
     5 #上传mha安装包
     6 [root@mysql-db01 tools]# rz -be
     7 mha4mysql-manager-0.56-0.el6.noarch.rpm
     8 mha4mysql-manager-0.56.tar.gz
     9 mha4mysql-node-0.56-0.el6.noarch.rpm
    10 mha4mysql-node-0.56.tar.gz
    11 #安装node包
    12 [root@mysql-db01 tools]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
    13 #登录数据库
    14 [root@mysql-db01 tools]# mysql -uroot -poldboy123
    15 #添加mha管理账号
    16 mysql> grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';
    17 #查看是否添加成功
    18 mysql> select user,host from mysql.user;
    19 #主库上创建,从库会自动复制(在从库上查看)
    20 mysql> select user,host from mysql.user;
    
    2.3.2.maxscale关闭
    # maxadmin -S /data1/maxscale4007/maxadmin.sock "shutdown maxscale"
    
    [root@localhost sa]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
    [root@localhost sa]# rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm
    

    强制关掉贰个从节点,将读重定向到次级读节点。

    2.命令软连接

    1 #如果不创建命令软连接,检测mha复制情况的时候会报错
    2 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
    3 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
    
    2.3.3.maxscale服务情况
    MaxScale> list listeners
    Listeners.
    --------------------- -------------------- ----------------- ------- --------
    Service Name         | Protocol Module    | Address         | Port  | State
    --------------------- -------------------- ----------------- ------- --------
    Read-Write Service   | MySQLClient        | *               |  4007 | Running
    MaxAdmin Service     | maxscaled          | *               |  6604 | Running
    MaxAdmin Service     | maxscaled          | /data1/maxscale4007/maxadmin.sock |     0 | Running
    --------------------- -------------------- ----------------- ------- --------
    
    MaxScale> list servers
    Servers.
    ------------------- ----------------- ------- ------------- --------------------
    Server             | Address         | Port  | Connections | Status              
    ------------------- ----------------- ------- ------------- --------------------
    server1            | 192.168.2.120   |  3389 |           0 | Master, Running
    server2            | 192.168.2.121   |  3389 |           0 | Slave, Running
    ------------------- ----------------- ------- ------------- --------------------
    
    MaxScale> list services
    Services.
    -------------------------- ---------------------- -------- ---------------
    Service Name              | Router Module        | #Users | Total Sessions
    -------------------------- ---------------------- -------- ---------------
    Read-Write Service        | readwritesplit       |      1 |     2
    MaxAdmin Service          | cli                  |      3 |     3
    -------------------------- ---------------------- -------- ---------------
    
    show services
    Service 0x18d6330
        Service:                             Read-Write Service
        Router:                              readwritesplit (0x7f17ce109d40)
        State:                               Started
        Number of router sessions:              1
        Current no. of router sessions:         1
        Number of queries forwarded:            1
        Number of queries forwarded to master:  0 (0.00%)
        Number of queries forwarded to slave:   1 (100.00%)
        Number of queries forwarded to all:     0 (0.00%)
        Started:                             Sat Jan  6 09:43:33 2018
        Root user access:                    Enabled
        Backend databases:
            192.168.2.120:3389  Protocol: MySQLBackend
            192.168.2.121:3389  Protocol: MySQLBackend
        Users data:                          0x18eb920
        Total connections:                   2
        Currently connected:                 2
    Service 0x18d4870
        Service:                             MaxAdmin Service
        Router:                              cli (0x7f17ce313560)
        State:                               Started
        Started:                             Sat Jan  6 09:43:33 2018
        Root user access:                    Disabled
        Backend databases:
        Users data:                          0x18ed620
        Total connections:                   3
        Currently connected:                 3
    

    自己那边包是已经下载好的,直接利用rpm安装了。至此算是安装收尾了

    新葡亰496net 7

    3.管理节点(mha-manager

    2.3.4.测量检验读写分离
    • maxScale张开info等第日志
    # vim /etc/maxscale4007.cnf
    log_info=1
    
    • maxScale重新加载配置
    # maxadmin -S /data1/maxscale4007/maxadmin.sock 
    MaxScale> reload config
    Reloading configuration from file.
    
    • client测试
    # mysql -h 192.168.2.122 -P4007 -uuser_maxscale -pH2kXvhaJxRSl
    
    • maxScale剖判日志
    2018-01-06 09:50:48   [7]  info   : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select * from t1 
    2018-01-06 09:50:48   [7]  info   : Route query to slave    192.168.2.121:3389 <
    2018-01-06 09:51:03   [7]  info   : > Autocommit: [enabled], trx is [open], cmd: COM_QUERY, type: QUERY_TYPE_BEGIN_TRX, stmt: begin 
    2018-01-06 09:51:03   [7]  info   : Route query to master   192.168.2.120:3389 <
    2018-01-06 09:51:03   [7]  info   : > Autocommit: [enabled], trx is [open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select * from t1 
    2018-01-06 09:51:03   [7]  info   : Route query to master   192.168.2.120:3389 <
    2018-01-06 09:51:03   [7]  info   : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_COMMIT, stmt: commit 
    2018-01-06 09:51:03   [7]  info   : Route query to master   192.168.2.120:3389 <
    

    maxScale注意:

    • 创造链接的时候,不支持压缩协议
    • 转发路由无法动态的辨认master节点的动员搬迁
    • LONGLOB字段不辅助
    • 在转手气象会将语句转到master节点中(保险专门的工作一致):
      大廷广众钦定业务;
      prepared的语句;
      语句中带有存款和储蓄进度,自定义函数
      包括多条语句音讯:INSERT INTO ... ; SELECT LAST_INSERT_ID();
    • 一对语句暗中同意会发送到后端的具备server中,可是能够内定
      use_sql_variables_in=[master|all] (default: all)
    • maxscale不支持主机名相配的验证方式,只帮助IP地址方式的host深入分析。所以在增添user的时候记得使用合适的范式。
    • 跨库查询不援救,会议及展览示的钦命到首个数据库中

    简单来讲介绍一下MHA的Manager工具包和Node工具包

    读重定向到8003节点,写依然是主节点,名爵奥迪Q5状态也健康,假若尝试关闭全数的读节点,读将自行重定向到主(写)节点,表明从节点的荒谬都以足以包容的。
    那一点认证,从(读)节点的其他错误都以不影响atlas对外提供服务器的,假设成功主节点的高可用,atlas就足以健全地对外提供劳务了。

    3.1在mysql-db03上设置管理节点

    1 #使用epel源
    2 [root@mysql-db03 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
    3 #安装manager依赖包
    4 [root@mysql-db03 ~]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
    5 #安装manager包
    6 [root@mysql-db03 tools]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 
    7 Preparing...              ########################################### [100%]
    8 1:mha4mysql-manager       ########################################### [100%]
    

    3.Keepalived lvs

    Manager工具包重要不外乎以下多少个工具:

    新葡亰496net 8

    4.配置文件

     1 #创建配置文件目录
     2 [root@mysql-db03 ~]# mkdir -p /etc/mha
     3 #创建日志目录
     4 [root@mysql-db03 ~]# mkdir -p /var/log/mha/app1
     5 #编辑mha配置文件
     6 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf
     7 [server default]
     8 manager_log=/var/log/mha/app1/manager        
     9 manager_workdir=/var/log/mha/app1            
    10 master_binlog_dir=/application/mysql/data        
    11 user=mha                                   
    12 password=mha                               
    13 ping_interval=2
    14 repl_password=oldboy123
    15 repl_user=rep
    16 ssh_user=root                               
    17 
    18 [server1]                                   
    19 hostname=10.0.0.51
    20 port=3306                                  
    21 
    22 [server2]
    23 candidate_master=1                         
    24 check_repl_delay=0                          
    25 hostname=10.0.0.52
    26 port=3306
    27 
    28 [server3]
    29 hostname=10.0.0.53
    30 port=3306
    

    【配置文件详解】

     1 [server default]
     2 #设置manager的工作目录
     3 manager_workdir=/var/log/masterha/app1
     4 #设置manager的日志
     5 manager_log=/var/log/masterha/app1/manager.log 
     6 #设置master 保存binlog的位置,以便MHA可以找到master的日志,我这里的也就是mysql的数据目录
     7 master_binlog_dir=/data/mysql
     8 #设置自动failover时候的切换脚本
     9 master_ip_failover_script= /usr/local/bin/master_ip_failover
    10 #设置手动切换时候的切换脚本
    11 master_ip_online_change_script= /usr/local/bin/master_ip_online_change
    12 #设置mysql中root用户的密码,这个密码是前文中创建监控用户的那个密码
    13 password=123456
    14 #设置监控用户root
    15 user=root
    16 #设置监控主库,发送ping包的时间间隔,尝试三次没有回应的时候自动进行failover
    17 ping_interval=1
    18 #设置远端mysql在发生切换时binlog的保存位置
    19 remote_workdir=/tmp
    20 #设置复制用户的密码
    21 repl_password=123456
    22 #设置复制环境中的复制用户名 
    23 repl_user=rep
    24 #设置发生切换后发送的报警的脚本
    25 report_script=/usr/local/send_report
    26 #一旦MHA到server02的监控之间出现问题,MHA Manager将会尝试从server03登录到server02
    27 secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=192.168.0.50 --master_port=3306
    28 #设置故障发生后关闭故障主机脚本(该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用)
    29 shutdown_script=""
    30 #设置ssh的登录用户名
    31 ssh_user=root 
    32 
    33 [server1]
    34 hostname=10.0.0.51
    35 port=3306
    36 
    37 [server2]
    38 hostname=10.0.0.52
    39 port=3306
    40 #设置为候选master,如果设置该参数以后,发生主从切换以后将会将此从库提升为主库,即使这个主库不是集群中事件最新的slave
    41 candidate_master=1
    42 #默认情况下如果一个slave落后master 100M的relay logs的话,MHA将不会选择该slave作为一个新的master,因为对于这个slave的恢复需要花费很长时间,通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时,这个参数对于设置了candidate_master=1的主机非常有用,因为这个候选主在切换的过程中一定是新的master
    43 check_repl_delay=0
    

    3.1.Keepalived lvs简介

    在网络选拔才具中,负载均衡一向是火爆话题,使用负载均衡手艺重要的指标包括如下几点:

    • 高可用性:
      构成系统的有个别设备或部件失效,并不会潜移暗化健康的服务

    • 可扩大性:
      用户的加码,引起访谈数 以至流量的充实,这种意况下,须求对系统举办扩大体积,以应对这种火速拉长,并且普通该现象中最大旨扩张供给正是对用户是晶莹的

    • 负载本事:
      当全数连串的担任趋于饱和时,通过扩大物理服务器和扩张物理带宽来化解这些麻烦。扩张物理服务器后,系统的负荷情状就要有着集群的情理服务器之间依据钦定的算法从新的高峰达新的动态平衡

    设想服务器是负载均衡种类的基本框架结构,分为:转载器(Director)和真实性服务器。

    新葡亰496net 9

    图表源于keepalived官方网址

    • 转发器(Director):
      重大担负包转载、负载均衡,该功能通过ipvsadm具体落到实处。

    • 实打实服务器(Real):
      确实经受并拍卖必要的服务器,经常泛指:nginx、mysql等分化意况等服务

    masterha_check_ssh 检查MHA的SSH配置状况
    masterha_check_repl 检查MySQL复制状况
    masterha_manger 启动MHA
    masterha_check_status 检测当前MHA运行状态
    masterha_master_monitor 检测master是否宕机
    masterha_master_switch 控制故障转移(自动或者手动)
    masterha_conf_host 添加或删除配置的server信息
    

    Atlas中间件会自动过滤掉一部分扬汤止沸的操作,举个例子不带where条件的delete就无法实践

    5.ssh-key(全部节点)

    1 #创建秘钥对
    2 [root@mysql-db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
    3 #发送公钥,包括自己
    4 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
    5 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
    6 [root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.53
    

    3.2.Keepalived lvs安装

    Node工具包(那些工具平常由MHA Manager的台本触发,没有供给人工操作)首要不外乎以下多少个工具:

    新葡亰496net 10

    6.起步前测验

    1 #测试ssh
    2 [root@mysql-db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
    3 #看到如下字样,则测试成功
    4 Tue Mar  7 01:03:33 2017 - [info] All SSH connection tests passed successfully.
    5 #测试复制
    6 [root@mysql-db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
    7 #看到如下字样,则测试成功
    8 MySQL Replication Health is OK.
    
    3.2.1.软件参考文书档案

    参照文书档案:
    法定文书档案:http://www.keepalived.org/pdf/sery-lvs-cluster.pdf

    软件下载:
    keepalived软件:http://www.keepalived.org/download.html
    Ipvs软件:http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz

    save_binary_logs 保存和复制master的二进制日志
    apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
    filter_mysqlbinlog 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
    purge_relay_logs 清除中继日志(不会阻塞SQL线程)
    

    尚无进展分表测量试验。

    7.启动MHA

    1 #启动
    2 [root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
    
    3.2.2.系统遭受介绍
    • 系统版本
      CentOS release 6.7 (Final) x86_64

    • MySQL版本
      mysql-5.7.20.-x86_64(RPM)

    • keepalived版本
      keepalived-1.2.13-5

    • 架构暗暗表示图

    新葡亰496net 11

    图影片来源于原创

    2:配置主机SSH免密登陆

     

    8.failover测试

     1 #登录数据库(db02)
     2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
     3 #检查复制情况
     4 mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.0.0.51
                      Master_User: rep
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 191
                   Relay_Log_File: mysql-db02-relay-bin.000005
                    Relay_Log_Pos: 401
            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: 191
                  Relay_Log_Space: 1883
                  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: 1
                      Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
                 Master_Info_File: /application/mysql-5.6.16/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
                Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
                    Auto_Position: 1
     5 #登录数据库(db03)
     6 [root@mysql-db03 ~]# mysql -uroot -poldboy123
     7 #检查复制情况
     8 mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.0.0.51
                      Master_User: rep
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 191
                   Relay_Log_File: mysql-db02-relay-bin.000005
                    Relay_Log_Pos: 401
            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: 191
                  Relay_Log_Space: 1883
                  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: 1
                      Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
                 Master_Info_File: /application/mysql-5.6.16/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
                Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
     9 #停掉主库
    10 [root@mysql-db01 ~]# /etc/init.d/mysqld stop
    11 Shutting down MySQL..... SUCCESS!
    12 #登录数据库(db02)
    13 [root@mysql-db02 ~]# mysql -uroot -poldboy123
    14 #查看slave状态
    15 mysql> show slave statusG
    16 #db02的slave已经为空
    17 Empty set (0.00 sec)
    18 #登录数据库(db03)
    19 [root@mysql-db03 ~]# mysql -uroot -poldboy123
    20 #查看slave状态
    21 mysql> show slave statusG
    

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

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 10.0.0.52

                      Master_User: rep

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: mysql-bin.000006

              Read_Master_Log_Pos: 191

                   Relay_Log_File: mysql-db03-relay-bin.000002

                    Relay_Log_Pos: 361

            Relay_Master_Log_File: mysql-bin.000006

                 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: 191
                  Relay_Log_Space: 1883
                  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: 1
                      Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
                 Master_Info_File: /application/mysql-5.6.16/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
                Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
    
    3.2.3.lvs客户端
    • 忠实服务器上操作(slave1 slave2),参照格式编写lvs 客户端配置脚本,保存后予以施行权限。其总体的开始和结果如下:
    # vim /usr/local/bin/lvs_real
    
    #!/bin/bash
    #description : start realserver
    
    VIP=192.168.2.210
    
    /etc/rc.d/init.d/functions
    
    case "$1" in
    start)
            echo " start LVS of REALServer"
            /sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 up
            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
            ;;
    stop)
            /sbin/ifconfig lo:0 down
            echo "close LVS Directorserver"
            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 "Usage: $0 {start|stop}"
            exit 1
    esac
    
    • 诚实服务器上操作(slave1 slave2),运行lvs client并配置成开机自运维
    # chmod 755 /usr/local/bin/lvs_real
    # chmod 755 /etc/rc.d/init.d/functions
    # /usr/local/bin/lvs_real start
    # echo "/usr/local/bin/lvs_real start" >> /etc/rc.d/rc.local
    
    • 验证lvs client
    # ifconfig
    
    … …  省略  … …
    lo:0      Link encap:Local Loopback  
              inet addr:10.209.5.201  Mask:255.255.255.255
              UP LOOPBACK RUNNING  MTU:16436  Metric:1
    

    鉴于自家这两台测量检验机是从运营手里申请的,折腾过来配置SSH浪费了比非常多小时,並且本身那边依然两台server取代MHA的一主两从一管理的四台机器,中间照旧稍微难题的

     

    五、VIP漂移

    3.2.4.设置配备keepalived
    • 安装ipvs
      Ipvs(IP Virtual Server)是全体负载均衡的根基,若无这么些基础,故障隔断与切换就毫无意义;Ipvs具体是由ipvsadm那一个顺序来成功的。因此必要安装ipvsadm
    # yum -y install ipvsadm-1.26-4.el6.x86_64
    
    • 安装keepalived软件
      负载均衡器上操作(Master Backup)安装keepalived和ipvsadm工具包
    # yum -y install keepalived.x86_64
    
    • 开启路由转载
      负载均衡器上操作(Master Backup),开启路由转发
    # echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
    # sysctl -p
    
    • Master的布置文件
    # vim /etc/keepalived/keepalived.conf
    
    ! Configuration File for keepalived
    
    global_defs {
       notification_email {
         acassen@firewall.loc
         failover@firewall.loc
         sysadmin@firewall.loc
       }
       router_id lvs_mysql_3389
    }
    
    vrrp_instance vi_3389 { 
        state MASTER
        interface eth0
        lvs_sync_daemon_inteface eth0
        virtual_router_id 11
        priority 90
        nopreempt
        advert_int 1 
        authentication { 
            auth_type PASS 
            auth_pass mysql3389
        } 
        virtual_ipaddress { 
            192.168.2.210
        } 
    }
    
    virtual_server 192.168.2.210 3389 { 
        delay_loop 2 
        lb_algo wrr
        lb_kind DR 
    #    persistence_timeout 60 
        protocol TCP 
    
        real_server 192.168.2.120 3389 {
            weight 3 
        TCP_CHECK {
                connect_timeout 10
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3389
            }
        }
    
        real_server 192.168.2.121 3389 {
            weight 3 
        TCP_CHECK {                 
                connect_timeout 10      
                nb_get_retry 3          
                delay_before_retry 3    
                connect_port 3389
            }
        } 
    
    }
    
    • Backup的安插文件
    # vim /etc/keepalived/keepalived.conf
    
    ! Configuration File for keepalived
    
    global_defs {
       notification_email {
         acassen@firewall.loc
         failover@firewall.loc
         sysadmin@firewall.loc
       }
       router_id lvs_mysql_3389
    }
    
    vrrp_instance vi_3389 { 
        state MASTER
        interface eth0
        lvs_sync_daemon_inteface eth0
        virtual_router_id 11
        priority 10
        nopreempt
        advert_int 1 
        authentication { 
            auth_type PASS 
            auth_pass mysql3389
        } 
        virtual_ipaddress { 
            192.168.2.210
        } 
    }
    
    virtual_server 192.168.2.210 3389 { 
        delay_loop 2 
        lb_algo wrr
        lb_kind DR 
    #    persistence_timeout 60 
        protocol TCP 
    
        real_server 192.168.2.120 3389 {
            weight 3 
        TCP_CHECK {
                connect_timeout 10
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3389
            }
        }
    
        real_server 192.168.2.121 3389 {
            weight 3 
        TCP_CHECK {                 
                connect_timeout 10      
                nb_get_retry 3          
                delay_before_retry 3    
                connect_port 3389
            }
        } 
    
    }
    
    • 起步服务并证实
      启航keepalived服务并证实(Master Backup)
    # /etc/init.d/keepalived start
    # chkconfig keepalived on
    

    两台机器生成本身的公钥信息:ssh-keygen -t rsa

    相见的难点:

    1.vip漂移的三种方法

    • 通过keepalived的方式,管理VIP的漂移
    • 由此MHA自带脚本办法,管理VIP的悬浮

    3.3.Keepalived lvs高可用验收

    • keepalived故障测验

    Master模拟宕机,测验Backup是或不是能够第有的时候直接管Keepalived服务,并切换vip

    • Master重启机器
    # init 6
    
    • Backup已经看到可以接管
    # ip addr
    … …  省略  … …
    8: bond0: <BROADCAST,MULTICAST,MASTER,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP 
        link/ether a0:36:9f:79:07:4c brd ff:ff:ff:ff:ff:ff
        inet 10.209.5.195/25 brd 10.209.5.255 scope global bond0
        inet 10.209.5.201/32 scope global bond0    # bakcup已经看出可以接管
        inet6 fe80::a236:9fff:fe79:74c/64 scope link 
           valid_lft forever preferred_lft forever
    
    • lvs client故障测量检验

    将slave1 slave2主机进行重启,用于模拟宕机,测量试验是不是会将央浼转载至正规的Master1

    • Slave1重启机器
    # init 6
    
    • Slave2重启机器
    # init 6
    
    • 负载局衡器上印证转载链路状态,链路平常能够接二连三Master
    # ipvsadm -L
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:Port Scheduler Flags
      -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
    TCP  10.209.5.201:chevinservices wrr
      -> 10.209.5.191:chevinservices  Route   3      0          0
    
    • MHA故障切换对LVS的熏陶测量试验

    • MySQL Master1重启机器

    # init 6
    
    • Master故障后,负载均衡器的链路状态
    # ipvsadm -L
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:Port Scheduler Flags
      -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
    TCP  10.209.5.201:chevinservices wrr
      -> 10.209.5.192:chevinservices  Route   3      0          0         
      -> 10.209.5.193:chevinservices  Route   3      0          0
    

    借使MySQL 原本的Master 是重启的气象,负载均衡器还有大概会将诉求转载至原Master,不会去检查测试原Master的复制状态以及是或不是有延期的,以下是原Master起来后,负载均衡器上的链路状态

    # ipvsadm -L
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:Port Scheduler Flags
      -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
    TCP  10.209.5.201:chevinservices wrr
      -> 10.209.5.191:chevinservices  Route   3      0          0         
      -> 10.209.5.192:chevinservices  Route   3      0          0         
      -> 10.209.5.193:chevinservices  Route   3      0          0  
    

    以一台机器为例,34正片本人的公钥到别的机器:

    一齐先服务不能起动,出现谬误日志proxy-plugin.c.1783: I have no server backend, closing connection,是因为布置的user音信在每一个节点差别等导致的。
    新兴涂改pwds 节点的user音信,个中user的新在每二个节点都平等,蕴涵用户名和密码,服务平常运转。

    2.MHA自带脚本办法

    3.4.Keepalived lvs运转注意事项

    • 线上转移注意事项

    线上keepalived情状(基于端口探测格局)不会检讨MySQL复制链路以及延缓情状,所以对于大数量量表的DDL改动,最棒是身处晚间依然专门的职业低峰期举办,复制的不行也要即刻的管理,幸免因数量延迟只怕复制不一同导致专门的学问读取过期的数据

    • keepalived环境slave扩容
      1.配备新的slave
      2.修改Mha配置文件,将新增加多slave也增多,并设置成永不成为master
      3.在keepalived的Backup节点上加多新slave的布置标签,并将keepalived Master的keepalived服务截至,此时keepalive的服务会切换到Backup节点上,将master的安插文件在进展立异,并把劳动运转,那样就做到了online的扩大体量职业

    • 主干切换后的注意事项
      1.率先检查MHA切换是还是不是中标、除原主以外的别的slave是不是change成功到切到新master上
      2.在负载均衡器上是还是不是检查链路是否不荒谬(ipvsad –L),並且在中央切换后决然要手动测量检验链路是不是通畅,因为某个时候master的互连网服务至极等网络状态会挑起lvs client职业特别,倘诺手动链接链路相当并且负载均衡器上链路符合规律,请第有的时候常间去该MySQL服务器上运维lvs client 程序)( lvs_real start)

    • lvs client的珍爱与停机
      当MySQL的slave服务器由于硬件故障可能切换导致急需多量光阴用于停机维护时,登入到停机维护的器材上实施“/usr/local/bin/maintenance_mode.sh 10.209.5.194 3349”,脚本有三个参数:禁止访谈的IP、禁止访问的端口,一般来说正是明确命令禁止keepalived server转发过来的MySQL 端口对应的诉求。脚本内容见附录。

    scp ~/.ssh/id_rsa.pub root@172.16.16.35:/root/.ssh/authorized_keys
    

    新葡亰496net 12

    2.1改变配置文件

    1 #编辑配置文件
    2 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf
    3 #在[server default]标签下添加
    4 [server default]
    5 #使用MHA自带脚本
    6 master_ip_failover_script=/usr/local/bin/master_ip_failover
    

    附录

    • Keepalived配置文件详解
    global_defs {
       notification_email {  #指定keepalived在发生切换时需要发送email到的对象,一行一个
        sysadmin@fire.loc
       }
       notification_email_from Alexandre.Cassen@firewall.loc #指定发件人
       smtp_server localhost #指定smtp服务器地址
       smtp_connect_timeout 30 #指定smtp连接超时时间
       router_id LVS_DEVEL #运行keepalived机器的一个标识
    }
    vrrp_sync_group VG_1{ #监控多个网段的实例
    group {
    inside_network #实例名
    outside_network
    }
    notify_master /path/xx.sh #指定当切换到master时,执行的脚本
    netify_backup /path/xx.sh #指定当切换到backup时,执行的脚本
    notify_fault "path/xx.sh VG_1" #故障时执行的脚本
    notify /path/xx.sh 
    smtp_alert #使用global_defs中提供的邮件地址和smtp服务器发送邮件通知
    }
    vrrp_instance inside_network {
        state BACKUP #指定那个为master,那个为backup,如果设置了nopreempt这个值不起作用,主备考priority决
    
    定
        interface eth0 #设置实例绑定的网卡
        dont_track_primary #忽略vrrp的interface错误(默认不设置)
        track_interface{ #设置额外的监控,里面那个网卡出现问题都会切换
        eth0
        eth1
        }
        mcast_src_ip #发送多播包的地址,如果不设置默认使用绑定网卡的primary ip
        garp_master_delay #在切换到master状态后,延迟进行gratuitous ARP请求
        virtual_router_id 50 #VPID标记
        priority 99 #优先级,高优先级竞选为master
        advert_int 1 #检查间隔,默认1秒
        nopreempt #设置为不抢占 注:这个配置只能设置在backup主机上,而且这个主机优先级要比另外一台高
        preempt_delay #抢占延时,默认5分钟
        debug #debug级别
        authentication { #设置认证
            auth_type PASS #认证方式
            auth_pass 111111 #认证密码
        }
        virtual_ipaddress { #设置vip
            192.168.202.200
        }
    }
    virtual_server 192.168.202.200 23 {
        delay_loop 6 #健康检查时间间隔
        lb_algo rr  #lvs调度算法rr|wrr|lc|wlc|lblc|sh|dh
        lb_kind DR  #负载均衡转发规则NAT|DR|RUN
        persistence_timeout 5 #会话保持时间
        protocol TCP #使用的协议
        persistence_granularity <NETMASK> #lvs会话保持粒度
        virtualhost <string> #检查的web服务器的虚拟主机(host:头)    
        sorry_server<IPADDR> <port> #备用机,所有realserver失效后启用
    real_server 192.168.200.5 23 {
                weight 1 #默认为1,0为失效
                inhibit_on_failure #在服务器健康检查失效时,将其设为0,而不是直接从ipvs中删除 
                notify_up <string> | <quoted-string> #在检测到server up后执行脚本
                notify_down <string> | <quoted-string> #在检测到server down后执行脚本
    
    TCP_CHECK {
                connect_timeout 3 #连接超时时间
                nb_get_retry 3 #重连次数
                delay_before_retry 3 #重连间隔时间
                connect_port 23  健康检查的端口的端口
                bindto <ip>   
              }
    HTTP_GET | SSL_GET{
        url{ #检查url,可以指定多个
             path /
             digest <string> #检查后的摘要信息
             status_code 200 #检查的返回状态码
            }
        connect_port <port> 
        bindto <IPADD>
        connect_timeout 5
        nb_get_retry 3
        delay_before_retry 2
    }
    
    SMTP_CHECK{
        host{
        connect_ip <IP ADDRESS>
        connect_port <port> #默认检查25端口
        bindto <IP ADDRESS>
             }
        connect_timeout 5
        retry 3
        delay_before_retry 2
        helo_name <string> | <quoted-string> #smtp helo请求命令参数,可选
    }
    MISC_CHECK{
        misc_path <string> | <quoted-string> #外部脚本路径
        misc_timeout #脚本执行超时时间
        misc_dynamic #如设置该项,则退出状态码会用来动态调整服务器的权重,返回0 正常,不修改;返回1,
    
    检查失败,权重改为0;返回2-255,正常,权重设置为:返回状态码-2
    }
        }
    



    下一场推行授权语句:

     

    2.2编辑脚本

    1 #根据配置文件中脚本路径编辑
    2 [root@mysql-db03 ~]# vim /etc/mha/master_ip_failover
    3 #修改以下几行内容
    4 my $vip = '10.0.0.55/24';
    5 my $key = '0';
    6 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
    7 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; 
    8 #添加执行权限,否则mha无法启动
    9 [root@mysql-db03 ~]# chmod  x /etc/mha/master_ip_failover
    

    【脚本内容如下】

    #!/usr/bin/env perl
    
    use strict;
    use warnings FATAL => 'all';
    
    use Getopt::Long;
    
    my (
        $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
        $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
    );
    
    my $vip = '10.0.0.55/24';
    my $key = '1';
    my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip";
    my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down";
    
    GetOptions(
        'command=s'          => $command,
        'ssh_user=s'         => $ssh_user,
        'orig_master_host=s' => $orig_master_host,
        'orig_master_ip=s'   => $orig_master_ip,
        'orig_master_port=i' => $orig_master_port,
        'new_master_host=s'  => $new_master_host,
        'new_master_ip=s'    => $new_master_ip,
        'new_master_port=i'  => $new_master_port,
    );
    
    exit &main();
    
    sub main {
    
        print "nnIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===nn";
    
        if ( $command eq "stop" || $command eq "stopssh" ) {
    
            my $exit_code = 1;
            eval {
                print "Disabling the VIP on old master: $orig_master_host n";
                &stop_vip();
                $exit_code = 0;
            };
            if ($@) {
                warn "Got Error: $@n";
                exit $exit_code;
            }
            exit $exit_code;
        }
        elsif ( $command eq "start" ) {
    
            my $exit_code = 10;
            eval {
                print "Enabling the VIP - $vip on the new master - $new_master_host n";
                &start_vip();
                $exit_code = 0;
            };
            if ($@) {
                warn $@;
                exit $exit_code;
            }
            exit $exit_code;
        }
        elsif ( $command eq "status" ) {
            print "Checking the Status of the script.. OK n";
            exit 0;
        }
        else {
            &usage();
            exit 1;
        }
    }
    
    sub start_vip() {
        `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
    }
    sub stop_vip() {
         return 0  unless  ($ssh_user);
        `ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;
    }
    
    sub usage {
        print
        "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=portn";
    }
    
    chmod 600 /root/.ssh/authorized_keys
    

    依照MGOdyssey Keepalived Atlas的高可用加读写分离

    2.3手动绑定VIP

    1 #绑定vip
    2 [root@mysql-db01 ~]# ifconfig eth0:0 10.0.0.55/24
    3 #查看vip
    4 [root@mysql-db01 ~]# ip a |grep eth0
    5 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    6     inet 10.0.0.51/24 brd 10.0.0.255 scope global eth0
    7     inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0
    

    按理说是OK了,大家作证一下:

    雄起雌伏能够品尝,在名爵Odyssey的底子上做四个基于keepalived的自行故障转移,写节点能够依照VIP做活动故障转移,
    然后在此基础上,基于VIP 其余节点做读写分离,理论上得以周详地完毕自动故障转移的高可用 读写分离。
    如此YY起来的话,以为那样子也略屌,自动故障转移有了,读写分离也是有了,理论上,只要有三个存世的节点,都足以寻常对外提供劳动。

    2.4测试VIP漂移

     1 #登录db02
     2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
     3 #查看slave信息
     4 mysql> show slave statusG
    

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

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 10.0.0.51

                      Master_User: rep

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: mysql-bin.000007

              Read_Master_Log_Pos: 191

                   Relay_Log_新葡亰496net,File: mysql-db02-relay-bin.000002

                    Relay_Log_Pos: 361

            Relay_Master_Log_File: mysql-bin.000007

                 Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

     5 #停掉主库
     6 [root@mysql-db01 ~]# /etc/init.d/mysqld stop
     7 Shutting down MySQL..... SUCCESS!
     8 #在db03上查看从库slave信息
     9 mysql> show slave statusG
    

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

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 10.0.0.52

                      Master_User: rep

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: mysql-bin.000006

              Read_Master_Log_Pos: 191

                   Relay_Log_File: mysql-db03-relay-bin.000002

                    Relay_Log_Pos: 361

            Relay_Master_Log_File: mysql-bin.000006

                 Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

    10 #在db01上查看vip信息
    11 [root@mysql-db01 ~]# ip a |grep eth0
    12 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    13 inet 10.0.0.51/24 brd 10.0.0.255 scope global eth0
    14 #在db02上查看vip信息
    15 [root@mysql-db02 ~]# ip a |grep eth0
    16 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    17     inet 10.0.0.52/24 brd 10.0.0.255 scope global eth0
    18     inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0
    
    [root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf
    Sat May 27 10:11:15 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Sat May 27 10:11:15 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
    Sat May 27 10:11:15 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
    Sat May 27 10:11:15 2017 - [info] Starting SSH connection tests..
    Sat May 27 10:11:16 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
    Sat May 27 10:11:15 2017 - [debug] Connecting via SSH from root@172.16.16.34(172.16.16.34:22) to root@172.16.16.35(172.16.16.35:22)..
    ssh: connect to host 172.16.16.34 port 22: Connection refused
    Sat May 27 10:11:15 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.16.16.34(172.16.16.34:22) to root@172.16.16.35(172.16.16.35:22) failed!
    Sat May 27 10:11:16 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
    Sat May 27 10:11:16 2017 - [debug] Connecting via SSH from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22)..
    ssh: connect to host 172.16.16.35 port 22: Connection refused
    Sat May 27 10:11:16 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22) failed!
    Sat May 27 10:11:17 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
    Sat May 27 10:11:16 2017 - [debug] Connecting via SSH from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22)..
    ssh: connect to host 172.16.16.35 port 22: Connection refused
    Sat May 27 10:11:16 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22) failed!
    SSH Configuration Check Failed!
    at /usr/bin/masterha_check_ssh line 44
    [root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf
    Sat May 27 10:11:40 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Sat May 27 10:11:40 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
    Sat May 27 10:11:40 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
    Sat May 27 10:11:40 2017 - [info] Starting SSH connection tests..
    Sat May 27 10:11:41 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
    Sat May 27 10:11:40 2017 - [debug] Connecting via SSH from root@172.16.16.34(172.16.16.34:22) to root@172.16.16.35(172.16.16.35:22)..
    ssh: connect to host 172.16.16.34 port 22: Connection refused
    Sat May 27 10:11:40 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.16.16.34(172.16.16.34:22) to root@172.16.16.35(172.16.16.35:22) failed!
    Sat May 27 10:11:41 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
    Sat May 27 10:11:41 2017 - [debug] Connecting via SSH from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22)..
    ssh: connect to host 172.16.16.35 port 22: Connection refused
    Sat May 27 10:11:41 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22) failed!
    Sat May 27 10:11:42 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
    Sat May 27 10:11:41 2017 - [debug] Connecting via SSH from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22)..
    ssh: connect to host 172.16.16.35 port 22: Connection refused
    Sat May 27 10:11:41 2017 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.16.16.35(172.16.16.35:22) to root@172.16.16.34(172.16.16.34:22) failed!
    SSH Configuration Check Failed!
    

    新葡亰496net 13

    六、binlog-server

    意识是失利的,大家这里须要把自身的公钥信息出席到表明(两台机械都要举行):

     

    1.先决条件

    • mysql版本5.6以上,必须拉开GTID
    • mha版本0.56以上
    [root@localhost .ssh]# cat id_rsa.pub >>authorized_keys
    

     

    2.修改配置文件

    1 [root@mysql-db03 ~]# vim /etc/mha/app1.cnf
    2 [binlog1]
    3 no_master=1
    4 hostname=10.0.0.53
    5 master_binlog_dir=/data/mysql/binlog/
    

    重新实践就OK了

    altas详细安排参谋:

    3.备份binlog

    1 #创建备份binlog目录
    2 [root@mysql-db03 ~]# mkdir -p /data/mysql/binlog/
    3 #进入该目录
    4 [root@mysql-db03 ~]# cd /data/mysql/binlog/
    5 #备份binlog
    6 [root@mysql-db03 binlog]# mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &
    7 #启动mha
    8 [root@mysql-db03 binlog]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
    
    [root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf
    

    4.测试binlog-server

     1 #查看binlog目录中的binlog
     2 [root@mysql-db03 binlog]# ll
     3 total 44
     4 -rw-r--r-- 1 root root 285 Mar  8 03:11 mysql-bin.000001
     5 #登录主库
     6 [root@mysql-db01 ~]# mysql -uroot -poldboy123
     7 #刷新binlog
     8 mysql> flush logs;
     9 #再次查看binlog目录
    10 [root@mysql-db03 binlog]# ll
    11 total 48
    12 -rw-r--r-- 1 root root 285 Mar  8 03:11 mysql-bin.000001
    13 -rw-r--r-- 1 root root 143 Mar  8 04:00 mysql-bin.000002
    

    此处运用到了MHA的布局文件,大家贴一下:

    七、mysql读写分离中间件Atlas

    [root@localhost .ssh]# cat /etc/mha/app1.cnf
    [server default]
    manager_log=/var/log/mha/app1/manager.log
    manager_workdir=/var/log/mha/app1.log
    master_binlog_dir=/home/mysql/db3306/log/
    master_ip_failover_script=/usr/local/bin/master_ip_failover
    master_ip_online_change_script=/usr/local/bin/master_ip_online_change
    password=123456
    ping_interval=1
    remote_workdir=/tmp
    repl_password=123456
    repl_user=root
    report_script=/usr/local/bin/send_report
    shutdown_script=""
    ssh_user=root
    user=root
    
    [server1]
    hostname=172.16.16.34
    port=3306
    
    [server2]
    hostname=172.16.16.35
    port=3306
    candidate_master=1
    check_repl_delay=0
    
    [server3]
    hostname=172.16.16.35
    port=3307
    

    1.简介

      Atlas是由 Qihoo 360厂商Web平台部基础框架结构团队开垦爱慕的五个基于MySQL协议的多少中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2本子的底蕴上,修改了一大波bug,增多了众多成效特色。它在MySQL官方推出的MySQL-Proxy 0.8.2本子的根基上,修改了大批量bug,加多了重重效果特色。

    本身那边创办了三个root@%的最高权力给MHA来行使。由于大家如若一主两从是早已搭建好的,具体授权什么的也不在赘述了。相信我们安顿MHA的话对于这个小意思都是小耳鼻喉科了。

    2.重要功效

    • 读写分离
    • 从库负载均衡
    • IP过滤
    • 机动分表
    • DBA可平滑上下线DB
    • 活动摘除宕机的DB

    3:大家也得以检查测验一下复制的标题。

    3.Atlas相对于官方MySQL-Proxy的优势

    • 将主流程中全数Lua代码用C重写,Lua仅用于管理接口
    • 重写网络模型、线程模型
    • 兑现了着实意义上的连接池
    • 优化了锁机制,品质升高数十倍

    唯独此前要安装大家的从库read_only=1;

    4.安装

    大家有福了,安装Atlas真的是炒鸡轻松,官方提供的Atlas有三种:

    Atlas (普通) : Atlas-2.2.1.el6.x86_64.rpm

    Atlas (分表) : Atlas-sharding_1.0.1-el6.x86_64.rpm

    此间大家只要求下载普通的就可以。

    1 #在主库安装,进入安装包目录
    2 [root@mysql-db01 ~]# cd /home/oldboy/tools/
    3 #下载Atlas
    4 [root@mysql-db01 tools]# 
    5 wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
    6 #安装
    7 [root@mysql-db01 tools]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 
    8 Preparing...               ########################################### [100%]
    9   1:Atlas                  ########################################### [100%]
    
    mysql -h172.16.16.35 -P3306 -uroot -p123456 -e'set global read_only=1'
    mysql -h172.16.16.35 -P3307 -uroot -p123456 -e'set global read_only=1'
    

    5.布局文件

     1 #进入Atlas工具目录
     2 [root@mysql-db01 ~]# cd /usr/local/mysql-proxy/bin/
     3 #生成密码
     4 [root@mysql-db01 bin]# ./encrypt oldboy123
     5 #修改Atlas配置文件
     6 [root@mysql-db01 ~]# vim /usr/local/mysql-proxy/conf/test.cnf
     7 #Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
     8 proxy-backend-addresses = 10.0.0.51:3306
     9 #Atlas后端连接的MySQL从库的IP和端口
    10 proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306
    11 #用户名与其对应的加密过的MySQL密码
    12 pwds = root:1N/CNLSgqXuTZ6zxvGQr9A==
    13 #SQL日志的开关
    14 sql-log = ON
    15 #Atlas监听的工作接口IP和端口
    16 proxy-address = 0.0.0.0:3307
    17 #默认字符集,设置该项后客户端不再需要执行SET NAMES语句
    18 charset = utf8
    

    下一场试行行检查查:

    6.启动

    1 [root@mysql-db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
    2 OK: MySQL-Proxy of test is started
    
    [root@localhost .ssh]# masterha_check_repl --conf=/etc/mha/app1.cnf
    Sat May 27 15:01:57 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Sat May 27 15:01:57 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
    Sat May 27 15:01:57 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
    Sat May 27 15:01:57 2017 - [info] MHA::MasterMonitor version 0.57.
    Sat May 27 15:01:57 2017 - [info] GTID failover mode = 1
    Sat May 27 15:01:57 2017 - [info] Dead Servers:
    Sat May 27 15:01:57 2017 - [info] Alive Servers:
    Sat May 27 15:01:57 2017 - [info] 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3306)
    Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3307)
    Sat May 27 15:01:57 2017 - [info] Alive Slaves:
    Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Sat May 27 15:01:57 2017 - [info] GTID ON
    Sat May 27 15:01:57 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:01:57 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Sat May 27 15:01:57 2017 - [info] GTID ON
    Sat May 27 15:01:57 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:01:57 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:01:57 2017 - [info] Checking slave configurations..
    Sat May 27 15:01:57 2017 - [info] Checking replication filtering settings..
    Sat May 27 15:01:57 2017 - [info] binlog_do_db= , binlog_ignore_db=
    Sat May 27 15:01:57 2017 - [info] Replication filtering check ok.
    Sat May 27 15:01:57 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
    Sat May 27 15:01:57 2017 - [info] Checking SSH publickey authentication settings on the current master..
    Sat May 27 15:01:57 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
    Sat May 27 15:01:57 2017 - [info]
    172.16.16.34(172.16.16.34:3306) (current master)
     --172.16.16.35(172.16.16.35:3306)
     --172.16.16.35(172.16.16.35:3307)
    
    Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
    Sat May 27 15:01:57 2017 - [info] ok.
    Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
    Sat May 27 15:01:57 2017 - [info] ok.
    Sat May 27 15:01:57 2017 - [warning] master_ip_failover_script is not defined.
    Sat May 27 15:01:57 2017 - [warning] shutdown_script is not defined.
    Sat May 27 15:01:57 2017 - [info] Got exit code 0 (Not master dead).
    
    MySQL Replication Health is OK.
    

    7.管制操作

     1 #用atlas管理用户登录
     2 [root@mysql-db01 ~]# mysql -uuser -ppwd -h127.0.0.1 -P2345
     3 #查看可用命令帮助
     4 mysql> select * from help;
     5 #查看后端代理的库
     6 mysql> SELECT * FROM backends;
    

       ------------- ---------------- ------- ------

      | backend_ndx | address        | state | type |

       ------------- ---------------- ------- ------

      |           1 | 10.0.0.51:3307 | up    | rw   |

      |           2 | 10.0.0.53:3307 | up    | ro   |

      |           3 | 10.0.0.52:3307 | up    | ro   |

       ------------- ---------------- ------- ------

     7 #平滑摘除mysql
     8 mysql> REMOVE BACKEND 2;
     9 Empty set (0.00 sec)
    10 #检查是否摘除成功
    11 mysql> SELECT * FROM backends;
    

       ------------- ---------------- ------- ------

      | backend_ndx | address        | state | type |

       ------------- ---------------- ------- ------

      |           1 | 10.0.0.51:3307 | up    | rw   |

      |           2 | 10.0.0.52:3307 | up    | ro   |

       ------------- ---------------- ------- ------

    12 #保存到配置文件中
    13 mysql> SAVE CONFIG;
    14 Empty set (0.06 sec)
    

     

     

     

    【开源是一种精神,分享是一种美德】

      — By GoodCook

      — 笔者QQ:253097001

      — 款待我们天天来调换

      —原创文章,允许转载,转发时请务必以超链接情势申明作品 原始出处 、笔者音信和本评释。不然将研商法律权利。

    大家来看复制是OK 的,这里大家批注掉了#master_ip_failover_script,依照自身看大师兄的博客里面所说MHA的Failover有两种格局:一种是编造IP地址,一种是全局配置文件。MHA并未限定使用哪种艺术,而是让用户自个儿选取,设想IP地址的主意会拉拉扯扯到别的的软件,比方keepalive软件,并且还要修改脚本master_ip_failover。所以说大家这里先注释掉那块。

    纵然如此已经成功了,但是有八个warning,因为那多少个脚本大家还未曾概念,前边补上,先不管她

    4:提起MHA

    [root@localhost .ssh]#nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
    [1] 8195
    

    检查一下MHA的运营情状:

    [root@localhost .ssh]# masterha_check_status --conf=/etc/masterha/app1.cnf
    app1 (pid:8469) is running(0:PING_OK), master:172.16.16.34
    

    意识是运维境况,证明运维是成功的,大家去看一下日记:

    [root@localhost masterha]# cat /var/log/mha/app1/manager.log
    Sat May 27 15:50:47 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Sat May 27 15:50:47 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
    Sat May 27 15:50:47 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
    Sat May 27 15:50:47 2017 - [info] MHA::MasterMonitor version 0.57.
    Sat May 27 15:50:47 2017 - [warning] /var/log/mha/app1.log/app1.master_status.health already exists. You might have killed manager with SIGKILL(-9), may run two or more monitoring process for the same application, or use the same working directory. Check for details, and consider setting --workdir separately.
    Sat May 27 15:50:48 2017 - [info] GTID failover mode = 1
    Sat May 27 15:50:48 2017 - [info] Dead Servers:
    Sat May 27 15:50:48 2017 - [info] Alive Servers:
    Sat May 27 15:50:48 2017 - [info] 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3306)
    Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3307)
    Sat May 27 15:50:48 2017 - [info] Alive Slaves:
    Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Sat May 27 15:50:48 2017 - [info] GTID ON
    Sat May 27 15:50:48 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:50:48 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Sat May 27 15:50:48 2017 - [info] GTID ON
    Sat May 27 15:50:48 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:50:48 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
    Sat May 27 15:50:48 2017 - [info] Checking slave configurations..
    Sat May 27 15:50:48 2017 - [info] Checking replication filtering settings..
    Sat May 27 15:50:48 2017 - [info] binlog_do_db= , binlog_ignore_db=
    Sat May 27 15:50:48 2017 - [info] Replication filtering check ok.
    Sat May 27 15:50:48 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
    Sat May 27 15:50:48 2017 - [info] Checking SSH publickey authentication settings on the current master..
    Sat May 27 15:50:48 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
    Sat May 27 15:50:48 2017 - [info]
    172.16.16.34(172.16.16.34:3306) (current master)
     --172.16.16.35(172.16.16.35:3306)
     --172.16.16.35(172.16.16.35:3307)
    
    Sat May 27 15:50:48 2017 - [warning] master_ip_failover_script is not defined.
    Sat May 27 15:50:48 2017 - [warning] shutdown_script is not defined.
    Sat May 27 15:50:48 2017 - [info] Set master ping interval 1 seconds.
    Sat May 27 15:50:48 2017 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s server03 -s server02
    Sat May 27 15:50:48 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
    Sat May 27 15:50:48 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
    

    不曾难题。

    假如大家向关闭的话也特别轻便

    [root@localhost .ssh]# masterha_stop --conf=/etc/mha/app1.cnf
    

    5:管理VIP:

    笔者们地点已经说过了,有二种VIP的处理艺术,一种是keepalived,一种是本子的法子管理VIP,keepalived的管制方法比较简单就是主节点和备用节点两台机器,监察和控制MySQL进程就好了,那一个和keepalived MySQL双主并未太大分裂在布署方面,关于这么些布局能够看下作者的上篇博客,博客地址:keepalived MySQL双主搭建

    上面大家最主要运用脚本的方法管理VIP,定义master_ip_failover,大家这里间接使用大师兄的博客里面包车型客车本子:

    #!/usr/bin/env perl
    
    use strict;
    use warnings FATAL => 'all';
    
    use Getopt::Long;
    
    my (
    $command, $ssh_user, $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip, $new_master_port
    );
    
    my $vip = '172.16.16.20/24';
    my $key = '1';
    my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
    my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
    
    GetOptions(
    'command=s' => $command,
    'ssh_user=s' => $ssh_user,
    'orig_master_host=s' => $orig_master_host,
    'orig_master_ip=s' => $orig_master_ip,
    'orig_master_port=i' => $orig_master_port,
    'new_master_host=s' => $new_master_host,
    'new_master_ip=s' => $new_master_ip,
    'new_master_port=i' => $new_master_port,
    );
    
    exit &main();
    
    sub main {
    
    print "nnIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===nn";
    
    if ( $command eq "stop" || $command eq "stopssh" ) {
    
    my $exit_code = 1;
    eval {
    print "Disabling the VIP on old master: $orig_master_host n";
    &stop_vip();
    $exit_code = 0;
    };
    if ($@) {
    warn "Got Error: $@n";
    exit $exit_code;
    }
    exit $exit_code;
    }
    elsif ( $command eq "start" ) {
    
    my $exit_code = 10;
    eval {
    print "Enabling the VIP - $vip on the new master - $new_master_host n";
    &start_vip();
    $exit_code = 0;
    };
    if ($@) {
    warn $@;
    exit $exit_code;
    }
    exit $exit_code;
    }
    elsif ( $command eq "status" ) {
    print "Checking the Status of the script.. OK n";
    exit 0;
    }
    else {
    &usage();
    exit 1;
    }
    }
    
    sub start_vip() {
    `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
    }
    sub stop_vip() {
    return 0 unless ($ssh_user);
    `ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;
    }
    
    sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=portn";
    }
    

    下一场我们手动在server1上增添虚构IP

    /sbin/ifconfig eth0:1 172.16.16.20/24
    

    再次聊到来MHA manager:

    [root@localhost masterha]# masterha_stop --conf=/etc/masterha/app1.cnf
    [root@localhost masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
    [root@localhost masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
    app1 (pid:3953) is running(0:PING_OK), master:172.16.16.34
    

    今昔的话,我们的MHA已经完全搭建起来了,上面测量试验一下故障转移看看有没相当:

    (1)上面初步测量检验手动的故障转移:

    手动故障转移:

    masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.16.16.34 --dead_master_port=3306 --new_master_host=172.16.16.35 --new_master_port=3306 --ignore_last_failover
    

    施行到位后就能够看出主库的地址早已是172.16.16.35:3306,VIP:172.16.16.20也早已改换成了172.16.16.35上了

    (2)测验一下机动故障转移

    未来大家再一次做为主

    VIP:172.16.16.20在server2:172.16.16.35上,MySQL master是:172.16.16.35:3306

    新葡亰496net名爵RAV4的读写分离,MySQL读写分离最棒实施。从库:172.16.16.34:3306和172.16.16.35:3307

    手动kill 掉主库

    意识早就自行切换了,接下去看一下日记:

    Mon Jun 5 14:23:13 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Mon Jun 5 14:23:13 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:23:13 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:23:13 2017 - [info] MHA::MasterMonitor version 0.57.
    Mon Jun 5 14:23:14 2017 - [info] GTID failover mode = 1
    Mon Jun 5 14:23:14 2017 - [info] Dead Servers:
    Mon Jun 5 14:23:14 2017 - [info] Alive Servers:
    Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:14 2017 - [info] 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3307)
    Mon Jun 5 14:23:14 2017 - [info] Alive Slaves:
    Mon Jun 5 14:23:14 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:14 2017 - [info] GTID ON
    Mon Jun 5 14:23:14 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:14 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:14 2017 - [info] GTID ON
    Mon Jun 5 14:23:14 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:14 2017 - [info] Current Alive Master: 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:14 2017 - [info] Checking slave configurations..
    Mon Jun 5 14:23:14 2017 - [info] Checking replication filtering settings..
    Mon Jun 5 14:23:14 2017 - [info] binlog_do_db= , binlog_ignore_db=
    Mon Jun 5 14:23:14 2017 - [info] Replication filtering check ok.
    Mon Jun 5 14:23:14 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
    Mon Jun 5 14:23:14 2017 - [info] Checking SSH publickey authentication settings on the current master..
    Mon Jun 5 14:23:14 2017 - [info] HealthCheck: SSH to 172.16.16.35 is reachable.
    Mon Jun 5 14:23:14 2017 - [info]
    172.16.16.35(172.16.16.35:3306) (current master)
     --172.16.16.34(172.16.16.34:3306)
     --172.16.16.35(172.16.16.35:3307)
    
    Mon Jun 5 14:23:14 2017 - [info] Checking master_ip_failover_script status:
    Mon Jun 5 14:23:14 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306
    
    
    IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
    
    Checking the Status of the script.. OK
    Mon Jun 5 14:23:14 2017 - [info] OK.
    Mon Jun 5 14:23:14 2017 - [warning] shutdown_script is not defined.
    Mon Jun 5 14:23:14 2017 - [info] Set master ping interval 1 seconds.
    Mon Jun 5 14:23:14 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
    Mon Jun 5 14:23:14 2017 - [info] Starting ping health check on 172.16.16.35(172.16.16.35:3306)..
    Mon Jun 5 14:23:14 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
    Mon Jun 5 14:23:46 2017 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
    Mon Jun 5 14:23:46 2017 - [info] Executing SSH check script: exit 0
    Mon Jun 5 14:23:46 2017 - [info] HealthCheck: SSH to 172.16.16.35 is reachable.
    Mon Jun 5 14:23:47 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
    Mon Jun 5 14:23:47 2017 - [warning] Connection failed 2 time(s)..
    Mon Jun 5 14:23:48 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
    Mon Jun 5 14:23:48 2017 - [warning] Connection failed 3 time(s)..
    Mon Jun 5 14:23:49 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
    Mon Jun 5 14:23:49 2017 - [warning] Connection failed 4 time(s)..
    Mon Jun 5 14:23:49 2017 - [warning] Master is not reachable from health checker!
    Mon Jun 5 14:23:49 2017 - [warning] Master 172.16.16.35(172.16.16.35:3306) is not reachable!
    Mon Jun 5 14:23:49 2017 - [warning] SSH is reachable.
    Mon Jun 5 14:23:49 2017 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status..
    Mon Jun 5 14:23:49 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Mon Jun 5 14:23:49 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:23:49 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:23:49 2017 - [info] GTID failover mode = 1
    Mon Jun 5 14:23:49 2017 - [info] Dead Servers:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Alive Servers:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307)
    Mon Jun 5 14:23:49 2017 - [info] Alive Slaves:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Checking slave configurations..
    Mon Jun 5 14:23:49 2017 - [info] Checking replication filtering settings..
    Mon Jun 5 14:23:49 2017 - [info] Replication filtering check ok.
    Mon Jun 5 14:23:49 2017 - [info] Master is down!
    Mon Jun 5 14:23:49 2017 - [info] Terminating monitoring script.
    Mon Jun 5 14:23:49 2017 - [info] Got exit code 20 (Master dead).
    Mon Jun 5 14:23:49 2017 - [info] MHA::MasterFailover version 0.57.
    Mon Jun 5 14:23:49 2017 - [info] Starting master failover.
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] * Phase 1: Configuration Check Phase..
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] GTID failover mode = 1
    Mon Jun 5 14:23:49 2017 - [info] Dead Servers:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Checking master reachability via MySQL(double check)...
    Mon Jun 5 14:23:49 2017 - [info] ok.
    Mon Jun 5 14:23:49 2017 - [info] Alive Servers:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307)
    Mon Jun 5 14:23:49 2017 - [info] Alive Slaves:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Starting GTID based failover.
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] ** Phase 1: Configuration Check Phase completed.
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] * Phase 2: Dead Master Shutdown Phase..
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] Forcing shutdown so that applications never connect to the current master..
    Mon Jun 5 14:23:49 2017 - [info] Executing master IP deactivation script:
    Mon Jun 5 14:23:49 2017 - [info] /usr/local/bin/master_ip_failover --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306 --command=stopssh --ssh_user=root
    
    
    IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
    
    Disabling the VIP on old master: 172.16.16.35
    Mon Jun 5 14:23:49 2017 - [info] done.
    Mon Jun 5 14:23:49 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
    Mon Jun 5 14:23:49 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed.
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] * Phase 3: Master Recovery Phase..
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase..
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:194
    Mon Jun 5 14:23:49 2017 - [info] Retrieved Gtid Set: 806ede0c-357e-11e7-9719-00505693235d:1
    Mon Jun 5 14:23:49 2017 - [info] Latest slaves (Slaves that received relay log files to the latest):
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] The oldest binary log file/position on all slaves is mysql-bin.000003:194
    Mon Jun 5 14:23:49 2017 - [info] Retrieved Gtid Set: 806ede0c-357e-11e7-9719-00505693235d:1
    Mon Jun 5 14:23:49 2017 - [info] Oldest slaves:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] * Phase 3.3: Determining New Master Phase..
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] Searching new master from slaves..
    Mon Jun 5 14:23:49 2017 - [info] Candidate masters from the configuration file:
    Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:23:49 2017 - [info] GTID ON
    Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:23:49 2017 - [info] Non-candidate masters:
    Mon Jun 5 14:23:49 2017 - [info] Searching from candidate_master slaves which have received the latest relay log events..
    Mon Jun 5 14:23:49 2017 - [info] New master is 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:23:49 2017 - [info] Starting master failover..
    Mon Jun 5 14:23:49 2017 - [info]
    From:
    172.16.16.35(172.16.16.35:3306) (current master)
     --172.16.16.34(172.16.16.34:3306)
     --172.16.16.35(172.16.16.35:3307)
    
    To:
    172.16.16.34(172.16.16.34:3306) (new master)
     --172.16.16.35(172.16.16.35:3307)
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] * Phase 3.3: New Master Recovery Phase..
    Mon Jun 5 14:23:49 2017 - [info]
    Mon Jun 5 14:23:49 2017 - [info] Waiting all logs to be applied..
    Mon Jun 5 14:23:49 2017 - [info] done.
    Mon Jun 5 14:23:49 2017 - [info] Getting new master's binlog name and position..
    Mon Jun 5 14:23:49 2017 - [info] mysql-bin.000001:427
    Mon Jun 5 14:23:49 2017 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.16.16.34', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='root', MASTER_PASSWORD='xxx';
    Mon Jun 5 14:23:49 2017 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 427, 806ede0c-357e-11e7-9719-00505693235d:1
    Mon Jun 5 14:23:49 2017 - [info] Executing master IP activate script:
    Mon Jun 5 14:23:49 2017 - [info] /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306 --new_master_host=172.16.16.34 --new_master_ip=172.16.16.34 --new_master_port=3306 --new_master_user='root' --new_master_password=xxx
    Unknown option: new_master_user
    Unknown option: new_master_password
    
    
    IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
    
    Enabling the VIP - 172.16.16.20/24 on the new master - 172.16.16.34
    Mon Jun 5 14:23:50 2017 - [info] OK.
    Mon Jun 5 14:23:50 2017 - [info] Setting read_only=0 on 172.16.16.34(172.16.16.34:3306)..
    Mon Jun 5 14:23:50 2017 - [info] ok.
    Mon Jun 5 14:23:50 2017 - [info] ** Finished master recovery successfully.
    Mon Jun 5 14:23:50 2017 - [info] * Phase 3: Master Recovery Phase completed.
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info] * Phase 4: Slaves Recovery Phase..
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info] * Phase 4.1: Starting Slaves in parallel..
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info] -- Slave recovery on host 172.16.16.35(172.16.16.35:3307) started, pid: 636. Check tmp log /var/log/mha/app1.log/172.16.16.35_3307_20170605142349.log if it takes time..
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info] Log messages from 172.16.16.35 ...
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info] Resetting slave 172.16.16.35(172.16.16.35:3307) and starting replication from the new master 172.16.16.34(172.16.16.34:3306)..
    Mon Jun 5 14:23:50 2017 - [info] Executed CHANGE MASTER.
    Mon Jun 5 14:23:50 2017 - [info] Slave started.
    Mon Jun 5 14:23:50 2017 - [info] gtid_wait(806ede0c-357e-11e7-9719-00505693235d:1) completed on 172.16.16.35(172.16.16.35:3307). Executed 0 events.
    Mon Jun 5 14:23:50 2017 - [info] End of log messages from 172.16.16.35.
    Mon Jun 5 14:23:50 2017 - [info] -- Slave on host 172.16.16.35(172.16.16.35:3307) started.
    Mon Jun 5 14:23:50 2017 - [info] All new slave servers recovered successfully.
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info] * Phase 5: New master cleanup phase..
    Mon Jun 5 14:23:50 2017 - [info]
    Mon Jun 5 14:23:50 2017 - [info] Resetting slave info on the new master..
    Mon Jun 5 14:23:50 2017 - [info] 172.16.16.34: Resetting slave info succeeded.
    Mon Jun 5 14:23:50 2017 - [info] Master failover to 172.16.16.34(172.16.16.34:3306) completed successfully.
    Mon Jun 5 14:23:50 2017 - [info] Deleted server1 entry from /etc/masterha/app1.cnf .
    Mon Jun 5 14:23:50 2017 - [info]
    
    ----- Failover Report -----
    
    app1: MySQL Master failover 172.16.16.35(172.16.16.35:3306) to 172.16.16.34(172.16.16.34:3306) succeeded
    
    Master 172.16.16.35(172.16.16.35:3306) is down!
    
    Check MHA Manager logs at localhost.localdomain:/var/log/mha/app1/manager.log for details.
    
    Started automated(non-interactive) failover.
    Invalidated master IP address on 172.16.16.35(172.16.16.35:3306)
    Selected 172.16.16.34(172.16.16.34:3306) as a new master.
    172.16.16.34(172.16.16.34:3306): OK: Applying all logs succeeded.
    172.16.16.34(172.16.16.34:3306): OK: Activated master IP address.
    172.16.16.35(172.16.16.35:3307): OK: Slave started, replicating from 172.16.16.34(172.16.16.34:3306)
    172.16.16.34(172.16.16.34:3306): Resetting slave info succeeded.
    Master failover to 172.16.16.34(172.16.16.34:3306) completed successfully.
    Mon Jun 5 14:23:50 2017 - [info] Sending mail..
    sh: /usr/local/bin/send_report: No such file or directory
    Mon Jun 5 14:23:50 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln2066] Failed to send mail with return code 127:0
    tail: /var/log/mha/app1/manager.log: file truncated
    Mon Jun 5 14:48:26 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Mon Jun 5 14:48:26 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:48:26 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:48:26 2017 - [info] MHA::MasterMonitor version 0.57.
    Mon Jun 5 14:48:26 2017 - [info] GTID failover mode = 1
    Mon Jun 5 14:48:26 2017 - [info] Dead Servers:
    Mon Jun 5 14:48:26 2017 - [info] Alive Servers:
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307)
    Mon Jun 5 14:48:26 2017 - [info] Alive Slaves:
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:48:26 2017 - [info] GTID ON
    Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:48:26 2017 - [info] GTID ON
    Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] Checking slave configurations..
    Mon Jun 5 14:48:26 2017 - [info] Checking replication filtering settings..
    Mon Jun 5 14:48:26 2017 - [info] binlog_do_db= , binlog_ignore_db=
    Mon Jun 5 14:48:26 2017 - [info] Replication filtering check ok.
    Mon Jun 5 14:48:26 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
    Mon Jun 5 14:48:26 2017 - [info] Checking SSH publickey authentication settings on the current master..
    Mon Jun 5 14:48:26 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
    Mon Jun 5 14:48:26 2017 - [info]
    172.16.16.34(172.16.16.34:3306) (current master)
     --172.16.16.35(172.16.16.35:3306)
     --172.16.16.35(172.16.16.35:3307)
    
    Mon Jun 5 14:48:26 2017 - [info] Checking master_ip_failover_script status:
    Mon Jun 5 14:48:26 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.34 --orig_master_ip=172.16.16.34 --orig_master_port=3306
    
    
    IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
    
    Checking the Status of the script.. OK
    Mon Jun 5 14:48:26 2017 - [info] OK.
    Mon Jun 5 14:48:26 2017 - [warning] shutdown_script is not defined.
    Mon Jun 5 14:48:26 2017 - [info] Set master ping interval 1 seconds.
    Mon Jun 5 14:48:26 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
    Mon Jun 5 14:48:26 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
    Mon Jun 5 14:48:26 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
    ^C
    [root@localhost ~]# tail -100 /var/log/mha/app1/manager.log
    Mon Jun 5 14:48:26 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Mon Jun 5 14:48:26 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:48:26 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
    Mon Jun 5 14:48:26 2017 - [info] MHA::MasterMonitor version 0.57.
    Mon Jun 5 14:48:26 2017 - [info] GTID failover mode = 1
    Mon Jun 5 14:48:26 2017 - [info] Dead Servers:
    Mon Jun 5 14:48:26 2017 - [info] Alive Servers:
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306)
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307)
    Mon Jun 5 14:48:26 2017 - [info] Alive Slaves:
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:48:26 2017 - [info] GTID ON
    Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] Primary candidate for the new Master (candidate_master is set)
    Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
    Mon Jun 5 14:48:26 2017 - [info] GTID ON
    Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
    Mon Jun 5 14:48:26 2017 - [info] Checking slave configurations..
    Mon Jun 5 14:48:26 2017 - [info] Checking replication filtering settings..
    Mon Jun 5 14:48:26 2017 - [info] binlog_do_db= , binlog_ignore_db=
    Mon Jun 5 14:48:26 2017 - [info] Replication filtering check ok.
    Mon Jun 5 14:48:26 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
    Mon Jun 5 14:48:26 2017 - [info] Checking SSH publickey authentication settings on the current master..
    Mon Jun 5 14:48:26 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
    Mon Jun 5 14:48:26 2017 - [info]
    172.16.16.34(172.16.16.34:3306) (current master)
     --172.16.16.35(172.16.16.35:3306)
     --172.16.16.35(172.16.16.35:3307)
    
    Mon Jun 5 14:48:26 2017 - [info] Checking master_ip_failover_script status:
    Mon Jun 5 14:48:26 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.34 --orig_master_ip=172.16.16.34 --orig_master_port=3306
    
    
    IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
    
    Checking the Status of the script.. OK
    Mon Jun 5 14:48:26 2017 - [info] OK.
    Mon Jun 5 14:48:26 2017 - [warning] shutdown_script is not defined.
    Mon Jun 5 14:48:26 2017 - [info] Set master ping interval 1 seconds.
    Mon Jun 5 14:48:26 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
    Mon Jun 5 14:48:26 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
    

     

    切换今后大家开掘以往大旨是OK的了,不过我们忘记了很要紧的叁个标题,那正是slave的relay log,主从复制在缺省气象下从库的relay logs会在SQL线程推行实现后被自动删除,可是对于MHA场景下,对于某个滞后从库的重作冯妇依赖于任何从库的relay log,由此选拔禁止使用自动删除功效以及定时清理的诀窍。对于清理过多过大的relay log要求留意引起的复制延迟能源开荒等。所以这里要将relay log的电动清除装置为OFF,选用手动清除relay log的艺术:

    mysql -uroot -h172.16.16.35 -P3306 -p123456 -e'set global relay_log_purge=OFF;'
    mysql -uroot -h172.16.16.35 -P3306 -p123456 -e'set global relay_log_purge=OFF;'
    

    暗中认可设置为OFF,那样relay lay每一次SQL施行线程完结后并不会被电动删除了,所以说我们须求手动删除掉relay log,在mha node的工具包里面有个purge_relay_logs工具来直接管理这几个职业,

    [root@mxqmongodb2 data]# purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3306
    2017-06-06 09:11:23: purge_relay_logs script started.
    Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000001 ..
    Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000002 ..
    Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000003 ..
    Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
    2017-06-06 09:11:26: All relay log purging operations succeeded.
    [root@mxqmongodb2 data]# purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3307
    2017-06-06 09:11:41: purge_relay_logs script started.
    Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000001 ..
    Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000002 ..
    Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000003 ..
    Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
    2017-06-06 09:11:44: All relay log purging operations succeeded.
    

    大家那样就足以手动清除掉relay log了,也得以加到定期人物里面定期施行。

    /usr/bin/purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3307
    

    那样算是搭建完了。

    末尾,那文章大批量参阅了大师兄的MHA博客: 

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net名爵RAV4的读写分离,MySQL读写分离最

    关键词:

上一篇:SQL语句优化,NOCOUNT优化存储过程

下一篇:没有了