DBA数据库笔记之(四)MySQL主从复制

作者: Mr培 | 来源:发表于2024-01-18 12:42 被阅读0次

主从复制的原理

主从复制的作用

数据备份和恢复
高可用
负载均衡

主从复制的原理

主从复制的原理.png

两种复制方法

基于位点的复制

show master status\G;

基于GTID的复制

主从复制的搭建

基于位点的主从复制部署流程

确定主库Binlog是否开启
修改主从server_id
主库导出数据
从库导入数据
确定主库备份时的位点
在从库配置主库信息
查看复制状态并测试数据是否同步

实战

确定主库Binlog是否开启

show global variables like "log_bin";
# 查看配置文件Binlog是否开启
vim /data/mysql/conf/my.cnf
# 查看是否有配饰 log-bin=

修改主server_id

# 查看主库的server_id
select @@global.server_id;
# 动态修改主库的server_id,范围 1~2^32-1
set global server_id=12161;
# 修改配置文件
vim /data/mysql/conf/my.cnf
server-id = 12161

修改从server_id

# 查看主库的server_id
select @@global.server_id;
# 动态修改主库的server_id
set global server_id=12162;
# 修改配置文件
vim /data/mysql/conf/my.cnf
server-id = 12162

关闭主和从的GTID

# 修改配置文件
vim /data/mysql/conf/my.cnf
gtid_mode=off
# 注释掉
# enforce_gtid_consistency=on

重启主从的MySQL

/etc/init.d/mysql.server restart

主库创建复制用户

create user 'repl'@'%' identifiled with mysql_native_password by '123456';
grant replication slave on *.* to 'repl'@'%';

主库导出数据

# --single-transaction 开启一个事务进行备份，可重复读，不堵塞应用保证数据一致性；--all-databases 备份所有库；--master-data=2 记录备份时的位置 =2 以注释的方式记录；--set-gtid-purged=off 不增加gtid的信息
mysqldump -uroot -p --single-transaction --all-databases --master-data=2 --set-gtid-purged=off > alldb_bak.sql
# 将备份传到从库
scp alldb_bak.sql 192.168.12.162:/data/backup

从库导入数据

mysql -uroot -p <alldb_bak.sql

在从库配置主库信息

# 先确定备份时的位点
head -n 30 alldb_bak.sql
# 找到位点信息Binlog，MASTER_LOG_FILE='mysql-bin.000029',MASTER_LOG_POS=699;

# 查看是否已经存在主从复制关系
show slave status\G;
# 在从库建立主从复制的链接;master_host 主库的ip地址;master_user 主库的备份用户；master_log_file 主库在全备时的Binlog
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000029',master_log_pos=699;
start slave;

查看复制状态

show slave status\G;

测试数据是否同步
... ...

使用Xtrabackup创建主从复制

主库使用Xtrabackup进行全备

xtrabackup --dafults-file=/data/mysql/conf/my.cnf -uroot -p --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream

将全备传到从库

scp xtrabackup.xbstream 192.168.12.162:/data/backup/recover

清空从库

# 先停止从库
/etc/init.d/mysql.server stop
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf

把全备导入到新实例

cd /data/backup/recover
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./

启动MySQL

chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start
ps -ef | grep mysql

查看备份时的位点信息

cat xtrabackyp_binlog_info

创建复制关系

# 查看是否已经存在主从复制关系
show slave status\G;
# 在从库建立主从复制的链接;master_host 主库的ip地址;master_user 主库的备份用户；master_log_file 主库在全备时的Binlog
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000030',master_log_pos=196;
# 启动主从复制
start slave;

查看主从状态

show slave status\G;

测试同步
... ...

基于GTID的主从复制

GTID是什么

GTID的组成

# uuid+pid组成；pid 代表已经提交的事务数量
show global variables like "%gtid_purged%";
# 查看MySQL的uuid,MySQL的唯一标识
show global variables like "%server_uuid%";
# 查看uuid持久化路径
cat /data/mysql/data/auto.cnf
# 查看控制gtid开启和关闭的参数;gtid_mode=off_permissive/on_permissive/on/off
show global variables like "gtid_mode"；

