DBA数据库笔记之(三)MySQL日志和备份

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

DBA必备的日志文件

二进制日志Binlog及其作用

Binlog的相关操作

开启Binlog

# 不支持动态修改
vim /data/mysql/conf/my.cnf
# 在[mysqld]下增加
log-bin=/data/mysql/binlog/mysql-bin
# 查看开启情况
show variables like "%log_bin%";

关闭Binlog

# 注释掉
# log-bin=/data/mysql/binlog/mysql-bin
# 新增参数
skip_log_bin
# 或者新增参数
# disable_log_bin
# 关闭当前会话的Binlog
set session sql_log_bin=0;

设置Binlog大小

# 新增参数 默认1G
max_binlog_size=512M
# 查看Binlog大小
show global variables like "max_binlog_size";

Binlog的作用

复制
备份
迁移

二进制日志Binlog的记录格式

日志格式	优点	缺点
statement	日志量少，节约IO，性能高	在主从复制中可能会导致主从数据不一致，比如使用了不确定的函数，类似uuid()函数等
row	主从数据基本一致，支持闪回	日志量多，传输和存储成本较高；不容易阅读
mixed	日志量少，节约IO,性能高，解决了statement格式部分数据不一致的情况	不支持闪回，部分高可用架构不支持该格式，不方便将数据同步到其他类型的数据库

Binlog日志格式的修改

修改配置文件

vim /data/mysql/conf/my.cnf
# 在[mysqld]找到 binlog_format=row

会话级别动态修改

set session binlog_format='row';

全局动态修改

set global binlog_gormat='row';

解析二进制日志Binlog内容

基于位点的解析

# 查看当前binlog文件和位点和gtid
show master status\G;
# 新建表，操作表后，位点变化
# 进入binlog所在路径
cd /data/mysql/binlog/
# 解析binlog，--start-position开始的位点，mysql-bin.000019 binlog文件通过show master status\G查看
mysqlbinlog --start-position=618 mysql-bin.000019 -vv > /data/01.sql
# 查看解析结果
cat /data/01.sql

基于时间的解析

mysqlbinlog --start-datetime="2023-05-08 22:30:00" mysql-bin.000019 > 02.sql

基于GTID解析Binlog

mysqlbinlog --include-gtids 'gtid' mysql-bin.000019 -vv > /data/03.sql

只解析某个库的

# 查看初始信息
show master status\G;
# 在两个库分别创建一张表, -d 指定数据库
mysqlbinlog --start-position=1517 --stop-position=2498 -d database mysql-bin.000019 -vv > /data/04.sql

解析加密的Binlog

vim /data/mysql/conf/my.cnf
# 配置Binlog加密，在在[mysqld]下增加
early-plugin-load=keyring_file.so
keyring_file_data=/data/mysql/keyring
binlog_encryption=on
# 重启mysql
# 查看binlog是否已经加密
show binary logs;

mysqlbinlog --read-from-remote-server -uroot -p --start-position=1517 --stop-position=196 mysql-bin.000019 -vv > /data/05.sql

# 关闭加密，注释代码，删除生成的加密binlog

对无用的二进制日志Binlog进行清除

Binlog的清除方法

自动清除

# 查询过期时间，后期版本肯能删除此参数
show global variables like "expire_logs_days";
# 另一个参数 /秒
show global variables like "binlog_expire_logs_seconds";
# 设置
set global binlog_expire_logs_seconds=604800;
# 两个参数不能同时使用
set global expire_logs_days=0;
# 自动清除超过新设置时间的日志记录
flush logs;

删除指定Binlog之前的文件

# 查看当前有哪些Binlog
show binary logs;
# 清除mysql-bin.000002这个日志之前的日志
purge binary logs to 'mysql-bin.000002';

删除指定时间之前的Binlog文件

purge binary logs before '2023-02-02 00:00:00';

Binlog清除注意事项

尽量采用自动清除的方式
确保要清楚的Binlog所有从库都不在使用了
MySQL磁盘需要监控

二进制日志Binlog如何落盘？

MySQL落盘过程

