MySQL学习【第十三篇日志管理】

一.MySQL日志类型

日志文件　　　　选项　　　　　　　　　　　　　　 文件名/表名称　　　　　　  　　　　程序

错误日志　　　　--log-error　　　   　　　　　　　　host_name.err　　　　　　　　　　N/A

常规日志　　　　--general_log　　 　　　　　　　　host_name.log/general_log　　　　  N/A

慢速查询日志　　--slow_query_log/--log_query_time   host_name-slow.log/slow_log　　　mysqldumpslow

二进制日志　　　--log-bin/--expire-logs-days　　　　 host_name-bin.000001　　　　       mysqlbinlog

审计日志　　　　--audit_log/--audit_log_file　　　　   audit.log　　　　　　　　　　　　　N/A

 

二.错误日志

1.作用：记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。

2.默认日志位置：$MYSQL_HOME/data/

3.开启方式（mysql默认开启）

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/application/mysql/data/$hostname.err
#查看方式
mysql> show variables like 'log_error';

 

 

三.一般查询日志（基本不开）

1.作用：记录MySQL所有执行成功的sql语句，可以用作审计，但一般不开

2.默认日志位置：$MYSQL_HOME/data/

3.开启方式：（mysql默认不开）

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=on
general_log_file=/application/mysql/data/$hostnamel.log
#查看方式
mysql> show variables like '%gen%';

 

 

四.二进制日志（重中之重，这个不会，白学）

1.作用：除了DQL语句记录其他sql语句，并拆分为多个event(事件)进行记录

2.二进制日志模式：

statement：语句模式，记录sql语句（默认模式）。
row：行模式，即数据行的变化过程
mixed：以上两者的混合模式。
企业推荐使用row模式

3.语句模式和行模式优缺点

statement优点：简单明了，容易被看懂，就是sql语句，记录时不需要太多的磁盘空间。

　　　　 缺点：记录不够严谨

          row优点：记录更加严谨

　　　　 缺点：有可能会需要更多的磁盘空间，不太容易被读懂

4.bin-log的作用

　　a）如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
　　b）数据的备份恢复
　　c）数据的复制

 

五.二进制日志的管理操作

1.开启方式（在mysql5.7中开启bin-log需要加上serverid）

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

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

 

2.二进制日志的操作

#物理查看
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql      285 Mar  6  2017 mysql-bin.000001
#命令行查看
mysql> show binary logs;
mysql> show master status;
#查看binlog事件
mysql> show binlog events in 'mysql-bin.000007';

 

 

六.事件

1.事件介绍

在binlog中最小的记录单元为event，一个事务会被拆分成多个事件（event）

2.事件特性

　　a) 每个event都有一个开始位置（start position）和结束位置（stop position）。

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

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

　　d) MySQL第一个记录的事件，都是从120开始的。

3.row模式下，二进制日志分析及恢复

#查看binlog信息
mysql> show master status;
#创建一个binlog库
mysql> create database binlog;
#使用binlog库
mysql> use binlog
#创建binglog_table表
mysql> create table binlog_table(id int);
#查看binlog信息
mysql> show master status;
#插入数据1
mysql> insert into binlog_table values(1);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#插入数据2
mysql> insert into binlog_table values(2);
#插入数据3
mysql> insert into binlog_table values(3);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#删除数据1
mysql> delete from binlog_table where id=1;
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#更改数据2为22
mysql> update binlog_table set id=22 where id=2;
#查看binlog
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#查看数据
mysql> select * from binlog_table;
#删表
mysql> drop table binlog_table;
#删库
mysql> drop database binlog;

 

4.数据恢复到delete之前

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000013';
#使用mysqlbinlog来查看
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013|grep -v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013
### UPDATE `binlog`.`binlog_table`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
#分析
update binlog.binlog_table
set
@1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22
where
@1=2  --------->@1表示binlog_table中的第一列,集合表结构就是id=2
#结果
update binlog.binlog_table set id=22 where id=2;
#截取二进制日志
查看二进制日志后，发现delete语句开始位置是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013
#临时关闭binlog
mysql> set sql_log_bin=0;
#执行sql文件
mysql> source /tmp/binlog.sql
#查看删除的库
mysql> show databases;
#进binlog库
mysql> use binlog
#查看删除的表
mysql> show tables;
#查看表中内容
mysql> select * from binlog_table;

 

5.几个疑问？

如果时基于binlog的全量备份，那可是耗时耗力废资源，咋解决呢？
1）可以用备份恢复+短时间内二进制日志，恢复到故障之前
2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback
3）延时从库 

如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到，所以使用一个参数 （-d 参数接库名）

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000014';
#查看db1的操作
[root@db01 data]# mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000014

 

6.刷新以及删除binlog日志
a( 刷新binlog日志
　　1）flush logs;
　　2）重启数据库时会刷新
　　3）二进制日志上限（max_binlog_size）

b( 删除二进制日志（原则上存储能力范围内，能多保留则多保留，基于上一次全备前的可以选择删除）

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

#########七天前的删除

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;     ###三天前删除



PURGE BINARY LOGS TO 'mysql-bin.000010';     到10为止，之前的全删

mysql> reset master;    ######全删，并且日志从0000001开始