起因：在工作中常常要用到mysql，平常只是对数据库crud，并没有认真的了解过她，sql语句也只是会一些最基本的，和常用的，一些不常用的都要去网上百度，即决定学习一下mysql，来了解她，虽然开发很多都是黑盒，但追本溯源总是我们想要的。
1. Binlog日志深入分析
1.1. Binlog记录模式及参数配置
DDL：全部记录，定义语言
DML：除select以外都会记录
1log_bin=mysql-bin
2binlog_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
先查看了了一下日志
mysql> show binlog events in 'mysql-bin.000001';
mysqlbinlog --start-position=100 --stop-position=120 --database=mydb mysql- bin.000001 > mysql.sql
statement里面只有操作语句是非注释的，其他的说明都是#注释的
看我们的binlog日志大小
1show variables like '%binlog_size%'; #如果一个事务超过binlon大小不不会写入下一个
2max_binlog_size=1024m #每个binlog日志文件大小
3expire_logs_days=7 #binlog的过期时间
4binlog_cache_size=32768 DML操作不不频繁 <=1m, DML频繁且事务大 2-4m
5max_binlog_cache_size 32位4G，64位16P
1mysql> flush logs; #生成一个新binlog
2mysql> 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日志的正确打开方式
1mysql> show binlog events in 'mysql-bin.000002'
2mysql> 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
数据库备份优化相关，请移步我的下一篇博客MySQL数据库备份优化
不要以为每天把功能完成了就行了，这种思想是要不得的，互勉~！
网友评论