mysql 主从练习

--character-set-server=utf8mb4
设置数据库默认字符集为 utf8mb4。
utf8mb4 是真正完整的 UTF-8，支持 emoji、生僻汉字；MySQL 里旧的utf8只是 utf8mb3，不支持 4 字节字符。
--collation-server=utf8mb4_unicode_ci
字符集对应的排序规则。
unicode_ci：基于 Unicode 标准比较，大小写不敏感（ci = case insensitive）
作用：字符串查询、排序、索引比较用这套规则。
--server-id=1
服务器唯一 ID，主从复制核心参数。
集群内每个 MySQL 实例 ID 必须不一样；主库写1，从库写2。
用于 binlog 日志标记这条日志是哪个实例产生的，从库靠 ID 区分来源。
不配置 server-id，MySQL 直接不启用主从复制。
--log-bin=mysql-bin
开启二进制日志 binlog，文件名前缀是mysql-bin。
binlog 记录所有对数据库的写操作（INSERT/UPDATE/DELETE/DDL），主从复制本质就是从库拉取主库 binlog，重放 SQL。
没有 binlog，主库无法同步数据给从库。
--binlog-format=ROW
binlog 记录格式，一共 3 种：
ROW（行模式，推荐）：记录每一行数据修改前后的值。
优点：最可靠，存储过程、触发器、函数都能正常同步；生产首选。
STATEMENT：记录原始 SQL 语句，有不确定性（now()、rand () 会造成主从数据不一致）
MIXED：混合模式
我们主从复制固定用 ROW。
--gtid_mode=ON
开启 GTID（全局事务 ID）。
GTID 给每一个事务分配一个全局唯一 ID，格式：server-uuid:事务号。
传统主从需要记录「binlog 文件名 + 偏移量 position」；GTID 模式下，从库只需要告诉主库：我已经同步到哪个事务，自动找位点，切换主库、故障转移非常方便。
现在新环境主从复制优先 GTID 模式。
--enforce-gtid-consistency=ON
GTID 一致性强制校验。
开启后，MySQL 会禁止执行无法被 GTID 安全复制的 SQL（比如 CREATE TABLE ... SELECT 这类非原子语句）
保证 binlog 里所有事务都可以用 GTID 追踪，避免 GTID 主从同步异常。
开启 gtid_mode=ON 时，必须配套打开这个参数。

MySQL Docker 主从复制配置方案

方案说明

两个容器：mysql-node（主库 Master）、mysql-node1（从库 Slave），MySQL8.0 主从复制（GTID 模式，推荐）

前提：两个容器在同一个 docker 网络，互相可以 ping 通；两个实例版本必须一致（都是 mysql8.0）架构：主库写，从库自动同步；从库默认不能写。如果你需要双向互相同步（主主）我后面也可以给配置。

一、先确认 docker 网络

# 查看容器网络
docker inspect mysql-node | grep NetworkMode
docker inspect mysql-node1 | grep NetworkMode

如果不在同一个自定义网络，创建并加入同一个网络：

docker network create mysql-net
docker network connect mysql-net mysql-node
docker network connect mysql-net mysql-node1

容器内部通信：直接用容器名做 hostname，主库地址就是 mysql-node，端口 3306。

二、修改主库 mysql-node 的配置（command 参数，不用单独挂 cnf）

修改 mysql-node 的 compose command，增加主库配置：

command:
  - --character-set-server=utf8mb4
  - --collation-server=utf8mb4_unicode_ci
  - --server-id=1
  - --log-bin=mysql-bin
  - --binlog-format=ROW
  - --gtid_mode=ON
  - --enforce-gtid-consistency=ON

server-id=1 主库唯一 ID，不能和从库重复。

重启主库：

docker compose up -d

主库内操作：创建同步账号

docker exec -it mysql-node mysql -uroot -proot123456

执行 SQL：

CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
-- 锁表，防止数据变动（全新空库可以省略）
FLUSH TABLES WITH READ LOCK;
-- 查看master状态，GTID模式不需要记录binlog文件和pos
SHOW MASTER STATUS;

保持这个 mysql 会话不要关闭，解锁会中断快照。如果两个库现在数据不一致，需要mysqldump 把主库数据导出导入到从库，再开启同步。

三、修改从库 mysql-node1 的配置

