下载：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负载均衡。
扫一扫
1798
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
