澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:MG普拉多的读写分离,My

2019-07-28 作者:数据库网络   |   浏览(107)
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。MG奥迪TT RS为多主形式。大家挂念选择中间件能够落到实处MG牧马人的读写分离。中间件选型为Atlas,Atlas是360团队开源的一套基于MySQL-Proxy基础之上的代理,修改了MySQL-Proxy的有的BUG,况且打开了优化。

 

一、意况希图

澳门新萄京官方网站 1

MHA算是标准相比成熟的MySQL高可用搞定方案,在MySQL故障切换进程中,MHA能不负众望自动完毕数据库的故障切换操作,而且在举行故障切换的进程中,MHA能在最大程度上保证数据的一致性,以实现确实意义上的高可用。软件首要有MHA Manager(管理节点)和MHA Node(数据节点)两片段构成,在MHA自动故障切换进度中,MHA试图从宕机的主服务器上保留二进制日志,最大程度的保证数据的不放弃,但那并不总是低价的。比方,假设主服务器硬件故障或十分小概通过ssh访谈,MHA没有办法保存二进制日志,只进行故障转移而错失了新式的多寡。使用MySQL 5.5的半联袂复制,能够大大减弱数据错失的风险。MHA能够与半联手复制结合起来。若是唯有三个slave已经取出了新星的二进制日志,MHA能够将流行的二进制日志应用于任何兼具的slave服务器上,因此可以保障具备节点的多少一致性。

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

 

大家看一下,安装完Atlas 未来会生成多少个文本夹。首先大家要配置密码的加密:

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

 

本身的root@%账号密码就是123456,比较轻松。大家先记下这么些加密字符串,等会要在布署文件中配备。配置配置文件

[root@YC-ss1 conf]# cp test.cnf atlas.cnf
[root@YC-ss1 conf]# cat atlas.cnf 
[mysql-proxy]


#带#号的为非必需的配置项目

#管理接口的用户名
admin-username = user

#管理接口的密码
admin-password = pwd

#Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
proxy-backend-addresses = 172.16.128.239:3306

#Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
proxy-read-only-backend-addresses = 172.16.128.240:3306,172.16.128.241:3306,172.16.128.242:3306

#用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
pwds = root:/iZxz 0GRoA=

#设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
daemon = true

#设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
keepalive = true

#工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
event-threads = 16

#日志级别,分为message、warning、critical、error、debug五个级别
log-level = message

#日志存放的路径
log-path = /usr/local/mysql-proxy/log

#SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
#sql-log = OFF

#慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。
#sql-log-slow = 10

#实例名称,用于同一台机器上多个Atlas实例间的区分
#instance = test

#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:6002

#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:6003

#分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
#tables = person.mt.id.3

#默认字符集,设置该项后客户端不再需要执行SET NAMES语句
#charset = utf8

#允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1

#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
#lvs-ips = 192.168.1.1

 

 

启动Atlas

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

 

OK,今后大家的Atlas已经起步了。大家登陆查看一下音讯:

[root@YC-ss1 conf]#  mysql -h127.0.0.1 -P6003 -uuser -ppwd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
(user@127.0.0.1) [(none)]> SELECT * FROM help
    -> ;
 ---------------------------- --------------------------------------------------------- 
| command                    | description                                             |
 ---------------------------- --------------------------------------------------------- 
| SELECT * FROM help         | shows this help                                         |
| SELECT * FROM backends     | lists the backends and their state                      |
| SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id     | online backend server, ...                              |
| ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
| ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
| SELECT * FROM clients      | lists the clients                                       |
| ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
| REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
| SELECT * FROM pwds         | lists the pwds                                          |
| ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
| ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
| REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
| SAVE CONFIG                | save the backends to config file                        |
| SELECT VERSION             | display the version of Atlas                            |
 ---------------------------- --------------------------------------------------------- 
16 rows in set (0.00 sec)

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

 

接下来选用服务端口访问一下:

[root@YC-ss1 ~]# mysql -h127.0.0.1 -P6002 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.0.81-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

(root@127.0.0.1) [(none)]> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| mxqteset           |
| mysql              |
| performance_schema |
| sys                |
 -------------------- 
5 rows in set (0.00 sec)

(root@127.0.0.1) [(none)]> select @@server_id;
 ------------- 
| @@server_id |
 ------------- 
|  1282403306 |
 ------------- 
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> select @@server_id;
 ------------- 
| @@server_id |
 ------------- 
|  1282413306 |
 ------------- 
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> select @@server_id;
 ------------- 
| @@server_id |
 ------------- 
|  1282423306 |
 ------------- 
1 row in set (0.00 sec)

 

能够见到大家询问了一次。已经路由到了MG帕杰罗的四个节点上了。读已经是一直不难点的。写的话都会由此VIP:172.16.128.239来写。何况大家的VIP是会自动故障转移,也便是说保障是可写的。那样基本上就贯彻的读写分离。如若大家不想让240那台机械发出读,就足以把优先级配置的低一些,优先路由倒别的的机械。

指标是尝试altas的读写分离,现成一套搭建好做测量检验的MGKoleos(单主),于是就腿搓绳,在MG昂Cora基础上搭建altas。

1.mysql-db01

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

图形源于互联网

此时此刻MHA首要帮衬一主多从的架构,要搭建MHA,供给一个复制集群中务必至少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,另外一台充当从库,因为至少要求三台服务器。

3:压力测试

笔者们应用tpcc-mysql举办测量检验。先进行设置,下载地址:

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

 

初叶化压测数据:

(root@127.0.0.1) [tpccmysql]> create database tpccmysql;
Query OK, 1 row affected (0.02 sec)

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

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.18 sec)

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

Query OK, 0 rows affected (0.00 sec)

 

可是从未外键的话是无法压测的,及时压测非常多也是没戏的。唉,坑爹,幸亏有二个TPCC去除了这一个之外键的,这么些应该是叶大大搞的,github地址:

咱俩应用去除此之外键的TPCC进行压测,先实行安装

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

 

早先化数据

(root@127.0.0.1) [(none)]> create database tpcc;
Query OK, 1 row affected (0.07 sec)

(root@127.0.0.1) [(none)]> use tpcc
Database changed
(root@127.0.0.1) [tpcc]> source /home/tpccmysql/tpcc-mysql-autoinc-pk-master/create_table-autoinc-pk.sql;

 

使用tpcc_load加载数据:

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

 

以此历程有一点悠久,可以去楼下抽支烟。然后开始展览压力测量试验,测量试验贰个小时,这一个时间也可能有一些久,能够下楼买个冰棍了。

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

 

等待擦测量试验结果

STOPPING THREADS........

<Raw Results>
  [0] sc:24209  lt:1  rt:0  fl:0 
  [1] sc:24167  lt:0  rt:0  fl:0 
  [2] sc:2421  lt:1  rt:0  fl:0 
  [3] sc:2420  lt:0  rt:0  fl:0 
  [4] sc:2423  lt:0  rt:0  fl:0 
 in 3600 sec.

