day09-MySQL-日志管理

day09-MySQL-日志管理

mysqlbinlog和mysqldump：逻辑损坏的数据恢复

一、日志介绍及管理

MySQL日志简介

1. 错误日志

1. 作用

记录启动\关闭\日常运行过程中，状态信息，警告信息，错误信息

2. 配置

默认就是开启的，在此路径下：/数据路径下/hostname.err

//手动设定，修改配置文件：

vim /etc/my.cnf

log_error=/var/log/mysql.log

重启生效

//查看设置的结果

show variables like 'log_error';

3. 日志内容查看

主要关注错误日志中有[error]的部分，看上下文

---

2. binlog：二进制日志 *****

1. 作用

(1) 备份恢复必须依赖二进制文件

(2) 主从环境必须依赖二进制文件，必须开启

注意：MySQL默认是没有开启二进制日志的。

2. binlog配置

(5.7必须加server_id，binlog日志才能使用)

//创建配置文件所需路径及权限

mkdir -p /data/mysql

chown -R mysql.mysql /data/mysql

//修改配置文件

vim /etc/my.cnf

[mysqld]

server_id=6 //5.7必须加server_id（5.6不用，加了起不来）

log_bin=/data/mysql/mysql-bin //既是设置二进制日志的路径，又是打开二进制开关

binlog_format=row //binlog记录格式

重启数据库生效

//配置成功后，会生成两个文件

[root@mysql-db01~]# cd /data/mysql/

[root@mysql-db01mysql]# ls

mysql-bin.000001 mysql-bin.index

[root@mysql-db01mysql]# cat mysql-bin.index

/data/mysql/mysql-bin.000001 //一共有多少个mysql-bin文件在使用

//查看binlog_format记录格式

注意:

log bin路径=/data/mysql/mysql-bin

/data/mysql/ 存放二进制日志的目录，必须创建好，必须对mysql有权限

mysql-bin 日志文件名字的前缀

3. binlog记录了什么?

(1）引入

binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。

(2）记录SQL语句种类

DDL：原封不动的记录当前DDL。

DCL：原封不动的记录当前DDL。

DML：只记录已经提交的事务DML

(3) 记录方式

三种记录方式：受binlog_format (binlog的记录格式) 参数影响

1) statement (5.6默认)： 语句模式原封不动的记录当前DML。

2) ROW (5.7默认值)： 记录数据行的变化 (用户看不懂，需要工具分析)

3) mixed (混合)模式： 以上两种模式的混合

statement与ROW模式的对比：

statement：可读性较高，日志量少，但是不够严谨，不能细节到行的变化

例子：id name intime

insert into t1 values(1,'zs',now())

ROW：可读性很低，每行的变化都会记录，日志量很大，足够严谨

我们建议：使用row记录模式

4. event (事件) 是什么?

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

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

对于DML语句来讲：例如以下列子，就被分为了4个event（是一个事务，但是由4个事件组成）

begin;

DML1

DML2

commit;

event的组成：

position号码：位置号码，某个事件在binlog文件中的相对位置号

