MYSQL 基于binlog 实现主从复制

环境

  1. ubuntu 24
  2. mysql 9.4.0

资料：

9.4 官方文档： https://dev.mysql.com/doc/refman/9.0/en/replication-multi-source-configuration.html

流程

  1. 创建mysql 应用 （主库和从库）（这里使用docker）
  2. 主库操作：
    1.  创建远程登录权限 - 这个账号用于数据读写  
    2. 创建专门用于复制的账户，授予此帐户复制所需的权限
    3. 使用创建的账号进行一次远程登录 （重要）
  3. 从机中：
    1. 创建远程登录权限 - 这个账号用于数据读写
    2. 建立主从关系
    3. 开启监听

 

一些用到的命令

# 查看容器IP
docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' <container_name_or_id>

# MYSQL 中执行：

# 查看当前用户权限
SHOW GRANTS;
# 查看特定用户的权限
SHOW GRANTS FOR 'root'@'localhost';

# 在 主机上查看进程状态
SHOW PROCESSLIST;

# 查看 server_id
- SHOW VARIABLES LIKE 'server_id';

# 查看binlog 
- SHOW BINARY LOG STATUS;

 

创建

1、创建容器

  • mysql/master/conf 与 mysql/master/data 目录
  • mysql/slave/conf 与 mysql/slave/data 目录

2、在conf 下新建 my.cnf 文件， 内容：

[mysqld]
# 服务器唯一id，默认值1
server-id=1

# 设置日志格式，默认值ROW, 开发环境使用STATEMENT
# STATEMENT: MySQL 会记录执行的 SQL 语句，而不是记录每个行的变化。这意味着在主服务器上执行的每个 SQL 语句都会被记录到二进制日志中，供从服务器重放。 缺点：不一致性风险：如果 SQL 语句依赖于非确定性函数（如 NOW()、RAND() 等），在主从服务器上执行的结果可能不同； 某些 SQL 语句无法使用 STATEMENT 格式进行复制。
# ROW：记录每一行的变化，确保在主从服务器上数据一致，但会占用更多空间。
# MIXED:自动选择使用 STATEMENT 或 ROW 格式，根据具体的 SQL 语句类型来决定。
binlog_format=ROW

# 二进制日志名，默认binlog
# log-bin=binlog

# 设置需要复制的数据库，默认复制全部数据库
binlog-do-db=tfdata

# 设置不需要复制的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema

 


3、创建应用 - 主机与从机

docker run -d -p 3307:3306 -v /home/tf/mysql/master/conf:/etc/mysql/conf.d -v /home/tf/mysql/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql-master mysql:9.4.0
docker run -d -p 3308:3306 -v /home/tf/mysql/slave/conf:/etc/mysql/conf.d -v /home/tf/mysql/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql-slave1 mysql:9.4.0

 

4、mysql -uroot -p -- 可选操作
- pwd: 123456
# caching_sha2_password 新版使用caching_sha2_password 替换了mysql_native_password
# 可以通过 show plugins; 查看当前版本自动加载的插件
- ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';

5、主机中： 创建远程登录权限 - 这个账号用于数据读写 

CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;

 


# 创建专门用于复制的账户，授予此帐户复制所需的权限

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

 

6、从机中： 创建远程登录权限 - 这个账号用于数据读写

CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;

 

7、重点 - 使用test与 slave 分别进行一次远程连接 主机库，在主库中留下缓存（连接后binlog 会改变）
- 原因：遇到的问题：
- Last_IO_Error: Error connecting to source 'test@172.17.0.2:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
- 解决：
- 使用slave进行一次远程连接 主机库（连接后 binlog 会改变）
- 连接之后binlog 会有所增加 通过 SHOW BINARY LOG STATUS; 可以查看到

8、建立主从关系 

# 查看binlog
- SHOW MASTER STATUS; 换成了 SHOW BINARY LOG STATUS;
# 8.0 后命令变更为： FOR CHANNEL "slave" 中的slave为自定义通道名
- CHANGE REPLICATION SOURCE TO SOURCE_HOST="172.17.0.2", SOURCE_USER="slave", SOURCE_PASSWORD="123456", SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=853 FOR CHANNEL "slave";
- # 启动通道

- START REPLICA;

- 或
- START REPLICA FOR CHANNEL "slave";


- # 查看通道状态

- SHOW REPLICA STATUS \G;
- SHOW REPLICA STATUS FOR CHANNEL "slave"\G

 

 

关于REPLICA 的一些擦作：

# 停止通道
# 所有
STOP REPLICA;
# 单一
STOP REPLICA FOR CHANNEL "slave1";
# 重置通道
- RESET REPLICA;
- RESET REPLICA FOR CHANNEL "slave1";

# 查看 server_id
- SHOW VARIABLES LIKE 'server_id';

 

可能遇到的问题

  1. 从库连接失败

    1.  Replica_IO_Running: Connecting
      Replica_SQL_Running: Yes

    2. Last_IO_Error: Error connecting to source 'slave@172.17.0.2:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
    3. 这个是因为你的slave 账户权限不对 或 没有执行 第7步 就先建立复制通过
    4. 解决：
      1. 检查账号权限 和 远程登录一次
      2. 从库中 重置通过， 重新建立
  2. 连接成功， 但是复制通道开启失败

    1. Replica_IO_Running: Yes
      Replica_SQL_Running: No

    2. 这个基本上都是主库 binglog 索引位置发生改变
    3. 解决：
      1. SHOW BINARY LOG STATUS;
      2. 更新成最新binlog 索引位置

 

PS:
- 项目启动后避免在主库上进行权限和新增用户等操作（就不该有， 用户权限应该是应用程序中控制的）
- 从机与从机是相互独立的
- 数据不一致问题： --- 解决
- 原因： 网络延迟 或 主库或从库出现故障
-解决：
- 使用GTID GTID是一种全局事务标识符，可以在主从库之间进行数据同步。使用GTID可以避免由于网络延迟等原因导致的数据不一致。
- 第三方工具：例如pt-table-checksum和pt-table-sync 来检查和同步主从库之间的数据。

 

posted @ 2025-10-01 12:42  萤huo虫  阅读(3)  评论(0)    收藏  举报