MHA+ProxySQL实现读写分离高可用

最近在研究ProxySQL，觉得还挺不错的，所以就简单的折腾了一下，ProxySQL目前也是Percona在推荐的一个读写分离的中间件。关于详细的介绍可以参考官方文档。https://github.com/sysown/proxysql/wiki

本文主要介绍的是MHA+ProxySQL读写分离以及高可用，ProxySQL的细节请参考文档，目前已经有人写的非常详细了，文章最后会给出链接。当然和Group Replication，PXC搭配那是更完美的。关于MHA的配置参考我前面的文章，本文就不再介绍。下面来看看

MHA和ProxySQL搭配使用的架构图：

测试环境（1主2从）：

M-> 192.168.0.20

S1-> 192.168.0.30

S2-> 192.168.0.40

proxysql -> 192.168.0.10

1. 检查主从复制是否正常：

[root@db_server_yayun_04 ~]# masterha_check_repl --conf=/data/mha//mha.cnf

Thu Jun  ::  - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Thu Jun  ::  - [info] Reading application default configuration from /data/mha//mha.cnf..

Thu Jun  ::  - [info] Updating application default configuration from /usr/local/bin/load_cnf..

Thu Jun  ::  - [info] Reading server configuration from /data/mha//mha.cnf..

Thu Jun  ::  - [info] Setting max_ping_errors to , ping_interval to .

Thu Jun  ::  - [info] MHA::MasterMonitor version 0.57.

Thu Jun  ::  - [info] GTID failover mode =

Thu Jun  ::  - [info] Dead Servers:

Thu Jun  ::  - [info] Alive Servers:

Thu Jun  ::  - [info]   192.168.0.20(192.168.0.20:)

Thu Jun  ::  - [info]   192.168.0.30(192.168.0.30:)

Thu Jun  ::  - [info]   192.168.0.40(192.168.0.40:)

Thu Jun  ::  - [info] Alive Slaves:

Thu Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Thu Jun  ::  - [info]     GTID ON

Thu Jun  ::  - [info]     Replicating from 192.168.0.20(192.168.0.20:)