开始位置号 (#at 120)

事件内容

结束位置号 (下一个#at 340，或者是end log_pos 340)

例子说明：

DML事务（4个事件） position位置号

begin; 120 - 340

DML1 340 - 460

DML2 460 - 550

commit; 550 - 760

位置号的作用是什么?

为了方便我们截取事件

5. binlog文件查看

log_bin参数设置的路径，可以找到二进制日志

show variables like 'log_bin%';

show binary logs; //查看数据库有效的二进制日志

flush logs; //刷新出一个新的二进制文件

show master status; //MySQL正在用的二进制文件只有一个，之前的已成为历史，用此命令查看

file：当前MySQL正在使用的文件名

Position：最后一个事件的结束位置号

6. binlog内容查看

(1）event查看

//查看正在使用的二进制文件

show master status;

//查看此二进制文件的event

show binlog events in 'mysql-bin.000004';

注释：

Log_name： binlog文件名

Pos： 事件的开始位置号

Event_type： 事件类型

Format_desc： 格式描述，每一个日志文件的第一个事件，多用户没有意义，MySOL识别binlog必要信息

5.6 4-120

5.7 4-154

Server_id: mysql服务号标识

End_log_pos： 事件的结束位置号

Info： 事件内容

例子：

//录入数据，看事件信息

create database testdb;

use testdb;

create table t1(id int);

insert into t1 values(1);

commit;

show binlog events in 'mysql-bin.000004';

(2）binlog文件内容详细查看

[root@mysql-db01~]# mysqlbinlog /data/mysql/mysql-bin.000006;

// mysqlbinlog -d ：过滤库查看

[root@mysql-db01~]# mysqlbinlog -d testdb /data/mysql/mysql-bin.000006;

7. binlog截取 (恢复)

1.数据损坏

1) 物理损坏

硬盘损坏，文件系统损坏，数据文件损坏 (ibd) rm

2) 逻辑损坏

drop truncate delete update

2.数据恢复，截取二进制日志

1）截取开始位置号120到结束位置号513的内容

[root@mysql-db03~]# mysqlbinlog --start-position=120 --stop-position=513 /data/mysql/mysql-bin.000006 >/tmp/binlog.sql

2）数据恢复

1.方法一

下面使用 root 用户恢复所有数据库

mysql> mysql -u root -p < /tmp/binlog.sql

2.方法二

mysql> set sql_log_bin=0; //关闭二进制日志的生成

mysql> source /tmp/binlog.sql //恢复数据库

mysql> set sql_log_bin=1; //开启二进制日志的生成

//查看是否恢复

3）日志恢复案例

逻辑损坏

SQL导致的误删除，误修改

只要你拥有全量的binlog，可以把数据库恢复到任意的时间点

故障案例模拟：

//运行以下语句，模拟故障场景

(0) oldguo [testdb]>drop database if exists db ; //如果db存在就删除

(1) oldguo [testdb]>create database db charset utf8; //创建db库

(2) oldguo [testdb]>use db;

(3) oldguo [db]>create table t1 (id int);

(4) oldguo [db]>insert into t1 values(1),(2),(3);

(5) oldguo [db]>insert into t1 values(4),(5),(6);

(6) oldguo [db]>commit；

(7) oldguo [db]>update t1 set id=30 where id=3; //修改id=3为id=30

(8) oldguo [db]>commit;

(9) oldguo [db]>delete from t1 where id=4; //删除id=4

(10) oldguo [db]>commit;

(11) oldguo [db]>insert into t1 values(7),(8),(9); //新增行数据

(12) oldguo [db]>commit;

(13) oldguo [db]>drop table t1; //删除t1表

需求：将数据库恢复到以下状态 (提示第9步和第13步是误操作，其他都是正常操作)

//进行恢复，恢复完的各行结果如下：

1

2

30

4

5

6

7

8

9

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

恢复过程：

1.查看当前使用的 binlog文件

oldguo [db]>show master status;

2.查看事件：

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

#第一段：

//截取第一段

# mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql

#第二段：

//截取第二段

# mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql

//数据恢复

mysql> set sql_log_bin=0; //关闭二进制日志的生成

mysql> source /tmp/bin1.sql //恢复数据库

mysql> source /tmp/bin2.sql //恢复数据库

mysql> set sql_log_bin=1; //开启二进制日志的生成

//查看

select * from t1;

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

8. binlog日志其他操作

1. 自动清理binlog日志（默认不自动清零）

show variables like '%expire%';

expire_logs_days

//0代表永不过期，一直可以存

设置自动清理时间，是要按照全备周期+1

//例如一周做一次全备份，就是7+1，8天

(1) 临时生效：

set global expire_logs_days=8;

(2) 永久生效：

vim /etc/my.cnf

expire_logs_days=8;

企业建议，至少保留两个全备周期+1的binlog

2. 手工清理

purge binary logs before now() - interval 3 day;

purge binary logs to 'mysql-bin.000010';

注意：不要手工 rm binlog文件

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

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

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

mysql> reset master;

4. 日志是怎么滚动

(1) flush logs;

(2) 重启mysql也会自动滚动一个新的

日志存储空间大小(max_binlog_size)，日志文件达到1G大小

备份时，加入参数也可以自动滚动

---

3. slow_log 慢日志

1. 作用：

记录运行较慢的SQL语句的日志

slow_query_log=1 #是否启用慢查询日志，1为启用，0为禁用 slow_query_log_file=slow.log #指定慢查询日志文件的路径和名字，可使用绝对路径指定；默认值是'主机名_slow.log'，位于datadir目录 long_query_time=2 #SQL语句运行时间阈值，执行时间大于参数值的语句才会被记录下来 min_examined_row_limit=100 #SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值 log_queries_not_using_indexes=1 #将没有使用索引的语句记录到慢查询日志 log_throttle_queries_not_using_indexes=10 #设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间 log-slow-admin-statements=1 #记录执行缓慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。 log_slow_slave_statements=0 #记录从库上执行的慢查询语句 log_timestamps=system #5.7版本新增时间戳所属时区参数，默认记录UTC时区的时间戳到慢查询日志，应修改为记录系统时区 log_output=FILE,TABLE #指定慢查询日志的输出方式，从5.5版本开始可以记录到日志文件(FILE，慢查询日志)和数据库表(TABLE，mysql.slow_log)中

2. 开启慢日志 (默认没开启 )

(1) 开关：

slow_query_log=1

(2) 文件位置及名字

slow_query_log_file=/data/mysql/slow.log

(3) 设定慢查询时间：SQL语句运行时间阈值，执行时间大于参数值的语句才会被记录下来

long_query_time=0.1

(4) 没走索引的语句也记录：

log_queries_not_using_indexes

(5) 配置慢日志时间

# 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

# 重启生效

[root@mysql-db01~]# /etc/init.d/mysqld restart

(6) mysqldumpslow 分析慢日志工具

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

-s ---sort排序

c ---语句执行的次数

-t 10 ---显示前十条

pt-query-diagest 第三方分析慢日志工具

下载地址：

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工具包中的命令：

---

二、备份恢复 (重点内容)

运维的备份恢复相关的职责

1. 设计备份策略

2. 日常备份

3. 恢复演练

4. 故障恢复

5. 迁移

1.备份类型

热备

在数据库正常运行时，备份数据，并且能够一致性恢复（innodb）

温备

锁表备份，只能查询不能修改（myisam）

冷备 (不推荐)

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

2.备份方式及工具介绍

(1) 逻辑备份工具：基于SQL语句进行备份

mysqldump

mysqlbinlog

(2) 物理备份工具：基于磁盘数据文件备份

xtrabackup (XBK)：percona公司，第三方工具 *****

MySQL Enterprise Backup (MEB)

(3) 逻辑备份和物理备份的比较

mysqldump

优点：

1.不需要下载安装

2.备份出来的是SQL，文本格式，可读性高，便于对备份进行二次处理

3.压缩比较高，节省备份的磁盘空间

缺点：

1.依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较耗费资源，

数据量大的话转储效率较低

2.不支持增量备份

建议：

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

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

xtrabackup(XBK) -- 第三方物理备份工具

优点：

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

缺点：

1.可读性差

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

建议：

>100G，

3.备份策略

全备：全库备份

增量：备份变化的数据

逻辑备份=mysqldump (全备）+ mysqlbinlog（增量） //mysqldump不支持增量

物理备份=xtrabackup

备份周期：根据数据量设计备份周期

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

4.备份工具使用

1. mysqldump (逻辑备份工具)

(1) 通用参数

-u 用户

-p 密码

-S 远程

-h 主机名

-P 端口

如：

mysqldump -uroot -p

(2) 备份专用基本参数

1) -A 全备参数

例子1：全备

//创建备份目录

[root@mysql-db01~]# mkdir /bak

[root@mysql-db01~]# chown -R mysql.mysql /bak

//添加mysqldump配置文件（登录数据库认证文件）

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

[mysqldump]

socket = /opt/mysql.sock

//全备

[root@mysql-db01~]# mysqldump -uroot -p -A >/bak/full.sql

Enter password:

2) -B db1 db2 db3 备份多个单库

例子2：备份单库或多库（比较适合于做跨版本的迁移，只备份生产库）

[root@mysql-db01~]# mysqldump -uroot -p -B oldboy world >/backup/bak.sql

Enter password:

3) 备份单个或多个表 //什么参数都不加

