MySQL 配置主从同步

image.png

MySQL主从复制的流程

image.png

  1. 主库db的更新事件(update、insert、delete)被写到binlog
  2. 主库创建一个binlog dump thread,把binlog的内容发送到从库
  3. 从库启动并发起连接,连接到主库
  4. 从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
  5. 从库启动之后,创建一个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,取值范围为02^311,表示slave至少落后master的复制时间 
											  来自master的事件不直接执行,而是至少等到master执行完该该事件MASTER_DELAY所指定的时间间隔后才执行
  | MASTER_HEARTBEAT_PERIOD = interval		# 设置复制心跳的周期,取值范围为04294967秒。精确度可以达到毫秒,最小的非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 环境变量配置

image.png

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;

image.png
配置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;(主库执行)
image.png
例如:我从126 开始同步,后续执行了create database命令 那么就要确保从库没有这个数据库

双主同步

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

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

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

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

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

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

目录结构

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

image.png
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

image.png

  • master 2

image.png

🔥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数据库配置和数据目录

image.png

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

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

image.png
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
image.png
master 2
image.png
master 3
image.png
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 statusG;
……
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
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>