<Raw Results2(sum ver.)>
  [0] sc:24209  lt:1  rt:0  fl:0 
  [1] sc:24210  lt:0  rt:0  fl:0 
  [2] sc:2421  lt:1  rt:0  fl:0 
  [3] sc:2421  lt:0  rt:0  fl:0 
  [4] sc:2423  lt:0  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.43% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 100.00%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 99.96%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 403.500 TpmC

 

我们能够观望在自身那几个等第压测下,Atlas MGCR-V还是很平稳的。上边我们看下故障转移

 

2.mysql-db02

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

文/Bruce.Liu1

上边大家就起来出手安顿大家的MHA高可用,因为小编只有两台设想机,所以就只能遵照两台来搞了,中间也踩了点坑,下边看一下我们的主干条件:

4:故障转移的测验

往上边回想一下我们的机器意况

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

当今我们Atlas提供的劳动端口是172.16.128.242:6002,大家模拟NODE1节点故障 ,然后看一下压测的结果最后结果是什么的。

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

RAMP-UP TIME.(120 sec.)

 

第二部关闭NODE1,

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

 

整整都TM的gone away了,约等于没办法的,大家在重新开首压测,发掘是没难题的,

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

RAMP-UP TIME.(120 sec.)

MEASURING START.

  10, 905(0):1.694|4.648, 905(0):0.449|4.042, 91(0):0.217|0.562, 90(0):2.178|3.151, 90(0):5.098|10.983
  20, 724(0):1.750|2.948, 725(0):0.548|1.545, 73(0):0.173|0.230, 72(0):1.947|2.309, 73(0):4.700|5.217
  30, 1050(0):1.746|4.647, 1046(0):0.372|0.746, 104(0):0.163|0.258, 105(0):1.843|1.966, 105(0):5.323|5.616
  40, 917(0):1.754|4.656, 918(0):0.392|0.985, 92(0):0.190|0.191, 91(0):2.042|2.340, 92(0):5.574|6.931
  50, 951(0):1.688|2.792, 950(1):0.440|5.618, 96(0):0.206|0.373, 96(0):2.240|3.440, 95(0):4.809|4.985
  60, 965(0):1.764|2.737, 966(0):0.393|0.836, 96(0):0.222|0.517, 97(0):2.220|2.448, 95(0):4.989|4.990
  70, 940(0):1.589|2.525, 941(0):0.463|1.766, 94(0):0.164|0.263, 94(0):2.446|3.339, 96(0):5.512|5.704
  80, 746(2):1.671|7.135, 742(1):0.445|5.139, 75(0):0.229|0.324, 74(0):2.353|2.653, 74(0):5.342|5.454
  90, 1022(0):1.784|4.030, 1022(0):0.421|1.311, 102(0):0.311|0.479, 102(0):2.093|3.696, 102(0):5.810|6.025
 100, 1012(0):1.809|2.774, 1012(0):0.436|0.961, 101(0):0.207|0.381, 102(0):2.135|2.262, 101(0):5.867|5.931
 110, 928(0):1.651|2.960, 930(0):0.397|1.347, 93(0):0.193|0.371, 92(0):2.157|3.873, 93(0):5.445|6.339
 120, 1000(0):1.670|2.486, 1001(0):0.408|0.888, 99(0):0.257|0.405, 100(0):1.943|2.189, 100(0):5.292|5.770
 130, 992(0):1.749|4.123, 990(0):0.463|1.120, 100(0):0.206|0.277, 99(0):1.742|1.759, 100(0):4.674|5.843
 140, 868(1):1.893|7.145, 864(0):0.447|1.527, 86(0):0.188|0.221, 87(0):2.262|2.292, 86(0):4.586|4.684
 150, 1024(0):1.641|2.351, 1027(0):0.415|0.680, 103(0):0.153|0.195, 102(0):1.936|5.337, 102(0):4.877|5.004

<Raw Results>
  [0] sc:28932  lt:6  rt:0  fl:0 
  [1] sc:28921  lt:2  rt:0  fl:0 
  [2] sc:2894  lt:0  rt:0  fl:0 
  [3] sc:2893  lt:0  rt:0  fl:0 
  [4] sc:2894  lt:0  rt:0  fl:0 
 in 300 sec.

<Raw Results2(sum ver.)>
  [0] sc:28932  lt:6  rt:0  fl:0 
  [1] sc:28934  lt:2  rt:0  fl:0 
  [2] sc:2894  lt:0  rt:0  fl:0 
  [3] sc:2893  lt:0  rt:0  fl:0 
  [4] sc:2894  lt:0  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.47% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 99.98%  [OK]
        Payment: 99.99%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 5787.600 TpmC

 

我们再看一下Atlas的田间管理音信

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

意识有毛病的NODE1节点的地方竟然如故UP,也正是说大家还要和谐想办法通告Atlas作者那一个节点挂了,要修改配置音讯。然而最起码表明读写分离是足以做到了。所以说那一个还是有一点不可相信的呀,大家不能够落到实处。我们选材mycat试试

复制景况计划

3.mysql-db03

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

小说大纲

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

安装使用mycat,贴一下mycat的配置文件

[root@YC-ss1 conf]# cat schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
        <table name="customer" primaryKey="aid"  dataNode="tp1" />
        <table name="district" primaryKey="aid"  dataNode="tp1" />
        <table name="history" primaryKey="aid"  dataNode="tp1" />
        <table name="item" primaryKey="aid"  dataNode="tp1" />
        <table name="new_orders" primaryKey="aid"  dataNode="tp1" />
        <table name="order_line" primaryKey="aid"  dataNode="tp1" />
        <table name="orders" primaryKey="aid"  dataNode="tp1" />
        <table name="stock" primaryKey="aid"  dataNode="tp1" />
        <table name="warehouse" primaryKey="aid"  dataNode="tp1" />
    </schema>


    <dataNode name="tp1" dataHost="dbhost239" database="tpcc" />

    <dataHost name="dbhost239" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="172.16.128.239:3306" user="root"
                        password="123456">
                </writeHost>
                <writeHost host="hostS1" url="172.16.128.240:3306" user="root"
                        password="123456" />
                <writeHost host="hostS2" url="172.16.128.241:3306" user="root"                      
                        password="123456" />
                <writeHost host="hostS3" url="172.16.128.242:3306" user="root"                      
                        password="123456" />
    </dataHost>

</mycat:schema>

 

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

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

 

接下来先压测:

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

 

看一下压测结果

 250, 1320(0):1.793|4.080, 1320(0):0.370|1.357, 133(0):0.160|0.185, 131(0):1.993|2.155, 132(0):2.476|3.372
 260, 1154(0):1.695|3.316, 1153(0):0.371|1.664, 114(0):0.138|0.144, 116(0):1.936|3.117, 114(0):3.079|3.506
 270, 1076(0):1.780|3.807, 1076(0):0.359|0.706, 108(0):0.155|0.195, 107(0):1.830|3.335, 109(0):2.296|2.368
 280, 1094(0):1.682|2.435, 1092(0):0.362|1.352, 109(0):0.142|0.163, 110(0):1.963|2.259, 109(0):2.760|2.868
 290, 1264(0):1.668|2.191, 1269(0):0.339|0.744, 127(0):0.159|0.188, 127(0):1.847|1.943, 126(0):2.909|3.890
 300, 1169(0):1.605|4.055, 1166(0):0.336|1.421, 117(0):0.160|0.192, 117(0):1.874|3.247, 118(0):2.456|2.595