例子3：world数据库下的 world，city表

[root@mysql-db01~]# mysqldump -uroot -p world city >/bak/city.sql

Enter password:

以上备份恢复时：必须库事先存在，并且use到数据库才能source恢复

注意：以下两个语句都是可以备份world数据库下所有表

1.mysqldump -uroot -p -B world >/bak/oldboy.sql

2.mysqldump -uroot -p world >/bak/oldboy1.sql

//比较：

-B 比不加参数的多了create database 和 use语句

结论：

备份库时加-B

备份表时不加参数

4) 特殊参数1使用（备份必加参数）

-R 备份存储过程及函数

--triggers 备份触发器

例子：

mysqldump -uroot -p -A -R --triggers >/bak/full.sql

5) 特殊参数2使用

++++++++++++++++++++

每天晚上12点全备

上午10点钟，数据库被误删除了

二进制日志截取将成为我们比较棘手的问题

起点：怎么办

终点：好找

6) -F 在备份开始时，刷新一个新binlog日志

例子：

mysqldump -uroot -p -A -R --triggers -F >/bak/full.sql

7) --master-data=2

以注释的形式，保存备份开始时间点的 binlog的状态信息

0 默认值

1 以change master to命令形式，可以用作主从复制

2 以注释的形式记录，备份时刻的文件名+postion号

