MGPAJERO的读写分离,Atlas的读写分离尝试

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。MGCR-V为多主形式。我们考虑接纳中间件能够完毕MG奥迪Q5的读写分离。中间件选型为Atlas,Atlas是360团社团开源的一套基于MySQL-Proxy基础之上的代办,修改了MySQL-Proxy的一些BUG,并且举行了优化。

 

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)

 

可以见到我们查询了四回。已经路由到了MGEvoque的八个节点上了。读已经是没有失水准的。写的话都会经过VIP:172.16.128.239来写。而且我们的VIP是会自行故障转移,也等于说保险是可写的。那样基本上就完毕的读写分离。若是大家不想让240那台机器发出读,就可以把优先级配置的低一些,优先路由倒其余的机器。

目标是尝试altas的读写分离,现有一套搭建好做测试的MG奥迪Q7(单主),于是就腿搓绳,在MGKoleos基础上搭建altas。

时下MHA主要支撑一主多从的架构,要搭建MHA,须求2个复制集群中务必至少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,别的一台充当从库,因为至少须要三台服务器。

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
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凯雷德依然很平静的。上边大家看下故障转移

 

上面大家就起初早先布署我们的MHA高可用,因为本身唯有两台虚拟机,所以就不得不依据两台来搞了,中间也踩了点坑,上面看一下我们的主导条件:

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
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试试

复制环境准备

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

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
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会不会把连接有没有卓殊态。
首先给2个缕缕的压测,