gtid_executed

用来存储当前实例上已经执行的事务gtid集合

show global variables like "gtid_executed";
# 或者
select * from mysql.gtid_executed;

enforce_gtid_consistency

控制事务是不是允许违反gtid一致性；off所有事务都运行违反gtid一致性；on不允许；warn

set global enforce_gtid_consistency = on;

基于GTID复制的优势

可以知道事务在哪个实例上提交的
比较方便进行复制结构上的故障转移
很方便判断主从一致性

使用GTID的注意事项

事务和非事务引擎不能在同一个事务里
create table ... select
建议设置 --enforce-gtid-consistency

位点复制改成GTID复制

查看GTID状态

show slave status\G;
show global variables like "gtid_mode";
show global variables like "enforce_gtid_consistency";
# 恢复默认状态
set global enforce_gtid_consistency = off;

修改enforce_gtid_consistency=warn

# 主库从库执行,等待一段时间后业务没有问题再修改为on
set global enforce_gtid_consistency = warn;

修改enforce_gtid_consistency=on

# 主库从库执行
set global enforce_gtid_consistency = on;

修改gtid_mode=off_permisssive

# 主库从库
set global gtid_mode=off_permisssive;

修改gtid_mode=on_permissive;

# 主库从库
set global gtid_mode=on_permissive;

查看正在执行的匿名事务数量

# 0 没有正在执行的匿名事务数量就可以执行下一步
show status like 'ongoing_anonymous_transaction_count';

刷新日志

# 主库从库
flush logs;

开启GTID

# 主从库
set global gtid_mode=on;

参数持久化

# 主从库
vim /data/mysql/conf/my.cnf
gtid_mode=on
enforce_gtid_consistency=on

修改复制参数

# 从库执行
stop slave;
change master to master_auto_position=1;
start slave;

查看复制状态

show slave status\G;

测试
... ...

从0部署GTID复制

确定主库Binlog是否开启

show global variables like "log_bin";
# 或者查看配置文件
cat /data/mysql/conf/my.cnf
log-bin=/data/mysql/binlog/mysql-bin

主从修改server_id

# 主从server_id需要不一样
select @@global.server_id;
# 修改server_id
set global server_id=xxx;
# 且配置文件也要修改
vim /data/mysql/conf/my.cnf
server-id=xxx;

主从开启GTID

set global gtid_mode=off_permissive;
set global gtid_mode=on_permissive;
set global gtid_mode=on;
set global enforce_gtid_consistency=on;
# 修改配置文件
vim /data/mysql/conf/my.cnf
gtid_mode=on
enforce_gtid_consistency=on

创建复制用户

#主库
create user 'repl'@'%' identifiled with mysql_native_password by '123456';
grant replication slave on *.* to 'repl'@'%';

主库导入数据

mysqldump -uroot -p --single-transaction --all-databases --master-data=2 --set-gtid-purged=on > alldb.sql
# 将sql传输到从库
scp alldb.sql 192.168.12.162:/data/backup/

从库导入数据

# 先清空gtid集合
reset master;
mysql -uroot -p < alldb.sql

在从库配置主库信息

# 清空以前的主从关系
stop slave;
reset slave;
# 配置主从库信息
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_auto_position=1;
start slave;

查看复制状态

show slave status\G;

测试
... ...

多线程复制

MySQL5.6的并行复制

支持库级别的并行复制
配置方式

# 在从库执行
show global variables like "slave_parallel_sorkers";
# 开启库级别的多线程复制
set global slave_parallel_workers=4;
# 重启主从复制
stop slave;
start slave;
# 查看复制线程
show processlist；

5.6并行复制的不足

基于库

MySQL5.7的并行复制

5.7并行复制原理

基于组提交

配置方式