创建Binlog缓冲区
有变更操作，先写Binlog缓冲区
将Binlog缓冲区的数据写入到磁盘

Binlog落盘频率

# 查看落盘频率
show global variables like "sync_binlog";

sync_binlog=0

代表禁用MySQL服务将Binlog同步到磁盘的功能由操作系统控制Binlog的刷盘（性能较好，系统崩溃时可能丢失部分事务）

sync_binlog=1

表示每个事务都会同步到磁盘

sync_binlog=N

大于一的值

查询日志GeneralLog及其作用

General Log的设置

动态开启

# 查看genneral是否开启
show global variables like "general%";
# 开启genneral
set global general_log = on;

永久生效

 vim /data/mysql/conf/my.cnf
 # 找到 general_log_file = /data/mysql/log/mysql-general.log 在前面添加参数
 general_log = on

修改General Log的输出方式

# 查看输出方式
show global variables like "log_output";

查看General Log

在MySQL中执行一条查询语句
查看General Log

tail /data/mysql/log/mysql-general.log

General Log的作用

诊断问题
性能调整
审计

General Log的缺点

性能开销，不建议在生产环境开启
日志文件过大
安全性问题

慢查询日志SlowLog的开启及查看

动态开启

# 查询相关参数
show global variables like "slow_query_log%";
# 开启
set global slow_query_log = 1;
# 设置慢查询日志的路径
set global slow_query_log_file="";
# 慢查询的预值,线上业务建议设置为 1s
show global variables like "long_query_time";

永久生效

vim /data/mysql/conf/my.cnf
# 新增或者修改
# 是否开启慢查询日志
slow_query_log = 1
# 指定多少秒返回查询的结果为慢查询
long_query_time = 1
# 指定保存路径及文件名，默认为数据文件目录
slow_query_log_file = /data/mysql/log/mysql-slow.log


其它参数配置
long-queries-not-using-indexes　　 --记录所有没有使用到索引的查询语句
min_examined_row_limit=1000　　　　--记录那些由于查找了多余1000次而引发的慢查询
long-slow-admin-statements　　　　 --记录那些慢的optimize table，analyze table和alter table语句
log-slow-Slave-statements　　　　  --记录由Slave所产生的慢查询

Slow Log 的特殊设置

log_slow_admin_statements

开启记录管理语句，比如 create table

log_queries_not_using_indexes

开启记录不使用索引的查询

min_examined_row_limit

表示检查行数少于参数配置的值就不会记录到慢日志中，用来控制扫描行数，一般建议设置为0

Slow Log的内容解析

确定是否开启慢查询

show global variables like "slow_query_log";
show global variables like "slow_query_log_file";
show global variables like "long_query_time";

制造一条慢查询

select sleep(2);

查看慢查询

tail /data/mysql/log/mysql-slow.log

慢查询额外信息的输出

开启慢查询额外输出

set global log_slow_extra = on;

构造慢查询

select sleep(1);
tail /data/mysql/log/mysql-slow.log

官方慢查询分析工具 mysqldumpslow

不加任何选项

cd /data/mysql/log/
mysqldumpslow mysql-slow.log

按查询时间排序

# -s 表示排序； t 表示按时间排序； -t 表示显示前多少行
mysqldumpslow -s t -t 5 mysql-slow.log

按访问次数排序

mysqldumpslow -s c -t 5 mysql-slow.log

利用错误日志ErrorLog进行排错

错误日志的配置和查看

错误日志的路径

vim /data/mysql/conf/my.cnf
# 找到 log-error

或者

show global variables like "log_error";

错误日志的记录级别

show global variables like "log_error_verbosity";
# 1 只记录错误事件；2 记录警告和错误事件；3记录所有信息

错误日志的内容解析

错误日志的作用

借助错误日志定位启动异常
诊断数据库连接异常
显示死锁信息

8.0错误日志的过滤

开启错误日志过滤

# 在MySQL内安装组件(日志过滤器组件)
install component 'file://component_log_filter_dragnet';
# 控制MySQL要加载的日志组件；log_sink_internal 默认的日志接收器
set global log_error_services = 'log_filter_dragnet;log_sink_internal';