Thu Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Thu Jun  ::  - [info]   192.168.0.40(192.168.0.40:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Thu Jun  ::  - [info]     GTID ON

Thu Jun  ::  - [info]     Replicating from 192.168.0.20(192.168.0.20:)

Thu Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Thu Jun  ::  - [info] Current Alive Master: 192.168.0.20(192.168.0.20:)

Thu Jun  ::  - [info] Checking slave configurations..

Thu Jun  ::  - [info] Checking replication filtering settings..

Thu Jun  ::  - [info]  binlog_do_db= , binlog_ignore_db=

Thu Jun  ::  - [info]  Replication filtering check ok.

Thu Jun  ::  - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Thu Jun  ::  - [info] Checking SSH publickey authentication settings on the current master..

Thu Jun  ::  - [info] HealthCheck: SSH to 192.168.0.20 is reachable.

Thu Jun  ::  - [info]

192.168.0.20(192.168.0.20:) (current master)

 +--192.168.0.30(192.168.0.30:)

 +--192.168.0.40(192.168.0.40:)

Thu Jun  ::  - [info] Checking replication health on 192.168.0.30..

Thu Jun  ::  - [info]  ok.

Thu Jun  ::  - [info] Checking replication health on 192.168.0.40..

Thu Jun  ::  - [info]  ok.

Thu Jun  ::  - [warning] master_ip_failover_script is not defined.

Thu Jun  ::  - [warning] shutdown_script is not defined.

Thu Jun  ::  - [info] Got exit code  (Not master dead).

MySQL Replication Health is OK.

[root@db_server_yayun_04 ~]#

2. 配置后端MySQL。登入ProxySQL，把MySQL主从的信息添加进去。将主库master也就是做写入的节点放到HG 100中，salve节点做读放到HG 1000。在proxysql输入命令：

mysql -uadmin -padmin -h127.0.0. -P6032

[admin@127.0.0.1][(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.168.0.20',3306,1,1000,10,'test my proxysql');

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.0.30',3306,1,1000,10,'test my proxysql');

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.0.40',3306,1,1000,10,'test my proxysql');

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> select * from mysql_servers;

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

| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment          |

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

| 100          | 192.168.0.20 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | test my proxysql |

| 1000         | 192.168.0.30 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | test my proxysql |

| 1000         | 192.168.0.40 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | test my proxysql |

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

3 rows in set (0.00 sec)

3. 配置后端MySQL用户。这个用户需要先在后端MySQL(20,30,40)里真实存在，一个是监控账号、一个是程序账号。
(1) 监控账号（用来监控后端mysql是否存活以及read_only变量）：

GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'192.168.0.10' IDENTIFIED BY 'proxysql';

(2) 程序账号（这里为了后面测试方便给了all权限）：

GRANT all ON *.* TO 'yayun'@'192.168.0.10' identified by 'yayun';

4. 在后端MySQL里添加完之后再配置ProxySQL：这里需要注意，default_hostgroup需要和上面的对应。（proxysql）

[admin@127.0.0.1][(none)]> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('yayun','yayun',1,100,1);

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> select * from mysql_users;

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

| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |

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

| yayun    | yayun    | 1      | 0       | 100               | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |

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

1 row in set (0.00 sec)

[admin@127.0.0.1][(none)]>

5. 设置健康监测账号（proxysql）：

[admin@127.0.0.1][(none)]> set mysql-monitor_username='proxysql';

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> set mysql-monitor_password='proxysql';

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]>

6. 加载配置和变量：因为修改了servers、users和variables，所以加载的时候要执行：

[admin@127.0.0.1][(none)]> load mysql servers to runtime;

Query OK, 0 rows affected (0.01 sec)

[admin@127.0.0.1][(none)]> load mysql users to runtime;

Query OK, 0 rows affected (0.00 sec)

[admin@127.0.0.1][(none)]> load mysql variables to runtime;

Query OK, 0 rows affected (0.00 sec)

[admin@127.0.0.1][(none)]> save mysql servers to disk;

Query OK, 0 rows affected (0.05 sec)

[admin@127.0.0.1][(none)]> save mysql users to disk;

Query OK, 0 rows affected (0.02 sec)

[admin@127.0.0.1][(none)]> save mysql variables to disk;

Query OK, 74 rows affected (0.01 sec)

[admin@127.0.0.1][(none)]>

7. 连接数据库，通过proxysql的客户端接口访问(6033)：(我这里从40从库上面发起连接)

 mysql -uyayun -pyayun -h192.168.0. -P6033

[root@db_server_yayun_04 ~]# mysql -uyayun -pyayun -h192.168.0.10 -P6033

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.5.30 (ProxySQL)

Copyright (c) 2000, 2016, 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.

[yayun@192.168.0.10][(none)]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| yayun              |

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

5 rows in set (0.01 sec)

[yayun@192.168.0.10][(none)]>

8. 创建表并且写入数据进行查询：

[yayun@192.168.0.10][(none)]> use yayun

Database changed, 2 warnings

[yayun@192.168.0.10][yayun]> create table t1 ( id int);

Query OK, 0 rows affected (0.08 sec)

[yayun@192.168.0.10][yayun]> insert into t1 select 1;

Query OK, 1 row affected (0.05 sec)

Records: 1  Duplicates: 0  Warnings: 0

[yayun@192.168.0.10][yayun]> select * from t1;

+------+

| id   |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

[yayun@192.168.0.10][yayun]>

可以看到创建了表，并且插入了数据，查询也没问题。proxysql有个类似审计的功能，可以查看各类SQL的执行情况。在proxysql执行SQL查看。

[admin@127.0.0.1][(none)]> select * from stats_mysql_query_digest;

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

| hostgroup | schemaname         | username | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time | min_time | max_time |

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

| 100       | yayun              | yayun    | 0xA9518ABEA63705E6 | create table t1 ( id int)        | 1          | 1497577980 | 1497577980 | 79733    | 79733    | 79733    |

| 100       | yayun              | yayun    | 0x3765930C7143F468 | select * from t1                 | 1          | 1497577997 | 1497577997 | 1537     | 1537     | 1537     |

| 100       | yayun              | yayun    | 0x4BBB5CD4BC2CFD94 | insert into t1 select ?          | 1          | 1497577986 | 1497577986 | 33350    | 33350    | 33350    |

| 100       | information_schema | yayun    | 0x620B328FE9D6D71A | SELECT DATABASE()                | 1          | 1497577955 | 1497577955 | 4994     | 4994     | 4994     |

| 100       | information_schema | yayun    | 0x594F2C744B698066 | select USER()                    | 1          | 1497577951 | 1497577951 | 0        | 0        | 0        |

| 100       | information_schema | yayun    | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1          | 1497577951 | 1497577951 | 0        | 0        | 0        |

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

6 rows in set (0.00 sec)

[admin@127.0.0.1][(none)]>

9. 可以看到读写都发送到了组100上面，组100是主库，说明没有读写分离。那是因为还有配置没有完成，我们需要自己定义规则。
定义路由规则，如：除select * from tb for update的select全部发送到slave，其他的的语句发送到master。

[admin@127.0.0.1][(none)]> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> load mysql query rules to runtime;

Query OK, 0 rows affected (0.00 sec)

[admin@127.0.0.1][(none)]> save mysql query rules to disk;

Query OK, 0 rows affected (0.02 sec)

[admin@127.0.0.1][(none)]> select rule_id,active,match_pattern,destination_hostgroup,apply from runtime_mysql_query_rules;

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

| rule_id | active | match_pattern        | destination_hostgroup | apply |

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

| 1       | 1      | ^SELECT.*FOR UPDATE$ | 100                   | 1     |

| 2       | 1      | ^SELECT              | 1000                  | 1     |

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

2 rows in set (0.00 sec)

[admin@127.0.0.1][(none)]>

清理掉统计信息，再次进行测试。

select * from stats_mysql_query_digest_reset;

再次运行读写，然后再查看，发现已经实现读写分离。

[admin@127.0.0.1][(none)]> select * from stats_mysql_query_digest;

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

| hostgroup | schemaname | username | digest             | digest_text             | count_star | first_seen | last_seen  | sum_time | min_time | max_time |

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

| 1000      | yayun      | yayun    | 0x3765930C7143F468 | select * from t1        | 1          | 1497578494 | 1497578494 | 21751    | 21751    | 21751    |

| 100       | yayun      | yayun    | 0x4BBB5CD4BC2CFD94 | insert into t1 select ? | 1          | 1497578492 | 1497578492 | 54852    | 54852    | 54852    |

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

2 rows in set (0.00 sec)

[admin@127.0.0.1][(none)]>

重点来了，如何配合MHA实现高可用呢？那么需要利用到proxysql里面的mysql_replication_hostgroups表。mysql_replication_hostgroups 表的主要作用是监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组，定义 hostgroup 的主从关系。ProxySQL monitor 模块会监控 HG 后端所有servers 的 read_only 变量，如果发现从库的 read_only 变为0、主库变为1，则认为角色互换了，自动改写 mysql_servers 表里面 hostgroup 关系，达到自动 Failover 效果。

我们看看mysql_replication_hostgroups表结构：

[admin@127.0.0.1][(none)]> show create table mysql_replication_hostgroups\G

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

       table: mysql_replication_hostgroups

Create Table: CREATE TABLE mysql_replication_hostgroups (

    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,

    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),

    comment VARCHAR,

    UNIQUE (reader_hostgroup))

1 row in set (0.00 sec)

[admin@127.0.0.1][(none)]>

3个字段很明显，写主机组，读主机组，备注。那么我们现在插入数据。我们的100是写，1000是读。

[admin@127.0.0.1][(none)]> insert into  mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment)values(100,1000,'测试我的读写分离高可用');

