6-25日志和备份恢复

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

## 2. 重点参数

innodb_flush_log_at_trx_commit 

作用：控制redo buffer刷写磁盘的策略

0 :  redobuffer---每秒-->os buffer--每秒->磁盘

如果出现宕机，有可能丢失一秒的事务。

如果说你的业务，对数据丢失有一定容忍度，可以使用。

1 : redobuffer---commit-->os buffer--commit->磁盘

2 :redobuffer---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 排错

### 错误日志

### 怎么配置？

```

默认位置:

mysql> select @@log_error;

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

生产要求：日志要和数据分开存放

[root@db01 ~]# mkdir /data/binlog -p

[root@db01 ~]# vim /etc/my.cnf

log_bin=/data/binlog/mysql-bin

server_id=6

### 1.2.3 如何查看配置

mysql> show variables like '%log_bin%';

### 1.2.4  binlog记录了什么?

#### 大面上说明

记录了数据库中所有变更类的操作

DDL

DCL

DML

#### 详细的说明

（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;

+-----------------+

| @@binlog_format |

+-----------------+

| ROW            |

+-----------------+

1 row in set (0.00 sec)

mysql>

### 1.2.5 二进制日志事件（event）

#### 简介

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

对于DDL,DCL,一个语句就是一个event

对于DML语句来讲:只记录已提交的事务。

例如以下列子,就被分为了4个event

    position号码

begin;      120  - 340

DML1        340  - 460

DML2        460  - 550

commit;    550  - 760

#### event的组成

三部分构成:

(1) 事件的开始标识

(2) 事件内容

(3) 事件的结束标识

Position:

开始标识: at 194

结束标识: end_log_pos 254

194? 254?

某个事件在binlog中的相对位置号

位置号的作用是什么？

为了方便我们截取事件

### 1.2.6 二进制日志位置的查看

#### 查看二进制日志所在位置

mysql> show variables like '%log_bin%';

[root@db01 /data/binlog]# ls -l /data/binlog/

总用量 12

-rw-r----- 1 mysql mysql 177 6月  25 09:35 mysql-bin.000001

-rw-r----- 1 mysql mysql 154 6月  25 09:35 mysql-bin.000002

-rw-r----- 1 mysql mysql  60 6月  25 09:35 mysql-bin.index

#### 查看正在使用的二进制日志

mysql> show binary logs;

+------------------+-----------+

| Log_name        | File_size |

+------------------+-----------+

| mysql-bin.000001 |      177 |

| mysql-bin.000002 |      154 |

+------------------+-----------+

2 rows in set (0.00 sec)

mysql>

log_name : 目前MySQL存在的二进制日志名字

file_size: 目前mysql用到哪个position号

mysql> show master status ;

+------------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |      322 |              |                  |                  |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql>

### 1.2.7 查看二进制文件内容 ******

#### 查看二进制日志事件

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

mysql> show binlog events in 'mysql-bin.000002';  ## 查看2号binlog的事件

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

Log_name  ：日志名

Pos        ：事件开始的position *****

Event_type ：事件类型

Server_id  ：发生在哪台机器的事件

End_log_pos：事件结束的位置号  *****

Info      ：事件内容 *****

#### 查看二进制日志内容

[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000002 |grep -v "SET" >/tmp/aa.txt

[root@db01 /data/binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002

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

#### 如何按需截取日志

#### 1. 基于position号的截取 *****

--start-position=###

--stop-position=###

截取二进制日志核心在于，找起点和终点

[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;

+------------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |    2196 |              |                  |                  |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

#2. 查看事件

mysql> show binlog events in 'mysql-bin.000002';

| mysql-bin.000002 |  980 | Query          |        6 |        1096 | create database binlog charset utf8mb4 |

| mysql-bin.000002 | 2098 | Query          |        6 |        2196 | drop database binlog             

980-2098

# 3. 截取日志

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

# 4. 进行恢复

mysql> set sql_log_bin=0;

mysql> source /tmp/bak.sql

# 5. 验证数据

mysql> use binlog

Database changed

mysql> select * from t1;

+------+

| id  |

+------+

|    1 |

|    2 |

|    3 |

+------+

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

#### 思考问题？下面怎么恢复？

#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;

truncate table t1;

insert into t1 values(3);

commit;

#4.

drop database binlog;

基于position号恢复需要多次截取，找起点和终点过程很复杂。

#### 什么是GTID （全局事务编号）

5.6 版本新加的特性,5.7中做了加强

5.6 中不开启,没有这个功能.

5.7 中的GTID,即使不开也会有自动生成

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

是对于一个已提交事务的编号，并且是一个全局唯一的编号。

它的官方定义如下：

GTID = source_id ：transaction_id

7E11FA47-31CA-19E1-9E56-C43AA21293967:29

说明：

DDL DCL，一条语句（事件）就是一个事务，占一个GTID号

DML：一个完整的事务(begin--》commit)，是一个事务，占一个GTID号

####  开启GTID

vim /etc/my.cnf

gtid-mode=on

enforce-gtid-consistency=true

systemctl restart mysqld

#### 查看本机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截取二进制日志

[root@db01 ~]# mysqlbinlog --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' /data/binlog/mysql-bin.000003 >/tmp/gtid.sql

注意： 以上日志截取出来的日志不能直接恢复？为啥呢？

gtid的幂等性？

#### 正确的截取方法是

[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' /data/binlog/mysql-bin.000003 >/tmp/gtids.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 二进制日志其他操作

#### 临时关闭

set sql_log_bin=0;   

说明：

临时关闭二进制日志记录，退出mysql窗口可以恢复

做数据恢复之前，使用以上参数

#### 自动清理

参数：

mysql> select @@expire_logs_days;

设置依据？

至少是一个全备周期+1，企业建议至少2个全备周期+1

怎么设置？

临时设置，重启失效

mysql> set global expire_logs_days=8;

永久设置，重启生效

vim /etc/my.cnf

expire_logs_days=8

#### 手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

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

注意:不要手工 rm binlog文件

1. my.cnf binlog关闭掉,启动数据库

2.把数据库关闭,开启binlog,启动数据库

删除所有binlog,并从000001开始重新记录日志

删除所有binlog，从000001开始（危险！！！！）

mysql> reset master;

#### 日志滚动

重启数据库

flush logs

mysqladmin -uroot -p flush-logs

show variables like '%max_binlog_size%';

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

## 1.3 优化相关日志-slowlog

### 1.3.1 作用

记录慢SQL语句的日志,定位低效SQL语句的工具日志

### 1.3.2 开启慢日志(默认没开启)

开关:

slow_query_log=1

文件位置及名字

slow_query_log_file=/data/mysql/slow.log

设定慢查询时间:

long_query_time=0.1

没走索引的语句也记录:

log_queries_not_using_indexes

vim /etc/my.cnf

slow_query_log=1

slow_query_log_file=/data/mysql/slow.log

long_query_time=0.1

log_queries_not_using_indexes

systemctl restart mysqld

### 1.3.3 mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/data/slow.log

# 1.3.4 第三方工具(自己扩展)

https://www.percona.com/downloads/percona-toolkit/LATEST/

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5

toolkit工具包中的命令:

./pt-query-diagest  /data/mysql/slow.log

Anemometer基于pt-query-digest将MySQL慢查询可视化

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

# 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.压缩比较高，节省备份的磁盘空间

缺点：

4.依赖于数据库引擎，需要从磁盘把数据读出

然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低

建议：

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

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

1EB  =1024 PB = 1000000 TB

## 4.2 xtrabackup(XBK)

优点：

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

缺点：

2.可读性差

3.压缩比低，需要更多磁盘空间

建议：

>100G<TB

# 5.备份策略

## 5.1 备份方式：

全备:全库备份，备份所有数据

增量:备份变化的数据

逻辑备份=mysqldump+mysqlbinlog

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

## 5.2 备份周期:

根据数据量设计备份周期

比如：周日全备，周1-周6增量

其他：通过主从复制备份

# 6. 逻辑备份工具-mysqldump

# 6.1 客户端通用命令,和链接有关

-u

-p

-S

-h

-P

本地备份连接方式：

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

远程备份的连接方式：

mysqldump -uroot -pxxx -h xxx  -P xxx 

# 6.2 基本备份参数

-A  全库备份 

例子：实现全库备份

[root@db01 ~]# mkdir -p /data/backup

[root@db01 ~]#  mysqldump -uroot -p123 -A -S /tmp/mysql.sock >/data/backup/full.sql

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

例子: 备份oldboy和world数据库

[root@db01 ~]#  mysqldump -uroot -p123 -B world oldboy  -S /tmp/mysql.sock >/data/backup/db.sql

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

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

[root@db01 ~]# 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

Creates a consistent snapshot by dumping all tables in a

single transaction. Works ONLY for tables stored in

storage engines which support multiversioning (currently

only InnoDB does); the dump is NOT guaranteed to be

consistent for other storage engines. While a

--single-transaction dump is in process, to ensure a

valid dump file (correct table contents and binary log

position), no other connection should use the following

statements: ALTER TABLE, DROP TABLE, RENAME TABLE,

TRUNCATE TABLE, as consistent snapshot is not isolated

from them. Option automatically turns off --lock-tables.

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

# 6.5 其他(不是必加的，了解下功能即可)

-F

--set-gtid-purged=auto

auto , on

off

使用场景:

1. --set-gtid-purged=OFF,可以使用在日常备份参数中.

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

2. auto , on:在构建主从复制环境时需要的参数配置

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --max-allowed-packet=128M --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

--max-allowed-packet=# 

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

推荐阅读更多精彩内容