show global variables like "slave_parallel_type";
stop slave sql_thread;
set global slave_parallel_workers=8;
set global slave_parallel_type=logical_clock;
# 启动sql线程
start slave sql_thread;
flush logs;
show processlist；

MySQL5.7.22及之后版本的并行复制

MySQL5.7.22的并行复制介绍

# 新增一个参数，在主库配置，控制是否启动新策略；writeset;writeset_session;
show global variables like "binlog_transaction_dependency_tracking";

配置方式

# 在主库
set global binlog_transaction_dependency_tracking=writeset;
# 在从库
set global slave_parallel_workers=8;
stop slave sql_thread;
set global slave_parallel_type=logical_clock;
# 控制二进制日志提交再将二进制日志同步到磁盘之前等待的微秒数
set global binlog_group_commit_sync_delay=3000;
start slave sql_thread;
# 刷新日志
flush logs;
# 查看复制线程，查看配置前后变化
show processlist；

一主两从和级联的切换

部署一套基于位点的一主两从架构

部署三套MySQL

# 保证三台mysql的server_id不一致
vim /etc/mysql/conf/my.cnf

# 动态修改
set global server_id=12163;

修改配置文件

# 在三台mysql修改配置参数
vim /data/mysql/conf/my.cnf
#关闭gtid
gtid_mode=off
# enforce_gtid_consistency=on
# 开启一个参数
log-slave-updates=1
# 重启
/etc/init.d/mysql.server restart

创建一主两从架构

# 主库
# 创建复制用户
create user 'repl'@'%' identifiled with mysql_native_password by '123456';
# 导出主库数据
xtrabackup --dafults-file=/data/mysql/conf/my.cnf -uroot -p --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream
# 将备份传到两台从库
scp xtrabackup.xbstream 192.168.12.xx:/data/backup/recover

# 两台从库操作
# 确认磁盘空间是否足够
df -TH
# 停掉mysql
/etc/init.d/mysql.server stop
# 清空从库的数据和binlog目录
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf
# 解压备份
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
# 恢复完成后，修改属组
shown -R mysql.mysql /data/mysql
# 启动
/etc/init.d/mysql.server start
ps -ef | grep mysql
# 查看备份时的位点信息
cat xtrabackup_binlog_info
# 查看复制状态,为空
show slave status\G;

# 创建主从复制关系
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000050',master_log_pos=196;
start slave;
show slave status\G;

测试
... ...

基于位点的复制架构切换

一主两从改成级联

# 停掉两台同步复制
stop slave;

# 在一台从库上执行
show slave status\G;
# 记住两个参数
Relay_Master_Log_File:mysql-bin.000050 当前从库执行到的主库binlog
Exec_Master_Log_Pos:733 当前从库执行到的主库位点

# 查看当前从库的位点
show master status\G; # 当前从库的位点和主库的位点要一样 Position=Exec_Master_Log_Pos
# 启动当前复制
start slave;

# 在另一台从库执行
# 启动复制到特定位点,前一台从库的复制位点信息
start slave until master_log_file='mysql-bin.000050',master_log_pos=733;
# 查看复制状态，会自动关闭sql线程Slave_SQL_Running=No
show slave status\G;
# 停掉主从复制
stop slave;
# 修改复制链接到前一台从库，位点信息为前一台从库的位点信息
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_log_file='mysql-bin.000051',master_log_pos=742;
start slave;
show slave status\G;

级联改成一主两从

# 在第一台从库操作
stop slave;
# 查看自己的当前位点信息
show master status\G;
# 查看对应的主库binlog和位点
show slave status\G;

# 在第二台从库操作
# 对比和第一台从库的binlog和位点信息是否对应
show slave status\G;
# 如果和第一台是一致的，则停止
stop slave;

# 在第一台从库操作
start slave;

# 在第二台从库操作
# 重新配置复制信息;master_host 填主库ip；master_log_file和master_log_pos填写第一台从库上对应的主库位点信息
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000050',master_log_pos=1073;
start slave;

