MySQL 分库分表 + 主从复制

一、主从复制

1. 环境说明

1个master节点，2个slave节点

  • mysql-master
  • mysql-slave1
  • mysql-slave2

此处使用docker启动多个 MySQL 容器,为每个容器挂载配置文件

docker run -d --name mysql-master -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:8.0
docker run -d --name mysql-slave1 -e MYSQL_ROOT_PASSWORD=root -p 3307:3306 mysql:8.0
docker run -d --name mysql-slave2 -e MYSQL_ROOT_PASSWORD=root -p 3308:3306 mysql:8.0

创建配置文件目录

mkdir -p ~/mysql/master ~/mysql/slave1 ~/mysql/slave2

2. 修改配置

master的 my.cnf （~/mysql/master/my.cnf）

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

slave1 的 my.cnf（~/mysql/slave1/my.cnf）

[mysqld]
server-id=2
relay_log_recovery=ON
read_only=ON
super_read_only=ON
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

slave2 的 my.cnf（~/mysql/slave2/my.cnf）

[mysqld]
server-id=3
relay_log_recovery=ON
read_only=ON
super_read_only=ON
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

3. 挂载配置文件方式重新运行容器

docker run -d --name mysql-master \
  -v ~/mysql/master/my.cnf:/etc/mysql/conf.d/my.cnf \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3306:3306 mysql:8.0

docker run -d --name mysql-slave1 \
  -v ~/mysql/slave1/my.cnf:/etc/mysql/conf.d/my.cnf \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3307:3306 mysql:8.0

docker run -d --name mysql-slave2 \
  -v ~/mysql/slave2/my.cnf:/etc/mysql/conf.d/my.cnf \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3308:3306 mysql:8.0

4. 在 Master 上创建复制账号：

进入mysql-master容器

docker exec -it mysql-master mysql -uroot -proot

执行SQL

```sql
CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

```

5. 在 Slave 上配置复制

进入 slave1： docker exec -it mysql-slave1 mysql -uroot -proot

执行：

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='host.docker.internal',  -- 如果是 Linux，改成宿主机IP
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='replpass',
  SOURCE_AUTO_POSITION=1;

START REPLICA;
SHOW REPLICA STATUS\G

进入 slave2 做同样的配置（server-id 不同）。

6. 验证主从

  1. 在 master 插入数据：
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO t1 VALUES (1, 'hello');
  1. 然后在 slave1、slave2：
SELECT * FROM testdb.t1;

看到数据同步，就说明复制成功。

7. 增强：稳定高可用要点

  1. GTID（已开启）：保证切换时不用管 binlog 文件位置。
  2. 半同步复制（Semi-Sync，可选）：减少数据丢失风险。 在 master/replica 里：
    INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

SET GLOBAL rpl_semi_sync_source_enabled=1;
SET GLOBAL rpl_semi_sync_replica_enabled=1;
  3. 只读保护：slave 开启 read_only=ON + super_read_only=ON，避免误写。
  4. Failover 管理：结合 Orchestrator 管理拓扑，故障时自动提升 slave。
  5. 读写分离：可加 ProxySQL/Haproxy，将写流量导向 master，读流量分散到 slaves。
  6. 备份：定期使用 Percona XtraBackup 做物理备份，保留 binlog 做点时间恢复（PITR）。