Query OK, 1 row affected (0.00 sec)

[admin@127.0.0.1][(none)]> load mysql servers to runtime;

Query OK, 0 rows affected (0.01 sec)

[admin@127.0.0.1][(none)]> save mysql servers to disk;

Query OK, 0 rows affected (0.03 sec)

[admin@127.0.0.1][(none)]> select * from runtime_mysql_replication_hostgroups;

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

| writer_hostgroup | reader_hostgroup | comment                           |

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

| 100              | 1000             | 测试我的读写分离高可用            |

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

1 row in set (0.00 sec)

[admin@127.0.0.1][(none)]>

我们用vc-mysql-sniffer在从库抓一下。看看能看见什么。

[root@db_server_yayun_03 ~]# ./vc-mysql-sniffer

# Time:  ::

# User@Host: unknown_user[unknown_user] @ 192.168.0.10: []

# Query_time: 0.005198

SHOW GLOBAL VARIABLES LIKE 'read_only';

# Time:  ::

# User@Host: unknown_user[unknown_user] @ 192.168.0.10: []

# Query_time: 0.000564

SHOW SLAVE STATUS;

# Time:  ::

# User@Host: unknown_user[unknown_user] @ 192.168.0.10: []

# Query_time: 0.004008

SHOW GLOBAL VARIABLES LIKE 'read_only';

# Time:  ::

# User@Host: unknown_user[unknown_user] @ 192.168.0.10: []

# Query_time: 0.004923

SHOW GLOBAL VARIABLES LIKE 'read_only';

# Time:  ::

# User@Host: unknown_user[unknown_user] @ 192.168.0.10: []

# Query_time: 0.003617

SHOW GLOBAL VARIABLES LIKE 'read_only';

可以看见在检查read_only变量。我们先看看现在主机组的关系：

[admin@127.0.0.1][(none)]> select * from runtime_mysql_servers;

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

| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment          |

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

| 100          | 192.168.0.20 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.30 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.40 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

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

3 rows in set (0.00 sec)

[admin@127.0.0.1][(none)]>

可以看见100主机组是主库，1000主机组是从库。下面使用MHA在线变换主从关系。我们把从库40提升为主库。

[root@db_server_yayun_04 ~]# masterha_master_switch --master_state=alive --conf=/data/mha//mha.cnf --new_master_host=192.168.0.40 --new_master_port= --orig_master_is_new_slave

Fri Jun  ::  - [info] MHA::MasterRotate version 0.57.

Fri Jun  ::  - [info] Starting online master switch..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : Configuration Check Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Fri Jun  ::  - [info] Reading application default configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Updating application default configuration from /usr/local/bin/load_cnf..

Fri Jun  ::  - [info] Reading server configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Setting max_ping_errors to , ping_interval to .

Fri Jun  ::  - [info] GTID failover mode =

Fri Jun  ::  - [info] Current Alive Master: 192.168.0.20(192.168.0.20:)

Fri Jun  ::  - [info] Alive Slaves:

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.20(192.168.0.20:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Fri Jun  ::  - [info]   192.168.0.40(192.168.0.40:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.20(192.168.0.20:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.0.20(192.168.0.20:)? (YES/no): y

Fri Jun  ::  - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] Checking MHA is not monitoring or doing failover..

Fri Jun  ::  - [info] Checking replication health on 192.168.0.30..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] Checking replication health on 192.168.0.40..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] 192.168.0.40 can be new master.