STOPPING THREADS........

<Raw Results>
  [0] sc:35560  lt:3  rt:0  fl:0 
  [1] sc:35563  lt:1  rt:0  fl:0 
  [2] sc:3557  lt:0  rt:0  fl:0 
  [3] sc:3557  lt:0  rt:0  fl:0 
  [4] sc:3557  lt:0  rt:0  fl:0 
 in 300 sec.

<Raw Results2(sum ver.)>
  [0] sc:35560  lt:3  rt:0  fl:0 
  [1] sc:35565  lt:1  rt:0  fl:0 
  [2] sc:3557  lt:0  rt:0  fl:0 
  [3] sc:3557  lt:0  rt:0  fl:0 
  [4] sc:3557  lt:0  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 99.99%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 7112.600 TpmC

 

是因为大多压测都以翻新数据,所以都路由到了239VIP上。我们得以一向测一下读取

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

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

 

读的央浼会路由倒三台机器上。今后若是240的主机挂掉,VIP239从240飘到241,大家看一下mycat会不会把连接有未有毛病。 首先给三个不住的压测,

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

 

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

1184, HY000, Connection refused
payment 7:1
1184, HY000, Connection refused
neword 4:1
1184, HY000, Connection refused
payment 6:1
1184, HY000, Connection refused
payment 5:1
1184, HY000, Connection refused
payment 1:1
1184, HY000, Connection refused
payment 0:1
1184, HY000, Connection refused
neword 3:1
1184, HY000, Connection refused
payment 6:1
1184, HY000, Connection refused
neword 4:1
1184, HY000, Connection refused
payment 7:1
1184, HY000, Connection refused
  70, 723(2):1.654|47.728, 726(5):0.655|76.966, 72(0):0.164|0.298, 72(0):1.957|2.127, 72(0):2.550|2.571
  80, 1178(0):1.769|2.702, 1182(0):0.416|0.870, 118(0):0.159|0.288, 117(0):1.938|1.971, 118(0):2.769|3.452
  90, 1332(0):1.766|4.103, 1330(0):0.425|1.254, 134(0):0.175|0.178, 133(0):2.207|2.644, 134(0):3.027|3.508
 100, 901(0):1.822|2.738, 902(0):0.431|1.109, 90(0):0.152|0.158, 90(0):2.040|2.049, 90(0):2.991|3.176
 -------
 <Raw Results>
  [0] sc:22875  lt:3  rt:81654  fl:40 
  [1] sc:22879  lt:5  rt:87541  fl:40 
  [2] sc:2288  lt:0  rt:9995  fl:5 
  [3] sc:2287  lt:0  rt:13993  fl:7 
  [4] sc:2291  lt:0  rt:5997  fl:3 
 in 240 sec.

<Raw Results2(sum ver.)>
  [0] sc:22881  lt:3  rt:81655  fl:40 
  [1] sc:22881  lt:5  rt:87552  fl:40 
  [2] sc:2288  lt:0  rt:9995  fl:5 
  [3] sc:2287  lt:0  rt:13993  fl:7 
  [4] sc:2291  lt:0  rt:5997  fl:3 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 99.99%  [OK]
        Payment: 99.98%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 5719.500 TpmC

 

可以看到,在keepalived切换进度中,连接是战败的,可是最后VIP切换来功现在全数的总是就见惯司空了。所以说采用MYCAT来落实读写分离是卓有成效的。但是也可以有叁个题目,session close的时候会不不奇怪,不过新链接能够有限协助是一向不难题的,从末了压测结果也是足以看出来的。可是从基本也满意了咱们的中坚供给。

读写分离理论上讲,跟复制格局尚未涉及,atlas担任的是重定向读写,至于复制情势自个儿选用,这里是测量检验景况,在此以前测量检验MG途乐的单机多实例,名爵LX570单主形式的复制形式,就顺手借助MGPRADO做依照atlas的读写分离。

二、安装mysql

1.读写分离简要介绍

我们假诺一主两从的情况大家早就搭建好了。

澳门新萄京官方网站 2

1.安装包希图

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

1.1.常见高并发场景

乘势二个网站的事务不停扩充,数据持续追加,数据库的下压力也会更加的大,对数据库只怕SQL的基本优化或然达不到最后的作用,此时能够虚构通过添加数据库节点来使其落成进步质量的指标一般有以下常见两种方案。

  • 读写分离
    张开三个帖子内容页,供给select帖子表,和帖子评论表,各样耗费时间10ms的话。每秒一千次查询就是以此数据库的极端了。也正是说,那些论坛只好承载每秒500次访谈。那么大家就能够对这些数据库做读写分离,来成倍增进数据库的读品质。

  • 水平分表
    贸易历史,这种表。因为非常少查询1年前的音信。所以,我们能够按年度来进展水平分表。将今年的表优先对待。就减弱了要查询的表的分寸。

  • 分库
    要是大家的系统相当大了,作用十分的多,就能有好多档期的顺序的多少库表,举个例子经营发卖活动的表,和用户账号的表是未有关系的,那么大家就足以将她们分到多少个数据库中,然后放到差异的独门的数据库服务器,就会使数据库吞吐量成倍扩展。

  • 垂直分表
    杰出的选择场景是在篇章列表那样的场景,一般来说,我们的稿子表会有title、userId、Content等字段,当中的Content字段一般是Text或许LongText类型,而别的的字段都是定点长度的数据类型。大家了然一个数据库优化准绳是:

    • 举例二个表的具有字段都以一向长度类型的,那么它正是定长表,定长表比动态长度表查询品质要高
    • 那正是说,我们就足以应用垂直分表来将小说表分成文章表和小说内容表。于是小说列表页面所需的询问,就只供给查询一张定长表了。
  • 引入Cache
    万般来说固然前面二个的数据库架构做得再好,对于定期抢购/抽取奖品/等,这种近乎高密度密集访谈的光景也是力所不如,最棒的措施依旧在服务层和多少库层增多八个缓存层用,使其让必要命中至缓存层,数据层只长久化更动的数码即可。

唯有两台机器,所以说凑合着用起来吧

 

2.安装

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

1.2.读写分离原理

MySQL的主从复制和MySQL的读写分离两者兼有紧凑联系,首先布署主从复制,独有主从复制完了,本领在此基础上进行数据的读写分离。简单的话,读写分离就是只在主服务器上写,只在从服务器上读,基本的准则是让主数据库处总管务性查询,而从数据库管理select查询。当业务量很大时,一台服务器的质量无法知足供给,就足以因而配备主从复制完成写分离来分担负载,制止因负载太高而招致无法立时响应央求。

