第八章· MySQL日志管理—–二进制日志

Posted by

二进制日志

作用:记录以提交的DML事务语句,并拆分为多个事件(event)来进行记录

记录所有DDL、DCL等语句

总之,二进制日志会记录所有对数据库发生修改的操作

二进制日志模式:
statement:语句模式(默认模式)

row:行模式,即数据行的变化过程

mixed:以上两者的混合模式

企业推荐使用row模式

优缺点:

statement模式

优点:简单明了,容易被看懂,就是SQL语句,记录时不需要太多的磁盘空间

缺点:记录不够严谨

row模式:

优点:记录更加严谨

缺点:有可能会需要更多的磁盘空间,不太容易被看懂

binlog的作用

1)如果我拥有数据库搭建开始所有的二进制日志,那么可以把数据恢复到任意时刻

2)数据的备份恢复

3)数据 的复制

二进制日志的管理操作

开启方式

MySQL5.6开启方式

[root@db03 ~]# vim /etc/my.cnf
[mysqld] 
log-bin=mysql-bin
binlog_format=row

[root@db03 ~]# systemctl restart mysqld.service 

在MySQL5.7中开启还必须加server_id

[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
server_id=1

二进制日志的操作

[root@db03 ~]# ll /application/mysql/data/mysql-bin*
-rw-rw---- 1 mysql mysql 120 Mar 27 00:56 /application/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 Mar 27 00:56 /application/mysql/data/mysql-bin.index

#查看有多少binlog
mysql> show binary logs;
 Log_name记录binlog日志名   File_size这个大小其实是日志文件位置点 

#查看最新binlog(和位置点)
mysql> show master status;

#查看binlog日志事件
mysql> show binlog events in 'mysql-bin.000002';

在开启binlog日志后,不要去修改日志文件的位置

MySQL5.6中每个binlog的起始位置点是120

当使用show binary logs;在MySQL5.6中看到结束位置点是143则说明该日志文件并没有记录内容 (行级167结束)

binlog如同磁盘一样本身会占据一部分空间,这部分空间就是120中的数据

查看binlog日志
[root@db03 ~]# mysqlbinlog /application/mysql/data/mysql-bin.000001 

查看row模式时,会出现读日志不容易被看懂,可加以下参数使日志易读
[root@db03 ~]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000002 

事件介绍

1)在binlog中最小的记录单元为event

2)一个事务会被拆分成多个事件(event)

所谓事件就是在MySQL中组成每个事务的SQL语句

在binlog日志文件中体现为atxxx,at后的每个位置点都表示一个事件

事件(event)特性

1)每个event都有一个开始位置(start position)和结束位置点(stop position)

2)所谓的位置就是event对整个二进制的文件的相对位置

3)对于一个二进制日志中,前120个position 是文件格式信息预留的空间

4)MySQL第一个记录的事件是从120开始的

row模式下二进制日志分析及数据恢复实战

环境:

#查看binlog信息
mysql> show master status;

#创建一个binlog库
mysql> create database test_binlog;

#进入binlog库
mysql> use test_binlog

#创建binlog_table表
mysql> create table binlog_table(id int);

mysql> insert into binlog_table values(1);
mysql> insert into binlog_table values(2);
mysql> insert into binlog_table values(3);


#查看一下binlog信息,发现位置点发送了变化
mysql> show master status;

#删除、修改数据
mysql> delete from binlog_table where id=3;
mysql> update binlog_table set id=22 where id=2;

#删表、删库
mysql> drop table binlog_table;
mysql> drop database test_binlog;

恢复:

#使用mysqlbinlog来查看binlog文件
[root@db03 ~]# mysqlbinlog --base64-output=decode -vvv /application/mysql/data/mysql-bin.000007 

#截取所需要的二进制日志(按需截取)
#看截取的内容
[root@db03 ~]# mysqlbinlog --start-position=120 --stop-position=992 /application/mysql/data/mysql-bin.000007

#截取需要的日志导SQL文件中
[root@db03 ~]# mysqlbinlog --start-position=120 --stop-position=992 /application/mysql/data/mysql-bin.000007 > /tmp/test_binlog.sql 

#临时关闭binlog,避免恢复数据时重复记录
mysql> set sql_log_bin=0;

#执行SQL文件
mysql> source /tmp/test_binlog.sql;

#查看删除库
mysql> show databases;

#进入test_binlog库
mysql> use test_binlog

#查看删除的表
mysql> show tables;

#查看表中的内容
mysql> select * from binlog_table;

存在问题