Fri Jun  ::  - [info]

From:

192.168.0.20(192.168.0.20:) (current master)

 +--192.168.0.30(192.168.0.30:)

 +--192.168.0.40(192.168.0.40:)

To:

192.168.0.40(192.168.0.40:) (new master)

 +--192.168.0.30(192.168.0.30:)

 +--192.168.0.20(192.168.0.20:)

Starting master switch from 192.168.0.20(192.168.0.20:) to 192.168.0.40(192.168.0.40:)? (yes/NO): yes

Fri Jun  ::  - [info] Checking whether 192.168.0.40(192.168.0.40:) is ok for the new master..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] 192.168.0.20(192.168.0.20:): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.

Fri Jun  ::  - [info] 192.168.0.20(192.168.0.20:): Resetting slave pointing to the dummy host.

Fri Jun  ::  - [info] ** Phase : Configuration Check Phase completed.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : Rejecting updates Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] Executing master ip online change script to disable write on the current master:

Fri Jun  ::  - [info]   /usr/local/bin/master_ip_online_change --command=stop --orig_master_host=192.168.0.20 --orig_master_ip=192.168.0.20 --orig_master_port= --orig_master_user='root' --orig_master_password='' --new_master_host=192.168.0.40 --new_master_ip=192.168.0.40 --new_master_port= --new_master_user='root' --new_master_password='' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave

Fri Jun  ::   Set read_only on the new master.. ok.

Fri Jun  ::   Drpping app user on the orig master..

Fri Jun  ::   Waiting all running  threads are disconnected.. (max  milliseconds)

{'Time' => '','Command' => 'Sleep','db' => undef,'Id' => '','Info' => undef,'User' => 'proxysql','State' => '','Host' => '192.168.0.10:54482'}

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.30:53451'}

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.40:53136'}

Fri Jun  ::   Waiting all running  threads are disconnected.. (max  milliseconds)

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.30:53451'}

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.40:53136'}

Fri Jun  ::   Waiting all running  threads are disconnected.. (max  milliseconds)

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.30:53451'}

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.40:53136'}

Fri Jun  ::   Set read_only= on the orig master.. ok.

Fri Jun  ::   Waiting all running  queries are disconnected.. (max  milliseconds)

{'Time' => '','Command' => 'Sleep','db' => undef,'Id' => '','Info' => undef,'User' => 'proxysql','State' => '','Host' => '192.168.0.10:54481'}

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.30:53451'}

{'Time' => '','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Host' => '192.168.0.40:53136'}

Fri Jun  ::   Killing all application threads..

Fri Jun  ::   done.

SIOCSIFFLAGS: Cannot assign requested address

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] Locking all tables on the orig master to reject updates from everybody (including root):

Fri Jun  ::  - [info] Executing FLUSH TABLES WITH READ LOCK..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] Orig master binlog:pos is mysql-bin.:.

Fri Jun  ::  - [info]  Waiting to execute all relay logs on 192.168.0.40(192.168.0.40:)..

Fri Jun  ::  - [info]  master_pos_wait(mysql-bin.:) completed on 192.168.0.40(192.168.0.40:). Executed  events.

Fri Jun  ::  - [info]   done.

Fri Jun  ::  - [info] Getting new master's binlog name and position..

Fri Jun  ::  - [info]  mysql-bin.:

Fri Jun  ::  - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.0.40', MASTER_PORT=, MASTER_AUTO_POSITION=, MASTER_USER='repl', MASTER_PASSWORD='xxx';

Fri Jun  ::  - [info] Executing master ip online change script to allow write on the new master:

Fri Jun  ::  - [info]   /usr/local/bin/master_ip_online_change --command=start --orig_master_host=192.168.0.20 --orig_master_ip=192.168.0.20 --orig_master_port= --orig_master_user='root' --orig_master_password='' --new_master_host=192.168.0.40 --new_master_ip=192.168.0.40 --new_master_port= --new_master_user='root' --new_master_password='' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave

Fri Jun  ::   Set read_only= on the new master.

Fri Jun  ::   Creating app user on the new master..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Switching slaves in parallel..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] -- Slave switch on host 192.168.0.30(192.168.0.30:) started, pid:

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] Log messages from 192.168.0.30 ...

Fri Jun  ::  - [info]

Fri Jun  ::  - [info]  Waiting to execute all relay logs on 192.168.0.30(192.168.0.30:)..

Fri Jun  ::  - [info]  master_pos_wait(mysql-bin.:) completed on 192.168.0.30(192.168.0.30:). Executed  events.

Fri Jun  ::  - [info]   done.

Fri Jun  ::  - [info]  Resetting slave 192.168.0.30(192.168.0.30:) and starting replication from the new master 192.168.0.40(192.168.0.40:)..

Fri Jun  ::  - [info]  Executed CHANGE MASTER.

Fri Jun  ::  - [info]  Slave started.

