第八章·DBA-MySQL日志管理

MySQL日志简介

错误日志

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

默认路径及文件名:
源码和二进制安装：datadir/$hostname.err
yum安装：/var/log/mysql.log

默认开启。如何修改:

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

一般查询日志

作用:
记录MySQL的常规操作,可以做审计用但一般很少开启

默认路径及文件名:
datadir/$hostname.log

默认不开启。如何修改:

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

二进制日志

作用:
记录已提交的DML事务语句，并拆分为多个事件（event）来进行
记录 记录所有DDL、DCL等语句
总之，二进制日志会记录所有对数据库发生修改的操作

默认路径及文件名:
datadir/xxx.000001

默认不开启。如何修改:

## mysql5.6
# 相对路径默认在data下
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin

# 绝对路径位置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin

# 自定义位置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log-bin=/tmp/lw-bin

## MySQL5.7
MySQL5.7想要开binlog必须配置 server_id
server_id=1
log-bin=mysql-bin

mysql-bin.index：MySQL二进制日志binlog的索引文件，有几个binlog会记录几个binlog

二进制日志的工作模式

statement：语句模式，上图中将update语句进行记录（默认模式）。

row：行模式，即数据行的变化过程，上图中Age=19修改成Age=20的过程事件。

mixed：以上两者的混合模式。 企业推荐使用row模式

statment：语句模式（MySQL5.6默认的工作模式）

mysql> show variables like  'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
将所有的语句，记录在binlog中
    优点：通俗易懂，占用磁盘空间小
    缺点：不严谨

row：行级模式（MySQL5.7的默认工作模式）

mysql[(none)]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
将所有的语句及变化过程，记录在binlog中
    优点：严谨
    缺点：不易懂、占用磁盘空间大

工作模式如何修改

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

如何查看二进制日志

## 查看默认语句模式
[root@db02 data]# mysqlbinlog mysql-bin.000001

## 查看行级模式
[root@db02 data]# mysqlbinlog -vvv --base64-output=decode-row mysql-bin.000002

## 库内查看当前有几个binlog日志及大小
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       143 |
| mysql-bin.000002 |       738 |
+------------------+-----------+

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

事件介绍

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

事件（event）特性

1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的

## mysql5.6
在MySQL5.6中，一个新的binlog起始位置点是120,120是系统信息预留空间
其实120，就是该文件的大小
143是空binlog，里面没有任何SQL语句执行

## mysql5.7
在MySQL5.7中，一个新的binlog起始位置点是154,154是系统信息预留空间
其实154，就是该文件的大小
177是空binlog，里面没有任何SQL语句执行

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;

恢复数据到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;

思考:
数据库或表被误删除的是很久之前创建的（一年前）
如果基于binlog全量恢复，成本很高
1）可以用备份恢复+短时间内二进制日志，恢复到故障之前
2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback
3）延时从库
如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到 想一个办法过滤出来？
1）grep？
其他过滤方案？
1）-d 参数接库名

#用户使用数据是穿插使用的，binlog也不是一个库一个库记录的

解决方案：
只需要过滤出，被删除的数据库相关binlog中的sql语句
使用-d指定数据库截取binlog
[root@db04 data]# mysqlbinlog -d lw2 -vvv --base64-output=decode-row mysql-bin.000004

删除、刷新binlog

刷新binlog日志

1）手动执行 flush logs;

2）重启数据库时会自动刷新binlog

3）二进制日志上限（max_binlog_size），binlog大小达到1g时

4）使用mysqladmin flush-log

使用mysqldump做备份时，可以刷新binlog
[root@db02 data]# mysqldump -A -F > /tmp/full.sql

删除binlog日志

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

删除方式

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

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

2.使用purge命令删除指定时间段binlog

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

3.指定binlog名字删除，之前的binlog都删除

purge binary logs to 'mysql-bin.000010';
mysql[(none)]> purge binary logs to 'mysql-bin.000007';

4.重置binlog，删除所有binlog

mysql> reset master; 

慢查询日志

作用:
1）是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
2）通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

默认路径及文件名:
datadir/$hostname-slow.log

默认不开启。如何修改:

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
# 指定是否开启慢查询日志
slow_query_log = 1
# 指定慢日志文件存放位置（默认在data）
slow_query_log_file=/application/mysql/data/slow.log
# 设定慢查询的阀值(默认10s)
long_query_time=0.05
# 不使用索引的SQL语句是否记录到慢查询日志
log_queries_not_using_indexes

------------------------------------------------------------
# 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=1000（鸡肋）

模拟慢查询语句

#进入world库
mysql> use world
#查看表
mysql> show tables
#将city表中所有内容加到t1表中
mysql> create table t1 select * from city;
#查看t1的表结构
mysql> desc t1;
#将t1表所有内容插入到t1表中（多插入几次）
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
#提交
mysql> commit;
#删除t1表中id>2000的数据
mysql> delete from t1 where id>2000;
#查看慢日志
[root@db01 ~]# cat /application/mysql/data/mysql-db01

使用mysqldumpslow命令来分析慢查询日志

[root@db02 data]# mysqldumpslow -s t -t 10 db02-slow.log

参数说明:

-s:
是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；

-t:
是top n的意思，即为返回前面多少条的数据；

-g:
后边可以写一个正则匹配模式，大小写不敏感的；

percona 慢查询工具

yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm

使用percona公司提供的pt-query-digest工具分析慢查询日志

[root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log

有能力的可以做成可视化界面：
Anemometer基于pt-query-digest将MySQL慢查询可视化
httpss://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载
httpss://github.com/box/Anemometer 可视化代码下载