MySQL 横向扩展主从复制

MySQL 支持横向扩展（horizontal scaling），但需要通过特定的技术和架构设计来实现，因为 MySQL 本身是关系型数据库，设计上更倾向于纵向扩展（vertical scaling）。以下是对 MySQL 横向扩展的支持和实现方式的说明：

MySQL 横向扩展的定义

横向扩展是指通过增加更多的服务器节点来分担数据库负载，而不是仅通过增强单台服务器的硬件性能（如增加 CPU、内存或磁盘）来提升性能。MySQL 的横向扩展通常通过以下方式实现：

MySQL 横向扩展的主要方法

主从复制（Replication）

读写分离

分库分表（Sharding）

MySQL Cluster（NDB Cluster）

分布式数据库中间件

MySQL 横向扩展的适用场景

注意事项

与 NoSQL 数据库的对比

相比 NoSQL 数据库（如 MongoDB、Cassandra），MySQL 的横向扩展能力稍逊，因为 NoSQL 数据库天生为分布式设计，分片和扩展更简单。但 MySQL 在事务支持、SQL 标准兼容性和成熟生态方面有优势，适合需要强一致性和复杂查询的场景。

总结

MySQL 支持横向扩展，但需要通过主从复制、分库分表、MySQL Cluster 或分布式中间件等方式实现。选择具体方案时，需根据业务场景（如读写比例、数据量、可用性需求）进行权衡。对于简单场景，主从复制和读写分离已足够；对于大规模高并发场景，分库分表或分布式解决方案（如 Vitess、TiDB）更适合。

mysql 主从复制实验

root@ser745692301841:/dev_dir/docker/mysqlmasterslave# ls
docker-compose.yml  mysql-master.cnf  mysql-slave.cnf
root@ser745692301841:/dev_dir/docker/mysqlmasterslave# cat docker-compose.yml
version: '3'

services:
  mysql-master:
    image: mysql:latest
    container_name: mysql-master
    environment:
      - MYSQL_ROOT_PASSWORD=rootpassword
      - MYSQL_DATABASE=testdb
      - MYSQL_USER=testuser
      - MYSQL_PASSWORD=testpassword
    ports:
      - "3306:3306"
    volumes:
      - ./mysql-master-data:/var/lib/mysql
      - ./mysql-master.cnf:/etc/mysql/conf.d/mysql-master.cnf
    networks:
      - mysql-network

  mysql-slave:
    image: mysql:latest
    container_name: mysql-slave
    environment:
      - MYSQL_ROOT_PASSWORD=rootpassword
      - MYSQL_DATABASE=testdb
      - MYSQL_USER=testuser
      - MYSQL_PASSWORD=testpassword
    ports:
      - "3307:3306"
    volumes:
      - ./mysql-slave-data:/var/lib/mysql
      - ./mysql-slave.cnf:/etc/mysql/conf.d/mysql-slave.cnf
    depends_on:
      - mysql-master
    networks:
      - mysql-network

networks:
  mysql-network:
    driver: bridge
root@ser745692301841:/dev_dir/docker/mysqlmasterslave# cat  mysql-master.cnf
[mysqld] 
server-id=1 
log-bin=mysql-bin 
binlog-format=ROW
root@ser745692301841:/dev_dir/docker/mysqlmasterslave# cat  mysql-slave.cnf
[mysqld]
server-id=2 
relay_log=relay-bin
read-only=1

启动容器

root@ser745692301841:/dev_dir/docker/mysqlmasterslave# docker-compose up -d
Creating network "mysqlmasterslave_mysql-network" with driver "bridge"
Creating mysql-master ... done
Creating mysql-slave  ... done
root@ser745692301841:/dev_dir/docker/mysqlmasterslave# 
root@ser745692301841:/dev_dir/docker/mysqlmasterslave# docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS         PORTS                               NAMES
ab71e278ebd7   mysql:latest   "docker-entrypoint.s…"   5 seconds ago   Up 4 seconds   33060/tcp, 0.0.0.0:3307->3306/tcp   mysql-slave
ba06b2746d6f   mysql:latest   "docker-entrypoint.s…"   5 seconds ago   Up 5 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql-master

登录主节点容器

root@ser745692301841:/dev_dir/docker/mysqlmasterslave# docker exec -it mysql-master mysql -uroot -prootpassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

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>

在主节点创建复制用户

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'replpassword';
Query OK, 0 rows affected (0.012 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.003 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected, 1 warning (0.005 sec)

查看主节点状态

mysql> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1024 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.001 sec)

登录从节点

root@ser745692301841:/dev_dir/docker/mysqlmasterslave# docker exec -it mysql-slave mysql -uroot -prootpassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 9.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

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>

配置从服务器连接到主服务器

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='你的IP',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='replpassword',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=0,
  GET_SOURCE_PUBLIC_KEY=1;

启动从服务器复制

mysql> START REPLICA;
Query OK, 0 rows affected (0.067 sec)

检查从服务器复制状态

mysql> STOP REPLICA;
Query OK, 0 rows affected, 1 warning (0.001 sec)

mysql> RESET REPLICA ALL;
Query OK, 0 rows affected (0.014 sec)

mysql> CHANGE REPLICATION SOURCE TO
    ->   SOURCE_HOST='你的IP',
    ->   SOURCE_PORT=3306,
    ->   SOURCE_USER='repl',
    ->   SOURCE_PASSWORD='replpassword',
    ->   SOURCE_LOG_FILE='mysql-bin.000001',
    ->   SOURCE_LOG_POS=0,
    ->   GET_SOURCE_PUBLIC_KEY=1;
Query OK, 0 rows affected, 2 warnings (0.019 sec)

mysql> START REPLICA;
Query OK, 0 rows affected (0.072 sec)

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 你的IP
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000001
          Read_Source_Log_Pos: 158
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Source_Log_File: mysql-bin.000001
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 158
              Relay_Log_Space: 580
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: b0dc265d-7efb-11f0-9af3-0242ac120002
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.001 sec)

上面的 Replica_IO_Running: Yes、Replica_SQL_Running: Yes 你可以在master上做一些修改，主节点的修改将会同步到从节点。

mysql> STOP REPLICA;
Query OK, 0 rows affected (0.008 sec)