错误日志过滤举例

# 将信息事件限制为每一分钟最多只显示一个
set global dragnet.log_error_filter_rules = 'if prio >= information then throttle 1/60.';

关闭错误日志过滤

set global log_error_services = 'log_sink_internal';
# 卸载过滤器
uninstall component 'file://component_log_filter_dragnet';
# 日志级别调整为2
set global log_error_verbosity = 2;

通过表记录错误日志

通过文件记录错误日志存在的问题
从表中查询错误日志

select * from performance_schema.error_log limit 10;

错误日志表用法举例

# 查看最新的10行错误信息
select * from performance_schema.error_log order by logged desc limit 10;
# 查询日志级别为error的所有日志
select * from performance_schema.error_log where prio='error'\G;

performance_schema.error_log表不允许执行 truncate 操作

中继日志RedoLog

中继日志的配置

MySQL8.0.30之前中继日志的容量配置

# 不支持动态修改，每个文件的大小，文件个数
show global variables like "innodb_log_file%";

MySQL8.0.30或更高版本中继日志的容量配置

# 中继日志的总大小
show global variables like "innodb_redo_log_capacity";

中继日志的保存路径

show global variables like "innodb_log_group_home_dir";

中继日志在崩溃恢复中的作用

用两次写缓冲区的数据修复损坏的数据页
通过中继日志恢复数据页
事务处理

中继日志的最佳实践

配置合适的中继日志大小
将中继日志文件放在独立的磁盘上
中继日志性能指标监控

中继日志如何落盘？

show global variables like "innodb_flush_log_at_trx_commit";

innodb_flush_log_at_trx_commit 配置的含义

0，每秒将日志缓冲区写入日志文件一次，并在日志文件上执行磁盘刷新操作

1，在每次提交事务时，日志缓冲区都会写入日志文件中，并在日志文件上执行磁盘刷新操作（最安全的，性能最差，建议设为1）

2，在每次提交事务后写入日志，并且日志每秒刷新一次到磁盘

MySQL8.0的中继日志RedoLog的归档和禁用

⚠️中继日志的归档

中继日志归档的作用

MySQL在数据备份过程中可能会复制RedoLog，这期间MySQL频繁变更那么复制RedoLog的速度就跟不上RedoLog的生成速度，因为RedoLog是以覆盖方式记录就可能会丢失部分RedoLog，MySQL8.0.17引入RedoLog归档来解决MySQL在备份时RedoLog丢失的情况

创建用于中继日志归档的文件夹

mkdir -p /data/mysql/redolog-archiving/redo-20230101
chown -R mysql.mysql /data/mysql/redolog-archiving/
# 归档目录不能让其它用户访问，不能与MySQL已有的目录重复比如日志目录，数据目录
chmod 700 /data/mysql/redolog-archiving/redo-20230101/

启动RedoLog归档

set global innodb_redo_log_archive_dirs = "redolog-archiving:/data/mysql/redolog-archiving/";
# ⚠️启用失败 Permission denied未解决
do innodb_redo_log_archive_start("redolog-archiving","redo-20230101");
# 关闭
do innodb_redo_log_archive_stop();

中继日志的禁用

禁用中继日志

# 关闭
alter instance disable innodb redo_log;
# 查看状态
show global status like "innodb_redo_log_enabled";
# 开启
alter instance enable innodb redo_log;

进行开启和关闭Redo Log 写入速度的对比实现
实现结论

数据测试导入时建议关闭RedoLog，然后开启。MySQL正式运行时建议开启RedoLog。

回滚日志UndoLog

Undo Log的作用

可用于事务回滚
应用于MVCC
崩溃恢复

Undo Log在崩溃恢复过程中的作用

恢复RedoLog
扫描出UndoLog并缓存
依次处理每个undo段
提交或回滚事务

UndoLog的最佳实践

将UndoLog单独分配到高性能的硬盘上
合理控制undolog的数量和表空间数据
避免使用长事务(建议truncate而不是delete)

DBA必备的备份和恢复技能

逻辑备份工具mysqldump备份和恢复原理

