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);
*****恢复数据到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;
###删除、刷新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(鸡肋)
模拟慢查询语句
************使用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 可视化代码下载