一、mysql主从复制的原理
二、主从复制有以下几种方式：
1、同步复制：master的变化，必须等待slave-1，slave-2……slave-n完成以后才能返回
2、异步复制：master只需要完成自己的数据库操作即可，至于slave是否收到二进制日志，是否完成操作，不用关心。mysql默认设置
3、半同步复制：master只保证slave中的一个操作成功，就返回，其他slave不管，这个功能，是由google为mysql引入的
三、环境配置
虚拟机：VMware Fusion
操作系统：AlmaLinux 64
mysql：8.0
docker：Docker version 24.0.7, build afdd53b四、配置主从复制同步
#这里可以直接把对应的配置文件挂载出来，方便后续查看，不用进容器
docker run -d --name mysqlv8 \
-p 3306:3306 \
--restart=always \
-v /wj/mysql/config:/etc/mysql/conf.d \
-v /wj/mysql/data:/var/lib/mysql \
-v /wj/mysql/logs:/var/log/mysql \
-e MYSQL_ROOT_PASSWORD=Cde12345 \
mysql:8.0
#这里有个注意事项，在conf.d创建完my.cnf文件以后不要把/etc/my.cnf的全部内容复制过来，如果复制过来后续启动会报错，只添加需要的内容
主配置文件（如 /etc/mysql/my.cnf）通过 !includedir 包含 /etc/mysql/conf.d/ 目录五、配置主服务器
#日志系统的名称（这个名字是可以随便修改的）
#启动mysql二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
log_bin=master-a-bin
#设置日志文件的格式
binlog-format=ROW
#当前服务器的主机号（ID必须是唯一的）
server-id=1
#将要实现主从复制的数据库
#指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog_do_db=ddm
#binlog_ignore_db指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可六、配置从服务器
如果用8.0在创建用户的时候就要确定是多个IP还是给固定IP创建的用户，如果创建的用户是精确的IP（例如：172.16.156.134）授权的时候就不能用模糊匹配，否则会报错，创建的用户要和授权地址保持一致
#配置从服务器登录主服务器的账号授权
#5.7之前的
#grant replication slave on *.* to 'root'@'172.16.156.%' identified by 'root';
#8.0以后的需要先创建用户、在单独授权
-- 显式创建用户
CREATE USER 'repl'@'172.16.156.134' IDENTIFIED BY 'Secur3P@ss!';
-- 单独授权，，，用172.16.156.%模糊创建的账户授权一直connecting，改为精准IP问题解决
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.156.%';
FLUSH PRIVILEGES;
#GRANT <权限> ON <数据库.表> TO <用户@主机> IDENTIFIED BY <密码>;
#GRANT REPLICATION SLAVE：
#授予用户主从复制权限，允许从库（Slave）通过该用户连接主库（Master）并同步数据。
#ON *.*
#权限作用范围为 所有数据库和所有表，因主从复制需全局权限才能读取二进制日志。
#TO 'root'@'172.16.156.134'
#'root'：指定用户名为 root（不推荐使用高权限账户）。
#@'172.16.156.134'：限定用户仅允许从 IP 172.16.156.134 连接主库。
#IDENTIFIED BY 'root'
#设置用户密码为 root（存在严重安全隐患，密码应复杂化）。
#刷新更改的配置
flush PRIVILEGES;七、用户和权限校验
检查用户是否存在
SELECT user, host FROM mysql.user WHERE user = 'repl';
验证权限：
SHOW GRANTS FOR 'repl'@'172.16.156.%';
八、配置从服务器的my.cnf文件
#日志文件名称
log_bin=master-a-bin
#二进制日志的格式，跟主服务器一样
binlog-format=ROW
#各个服务器的ID，这个必须是唯一的
server-id=2
#双主互相备份
#log-slave-updates=true配置完主、从服务器需要重启mysql镜像
重启完主服务器，进入到mysql查看master的状态
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| master-a-bin.000003 | 157 | ddm | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)file：master-a-bin.000003日志文件
position：157文件位置（偏移量）
Binlog_Do_DB:ddm实现主从复制的数据库
九、重启从服务器，进入到mysql执行
#重置从库复制链路
change master to master_host='172.16.156.128',
master_port=3306,
master_user='repl',
master_password='Secur3P@ss!',
master_log_file='master-a-bin.000016',
master_log_pos=157;
#master_host：主库地址
#master_port：主库短口
#master_user：刚才创建的用户名
#master_password：刚才创建的密码
#master_log_file：查主库状态的文件名
#master_log_pos：查主库的偏移量
停止所有复制线程：
STOP SLAVE;
开启复制线程
START SLAVE;到此主从复制完成!
十一、双主一从主从同步功能实现
双主单从模式：两个主节点（Master1、Master2）互相复制，同时从节点（Slave）仅从其中一个主节点同步数据
1、修改配置文件（双主节点均需操作）
[mysqld]
server-id = 1 # Master1设为1，Master2设为2，确保唯一性
binlog-format=ROW
log-bin = master-a-bin # 开启二进制日志
binlog_format = mixed # 推荐混合模式（兼容性更好）
auto-increment-increment = 2 # 自增步长（避免主键冲突）
auto-increment-offset = 1 # Master1设为1，Master2设为2
bind-address = 0.0.0.0 # 允许远程连接2、创建复制用户（双主节点均需操作）
这里的地址双主要互配
CREATE USER 'repl'@'172.16.156.136' IDENTIFIED WITH mysql_native_password BY 'Secur3P@ss!'; # 显式指定认证插件
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.156.136'; # 授予复制权限
FLUSH PRIVILEGES; # 刷新权限3、配置双向主从关系
在 Master1 上指向 Master2：
CHANGE MASTER TO
MASTER_HOST = '172.16.156.136',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'Secur3P@ss!',
MASTER_AUTO_POSITION = 1; # 使用GTID自动定位（需开启GTID）
START SLAVE; # 启动复制线程这里报错了
mysql> CHANGE MASTER TO
-> MASTER_HOST = '172.16.156.136',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = 'Secur3P@ss!',
-> MASTER_AUTO_POSITION = 1;
ERROR 1777 (HY000): CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1 cannot be executed because @@GLOBAL.GTID_MODE = OFF.
当设置 MASTER_AUTO_POSITION = 1（或 SOURCE_AUTO_POSITION = 1）时，MySQL要求必须启用GTID模式（即 @@GLOBAL.GTID_MODE = ON
当前环境中 GTID_MODE 为 OFF，因此无法直接使用自动定位功能
解决方案：
1、停止复制进程（如果从库已启动复制）：
STOP SLAVE;
2、逐步修改GTID模式（需依次执行，不可跳过）：
#开启 ENFORCE_GTID_CONSISTENCY，GTID 要求所有事务必须符合一致性约束，需先启用该参数：
SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON;
-- 步骤 1：设置为 OFF_PERMISSIVE
SET GLOBAL GTID_MODE = OFF_PERMISSIVE;
-- 步骤 2：设置为 ON_PERMISSIVE
SET GLOBAL GTID_MODE = ON_PERMISSIVE;
-- 步骤 3：检查是否有未完成的事务
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; -- 确保 Value 为 0
-- 步骤 4：最终设置为 ON
SET GLOBAL GTID_MODE = ON;
#主从架构的额外操作
#若为主从复制环境，需在所有节点执行上述操作，并在从库启用自动定位：
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE; -- 启用基于 GTID 的复制
#持久化配置
#修改 MySQL 配置文件 (my.cnf 或 my.ini)，避免重启后失效：
gtid_mode = ON
enforce_gtid_consistency = ON
另外一个主也要配置在 Master2 上指向 Master1：
CHANGE MASTER TO
MASTER_HOST = '172.16.156.128',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'Secur3P@ss!',
MASTER_AUTO_POSITION = 1; # 使用GTID自动定位（需开启GTID）
START SLAVE; # 启动复制线程配置从服务器
log_bin=master-a-bin
#二进制日志的格式，跟主服务器一样
binlog-format=ROW
#各个服务器的ID，这个必须是唯一的
server-id=3
#双主互相备份
#log-slave-updates=true
read-only = 1 # 从节点设为只读配置单向主从关系，只配置一个即可，因为这里配置了自动定位可以不在配置偏移量等信息
change master to master_host='172.16.156.128',
master_port=3306,
master_user='repl',
master_password='Secur3P@ss!',
MASTER_AUTO_POSITION = 1;验证与监控，检查复制状态（所有节点）
SHOW SLAVE STATUS\G # 关注 `Slave_IO_Running` 和 `Slave_SQL_Running` 是否为 `Yes`主一：
主二：
从：
主二其实没有出现waiting for的界面，他其实是有问题的，报了一个1236异常信息
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.156.136
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000002
Read_Master_Log_Pos: 157
Relay_Log_File: f3a6c7c47d7c-relay-bin.000003
Relay_Log_Pos: 279
Relay_Master_Log_File: master-a-bin.000002
Slave_IO_Running: No
Slave_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_Master_Log_Pos: 157
Relay_Log_Space: 718
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'could not find next log; the first event '' at 4, the last event read from './master-a-bin.000002' at 157, the last byte read from './master-a-bin.000002' at 157.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 676a8acd-0885-11f0-a531-925960da376c
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 250326 00:30:50
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified这个问题其实是因为开启了
MASTER_AUTO_POSITION = 1;
然后主库和从库的binlog日志不同步，随后又使用RESET MASTER导致binlog文件缺失
使用 RESET MASTER 命令彻底清除所有binlog
RESET MASTER
这个命令在生产中要谨慎使用解决办法：
1:关闭自动
stop slave;
change master to master_auto_position=0;
start slave;2、手动指定binlog文件位置
#让主服务先生成新的log日志
flush logs;
#查看主的状态
show master status;
#在另一个主库和从库上修改文件位置
change master to master_host='172.16.156.128',
master_port=3306,
master_user='repl',
master_password='Secur3P@ss!',
MASTER_LOG_FILE='master-a-bin.000003',
MASTER_LOG_POS=157;3、查看状态
show slave status \G;
注意事项：
1、避免数据冲突
使用 auto-increment-increment 和 auto-increment-offset 分散主键生成
应用层避免同时在双主节点操作同一数据集
2、高可用性增强
结合 Keepalived 或 ProxySQL 实现自动故障切换
定期备份并监控主从延迟（Seconds_Behind_Master）
3、安全配置
限制复制用户IP范围（如 'repl'@'192.168.1.%'）
启用SSL加密主从通信
扫一扫
715
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