差异性	物理备份	逻辑备份
备份内容	数据目录和文件,日志,配置文件等	建库和建表语句,数据写入语句
备份耗时	较快 (因为只涉及文件复制)	较慢(因为需要将数据转换为逻辑格式)
备份工具举例	Xtrabackup、ClonePlugin	mysqldump、mydumper
可移植性	可移植性较差，需要依赖底层硬件和操作系统	可移植性比物理备份要好，因为数据保存在逻辑文件中，不依赖底层硬件和操作系统
备份时机	MySQL运行和未运行时都可以备份	只能在MySQL启动时备份

准备

# 创建测试的用户和表
# 创建单独的备份用户
create user u_bak@'%' identified by '123456';
# 增加新增备份用户权限
grant select,reload,process,lock tables,replication client,replication_slave_admin,show view,trigger on *.* to'u_bak'@'%';

# 创建测试数据库，表,新增数据
create database bak1;
...

# 创建恢复用户
create user u_recover@'%' identified by '123456';
# 增加新增恢复用户权限
grant lock tables,drop,create,alter,select,insert on *.* u_recover'@'%';

# 创建用于恢复的数据库
create database recover1;

备份某个库

备份一个库的方式

cd /data/backup/
# bak1 数据库
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off bak1 > bak1.sql

恢复

# -h 恢复到目标服务器的数据库(不在mysql内部执行)
mysql -u'u_recover' -p'123456' -h 192.168.12.162 revocer1 < bak1.sql

mysqldump备份过程会执行哪些命令

实时打印general log

# 开启 general
set global general_log=on;
# 实时打印
show global variables like "%general%";
tail -f /data/mysql/log/mysql-general.log

执行备份查看会执行哪些命令

# 去掉 --set-gtid-purged=off 查看gtid过程
mysqldump -u'u_bak' -p'123456' bak1 > bak1.sql
# 窗口查看实时信息
tail -f /data/mysql/log/mysql-general.log

mysqldump备份原理总结

获取数据库的元数据信息
查看库里有哪些表
给备份的表加读锁
查看表结构
查看全表数据
查看表t1上的所有触发器
解锁，备份完成

逻辑备份恢复过程中会执行哪些命令

实时打印general log

# 开启 general
set global general_log=on;
# 实时打印
show global variables like "%general%";
tail -f /data/mysql/log/mysql-general.log

# 创建一个关闭gtid信息的备份
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off bak1 > bak1.sql

执行恢复操作查看会执行哪些命令

mysql -u'u_recover' -p'123456' -h 192.168.12.162 revocer1 < bak1.sql
# 窗口查看实时信息
tail -f /data/mysql/log/mysql-general.log

逻辑备份恢复过程原理总结

删除要导入的表（如果存在）
重新创建要导入数据的表
给表加写锁
关闭表的索引
写入备份数据
打开表的索引
解锁，恢复完成

逻辑备份工具mysqldump常用用法及如何恢复？

mysqldump有哪些细节上的用法

测试环境准备

创建两表，两库，测试数据

给恢复用户添加权限

grant lock tables,drop,create,alter,select,insert,delete,system_variables_admin,references,trigger,create view,set_user_id,system_user on *.* to u_recover@'%';

mysqldump备份举例

备份不增加GTID信息

# 备份
mysqldump -u'u_bak' -p'123456' bak1 > bak1.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 revocer1 < bak1.sql
# 备份时打印GTID信息，可能恢复时报错。可设置 --set-gtid-purged=off 不记录GTID的信息
# MySQL全备，或者需要备份创建从库则建议去掉 --set-gtid-purged=off 参数

备份多个库

# 备份
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -B bak1 bak2 bak3 > bak1_bak2_bak3.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 < bak1_bak2_bak3.sql

备份所有数据库

# 备份
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -A > bak_all.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 < bak_all.sql

备份某一张表

# 备份
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off bak1 t1 > bak1_t1.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 bak1 < bak1_t1.sql

备份某个库中的多张表

# 备份
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off bak1 t1 t2 t3> bak1_t1_t2_t3.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 bak1 < bak1_t1_t2_t3.sql

远程备份