command:
  - --character-set-server=utf8mb4
  - --collation-server=utf8mb4_unicode_ci
  - --server-id=2
  - --relay-log=mysql-relay-bin
  - --gtid_mode=ON
  - --enforce-gtid-consistency=ON

server-id=2，必须和主库不一样。

重启从库：

docker compose up -d

从库内配置主从关联

docker exec -it mysql-node1 mysql -uroot -proot123456

STOP SLAVE;
RESET SLAVE ALL;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-node',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='Repl@123456',
SOURCE_AUTO_POSITION=1; -- GTID自动位点，强烈推荐
START SLAVE;
-- 查看状态
SHOW SLAVE STATUS\G

✅ 成功标志：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两个都是 Yes 代表同步正常。

四、数据对齐（非常重要！）

如果两个库已经存在业务表 / 数据，必须先把主库数据完整导入从库，否则直接启动同步会报主键冲突。

# 导出主库全量备份
docker exec mysql-node mysqldump -uroot -proot123456 --all-databases --master-data=2 > backup.sql
# 导入到从库
docker exec -i mysql-node1 mysql -uroot -proot123456 < backup.sql

导入完成后，再执行上面从库的CHANGE REPLICATION SOURCE。

五、测试同步

在主库 mysql-node 建表、插入数据：

use test_db;
create table t1(id int);
insert into t1 values(1);

到从库 mysql-node1 查询，能查到这条记录即同步成功。

六、可选：主主双向同步（两个库互相读写同步）

注意：主主有主键冲突风险，测试玩可以，生产谨慎。

mysql-node：server-id=1
mysql-node1：server-id=2互相给对方配置CHANGE REPLICATION SOURCE，开启双向复制。

常见坑

server-id 重复：两个节点 ID 必须不一样，最常见报错
容器之间不通：必须在同一个 docker 自定义网络，不要用默认 bridge
账号 host 限制：repl 用户授权@%，允许容器网络访问
数据不一致：没有先做 mysqldump 全量备份，直接启动同步报主键冲突
8.0 密码认证：repl 账号默认 caching_sha2_password，如果报错，创建用户时指定：

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'Repl@123456';

完整记录

MySQL GTID主从复制搭建操作文档

一、环境信息

主库：mysql-master（Docker 容器）
从库：mysql-slave（Docker 容器）
数据库版本：MySQL 5.7
复制模式：GTID 主从复制
业务库：test_db

二、搭建步骤

1. 主库配置（my.cnf）

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

修改配置后重启主库 MySQL。

2. 主库创建复制账号

CREATE USER repl@'%' IDENTIFIED BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;

3. 主库全量备份（mysqldump）

重要：备份用来初始化从库数据，保证主从初始数据一致

docker exec mysql-master mysqldump -uroot -p --single-transaction --master-data=2 --databases test_db > test_db_backup.sql

使用全局读锁--lock-all-tables
备份后解除	UNLOCK TABLES;

参数说明：--single-transaction 使用 InnoDB 快照备份，不会加全局读锁（本次踩坑重点）

4. 从库配置（my.cnf）

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
relay_log=relay-bin
log_slave_updates=ON

修改配置后重启从库 MySQL。

5. 导入备份到从库

docker cp test_db_backup.sql mysql-slave:/tmp/
docker exec mysql-slave mysql -uroot -p < /tmp/test_db_backup.sql

6. 从库配置 GTID 主从同步

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='repl',
MASTER_PASSWORD='Repl@123456',
MASTER_AUTO_POSITION=1;

START SLAVE;

7. 校验主从状态（从库执行）

SHOW SLAVE STATUS\G

✅ 正常标准：Slave_IO_Running: Yes``Slave_SQL_Running: Yes

8. 测试验证

主库建表

CREATE TABLE test_db.`eploy` (
`name` varchar(255) NOT NULL,
`bumen` varchar(255) NOT NULL,
`time` datetime NOT NULL
);

主库插入测试数据

INSERT INTO test_db.eploy (name, bumen, time)
VALUES
('张三', '研发部', '2026-01-10 08:30:00'),
('李四', '研发部', '2026-02-15 09:10:00'),
('王五', '产品部', '2026-03-20 10:00:00'),
('赵六', '测试部', '2026-04-05 14:20:00'),
('孙七', '运维部', '2026-05-12 16:45:00'),
('周八', '人事部', '2026-06-18 11:30:00'),
('吴九', '财务部', '2026-07-01 08:50:00'),
('郑十', '市场部', '2026-08-22 15:10:00');

