2019-06-25 Day09～Mysql~日志管理及恢复

1. 上节遗留问题

RC模式: 可以屏蔽脏读,但是会出现不可重复读和幻读

2.重点参数 innodb_flush_log_at_trx_commit

作用： 控制redo buffer 刷写磁盘的策略
0：   redo buffer -----> 每秒 -----> os buffer -----> 每秒 -----> 磁盘
       如果 出现宕机，有可能丢失一秒的事务。
       如果你的业务，对数据丢失有一定的容忍度，可以使用（类似zabbix）

1： redo buffer -----> commit -----> os buffer -----> commit -----> 磁盘
    #工作中重点使用

2： redo buffer -----> commit -----> os buffer -----> 每秒 -----> 磁盘

innodb_flush_method=O_DIRECT
作用: 
控制 redo buffer 和 data buffer 刷磁盘时是否使用 os buffer 数据刷盘,不使用 os buffer;；日志刷盘,使用 os buffer
innodb_buffer_pool_size   50-80%

===================================

1、日志管理

1.1 排错

1.1.1 错误日志

现位置：

image.png
默认位置：

DATADIR/hostname.err

配置方式：

vim /etc/my.cnf

log_error=/data/mysql/data/mysql.log

使用方法：

查看 [ ERROR ] 上下文

1.2 数据恢复

binlog(二进制日志)

1.2.1 作用：

数据恢复
主从复制

1.2.2 如何配置？

log_bin
1. 开关
2. 设定存放位置

server_id
1. 5.6 中不需要
2. 5.7 使用以上参数时必须加上server_id

注意：
生产要求！！！
日志要和数据分开存放！
使用不同的物理磁盘进行存放！


配置二进制日志：

mkdir /data/binlog -p        #创建存放目录
chown -R mysql. /data     #授权
vim /etc/my.cnf                 #编辑配置文件
log_bin=/data/mysql/data/mysql-bin
 /etc/init.d/mysqld restart     # 重启
image.png
1.2.3 如何查看配置
mysql> show variables like '%log_bin%';
image.png
1.2.4 binlog 记录了什么？
###1. 大面上说明：
记录了数据库中所有变更类的操作
DDL
DCL
DML

###2. 详细的说明
（1）
对于DDL和DCL语句，记录发生过的语句
（2）DML（IUD）
前提：已经提交的事务IUD

关于记录格式：
ROW         ：RBR     行记录模式 ，记录的是行的变化
STATEMENT   ：SBR     语句记录模式，记录操作语句本身
MIXED       ：MBR     混合记录模式

电话面试的题目：
delete from city where id>1000；
记录方式：
RBR,逐行记录日志,日志量很大,可读性差。但是够严谨,不会出现记录错误
SBR,只记录语句本身, 日志量很少,可读性较强。对于函数类的操作,将来恢复时会有错误

5.7 版本，默认是RBR格式，也是企业建议的模式。
二进制日志记录格式查看

mysql> select @@binlog_format;

image.png

1.2.5 二进制日志事件（event）

简介
二进制日志的最小记录单元

对于DDL, DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
           position号码（截取日志使用）
beqin;     120  -  340
DML1       340  -  460
DML2       460  -  550
commit;    550  -  760

### event的组成
三部分构成:
(1)事件的开始标识
(2)事件内容
(3)事件的结束标识

Position:
开始标识: at 194
结束标识: end_log_pos 254 1
194?   254?
某个事件在binlog中的相对位置号
位置号的作用是什么?
为了方便我们截取事件

1.2.6 二进制日志的查看

image.png

查看正在使用的二进制日志
mysql> show binary logs;

image.png
log_name：目前Mysql存在的二进制日志名字
file_size：目前Mysql用到哪个polation号

mysql> show master status ;

image.png

1. 2. 7 查看二进制文件内容*****

（1）查看二进制日志事件

确认当前在用的binlog
mysql> show master status ;

image.png

查看binlog事件
mysql> show binlog events in 'mysql-bin.000001';

image.png

注释：每一行都是一个事件

每列说明：

Log_name      ：日志名        
Pos           ：事件开始的position   *****
Event_type    ：事件类型
Server_id     ：发生在那台机器上的事件
End_log_pos   ：事件结束的位置号      *****
Info          ：事件内容             *****
（2）查看二进制日志内容
平看日志信息：
mysqlbinlog /data/binlog/mysql-bin.000001 |grep -v "SET"
详细查看日志信息
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000001

