mysq日志管理

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

4.一般查询日志

作用：记录mysql所有执行成功的SQL语句信息，可以做审计用，但我们很少开启。

默认位置：

$msyql_home/data/

开启方式：（mysql安装之后默认不开启）

##编辑配置文件

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

[mysqld]

general_log=on

general_log_file=/application/mysql/data/$hostnamel.log

#查看方式

mysql> show variables like '%gen%';

5.二进制日志

作用：记录一提交的DML事物语句，并拆分为多个事件（event）来进行记录

记录所有的DDL 、DCL等语句

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

##二进制日志模式

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

　　row：行模式，即数据行的变化过程

　　mixed：以上两者的混合模式

　　企业推荐使用row模式

statement模式：

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

　　缺点：记录不够严谨

row模式

　　优点：记录更加严谨

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

binlog的作用

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

　　2）数据的备份恢复

　　3）数据的复制

******二进制日志管理操作实践

开启方式

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

[mysqld]

log-bin=mysql-bin

binlog_format=row

注意在mysql5.7开启binlog必须加上server-id

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

[mysqld]

log-bin=mysql-bin binlog_format=row

server_id=1

二进制日志的操作

#物理查看

[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.000006';

事件介绍：

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

　　2）一个事物会被拆分成多个事件（event）

事件（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 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 event  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

查看二进制日志后，发现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文件 my

sql> 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是都会被截取到

模拟数据

#刷新一个新的binlog

mysql> flush logs;

#创建db1、db2两个库

mysql> create database db1;

mysql> create database db2;

#库db1操作

mysql> use db1

#创建t1表

mysql> create table t1(id int);

#插入5条数据

mysql> insert into t1 values(1),(2),(3),(4),(5);

#提交

mysql> commit;

#库db2操作

mysql> use db2

#创建t2表

mysql> create table t2(id int);

#插入3条数据

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

#提交

mysql> commit;

#查看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

###删除、刷新binlog

****刷新binlog

　　1）flush logs

　　2）重启数据库是会被刷新

　　3）二进制日志上线（max_binlog_size）

****删除二进制日志

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

删除方式  

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

#临时生效 　　　　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.根据文件名删除

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

4.使用reset  master

mysql> reset master;

*******慢查询日志

作用：

　　1）是将mysql服务器中影响数据库性能的相关SQL 语句记录到日志文件中

　　2）通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

默认位置：

$mysql_host/data/$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

#不使用索引的慢查询日志是否记录到日志

log_queries_not_using_indexes

#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

min_examined_row_limit=100（鸡肋）

模拟慢查询语句

#进入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命令来分析慢查询日志

#输出记录次数最多的10条SQL语句


$PATH/mysqldumpslow -s c -t 10 /database/mysql/slow-log

参数说明

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

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

-g： 后面可以写一个正则匹配模式，大小写不敏感

如：

#得到返回记录集最多的10个查询

$PATH/mysqldumpslow -s r -t 10 /database/mysql/slow-log


#得到按照时间排序的前10条里面含有左连接的查询语句


$PATH/mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log

第三方推荐：

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 可视化代码下载