MySQL--10 日志简介

一.MySQL日志简介
二.错误日志
三.一般查询日志
四.二进制日志
五.慢查询日志

一.MySQL日志简介

二.错误日志

作用：

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

默认位置：$datadir

默认日志名： ${hostname}.err

开启方式:（MySQL安装完后默认开启)

#编辑配置文件

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

[mysqld]

log_error=/application/mysql/data/$hostname.err

#查看方式

mysql> show variables like 'log_error';

+---------------+------------+

| Variable_name | Value      |

+---------------+------------+

| log_error     | ./db01.err |

+---------------+------------+

1 row in set (0.00 sec)

三.一般查询日志

作用：

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

默认位置：*$datadir

默认日志名： ${hostname}.log

开启方式:（MySQL安装完之后默认不开启）

#编辑配置文件

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

[mysqld]

general_log=on

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

#查看方式

mysql> show variables like '%gen%';

+------------------+----------------------------------+

| Variable_name    | Value                            |

+------------------+----------------------------------+

| general_log      | ON                              |

| general_log_file | /application/mysql/data/db01.log |

+------------------+----------------------------------+

2 rows in set (0.00 sec)

四.二进制日志

作用：

记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录

记录所有DDL、DCL等语句

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

二进制日志模式:

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

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

mixed：以上两者的混合模式。

企业推荐使用row模式

默认位置：$datadir

默认日志名： mysql-bin

默认是否开启：关闭

优缺点:

statement模式：

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

缺点：记录不够严谨。

row模式：

优点：记录更加严谨。

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

binlog的作用:

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

2）数据的备份恢复

3）数据的复制

二进制日志的管理操作实战

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |      3614 |

| mysql-bin.000002 |       143 |

| mysql-bin.000003 |       120 |

+------------------+-----------+

3 rows in set (0.00 sec)

开启方式

mysql> show variables like 'binlog_format';

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)

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

[mysqld]

log-bin=mysql-bin

binlog_format=row  

mysql> show variables like 'binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

1 row in set (0.00 sec)

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

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

[mysqld]

log-bin=mysql-bin

binlog_format=row

server_id=1

总结：

statement：语句模式（默认）

row：行级模式（企业推荐使用）

记录数据行的变化过程

mixed：混合模式，把语句模式和行级模式混合使用

总结：

语句模式优点：通俗易懂，占用磁盘空间相对较少

语句模式缺点：不严谨，容易丢数据

行级模式优点：严谨，不易丢数据

行级模式缺点：看不懂，占用磁盘空间较大

mysql> create table binlog(id int); #DDL

mysql> insert into binlog values(1),(2),(3); #DML

binlog的作用:

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

2）数据的备份恢复

3）数据的复制（主从）

二进制日志的操作

#物理查看

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

[root@db01 ~]# mysqlbinlog  mysql-bin.00001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

mysqlbinlog: File 'mysql-bin.00001' not found (Errcode: 2 - No such file or directory)

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

事件介绍

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

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

#查看总共的binlog

mysql> show binary logs;

+----------------+-----------+

| Log_name       | File_size |

+----------------+-----------+

| mysql-bin.000001|      901 |

| mysql-bin.000002|     1767 |

+----------------+-----------+

事件（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 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 > /tmp/a.sql

#导入数据

[root@db01 data]# mysql -uroot -p1 < /tmp/a.sql

#临时关闭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

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

#查看db1的操作

[root@db01 data]# mysqlbinlog -d db01 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000014

指定数据库恢复

mysql> create database db1;

mysql> insert into binlog.binlog values(4);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> use db1