1:首先大家要设置MHA的安装包,安装MHA从前,要安装一些依附情况

atlas安装

3.起动并加多开机自启

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

1.3.读写分离类型

  • 传闻程序代码内部贯彻
    在代码中依照select,insert进行路由分类,那类方法也是当前添丁条件应用最布满的,优点是性质好,因为在程序代码中一度将读写的数据源拆分至八个,所以无需额外的MySQL proxy解析SQL报文,在进展路由至差异数据库节点。劣势是常见该架构较复杂,运行费用绝对较高。

  • 基于中间代理层达成
    代理层一般位于客户端和服务器之间,代理服务器接到客户端诉求后透过深入分析SQL文本再将SQL路由至可用的数据库节点中。优点是先后无需改换能够完结无缝迁移,可移植性较好。短处是性质相对前面一个略微逊色一些,并且并非有着的读操作都能够被路由至从节点中。

NODE节点:

rpm安装,须臾间形成

4.设置root密码

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

2.Maxsacle

yum install -y perl-DBD-MySQL

澳门新萄京官方网站 3

三、主从复制

2.1.Maxsacle简介

澳门新萄京官方网站 4

图表来源网络

maxscale是mariadb集团费用的一套数据库中间件,能够很方便的落到实处读写分离方案;並且提供了读写分离的负荷均衡和高可用性保险。另外maxscale对于前端选用来讲是晶莹剔透的,大家可以很有利的将运用迁移到maxscale中贯彻读写分离方案,来平均分摊主库的下压力。maxscale也提供了sql语句的深入分析过滤效果。这里大家首要讲授maxscale的装置、配置以及注意事项。

  • Maxsacle特性

1.带权重的读写分离(负载均衡)
2.SQL防火墙
3.有余路由政策(Connection based, Statement based,Schema based)
4.自动检查评定MySQL master Failover (协作MHA只怕MRM)
5.检查评定中央延时
6.多租户sharding架构

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

 

atlas的配置文件(暗中认可/usr/local/mysql_proxy/conf/test.cnf),个人以为是多少个十分如沐春风的陈设,基本上配置节点的备注都非常理解。
因为是单机多实例,这里仅仅通过差别的端口号来区分从库实例,能够是单从,也得以是多从,根据事先级来拆分。

1.先决条件

  • 主库和从库都要开启binlog (因为从库有相当大希望被进步为主库,所以必须开启binlog)

  • 主库和从库server-id分化

  • 要有主从复制用户

2.2.Maxsacle安装

Manager节点:

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

2.主库操作

2.2.1.软件参谋文书档案

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

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

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

其一节点需求细心的是,对应的用户名必须在各样是行使都留存且密码一样,不然会油不过生谬误

2.1改造配置文件

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

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

  • maxSacle版本
    maxscale-2.0.5

 

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

2.2成立主从复制用户

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

不过这几个包大家系统是从未的,须要大家设置相应的epel第三方能源库,再安装,大家得以先去

[mysql-proxy]

#带#号的为非必需的配置项目

#管理接口的用户名
admin-username = user

#管理接口的密码
admin-password = pwd

#Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
proxy-backend-addresses = ***.***.***.***:8001

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

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

#设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
daemon = true

#设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
keepalive = true

#工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
event-threads = 8

#日志级别,分为message、warning、critical、error、debug五个级别
log-level = message

#日志存放的路径
log-path = /usr/local/mysql-proxy/log

#SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
#sql-log = OFF

#慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。
#sql-log-slow = 10

#实例名称,用于同一台机器上多个Atlas实例间的区分
#instance = test

#Atlas监听的工作接口IP和端口
proxy-address = ***.***.***.***:1234

#Atlas监听的管理接口IP和端口
admin-address = ***.***.***.***:2345

#分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
#tables = person.mt.id.3

#默认字符集,设置该项后客户端不再需要执行SET NAMES语句
#charset = utf8

#允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1

#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
#lvs-ips = 192.168.1.1

3.从库操作

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

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

启动atlas

3.1改造配置文件

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

注:在既往只若是基于binlog日志的主从复制,则必须求切记主库的master状态新闻。

 

1 mysql> show master status;

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

| File             | Position |

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

| mysql-bin.000002 |      120 |

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

 

 


 

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

澳门新萄京官方网站 5

4.开启GTID

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

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

 | Variable_name            | Value |

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

 | enforce_gtid_consistency | ON    | #执行GTID一致

 | gtid_executed            |       |

 | gtid_mode                | ON    | #开启GTID模块

 | gtid_owned               |       |

 | gtid_purged              |       |

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

 


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

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

 

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

安装完之后实践以下语句查看一下源:

 

5.安插主从复制

 1 #登录数据库
 2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
 3 #配置复制主机信息
 4 mysql> change master to
 5 #主库IP
 6 -> master_host='10.0.0.51',
 7 #主库复制用户
 8 -> master_user='rep',
 9 #主库复制用户的密码
10 -> master_password='oldboy123',
11 #GTID位置点
12 -> master_auto_position=1;
13 #开启slave
14 mysql> start slave;
15 #查看slave状态
16 mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51                                 #主库IP
                  Master_User: rep
                  Master_Port: 3306                                      #主库端口
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004                          #binlog名
          Read_Master_Log_Pos: 191
               Relay_Log_File: mysql-db02-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes                                       #IO线程状态
            Slave_SQL_Running: Yes                                       #SQL线程状态
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1       #GTID号
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1       #GTID号
                Auto_Position: 1
2.1.6.maxscale成立布局文件
vim /etc/maxscale4007.cnf 

[maxscale]
threads=auto    #开启线程个数,默认为1.设置为auto会同cpu核数相同 
maxlog=1        #将日志写入到maxscale的日志文件中
log_to_shm=0    #不将日志写入到共享缓存中,开启debug模式时可打开加快速度
log_warning=1   #记录告警信息 
log_error=1     #记录错误信息
log_notice=1    #记录notice
log_info=0      #不记录info
log_debug=0     #不记录debug
#log_augmentation=1
LimitNOFILE=65536
logdir=/data1/maxscale4007/log/  
datadir=/data1/maxscale4007/data/  
cachedir=/data1/maxscale4007/cache/  
piddir=/data1/maxscale4007/pid/  
libdir=/usr/lib64/maxscale/  
execdir=/usr/bin/  

[server1]
type=server
address=192.168.2.120
port=3389
protocol=MySQLBackend

[server2]
type=server
address=192.168.2.121
port=3389
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=scalemon
passwd=80068EB8F16774E59BECBF8EE23AD60D
monitor_interval=5000      #监控心跳为5秒
#detect_replication_lag=true  #监控主从复制延迟
#max_slave_replication_lag  #控制maxscale运行的最大延迟
detect_stale_master=true   #当复制slave全部断掉时,maxscale仍然可用,将所有的访问指向master节点

#[Read-Only Service]
#type=service
#router=readconnroute
#servers=server2
#user=user_maxscale
#passwd=A622DB34CBF413527DE9048EBC3FE42E66EF97D8DD167887F781C5108213FD21
#router_options=slave

