上节回顾

binlog如何开启

#在my.cnf配置二进制日志路径和记录方式
#log_bin配置的位置就是二进制日志存放的位置
log_bin=/data/binlog/mysql-bin
#binlog_format的设置会改变
binlog_format=row
#5.7版本以后还得设置server_id
server_id=6

mysql的双1设置

#在my.cnf配置双1可以保证数据安全,但是会降低数据库性能
innodb_flush_log_at_trx_commit=1
sync_binlog=1

由于进程调度策略问题,每秒执行一次flush(刷到磁盘)操作并不是保证100%的每秒

  • innodb_flush_log_at_trx_commit设置为0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作
  • innodb_flush_log_at_trx_commit设置为1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去
  • innodb_flush_log_at_trx_commit设置为2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行.该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作

如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作

  • sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log
  • 当sync_binlog =N (N>0),MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去

mysqlbinlog日志查询

  • --skip-gtids : 查看日志时不显示gtid
  • --include-gtids : 显示被包含的gtid的日志
  • --exclude-gtids : 不显示被包含的gtid的日志
#查看日志
mysqlbinlog mysql-bin.000007
#查看日志详细信息
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000007
#查看gtid为1的日志
mysqlbinlog --include-gtids='192ae722-6e3e-11ea-9164-002170ee9a77:1' mysql-bin.000007

binlog操作语句

#查看对应binlog文件的内容
show binlog events in 'mysql-bin.000002';
#查看log_bin_basename参数
select @@log_bin_basename;
#查看所有已存在的二进制日志
show binary logs;
#刷新日志,会创建一个新的二进制日志文件
flush logs;
#查看正在使用的二进制日志
show master status;

日志清理

  • 自动清理
#在my.cnf设置日志保留周期,至少1轮全备周期长度的过期时间
expire_logs_days=15
  • 手动清理
#清理该日志之前的所有日志,不包括该日志,具体操作可以通过help purge命令查看
PURGE BINARY LOGS TO 'mysql-bin.000003';
#清空全部日志
reset master;

备份恢复

  • 备份周期: 根据数据量来调整
  • 备份工具: mysqldump(MDP),percona Xtrabackup(PBK,XBK),MySQL Enterprise BACKUP(MEB),mysqlbinlog
  • 备份方式: 逻辑,全备,增量,物理全备,物理增量
  • 检查备份可用性: crontab -l查看定时任务,根据定时任务执行的脚本寻找备份路径,查看备份文件的大小和内容,定期做备份恢复演练,确保备份可用
  • 备份类型: 热备(对于业务影响最小),温备(长时间锁表备份),冷备(业务关闭情况下备份)

mysqldump

连接数据库参数

  • -u : 登录用户
  • -p : 登录密码
  • -S : Socket方式登录
  • -h : 登录主机
  • -P : 登录端口
  • -A : 全库全表备份
  • -B : 指定库备份,后面需要跟上库名,用空格分隔
  • -R : 存储过程和函数,备份时必加
  • -E : 存储事件,备份时必加
  • --max_allowed_packet : 控制的是备份时传输数据包的大小,例如--max_allowed_packet=128M
  • --triggers : 存储表的触发器,备份时必加
  • --master-data[=#] : 备份时必加,这将导致二进制日志位置和文件名附加到输出中。如果等于1，则将其作为更改主命令打印；如果等于2，则该命令将以注释符号作为前缀。此选项将启用--lock all tables，除非也指定了--single transaction（在这种情况下，全局读取锁定只在转储开始时占用很短的时间；不要忘记阅读下面的--single transaction）。在任何情况下，日志上的任何操作都将在转储的确切时刻发生。选项会自动关闭--lock-tables。
  • --single-transaction : 备份时必加,通过在单个事务中转储所有表来创建一致的快照。只适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB支持多版本控制);不能保证转储与其他存储引擎一致。在处理——单事务转储时，为了确保转储文件有效(正确的表内容和二进制日志位置)，其他连接不应该使用以下语句:ALTER table、DROP table、RENAME table、TRUNCATE table，因为一致的快照不与它们隔离。选项会自动关闭--lock-tables。

--master-data=2会以注释的形式记录备份时刻的binlog信息,加上--single-transaction对于InnoDB的表,进行一致性快照备份,不锁表

#备份所有库所有表
mysqldump -uroot -p123456 -A > /backup/a.sql
#备份binlog库,gtid库所有表
mysqldump -uroot -p123456 -B binlog gtid  > /backup/b.sql
#备份world库city表和country表
mysqldump -uroot -p123 world city country > /backup/tab.sql

恢复案例

  • 背景环境:正在运行的网站系统,mysql-5.7.20 数据库,数据量50G,日业务增量1-5M
  • 备份策略:每天23:00点,计划任务调用mysqldump执行全备脚本
  • 故障时间点:年底故障演练:模拟周三上午10点误删除数据库