数据库或表被误删除的是很久之前创建的(一年前)
如果基于binlog全量恢复,成本很高
1)可以用备份恢复+短时间内二进制日志,恢复到故障之前
2)非官方方法,binlog2sql,binlog取反,类似于Oracle的flushback
3)延时从库

如果同一时间内和故障库无关的数据库都有操作,在截取binlog时都会被截取到
-d 参数接库名

 只截取test_binlog库中的120到992之间的数据
 mysqlbinlog -d test_binlog --start-position=120 --stop-position=992 /application/mysql/data/mysql-bin.000007 > /tmp/test_binlog.sql

演示如何在不同的binlog日志中截取需要的信息回复数据

#查看binlog信息
mysql> show master status;
2548

#创建一个test_binlog1库
mysql> create database test_binlog1;

#进入test_binlog1库
mysql> use test_binlog1

#创建student表
mysql> create table student(id int);

#查看binlog信息
mysql> show master status;
2666

#刷新一个新的binlog
mysql> flush logs;

#查看binlog信息
mysql> show master status;

#像student表中添加数据
mysql> insert into student values(1);
mysql> insert into student values(2);
mysql> insert into student values(3);

#查看binlog信息
mysql> show master status;
747

#删除修改表内容
mysql> delete from student where id=1;
mysql> update student set id=999 where id=3;

#删除表库文件
mysql> drop table student;
mysql> drop database test_binlog1;

恢复

查看mysql-bin.000007 日志中的 test_binlog1 相关的日志信息
[root@db03 ~]# mysqlbinlog -d test_binlog1 --base64-output=decode -vvv /application/mysql/data/mysql-bin.000007

#截取所需要的二进制日志(按需截取)
#看截取的内容
[root@db03 ~]# mysqlbinlog --start-position=2430 --stop-position=2666 /application/mysql/data/mysql-bin.000007

#截取需要的日志导SQL文件中
[root@db03 ~]# mysqlbinlog --start-position=2430 --stop-position=2666 /application/mysql/data/mysql-bin.000007 > /tmp/test_binlog1.sql;

查看mysql-bin.000008 日志中的 test_binlog1 相关的日志信息
[root@db03 ~]# mysqlbinlog -d test_binlog1 --base64-output=decode -vvv /application/mysql/data/mysql-bin.000008 

#截取所需要的二进制日志(按需截取)
#看截取的内容
[root@db03 ~]# mysqlbinlog -d test_binlog1 --start-position=120 --stop-position=747 /application/mysql/data/mysql-bin.000008

#截取需要库中的日志导SQL文件中
[root@db03 ~]# mysqlbinlog -d test_binlog1 --start-position=120 --stop-position=747 /application/mysql/data/mysql-bin.000008 > /tmp/test_binlog2.sql

#临时关闭binlog,避免恢复数据时重复记录
mysql> set sql_log_bin=0;

#执行test_binlog1.sql文件
mysql> source /tmp/test_binlog1.sql;

#查看数据库,恢复成功
mysql> show databases;

#进入数据库
mysql> use test_binlog1;

#查看数据表
mysql> show tables;

#产数据内容,未恢复
mysql> select * from student;

#执行test_binlog2.sql文件
注意:在执行test_binlog1.sql与test_binlog2.sql文件时顺序不可逆
mysql> source /tmp/test_binlog2.sql;

#查看数据内容,以恢复成功
mysql> select * from student;


注意:在截取binlog时,为了保证事务的完整性,需要连同一同截取,一般截取一个完整的事务,需要从BEGIN开始截取到COMMIT结束,否则这个事务将是不完整的事务

刷新binlog

1)[root@db03 ~]# mysqladmin -uroot -p123 -S /application/mysql/tmp/mysql.sock flush-logs

2)mysql> flush logs;

3)重启数据库时会刷新

4)二进制日志上限(max_binlog_size),当达到上限1G时会自动刷新出新的binlog

5)mysqldump -f 时会自动刷新binlog

删除binlog
1)原则
在存储能力范围内,能多保留则多保留
基于上一次全备前的可以选择删除

删除方式

1.根据存在时间删除日志

只保留7天的
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

2.使用purge命令删除

#只保留最近三天的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3.根据文件名删除

#删除mysql-bin.000010之前的
PURGE BINARY LOGS TO 'mysql-bin.000010';

此时刷新接着之前的继续添加binlog

4.使用reset master

#删除所有的binlog,并从头开始计算
mysql> reset master; 

此时刷新从mysql-bin.00001的开始添加binlog

错误一:

只能是RR,不能是RC或RU