#读写分离,用户需要有SELECT ON mysql.db;SELECT ON mysql.tables_priv;SHOW DATABASES ON *.*的权限  
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2
user=user_maxscale
passwd=41A9A7EFD164EF7C2D590F43F405E465
max_slave_connections=100%
use_sql_variables_in=master  #sql语句中的存在变量只指向master中执行
enable_root_user=1   #允许root用户登录执行
#master_accept_reads=true  #master节点也可以转发读请求 

[MaxAdmin Service]
type=service
router=cli

#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=3300

#读写分离服务端口
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4007

#管理服务端口
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=/data1/maxscale4007/maxadmin.sock
port=6604
[root@localhost yum.repos.d]# yum repolist
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* epel: mirror.lzu.edu.cn
repo id repo name status
base CentOS-6 - Base - 163.com 6,706
*epel Extra Packages for Enterprise Linux 6 - x86_64 12,305
extras CentOS-6 - Extras - 163.com 45
updates CentOS-6 - Updates - 163.com 318
yum yum 6,367
repolist: 25,741

测量检验读写分离

6.从库别的配置

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

2.3.Maxsacle配置

能够见到曾经有epel相关的能源了,所以大家就可以施行施行上边包车型地铁yum语句安装MHA的注重性子状。

MGRAV4是一主二从,主节点Server_id是8001,从节点的Server_id分别是8002和8003
能够发掘读消息重定向到8002节点,写音信重定向到8001节点,达成了读写分离

四、部署MHA

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

安装落成之后在两台机械安装NODE节点在master机器安装Manage:

澳门新萄京官方网站 6

1.蒙受打算(全部节点)

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

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

2.命令软连接

1 #如果不创建命令软连接,检测mha复制情况的时候会报错
2 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
3 [root@mysql-db01 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
2.3.3.maxscale劳务地方
MaxScale> list listeners
Listeners.
--------------------- -------------------- ----------------- ------- --------
Service Name         | Protocol Module    | Address         | Port  | State
--------------------- -------------------- ----------------- ------- --------
Read-Write Service   | MySQLClient        | *               |  4007 | Running
MaxAdmin Service     | maxscaled          | *               |  6604 | Running
MaxAdmin Service     | maxscaled          | /data1/maxscale4007/maxadmin.sock |     0 | Running
--------------------- -------------------- ----------------- ------- --------

MaxScale> list servers
Servers.
------------------- ----------------- ------- ------------- --------------------
Server             | Address         | Port  | Connections | Status              
------------------- ----------------- ------- ------------- --------------------
server1            | 192.168.2.120   |  3389 |           0 | Master, Running
server2            | 192.168.2.121   |  3389 |           0 | Slave, Running
------------------- ----------------- ------- ------------- --------------------

MaxScale> list services
Services.
-------------------------- ---------------------- -------- ---------------
Service Name              | Router Module        | #Users | Total Sessions
-------------------------- ---------------------- -------- ---------------
Read-Write Service        | readwritesplit       |      1 |     2
MaxAdmin Service          | cli                  |      3 |     3
-------------------------- ---------------------- -------- ---------------

show services
Service 0x18d6330
    Service:                             Read-Write Service
    Router:                              readwritesplit (0x7f17ce109d40)
    State:                               Started
    Number of router sessions:              1
    Current no. of router sessions:         1
    Number of queries forwarded:            1
    Number of queries forwarded to master:  0 (0.00%)
    Number of queries forwarded to slave:   1 (100.00%)
    Number of queries forwarded to all:     0 (0.00%)
    Started:                             Sat Jan  6 09:43:33 2018
    Root user access:                    Enabled
    Backend databases:
        192.168.2.120:3389  Protocol: MySQLBackend
        192.168.2.121:3389  Protocol: MySQLBackend
    Users data:                          0x18eb920
    Total connections:                   2
    Currently connected:                 2
Service 0x18d4870
    Service:                             MaxAdmin Service
    Router:                              cli (0x7f17ce313560)
    State:                               Started
    Started:                             Sat Jan  6 09:43:33 2018
    Root user access:                    Disabled
    Backend databases:
    Users data:                          0x18ed620
    Total connections:                   3
    Currently connected:                 3

作者这边包是现已下载好的,直接使用rpm安装了。至此算是安装收尾了

澳门新萄京官方网站 7

3.管制节点(mha-manager

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

maxScale注意:

  • 创制链接的时候,不帮忙压缩协议
  • 转载路由不可能动态的分辨master节点的动员搬迁
  • LONGLOB字段不帮衬
  • 在曾几何时动静会将语句转到master节点中(保障工作一致):
    明朗钦命工作;
    prepared的语句;
    语句中含有存款和储蓄进程,自定义函数
    满含多条语句音讯:INSERT INTO ... ; SELECT LAST_INSERT_ID();
  • 有些语句默许会发送到后端的保有server中,不过足以钦定
    use_sql_variables_in=[master|all] (default: all)
  • maxscale不扶助主机名相称的辨证格局,只支持IP地址格局的host分析。所以在增添user的时候记得使用合适的范式。
  • 跨库查询不帮忙,会来得的钦定到第4个数据库中

粗略介绍一下MHA的Manager工具包和Node工具包

读重定向到8003节点,写还是是主节点,名爵景逸SUV状态也健康,假诺尝试关闭全体的读节点,读将自动重定向到主(写)节点,表达从节点的失实都以足以相称的。
这点证实,从(读)节点的别的不当都以不影响atlas对外提供服务器的,假如产生主节点的高可用,atlas就能够圆随处对外提供劳务了。

3.1在mysql-db03上安装管理节点

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

3.Keepalived lvs

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

澳门新萄京官方网站 8

4.布局文件

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

【配置文件详解】

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

3.1.Keepalived lvs简介

在网络使用能力中,负载均衡一向是火爆话题,使用负载均衡本事首要的目标包含如下几点:

  • 高可用性:
    结合系统的一点设备或部件失效,并不会影响寻常的劳务

  • 可增加性:
    用户的充实,引起访谈数 以致流量的增加,这种意况下,须求对系统实行扩大体积,以应对这种神速增加,何况普通这场馆中最宗旨扩充供给正是对用户是晶莹剔透的

  • 负载才具:
    当全部系统的担任趋于饱和时,通过扩展物理服务器和扩展物理带宽来缓和这一个麻烦。增添物理服务器后,系统的负载景况将要具备集群的情理服务器之间遵照内定的算法从新完结新的动态平衡

虚构服务器是负载均衡连串的基本架构,分为:转载器(Director)和实在服务器。

澳门新萄京官方网站 9

图形来自keepalived官方网址

  • 转发器(Director):
    要害担当包转载、负载均衡,该意义通过ipvsadm具体贯彻。

  • 实际服务器(Real):
    当真接受并管理央求的服务器,日常泛指:nginx、mysql等差异情形等服务

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

Atlas中间件会活动过滤掉一部分快要灭亡的操作,比方不带where条件的delete就不能够实施

5.ssh-key(全部节点)

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

3.2.Keepalived lvs安装

Node工具包(那个工具日常由MHA Manager的脚本触发,没有要求人工操作)首要满含以下多少个工具:

澳门新萄京官方网站 10

6.起动前测量检验

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

参谋文书档案:
官方文书档案:http://www.keepalived.org/pdf/sery-lvs-cluster.pdf

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

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

尚无进展分表测量检验。

7.启动MHA

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

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

  • keepalived版本
    keepalived-1.2.13-5

  • 架构暗中提示图

澳门新萄京官方网站 11

图形来源于原创

2:配置主机SSH免密登陆

 

8.failover测试

 1 #登录数据库(db02)
 2 [root@mysql-db02 ~]# mysql -uroot -poldboy123
 3 #检查复制情况
 4 mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 191
               Relay_Log_File: mysql-db02-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
                Auto_Position: 1
 5 #登录数据库(db03)
 6 [root@mysql-db03 ~]# mysql -uroot -poldboy123
 7 #检查复制情况
 8 mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 191
               Relay_Log_File: mysql-db02-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
 9 #停掉主库
10 [root@mysql-db01 ~]# /etc/init.d/mysqld stop
11 Shutting down MySQL..... SUCCESS!
12 #登录数据库(db02)
13 [root@mysql-db02 ~]# mysql -uroot -poldboy123
14 #查看slave状态
15 mysql> show slave statusG
16 #db02的slave已经为空
17 Empty set (0.00 sec)
18 #登录数据库(db03)
19 [root@mysql-db03 ~]# mysql -uroot -poldboy123
20 #查看slave状态
21 mysql> show slave statusG

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.0.52

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 191

               Relay_Log_File: mysql-db03-relay-bin.000002

                Relay_Log_Pos: 361

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 1883
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 722cdde9-0272-11e7-a825-000c2951d7ad
             Master_Info_File: /application/mysql-5.6.16/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
            Executed_Gtid_Set: 722cdde9-0272-11e7-a825-000c2951d7ad:1
3.2.3.lvs客户端
  • 忠实服务器上操作(slave1 slave2),参照格式编写lvs 客户端配置脚本,保存后予以实行权限。其完整的剧情如下:
# vim /usr/local/bin/lvs_real

#!/bin/bash
#description : start realserver

VIP=192.168.2.210

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

case "$1" in
start)
        echo " start LVS of REALServer"
        /sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 up
        echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
        echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
        echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
        echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
        ;;
stop)
        /sbin/ifconfig lo:0 down
        echo "close LVS Directorserver"
        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
        echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
        echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
        ;;
