mysql备份和恢复

binlog基础查看

开关	select @@log_bin;
日志路径及名字	select @@log_bin_basename;
服务ID号	select @@server_id;
二进制日志格式	select @@binlog_format;
日志内容查看	show binary logs;
刷盘周期	select @@sync_binlog;   1默认事务刷屏 0提交oscache 大于1 批量刷盘
显示当前日志记录	show master status ;
查看日志内容	show binlog events in 'mysql-bin.000003';
读取分页信息	pager less
外部查看binlog非二进制	mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003
时间范围查询	mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000004
基于位点截取	mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql bin.000003 >/tmp/bin.sql
基于gtid截取	mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' -- exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql bin.000004
导出时跳过gtid	mysqlbinlog --skip-gtids --include-gtids='483e9795-ad5b-11ea-86e8- 000c298e182d:1-20' --exclude-gtids='483e9795-ad5b-11ea-86e8-000c298e182d:14','483e9795-ad5b-11ea- 86e8-000c298e182d:18' mysql-bin.000009 >/data/bin1.sql

binlog相关配置

gtid打开（方便运维）

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
systemctl restart mysqld

binlog打开，8以后应该默认打开  row模式删除时会把每一条删除记录都写入日志。

server-id=12323
log-bin=mysql-bin
binlog-format=ROW
binlog-row-image=FULL

binlog备份还原策略

基于全库备份，增量binlog位点还原

全库备份（所有项目必须定期全量备份）

-A 全备参数 -B db1 db2 db3 备份多个单库 备份单个或多个表 -R 备份存储过程及函数 --triggers 备份触发器 -E 备份事件 -F 在备份开始时 , 刷新一个新 binlog 日志 --master-data=2 以注释的形式 , 保存备份开始时间点的 binlog 的状态信息 功能： （ 1 ）在备份时，会自动记录，二进制日志文件名和位置号 （ 2 ）自动锁表（ FTWRL ） （ 3 ）如果配合 --single-transaction ，只对非 InnoDB 表进行锁表备份， InnoDB 表进行 “ 热 “” 备，实际 上是实现 快照备份。 --single-transaction innodb 存储引擎开启热备 ( 快照备份 ) 功能

例子脚本

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --

set-gtid-purged=ON --max-allowed-packet=256M >/data/backup/full.sql

恢复例子（ 在生产中恢复要谨慎，恢复会删除重复的表, 并且恢复非常耗时）

①set sql_log_bin=0;

②source /backup/full.sql

③ set sql_log_bin=1;

部分误操作导出binlog恢复还原

基于gtid恢复

①关闭binlog： set sql_log_bin=0;

②导入备份的sql：source /tmp/binlog.sql（注意导出的时候gtid的号要去除，否则会进行事务去重）

③开启binlog：set sql_log_bin=1;

binlog日志清理

尽量进入mysql清理，rm文件方式不安全

PURGE BINARY LOGS TO 'mysql-bin.000010' ;

其他备份恢复方式

Percona Xtrabackup支持全量和增量备份，不过恢复前需要合并步骤比较繁琐。

Clone Plugin  mysql8.0.17后这个工具非常好用，2条命令就能备份恢复数据。