mysql> create table tb1(id int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb1 values(1),(2);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> use db2

Database changed

mysql> create table tb2(id int);

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from db1.tb1;

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

mysql> select * from db2.tb2;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

mysql> insert into binlog.binlog values(10);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

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

+------------------+------+-------------+-----------+-------------+----

| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Inf

+------------------+------+-------------+-----------+-------------+----

| mysql-bin.000004 |    4 | Format_desc |         1 |         120 | Ser

| mysql-bin.000004 |  120 | Query       |         1 |         214 | cre

| mysql-bin.000004 |  214 | Query       |         1 |         308 | cre

| mysql-bin.000004 |  308 | Query       |         1 |         405 | use

| mysql-bin.000004 |  405 | Query       |         1 |         477 | BEG

| mysql-bin.000004 |  477 | Table_map   |         1 |         522 | tab

| mysql-bin.000004 |  522 | Write_rows  |         1 |         577 | tab

| mysql-bin.000004 |  577 | Xid         |         1 |         608 | COM

| mysql-bin.000004 |  608 | Query       |         1 |         706 | use

| mysql-bin.000004 |  706 | Query       |         1 |         778 | BEG

| mysql-bin.000004 |  778 | Table_map   |         1 |         824 | tab

| mysql-bin.000004 |  824 | Write_rows  |         1 |         874 | tab

| mysql-bin.000004 |  874 | Xid         |         1 |         905 | COM

| mysql-bin.000004 |  905 | Query       |         1 |         977 | BEG

| mysql-bin.000004 |  977 | Table_map   |         1 |        1022 | tab

| mysql-bin.000004 | 1022 | Write_rows  |         1 |        1072 | tab

| mysql-bin.000004 | 1072 | Xid         |         1 |        1103 | COM

| mysql-bin.000004 | 1103 | Query       |         1 |        1218 | use

+------------------+------+-------------+-----------+-------------+----

18 rows in set (0.00 sec)

mysql> show tables from db1;

Empty set (0.00 sec)

恢复，指定数据库

[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000001

[root@db01 data]# mysqlbinlog -d db1 --start-position=120 --stop-position=1103 /application/mysql/data/mysql-bin.000001  > /tmp/b.sql

[root@db01 data]# mysql -uroot -p1 < /tmp/b.sql 

mysql> show tables;

+----------------+

| Tables_in_db01 |

+----------------+

| tt             |

+----------------+

1 row in set (0.00 sec)

## -d 指定只查看某个库的binlog

删除、刷新binlog

#1.刷新binlog

mysql> flush logs;

#2.当重启数据库的时候，binlog会刷新

/etc/init.d/mysqld restart

#3.当binlog 大小达到1G的时候自动刷新

#1.删除binlog（临时，保留7天的）

mysql> SET GLOBAL expire_logs_days = 7;

#2.永久保存

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

[mysqld]

expire_logs_days = 7

#3.删除3天之前的binlog

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

#4.指定binlog名字删除，删除该binlog之前的所有binlog

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

#5.清除所有binlog

mysql> reset master;

删除方式

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语句分析，改进以达到提高数据库性能的目的

默认是否开启：关闭

默认位置：$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

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

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/db01.log

/application/mysql-5.6.44/bin/mysqld, Version: 5.6.44-log (Source distribution). started with:

Tcp port: 0  Unix socket: /application/mysql/tmp/mysql.sock

Time                 Id Command    Argument

191112 15:54:01	    1 Connect	root@localhost on

		    1 Query	select @@version_comment limit 1

191112 15:54:05	    1 Query	SELECT DATABASE()

		    1 Init DB	world

		    1 Query	show databases

		    1 Query	show tables

		    1 Field List	city

		    1 Field List	country

		    1 Field List	countrylanguage

191112 15:54:10	    1 Query	show tables

191112 15:54:51	    1 Query	create table t1 select * from city

191112 15:54:59	    1 Query	desc t1

191112 15:55:29	    1 Query	insert into t1 select * from t1

191112 15:55:33	    1 Query	insert into t1 select * from t1

191112 15:55:35	    1 Query	insert into t1 select * from t1

191112 15:55:44	    1 Query	commit

191112 15:55:58	    1 Query	delete from t1 where id>2000

191112 15:56:02	    1 Quit

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

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

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

[root@db01 data]# mysqldumpslow  -s c -t 10 /application/mysql/data/slow.log 

Reading mysql slow query log from /application/mysql/data/slow.log

Count: 3  Time=0.10s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  insert into t1 select * from t1

Count: 1  Time=0.08s (0s)  Lock=0.03s (0s)  Rows=0.0 (0), root[root]@localhost

  create table t1 select * from city

Count: 1  Time=0.09s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from t1 where id>N

Died at /application/mysql/bin/mysqldumpslow line 161, <> chunk 5.

参数说明:

-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

[root@db01 data]# mysqldumpslow  -s c -t 10 -g "left join"  /application/mysql/data/slow.log 

Reading mysql slow query log from /application/mysql/data/slow.log

Died at /application/mysql/bin/mysqldumpslow line 161, <> chunk 5.

第三方推荐（扩展）：

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

巴特西