当前位置:首页 > Mysql > 正文内容

MySQL日志管理

5年前 (2019-09-08)Mysql509

一.MySQL日志简介

image.png


二.错误日志

作用:

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


默认位置:

$MYSQL_HOME/data/


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

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/application/mysql/data/$hostname.err

#查看方式
mysql> show variables like 'log_error';


三.一般查询日志

作用:

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


默认位置:

$MYSQL_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%';



四.二进制日志

作用:

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

记录所有DDL、DCL等语句

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


二进制日志模式:


statement:语句模式,上图中将update语句进行记录(默认模式)。

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

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


事件介绍


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 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
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)原则

在存储能力范围内,能多保留则多保留

基于上一次全备前的可以选择删除


删除方式


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

image.png

“MySQL日志管理” 的相关文章

MySQL程序结构

MySQL程序结构

mysql是一个C/S服务结构1、mysql自带的客户端程序(/application/mysql/bin)mysqlmysqladminmysqldump2、应用程连接MySQL方式TCP/IP的连接方式套接字连接方式MySQL服务器构成1.MySQL的后台进程+线程+预分配的内存结构。2.MyS...

MySQL索引管理及执行计划

MySQL索引管理及执行计划

一.索引介绍 1.索引1)索引就好比一本书的目录,它能让你更快的找到自己想要的内容。2)让获取的数据更有目的性,从而提高数据库检索数据的性能。2.索引类型1.BTREE:B+树索引2.HASH:HASH索引3.FULLTEXT:全文索引4.RTREE:R树索引3.索引管理索引建立在表的列上...

 MySQL的存储引擎(一)Innodb

MySQL的存储引擎(一)Innodb

一.存储引擎简介1、文件系统:1.1 操作系统组织和存取数据的一种机制。1.2 文件系统是一种软件。2、文件系统类型:ext2 3 4 ,xfs 数据2.1 不管使用什么文件系统,数据内容不会变化2.2 不同的是,存储空间、大小、速度。3、MySQL引擎:3.1 可以理解为,MySQL的“文件系统...

MySQL的存储引擎(二)Innodb核心特性——事务

MySQL的存储引擎(二)Innodb核心特性——事务

Innodb核心特性——事务1.什么是事务主要针对DML语句(update,delete,insert)一组数据操作执行步骤,这些步骤被视为一个工作单元:1)用于对多个语句进行分组2)可以在多个客户机并发访问同一个表中的数据时使用所有步骤都成功或都失败1)如果所有步骤正常,则执行2)如果步骤出现错误...

MySQL的备份和恢复

备份的类型冷备份:这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。温备份:这些备份在读取数据时进行,但在多数情况下,在进...