MySQL备份恢复

---

MySQL备份恢复

	1. Binlog⽇志深⼊分析
	1.1. Binlog记录模式及参数设置
	1.2. Binlog⽇志正确的打开⽅式
	2. 对备份的正确理解
	2.1. 数据⼀致性的分析
	2.2. 使⽤mysqldump备份数据
	2.3. 不同存储引擎下的数据备份

1. Binlog⽇志深⼊分析

1.1. Binlog记录模式及参数配置

	DDL：全部记录，定义语⾔
	DML：除select以外都会记录
	
	log_bin=mysql-bin
	binlog_format=statement|row

mysql-bin是basename，mysql-bin-000001.log
Binlog有三种记录模式

		statement：SBR：delete from mytable 、update（基于⼀个简单的回放）
		create table
		insert
		update
		delete
		insert
		update table set a=1

先查看了⼀下⽇志

	1、mysql> show binlog events in 'mysql-bin.000001';
	2、mysqlbinlog --start-position=100 --stop-position=120 --database=mydb mysqlbin.000001 > mysql.sql

statement⾥⾯⾯只有操作语句是⾮注释的，其他的说明都是#注释的
看我们的binlog⽇志⼤⼩

	show variables like '%binlog_size%'; #如果⼀个事务超过binlon⼤⼩不会写⼊下⼀个
	max_binlog_size=1024m #每个binlog⽇志⽂件⼤⼩
	expire_logs_days=7 #binlog的过期时间
	binlog_cache_size=32768 DML操作不频繁 <=1m, DML频繁且事务⼤ 2-4m
	max_binlog_cache_size 32位4G，64位16P

	mysql> flush logs; #⽣成⼀个新binlog
	mysql> show binary logs; #查看系统binlog数

如果是mysqldump
1、找到这个表最初的记录表结构，和当时的数据，把这个数据insert全部拿出来
2、insert into，备份的时间点和出事的那个阶段咋办？

	row：RBR：update、delete=10，展示出10条更改前和更改后的语句

这个时候使⽤show binlog来查看已经看不到语句

	mysql> show binlog events in 'mysql-bin.000003'; #?是否还有⽤
	
	mysqlbinlog --base64-output=decode-rows -v mysql-bin.000003
	
	mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 #增加数据类型了

mixed：MBR：90%都是statement的模式

	90%的语句都是以statement模式进⾏的

1.2. Binlog⽇志的正确打开⽅式

	mysql> show binlog events in 'mysql-bin.000002'
	mysql> show binlog events [IN 'log_name'][FROM pos][LIMIT [offset,]
	row_count];

	mysqlbinlog --no-defaults --database=mydb --base64-output=decode-rows -v --
	start-position=123 --stop-position=456 mysql-bin.000002
	mysqlbinlog --no-defaults --database=mydb --base64-output=decode-rows -v --
	start-datetime='2019-12-11 16:30:00' --stop-datetime='2019-12-11 16:31:00'
	mysql-bin.000003

2. 对备份的正确认识

	全量备份：对应时间的数据是全量的⼀个备份
	差异备份：周⽇做了⼀次

时间点恢复

上⾯三个备份节点都是⼀个定时的数据补偿，在定时备份完成后⾄任意备份时间节点前，这段时间出现
问题需要Binlog能做的事情了

热备

数据库的读写操作均可正常进⾏，是要通过备份⼯具，myisam引擎不⽀持热备，innoDB⽀持热备

温备

数据库只能进⾏读操作，不能进⾏写操作

冷备

要让数据库停机

物理备份

直接copy数据⽂件

逻辑备份

将数据库⾥的数据导出进⾏备份的⽅式就是逻辑备份

2.1. MySQL常⽤的备份⼯具

mysqldump

mysql⾃带的备份⼯具，是逻辑备份
innodb可以使⽤mysqldump进⾏热备
myisam可以使⽤mysqldump进⾏温备
如果数据量较⼩可以使⽤

xtrabackup

Percona提供
是⼀种物理备份⼯具
⽀持完全备份，差异备份，增量备份

select语句直接备份

select * from a into outfile '/usr/local/a.bak'

cp命令

只能进⾏冷备

2.2 数据⼀致性的理解

数据⼀致性

热备：数据库还依旧可以读写
4:00 进⾏定时备份，假设你的数据⾮常多，需要备份10-20分钟
⼩刘账户余额在4点有200元，4点10分的时候，他转出了50元
假如在4点10分前还没有备份到余额表，4点11分开始备份余额表

在备份场景下如何保证数据⼀致性

第⼀种⽅式，在备份的时候给所有表加锁，只能读不能写
如果锁表的时候可以把写⼊的数据先放⼊MQ或缓存，待备份完成补偿进数据库，还有
就是要考虑及时读的问题
第⼆种⽅式：在备份开始的时候就对数据库的所有数据进⾏⼀个“快照”，快照记录了开始备份
的那⼀刻的数据状态

2.3 使⽤mysqldump备份

缺点：当数据位浮点型，会出现精度丢失
如果要进⾏并⾏备份可以使⽤mydumper/myloader
mysqldump -uroot -p123456 --databases mydb > mydb.sql #导出带数据库的备份脚本
mysqldump -uroot -p123456 --databases mydb ad_user > mydb.sql #导出数据库指定
表
mysqldump -uroot -p123456 --all-databases > mydb.sql #导出所有数据库
mysqldump -uroot -p123456 -d mydb > mydb.sql #导出数据库的所有表结构
mysqldump -uroot -p123456 -d mydb ad_user > mydb.sql #导出数据库的某个表结构

–master-data

某个时间全量备份：每天晚上4点-中间12点数据挂了-明天晚上4点之间这段时间就需要时间点恢复
前天晚上4点全量+4点-12点的binlog（如果知道4点备份的那个position）
能够在我们导出数据的时候在我们的脚本⾥带上全量结束的position

–master-data=0|1|2

1：如果主库被删除了，从库也会被删除，拿着备份⽂件去从库告知从库执⾏完从什么位置开始同
步
2：只记录备份的position，可以⽤这个位置快速导出binlog的语句

–flush-logs

在备份的那个时点新建⼀个binlog

其他常⽤选项

--routines ：存储过程
--triggers ： 触发器
--events ： 事件

2.4 不同存储引擎下如何进⾏备份

innodb

热备：需要在mysqldump⾥加⼊⼀个参数：--single-transaction
会基于备份⽣成⼀个独⽴的事务，专⻔进⾏对应时点快照数据处理的

mysqldump -uroot -p123456 --master-data=2 --single-transaction --routines -
-triggers --events --databases mydb > mydb.sql

myisam

温备：因为这个引擎不能⽀持事务，要保证数据⼀致性要锁表：--lock-tables

mysqldump -uroot -p123456 --master-data=2 --lock-tables --routines --
triggers --events --databases mydb > mydb.sql

–lock-all-tables #配置导出所有数据库 --all-databases