# 备份
mysqldump -u'u_bak' -p'123456' -h'192.168.12.161' --set-gtid-purged=off bak1 t1 > bak1_t1.sql
# 本地恢复
mysql -uu_recover -p'123456' bak1 < bak1_t1.sql

备份文件中增加删库语句

# --add-drop-database -B 
mysqldump -u'u_bak' -p'123456' -h'192.168.12.161' --set-gtid-purged=off --add-drop-database -B bak1 bak2 > bak1_bak2.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 < bak1_bak2.sql

不加建库语句

# 不加建库语句的备份
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -n -B bak1 bak2 > bak1_bak2.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 < bak1_bak2.sql

在备份文件中不增加删表语句

# 在备份文件中不增加删表语句的备份 --skip-add-drop-table
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off --skip-add-drop-table bak1 t1 > bak1_t1.sql
# 先删除已经存在的表 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 bak1 < bak1_t1.sql

insert语句包含所有的列名

# -c 指令
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -c bak1 t1 > bak1_t1.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 bak1 < bak1_t1.sql

只备份表结构

# -d 只备份表结构不备份数据
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -d bak1 t1 > bak1_t1.sql
# 恢复
mysql -u'u_recover' -p'123456' -h 192.168.12.162 bak1 < bak1_t1.sql

只备份表数据

mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -t bak1 t1 > bak1_t1.sql

用replace语句代替insert语句

# 只适用于 只备份表数据 的前提下(备份的数据恢复到已经存在的数据，有相同的数据则以备份数据为准)
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -t --replace bak1 t1 > bak1_t1.sql

用insert ignore 语句代替insert语句

# 只适用于 只备份表数据 的前提下(备份的数据与目标数据有相同的数据则以目标数据为准)
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -t --insert-ignore bak1 t1 > bak1_t1.sql

带条件的备份

# -w""  
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off bak1 t1 -w"id=1"> bak1_t1.sql

在备份前刷新MySQL服务的日志

# 查看所有的Binlog文件
show binary logs;
# 备份(在备份前刷新Binlog文件生成新的Binlog文件)
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off -F bak1 t1 > bak1_t1.sql

开启一个事务进行备份操作

# --single-transaction 可重复读，对事务类型的表有用（InnoDB），不阻塞应用的情况下保证数据一致性
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off --single-transaction bak1 t1 > bak1_t1.sql

不带加锁语句

mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off --skip-add-locks bak1 t1 > bak1_t1.sql

记录备份实例的位点

# 通过备份创建备份原的从库，可增加位点参数
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off --master-data=1 bak1 t1 > bak1_t1.sql
# 想在备份的时候看到位点信息在执行的时候又不执行位点信息
mysqldump -u'u_bak' -p'123456' --set-gtid-purged=off --master-data=2 bak1 t1 > bak1_t1.sql

--master-data={0|1|2}
0: 表示不记录二进制日志文件及其位置
1：以CHANGER MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器
2：以CHANGER MASTER TO 的方式记录位置，但默认为被注释

更多
mysqldump --help

⚠️实战数据定时自动备份

新建文件夹(根据自己的喜好创建)

mkdir /root/backup
mkdir /root/backup/data

在文件夹下创建脚本文件

vim mysqlbackup.sh

写入脚本内容

#!/bin/bash
# 逻辑备份目标数据库
date_str=$(date +%Y%m%d-%T)
mysqldump -u'backup' -p'12345678' -R -E -e -F --single-transaction --source-data=2 study > /root/mysql_backup/sql/$date_str.sql
#删除15天前的备份,只保留7天内的备份
find /root/mysql_backup/sql  -type f -name '*.sql' -mtime +15 -exec rm -rf {} \;

--routines, -R :导出存储过程以及自定义函数。
--events, -E :导出事件。
--extended-insert, -e :使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项。
–master-data={0|1|2}
:在未来版本将废弃使用--source-data替代
0: 表示不记录二进制日志文件及其位置
1：以CHANGER MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器
2：以CHANGER MASTER TO 的方式记录位置，但默认为被注释
--lock-all-tables :锁定所有表
--flush-logs ：滚动日志
如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备，这个选项不要和--lock-all-tables一起使用。