*)
        echo "Usage: $0 {start|stop}"
        exit 1
esac
  • 开诚相见服务器上操作(slave1 slave2),运转lvs client并配备成开机自运行
# chmod 755 /usr/local/bin/lvs_real
# chmod 755 /etc/rc.d/init.d/functions
# /usr/local/bin/lvs_real start
# echo "/usr/local/bin/lvs_real start" >> /etc/rc.d/rc.local
  • 验证lvs client
# ifconfig

… …  省略  … …
lo:0      Link encap:Local Loopback  
          inet addr:10.209.5.201  Mask:255.255.255.255
          UP LOOPBACK RUNNING  MTU:16436  Metric:1

由于自身这两台测量检验机是从运营手里申请的,折腾过来配置SSH浪费了许多时日,并且本人那边如故两台server取代MHA的一主两从一管制的四台机械,中间依然略微难点的

 

五、VIP漂移

3.2.4.装置配备keepalived
  • 安装ipvs
    Ipvs(IP Virtual Server)是任何负载均衡的底子,如果没有这么些基础,故障隔开分离与切换就毫无意义;Ipvs具体是由ipvsadm这几个顺序来成功的。因而供给安装ipvsadm
# yum -y install ipvsadm-1.26-4.el6.x86_64
  • 安装keepalived软件
    负载均衡器上操作(Master Backup)安装keepalived和ipvsadm工具包
# yum -y install keepalived.x86_64
  • 开启路由转载
    负载均衡器上操作(Master Backup),开启路由转载
# echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
# sysctl -p
  • Master的铺排文件
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   router_id lvs_mysql_3389
}

vrrp_instance vi_3389 { 
    state MASTER
    interface eth0
    lvs_sync_daemon_inteface eth0
    virtual_router_id 11
    priority 90
    nopreempt
    advert_int 1 
    authentication { 
        auth_type PASS 
        auth_pass mysql3389
    } 
    virtual_ipaddress { 
        192.168.2.210
    } 
}

virtual_server 192.168.2.210 3389 { 
    delay_loop 2 
    lb_algo wrr
    lb_kind DR 
#    persistence_timeout 60 
    protocol TCP 

    real_server 192.168.2.120 3389 {
        weight 3 
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3389
        }
    }

    real_server 192.168.2.121 3389 {
        weight 3 
    TCP_CHECK {                 
            connect_timeout 10      
            nb_get_retry 3          
            delay_before_retry 3    
            connect_port 3389
        }
    } 

}
  • Backup的计划文件
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   router_id lvs_mysql_3389
}

vrrp_instance vi_3389 { 
    state MASTER
    interface eth0
    lvs_sync_daemon_inteface eth0
    virtual_router_id 11
    priority 10
    nopreempt
    advert_int 1 
    authentication { 
        auth_type PASS 
        auth_pass mysql3389
    } 
    virtual_ipaddress { 
        192.168.2.210
    } 
}

virtual_server 192.168.2.210 3389 { 
    delay_loop 2 
    lb_algo wrr
    lb_kind DR 
#    persistence_timeout 60 
    protocol TCP 

    real_server 192.168.2.120 3389 {
        weight 3 
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3389
        }
    }

    real_server 192.168.2.121 3389 {
        weight 3 
    TCP_CHECK {                 
            connect_timeout 10      
            nb_get_retry 3          
            delay_before_retry 3    
            connect_port 3389
        }
    } 

}
  • 开发银行服务并证实
    早先keepalived服务并证实(Master Backup)
# /etc/init.d/keepalived start
# chkconfig keepalived on

两台机器生成自个儿的公钥音信:ssh-keygen -t rsa

欣逢的难题:

1.vip漂移的三种办法

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

3.3.Keepalived lvs高可用验收

  • keepalived故障测量试验

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

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

将slave1 slave2主机举行重启,用于模拟宕机,测验是还是不是会将央浼转发至正规的Master1

  • Slave1重启机器
# init 6
  • Slave2重启机器
# init 6
  • 负载局衡器上表达转载链路状态,链路不奇怪能够延续Master
# ipvsadm -L
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  10.209.5.201:chevinservices wrr
  -> 10.209.5.191:chevinservices  Route   3      0          0
  • MHA故障切换对LVS的影响测量试验

  • MySQL Master1重启机器

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

假设MySQL 原本的Master 是重启的事态,负载均衡器还有恐怕会将呼吁转载至原Master,不会去检查测量试验原Master的复制状态以及是或不是有延迟的,以下是原Master起来后,负载均衡器上的链路状态

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

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

