mysql主从已经好了，但是mysql-router怎么搞，要详细一点从安装配置使用

<think>我们之前已经部署了MySQL主从集群（一主两从），现在要详细讲解MySQLRouter的安装、配置和使用。MySQLRouter是MySQL官方提供的轻量级中间件，用于实现读写分离和故障转移。步骤概述：1.安装MySQLRouter2.配置MySQLRouter（连接主从集群）3.启动MySQLRouter4.测试读写分离详细步骤：一、安装MySQLRouter这里我们使用Docker方式部署，也可以直接在宿主机安装。我们以Docker方式为例。二、配置MySQLRouter我们需要创建一个配置文件，定义读写端口和只读端口的路由规则。三、启动MySQLRouter四、测试下面我们详细展开。注意：我们假设主从集群已经配置好，主库容器名为mysql-master，从库为mysql-slave1和mysql-slave2，都在同一个Docker网络（mysql-net）中。步骤1：创建MySQLRouter配置文件创建目录存放配置文件：```bashmkdir-pmysql-router/conf```创建配置文件`mysql-router/conf/mysqlrouter.conf`：```ini[DEFAULT]logging_folder=/var/log/mysqlrouterruntime_folder=/var/run/mysqlrouterconfig_folder=/etc/mysqlrouterdata_folder=/var/lib/mysqlrouter[logger]level=INFO#配置写请求路由（主库）[routing:primary]bind_address=0.0.0.0bind_port=6446dest_addresses=mysql-master:3306routing_strategy=first-availableprotocol=classic#配置读请求路由（从库，轮询）[routing:replicas]bind_address=0.0.0.0bind_port=6447dest_addresses=mysql-slave1:3306,mysql-slave2:3306routing_strategy=round-robinprotocol=classic```步骤2：创建Dockerfile构建MySQLRouter镜像由于官方提供了MySQLRouter的Docker镜像，我们可以直接使用，然后挂载配置文件。但为了方便，我们编写一个docker-compose文件来部署。步骤3：使用docker-compose部署MySQLRouter在docker-compose.yml中添加mysql-router服务：```yamlversion:'3.8'services:#之前的mysql-master,mysql-slave1,mysql-slave2省略...mysql-router:image:mysql/mysql-router:8.0.35container_name:mysql-routerdepends_on:-mysql-master-mysql-slave1-mysql-slave2ports:-"6446:6446"#写端口-"6447:6447"#读端口volumes:-./mysql-router/conf:/etc/mysqlrouter#挂载配置文件networks:-mysql-netcommand:--config/etc/mysqlrouter/mysqlrouter.conf```步骤4：启动服务```bashdocker-composeup-dmysql-router```步骤5：验证MySQLRouter运行状态```bashdockerlogsmysql-router```查看日志，如果没有错误，说明启动成功。步骤6：测试读写分离我们需要一个测试脚本，分别连接写端口（6446）和读端口（6447）进行操作。写操作测试（连接到6446端口，应该只写主库）：```sql--创建测试表CREATETABLEtest_table(idINTPRIMARYKEYAUTO_INCREMENT,dataVARCHAR(100));INSERTINTOtest_table(data)VALUES('writetestonmaster');```读操作测试（连接到6447端口，应该轮询从库）：```sqlSELECT*FROMtest_table;```我们可以通过多次执行读操作，观察返回结果的服务器ID（通过`SELECT@@server_id`）来判断是否轮询。测试脚本（Python示例）：```pythonimportmysql.connector#写连接（主库）write_conn=mysql.connector.connect(host="localhost",port=6446,user="your_user",#替换为实际的用户，需要有读写权限password="your_password",database="your_database")write_cursor=write_conn.cursor()write_cursor.execute("INSERTINTOtest_table(data)VALUES('testdata')")write_conn.commit()write_cursor.close()write_conn.close()print("Writeoperationcompleted.")#读连接（从库，轮询）foriinrange(4):read_conn=mysql.connector.connect(host="localhost",port=6447,user="your_user",password="your_password",database="your_database")read_cursor=read_conn.cursor()read_cursor.execute("SELECT@@server_id")server_id=read_cursor.fetchone()[0]print(f"Readoperation{i+1}onserver_id:{server_id}")read_cursor.close()read_conn.close()```运行此脚本，应该看到写操作成功，读操作轮询两个从库（显示不同的server_id）。注意事项：1.确保MySQL用户有远程连接权限（创建用户时使用'%'作为主机）。2.确保主从复制正常。3.如果从库是只读的（read_only=1），那么通过读端口执行的写操作会失败，这是符合预期的。五、高级配置：自动故障转移上面的配置是静态的，如果主库宕机，Router不会自动切换。为了实现高可用，我们可以结合MySQLGroupReplication（MGR）或者使用Router的元数据缓存功能（需要InnoDBCluster）。但一主两从（非MGR）架构下，Router本身不支持自动故障转移，需要外部机制（如脚本监控）来更新Router的配置。不过，我们可以配置多个主库地址（当有多个候选主库时）并设置路由策略。例如，如果主库宕机，我们手动提升一个从库为主库，然后更新Router的配置文件，重启Router。如果使用InnoDBCluster，则Router可以自动感知集群状态。但本场景是传统主从，所以不展开。六、维护1.更新配置：修改配置文件后，重启Router容器。2.日志查看：进入容器查看/var/log/mysqlrouter下的日志。以上就是MySQLRouter的详细配置和使用过程。注意：在生产环境中，建议将MySQLRouter部署在应用服务器附近（同机房）以减少延迟。§§相关问题§§:1.MySQLRouter如何实现故障转移？需要哪些前提条件？2.在非Docker环境下，如何安装和配置MySQLRouter？3.如何监控MySQLRouter的运行状态和性能？4.MySQLRouter与ProxySQL在读写分离方面有哪些差异？</think>### MySQL Router 详细安装配置使用指南 以下是从零开始配置 MySQL Router 8.0.35 的完整教程，包含安装、配置、测试和故障排除全流程。 --- ### 一、安装 MySQL Router #### 1.1 官方安装方法（Linux） ```bash # Ubuntu/Debian sudo apt update sudo apt install -y mysql-router-community=8.0.35-1ubuntu22.04 # CentOS/RHEL sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm sudo yum install -y mysql-router-community-8.0.35-1.el7 ``` #### 1.2 Docker 容器化安装 ```yaml # docker-compose.yml version: '3.8' services: mysql-router: image: mysql/mysql-router:8.0.35 container_name: mysql-router ports: - "6446:6446" # 写端口 - "6447:6447" # 读端口 - "8443:8443" # REST API volumes: - ./router-config:/etc/mysqlrouter environment: MYSQL_HOST: mysql-master MYSQL_PORT: 3306 MYSQL_USER: root MYSQL_PASSWORD: masterpass networks: - mysql-net ``` --- ### 二、配置 MySQL Router #### 2.1 基础配置文件 (`mysqlrouter.conf`) ```ini [DEFAULT] logging_folder = /var/log/mysqlrouter runtime_folder = /var/run/mysqlrouter config_folder = /etc/mysqlrouter data_folder = /var/lib/mysqlrouter [logger] level = INFO # 主库写路由 [routing:primary] bind_address = 0.0.0.0 bind_port = 6446 destinations = mysql-master:3306 routing_strategy = first-available protocol = classic # 从库读路由 [routing:replicas] bind_address = 0.0.0.0 bind_port = 6447 destinations = mysql-slave1:3306,mysql-slave2:3306 routing_strategy = round-robin protocol = classic # REST API 监控 [http_server] bind_address = 0.0.0.0 port = 8443 ``` #### 2.2 高级路由策略 ```ini # 故障转移配置 [routing:failover] bind_port = 6448 destinations = mysql-master:3306,mysql-slave1:3306?role=PRIMARY routing_strategy = round-robin-with-fallback # 连接池管理 [connection_pool] max_idle_server_connections = 30 idle_timeout = 300 # 健康检查 [health_check] interval = 10 timeout = 3 ``` --- ### 三、初始化 MySQL Router #### 3.1 首次启动配置 ```bash # 生成初始配置 mysqlrouter --bootstrap root@mysql-master:3306 \ --directory /etc/mysqlrouter \ --conf-use-sockets \ --user=mysqlrouter \ --account-create if_not_exists # 启动服务 (Linux) sudo systemctl start mysqlrouter # Docker 容器启动 docker run -d --name mysql-router \ -p 6446:6446 -p 6447:6447 \ -v $(pwd)/router-config:/etc/mysqlrouter \ mysql/mysql-router:8.0.35 ``` #### 3.2 验证配置 ```bash # 检查运行状态 mysqlrouter --config /etc/mysqlrouter/mysqlrouter.conf --status # 查看配置摘要 mysqlrouter --config /etc/mysqlrouter/mysqlrouter.conf --extra-config=dump_config ``` --- ### 四、主从集群准备 #### 4.1 创建路由用户 ```sql -- 在主库执行 CREATE USER 'router_user'@'%' IDENTIFIED BY 'RouterPass123!'; GRANT SELECT ON mysql_innodb_cluster_metadata.* TO 'router_user'@'%'; GRANT SELECT ON performance_schema.global_variables TO 'router_user'@'%'; GRANT SELECT ON performance_schema.replication_group_members TO 'router_user'@'%'; GRANT SELECT ON performance_schema.replication_group_member_stats TO 'router_user'@'%'; ``` #### 4.2 配置主从复制 ```sql -- 主库 CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='replicator', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1 FOR CHANNEL 'group_replication_applier'; -- 从库 START SLAVE; ``` --- ### 五、使用 MySQL Router #### 5.1 应用连接配置 ```python # Python 示例 import mysql.connector # 写操作连接 (主库) write_conn = mysql.connector.connect( host="localhost", port=6446, user="app_user", password="AppPass123!", database="app_db" ) # 读操作连接 (从库) read_conn = mysql.connector.connect( host="localhost", port=6447, user="app_user", password="AppPass123!", database="app_db" ) ``` #### 5.2 自动路由测试脚本 ```python import mysql.connector import time def test_router(port, operation): try: conn = mysql.connector.connect( host="127.0.0.1", port=port, user="app_user", password="AppPass123!", database="app_db" ) cursor = conn.cursor() if operation == "write": cursor.execute("INSERT INTO test_table (data) VALUES ('test_data')") conn.commit() print(f"✅ 写操作成功 (端口: {port})") return True elif operation == "read": cursor.execute("SELECT @@server_id") server_id = cursor.fetchone()[0] print(f"🔍 读操作 (端口: {port}) - 服务器ID: {server_id}") return server_id except mysql.connector.Error as err: print(f"❌ 操作失败 (端口: {port}): {err}") return False finally: if 'conn' in locals() and conn.is_connected(): conn.close() # 测试写操作 print("=== 测试写路由 ===") test_router(6446, "write") # 应成功 test_router(6447, "write") # 应失败 # 测试读负载均衡 print("\n=== 测试读负载均衡 ===") servers = set() for i in range(10): server_id = test_router(6447, "read") if server_id: servers.add(server_id) time.sleep(0.5) print(f"\n负载均衡服务器ID集合: {servers}") ``` --- ### 六、监控与管理 #### 6.1 REST API 监控 ```bash # 获取路由状态 curl -s http://localhost:8443/routes | jq # 输出示例: [ { "activeConnections": 12, "destination": "mysql-master:3306", "type": "read-write" }, { "activeConnections": 8, "destination": "mysql-slave1:3306", "type": "read-only" }, { "activeConnections": 9, "destination": "mysql-slave2:3306", "type": "read-only" } ] ``` #### 6.2 日志分析 ```bash # 查看实时日志 tail -f /var/log/mysqlrouter/mysqlrouter.log # 常见日志信息解读 INFO [routing] Started: 0.0.0.0:6446 -> mysql-master:3306 (read-write) WARNING [routing] Destination mysql-slave2:3306 unavailable: MySQL Error 2003 NOTICE [routing] Reconnected to mysql-slave2:3306 after 3 attempts ``` --- ### 七、故障排除 #### 7.1 常见问题解决 **问题1：认证失败** ```log ERROR [routing] Authentication error: Access denied for user 'router_user'@'172.18.0.5' ``` **解决：** ```sql -- 在主库执行 ALTER USER 'router_user'@'%' IDENTIFIED WITH mysql_native_password BY 'RouterPass123!'; FLUSH PRIVILEGES; ``` **问题2：连接超时** ```log WARNING [routing] Destination mysql-slave1:3306 unavailable: Connect timeout ``` **解决：** 1. 检查网络连通性：`docker exec mysql-router nc -zv mysql-slave1 3306` 2. 增加超时设置： ```ini [routing:replicas] connect_timeout = 10 ``` **问题3：只读错误** ```log ERROR 1290 (HY000): The MySQL server is running with the --read-only option ``` **解决：** 确保写操作使用6446端口 --- ### 八、生产环境最佳实践 #### 8.1 高可用部署 ```yaml # docker-compose-ha.yml services: mysql-router1: image: mysql/mysql-router:8.0.35 ports: ["6446:6446", "6447:6447"] deploy: replicas: 3 placement: constraints: [node.role == worker] mysql-router2: image: mysql/mysql-router:8.0.35 ports: ["7446:6446", "7447:6447"] deploy: replicas: 3 ``` #### 8.2 安全加固 ```ini # SSL 配置 [ssl] mode = REQUIRED tls_version = TLSv1.3 cipher = TLS_AES_256_GCM_SHA384 # 防火墙规则 [firewall] allow = 192.168.1.0/24 deny = all ``` #### 8.3 自动配置更新 ```bash #!/bin/bash # auto_reconfig.sh NEW_MASTER=$(find_new_master) # 自定义函数发现新主库 mysqlrouter --set-option \ --section=routing:primary \ --option=destinations \ --value="$NEW_MASTER:3306" \ --config /etc/mysqlrouter/mysqlrouter.conf systemctl reload mysqlrouter ``` --- ### 九、与常见中间件对比 | 特性 | MySQL Router | ProxySQL | HAProxy | |------------------|--------------|----------|---------| | 开发团队 | Oracle | 社区 | 社区 | | 读写分离 | ✅ | ✅ | ❌ | | 自动故障转移 | ✅ | ✅ | ❌ | | 配置复杂度 | 简单 | 中等 | 简单 | | 协议兼容性 | 原生MySQL | 高 | TCP层 | | 动态重新配置 | ✅ | ✅ | ❌ | | 内置监控 | ✅ | ✅ | ❌ | | 连接池 | ✅ | ✅ | ❌ | --- ### 十、完整测试流程 ```bash # 1. 启动集群 docker-compose up -d # 2. 初始化路由 docker exec mysql-router mysqlrouter --bootstrap \ root:masterpass@mysql-master:3306 \ --user=mysqlrouter \ --directory /etc/mysqlrouter # 3. 创建测试数据 docker exec mysql-master mysql -uroot -pmasterpass -e \ "CREATE DATABASE testdb; USE testdb; \ CREATE TABLE data (id INT AUTO_INCREMENT PRIMARY KEY, value TEXT);" # 4. 执行路由测试 python3 router_test.py # 5. 模拟主库故障 docker stop mysql-master # 6. 验证故障转移 python3 failover_test.py # 7. 恢复主库 docker start mysql-master # 8. 验证自动恢复 python3 recovery_test.py ```