基于GTID的复制架构切换

基于位点的复制改成基于GTID的复制

# 在三台mysql操作
# 设置warn后，查看是否有违反gtid一致性的sql语句，好即时调整
set global enforce_gtid_consistency=warn;
# 如果一段时间没有问题，则设置为on
set global enforce_gtid_consistency=on;
set global gtid_mode=off_permissive;
set global gtid_mode=on_permissive;
# 查看正在执行的匿名事务数量
show status like 'ongoing_anonymous_transaction_count';
flush logs;
set global gtid_mode=on;
# 修改配置文件，持久化配置
vim /data/mysql/conf/my.cnf
gtid_mode=on
enforce_gtid_consistency=on

# 在每台从库
stop slave;
change master to master_auto_position=1;
start slave;
# 查看复制状态
show slave status\G;

GTID复制一主两从改级联

# 在第二台从库执行
stop slave;
# master_host为第一台从库的ip
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_auto_position=1;
start slave;

GTID级联改一主两从

# 在第二台从库执行
stop slave;
# master_host为主库ip
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_auto_position=1;
start slave;
show slave status\G;

建议生产环境用GTID复制

忽略某张表的复制或者只复制某张表

只复制某一个库

配置方法

# 在从库
# 停止SQL线程
stop slave sql_thread;
# 复制多个库replicate_do_db=(db1,db2)
change replication filter replicate_do_db=(db1);
start slave sql_thread;

# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate_do_db=db1
# 查看状态
show slave status\G;

取消复制过滤

stop slave sql_thread;
change replication filter replicate_do_db=();
start slave sql_thread;

忽略某个库的复制

配置方法

# 在从库
# 停止SQL线程
stop slave sql_thread;
change replication filter replicate_ignore_db=(db1);
start slave sql_thread;

# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-ignore-db=db1

# 查看复制状态
show slave status\G;

取消复制过滤

stop slave sql_thread;
change replication filter replicate_ignore_db=();
start slave sql_thread;

只复制指定的表

配置方法

stop slave sql_thread;
change replication filter replicate_do_table=(db1.table01);
start slave sql_thread;

# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-do-table=db1.table01

取消复制过滤

stop slave sql_thread;
change replication filter replicate_do_table=();
start slave sql_thread;

忽略复制指定的表

配置方法

stop slave sql_thread;
change replication filter replicate_ignore_table=(db1.log01);
start slave sql_thread;

# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-ignore-table=db1.table01

取消复制过滤

stop slave sql_thread;
change replication filter replicate_ignore_table=();
start slave sql_thread;

指定同步某些表

配置方法

stop slave sql_thread;
# 只复制db1里面以table开头的表
change replication filter replicate_wild_do_table=('db1.table%');
start slave sql_thread;

# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-wild-do-table=db1.table%

取消复制过滤

stop slave sql_thread;
change replication filter replicate_wild_do_table=();
start slave sql_thread;

忽略同步某些表

配置方法

stop slave sql_thread;
change replication filter replicate_wild_ignore_table=('db1.table%');
start slave sql_thread;

取消复制过滤

stop slave sql_thread;
change replication filter replicate_wild_ignore_table=();
start slave sql_thread;

复制延迟如何处理？

复制延迟的原因

主库增删改并发大

# 模拟主库并发写入导入从库延迟写入
# 主库，使用压测工具
# 创建压测库
create database sysbench_ab;
# --mysql-db=sysbench_db压测的数据库；--threads=4多少个线程；--table_size=500000 每张表写入50万行数据；--tables=4创建压测表的个数
sysbench --db-driver=mysql --mysql-host=192.168.12.161 --mysql-port=3306 --mysql-user='repl_rw' --mysql-password='123456' --mysql-db=sysbench_db --threads=4 --table_size=500000 --tables=4 --time=100 oltp_write_only prepare

# 从库查看是否有延迟
show slave status\G;

大表在做DDL