在文件夹下为脚本文件授权

chmod +x mysqlbackup.sh

在文件夹下进行检验是否成功备份

./mysqlbackup.sh

配置自动执行脚本

vim /etc/crontab        
添加指令：
00 0    * * *   root    /root/backup/data/./mysqlbackup.sh

使添加的自动执行配置生效

/etc/init.d/cron restart

多线程逻辑备份工具mydumper的备份和恢复原理

mydumper的特点

多线程
导出的文件更容易管理（结构和数据分开存放）
能保证一致性

安装mydumper

下载mydumper

wget https://github.com/mydumper/mydumper/releases/download/v0.14.5-2/mydumper-0.14.5-2.el7.x86_64.rpm

安装mydumper

yum install mydumper-0.14.5-2.el7.x86_64.rpm -y
# 检车是否安装成功
mydumper --help

实验

创建备份用户，赋权

create user u_mydumper@'%' identifiled with mysql_native_password by '123456';
grant create,insert,select,reload,process,lock tables,replication client,replication_slave_admin,show view,trigger,backup_admin on *.* to u_mydumper@'%';
# 给用户加权限
grant super on *.* to u_mydumper@'%';

开启general log

set global general_log=on;
tail -f /data/mysql/log/mysql-general.log

cd /data/backup/
mkdir mydumper_bak

备份

# -o 要备份的文件夹
mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -B bak1 -o ./mydumper_bak/bak1

查看备份文件
查看备份过程的General Log
mydumper 备份原理总结

将所有当前打开的表都刷新到磁盘上
主线程给所有的表加全局读锁
创建多个线程，隔离级别设置为RR，都开启事务
查看位点，GTID信息
查看建库语句
获取表结构
通过select * 的方式获取数据
解锁
退出所有线程，备份完成

尝试恢复

创建恢复库

create databse bak1;
# 恢复
mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -B bak1 -d ./mydumper_bak/bak1/
# 查看恢复过程的General Log
tail -f /data/mysql/log/mysql-general.log

mydumper恢复原来总结

创建多个线程
进入到恢复库中创建表
多个线程开启事务
多个线程分别导入不同表的数据
提交
退出线程，恢复完成

多线程逻辑备份工具mydumper的使用及恢复

mydumper的使用

备份某一个库

cd /data/backup/mydumper_bak/
# 备份到哪个文件夹 -o ./bak1
mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -B bak1 -o ./bak1
# 恢复
myloader -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -B bak1_recover -d ./bak1

备份某一张表

# 备份多个表 -T bak1.t1,t2 
mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -T bak1.t1 -o ./bak1_t1
# 恢复 ，先删除已经存在的表
myloader -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -B bak1_recover -d ./bak1_t1

备份所有库

mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -o ./bak_all
# 恢复
myloader -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -d ./bak_all

不备份mysql和test库

mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock --regex '^(?!(mysql\|test\.))' -o ./no_bak_mysql_test

只存储mysql和test库

mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock --regex '^(mysql\|test\.)' -o ./bak_mysql_test

不存储以bak开头的所有数据库

mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock --regex '^(?!(bak))' -o ./no_bak_all

指定线程数进行备份

# 开启general log
set global general_log = on;
# 实时打印
tail -f /data/mysql/log/mysql-general.log

# 指定线程数进行备份 -t 8 
mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -t 8 -o ./bak_8_thired

压缩备份

mydumper -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -B bak1 --compress -o ./bak1
# 恢复,直接恢复
myloader -u 'u_mydumper' -p '123456' -S /tmp/mysql.sock -B bak1_recover -d ./bak1

物理备份工具XtraBackup备份和恢复原理

XtraBackup的特点

非阻塞备份
备份和恢复速度快
XtraBackup 8.0不支持备份MySQL 8.0之前的版本

XtraBackup的安装

# linux系统
wget https://downloads.percona.com/downloads/Percona-Xtrabackup-8.0/Percona-Xtrabackup-8.0.25-17/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.25-17.1.el7.x86_ 64.rpm
#ubuntu系统
wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/debian/focal/x86_64/percona-xtrabackup-80_8.0.26-18-1.focal_amd64.deb