一开端服务不或然运行,出现错误日志proxy-plugin.c.1783: I have no server backend, closing connection,是因为安排的user新闻在每种节点差异导致的。
新兴涂改pwds 节点的user消息,当中user的新在每三个节点都一律,包涵用户名和密码,服务平常运营。

2.MHA自带脚本办法

3.4.Keepalived lvs运转注意事项

  • 线上更动注意事项

线上keepalived意况(基于端口探测方式)不会检讨MySQL复制链路以及延缓情形,所以对于大数目量表的DDL更换,最佳是放在夜晚还是业务低峰期实行,复制的那个也要立马的拍卖,防止因数量延迟或许复制不联合导致业务读取过期的多少

  • keepalived环境slave扩容
    1.配备新的slave
    2.改造Mha配置文件,将新增加多slave也增进,并设置成永不成为master
    3.在keepalived的Backup节点上增多新slave的布局标签,并将keepalived Master的keepalived服务甘休,此时keepalive的劳务会切换成Backup节点上,将master的配置文件在举行翻新,并把服务运营,那样就完了了online的扩大容积职业

  • 主干切换后的注意事项
    1.第一检查MHA切换是或不是中标、除原主以外的其余slave是或不是change成功到切到新master上
    2.在负载均衡器上是或不是检查链路是不是符合规律(ipvsad –L),而且在主导切换后须求求手动测验链路是不是通畅,因为部分时候master的互联网服务相当等网络状态会挑起lvs client工作十二分,若是手动链接链路非常而且负载均衡器上链路不荒谬,请第一时间去该MySQL服务器上运行lvs client 程序)( lvs_real start)

  • lvs client的保卫安全与停机
    当MySQL的slave服务器由于硬件故障恐怕切换导致急需大量刻钟用于停机维护时,登陆到停机维护的设备上施行“/usr/local/bin/maintenance_mode.sh 10.209.5.194 3349”,脚本有八个参数:禁止访问的IP、禁止访谈的端口,一般来说就是禁止keepalived server转载过来的MySQL 端口对应的恳求。脚本内容见附录。

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

澳门新萄京官方网站 12

2.1退换配置文件

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

附录

  • Keepalived配置文件详解
