MySQL 配置主从同步

Rick.c.137

已于 2023-02-01 08:38:53 修改

阅读量1.9k

点赞数 3

分类专栏：工作日记文章标签： mysql 数据库

于 2023-01-31 20:50:45 首次发布

本文链接：https://blog.csdn.net/qq_36964761/article/details/128823306

版权

工作日记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL主从复制的流程

主库db的更新事件(update、insert、delete)被写到binlog
主库创建一个binlog dump thread，把binlog的内容发送到从库
从库启动并发起连接，连接到主库
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

注：上述流程为相对流程，并非绝对流程

MySQL 主从复制主要用途

l 读写分离
在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
l 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
l 高可用HA
l 架构扩展
随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL 主从形式

一主一从

![https://cdn.nlark.com/yuque/__puml/c13e1dbb89a738099944d63fa97f5378.svg]

一主多从，提高系统的读性能

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。
多主一从（从5.7开始支持）
在这里插入图片描述

多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。
双主复制

在这里插入图片描述

双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。
级联复制
在这里插入图片描述

级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

Slave配置项

CHANGE MASTER TO option [, option] ...
 
 
option:
    MASTER_BIND = 'interface_name'  		# 网卡信息
  | MASTER_HOST = 'host_name'				# master 主机
  | MASTER_USER = 'user_name'				# 用户名称
  | MASTER_PASSWORD = 'password'			# 密码
  | MASTER_PORT = port_num					# master 端口
  | MASTER_CONNECT_RETRY = interval			# 重连到master时的超时等待时间，默认为60秒。
  | MASTER_RETRY_COUNT = count				# 重连次数 0 表示重连次数无限制。
  | MASTER_DELAY = interval					# 默认值为0，取值范围为0至2^31–1，表示slave至少落后master的复制时间 
											  来自master的事件不直接执行，而是至少等到master执行完该该事件MASTER_DELAY所指定的时间间隔后才执行
  | MASTER_HEARTBEAT_PERIOD = interval		# 设置复制心跳的周期，取值范围为0 到 4294967秒。精确度可以达到毫秒，最小的非0值是0.001秒。
											  心跳信息由master在主机binlog日志文件在设定的间隔时间内没有收到新的事件时发出，以便slave知道master是否正常
  | MASTER_LOG_FILE = 'master_log_name'		# bin log 文件名称 通过 show master status 来查看
  | MASTER_LOG_POS = master_log_pos			# bin log 偏移量
  | MASTER_AUTO_POSITION = {0|1}			# 该参数在mysql5.6.5版本引入，如果进行change master to时使用MASTER_AUTO_POSITION = 1，slave连接master将使用基于GTID的复制协议。
    										  使用基于GTID协议的复制，slave会告诉master它已经接收到或执行了哪些事务。
											  计算这个集，slave需要读取全局参数gtid_executed以及通过show slave status获取的参数Retrieved_gtid_set。
    										  结果集作为初次握手的一部分，发送到master，master发回它已经执行的且不在结果集这部分的所有事务。
											  如果这些事务在master的binlog文件中已经被清除，master将会发送一个ER_MASTER_HAS_PURGED_REQUIRED_GTIDS错误信息到slave，复制将不会开启。
    										  使用基于GTID的复制时（MASTER_AUTO_POSITION = 1），首先要开启gtid_mode（在my.cnf中设置gtid-mode = ON），
											  MASTER_LOG_FILE 与 MASTER_LOG_POS也不能使用，否则会报错。 使用GTID后想要恢复到老的基于文件的复制协议，
										      在change master to时需要指定MASTER_AUTO_POSITION = 0以及MASTER_LOG_FILE 或 MASTER_LOG_POSITION中至少一项。
  | RELAY_LOG_FILE = 'relay_log_name'		# hange master to操作删除所有relay log文件并创建一个新的，除非我们指定RELAY_LOG_FILE 或 RELAY_LOG_POS。
  | RELAY_LOG_POS = relay_log_pos			  如果全局变量relay_log_purge设置为0（默认为ON），relay log也将会保持。mysql5.6.2之前，RELAY_LOG_FILE需要配置绝对路径，
											  mysql5.6.2及其后续版本，可以配置相对路径（相对mysql的data目录）。
  | MASTER_SSL = {0|1}						# SSL 相关
  | MASTER_SSL_CA = 'ca_file_name'			
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_CRL = 'crl_file_name'
  | MASTER_SSL_CRLPATH = 'crl_directory_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1} # 验证SSL
  | IGNORE_SERVER_IDS = (server_id_list) # 后面接以逗号分隔的0个或多个server-id，主要用于多主复制或环形复制的情况，处于复制链条中间的服务器异常

channel_option:
    FOR CHANNEL channel # for channel 允许指定同步应用于哪个复制区域通道。
        				# 提供FOR CHANNEL通道子句将CHANGE MASTER TO语句应用到特定的复制区域通道
						# 这样可以添加多个 同步来源实现更多的同步效果

server_id_list:
    [server_id [, server_id] ... ]

Slave 配置需要同步的表

–replication-do-db 设定需要复制的数据库（多数据库使用逗号，隔开）
–replication-ignore-db 设定需要忽略的复制数据库（多数据库使用逗号，隔开）
–replication-do-table 设定需要复制的表
–replication-ignore-table 设定需要忽略的复制表
–replication-wild-do-table 同replication-do-table功能一样，但是可以通配符
–replication-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

搭建

一主多从

使用docker compose 进行快速搭建

目录结构

m1 master mysql数据库配置和数据目录
s1 slave1 mysql数据库配置和数据目录
s2 slave2 mysql数据库配置和数据目录
s3 slave3 mysql数据库配置和数据目录
env mysql 环境变量配置

MYSQL_ROOT_PASSWORD=root # mysql root 密码

docker compose 配置文件

version: '3'

services:
  mysql-master:
    container_name: mysql-master
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./m1/lib/mysql:/var/lib/mysql
      - ./m1/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "23300:3306"
  mysql-slave-1:
    container_name: mysql-slave-1
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./s1/lib/mysql:/var/lib/mysql
      - ./s1/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "23301:3306"
  mysql-slave-2:
    container_name: mysql-slave-2
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./s2/lib/mysql:/var/lib/mysql
      - ./s2/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "23302:3306"
  mysql-slave-3:
    container_name: mysql-slave-3
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./s3/lib/mysql:/var/lib/mysql
      - ./s3/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "23303:3306"

networks:
  default:
    external:
      name: mysql-cluster

修改配置 server_id

-- master
set global server_id = 1
-- slave 1
set global server_id = 2
-- slave 2
set global server_id = 3
-- slave 3
set global server_id = 4

表结构信息

-- 创建数据库
create database test character set utf8mb4;
-- 使用数据库
use test;
-- 用户表
create table if not exists test.test_table
(
     id varchar(64) not null primary key comment 'ID',
     product_no varchar(32) not null comment '商品编号',
     category varchar(64) not null comment '类别',
     types varchar(64) not null comment '类型',
     module varchar(255) not null comment '所属模块',
     `explain` varchar(255) not null comment '说明',
     `detail` varchar(255) not null comment '详情',
     replacement_cycle int not null comment '更换单位 0-实时 1 天 2 月 3 一次性',
     replacement_number int not null comment '更新数值',
     price int not null comment '价格 分',
     price_time int not null comment '购买有效期 0 年 1 月 2 次',
     icon varchar(255) not null comment '图片',
     user_types int not null comment '用户类型',
     create_time datetime not null comment '创建时间'
) comment '用户表';

🔥master 配置

-- 创建用户
create user slave_user@'%' identified by 'slave_user';
-- 授权
grant replication slave on *.* to slave_user@'%';

🔥所有slave 配置

-- 设置过滤的表
change replication filter
    replicate_do_db=(test),
    replicate_do_table=(test.test_table);
-- 配置从库
change master to
    master_host = 'mysql-master',
    master_port = 3306,
    master_user = 'slave_user',
    master_password = 'slave_user';

start slave ;

🔥中途添加slave 数据库进来

可通过mysqldump 把master数据dump下来复制到从库中，在进行同步全量同步
只进行同步后面的操作

查看master binlog 位置

show master status;

配置slave master_log_pos

change master to
    master_host = '192.168.0.148',
    master_port = 23300,
    master_user = 'root',
    master_log_pos =49484,
    master_password = 'root';
start slave ;

通过命令不同从指定pos 开始同步

此方式问题比较多，必须保证 master_log_pos之后的位置是数据库中不存在的
例如：
我从0 开始（注意，不要从0 开始，要看实际情况）show binlog events;（主库执行）

例如：我从126 开始同步，后续执行了create database命令那么就要确保从库没有这个数据库

双主同步

mysql主从方案,有一主多从，读写分离等功能，但是单主存在单点故障，从库切换成主库需要作改动等缺点。
因此，如果是双主或者多主，就会增加mysql入口，增加高可用。
不过多主需要考虑自增长ID问题，这个需要特别设置配置文件，比如双主，可以使用奇偶，总之，主之间设置自增长ID相互不冲突就能完美解决自增长ID冲突问题。
MySQL双主（主主）架构方案思路是:

两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；
masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；
两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务）；
所有提供服务的从服务器与masterB进行主从同步（双主多从）;
建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）；

这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上（尽可能减少主库宕机对业务造成的影响），减少了主从同步给线上主库带来的压力；

但是也有几个不足的地方:

masterB可能会一直处于空闲状态（可以用它当从库，负责部分查询）；
主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据，这样可能会造成一定程度的同步延时；

关键词：VIP (虚拟IP visual ip)

目录结构

m1 master1 mysql数据库配置和数据目录
m2 master2 mysql数据库配置和数据目录

docker-compose

version: '3'

services:
  mysql-master-1:
    container_name: mysql-master-1
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./m1/lib/mysql:/var/lib/mysql
      - ./m1/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "33300:3306"
  mysql-master-2:
    container_name: mysql-master-2
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./m2/lib/mysql:/var/lib/mysql
      - ./m2/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "33301:3306"
networks:
  default:
    external:
      name: mysql-cluster

数据库结构

-- 创建数据库
create database test character set utf8mb4;
-- 使用数据库
use test;
-- 用户表
create table if not exists test.test_table
(
  id varchar(64) not null primary key comment 'ID',
  product_no varchar(32) not null comment '商品编号',
  category varchar(64) not null comment '类别',
  types varchar(64) not null comment '类型',
  module varchar(255) not null comment '所属模块',
  `explain` varchar(255) not null comment '说明',
  `detail` varchar(255) not null comment '详情',
  replacement_cycle int not null comment '更换单位 0-实时 1 天 2 月 3 一次性',
  replacement_number int not null comment '更新数值',
  price int not null comment '价格 分',
  price_time int not null comment '购买有效期 0 年 1 月 2 次',
  icon varchar(255) not null comment '图片',
  user_types int not null comment '用户类型',
  create_time datetime not null comment '创建时间'
) comment '用户表';

🔥###查看两个master 之间的 log Position

master 1

master 2

🔥master 1 配置

set global server_id = 1;
-- 过滤同步数据
change replication filter
    replicate_do_db=(test),
    replicate_do_table=(test.test_table);
-- 同步master 2
change master to
    master_host='mysql-master-2',
    master_port=3306,
    master_user='root',
    master_password='root',
    master_log_file='binlog.000002',
    master_log_pos=1510;
-- 启动
start slave;

🔥master 2 配置

set global server_id = 2;
-- 过滤同步数据
change replication filter
    replicate_do_db=(test),
    replicate_do_table=(test.test_table);
-- 同步master 2
change master to
    master_host='mysql-master-1',
    master_port=3306,
    master_user='root',
    master_password='root',
    master_log_file='binlog.000002',
    master_log_pos=1510;
-- 启动
start slave;

多台MYSQL同步

目录结构

m1 master1 mysql数据库配置和数据目录
m2 master2 mysql数据库配置和数据目录
m3 master3 mysql数据库配置和数据目录

version: '3'

services:
  mysql-master-1:
    container_name: mysql-master-1
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./m1/lib/mysql:/var/lib/mysql
      - ./m1/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "33300:3306"
  mysql-master-2:
    container_name: mysql-master-2
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./m2/lib/mysql:/var/lib/mysql
      - ./m2/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "33301:3306"
  mysql-master-3:
    container_name: mysql-master-3
    image: mysql:8.0.31
    env_file:
      - ./env/mysql.env
    volumes:
      - ./m2/lib/mysql:/var/lib/mysql
      - ./m2/etc/mysql/conf.d:/etc/mysql/conf.d
    ports:
      - "33302:3306"
networks:
  default:
    external:
      name: mysql-cluster

数据库结构

-- 创建数据库
create database test character set utf8mb4;
-- 使用数据库
use test;
-- 用户表
create table if not exists test.test_table
(
  id varchar(64) not null primary key comment 'ID',
  product_no varchar(32) not null comment '商品编号',
  category varchar(64) not null comment '类别',
  types varchar(64) not null comment '类型',
  module varchar(255) not null comment '所属模块',
  `explain` varchar(255) not null comment '说明',
  `detail` varchar(255) not null comment '详情',
  replacement_cycle int not null comment '更换单位 0-实时 1 天 2 月 3 一次性',
  replacement_number int not null comment '更新数值',
  price int not null comment '价格 分',
  price_time int not null comment '购买有效期 0 年 1 月 2 次',
  icon varchar(255) not null comment '图片',
  user_types int not null comment '用户类型',
  create_time datetime not null comment '创建时间'
) comment '用户表';

修改多台MYSQL server_id

# master 1
set global server_id = 1;
# master 2
set global server_id = 2;
# master 3
set global server_id = 3;

链式同步架构

binlog position

这里三台一样，每次调整前一定要看一下

master 1 配置

-- 设置过滤的表
change replication filter
    replicate_do_db=(test),
    replicate_do_table=(test.test_table);
-- 配置从库
change master to
    master_host = 'mysql-master-3',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=1510;
start slave ;

master 2 配置

-- 设置过滤的表
change replication filter
    replicate_do_db=(test),
    replicate_do_table=(test.test_table);
-- 配置从库
change master to
    master_host = 'mysql-master-1',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=1510;
start slave ;

master 3 配置

-- 设置过滤的表
change replication filter
    replicate_do_db=(test),
    replicate_do_table=(test.test_table);
-- 配置从库
change master to
    master_host = 'mysql-master-2',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=1510;
start slave ;

写入测试

select * from test_table;
-- master 1
insert into test.`test_table` (`id`, `product_no`, `category`, `types`, `explain`, `detail`, `replacement_cycle`, `module`, `replacement_number`, `price`, `icon`, `user_types`, `price_time`, `create_time`) 
values ('10001', '10001', '微软/Microsoft', 'Test', '安阳', 'n1', 1, 'neGL', 1, 1, 'www.liliana-hills.name', 1, 1, '2022-01-08 12:24:38');

-- master 2
insert into test.`test_table` (`id`, `product_no`, `category`, `types`, `explain`, `detail`, `replacement_cycle`, `module`, `replacement_number`, `price`, `icon`, `user_types`, `price_time`, `create_time`) 
values ('10002', '10001', '微软/Microsoft', 'Test', '安阳', 'n1', 1, 'neGL', 1, 1, 'www.liliana-hills.name', 1, 1, '2022-01-08 12:24:38');

-- master 3
insert into test.`test_table` (`id`, `product_no`, `category`, `types`, `explain`, `detail`, `replacement_cycle`, `module`, `replacement_number`, `price`, `icon`, `user_types`, `price_time`, `create_time`) 
values ('10003', '10001', '微软/Microsoft', 'Test', '安阳', 'n1', 1, 'neGL', 1, 1, 'www.liliana-hills.name', 1, 1, '2022-01-08 12:24:38');

星形同步架构

binlog Postition
matser 1

master 2

master 3

master 1 同步配置

stop slave for channel 'receive-mysql-master-3';
reset slave for channel 'receive-mysql-master-3';
change master to
    master_host = 'mysql-master-3',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=2779 for channel 'receive-mysql-master-3';
start slave for channel 'receive-mysql-master-3';
show slave status for channel 'receive-mysql-master-3';

stop slave for channel 'receive-mysql-master-2';
reset slave for channel 'receive-mysql-master-2';
change master to
    master_host = 'mysql-master-2',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=2779 for channel 'receive-mysql-master-2';
start slave for channel 'receive-mysql-master-2';
show slave status for channel 'receive-mysql-master-2';

show slave status ;

master 2 配置

stop slave for channel 'receive-mysql-master-3';
reset slave for channel 'receive-mysql-master-3';
change master to
    master_host = 'mysql-master-3',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=2779 for channel 'receive-mysql-master-3';
start slave for channel 'receive-mysql-master-3';
show slave status for channel 'receive-mysql-master-3';

stop slave for channel 'receive-mysql-master-1';
reset slave for channel 'receive-mysql-master-1';
change master to
    master_host = 'mysql-master-1',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=2779 for channel 'receive-mysql-master-1';
start slave for channel 'receive-mysql-master-1';
show slave status for channel 'receive-mysql-master-1';

show slave status ;

master3 配置

stop slave for channel 'receive-mysql-master-2';
reset slave for channel 'receive-mysql-master-2';
change master to
    master_host = 'mysql-master-2',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=2779 for channel 'receive-mysql-master-2';
start slave for channel 'receive-mysql-master-2';
show slave status for channel 'receive-mysql-master-2';

stop slave for channel 'receive-mysql-master-1';
reset slave for channel 'receive-mysql-master-1';
change master to
    master_host = 'mysql-master-1',
    master_port = 3306,
    master_user = 'root',
    master_password = 'root',
    master_log_file ='binlog.000002',
    master_log_pos=2779 for channel 'receive-mysql-master-1';
start slave for channel 'receive-mysql-master-1';
show slave status for channel 'receive-mysql-master-1';

select * from test_table;
-- master 1
insert into test.`test_table` (`id`, `product_no`, `category`, `types`, `explain`, `detail`, `replacement_cycle`, `module`, `replacement_number`, `price`, `icon`, `user_types`, `price_time`, `create_time`) 
values ('10004', '10001', '微软/Microsoft', 'Test', '安阳', 'n1', 1, 'neGL', 1, 1, 'www.liliana-hills.name', 1, 1, '2022-01-08 12:24:38');

-- master 2
insert into test.`test_table` (`id`, `product_no`, `category`, `types`, `explain`, `detail`, `replacement_cycle`, `module`, `replacement_number`, `price`, `icon`, `user_types`, `price_time`, `create_time`) 
values ('10005', '10001', '微软/Microsoft', 'Test', '安阳', 'n1', 1, 'neGL', 1, 1, 'www.liliana-hills.name', 1, 1, '2022-01-08 12:24:38');

-- master 3
insert into test.`test_table` (`id`, `product_no`, `category`, `types`, `explain`, `detail`, `replacement_cycle`, `module`, `replacement_number`, `price`, `icon`, `user_types`, `price_time`, `create_time`) 
values ('10006', '10001', '微软/Microsoft', 'Test', '安阳', 'n1', 1, 'neGL', 1, 1, 'www.liliana-hills.name', 1, 1, '2022-01-08 12:24:38');

数据不一致/错误

在同步过程中难免会出现数据不一致情况

🔥删除时master 存在 slave 不存在

select * from performance_schema.replication_applier_status_by_worker

Last_SQL_Error: Could not execute Delete_rows event on table xxx ，Can't find record in xxx

原因：在master上删除一条记录，而slave上找不到。
解决方法：由于master要删除一条记录，而slave上找不到故报错，这种情况主上都将其删除了，那么从机可以直接跳过。

stop slave;
set global sql_slave_skip_counter=1;
start slave;
-- 如上命令若报错：ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with 
-- @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction或者可以换用如下命令：
STOP SLAVE;
SET @@SESSION.GTID_NEXT= 'f396f867-d755-11xxx85-005xxxxxb5a:264261655'    --在session里设置gtid_next，即跳过这个GTID
BEGIN; COMMIT;       --设置空事物
SET SESSION GTID_NEXT = AUTOMATIC;     -- 恢复GTID
START SLAVE;xxxx

🔥在slave已经有该记录，又在master上插入了同一条记录

解决方法：在从库上删除该记录，或者跳过该记录。然后在master上和slave上再分别确认一下

🔥在master上更新一条记录，而slave上找不到，丢失了数据

参考方法：在master上，用mysqlbinlog 分析下出错的binlog日志在干什么。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | grep -A '10' 794

#120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F
### UPDATE hcy.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='bbc' /* STRING(4) meta=65028 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='BTV' /* STRING(4) meta=65028 nullable=1 is_null=0 */
# at 794
#120302 12:08:36 server id 22 end_log_pos 821 Xid = 60
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

在slave上，查找下更新后的那条记录，应该是不存在的。

mysql> select * from t1 where id=2;
Empty set (0.00 sec)

然后再到master查看

mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
| 2 | BTV |
+----+------+
1 row in set (0.00 sec)

把丢失的数据在slave上填补，然后跳过报错即可。

mysql> insert into t1 values (2,'BTV');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
| 2 | BTV |
+----+------+
1 row in set (0.00 sec)
 
mysql> stop slave ;set global sql_slave_skip_counter=1;start slave;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

🔥log event entry exceeded max_allowed_packet

需要确保主备配置一样，然后尝试调大该参数的值。

stop slave;
set global max_allowed_packet =1024*1024*1024;
start slave

另外，5.6 版本中的 slave_max_allowed_packet_size 参数控制slave 可以接收的最大的packet 大小，该值通常大于而且可以覆盖 max_allowed_packet 的配置, 进而减少由于上面的问题导致主从复制中断。
查看本库设置

mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1073741824 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

🔥Slave failed to initialize relay log info structure from the repository

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决办法：查看日志，

可以看到报错，原来是找不到./server246-relay-bin.index文件，找到原因所在了，由于我使用的是冷备份文件恢复的实例，在mysql库中的slave_relay_log_info表中依然保留之前relay_log的信息，所以导致启动slave报错。
mysql提供了工具用来删除记录：slave reset；
slave reset执行候做了这样几件事：
1、删除slave_master_info ，slave_relay_log_info两个表中数据；
2、删除所有relay log文件，并重新创建新的relay log文件；
3、不会改变gtid_executed 或者 gtid_purged的值

注意

创建数据库表结果时，可以添加上 if not exists 同步时更方便，如果从库有这个表就不用同步了

参考

https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html

Rick.c.137

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL 配置主从同步

可以看到报错，原来是找不到./server246-relay-bin.index文件，找到原因所在了，由于我使用的是冷备份文件恢复的实例，在mysql库中的slave_relay_log_info表中依然保留之前relay_log的信息，所以导致启动slave报错。在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
复制链接

扫一扫