安装

#linux系统
yum install percona-xtrabackup-80-8.0.25-17.1.el7.x86_ 64.rpm -y
#ubuntu系统
sudo dpkg -i percona-xtrabackup-80_8.0.26-18-1.focal_amd64.deb

XtraBackup备份的原理

记录LSN,复制InnoDB的数据文件，复制RedoLog
备份加锁
备份完事务引擎的数据和日志后，锁定非事务表
复制非事务引擎的表数据文件
查询GTID信息和Binlog位点
停止复制RedoLog
释放锁
备份完成

XtraBackup恢复的原理
- Prepare阶段

模拟崩溃恢复，将RedoLog回放到数据文件中
重建RedoLog

- 恢复阶段

将数据文件复制或者移动到MySQL数据目录
还原完成
重启MySQL

XtraBackup实战

创建备份和恢复用户

# 创建备份用户
create user 'u_xtrabackup'@'localhost' identifiled with mysql_native_password by '123456';
grant select,reload,lock tables,backup_admin,replication client,reate tablespace,process,super on *.* to 'u_xtrabackup'@'localhost';

mkdir /data/backup/xtrabackup_bak/

在恢复的机器上安装XtraBackup

wget https://downloads.percona.com/downloads/Percona-Xtrabackup-8.0/Percona-Xtrabackup-8.0.25-17/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.25-17.1.el7.x86_ 64.rpm
# 安装
yum install percona-xtrabackup-80-8.0.25-17.1.el7.x86_ 64.rpm -y

备份所有数据

# 自动创建文件夹 /data/backup/xtrabackup_bak/alldata/
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --u_xtrabackup -p'123456' --backup --target-dir=/data/backup/xtrabackup_bak/alldata/

把备份传输到目标机器

scp -r /data/backup/xtrabackup_bak/alldata/ 192.168.12.162:/data/backup/
# 输入密码

数据恢复

执行prepare

xtrabackup --prepare --target-dir=./

清空目标实例

# 停掉mysql，备份目标数据文件
/etc/init.d/mysql.server stop
cd /data/mysql
mkdir /data/mysql_bak
mv data/ /data/mysql_bak
mv binlog/ /data/mysql_bak
mv log/ /data/mysql_bak

恢复数据并启动MySQL