# 在测试库里操作
use sysbench_ab;
show tables;
# after c; mysql8.0.12开始InnoDB原生支持快速加列，只能在表的最后加列
alter table sbtest1 add column d char(10) after c;

# 执行过程中查看从库情况 Seconds_Behind_Master:xx
show slave status\G;

从库备份导致延迟
大事务

# 在压力测试表中
insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;

# 等待主库执行完后在从库查看延迟状态
show slave status\G;

从库机器配置差

怎么判断延迟

Seconds_Behind_Master

表示从库延迟的秒数；0 可能没有延迟，不准确可能网络延迟

对比位点

Master_Log_File;Relay_Master_Log_File是否相等。Read_Master_Log_Pos；Exec_Master_Log_Pos是否相等。

对比GTID

Retrievec_Gtid_Set;Executed_Gtid_Set最后的数据是否一致（最后一个事务的id）

让ChatGPT帮忙写一个判断数据库延迟的脚本

帮忙写一个MySQL复制延迟的判断脚本
开启GTID的，可以判断show slave status结果中的 Retrieved_Gtid_Set 减去 Executed_Gtid_Set的值来计算从库落后的事务数
并每秒输出一次上面计算出来的落后事务数，不要通过查询表的方式计算落后事务数
比如
Retrieved_Gtid_Set: ec992f67-a08b-1ed-9a9f-024255bd70b6:4429853-4431720
Executed Gtid Set ec992f67-a08b-11ed-9a9f-02425567066:1-4431655
就需要计算4431720-4431655
并提供脚本里MySQL用户的创建命令，用户名用delay_check，密码用Yd_asdfa
需要的是Shell脚本

测试，重新创建测试库，压测，查看延迟信息

主从延迟处理方法

开启多线程
调整一些MySQL参数

 show global variables like "innodb_flush_log_at_trx_commit";
 # sysc_binlog=0 二进制日志从不同步到磁盘；1 二进制日志每个事务都会刷盘；100 每100个事务提交一次
 show global variables like "sysc_binlog";

调整从库机器配置
避免大事务
使用PT工具执行耗时长的DDL
调整架构

复制常见问题(报错)处理办法

server_id重复
端口不通
从库磁盘空间满了
主库要新增的内容在从库已经有了

# 比如主库创建从库已经存在表

# 在从库
stop slave;
show slave status\G;
set @session.gtid_next='Executed_Gtid_Set后面的gtid信息';
begin;commit;
start slave;
show slave status\G;

# 如果是基于位点的复制
stop slave;
set global sql_slave_skip_count=1;
start slave;
show slave status\G;

主库要更新的记录从库没有

# 模拟，主库新增两条记录，从库删除一条，主库更新从库删除的那条数据

# 从库 报错
show slave status\G;
cd /data/mysql/binlog/
# 解析报错的日志
mysqlbinlog mysql-relay-bin.000017 --start-position=1129 --base64-output=decode-rows -v > /tmp/1.sql
cat 1.sql
# 在从库补全丢失的记录
... ...
stop slave;start slave;
show slave status\G;

找不到主库的binlog位点

# 1. 可以将报错的从库转移到正常的从库补全复制，主从改级联
# 2. 重建复制（删除从库数据，重新简历从库复制）

三种日志格式对复制的影响

# 修改binlog日志格式
set global binlog_format=row

开始支持复制时的statement格式

mysql5.1.5之前支持，这种格式下binlog日志相对较少，但是涉及到跨库更新或者使用结果不确定的函数时，如使用uuid则容易出现主从数据不一致的情况

开始支持Row格式复制

主从一致性得到了保障，binlog日志量相对statement较多;生产环境强烈建议使用row格式

新增mixed格式

mysql5.1.8开始支持。statement和row的混合方式。

异步复制

传统的MySQL复制是异步复制

原理

异步复制原理.png
、

半同步复制

mysql5.5开始支持

原理

半同步复制.png
配置方法

# 主库 在MySQL中安装插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
# 查看
select * from mysql.plugin;
# 开启半同步复制插件
set global rpl_semi_sync_master_enabled=1;

