元数据集群是什么，我使用的是docker配置的主从

<think>我们之前讨论的是使用MySQLRouter的元数据缓存功能来实现读写分离。现在用户提到他们使用的是Docker配置的主从复制，并且对“元数据集群”这个概念有疑问。需要澄清的是：1.元数据集群：在MySQLRouter中，元数据集群指的是MySQLInnoDBCluster的元数据。InnoDBCluster使用组复制（GroupReplication）技术，并通过MySQLShell创建和管理。元数据存储在mysql_innodb_cluster_metadata数据库中。2.但是，如果用户使用的是传统的主从复制（而不是InnoDBCluster），那么就不能使用元数据缓存功能了吗？实际上，MySQLRouter8.0之后支持基于传统主从复制的元数据缓存，但需要手动配置。由于用户使用Docker配置的主从（传统主从复制，非InnoDBCluster），我们需要调整配置方案。以下是针对传统主从复制的MySQLRouter配置（不使用InnoDBCluster元数据）：</think>### MySQL 元数据集群详解（针对 Docker 主从环境） 元数据集群是 MySQL Router 的核心概念，它管理着数据库集群的拓扑信息。在您的 Docker 主从环境中（主库:3307，从库:3308/3309），元数据集群负责存储和维护这些节点的状态信息。 #### 元数据集群核心组件 | 组件 | 说明 | Docker 环境对应 | |------|------|----------------| | 元数据存储 | 存储集群拓扑信息 | `mysql_innodb_cluster_metadata` 数据库 | | 节点管理器 | 监控节点状态 | MySQL Router 内置功能 | | 配置服务 | 提供集群配置 | MySQL Router 配置文件 | | 状态同步 | 节点状态同步 | 通过主库复制 | #### Docker 环境专用配置方案 **1. 创建元数据存储容器** ```bash # 启动元数据存储容器 docker run -d --name mysql-metadata \ -e MYSQL_ROOT_PASSWORD=MetaRoot123! \ -e MYSQL_DATABASE=mysql_innodb_cluster_metadata \ -e MYSQL_USER=metadata_user \ -e MYSQL_PASSWORD=MetaPass123! \ -p 3310:3306 \ mysql:8.0.35 \ --server-id=100 \ --log-bin=metadata-bin \ --binlog-format=ROW ``` **2. 主从容器配置** ```bash # 主库容器 docker run -d --name mysql-master \ -e MYSQL_ROOT_PASSWORD=MasterRoot123! \ -p 3307:3306 \ mysql:8.0.35 \ --server-id=1 \ --log-bin=master-bin \ --binlog-format=ROW \ --gtid-mode=ON \ --enforce-gtid-consistency=ON # 从库1 docker run -d --name mysql-slave1 \ -e MYSQL_ROOT_PASSWORD=Slave1Root123! \ -p 3308:3306 \ mysql:8.0.35 \ --server-id=2 \ --relay-log=slave1-relay-bin \ --read-only=1 # 从库2 docker run -d --name mysql-slave2 \ -e MYSQL_ROOT_PASSWORD=Slave2Root123! \ -p 3309:3306 \ mysql:8.0.35 \ --server-id=3 \ --relay-log=slave2-relay-bin \ --read-only=1 ``` **3. 配置主从复制** ```bash # 在主库执行 docker exec -it mysql-master mysql -uroot -pMasterRoot123! -e " CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPass123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; SHOW MASTER STATUS;" # 在从库执行（替换MASTER_LOG_FILE和MASTER_LOG_POS） docker exec -it mysql-slave1 mysql -uroot -pSlave1Root123! -e " CHANGE MASTER TO MASTER_HOST='host.docker.internal', MASTER_PORT=3307, MASTER_USER='repl', MASTER_PASSWORD='ReplPass123!', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=157; START SLAVE;" docker exec -it mysql-slave2 mysql -uroot -pSlave2Root123! -e " CHANGE MASTER TO MASTER_HOST='host.docker.internal', MASTER_PORT=3307, MASTER_USER='repl', MASTER_PASSWORD='ReplPass123!', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=157; START SLAVE;" ``` **4. MySQL Router 容器配置** ```bash # 创建Router配置文件 mkdir -p ./mysqlrouter/conf cat > ./mysqlrouter/conf/mysqlrouter.conf <<EOF [DEFAULT] logging_folder=/var/log/mysqlrouter runtime_folder=/var/run/mysqlrouter config_folder=/etc/mysqlrouter data_folder=/var/lib/mysqlrouter [logger] level=INFO [metadata_cache:docker_cluster] router_id=1 bootstrap_server_addresses=mysql-metadata:3306 user=metadata_user password=MetaPass123! metadata_cluster=docker_prod_cluster ttl=5 # 主库路由 [routing:primary] bind_address=0.0.0.0 bind_port=6446 destinations=metadata-cache://docker_cluster/default?role=PRIMARY routing_strategy=first-available # 从库路由 [routing:replicas] bind_address=0.0.0.0 bind_port=6447 destinations=metadata-cache://docker_cluster/default?role=SECONDARY routing_strategy=round-robin max_replication_lag=300 EOF # 启动Router容器 docker run -d --name mysql-router \ --link mysql-metadata \ --link mysql-master \ --link mysql-slave1 \ --link mysql-slave2 \ -p 6446:6446 -p 6447:6447 \ -v $(pwd)/mysqlrouter/conf:/etc/mysqlrouter \ -v $(pwd)/mysqlrouter/log:/var/log/mysqlrouter \ mysql/mysql-router:8.0.35 ``` **5. 初始化元数据集群** ```bash # 在元数据存储容器执行 docker exec -it mysql-metadata mysql -uroot -pMetaRoot123! -e " CREATE DATABASE IF NOT EXISTS mysql_innodb_cluster_metadata; USE mysql_innodb_cluster_metadata; -- 创建集群元数据表 CREATE TABLE clusters ( cluster_id VARCHAR(36) PRIMARY KEY, cluster_name VARCHAR(255) NOT NULL, description TEXT, options JSON ); CREATE TABLE instances ( instance_id VARCHAR(36) PRIMARY KEY, cluster_id VARCHAR(36) NOT NULL, address VARCHAR(255) NOT NULL, port INT NOT NULL, role ENUM('PRIMARY', 'SECONDARY') NOT NULL, weight FLOAT DEFAULT 1.0, FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id) ); -- 添加集群信息 INSERT INTO clusters (cluster_id, cluster_name) VALUES ('d0c7a1b2-3e4f-4a5b-9c6d-7e8f9a0b1c2d', 'docker_prod_cluster'); -- 添加节点信息 INSERT INTO instances (instance_id, cluster_id, address, port, role, weight) VALUES ('node1', 'd0c7a1b2-3e4f-4a5b-9c6d-7e8f9a0b1c2d', 'mysql-master', 3306, 'PRIMARY', 1.0), ('node2', 'd0c7a1b2-3e4f-4a5b-9c6d-7e8f9a0b1c2d', 'mysql-slave1', 3306, 'SECONDARY', 0.8), ('node3', 'd0c7a1b2-3e4f-4a5b-9c6d-7e8f9a0b1c2d', 'mysql-slave2', 3306, 'SECONDARY', 0.7); -- 创建监控用户 CREATE USER 'router_monitor'@'%' IDENTIFIED BY 'MonitorPass123!'; GRANT SELECT ON mysql_innodb_cluster_metadata.* TO 'router_monitor'@'%'; GRANT SELECT ON performance_schema.* TO 'router_monitor'@'%'; " ``` #### 元数据集群工作原理 ```mermaid sequenceDiagram participant App as 应用程序 participant Router as MySQL Router participant Meta as 元数据存储 participant Master as 主库(3307) participant Slave1 as 从库1(3308) participant Slave2 as 从库2(3309) App->>Router: SQL查询 (端口6446/6447) Router->>Meta: 获取集群拓扑 Meta-->>Router: 返回节点列表和状态 alt 写操作 Router->>Master: 路由到主库 Master-->>Router: 返回结果 else 读操作 Router->>Slave1: 轮询选择从库 Slave1-->>Router: 返回结果 end Router-->>App: 返回查询结果 loop 每5秒 Router->>Master: 检查节点状态 Master->>Router: 返回复制状态 Router->>Meta: 更新拓扑信息 end ``` #### 元数据自动同步脚本 ```python # metadata_sync.py import mysql.connector import time import json from docker import DockerClient def sync_metadata(): docker = DockerClient(base_url='unix://var/run/docker.sock') # 获取主库状态 master_conn = mysql.connector.connect( host="mysql-master", port=3306, user="root", password="MasterRoot123!" ) master_cursor = master_conn.cursor(dictionary=True) master_cursor.execute("SHOW MASTER STATUS") master_status = master_cursor.fetchone() # 获取从库状态 slaves = [] for slave in ['mysql-slave1', 'mysql-slave2']: try: slave_conn = mysql.connector.connect( host=slave, port=3306, user="root", password=f"{slave.replace('mysql-','').capitalize()}Root123!" ) slave_cursor = slave_conn.cursor(dictionary=True) slave_cursor.execute("SHOW SLAVE STATUS") slave_status = slave_cursor.fetchone() slaves.append({ "host": slave, "port": 3306, "role": "SECONDARY", "state": "ONLINE" if slave_status['Slave_IO_Running'] == 'Yes' else "ERROR", "lag": slave_status['Seconds_Behind_Master'], "weight": 0.9 if slave_status['Seconds_Behind_Master'] < 10 else 0.6 }) except Exception as e: print(f"从库 {slave} 连接失败: {str(e)}") slaves.append({ "host": slave, "port": 3306, "role": "SECONDARY", "state": "OFFLINE", "lag": None, "weight": 0.1 }) # 更新元数据存储 meta_conn = mysql.connector.connect( host="mysql-metadata", port=3306, user="metadata_user", password="MetaPass123!", database="mysql_innodb_cluster_metadata" ) meta_cursor = meta_conn.cursor() # 更新主库状态 meta_cursor.execute(""" UPDATE instances SET state = %s, weight = %s WHERE role = 'PRIMARY' """, ("ONLINE", 1.0)) # 更新从库状态 for slave in slaves: meta_cursor.execute(""" UPDATE instances SET state = %s, weight = %s, last_check = NOW() WHERE address = %s """, (slave['state'], slave['weight'], slave['host'])) meta_conn.commit() print(f"元数据更新完成: 主库状态正常, 从库在线 {len([s for s in slaves if s['state']=='ONLINE'])}/{len(slaves)}") # 检查容器状态 for container in docker.containers.list(): if container.name.startswith('mysql-'): status = "running" if container.status == "running" else "stopped" print(f"容器状态: {container.name} - {status}") if __name__ == "__main__": while True: try: sync_metadata() except Exception as e: print(f"同步失败: {str(e)}") time.sleep(10) # 每10秒同步一次 ``` #### Docker Compose 完整部署 ```yaml # docker-compose.yml version: '3.8' services: metadata: image: mysql:8.0.35 container_name: mysql-metadata environment: MYSQL_ROOT_PASSWORD: MetaRoot123! MYSQL_DATABASE: mysql_innodb_cluster_metadata MYSQL_USER: metadata_user MYSQL_PASSWORD: MetaPass123! ports: - "3310:3306" command: - "--server-id=100" - "--log-bin=metadata-bin" - "--binlog-format=ROW" networks: - mysql-net master: image: mysql:8.0.35 container_name: mysql-master environment: MYSQL_ROOT_PASSWORD: MasterRoot123! ports: - "3307:3306" command: - "--server-id=1" - "--log-bin=master-bin" - "--binlog-format=ROW" - "--gtid-mode=ON" - "--enforce-gtid-consistency=ON" networks: - mysql-net slave1: image: mysql:8.0.35 container_name: mysql-slave1 environment: MYSQL_ROOT_PASSWORD: Slave1Root123! ports: - "3308:3306" command: - "--server-id=2" - "--relay-log=slave1-relay-bin" - "--read-only=1" depends_on: - master networks: - mysql-net slave2: image: mysql:8.0.35 container_name: mysql-slave2 environment: MYSQL_ROOT_PASSWORD: Slave2Root123! ports: - "3309:3306" command: - "--server-id=3" - "--relay-log=slave2-relay-bin" - "--read-only=1" depends_on: - master networks: - mysql-net router: image: mysql/mysql-router:8.0.35 container_name: mysql-router ports: - "6446:6446" # 主库路由 - "6447:6447" # 从库路由 volumes: - ./mysqlrouter/conf:/etc/mysqlrouter - ./mysqlrouter/log:/var/log/mysqlrouter depends_on: - metadata - master - slave1 - slave2 networks: - mysql-net command: --config=/etc/mysqlrouter/mysqlrouter.conf sync-agent: image: python:3.9 container_name: metadata-sync volumes: - ./scripts:/app command: python /app/metadata_sync.py depends_on: - metadata - master - slave1 - slave2 networks: - mysql-net networks: mysql-net: driver: bridge ``` #### 故障恢复策略 **自动故障转移流程**： 1. 检测主库不可用（连续3次心跳失败） 2. 从从库中选举新主库（选择复制延迟最小的） 3. 更新元数据存储中的主库信息 4. 重新配置其他从库指向新主库 5. 通知 MySQL Router 更新路由表 **故障恢复脚本**： ```bash #!/bin/bash # failover.sh # 1. 检测主库状态 if ! mysqladmin -h mysql-master -P 3306 -uroot -pMasterRoot123! ping &> /dev/null; then echo "主库故障，开始故障转移..." # 2. 选择新主库（复制延迟最小的） new_master=$(mysql -h mysql-metadata -P 3306 -umetadata_user -pMetaPass123! \ -D mysql_innodb_cluster_metadata -sN -e \ "SELECT address FROM instances WHERE role='SECONDARY' AND state='ONLINE' ORDER BY weight DESC LIMIT 1") # 3. 提升新主库 docker exec $new_master mysql -uroot -p${new_master^^}Root123! -e " STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only=0;" # 4. 更新元数据 mysql -h mysql-metadata -P 3306 -umetadata_user -pMetaPass123! \ -D mysql_innodb_cluster_metadata -e " UPDATE instances SET role='SECONDARY' WHERE role='PRIMARY'; UPDATE instances SET role='PRIMARY' WHERE address='$new_master';" # 5. 重新配置其他从库 for slave in $(docker ps --filter "name=mysql-slave" --format "{{.Names}}"); do if [ "$slave" != "$new_master" ]; then docker exec $slave mysql -uroot -p${slave^^}Root123! -e " STOP SLAVE; CHANGE MASTER TO MASTER_HOST='$new_master', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='ReplPass123!', MASTER_AUTO_POSITION=1; START SLAVE;" fi done echo "故障转移完成: 新主库 $new_master" else echo "主库状态正常" fi ``` #### 监控与告警 **Prometheus 监控配置**： ```yaml # prometheus.yml scrape_configs: - job_name: 'mysql-router' static_configs: - targets: ['mysql-router:8443'] - job_name: 'mysql-nodes' static_configs: - targets: - 'mysql-master:9104' # mysqld_exporter - 'mysql-slave1:9104' - 'mysql-slave2:9104' - 'mysql-metadata:9104' ``` **Grafana 仪表盘示例**： ``` MySQL Router 关键指标: 1. 连接数: router_connection_count 2. 查询速率: router_queries_per_second 3. 错误率: router_errors_total 4. 节点状态: mysql_global_status_wsrep_cluster_status 5. 复制延迟: mysql_slave_status_seconds_behind_master ``` #### 生产环境建议 1. **元数据高可用**： ```yaml # 元数据集群多副本 metadata1: image: mysql:8.0.35 ... metadata2: image: mysql:8.0.35 ... metadata3: image: mysql:8.0.35 ... ``` 2. **Router 负载均衡**： ```bash # 启动多个Router实例 for i in {1..3}; do docker run -d --name mysql-router-$i ... done # 使用Nginx负载均衡 upstream mysql_routers { server mysql-router-1:6446; server mysql-router-2:6446; server mysql-router-3:6446; } ``` 3. **数据持久化**： ```yaml volumes: - mysql-metadata-data:/var/lib/mysql - mysql-master-data:/var/lib/mysql - mysql-slave1-data:/var/lib/mysql - mysql-slave2-data:/var/lib/mysql volumes: mysql-metadata-data: mysql-master-data: mysql-slave1-data: mysql-slave2-data: ```