[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

 

MGPAJERO的读写分离,Atlas的读写分离尝试。可以见到,在keepalived切换进程中,连接是失败的,可是最终VIP切换来功今后全体的连日就平常了。所以说利用MYCAT来完成读写分离是行得通的。然而也有贰个难点,session
close的时候会有失水准,可是新链接可以确保是未曾难点的,从最后压测结果也是足以看出来的。可是从中央也满意了我们的主导须求。

读写分离理论上讲,跟复制方式尚未涉嫌,atlas负责的是重定向读写,至于复制方式本人拔取,这里是测试环境,以前测试MGRubicon的单机多实例,MGTiggo单主情势的复制情势,就顺手借助MG翼虎做依照atlas的读写分离。

咱俩假诺一主两从的环境我们曾经搭建好了。

二、安装mysql

997755.com澳门葡京 1

唯有两台机械,所以说凑合着用起来呢

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:首先大家要安装MHA的安装包,安装MHA之前,要设置一些借助环境

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

atlas安装

NODE节点:

3.起头并添加开机自启

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

rpm安装,须臾间做到

yum install -y perl-DBD-MySQL

4.设置root密码

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

997755.com澳门葡京 2

 

三 、主从复制

atlas的配置文件(暗中认同/usr/local/mysql_proxy/conf/test.cnf),个人感觉是七个非凡清爽的布局,基本上配置节点的备注都特别了解。
因为是单机多实例,那里仅仅通过不相同的端口号来分裂从库实例,可以是单从,也得以是多从,根据事先级来拆分。

Manager节点:

1.先决条件

  • 主库和从库都要开启binlog
    (因为从库有只怕被升级为主库,所以必须开启binlog)

  • 主库和从库server-id不一致

  • 要有主从复制用户

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

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

2.主库操作

这些节点须要注意的是,对应的用户名必须在每种是使用都存在且密码一样,否则会现出谬误

 

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

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

唯独这一个包我们系统是从未的,要求大家设置相应的epel第②方能源库,再设置,大家得以先去

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';
[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.从库操作

启动atlas

[root@localhost yum.repos.d]# rpm -ivh epel-release-6-8.noarch.rpm

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 |

+——————+———-+

 

 


 

997755.com澳门葡京 3

安装完事后推行以下语句查看一下源:

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.

 

 

[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

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 status\G
*************************** 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

测试读写分离

能够看到曾经有epel相关的财富了,所以我们就足以推行实施上边的yum语句安装MHA的依赖性环境。

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

MGLacrosse是一主二从,主节点Server_id是8001,从节点的Server_id分别是8002和8003
可以窥见读新闻重定向到8002节点,写音讯重定向到8001节点,达成了读写分离

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

四、部署MHA

997755.com澳门葡京 4

[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

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;

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

自己那边包是早已下载好的,直接运用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

997755.com澳门葡京 5

大约介绍一下MHA的Manager工具包和Node工具包

3.管理节点(mha-manager

读重定向到8003节点,写依然是主节点,MG昂Cora状态也健康,假设尝试关闭全体的读节点,读将自动重定向到主(写)节点,表明从节点的一无所能都是足以同盟的。
那或多或少证实,从(读)节点的其余不当都是不影响atlas对外提供服务器的,假设做到主节点的高可用,atlas就足以周密地对外提供服务了。

Manager工具包主要总结以下多少个工具:

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%]

997755.com澳门葡京 6

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信息

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

Atlas中间件会自行过滤掉一部分危险的操作,比如不带where条件的delete就不能履行

Node工具包(这个工具平日由MHA
Manager的台本触发,无需人工操作)主要归纳以下多少个工具:

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

997755.com澳门葡京 7

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

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.

尚无进展分表测试。

2:配置主机SSH免密登录

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 &

 

是因为笔者那两台测试机是从运营手里申请的,折腾过来配置SSH浪费了许多日子,而且作者那边依旧两台server代替MHA的一主两从一管制的四台机械,中间依旧多少难点的

8.failover测试

 1 #登录数据库(db02)
 2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
 3 #检查复制情况
 4 mysql> show slave status\G
*************************** 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 status\G
*************************** 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 status\G
16 #db02的slave已经为空
17 Empty set (0.00 sec)
18 #登录数据库(db03)
19 [root@mysql-db03 ~]# mysql -uroot -poldboy123
20 #查看slave状态
21 mysql> show slave status\G

*************************** 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

 

两台机器生成自身的公钥音讯:ssh-keygen -t rsa

五、VIP漂移

赶上的题材:

以一台机械为例,34正片自个儿的公钥到其余机器:

1.vip漂移的三种办法

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

一先导服务不能运转,出现错误日志proxy-plugin.c.1783: I have no server
backend, closing
connection,是因为安排的user音信在每一种节点不平等导致的。
后来修改pwds
节点的user消息,其中user的新在每二个节点都同一,包罗用户名和密码,服务不荒谬运行。

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

2.MHA自带脚本办法

997755.com澳门葡京 8

下一场实施授权语句:

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

 

chmod 600 /root/.ssh/authorized_keys

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 "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

    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=port\n";
}

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

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

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

一连可以品味,在MG福睿斯的底蕴上做贰个依照keepalived的机关故障转移,写节点可以依据VIP做活动故障转移,
接下来在此基础上,基于VIP+其余节点做读写分离,理论上得以周密地落到实处活动故障转移的高可用+读写分离。
如此那般YY起来的话,感觉那规范也略屌,自动故障转移有了,读写分离也有了,理论上,只要有二个现有的节点,都得以健康对外提供服务。

[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!

2.4测试VIP漂移

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

*************************** 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_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 status\G

*************************** 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_997755.com澳门葡京 ,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

997755.com澳门葡京 9

意识是没戏的,大家那里须求把团结的公钥消息参预到表达(两台机械都要履行):

六、binlog-server

 

[root@localhost .ssh]# cat id_rsa.pub >>authorized_keys

1.先决条件

  • mysql版本5.6上述,必须拉开GTID
  • mha版本0.56以上

 

重新实施就OK了

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/

altas详细布署参考:

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

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 &

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

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
[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

柒 、mysql读写分离中间件Atlas

自身那边创办了2个root@%的参天权力给MHA来拔取。由于大家要是一主两从是已经搭建好的,具体授权什么的也不在赘述了。相信大家布置MHA的话对于那个小难题都是小五官科了。

1.简介

  Atlas是由 Qihoo
360合营社Web平台部基础架构团队开发体贴的一个基于MySQL协议的数量中间层项目。它在MySQL官方推出的MySQL-Proxy
0.8.2本子的根底上,修改了大量bug,添加了诸多成效特色。它在MySQL官方推出的MySQL-Proxy
0.8.2本子的底子上,修改了大量bug,添加了不少意义特色。

3:大家也可以检测一下复制的题材。

2.重大成效

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

只是在此之前要设置大家的从库read_only=1;

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

  • 将主流程中具备Lua代码用C重写,Lua仅用于管理接口
  • 重写互连网模型、线程模型
  • 落到实处了真正意义上的连接池
  • 优化了锁机制,质量进步数十倍
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'

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%]

接下来实施检查:

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
[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.

6.启动

1 [root@mysql-db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
2 OK: MySQL-Proxy of test is started

咱俩看看复制是OK
的,那里大家诠释掉了#master_ip_failover_script,按照本身看大师兄的博客里面所说MHA的Failover有三种艺术:一种是杜撰IP地址,一种是全局配置文件。MHA并从未限定使用哪种办法,而是让用户本身挑选,虚拟IP地址的不二法门会拉扯到任何的软件,比如keepalive软件,而且还要修改脚本master_ip_failover。所以说我们这边先注释掉那块。

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

  — 欢迎大家随时来交换

  —原创小说,允许转载,转发时请务必以超链接格局讲明小说 原始出处
、小编消息和本注明。否则将探究法律权利。

即使如此一度打响了,不过有多个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 "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

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=port\n";
}

接下来大家手动在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

从库: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博客: 

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website