Fri Jun  ::  - [info] End of log messages from 192.168.0.30 ...

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] -- Slave switch on host 192.168.0.30(192.168.0.30:) succeeded.

Fri Jun  ::  - [info] Unlocking all tables on the orig master:

Fri Jun  ::  - [info] Executing UNLOCK TABLES..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] Starting orig master as a new slave..

Fri Jun  ::  - [info]  Resetting slave 192.168.0.20(192.168.0.20:) and starting replication from the new master 192.168.0.40(192.168.0.40:)..

Fri Jun  ::  - [info]  Executed CHANGE MASTER.

Fri Jun  ::  - [info]  Slave started.

Fri Jun  ::  - [info] All new slave servers switched successfully.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : New master cleanup phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info]  192.168.0.40: Resetting slave info succeeded.

Fri Jun  ::  - [info] Switching master to 192.168.0.40(192.168.0.40:) completed successfully.

可以看见已经成功切换。我们再来看看运行主机组的状态：

[admin@127.0.0.1][(none)]> select * from runtime_mysql_servers;

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

| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment          |

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

| 100          | 192.168.0.40 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.30 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.40 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.20 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

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

4 rows in set (0.00 sec)

[admin@127.0.0.1][(none)]>

可以看见40自动设置成了主机组100，也就是master。我们进行读写测试。

[admin@127.0.0.1][(none)]> select * from stats_mysql_query_digest;

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

| hostgroup | schemaname | username | digest             | digest_text                 | count_star | first_seen | last_seen  | sum_time | min_time | max_time |

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

| 1000      | yayun      | yayun    | 0x3765930C7143F468 | select * from t1            | 1          | 1497580568 | 1497580568 | 34920    | 34920    | 34920    |

| 100       | yayun      | yayun    | 0x5A680F86B3D8FB2B | select * from t1 for update | 1          | 1497580565 | 1497580565 | 9609     | 9609     | 9609     |

| 100       | yayun      | yayun    | 0x4BBB5CD4BC2CFD94 | insert into t1 select ?     | 1          | 1497580557 | 1497580557 | 133003   | 133003   | 133003   |

| 100       | yayun      | yayun    | 0x99531AEFF718C501 | show tables                 | 1          | 1497580544 | 1497580544 | 2051     | 2051     | 2051     |

| 100       | yayun      | yayun    | 0x74A739578E179F19 | show processlist            | 1          | 1497580532 | 1497580532 | 4335     | 4335     | 4335     |

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

5 rows in set (0.00 sec)

[admin@127.0.0.1][(none)]>

可以看见除了for update语句和insert发送到主库，查询是发送到了从库。当然虽然1000主机组里面有40，也就是会有非常少量的查询才会发送到主库。这个没法避免。
下面进行主库宕机测试，启动mha监控，关闭主库。mha切换日志，可以看见30当选主库：

[root@db_server_yayun_04 log]# cat manager.log

Fri Jun  ::  - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Fri Jun  ::  - [info] Reading application default configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Updating application default configuration from /usr/local/bin/load_cnf..

Fri Jun  ::  - [info] Reading server configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Setting max_ping_errors to , ping_interval to .

Fri Jun  ::  - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Fri Jun  ::  - [info] Reading application default configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Updating application default configuration from /usr/local/bin/load_cnf..

Fri Jun  ::  - [info] Reading server configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Setting max_ping_errors to , ping_interval to .

nfo]   192.168.0.20(192.168.0.20:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Fri Jun  ::  - [info] Current Alive Master: 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info] Checking slave configurations..

Fri Jun  ::  - [info] Checking replication filtering settings..

Fri Jun  ::  - [info]  binlog_do_db= , binlog_ignore_db=

Fri Jun  ::  - [info]  Replication filtering check ok.

Fri Jun  ::  - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Fri Jun  ::  - [info] Checking SSH publickey authentication settings on the current master..

Fri Jun  ::  - [info] HealthCheck: SSH to 192.168.0.40 is reachable.

Fri Jun  ::  - [info]

192.168.0.40(192.168.0.40:) (current master)

 +--192.168.0.20(192.168.0.20:)

 +--192.168.0.30(192.168.0.30:)

Fri Jun  ::  - [warning] master_ip_failover_script is not defined.

Fri Jun  ::  - [warning] shutdown_script is not defined.

Fri Jun  ::  - [info] Set master ping interval  seconds.

Fri Jun  ::  - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.

Fri Jun  ::  - [info] Starting ping health check on 192.168.0.40(192.168.0.40:)..

Fri Jun  ::  - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

Fri Jun  ::  - [warning] Got error on MySQL select ping:  (MySQL server has gone away)

Fri Jun  ::  - [info] Executing SSH check script: exit

Fri Jun  ::  - [info] HealthCheck: SSH to 192.168.0.40 is reachable.

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Got error on MySQL connect:  (Lost connection to MySQL server at 'reading initial communication packet', system error: )

Fri Jun  ::  - [warning] Connection failed  time(s)..

Fri Jun  ::  - [warning] Master is not reachable from health checker!