从库查询校验数据同步

SELECT * FROM test_db.eploy;

预期结果：主库新增表、新增数据，自动同步到从库。

三、本次实操遇到的问题 & 解决方案

问题 1：主库执行 CREATE TABLE 建表语句，长时间卡住，状态 `Waiting for global read lock`

现象执行建表 SQL 一直无返回，show processlist看到 DDL 线程状态：Waiting for global read lock。

根因全局读锁（FLUSH TABLES WITH READ LOCK）被某个长连接持有；一般是备份命令使用了--lock-all-tables，备份进程异常中断，锁不会自动释放。

全局读锁：实例级别只读，所有 DDL（建表、改表）全部阻塞。

解决方案

释放全局锁

UNLOCK TABLES;

找到持有锁的 Sleep 长连接，kill 会话，锁自动释放

KILL 会话ID;

后续备份改用--single-transaction，InnoDB 快照备份，不会加全局读锁，规避该问题。

注意：Binlog Dump GTID 是主从复制推送 binlog 正常线程，禁止 kill。

问题 2：查询`information_schema.metadata_locks`报错 `Unknown table 'METADATA_LOCKS'`

现象执行SELECT * FROM information_schema.metadata_locks;提示表不存在。

根因metadata_locks 元数据锁视图 MySQL5.7 版本才支持，低版本 MySQL 没有这个表，无法查看元数据锁。

解决方案放弃该视图，改用show processlist观察线程状态定位锁问题。

问题 3：KILL 线程时报错 `ERROR 1094 (HY000): Unknown thread id`

现象执行KILL 14;提示线程 ID 不存在。

根因该线程已经自动断开 / 回滚，线程已经销毁，processlist 里已经清除这条记录。

解决方案不需要处理，重新执行show processlist获取最新线程 ID 再操作。

四、日常运维常用命令

主库查看复制线程

show processlist;

Binlog Dump GTID：正常主从推送线程。

从库查看主从复制状态

show slave status\G

停止 / 启动从库复制

STOP SLAVE;
START SLAVE;

五、踩坑总结

mysqldump 备份优先使用--single-transaction，尽量避免--lock-all-tables，防止全局读锁阻塞 DDL。
Waiting for global read lock = 全局读锁，优先排查长连接 / 备份残留锁。
MySQL5.6 没有metadata_locks表，锁排查只能依靠show processlist。
Binlog Dump GTID线程是主从正常工作标志，不要杀掉。

你看这份文档是否合适？我可以帮你导出成 Markdown 文本，或者精简成适合内部 wiki 的版本。

posted @ 2026-10-14 15:16 阿砖阅读(5) 评论(0) 收藏举报

刷新页面返回顶部

mysql 主从练习

mysql 主从练习

方案说明

一、先确认 docker 网络

二、修改主库 mysql-node 的配置（command 参数，不用单独挂 cnf）

主库内操作：创建同步账号

三、修改从库 mysql-node1 的配置

从库内配置主从关联

四、数据对齐（非常重要！）

五、测试同步

六、可选：主主双向同步（两个库互相读写同步）

常见坑

完整记录

一、环境信息

二、搭建步骤

1. 主库配置（my.cnf）

2. 主库创建复制账号

3. 主库全量备份（mysqldump）

4. 从库配置（my.cnf）

5. 导入备份到从库

6. 从库配置 GTID 主从同步

7. 校验主从状态（从库执行）

8. 测试验证

三、本次实操遇到的问题 & 解决方案

问题 1：主库执行 CREATE TABLE 建表语句，长时间卡住，状态 Waiting for global read lock

问题 2：查询information_schema.metadata_locks报错 Unknown table 'METADATA_LOCKS'

问题 3：KILL 线程时报错 ERROR 1094 (HY000): Unknown thread id

四、日常运维常用命令

主库查看复制线程

从库查看主从复制状态

停止 / 启动从库复制

五、踩坑总结

公告

问题 1：主库执行 CREATE TABLE 建表语句，长时间卡住，状态 `Waiting for global read lock`

问题 2：查询`information_schema.metadata_locks`报错 `Unknown table 'METADATA_LOCKS'`

问题 3：KILL 线程时报错 `ERROR 1094 (HY000): Unknown thread id`