处理过程

  1. 停业务,挂维护页,避免数据的二次伤害
  2. 找一个临时库,恢复周二23:00全备
  3. 截取周二23:00 - 周三10点误删除之间的binlog,恢复到临时库
  4. 测试可用性和完整性
  5. 方法一:直接使用临时库顶替原生产库,前端应用割接到新库,方法二:将误删除的表导出,导入到原生产库
  6. 开启业务

故障模拟演练

准备数据

create database backup;
use backup;
create table t1 (id int);
insert into t1 values(1),(2),(3);

模拟备份

mysqldump -uroot -p123456 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction | gzip > /backup/full_$(date +%F).sql.gz

模拟数据变化

use backup;
insert into t1 values(11),(22),(33);
create table t2 (id int);
insert into t2 values(11),(22),(33);

模拟故障(生产环境,谨慎操作)

drop database backup;

恢复到临时库

  • 到备份目录下解压备份
gunzip /backup/full_2020-03-27.sql.gz
  • 从日志中找到备份时二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=753;
  • 去日志中查找到删库语句之前一条的position号,然后导出全备中没有记录的那一部分变化的数据
mysqlbinlog --skip-gtids --start-position=753 --stop-position=1519 /data/binlog/mysql-bin.000001 > /backup/bin.sql
  • 登录到临时库执行命令
set sql_log_bin=0;
source /backup/full_2019-07-15.sql
source /backup/bin.sql
  • 将恢复后的库导出
mysqldump -uroot -p123456 -B backup > /backup/backup.sql
  • 恢复备份到生产库
set sql_log_bin=0;
source /backup/backup.sql
set sql_log_bin=1;

数据库恢复练习

  1. 创建一个数据库1997sty,并创建一张表t1,然后插入5行任意数据
  2. 全备
  3. 插入两行数据,任意修改3行数据,删除1行数据
  4. 删除所有数据
  5. 再t1中又插入5行新数据,修改3行数据
  6. 跳过第四步恢复表数据

在构建主从时,使用--set-gtid-purged=AUTO或--set-gtid-purged=ON,仅是做普通的本机备份恢复时,可以添加--set-gtid-purged=OFF

#创建一个数据库`1997sty`,并创建一张表`t1`,然后插入5行任意数据
create database 1997sty charset utf8mb4;
use 1997sty;
create table t1(id int);
insert into t1 values(1),(2),(3),(4),(5);

#全备
mysqldump -uroot -p123456 -A -R --triggers --master-data=2 --single-transaction -E > /backup/lianxi.sql

#插入两行数据,任意修改3行数据,删除1行数据
insert into t1 values(6),(7);
update t1 set id=0 where id<4;
delete from t1 where id=4;

#删除所有数据
delete from t1;

#再`t1`中又插入5行新数据,修改3行数据
insert into t1 values(11),(22),(33),(44),(55);
update t1 set id=99 where id>30;

跳过第六步恢复表数据

#查看binlog记录位置
SET @@GLOBAL.GTID_PURGED='192ae722-6e3e-11ea-9164-002170ee9a77:1-21';
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4610;

#通过查看binlog分析出192ae722-6e3e-11ea-9164-002170ee9a77:25是删除全表数据的语句,然后导出二进制日志
mysqlbinlog --skip-gtids --exclude-gtids='192ae722-6e3e-11ea-9164-002170ee9a77:25' --include-gtids='192ae722-6e3e-11ea-9164-002170ee9a77:22-27'  --set-gtid-purged=OFF /data/binlog/mysql-bin.000001 > /backup/lianxibin.sql

恢复数据

#登录到临时数据库恢复数据
#恢复全表数据
source ~/lianxi.sql
#恢复除误操作以外的操作
source ~/lianxibin.sql

导出恢复后的数据,还原生产库

#导出数据
mysqldump -uroot -p123456 1997sty t1 > 1997sty.sql

#登录后还原数据
use 1997sty
set sql_log_bin=0;
source /backup/1997sty.sql
set sql_log_bin=1;

物理备份percona Xtrabackup

安装依赖包

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

下载软件并安装

percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

备份核心理念

  1. 针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件
  2. 针对InnoDB表,立即触发CKPT,copy所有InnoDB表相关的文件(ibdata1,ibd,frm)并且将备份过程中产生,新的数据变化的部分redo一起备份走
  3. 在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复

数据库全备

innobackupex --user=root --password=123456 --socket=/tmp/mysql.sock --no-timestamp /backup/full

利用全备进行恢复

#停止数据库
pkill mysqld
#删除数据库所有文件
rm -rf /data/mysql/data/*
#恢复数据库
innobackupex --apply-log /backup/full/
cp -a /backup/full/* /data/mysql/data/
chown -R mysql.mysql /data/mysql/data/*
#恢复完成,启动数据库
systemctl start mysqld