master-data可以自动加锁

1) 不加--single-transaction，启动所有表的温备份，所有表都锁定

2) 加上--single-transaction，对innodb可以不锁表热备，对非innodb表可以实现自动锁表功能

(3）备份必加参数最终版 *****

mysgldump -uroot -p -A -R --triggers --master-data=2 --single-transaction >/bak/full.sql

功能：

(1) 以注释的形式，保存备份开始时间点的 binlog的状态信息

(2) 对innodb可以不锁表热备

(3) 对非innodb表可以实现自动锁表功能

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

小练习:

1，实现所有表的单独备份

提示:information_schema.tables

2.模拟故障案例并回复

(1) 每天全备

(2) binlog日志是完整

(3) 模拟白天的数据变化

(4) 模拟下午两点误删除数据库

需求: 利用全备+binlog恢复数据库误删除之前。

---

2. XBK (物理备份工具)：重点讲解

---------MEB(MySQL Enterprise Backup)：自己了解

(1) XBK安装

//安装依赖包

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

//下载Percona XtraBackup安装包

Percona XtraBackup是一款基于MySQL的服务器的开源热备份实用程序，在备份过程中不会锁定数据库。

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

//XBK安装

yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

备份命令：

xtrabackup

innobackupex ******

(2) 备份实现原理

1) 非innodb引擎，锁定表，直接拷贝表的数据文件

2) Innodb引擎备份过程

数据+日志

将内存数据页刷写到磁盘拷贝；

在备份开始的时刻(10:00开始，备份结束10:02)，立即触发checkpoint动作，此时所有的数据文件

(LSN=1000)在此过程中产生的日志文件(to_lsn=1000 redo last_lsn=1020 和undo)也进行备份。

支持热备：备份某时间点的数据，将备份过程中产生的变化也备份出来

(3) 备份命令简单使用及备份内容介绍

XBK的备份依赖于/etc/my.cnf文件里面的【mysqld】相关配置文件

//创建备份目录/bak/

[root@mysql-db03~]# mkdir -p /bak

//默认全备：

[root@mysql-db03~]# innobackupex --user=root --password=3306 /bak

OK！

//注释：

xtrabackup_binlog_info：备份时间点二进制日志的信息（类似mysqldump中--master-data=2）

xtrabackup_checkpoints:

[root@mysql-db032023-02-18_15-38-08]# cat xtrabackup_checkpoints

backup_type = full-backuped 默认全备

from_lsn = 0 全备，lsn是从0开始备份的，增量另说。

to_lsn = 214180776 checkpoint时刻的lsn

last_lsn = 214180776 备份结束时的lsn

xtrabackup_info:详细显示

xtrabackup_logfile: 存储着备份过程中产生的redo日志文件

(4) 全备与恢复

1) 数据库全备

//添加数据库密码3306

[root@mysql-db03~]# mysqladmin -uroot -p password 3306

//备份到/bak/full，不加时间戳目录，full目录会自动生成

[rootadb03 bak]# innobackupex --user=root --password=3306 --no-timestamp /bak/full

2) 数据库恢复

1. 前提

恢复的路径必须是空的，mysql需要关闭

1) 停数据库

# pkill mysqld

# ps -ef | grep mysqld

2) 模拟条件：删数据

# rm -rf /application/mysql/data/*

3) 准备备份 (使用redo前滚，使用undo回滚，保证数据一致性)

# innobackupex --apply-log /bak/full/