# 从库 安装半同步复制插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
# 开启半同步复制插件
set global rpl_semi_sync_master_enabled=1;

# 主库从库 修改配置文件，新增配置
vim /data/mysql/conf/my.cnf
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so";
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

# 重启MySQL,如果有业务在使用，需要临时开启，可以不重启MySQL，但要保证配置文件修改正确
/etc/init.d/mysql.server restart

# 如果需要在线修改，还需要重启io线程
stop slave io_thread;
start slave io_thread;

# 查看半同步执行状态，主库
show global status like "%semi%";

增强半同步复制

MySQL5.7开始支持

原理

增强半同步复制.png
参数

# rpl_semi_sync_master_wait_point 默认AFTER_SYNC 半同步复制策略；after_commit 增强半同步复制
show global variables like "rpl_semi_sync_master_wait_point";

组复制

MySQL5.7推出

原理

组复制.png

复制相关的日志参数

配置双主相关环境

双主 A是B的从库，B是A的从库

# 使用之前的主从复制环境

# 在从库
show slave status\G;

# 在主库
show slave status\G;
stop slave;
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_auto_position=1;
start slave;
show slave status\G;

# 双写
# 在主库
set global auto_increment_increment=2;
set global auto_increment_offset=1;
# 在从库
set global auto_increment_increment=2;
set global auto_increment_offset=2;
# 双写，设置上列参数可以避免两库在同时写入数据主键冲突的情况；
# 生产环境不建议使用双写

复制过滤参数
| 参数 | 作用 |
| :----:|:----:|
| replicate_do_db | 只同步某个库 |
| replicate_ignore_db | 忽略同步某个库 |
| replicate_do_table | 只同步某些表 |
| replicate_ignore_table | 忽略同步某些表 |
| replicate_wild_do_table | 指定同步某些表，支持正则 |
| replicate_wild_ignore_table | 忽略同步某一些表，支持正则 |
| binlog_do_db | 只记录指定数据库的Binlog |
| binlog_ignore_db | 不记录指定数据库的Binlog |
多线程复制相关配置
| 参数 | 作用 |
| :----:|:----:|
| slave_parallel_workers | 控制复制的线程数 |
| slave_parallel_type | 控制并行复制的策略 |
| binlog_transaction_dependency_tracking | 5.7.22新增的控制并发策略的参数 |
| binlog_group_commit_sync_delay | 控制将二进制日志文件同步到磁盘之前二进制日志是交等待的微秒数 |
半同步复制相关配置
| 参数 | 作用 |
| :----:|:----:|
| rpl_semi_sync_master_enabled | 主库开启半同步复制 |
| rpl_semi_sync_slave_enabled | 从库开启半同步复制 |
| rpl_semi_sync_master_wait_point | 是否开启增强半同步复制 |

通过复制来恢复误删的库

在新实例恢复全备数据

# 前提，主从复制关系，主全备
... ... 
# 准备一新实例，将全备传输到新实例
scp xtrabackup.sbstream 192.168.12.164:/data/backup/recover
# 新实例
/etc/init.d/mysql.server stop
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf

#解压备份
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
# 恢复完成后，修改属组
shown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start

将新实例配置成原实例的从库

# 新实例
show slave status\G;
stop slave;
# 清空原实例的复制关系
reset slave;
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_auto_position=1;
# 一定不能先启动复制

确定回档时间点的位点

# 主库
cd /data/mysql/binlog/
# 找到误操作的binlog
cp mysql-bin.000069 /data/backup/
cd /etc/init.d/mysql.server stop
# 解析binlog,预估发送误操作的时间范围
mysqlbinlog mysql-bin.000069 --start-datetime='2023-07-31 23:30:00' --stop-datetime='2023-07-31 23:59:00‘ --base64-output=decode-rows -v > /data/backup/1.sql
# 找到误操作的binlog
cat 2023-07-31 23:30:00'