global_defs {
   notification_email {  #指定keepalived在发生切换时需要发送email到的对象,一行一个
    sysadmin@fire.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc #指定发件人
   smtp_server localhost #指定smtp服务器地址
   smtp_connect_timeout 30 #指定smtp连接超时时间
   router_id LVS_DEVEL #运行keepalived机器的一个标识
}
vrrp_sync_group VG_1{ #监控多个网段的实例
group {
inside_network #实例名
outside_network
}
notify_master /path/xx.sh #指定当切换到master时,执行的脚本
netify_backup /path/xx.sh #指定当切换到backup时,执行的脚本
notify_fault "path/xx.sh VG_1" #故障时执行的脚本
notify /path/xx.sh 
smtp_alert #使用global_defs中提供的邮件地址和smtp服务器发送邮件通知
}
vrrp_instance inside_network {
    state BACKUP #指定那个为master,那个为backup,如果设置了nopreempt这个值不起作用,主备考priority决

定
    interface eth0 #设置实例绑定的网卡
    dont_track_primary #忽略vrrp的interface错误(默认不设置)
    track_interface{ #设置额外的监控,里面那个网卡出现问题都会切换
    eth0
    eth1
    }
    mcast_src_ip #发送多播包的地址,如果不设置默认使用绑定网卡的primary ip
    garp_master_delay #在切换到master状态后,延迟进行gratuitous ARP请求
    virtual_router_id 50 #VPID标记
    priority 99 #优先级,高优先级竞选为master
    advert_int 1 #检查间隔,默认1秒
    nopreempt #设置为不抢占 注:这个配置只能设置在backup主机上,而且这个主机优先级要比另外一台高
    preempt_delay #抢占延时,默认5分钟
    debug #debug级别
    authentication { #设置认证
        auth_type PASS #认证方式
        auth_pass 111111 #认证密码
    }
    virtual_ipaddress { #设置vip
        192.168.202.200
    }
}
virtual_server 192.168.202.200 23 {
    delay_loop 6 #健康检查时间间隔
    lb_algo rr  #lvs调度算法rr|wrr|lc|wlc|lblc|sh|dh
    lb_kind DR  #负载均衡转发规则NAT|DR|RUN
    persistence_timeout 5 #会话保持时间
    protocol TCP #使用的协议
    persistence_granularity <NETMASK> #lvs会话保持粒度
    virtualhost <string> #检查的web服务器的虚拟主机(host:头)    
    sorry_server<IPADDR> <port> #备用机,所有realserver失效后启用
real_server 192.168.200.5 23 {
            weight 1 #默认为1,0为失效
            inhibit_on_failure #在服务器健康检查失效时,将其设为0,而不是直接从ipvs中删除 
            notify_up <string> | <quoted-string> #在检测到server up后执行脚本
            notify_down <string> | <quoted-string> #在检测到server down后执行脚本

TCP_CHECK {
            connect_timeout 3 #连接超时时间
            nb_get_retry 3 #重连次数
            delay_before_retry 3 #重连间隔时间
            connect_port 23  健康检查的端口的端口
            bindto <ip>   
          }
HTTP_GET | SSL_GET{
    url{ #检查url,可以指定多个
         path /
         digest <string> #检查后的摘要信息
         status_code 200 #检查的返回状态码
        }
    connect_port <port> 
    bindto <IPADD>
    connect_timeout 5
    nb_get_retry 3
    delay_before_retry 2
}

SMTP_CHECK{
    host{
    connect_ip <IP ADDRESS>
    connect_port <port> #默认检查25端口
    bindto <IP ADDRESS>
         }
    connect_timeout 5
    retry 3
    delay_before_retry 2
    helo_name <string> | <quoted-string> #smtp helo请求命令参数,可选
}
MISC_CHECK{
    misc_path <string> | <quoted-string> #外部脚本路径
    misc_timeout #脚本执行超时时间
    misc_dynamic #如设置该项,则退出状态码会用来动态调整服务器的权重,返回0 正常,不修改;返回1,

检查失败,权重改为0;返回2-255,正常,权重设置为:返回状态码-2
}
    }



然后推行授权语句:

 

2.2编纂脚本

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

【脚本内容如下】

#!/usr/bin/env perl

use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
    $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
);

my $vip = '10.0.0.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down";

GetOptions(
    'command=s'          => $command,
    'ssh_user=s'         => $ssh_user,
    'orig_master_host=s' => $orig_master_host,
    'orig_master_ip=s'   => $orig_master_ip,
    'orig_master_port=i' => $orig_master_port,
    'new_master_host=s'  => $new_master_host,
    'new_master_ip=s'    => $new_master_ip,
    'new_master_port=i'  => $new_master_port,
);

exit &main();

sub main {

    print "nnIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===nn";

    if ( $command eq "stop" || $command eq "stopssh" ) {

        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {

        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host n";
            &start_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
        print "Checking the Status of the script.. OK n";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
}
sub stop_vip() {
     return 0  unless  ($ssh_user);
    `ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;
}

sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=portn";
}
chmod 600 /root/.ssh/authorized_keys

依赖MG索罗德 Keepalived Atlas的高可用加读写分离

2.3手动绑定VIP

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

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

接二连三能够品味,在MG讴歌RDX的基础上做三个依据keepalived的电动故障转移,写节点能够依赖VIP做活动故障转移,
接下来在此基础上,基于VIP 其余节点做读写分离,理论上得以周密地贯彻机关故障转移的高可用 读写分离。
如此YY起来的话,以为那样子也略屌,自动故障转移有了,读写分离也可以有了,理论上,只要有贰个存活的节点,都足以健康对外提供劳动。

2.4测试VIP漂移

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

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.0.51

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 191

               Relay_Log_File: mysql-db02-relay-bin.000002

                Relay_Log_Pos: 361

        Relay_Master_Log_File: mysql-bin.000007

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

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

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.0.52

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 191

               Relay_Log_File: mysql-db03-relay-bin.000002

                Relay_Log_Pos: 361

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

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

澳门新萄京官方网站 13

六、binlog-server

发觉是失利的,我们这里必要把自个儿的公钥新闻加入到表达(两台机械都要实施):

 

1.先决条件

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

 

2.改变配置文件

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

再一次试行就OK了

altas详细安插参谋:

3.备份binlog

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

4.测试binlog-server

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

此地运用到了MHA的铺排文件,我们贴一下:

七、mysql读写分离中间件Atlas

[root@localhost .ssh]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""
ssh_user=root
user=root

[server1]
hostname=172.16.16.34
port=3306

[server2]
hostname=172.16.16.35
port=3306
candidate_master=1
check_repl_delay=0

[server3]
hostname=172.16.16.35
port=3307

1.简介

  Atlas是由 Qihoo 360铺面Web平台部基础架构团队开拓爱戴的四个基于MySQL协议的数目中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的根底上,修改了大批量bug,加多了好些个效果特色。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基本功上,修改了大量bug,增添了无数效果与利益特色。

自己这里开创了二个root@%的最高权力给MHA来利用。由于大家假诺一主两从是一度搭建好的,具体授权什么的也不在赘述了。相信大家计划MHA的话对于这几个小标题都以小性病科了。

2.重大功能

  • 澳门新萄京官方网站,读写分离
  • 从库负载均衡
  • IP过滤
  • 电动分表
  • DBA可平滑上下线DB
  • 自动摘除宕机的DB

3:大家也足以检查实验一下复制的主题素材。

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

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

但是在此之前要安装大家的从库read_only=1;

4.安装

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

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

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

此间大家只须求下载普通的就能够。

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

5.配置文件

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

接下来实践行检查查:

6.启动

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

Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
Sat May 27 15:01:57 2017 - [info] ok.
Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
Sat May 27 15:01:57 2017 - [info] ok.
Sat May 27 15:01:57 2017 - [warning] master_ip_failover_script is not defined.
Sat May 27 15:01:57 2017 - [warning] shutdown_script is not defined.
Sat May 27 15:01:57 2017 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

7.管理操作

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

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

  | backend_ndx | address        | state | type |

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

  |           1 | 10.0.0.51:3307 | up    | rw   |

  |           2 | 10.0.0.53:3307 | up    | ro   |

  |           3 | 10.0.0.52:3307 | up    | ro   |

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

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

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

  | backend_ndx | address        | state | type |

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

  |           1 | 10.0.0.51:3307 | up    | rw   |

  |           2 | 10.0.0.52:3307 | up    | ro   |

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

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

 

 

 

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

  — By GoodCook

  — 笔者QQ:253097001

  — 应接大家时刻来交换

  —原创文章,允许转发,转发时请务必以超链接格局表明小说 原始出处 、小编音讯和本注明。不然将索求法律义务。

大家看到复制是OK 的,这里大家讲授掉了#master_ip_failover_script,依据自个儿看大师兄的博客里面所说MHA的Failover有两种格局:一种是编造IP地址,一种是大局配置文件。MHA并从未限定使用哪一类艺术,而是让用户本人选取,设想IP地址的不二诀窍会推来推去到别的的软件,比方keepalive软件,而且还要修改脚本master_ip_failover。所以说我们那边先注释掉那块。

即便已经成功了,不过有七个warning,因为那多个脚本大家还不曾定义,前边补上,先不管他

4:提起MHA

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

检查一下MHA的运作意况:

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

察觉是运转情状,评释运营是打响的,大家去看一下日志:

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

Sat May 27 15:50:48 2017 - [warning] master_ip_failover_script is not defined.
Sat May 27 15:50:48 2017 - [warning] shutdown_script is not defined.
Sat May 27 15:50:48 2017 - [info] Set master ping interval 1 seconds.
Sat May 27 15:50:48 2017 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s server03 -s server02
Sat May 27 15:50:48 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
Sat May 27 15:50:48 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

尚无难题。

即使大家向关闭的话也特别轻便

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

5:管理VIP:

我们地点已经说过了,有三种VIP的管理办法,一种是keepalived,一种是本子的措施管理VIP,keepalived的管住措施比较轻巧正是主节点和备用节点两台机器,监察和控制MySQL进度就好了,这么些和keepalived MySQL双主并未太大分别在布置方面,关于那几个布局能够看下笔者的上篇博客,博客地址:keepalived MySQL双主搭建

上面我们任重(英文名:rèn zhòng)而道远利用脚本的措施管理VIP,定义master_澳门新萄京官方网站:MG普拉多的读写分离,MySQL读写分离最好施行。ip_failover,大家这里直接行使大师兄的博客里面包车型地铁台本:

#!/usr/bin/env perl

use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);

my $vip = '172.16.16.20/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

GetOptions(
'command=s' => $command,
'ssh_user=s' => $ssh_user,
'orig_master_host=s' => $orig_master_host,
'orig_master_ip=s' => $orig_master_ip,
'orig_master_port=i' => $orig_master_port,
'new_master_host=s' => $new_master_host,
'new_master_ip=s' => $new_master_ip,
'new_master_port=i' => $new_master_port,
);

exit &main();

sub main {

print "nnIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===nn";

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {

my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK n";
exit 0;
}
else {
&usage();
exit 1;
}
}

sub start_vip() {
`ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;
}

sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=portn";
}

接下来大家手动在server1上增加虚构IP

/sbin/ifconfig eth0:1 172.16.16.20/24

双重谈起来MHA manager:

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

近日来讲,大家的MHA已经完全搭建起来了,上面测量检验一下故障转移看看有未有失水准:

(1)下边初始测验手动的故障转移:

手动故障转移:

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

实行到位后就能够看出主库的地点早已是172.16.16.35:3306,VIP:172.16.16.20也曾经改造来了172.16.16.35上了

(2)测量检验一下自行故障转移

前段时间我们再度做为主

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

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

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:MG普拉多的读写分离,My

关键词: