mysql主从复制

原创已于 2025-07-24 15:10:50 修改 · 1k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #阿里云

于 2024-03-08 14:14:16 首次发布

主库配置开启binglog

vi /etc/my.cnf

log-bin=/mysql/mysql-bin/mysql-bin
binlog-format=ROW
server_id=1

从库配置

查询主库的server_id，从库配置不要重复就行

show variables like '%server_id%';

在这里插入图片描述
编辑从库的my.cnf文件
在文件中[mysqld]下面增加如下配置

server-id               =  123456789              //服务 ID，主从实例 server-id 需不同。
log_bin                 =  /var/log/mysql/mysql-bin.log
expire_logs_days        =  10;
max_binlog_size         =  100M
replicate-do-db         =  exampledb                //需要同步的数据库
replicate-ignore-db     =  mysql                  //不需要同步的数据库
replicate-ignore-db     =  information_schema     //不需要同步的数据库
replicate-ignore-db     =  performance_schema     //不需要同步的数据库

# 如果只是同步部分表则需要配置如下
replicate-do-table      =  xzs.user

# GTID
gtid_mode=on
enforce_gtid_consistency=on
binlog_format=row                             //设置 binlog 为 row
log-slave-updates=1

设置同步选项
执行前需要在主库赋予用户的REPLICATION CLIENT权限，否则Slave_IO_Running：一直是Connecting。赋权限sql如下

GRANT REPLICATION SLAVE ON *.* TO 'user'@'%';
FLUSH PRIVILEGES;

登录从库Mysql的root用户，执行如下命令，记得把用户和密码换成自己的

change master to master_host='rm-xxx.mysql.rds.aliyuncs.com', master_user='root', master_password='xxxx', master_port=3306, master_log_file='mysql-bin.000637', master_log_pos= 6433378, master_connect_retry=30, get_master_public_key=1;

值得注意的是需要加get_master_public_key=1，否则有可能报错

Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

其中的master_log_file和master_log_file通过在master上执行如下语句获得

show master status;

执行这个权限也是要root权限或者通过如下脚本授予用户权限

GRANT REPLICATION CLIENT ON *.* TO 'user'@'%';
FLUSH PRIVILEGES;

在这里插入图片描述
但是需要注意如果主库数据有变更，这两个值可能会一直有变动，可以先执行如下命令锁表

# 锁表
flush tables with read lock;
# 解锁
unlock tables

启动从实例

start slave;

检查同步结果

show slave status\G

查看系统返回信息中 Slave_IO_Running 和 Slave_SQL_Running 的状态是否为 Yes，如下所示。
在这里插入图片描述
同步阿里云的RDS至自建数据库也是可以的，阿里云rds默认开启了binglog，所以我们无需对主库进行配置，直接配置从库即可

同步过程中报错怎么处理

有时候同步会报错，这个错可能是比如你在从库建了一个表，然后主库建了一个相同的表名，这个时候同步就会报错表名冲突。当出现这种问题我们一般是选择跳过这个错误，因为从库已经有这张表了，那么怎么跳过呢？

我们可以通过查询如下表来获取同步失败的具体原因（需要有root权限）

select * from performance_schema.replication_applier_status_by_worker;

此时会查询到一些关键的字段
在这里插入图片描述

方案一：mysql8无效

stop slave;
set global sql_slave_skip_counter=1;
start slave;

上诉方案在mysql8中是无效的，原因是

在MySQL 8中，如果你使用的是基于GTID的复制模式，那么 set global sql_slave_skip_counter=1; 这个命令将不会生效 在GTID模式下，MySQL不允许使用 sql_slave_skip_counter 参数来跳过错误，因为这会导致GTID的不一致

方案二：跳过指定的事务（建议），

这种方法通过在从库上插入一个空的事务来跳过错误，而不会破坏GTID的一致性

stop slave;
SET GTID_NEXT='ab1b2733-2401-11e7-82fc-525400abbf4b:50';
begin;
commit;
SET GTID_NEXT="AUTOMATIC";
start slave;

如果不行就看方案四

方案三：跳过指定的错误码

需要写到配置文件中并重启mysql服务

vi /etc/my.cnf

在这里插入图片描述

systemctl restart mysqld

如果不行就看方案四
这里列出来一些常见的错误码

slave-skip-errors = 1537
slave-skip-errors = 1049
slave-skip-errors = 1062
slave-skip-errors = 1060
slave-skip-errors = 1032
slave-skip-errors = 1035
slave-skip-errors = 13146
slave-skip-errors = 1091
slave-skip-errors = 1054

方案四

有时候当由于某个事务卡主了同步，而我们又长时间没有发现的情况下，会导致从库的log_pos与主库的偏移较远，此时使用方案二或者四是不行的，此时就是需要重新同步，可以尝试如下

-- 1. 主库锁库
flush tables with read lock;

-- 2. 把数据备份恢复到从库，这步略过，可以使用navcat数据比对或其他方法

-- 3. 查询主库的log_pos
show master status;

-- 4. 重置从库之前的设置并重新设置
stop slave ;
RESET SLAVE;
change master to master_host='rm-xxx.mysql.rds.aliyuncs.com', master_user='root', master_password='xxxx', master_port=3306, master_log_file='mysql-bin.000637', master_log_pos= 6433378, master_connect_retry=30, get_master_public_key=1;
start slave;