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的作用

1. 复制
2. 备份
3. 迁移

二进制日志Binlog的记录格式

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清除注意事项

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

二进制日志Binlog如何落盘？

• MySQL落盘过程

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

• Binlog落盘频率

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

1. sync_binlog=0

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

2. sync_binlog=1

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

3. 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的作用

1. 诊断问题
2. 性能调整
3. 审计

General Log的缺点

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

慢查询日志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 的特殊设置

1. log_slow_admin_statements

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

2. log_queries_not_using_indexes

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

3. 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记录所有信息

• 错误日志的内容解析

错误日志的作用

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";

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

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

中继日志的最佳实践

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

中继日志如何落盘？

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的作用

1. 可用于事务回滚
2. 应用于MVCC
3. 崩?；指?/li>

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

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

UndoLog的最佳实践

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

DBA必备的备份和恢复技能

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

• 准备

# 创建测试的用户和表
# 创建单独的备份用户
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备份原理总结

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

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

• 实时打印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

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

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

逻辑备份工具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

??实战数据定时自动备份

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

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

2. 在文件夹下创建脚本文件

vim mysqlbackup.sh

3. 写入脚本内容

#!/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一起使用。

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

chmod +x mysqlbackup.sh

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

./mysqlbackup.sh

6. 配置自动执行脚本

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

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

/etc/init.d/cron restart

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

• mydumper的特点

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

安装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 备份原理总结

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

尝试恢复

• 创建恢复库

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恢复原来总结

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

多线程逻辑备份工具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的特点

1. 非阻塞备份
2. 备份和恢复速度快
3. 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备份的原理

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

• XtraBackup恢复的原理
• • Prepare阶段

1. 模拟崩?；指?，将RedoLog回放到数据文件中
2. 重建RedoLog

• • 恢复阶段

1. 将数据文件复制或者移动到MySQL数据目录
2. 还原完成
3. 重启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天之前的备份目录

最新的官方备份方式 ClonePlugin

• clone plugin 的动态安装

# 在MySQL里面
install plugin clone soname 'msyql_clone.so';
# 查看是否安装成功
select plugin_name,plugin_status from information_schema.plugins where plugin_name = 'clone';

• clone plugin配置持久化

vim /data/mysql/conf/my.cnf
# 在[mysqld]下新增或修改
plugin-load-add=mysql_clone.so
clone=force_plus_permanent

• 创建备份用户并赋予权限

create user 'u_clone'@'%' identifiled with mysql_native_password by '123456';
grant backup_admin on *.* to 'u_clone'@'%';

本地克隆

• 创建备份文件夹

mkdir /data/backup/clone
chown -R mysql.mysql /data/backup/clone

• 执行本地克隆

mysql -u'u_clone' -p'123456';
clone local data directory='/data/backup/clone/bakdata';

远程备份

• 创建备份文件夹

mkdir /data/backup/clone
chown -R mysql.mysql /data/backup/clone

• 增加备份源列表

# 登录mysql
install plugin clone soname 'msyql_clone.so';
# 白名单，限制从哪些服务器复制数据
set global clone_valid_donor_list='192.168.12.161:3306';

• 执行远程克隆操作

clone instance from 'u_clone'@'192.168.12.162':3306 identifiled by '123456' data directory='/data/backup/clone/bak_from_12_161';
# 查看备份状态
select * from performance_schema.clone_status\G;

• clone plugin的注意事项

1. 备份源和恢复实例必须具有完全相同的MySQL服务器版本
2. 不支持克隆MySQL服务器配置
3. 只能备份InnoDB引擎的表

MySQL误操作恢复

drop表恢复：实验过程

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

drop表恢复：前提条件

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

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工具注意事项

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

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

• 模拟误操作

delete from d_recover.t1;

• 把误操作的binlog复制到新目录

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