新实例同步到误操作前一个事务

# 在新实例
start slave io_thread;
start slave sql_thread until sql_before_gtids="3ebhb-ahsb3-3e3hb-000-12312eq:1243489";
show slave status\G;

确定误操作前的数据
清空复制关系

# 新实例
stop slave;
reset slave;

导出新实例中误删除的数据

mysqldump -uroot -p --set-gtid-purged=off -B recover > recover.sql
scp recover.sql 192.168.12.161:/data/backup/

在误操作的实例导入误删除的库

mysql -uroot -p < recover.sql

确定数据是否已经恢复
... ...

通过延迟从库恢复数据

配置延迟从库

# 主库全量备份
xtrabackup --dafults-file=/data/mysql/conf/my.cnf -uroot -p --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream
# 备份传到从库
scp xtrabackup.xbstream 192.168.12.161:/data/backup/

# 从库
/etc/init.d/mysql.server stop
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf
# 解压
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
# 移动备份目录
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start

# 查看是否存在主库备份过来的主从信息
show slave status\G;
stop slave;
reset slave;
# master_delay=3600 从库延迟的时间，单位秒
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_delay=3600,master_auto_position=1;
start slave;
show slave status\G;

测试
... ...

恢复操作

确定误操作的GTID

# 主库 进入到二进制日志文件夹
cd /data/mysql/binlog
# 找到误操作期间的binlog
cp mysql-bin.000070 /data/backup
cd /data/backup
mysqlbinlog mysql-bin.000070 --start-datetime='2023-07-31 23:30:00' --stop-datetime='2023-07-31 23:59:00‘ --base64-output=decode-rows -v > /data/backup/1.sql
cat 1.sql

配置同步到误操作前一个事务

# 主库
stop slave;
start slave sql_thread until sql_before_gtids='误操作的事务gtid值';
show slave status\G;

# 从库
stop slave;
# 停掉延迟复制
change master to master_delay=0;
start slave sql_thread until sql_before_gtids='误操作的事务gtid值';
start slave io_thread;
show slave status\G;

导出误操作的数据在主库恢复

mysqldump -u'root' -p --set-gtid-purged=off -B recover1 > recover.sql
scp recover.sql 192.168.12.161:/data/backup/

# 主库
mysql -uroot -p < recover.sql

通过ChatGPT编写复制创建脚本

编写一个自动创建主从复制的Shel脚本
复制是基于GTID的
脚本是在从库执行
主库的IP是192168.12.161
主库的备份用户是u_bak，密码是ljid71Gcd_a
需要在从库通过mysgldump远程备份主库的数据，再导入到从库
主库的复制用户是repl，密码是Uid_dQc63
从库的用户是root，密码是Auca@502846303

主从复制的原理.png

DBA数据库笔记之(四)MySQL主从复制

主从复制的原理

主从复制的作用

主从复制的原理

两种复制方法

主从复制的搭建

基于位点的主从复制部署流程

实战

使用Xtrabackup创建主从复制

基于GTID的主从复制

GTID是什么

基于GTID复制的优势

使用GTID的注意事项

位点复制改成GTID复制

从0部署GTID复制

多线程复制

MySQL5.6的并行复制

MySQL5.7的并行复制

MySQL5.7.22及之后版本的并行复制

一主两从和级联的切换

部署一套基于位点的一主两从架构

基于位点的复制架构切换

基于GTID的复制架构切换

忽略某张表的复制或者只复制某张表

只复制某一个库

忽略某个库的复制

只复制指定的表

忽略复制指定的表

指定同步某些表

忽略同步某些表

复制延迟如何处理？

怎么判断延迟

主从延迟处理方法

复制常见问题(报错)处理办法

三种日志格式对复制的影响

异步复制

半同步复制

增强半同步复制

组复制

复制相关的日志参数

配置双主相关环境

通过复制来恢复误删的库

通过延迟从库恢复数据

恢复操作

通过ChatGPT编写复制创建脚本

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

DBA数据库学习