Fri Jun  ::  - [warning] Master 192.168.0.40(192.168.0.40:) is not reachable!

Fri Jun  ::  - [warning] SSH is reachable.

Fri Jun  ::  - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /data/mha//mha.cnf again, and trying to connect to all servers to check server status..

Fri Jun  ::  - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Fri Jun  ::  - [info] Reading application default configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Updating application default configuration from /usr/local/bin/load_cnf..

Fri Jun  ::  - [info] Reading server configuration from /data/mha//mha.cnf..

Fri Jun  ::  - [info] Setting max_ping_errors to , ping_interval to .

Fri Jun  ::  - [info] GTID failover mode =

Fri Jun  ::  - [info] Dead Servers:

Fri Jun  ::  - [info]   192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info] Alive Servers:

Fri Jun  ::  - [info]   192.168.0.20(192.168.0.20:)

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)

Fri Jun  ::  - [info] Alive Slaves:

Fri Jun  ::  - [info]   192.168.0.20(192.168.0.20:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Fri Jun  ::  - [info] Checking slave configurations..

Fri Jun  ::  - [info] Checking replication filtering settings..

Fri Jun  ::  - [info]  Replication filtering check ok.

Fri Jun  ::  - [info] Master is down!

Fri Jun  ::  - [info] Terminating monitoring script.

Fri Jun  ::  - [info] Got exit code  (Master dead).

Fri Jun  ::  - [info] MHA::MasterFailover version 0.57.

Fri Jun  ::  - [info] Starting master failover.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : Configuration Check Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] GTID failover mode =

Fri Jun  ::  - [info] Dead Servers:

Fri Jun  ::  - [info]   192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info] Checking master reachability via MySQL(double check)...

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] Alive Servers:

Fri Jun  ::  - [info]   192.168.0.20(192.168.0.20:)

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)

Fri Jun  ::  - [info] Alive Slaves:

Fri Jun  ::  - [info]   192.168.0.20(192.168.0.20:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Fri Jun  ::  - [info] Starting GTID based failover.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] ** Phase : Configuration Check Phase completed.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : Dead Master Shutdown Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] Forcing shutdown so that applications never connect to the current master..

Fri Jun  ::  - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.

Fri Jun  ::  - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.

Fri Jun  ::  - [info] * Phase : Dead Master Shutdown Phase completed.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : Master Recovery Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase 3.1: Getting Latest Slaves Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] The latest binary log file/position on all slaves is mysql-bin.:

Fri Jun  ::  - [info] Retrieved Gtid Set: 900f20f2-f48c-11e6-8d78-000c2930a8b9:

Fri Jun  ::  - [info] Latest slaves (Slaves that received relay log files to the latest):

Fri Jun  ::  - [info]   192.168.0.20(192.168.0.20:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Fri Jun  ::  - [info] The oldest binary log file/position on all slaves is mysql-bin.:

Fri Jun  ::  - [info] Retrieved Gtid Set: 900f20f2-f48c-11e6-8d78-000c2930a8b9:

Fri Jun  ::  - [info] Oldest slaves:

Fri Jun  ::  - [info]   192.168.0.20(192.168.0.20:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase 3.3: Determining New Master Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] Searching new master from slaves..

Fri Jun  ::  - [info]  Candidate masters from the configuration file:

Fri Jun  ::  - [info]   192.168.0.30(192.168.0.30:)  Version=5.7.-log (oldest major version between slaves) log-bin:enabled

Fri Jun  ::  - [info]     GTID ON

Fri Jun  ::  - [info]     Replicating from 192.168.0.40(192.168.0.40:)

Fri Jun  ::  - [info]     Primary candidate for the new Master (candidate_master is set)

Fri Jun  ::  - [info]  Non-candidate masters:

Fri Jun  ::  - [info]  Searching from candidate_master slaves which have received the latest relay log events..

Fri Jun  ::  - [info] New master is 192.168.0.30(192.168.0.30:)

Fri Jun  ::  - [info] Starting master failover..

Fri Jun  ::  - [info]

From:

192.168.0.40(192.168.0.40:) (current master)

 +--192.168.0.20(192.168.0.20:)

 +--192.168.0.30(192.168.0.30:)

To:

192.168.0.30(192.168.0.30:) (new master)

 +--192.168.0.20(192.168.0.20:)

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase 3.3: New Master Recovery Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info]  Waiting all logs to be applied..

Fri Jun  ::  - [info]   done.

Fri Jun  ::  - [info]  Replicating from the latest slave 192.168.0.20(192.168.0.20:) and waiting to apply..

Fri Jun  ::  - [info]  Waiting all logs to be applied on the latest slave..

Fri Jun  ::  - [info]  Resetting slave 192.168.0.30(192.168.0.30:) and starting replication from the new master 192.168.0.20(192.168.0.20:)..

Fri Jun  ::  - [info]  Executed CHANGE MASTER.

Fri Jun  ::  - [info]  Slave started.

Fri Jun  ::  - [info]  Waiting to execute all relay logs on 192.168.0.30(192.168.0.30:)..