1.2.8 基于二进制日志数据恢复案例

如何按需截取日志

1. 基于position号的截取

--start-position=###
--stop-position=###
截取二进制日志核心在于找起点和终点
image.png

截取日志

[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=322 /data/binlog/mysql-bin.000002 >/tmp/bin.sql

恢复：

mysql> drop database oldboy1;
mysql> show databases;
mysql> set sql_log_bin=0;
mysql>source /tmp/bin.sql

2. 基于时间点的截取（需求不大，了解即可）

--start-datetime
--stop-datetime
for example: 2004-12-25 11:25:56

《按需恢复详解》

案例: 使用binlog日志进行数据恢复
模拟:
#1. 
create database binlog charset utf8mb4;
#2. 
use binlog;
create table t1(id int);
#3. 
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
#4. 
drop database binlog;

恢复：

1.找到起点和终点
mysql> show master status;

2.查看事件
mysql> show binlog events in 'mysql-bin.000001';

3. 截取日志
mysqlbinlog --start-position=259 --stop-position=1377 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

 4. 进行恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql

 5. 验证数据
mysql> show databases;

1.2.9 开启GTID功能的二进制日志管理

什么是GTID

全局事务编号

开启GTID

vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
/etc/init.d/mysqld restart

查看本机GTID信息

环境模拟：

mysql> create database gg;
mysql> show master status;
mysql> use gg;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t1 values(2);
mysql> commit;
mysql> insert into t1 values(3);
mysql> commit;
drop database gg;

基于GTID截取二进制日志

mysqlbinlog --include-gtids='1357a112-8d94-11e9-8bda-000c29c21dbb:7-11' /data/binlog/mysql-bin.000004 >/tmp/gtid.sql


注意：
以上日志截取出来的日志不能直接恢复！

gtid的幂等性

正确的截取方式：

mysqlbinlog --skip-gtids --include-gtids='1357a112-8d94-11e9-8bda-000c29c21dbb:7-11' /data/binlog/mysql-bin.000004 >/tmp/gtid.sql

恢复

mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql

跳过某些gtid不截取（不连续取号或排除）


mysqlbinlog --skip-gtids --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' --exclude-gtids='ee956c61-9653-11e9-8518-000c29099eb6:2,ee956c61-9653-11e9-8518-000c29099eb6:4' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql

1.2.10 二进制日志其他操作

（1）临时关闭

set sql_log_bin=0;
说明：
临时关闭二进制日志记录，退出mysql窗口可以恢复
做数据恢复之前，使用以上参数。

（2）自动清理

参数：
mysql> select @@expire_logs_days;

设置依据？
至少是一个全备周期 +1，企业建议至少2个全被周期 +1

临时设置：重启失效
mysql> set global expire_logs_days=8';

永久设置：重启生效
vim /etc/my.cnf 
expire_logs_days=8

（3）手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010';

注意:不要手工rm binlog文件
1. my.cnf binlog关闭掉,启动数据库 
2. 数据库关闭,开启binlog,启动数据库
删除所有binlog，并从000001开始重新记录日志

注意，禁用：
删除所有binlog，从000001开始删除（危险！！！！！）
mysql>reset master;

（4）日志滚动

mysql> flush logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |      1498 |
| mysql-bin.000004 |      1451 |
| mysql-bin.000005 |       194 |
+------------------+-----------+

mysql> show variables like '%max_binlog_size%';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+

备份加一些参数，会触发滚动日志

1. 3 优化相关日志— slowlog

1.3.1 作用
记录慢SQL语句的日志，定位低效SQL语句的工具日志。
1.3.2 开启慢日志，默认没有开启
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> select @@slow_query_log_file;
+--------------------------------+
| @@slow_query_log_file          |
+--------------------------------+
| /data/mysql/data/db01-slow.log |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)

mysql>  select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.00 sec)

最终设置：

slow_query_log=1 
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
1.3.3 mysqldumpslow 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log
image.png

备份恢复与迁移 ******

1、DBA、运维在数据库备份恢复方面的职责

1.1 设计备份策略

全备
增量
时间
自动

