MySQL Router安装和读写分离实现

 
MySQL Router 安装

1. 解压缩，修改路径名字
2. root@ubuntu06:/usr/local/mysql_install_package# tar xvf mysql-router-8.3.0-linux-glibc2.28-x86_64.tar.xz
3. root@ubuntu06:/usr/local/mysql_install_package# mv mysql-router-8.3.0-linux-glibc2.28-x86_64 mysql-router8.3
4. root@ubuntu06:/usr/local/mysql_install_package# cd mysql-router8.3
5. 创建一个router服务的数据路径router
6. root@ubuntu06:/usr/local/mysql-router8.3# mkdir router
7. root@ubuntu06:/usr/local/mysql-router8.3# cd router
8. 在router路径下创建data log config run
9. root@ubuntu06:/usr/local/mysql-router8.3/router# mkdir data log config run

复制代码

用解压包中的设置文件模板，依据当前的主从复制，编辑router的设置文件

1. [DEFAULT]
2. logging_folder = /usr/local/mysql-router8.3/router/log
3. plugin_folder = /usr/local/mysql-router8.3/lib/mysqlrouter
4. config_folder = /usr/local/mysql-router8.3/router/config
5. runtime_folder = /usr/local/mysql-router8.3/router/run
6. data_folder = /usr/local/mysql-router8.3/router/data
7. #keyring_path = /var/lib/keyring-data
8. #master_key_path = /var/lib/keyring-key
10. [logger]
11. level = INFO
12. filename = mysqlrouter.log
13. timestamp_precision = second
15. [routing:basic_failover]
16. # To be more transparent, use MySQL Server port 3306
17. bind_address = 0.0.0.0
18. bind_port = 7001
19. routing_strategy = first-available
20. mode = read-write
21. destinations = 192.168.152.112:8000,192.168.152.113:8000
23. [routing:load_balance]
24. bind_address = 0.0.0.0
25. bind_port = 7002
26. #routing_strategy = first-available
27. mode = read-only
28. destinations = 192.168.152.113:8000,192.168.152.112:8000
31. # If no plugin is configured which starts a service, keepalive
32. # will make sure MySQL Router will not immediately exit. It is
33. # safe to remove once Router is configured.
34. [keepalive]
35. interval = 60

复制代码

 
MySQL Router systemd服务脚本

MySQL Router systemd服务脚本如下

1. [Unit]
2. Description=Percona MySQL Router
3. After=network.target
4. After=syslog.target
7. [Service]
8. Type=notify
9. User=mysql
10. Group=mysql
11. # Start main service
12. ExecStart=/usr/local/mysql-router8.3/bin/mysqlrouter -c /usr/local/mysql-router8.3/router/config/mysqlrouter.conf
13. # Sets open_files_limit
14. LimitNOFILE = 100000
16. Restart=on-failure
17. PrivateTmp=true
19. [Install]
20. WantedBy=multi-user.target

复制代码

 编辑好MySQL systemd设置文件后，reload

1. systemctl daemon-reload

复制代码

一开始通过systemctl start mysqlrouter报错，查看系统日记如下，提示相关路径没有权限

重新授权 MySQL router的数据文件路径chown mysql:mysql -R mysql-router8.3，再次启动，可以正常启动

 
MySQL Router 读写分离

通过绑定的读&写节点端口号70001端口，可以在正常连接至节点

通过绑定的只读节点的7002端口，哀求可以在主从两个节点之间轮训，请注意MySQL是在连接的时候轮训设置的读节点的，数据库连接之后指向的节点就稳定了，所以这个验证要新建连接的方式

自动故障转移

 
MySQL Router安装位置

MySQL Router自身不存储数据，足够的轻量和简单，进做一个数据库连接的转发工作，算做事无状态服务。正如官网上给出的示例图，MySQL Router更应该安装在Application端，作为Application的一部分，而不是数据库端。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。