Fri Jun  ::  - [info]  master_pos_wait(mysql-bin.:) completed on 192.168.0.30(192.168.0.30:). Executed  events.

Fri Jun  ::  - [info]   done.

Fri Jun  ::  - [info]   done.

Fri Jun  ::  - [info] Getting new master's binlog name and position..

Fri Jun  ::  - [info]  mysql-bin.:

Fri Jun  ::  - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.0.30', MASTER_PORT=, MASTER_AUTO_POSITION=, MASTER_USER='repl', MASTER_PASSWORD='xxx';

Fri Jun  ::  - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin., , 56195f28-36e2-11e7-991b-000c29e3f5ab:-,

900f20f2-f48c-11e6-8d78-000c2930a8b9:

Fri Jun  ::  - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.

Fri Jun  ::  - [info] Setting read_only= on 192.168.0.30(192.168.0.30:)..

Fri Jun  ::  - [info]  ok.

Fri Jun  ::  - [info] ** Finished master recovery successfully.

Fri Jun  ::  - [info] * Phase : Master Recovery Phase completed.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : Slaves Recovery Phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase 4.1: Starting Slaves in parallel..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] -- Slave recovery on host 192.168.0.20(192.168.0.20:) started, pid: . Check tmp log /data/mha//log/192.168..20_3306_20170616103953.log if it takes time..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] Log messages from 192.168.0.20 ...

Fri Jun  ::  - [info]

Fri Jun  ::  - [info]  Resetting slave 192.168.0.20(192.168.0.20:) and starting replication from the new master 192.168.0.30(192.168.0.30:)..

Fri Jun  ::  - [info]  Executed CHANGE MASTER.

Fri Jun  ::  - [info]  Slave started.

Fri Jun  ::  - [info]  gtid_wait(56195f28-36e2-11e7-991b-000c29e3f5ab:-,

900f20f2-f48c-11e6-8d78-000c2930a8b9:) completed on 192.168.0.20(192.168.0.20:). Executed  events.

Fri Jun  ::  - [info] End of log messages from 192.168.0.20.

Fri Jun  ::  - [info] -- Slave on host 192.168.0.20(192.168.0.20:) started.

Fri Jun  ::  - [info] All new slave servers recovered successfully.

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] * Phase : New master cleanup phase..

Fri Jun  ::  - [info]

Fri Jun  ::  - [info] Resetting slave info on the new master..

Fri Jun  ::  - [info]  192.168.0.30: Resetting slave info succeeded.

Fri Jun  ::  - [info] Master failover to 192.168.0.30(192.168.0.30:) completed successfully.

Fri Jun  ::  - [info] 

----- Failover Report -----

mha: MySQL Master failover 192.168.0.40(192.168.0.40:) to 192.168.0.30(192.168.0.30:) succeeded

Master 192.168.0.40(192.168.0.40:) is down!

Check MHA Manager logs at db_server_yayun_04:/data/mha//log/manager.log for details.

Started automated(non-interactive) failover.

Selected 192.168.0.30(192.168.0.30:) as a new master.

192.168.0.30(192.168.0.30:): OK: Applying all logs succeeded.

192.168.0.20(192.168.0.20:): OK: Slave started, replicating from 192.168.0.30(192.168.0.30:)

192.168.0.30(192.168.0.30:): Resetting slave info succeeded.

Master failover to 192.168.0.30(192.168.0.30:) completed successfully.

再来看看proxysql里面现在主机组的情况：

[admin@127.0.0.1][(none)]> select * from runtime_mysql_servers;

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

| hostgroup_id | hostname     | port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment          |

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

| 100          | 192.168.0.30 | 3306 | ONLINE  | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.30 | 3306 | ONLINE  | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.40 | 3306 | SHUNNED | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

| 1000         | 192.168.0.20 | 3306 | ONLINE  | 1      | 0           | 1000            | 10                  | 0       | 100            | test my proxysql |

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

4 rows in set (0.01 sec)

[admin@127.0.0.1][(none)]>

可以看见40已经是SHUNNED状态，这个已经自动被踢出。30当选主库，自动被设置在主机组100. 再次运行读写测试：

[admin@127.0.0.1][(none)]> select * from stats_mysql_query_digest;

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

| hostgroup | schemaname         | username | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time | min_time | max_time |

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

| 100       | yayun              | yayun    | 0x4BBB5CD4BC2CFD94 | insert into t1 select ?          | 1          | 1497581038 | 1497581038 | 114743   | 114743   | 114743   |

| 1000      | information_schema | yayun    | 0x620B328FE9D6D71A | SELECT DATABASE()                | 1          | 1497581026 | 1497581026 | 31128    | 31128    | 31128    |

| 100       | information_schema | yayun    | 0x594F2C744B698066 | select USER()                    | 1          | 1497581025 | 1497581025 | 0        | 0        | 0        |

| 1000      | yayun              | yayun    | 0x3765930C7143F468 | select * from t1                 | 1          | 1497581045 | 1497581045 | 3283     | 3283     | 3283     |

| 100       | information_schema | yayun    | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1          | 1497581025 | 1497581025 | 0        | 0        | 0        |

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