1.2 日常备份检查!

备份存在性—检查备份路径下是否有真实数据
定期检查备份空间是否充足

1.3 定期恢复演练（测试库）

一季度 或者 半年

1.4 故障恢复

通过现有备份,能够将数据库恢复到故障之前的时间点.

1.5 迁移
  1. 停机时间
  2. 回退方案

2、备份类型

2.1 热备

在数据库正常业务时，备份数据,并且能够一致性恢复(只能是innodb)
对业务影响非常小

2.2 温备

锁表备份，只能查询不能修改(myisam)
影响到写入操作

2.3 冷备

关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.业务停止

3、备份方式及工具介绍

3.1 逻辑备份工具

基于SQL语句进行备份
mysqldump(MDP) *****
mysqlbinlog *****

3.2 物理备份工具

基于磁盘数据文件备份
xtrabackup (XBK) : percona第三方 *****
MysQL Enterprise Backup (MEB,企业版收费的)

4、逻辑备份和物理备份的比较

4.1 mysqldump (MDP)

优点:

  1. 不需要下载安装.
  2. 备份出来的是SQL,文本格式,可读性高,便于备份处理
  3. 压缩比较高,节省备份的磁盘空间

缺点

  1. 依赖于数据库引擎,需要从磁盘把数据读出
    然后转换成SQL进行转储,比较耗费资源,数据量大的话效率较低

建议:

100G以内的数据量级,可以使用mysqldump

超过TB以上,我们也可能选择的是mysqldump,配合分布式的系统

1EB =1024 PB =1000000 TE

4.2 xtrabackup (XBK)

优点:

  1. 类似于直接cp数据文件,不需要管逻辑结构,相对来说性能较高

缺点:

  1. 可读性差
  2. 压缩比低,需要更多磁盘空间

建议:

100GKTB

5、备份策略

备份方式:

全备：全库备份,备份所有数据
增量：备份变化的数据

逻辑备份=mysqldump+mysqlbinlog

物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog

备份周期:

根据数据量设计备份周期
比如：周日全备,周1 - 周6增量
其他：通过主从复制备份

6、逻辑备份工具-mysqldump

6. 1 客户端通用命令，和链接有关

-u
-p
-S
-h
-P

本地备份连接方式：

mysqldump -uroot -pxxx -S /tmp/myql.sock

远程备份连接方式：

mysqldump -uroot -pxxx -h xxx -P xxx

6.2 基本备份参数

-A 全库备份

例子：实现全库备份

mkdir -p /data/backup
mysqldump -uroot -p123 -A -S /tmp/mysql.sock >/data/backup/full.sql

-B 备份单个库或多个库数据

例子：备份oldboy和world数据库

mysqldump -uroot -p123 -B world oldboy -S /tmp/mysql.sock >/data/backup/db.sql

库名 表名 ： 备份某个库下的1张或多张表

例子：备份world数据库下的city和country表

mysqldump -uroot -p123 world city country -S /tmp/mysql.sock >/data/backup/tab.sql

注意： 
此种方法，只会备份建表 + 插入语句
所以恢复前需要把库建好，而且要use到库中。
6. 3 必加参数 （1）

-R
在备份时，同时备份存储过程和函数，如果没有会自动忽略

-E
在备份时，同时备份EVENT，如果没有会自动忽略

--triggers
在备份时，同时备份触发器，如果没有会自动忽略

6. 4 必加参数（2）重要参数

--master-data=2
记录备份开始时 position号，可以作为将来做日志截取的起点。

功能：

  1. 记录备份时的position
  2. 自动锁表
  3. 配合--single-transaction，减少锁的（innodb引擎）

--single-transaction
对于innodb的表，实现快照备份，不锁表

6.5 其他参数（不是必加，了解即可）

-F

--set-gtid-purged=auto

--max-allowed-packet

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 196,165评论 5赞 462
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,503评论 2赞 373
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 143,295评论 0赞 325
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,589评论 1赞 267
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,439评论 5赞 358
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,342评论 1赞 273
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,749评论 3赞 387
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,397评论 0赞 255
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,700评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,740评论 2赞 313
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,523评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,364评论 3赞 314
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,755评论 3赞 300
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,024评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,297评论 1赞 251
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,721评论 2赞 342
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,918评论 2赞 336