router配置

下载：https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-2.1.3-1.el7.x86_64.rpm

yum install 安装

查看安装位置：
[root@mysql ~]# rpm -ql mysql-router-2.1.3-1.el7.x86_64
/etc/mysqlrouter
/etc/mysqlrouter/mysqlrouter.conf
/usr/bin/mysqlrouter
/usr/lib/systemd/system/mysqlrouter.service
/usr/lib/tmpfiles.d/mysqlrouter.conf
/usr/lib64/libmysqlharness.so
/usr/lib64/libmysqlharness.so.1
/usr/lib64/libmysqlrouter.so
/usr/lib64/libmysqlrouter.so.1
/usr/lib64/mysqlrouter
/usr/lib64/mysqlrouter/keepalive.so
/usr/lib64/mysqlrouter/logger.so
/usr/lib64/mysqlrouter/metadata_cache.so
/usr/lib64/mysqlrouter/mysql_protocol.so
/usr/lib64/mysqlrouter/routing.so
/usr/lib64/mysqlrouter/x_protocol.so
/usr/share/doc/mysql-router-2.1.3
/usr/share/doc/mysql-router-2.1.3/License.txt
/usr/share/doc/mysql-router-2.1.3/README.txt
/var/log/mysqlrouter
/var/run/mysqlrouter

配置：/etc/mysqlrouter/mysqlrouter.conf
```
[DEFAULT]
logging_folder = /var/log/mysqlrouter/                日志目录
plugin_folder = /usr/lib64/mysqlrouter                router的插件位置。根据安装目录决定。
runtime_folder = /var/run/mysqlrouter                运行时候的目录路径
config_folder = /etc/mysqlrouter                        配置文件路径

[logger]
level = info                        日志级别

[keepalive]
interval = 60                        健康检查频率60s。

[routing:failover]                        路由：failover策略
bind_address = 127.0.0.1            工具启动的时候绑定的网卡地址, 默认为 127.0.0.1, 如果没有指定端口, bind_port 选项则必须指定;
bind_port = 7001                         绑定的端口，监听端口
max_connections = 2000                连接到router的最大连接数。
connect_timeout=3                         连接超时配置，默认为1s。
mode = read-write                            该选项必须要指定, 不同模式对应不同的调度策略, 目前支持两种方式: read-write 和 read-only;
destinations = 192.168.0.204:3307,192.168.0.203:3307,192.168.0.202:3306        以逗号形式提供后端需要连接的 MySQL Server 列表(master);
 
[routing:balancing]                                负载均衡模式。
bind_address = 0.0.0.0
bind_port = 7002
connect_timeout = 3
max_connections = 1024
mode = read-only
destinations = 192.168.0.202:3306,10.166.224.34:3310        指定所有slave。

```

在 mode 选项中, 我们可以选用 read-write 和 read-only 模式:
read-write: 通常用于路由到后端的 MySQL master, 在 read-write 模式中, 所有的流量都转发到 destinations 指定的列表的首个地址, 比如 “127.0.0.1:3301, 127.0.0.1:3302”, 则所有的流量都转发到 3301 端口, 如果 3301 端口失败, 则尝试连接 3302 端口, 如果列表中没有有效的MySQL Server, 路由请求会被中断丢弃; 这种方式被称作 “first-available”. 这种方式可以适用于一般的主从架构中, 比如指定列表 “master:3301, slave1:3301”, 在 master 出现故障的时候, MySQL Router 会自己连接 slave1, 不过中间的切换过程需要我们做很多的操作来满足这种工作模式.

read-only: 路由请求到后端的 MySQL slave, 从这点看 read-only 模式下, destitions 指定的 MySQL Server 列表应该都是 slave, 不同于 read-write 都指定的是 master. 在 read-only 模式中, 使用轮询( round-robin )方式转发请求到后端的 Server. 如果一个 Server 不可用，则尝试下一个 Server, 这意味着不会丢失一个请求, 如果服务都不可用, 则丢弃请求.这种模式下如果应用读写分离, read-only 会是比较好的选择.

从上面两点看, MySQL Router 的服务模式和我们熟知的 cobar, atlas, kingshard 等大为不同, master, slave 都需要单独配置, 这点在扩展性方面比较差, 估计很多人不会喜欢该模式,

启动：
[root@mysql ~]# mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &
[1] 20144

注意，这里没有配置账户密码，直接使用db中的账户，router只负载转发，
7001转发给master，7002转发给slave。也就是说需要应用来实现读写分离。
```
[root@mysql ~]# ps -ef | grep mysqlrouter
root     20144 16152  0 15:15 pts/0    00:00:00 mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
root     20166 16152  0 15:19 pts/0    00:00:00 grep --color=auto mysqlrouter

[root@mysql ~]# netstat -an | grep 700
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN    
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN  
```
登录测试：

[root@mysql ~]# mysql -udba -p -h127.0.0.1 -P7001
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| maxscale_schema    |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.04 sec)

mysql> select * from test.tab;
+------+
| id   |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
| NULL |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
|  200 |
+------+
15 rows in set (0.23 sec)
这里没有100和101数据，说明是master。

[root@mysql ~]# mysql -udba -p -h127.0.0.1 -P7002
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
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.

mysql> select * from test.tab;
+------+
| id   |
+------+
|    1 |
|    2 |
|  101 |
| NULL |
| NULL |
| NULL |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
|  200 |
+------+
16 rows in set (0.02 sec)

OK，slave了。

断开再来一次：
[root@mysql ~]# mysql -udba -p -h127.0.0.1 -P7002
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
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.

mysql> select * from test.tab;
+------+
| id   |
+------+
|    1 |
|    2 |
|  100 |
| NULL |
| NULL |
| NULL |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
|  200 |
+------+
16 rows in set (0.08 sec)

轮询。

要满足读写分离，还需要其他中间件或是应用实现，这不满足高可用需求，无法满足读写分离，只能提供slave负载均衡。