5 rows in set (0.00 sec)

可以看见依然没有问题。到这里本文就结束了，上面是1主2从，如果是1主1从，那么从挂了怎么办呢？需要额外一条规则，那就是在mysql_servers的hostgroup 1000 里面要插一条主库的记录，然后把weight设小，当读不到从库，回去主库查询。

关于ProxySQL比较详细的文章可以参考如下：

ProxySQL之安装及配置详解

ProxySQL之读写分离与分库路由演示

MHA+ProxySQL实现读写分离高可用的更多相关文章

MySQL for OPS 09：MHA + Atlas 实现读写分离高可用
写在前面的话前面做了 MHA 高可用,但是存在这样一个问题,我们花了 4 台机器,但是最终被利用起来的也就一台,主库.这样硬件利用率才 25%,这意味着除非发生故障,不然其他几台机器都是摆设.明显的 ...
【DB宝42】MySQL高可用架构MHA+ProxySQL实现读写分离和负载均衡
目录一.MHA+ProxySQL架构二.快速搭建MHA环境 2.1 下载MHA镜像 2.2 编辑yml文件,创建MHA相关容器 2.3 安装docker-compose软件(若已安装,可忽略) 2 ...
MHA+ProxySQL 读写分离高可用
文档结构如下: 1.ProxySQL说明 ProxySQL是mysql的一款中间件的产品,是灵活的mysql代理层,可以实现读写分离,支持query路由器的功能,支持动态指定sql进行缓存,支持动态加 ...
MySQL读写分离高可用集群及读操作负载均衡（Centos7）
目录概述 keepalived和heartbeat对比一.环境二.部署部署lvs代理和keepalived MySQL+heartbeat+drbd的部署 MySQL主从复制 web服务器及a ...
Atlas读写分离[高可用]
Atlas下载地址: https://github.com/Qihoo360/Atlas/releases Atlas是出于360的, 比mysql-proxy更稳定, 部署起来更方便. 环境: pr ...
mysql读写分离[高可用]
顾名思义, 在mysql负载均衡中有多种方式, 本人愚钝,只了解驱动中间件和mysql_proxy两种方式, 对于驱动,利用的是ReplicationDriver,具体请看远哥的这篇文章: MySQL ...
技术实战：基于 MHA 方式实现 MySQL 的高可用(转)
转自:http://os.51cto.com/art/201307/401702_all.htm MHA故障转移可以很好的帮我们解决从库数据的一致性问题,同时最大化挽回故障发生后的数据.本文分享了基于 ...
MHA+atlas（数据库的高可用与读写分离）
学习完了mycat的高可用还是复习一下MHA+atlas吧,个人感觉还是比mycat好用,毕竟MHA有数据补全和切换主从的机制 1 MHA是什么? MHA(Master High Availabili ...
MySQL架构之主从+ProxySQL实现读写分离
准备服务器: docker run -d --privileged -v `pwd`/mysql_data:/data -p 3001:3306 --name mysql5-master --host ...

随机推荐

struts2中错误There is no Action mapped for namespace [/] and action name [] associated with context path
1 There is no Action mapped for namespace [/] and action name [] associated with context path [/Stru ...
sqlserver 用户、账号、安全等问题小汇
一.孤立账号 SQL Server 的用户安全管理分两层,整个SQL Server 服务器一层,每个数据库一层. 在服务器层的帐号,叫登录账户(SQL Server:服务器角色),可以设置它管理整个S ...
For-Each循环
For-Each循环也叫增强型的for循环,或者叫foreach循环. For-Each循环是JDK5.0的新特性(其他新特性比如泛型.自动装箱等). For-Each循环的加入简化了集合的遍历. 语 ...
解决&period;Net 4&period;0 A potentially dangerous Request&period;Form value was detected from the client 异常
在web.config中加入 <httpRuntime maxRequestLength="22000" executionTimeout="43200" ...
java基础学习之消息对话款
package Dome; import java.awt.event.*; import java.awt.*; import javax.swing.*; public class WindowM ...
struts2文件下载火狐浏览器的文件名乱码问题
这是一个文件下载的action,红色部分为火狐浏览器需要特地做的事情. @Controller @Scope(value = "prototype") public class F ...
Explain 结果解读与实践
Explain 结果解读与实践基于 MySQL 5.0.67 ,存储引擎 MyISAM . 注:单独一行的"%%"及"`"表示分隔内容,就象分开“第一章”“第 ...
rownum与rowId
一.RowNum Rownum是oracle生成结果集时得到的一个伪列, 按照读出行的顺序, 第一条rownum=1, 第二条=2. 对于 Oracle 的 rownum 问题,很多资料都说不支持 ...
transfrom属性
transfrom可以实现一些形变.常见的有平移.缩放和旋转三种.使用起来很简单: //横纵放大1.3倍 self.imageButton.transform=CGAffineTransformSca ...
SharePoint将网站另存为模板
1.将网站另存为模板参考文章 http://blog.csdn.net/dyp330/article/details/23180843 http://blog.163.com/berlin1989@ ...