cd /data/backup/alldata
# --copy-back 或者 --move-back
xtrabackup --dedaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
# 手动创建一下log文件夹
cd /data/mysql
mkdir log
touch /data/mysql/log/mysql.err
chown -R mysql.mysql ./*
# 启动MySQL
/etc/init.d/mysql.server start

压缩备份和恢复

压缩备份

xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'123456' --backup --compress --target-dir=/data/backup/xtrabackup_bak/alldata_compress/
# 将压缩文件传输到目标服务器
scp -r /data/backup/xtrabackup_bak/alldata_compress/ 192.168.12.62:/data/backup/

解压备份文件

# 检查是否安装qpress命令
# 解压
xtrabackup --decompress --target-dir=/data/backup/alldata_compress/

数据恢复

xtrabackup --prepare --target-dir=/data/backup/alldata_compress/
/etc/init.d/mysql.server stop
cd /data/mysql
mkdir /data/mysql_bak
mv data/ /data/mysql_bak
mv binlog/ /data/mysql_bak
# 恢复操作
xtrabackup --dedaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=/data/backup/alldata_compress
chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start

让chatgtp帮我们编写自动化备份脚本

通过xtrabackup备份数据的命令如下：
xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'123456' --backup --compress --target-dir=/data/backup/xtrabackup_bak/alldata_compress/
请帮我写一个MySQL物理备份脚本，每天0点执行备份，备份文件夹的名字以bak_开头，包括时间戳，并且通过find的形式删除10天之前的备份目录

MySQL误操作恢复

drop表恢复：实验过程

往测试表test_recover写入测试数据，数据是（1,1）,（2,2）
进行全备，全备中的数据自然有（1,1），（2,2）
增量写入（3,3）
误操作：drop table test_recover;
在恢复实例导入全备，数据是1,1）,（2,2）
通过Binlog获取增量数据（3,3）导入恢复实例
恢复实例表test——recover的数据到入到误操作的实例
恢复完成

drop表恢复：前提条件

有要回档时间点之前的全备
开启Binlog，并且从全备到回档时间点的Binlog不能缺失
尽可能有已经部署好的恢复实例

drop表恢复：环境准备

新建测试表并写入数据

create database recover;
use recover;
# 创建测试表
... ...

增加备份用户

create user 'u_backup'@'localhost' identifiled with mysql_native_password by '123456';
grant select,reload,process,super,lock tables,backup_admin on *.* to 'u_backup'@'localhost';

进行全备

xtrabakup --defaults-file=/data/mysql/conf/my.cnf -uu_backup -p'123456' --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream

模拟增量数据导入

use recover
insert into ... ...

查询测试表所有数据
模拟误操作

drop table test_recover;

drop表恢复：全备导入新实例

全备传输到新实例

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

清空新实例

/etc/init.d/mysql.server stop
rm /data/msyql/data/* -rf
rm /data/msyql/binlog/* -rf

把全备导入到新实例

# 相当于解压
xbstream -x < xtrabackup.xbstream 
xtrabackup --prepare --target-dir=./
# 将全备移动到数据目录文件下
xtrabackup --dafults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
# 修改属组
chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start

drop表恢复：在新实例恢复增量数据

将Binlog拷贝到新实例

# 查看原实例全备时的Binlog和目标Binlog
cat xtrabackup_binlog_info
# 将原最新Binlog传到恢复实例
scp mysql-bin.000023 192.168.12.162:/data/backup/binlog
cd /data/backup/binlog

确定误操作前一个事务的gtid值

mysqlbinlog mysql-bin.000023 --start-datatime='2023-06-21 00:30:00' --stop-datatime='2023-06-21 00:59:00' --base64-output=decode-rows -v >/data/backup/1.sql
# 找到误操作前一个事务的gtid值
vim data/backup/1.sql

导入增量数据

#在目标实例增加恢复用户
... ...
mysqlbinlog --include-gtids='xxxxxx:2310761-2310762' mysql-bin.000023 | mysql -uu_recover -p'123456'

drop表恢复：将误操作的数据恢复到原实例

导出无删除的表

mysqldump -uroot -p --set-gtid-purged=off --skip-add-drop-table recover test_recover > recover_test_recover.sql

把误操作表的备份文件传输到原实例

scp recover_test_recover.sql 192.168.12.162:/data/backup

误操作的实例上恢复数据

cd /data/backup
mysql -uroot -p recover < recover_test_recover.sql

delete误删除恢复：环境准备

# 创建测试数据库，表，插入数据
# 创建恢复用户，赋予权限
... ...

安装Binlog解析工具my2sql

# github上下载my2sql，可直接执行命令
chmod +x my2sql
./my2sql

delete误删除恢复：my2sql工具注意事项

Binlog格式必须为row，且binlog_row_image=full
只能回滚DML,不能回滚DDL
用户密码认证必须是mysql_native_password才能解析

delete误删除恢复：模拟误操作并确认位点

模拟误操作

delete from d_recover.t1;

cd /data/mysql/binlog
mkdir /data/backup/rollback
cp mysql-bin.000023 /data/backup/rollback

查看误操作事务开始和结束的位点

# 估算误操作的时间
mysqlbinlog mysql-bin.000023 --start-datatime='2023-06-21 00:30:00' --stop-datatime='2023-06-21 00:59:00' --base64-output=decode-rows -v >operation.sql
# 找到误操作的开始和结束位点
vim operation.sql

delete误删除恢复：恢复操作

生成回滚SQL

./my2sql -user u_rellback -password '123456' -hot 127.0.0.1 -database d_recover -tables t1 -work-type rollback -start-file ./rollback/mysql-bin.000023 -start-pos 3556 -stop-pos 3719 -output-dir recover_o1
# 查看
cd recover_o1
cat rollback.23.sql

导入回滚sql

mysql -uroot -p < rollback.23.sql