1. 数据库备份恢复管理（进阶）
1.1 逻辑方式备份数据信息 mysqldump 参数应用
1.1.1 扩展参数 --master-data（source-data）
- 功能作用：
- 可以实现对数据表信息进行锁定
- 会在备份文件中生成备份数据结束的位置点信息 --master-data=1（默认--没有注释显示change master命令信息） --master-data=2（含有注释显示change master命令信息
CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000025', MASTER_LOG_POS=137469; --- 可以区分全量备份数据和增量数据 可以用于从库进行数据同步
1.1.2 扩展参数 --single-transaction
- 功能作用：
- 在进行备份时，会完成一次语句信息提交操作
- 可以实现生成数据快照信息，从而避免利用锁机制保证数据一致性，影响业务
1.1.3 扩展参数 --max-allowed-packet=128M
- 功能作用：
- 影响客户端的DML语句的执行操作
- 影响数据库服务备份恢复操作
PS：在备份时应用此参数信息，可以在恢复数据信息，恢复大表的大量数据信息
若不配置此参数，在早期5.6 5.5 数据库中，恢复大表数据时，会出现以下报错信息
ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (1024) - truncated
Packet for query is too large (20682943>1048576).
You can change this value on the server by setting the max_allowed_packet’ variable.
1.1.4 扩展参数 -R -E --triggers
| 参数选项 | 含义 | 技术相关 | 功能作用 |
|---|---|---|---|
| -R | 备份数据库存储过程信息 | （开发相关） | 开发代码 SQL语句 C客户端 - 传参信息 网络（带宽固定）-》 mysql server （脚本信息-注册脚本 登录脚本 -- 存储过程） |
| -E | 备份数据库事件信息 | （开发相关） | 数据库中可以实现自动执行语句的定时任务信息（银行卡利息） |
| --triggers | 备份数据库触发器信息 | （开发相关） | 当执行增 删 改时，会自动完成一些列操作 |
1.1.5 综合备份语句
- 以下mysqldump操作命令备份数据库所有数据，是最全最合理备份方式
mysqldump -uroot -p123456 -A --source-data --single-transaction --max-allowed-packet=64M -R -E --triggers >/backup/all.sql
1.2 物理方式备份数据信息（进阶-热备）
- 应用第三方软件：xtrabackup
1.2.1部署安装/应用xbk工具
- 步骤一：下载xbk软件程序包
MySQL 高于8.0.19版本 应用xbk软件版本 对应数据库版本 （MySQL 8.0.32 ）
MySQL 8.0.11~8.0.19 应用xbk软件版本 PXB 8.0
MySQL 低于8.0.11版本 应用xbk软件版本 PXB 2.4
- 步骤二：安装xbk软件程序包
yum localinstall -y percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm
-
步骤三：应用xbk软件进行备份恢复（物理备份/恢复）
-
全量备份
mkdir -p /backup/full -- 空目录 xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --port=3306 --user=root --password=123 --backup --targetdir=/backup/full #--defaults-file -- 加载需要备份数据的实例信息（datadir 加载数据目录信息） 2025-01-20T06:12:42.807871+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK! #以上备份操作成功，会看到命令执行后最后的输出信息为 OK #或者使用参数--datadir替换掉参数--defaults-file xtrabackup --datadir=/data/3306/data --host=10.0.0.51 --port=3306 --user=root --password=123 --backup --target-dir=/backup/fullPS：利用xbk工具只能实现本地数据库数据信息备份
备份数据特点：xbk可以将备份开始前的所有磁盘文件数据保存备份，也可以将备份期间增量数据做备份 -
全量恢复
-
步骤一：需要停止数据库服务
/etc/init.d/mysql.server stop -
步骤二：清理原有数据目录信息
cp -a /data/3306/data/ /backup/data-02 rm -rf /data/3306/data/* rm -rf /data/3306/log/* -
步骤三：恢复数据信息
xtrabackup --prepare --target-dir=/backup/full #将内存部分数据恢复 [Note] [MY-011825] [Xtrabackup] completed OK! xtrabackup --copy-back --target-dir=/backup/full #将磁盘文件部分恢复 [Note] [MY-011825] [Xtrabackup] completed OK! chown -R mysql.mysql /data/3306/data/ chown -R mysql.mysql /data/3306/log/ -
步骤四：启动恢复数据库服务/测试数据信息
/etc/init.d/mysql.server start
-
-
增量备份
-
创建备份数据的目录
mkdir -p /backup/full -- 周日做的全量备份数据保存目录 mkdir -p /backup/week-01 -- 周一做的增量备份数据保存目录 mkdir -p /backup/week-02 -- 周二做的增量备份数据保存目录 -
步骤二：实现数据备份操作
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --parallel=4 --target-dir=/backup/full #以上备份操作，表示第一次的全量备份 （参数--parallel=4 在进行备份数据时开启的线程数量 可以提高备份数据效率） #创建周一的增量数据 mysql> create database test10; Query OK, 1 row affected (0.03 sec) mysql> use test10; Database changed mysql> create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --parallel=4 --target-dir=/backup/week-01 --incremental-basedir=/backup/full #以上备份操作，表示第一次的增量备份 （--incremental-basedir 加载识别上一次备份数据目录信息） #创建周二的增量数据 mysql> create database test11; Query OK, 1 row affected (0.03 sec) mysql> use test11; Database changed mysql> create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --parallel=4 --target-dir=/backup/week-02 --incremental-basedir=/backup/week-01 #以上备份操作，表示第二次的增量备份 （--incremental-basedir 加载识别上一次备份数据目录信息）
-
-
增量恢复
-
步骤一：需要停止数据库服务
/etc/init.d/mysql.server stop -
步骤二：清理原有数据目录信息
cp -a /data/3306/data/ /backup/data-02 rm -rf /data/3306/data/* rm -rf /data/3306/log/* -
步骤三：恢复数据信息
xtrabackup --prepare --apply-log-only --target-dir=/backup/full #以上操作，表示加载全量数据目录中信息 （加载全量数据目录中内存数据信息 加载磁盘数据信息） xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/week-01 xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/week-02 #以上操作，表示加载全量数据目录中信息 （加载增量数据目录中内存数据信息 加载磁盘数据信息） xtrabackup --prepare --target-dir=/backup/full #将全量和增量整合后的内存信息进行恢复 xtrabackup --datadir=/data/3306/data --copy-back --target-dir=/data/backup/full 或者 xtrabackup --copy-back --target-dir=/backup/full #将全量和增量整合后的磁盘信息进行恢复 #--apply-log-only --表示不会在恢复数据过程中，触发回滚操作 chown -R mysql.mysql /data/3306/data/ chown -R mysql.mysql /data/3306/log/ -
步骤四：启动恢复数据库服务/测试数据信息
/etc/init.d/mysql.server start
-
-
2. 数据库服务主从同步
2.1 主从同步功能作用
- 可以解决高并发问题 （读负载均衡 写负载均衡）
- 可以保证数据安全性 （物理损坏 逻辑损坏-延迟从库）
- 可以实现业务高可用 （主数据库承载业务访问 当主库故障可以自动切换从库）
- 可以实现业务功能测试
- 可以实现数据备份
2.2 ==主从同步原理==
-
步骤一：创建多个（至少两个）数据库实例（主实例 从实例 -- 不在一个服务器上 51 主数据库实例 52 从数据库实例）
-
步骤二：在从数据库中进行连接主库的配置信息
change master to MASTER_HOST='10.0.0.51', -- 设置需要同步数据的主库地址信息 MASTER_PORT=3306, -- 设置需要同步数据的主库端口信息 MASTER_USER='repl', -- 连接登录主库用户信息 MASTER_PASSWORD='123456', -- 连接登录主库用户密码信息 MASTER_LOG_FILE='binlog.000003', -- 同步数据的位置点 （会根据备份文件信息设置 --source-data） MASTER_LOG_POS=681, MASTER_CONNECT_RETRY=10; -- 主从同步网络异常的重试次数以上配置内容也会保存到文件中 ==master_info文件==中
-
步骤三：启动主从同步功能
start slave; -- 加载master_info信息 -- 在从库创建两个线程信息（IO SQL--show processlist） -- 利用IO线程向主库发送连接请求 -
步骤四：当主库验证从库连接请求正确后，会在主库中生成dump线程
dump线程会做两件事： ① 负责和从库IO线程建立实时会话 ② 利用dump线程监控binlog信息变化，根据IO线程请求，将变化binlog日志内容发送给IO线程 -
步骤五：当从库IO线程接收到dump线程发送binlog日志信息会做两件事 ==relay log文件==
① 将接收的binlog内容存储到从库的relay log 文件 ② 会根据接收binlog位置点信息，更新master_info文件中的位置点 -
步骤六：利用SQL线程可以实现SQL语句信息回放，从而实现从库可以主库数据同步 ==relay log info文件==
① 加载relay log文件内容，并根据文件内容顺序，执行SQL语句信息 ② 更新relay log info文件内容，记录回放数据的位置点
==实现主从同步建立：需要创建生成4个文件 3个线程==
==4个文件：master_info，binlog，relay log，relay log info文件==
==3个线程：IO线程，SQL线程，dump线程==
2.3 主从同步建立过程
-
步骤一：创建两个数据库服务实例
10.0.0.51 3306 主实例 10.0.0.52 3306 从实例 （克隆的从实例 ①需要修改地址 ②清理数据目录重新初始化创建从库实例 注意server_id=2配置不能和主库一致） -
步骤二：在从库上备份数据信息
mysqldump -uroot -p123 -h10.0.0.51 -P3306 -A --source-data --single-transaction -R -E --triggers >/backup/all.sql mysql -uroot </backup/all.sql -
步骤三：在主库上创建连接用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123456'; grant replication slave on *.* to repl@'10.0.0.%'; -
步骤四：在从库编写配置信息
change master to MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_CONNECT_RETRY=10; MASTER_LOG_FILE='bin-log.000031', #实际情况填写 MASTER_LOG_POS=157 #实际情况填写 -
步骤五：启动主从同步功能/检查主从同步情况
mysql> start slave; mysql> show processlist; -- 从库查看线程 | 13 | system user | connecting host | NULL | Connect |31 | Waiting for source to send event | NULL | -- IO线程 | 14 | system user | | NULL | Query |31 | Replica has read all relay log; waiting for more updates | NULL| -- SQL线程 mysql> show processlist; -- 从库查看线程 | 16 | repl | 10.0.0.52:58394 | NULL | Binlog Dump |154 | Source has sent all binlog to replica; waiting for more updates | NULL|
2.4 主从异常排错
2.4.1 需要确认错误信息
show slave status\G
#表示从主库上同步数据位置点
Master_Log_File: bin-log.000031
Read_Master_Log_Pos: 1361
#已经存储到relay log位置点信息
Relay_Master_Log_File: bin-log.000031
Exec_Master_Log_Pos: 1361
#表示主从延迟情况 0 主从数据一致 >0 主从延迟越严重 参考数据库延迟监控配置：https://blog.csdn.net/m0_74208866/article/details/141168333
Seconds_Behind_Master: 0
#从库回放的relay log位置点信息
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 1528
Slave_IO_Running: Yes -- IO线程是否有异常
Slave_SQL_Running: Yes -- SQL线程是否有异常
#表示从库IO线程和SQL线程出现异常的原因介绍
Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
#设置SQL线程回放过滤白名单和黑名单数据库信息
Replicate_Do_DB:
Replicate_Ignore_DB:
#设置SQL线程回放过滤白名单和黑名单数据表信息
Replicate_Do_Table:
Replicate_Ignore_Table:
#设置SQL线程回放过滤多个表的白名单和黑名单设置
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
2.4.2 确认主从同步两个方面异常问题
- 确认主从是否正常同步
- 确认主从是否延迟同步
2.4.3 主从异常同步原因分析
(1) IO线程异常
情况01：IO线程状态显示为：connecting
- 原因1：连接地址、端口、用户、密码信息不对可能会导致连接异常
- 解决方法1：从库调整连接信息
stop slave;
reset slave all; -- 之前连接主库配置信息清除
change master to .. -- 重新和主库建立连接
PS：清理主从配置或关闭主从同步前，需要确认好主从同步的位置点
-
原因2：防火墙安全策略阻止连接建立、网络通讯配置异常影响连接建立
-
表现形式：从库上 ping 主库不通 telnet 主库端口 超时
-
解决方法：检查防火墙，iptables，selinux等，云上则查看安全组设置，找网络人员排查问题
-
原因3：到达数据库服务连接数上限，造成主从连接产生异常
-
解决方法：设置更改最大连接数
max_connections -- 设置数据库最大连接数（默认 151 建议范围不要超过1500-3000）
set global max_connections=3
**情况02：IO线程状态显示为：no **
- 原因1：IO线程在请求日志信息失败，有可能日志信息被无意清理了；(binlog清理 relaylog清理-重新生成)
- 原因2： IO线程在请求日志信息失败，有可能主从配置的标识信息重复冲突了；(server_id server_uuid）
(2) SQL线程异常
情况01：SQL线程状态显示为：no
- 原因：
- 回放的对象已经存在，涉及到的对象可能有库、表、用户、索引...; （DDL操作冲突）
- 插入（insert）的操作对象有异常、修改（update alter）的操作对象有异常、删除（delete drop）的操作对象有异常；（DML操作冲突 --约束信息）
- 由于数据库设置的约束信息，与执行的SQL语句产生冲突问题；
- 在数据库不同版本之间进行数据同步时，可能出现配置冲突问题（比如：5.6可以识别时间为0字段，5.7不能识别时间为0字段）
PS：当SQL线程进行回放数据（执行relaylog中SQL语句时），不能执行成功
-
可能造成此异常的情况：
- 在进行主从配置时，指定的位置点出现错误（change master to）；
- 在进行主从配置前，从库被写入相应的数据信息了，与主库同步数据产生冲突（误连接从库进行操作了）；
避免管理人员连接数据库出错，可以将从库设置为只读状态 read_only=ON super_read_only=ON- 在从库工作繁忙状态时，从库宕机了，业务恢复后可能出现异步同步数据错乱（主库操作创建表操作没同步，同步了插入表操作）；
避免异步方式同步数据错误，可以调整主从同步方式为 半同步 增强半同步 全同步- 在进行主从切换时（假设进行的是手工切换），没有正确操作锁定源主库和binlog日志信息；
导致切换前主库数据没有完全同步，切换后从库数据（原主库）比主库数据（原从库）信息更全；- 在应用数据库双主结构时，没有正确使用（经常导致相互同步数据，主键或唯一键冲突）
若企业创建必须使用双主架构，实现双写机制，可以使用全局序列机制，实现主键或唯一键的统一分配； -
解决方案：
- 方式一：将从库中冲突对象或数据删除（drop 对象 delete 数据）
- 方式二：跳过数据冲突的错误信息
set global sql_slave_skip_counter=2 -- 实现设置跳过冲突事务的次数信息 1007 错误码 表示数据库主从同步冲突 1050 错误码 表示数据表主从同步冲突 1062 错误码 表示数据信息主从同步冲突 slave_skip_errors='1007,1050,1062' -- 实现设置忽略指定错误码信息
(3) 确认主从是否延迟同步
- 外部因素
- 网络通讯不稳定，有带宽阻塞等情况，造成主从数据传输同步延时；
- 主从硬件差异大，从库磁盘性能较低，内存和CPU资源都不够充足；
- 主从配置区别大，从库配置没有优化，导致并发处理能力低于主库；（参考了解）
- 主库因素导致的延时问题
- 主要涉及Dump thread工作效率缓慢，可能是由于主库并发压力比较大；
- 主要涉及Dump thread工作效率缓慢，可能是由于从库数量比较多导致；（1-3从 1-4从）
- 主要涉及Dump thread工作效率缓慢，主要由于线程本身串型工作方式；（`利用组提交缓解此类问题-5.6开始 group commit`）
主库本身可以并发多个事务运行，默认情况下主从同步Dump thread只有一个，只能采用串型方式传输事务日志信息；
# 主库查看是否开启组提交：
binlog_group_commit_sync_delay -- 半同步主从构建时，会应用？
-- 表示延迟多少微秒同步到磁盘
binlog_group_commit_sync_no_delay_count
-- 表示延迟提交的最大事务数量
- 从库因素导致的延时问题
- 从库产生延迟受SQL线程影响较大，由于线程本身串型工作方式导致；
利用不同数据库并行执行事务操作，但是一个库有多张表情况，产生大量并发事务操作，依旧是串型的（5.6开始 多SQL线程回放）
利用logical_clock机制进行并发回放，由于组提交事务是没有冲突的，从库并行执行也不会产生冲突（5.7开始 多SQL线程回放）
根据日志内容信息，获取logical_clock机制的组提交标记信息：`（事务级别并发）`
# 从库开启逻辑时钟功能，并设置SQL多线程回放
slave_parallel_type=loglcal_clock
-- 设置回放方式为loglcal_clock
slave_parallel_workers=8
-- 设置SQL回放数据线程数量
- 其他因素导致的延时问题
- 由于数据库大事务产生的数据同步延时问题；（更新100W数据/尽量切割事务）
- 由于数据库锁冲突机制的数据同步延时问题；（资源被锁无法同步/隔离级别配置RR-锁冲突严重，可调整RC降低延时 索引主从一致）
- 由于数据库过度追求安全配置也会导致同步延时问题（从库关闭双一参数）；
2.5 数据库服务同步扩展
2.5.1 数据库服务延时同步
(1) 应用作用
可以利用延时从库，恢复主库误操作数据
(2) 构建过程
- 步骤一：创建两个数据库实例
10.0.0.51 3306 主库
10.0.0.52 3306 从库（延时）
-
步骤二：实现主从同步配置
- 主库进行数据备份 并创建同步用户
- 从库恢复数据信息
- 从库做主从配置 change master to
- 启动主从同步关系 start slave
-
步骤三：将从库设置为延时同步
stop slave sql_thread;
change master to master_delay=300; -- 设置延时300s
start slave sql_thread;
show slave status\G
SQL_Delay: 300 -- 开启了延时功能
SQL_Remaining_Delay: 258 -- 显示延时同步倒计时信息
(3) 应用延时从库恢复数据
- 步骤一：模拟企业操作管理主库信息
主库写操作
-- 正常的写操作
insert into test01.t1 values (4),(5),(6);
-- 错误的写操作（误操作）
drop database world;
- 步骤二：利用从库恢复数据信息
#只是不会回放错误数据，正确回放合理的SQL语句
#将从库上正确数据保存备份，导入到主库中恢复数据
stop slave sql_thread; #定位错误信息的位置点 db02-relay-bin.000002 993
1）在从库上回放正确数据信息
change master to master_delay=0;
start slave until relay_log_file="db02-relay-bin.000002", relay_log_pos=993;
2.5.2 数据库服务克隆同步
-
克隆数据应用方式：
-
方式一：本地克隆备份数据
-
步骤一：创建一个数据库实例 10.0.0.51 3306
-
步骤二：在数据库中安装克隆插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so'; 或者 [mysqld] plugin-load-add=mysql_clone.so clone=FORCE_PLUS_PERMANENT #以上安装克隆插件方式，都是表示永久配置 #检查插件 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone'; -
步骤三：创建克隆数据使用的用户信息
create user clone_user@'%' identified by 'password'; grant backup_admin on *.* to 'clone_user'; -
步骤四：实现数据本地克隆备份
mkdir -p /data/test chown -R mysql.mysql /data/ mysql -uclone_user -ppassword clone local data directory = '/data/test/clonedir'; #可以查看本地克隆进度情况 mysql> select stage,state,end_time from performance_schema.clone_progress;
-
-
方式二：远程克隆迁移数据
-