230218 16:08:18 completed OK!

//注释：

--apply-log --将redo和undo的数据进行应用

2.恢复数据

1）恢复

//方法一：

cd /bak/full

cp -a * /application/mysql/data/

//方法二：

innobackupex --copy-back /bak/full/

会报错：

//解决办法：上面方法依赖于下面功能

[root@mysql-db03~]# vim /etc/my.cnf

//查看

3）改归属权限，起数据库

chown -R mysql.mysql /application/mysql/data/*

/etc/init.d/mysqld start

---

(5) 增量备份与恢复

1. 增量备份介绍

基于上一次备份的增量备份，优点是节约磁盘空间和时间

局限性: 依赖于全备和上次备份，每次备份都有LSN的连续性

XBK工具，需要将需要的增量合并到全备中来恢复

2. 增量备份策略设计及实现

备份策略: 每周日全备，周一到周六是增量

(1) 全备

//清空之前的备份数据

rm -rf /bak/*

//周日全备

innobackupex --user=root --password=123 --no-timestamp /bak/full

(2) 模拟数据

create database full charset utf8;

use full;

create table t1(id int);

insert into t1 values(1),(2),(3);

commit;

(3) 周一的增量备份

innobackupex --user=root --password=3306 --no-timestamp --incremental --incremental-basedir=/bak/full /bak/inc1

//注释

--incremental： 打开增量备份的功能

--incremental-basedir=： 上次备份的路径，full基于全备

(4) 周二的数据模拟

create database inc1 charset utf8;

use inc1;

create table t2(id int);

insert into t2 values(1),(2),(3);

commit;

(5) 周二的增量备份

innobackupex --user=root --password=3306 --no-timestamp --incremental --incremental-basedir=/bak/inc1 /bak/inc2

//注释

--incremental-basedir=/bak/inc1： 基于周一的增量做备份

(6) 查看备份文件，分析三者的联系于区别

一定要保证每次增量LSN跟上次是连续的

3. 全备+增量的恢复（生产要求）

(1) 准备备份

--apply-log (redo+undo)

--redo-only (redo)

--redo-only (redo) 什么时候用？

1）full backup

2）除了最后一次增量不需要加redo-only

//全备准备:（在生产中一定要按照这个顺序进行恢复，否则会出问题）

[root@mysql-db03~]# innobackupex --apply-log --redo-only /bak/full/

//第一次增量合并准备:

[root@mysql-db03~]# innobackupex --apply-log --redo-only --incremental-dir=/bak/inc1 /bak/full

注释：

--incremental-dir=：要恢复的增量文件所在的目录，跟备份的参数不一样

//第二次增量合并准备:（第二次不加redo-only）

[root@mysql-db03~]# innobackupex --apply-log --incremental-dir=/bak/inc2 /bak/full

//全备再次准备:

[root@mysql-db03~]# innobackupex --apply-log /bak/full/

(2) 模拟条件：删数据

[root@mysql-db03~]# rm -rf /application/mysql/data/*

[root@mysql-db03~]# pkill mysqld

[root@mysql-db03~]# ps -ef |grep mysqld

(3) 恢复数据(同之前的步骤)

//方法一：

[root@mysql-db03~]# cd /bak/full

[root@mysql-db03~]# cp -a * /application/mysql/data/

//方法二：

[root@mysql-db03~]# innobackupex --copy-back /bak/full/

(4）改归属权限，起数据库

[root@mysql-db03~]# chown -R mysql. /application/mysql/data/*

[root@mysql-db03~]# /etc/init.d/mysqld start

---

3. xtrabackup 全备+增量+binlog故障案例演练 (小项目)

------（可以写进简历）

背景:

某大型网站，mysql数据库，数据量500G，每日更新量100M-200M

备份策略:

xtrabackup，每周日23:00进行全备，周一到周六23:00进行增量备份。

故障场景:

周三下午3点出现数据库意外删除表操作。

思路:

1.确认备份周日full+周一inc1+周二inc2

2.进行备份准备

3.找测试库，使用备份恢复数据，目前数据状态: 周二晚上23:00时间点

4.截取周二备份时间点到周三下午误删除之前二进制日志

起点:备份中会记录

终点:分析日志文件事件

5.恢复二进制日志，导出故障表，导入回生产库

恢复结果:

花了多长时间恢复业务?