1. MySQL日志之binlog日志

1.1 mysql工具mysqlbinlog

mysqbinlog工具的作用是解析mysql的二进制binlog日志内容,把二进制的日志解析成可以在MySQL数据库里执行的SQL语句。

1.2 MySQL的·binlog日志是什么?

MySQL数据目录下的如下文件就是mysql的binlog日志




mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008   . . . . . .. . 提示:要想生成binlog必须在配置文件中打开log-bin功能 [root@db01 3306]# greplog-bin /data/3306/my.cnf log-bin =/data/3306/mysql-bin



1.3 mysql binlog日志功能开启



[root@db01 3306]# vim /data/3306/my.cnf log-bin = /data/3306/mysql-bin




1.4 mysql的binlog日志作用是什么?

       mysql的binlog日志作用是用来记录mysql内部增删等对mysql数据库有更新的内容的记录(对数据的改动),对数据库查询的语句如show,select开头的语句,不会被binlog日志记录。用于数据库的主从复制,以及增量恢复


测试题:

在MySQL数据库中,关于binlog日志,下列说法正确的是-----------(A

A:依靠足够长度的binlog日志和定期的全备,我们可以恢复任何时间点的单表数据。

B:以mysql主从同步为例,binlog中会记录主数据库的所有操作。

C:以mysql主从同步为例,binlog中会记录主数据库的所有查询操作。

D:binlog通过cat和vi无法查看,但可以通过gedit查看。

1.5 mysqlbinlog工具解析binlog日志实践

默认情况binlog日志是二进制格式的,不能使用查看文本工具的命令查看,例如:cat、vi




[root@db01 3306]# file /data/3306/mysql-bin.000001 /data/3306/mysql-bin.000001: MySQL replication log




解析指定库的binlog日志

范例:利用mysqlbinlog-d参数解析指定库的binlog日志




[root@db01 3306]#  oldboy /data/3306/mysql-bin.000001 -r oldboy.sql [root@db01 3306]# ll oldboy.sql                -rw-r--r-- 1 root root 4731 Aug 31 04:36 oldboy.sql [root@db01 3306]# cat oldboy.sql



结论:mysqlbinlog工具分库导出binlog,如果使用-d参数,那更新数据时,必须有usedatabase,才能分出指定库的binlog,例如:




use oldboy; insert into test values(1,’oldboy’)




下面的写法就不行



nsert into oldboy.test values(1,’oldboy’)



官方资料

查看数据库日志mysql 更新 mysql查看数据库操作日志_运维



按照位置截取:精确




mysqlbinlog mysql-bin.000003 365 456 pos.sql



# 说明:指定文件的第365到456结束,是binlog文件中的“# at 365”---“# at 456”;-r是指定文件,相当于重定向,如果命令中指定开始不指定结尾就是到文件的结尾,如果不指定开始则是从文件开头开始。


按照时间截取:模糊,不准




mysqlbinlog mysql-bin.000003 -'2016-10-8 12:12:12' '2016-10-8 12:20:20' -r time.sql



# 说明:时间在该binlog文件中的 # at456 下面即使,只指定开始时间就是到文件结尾,只指定结尾,则是从开头开始;-r是指定文件

1.6 mysqlbinlog命令小结

mysqlbinlog命令:

1. 把binlog日志解析为SQL语句(包含位置和时间点)。

2. -d参数根据指定库拆分binlog(拆分单表binlog可通过SQL关键字过滤)。

3. 通过位置参数截取部分binlog:--syart-position=365  --stop-position=456,;精确定位取部分内容。

4. 通过时间参数截取部分binlog:-stsrt-datetime='2016-10-8 12:12:12' --stop-deter=time='2016-10-8 12:20:20',模糊截取部分内容,会丢数据。

5. -r指定文件名,相当于重定向。

6. 解析ROW级别binlog日志的方法

查看数据库日志mysql 更新 mysql查看数据库操作日志_运维_02


2. MySQL数据库服务日志

2.1 错误日志(error.log)介绍与调整

1. 错误日志(error.log)介绍

的错误日志记录MySQL服务进程mysql在启动/关闭或运行过程中遇到的错误信息。


2. 错误日志(error.log)实践


法1:在配置文件中调整方法,当然可以在启动时加入启动参数




[mysqld_safe]            log-error=/data/3306/mysql_oldboy3306.err




法2:启动MySQL命令里加入:




mysql_safe --detaults-file=/data/3306/my.cnf  &




在数据库里也可以看到




mysql> show variables like '%log_error%'; +---------------+---------------------------------+ | Variable_name |      Value                      | +---------------+---------------------------------+ | log_error     |  | +---------------+---------------------------------+ 1 row in set (0.00 sec)




如果mysql数据库起不起来排查步骤

查看数据库日志mysql 更新 mysql查看数据库操作日志_查看数据库日志mysql 更新_03

1. 将日志文件备份,然后清空日志文件,并重启数据库,查看报错。

2. 如果是多实例的话讲多实例目录下的目录权限chown -R mysql.....。

3. 查看是否有管理mysql数据库的用户。

2.2 普通查询日志(genera log)介绍与调整(工作中不用)

1. 普通查询日志(generalquery log)介绍

   普通查询日志(general querylog),记录客户端连接信息和执行的SQL语句信息(增删改查,全部记录)。工作中不用,会消耗IO性能,


2. 普通查询日志generalquery log)调整




mysql> show variables like 'general_log%';               +-------------------------+--------------------------+ | Variable_name           |Value                     | +-------------------------+--------------------------+ | general_log             | ON                       | | general_log_file        | /data/3306/data/db01.log | +-------------------------+--------------------------+ 2 rows in set (0.00 sec)




临时生效:




mysql> set global general_log_file ='/data/3306/data/db01.log';             Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'general_log%';               +-------------------------+--------------------------+ | Variable_name           |Value                     | +-------------------------+--------------------------+ | general_log             | OFF                      | | general_log_file        | /data/3306/data/db01.log | +-------------------------+--------------------------+ 2 rows in set (0.00 sec)




永久生效(配置文件中配置):




[root@db01 3306]# grep gene /data/3306/my.cnf general_log = on                                  general_log_file = /data/3306/data/db01.log




实际的普通查询日志示例:

查看数据库日志mysql 更新 mysql查看数据库操作日志_运维_04


2.3 慢查询日志(slow query log)介绍与调整 *****

1. 慢查询日志介绍:

  慢查询日志,记录执行时间超出指定值的SQL语句

 

2. 慢查询日志的调整




long_query_time = 1                              log-slow-queries = /data/3306/slow.log           log_queries_not_using_indexes




慢查询的设置,对于数据库SQL的优化非常重要




[root@db01 /]# egrep "quer"/data/3306/my.cnf|tail -3 long_query_time = 1 log-slow-queries = /data/3306/slow.log log_queries_not_using_indexes




利用慢查询进行优化解决方案

1. 开启慢查询参数



long_query_time = 1                                   log-slow-queries = /data/3306/slow.log log_queries_not_using_indexes





2. 慢查询日志切割脚本




[root@db01 /]# vim /server/scripts/cut_slow_log.sh
#!/bin/bash
cd /data/3306 &&\
/bin/mv slow.log slow.log.$(date +%F) &&\
mysqladmin -uroot -poldboy123 -S/data/3306/mysql.sock flush-log

[root@db01 scripts]# tail -2 /var/spool/cron/root
# cut mysql slow log
00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh>/dev/null 2>&1





3. 使用工具mysqlsla分析慢查询,定时给相关人员信箱


使用explain优化SQL语句(select语句)的基本流程  * * * * *


原因:当网站出现问题,到会访问慢,在数据库中超过2秒就是很慢的查询了!

 

思路当用户访问数据慢时,就要想到是不是满查询引起的,所以我们要查找慢查询,而找找慢查询分两种情况,第一种是比较紧急情况下使用下面紧急处理的现场抓取的方法,进入mysql数据库将慢查询找到,然后建立索引,这起见借助explain可以查看慢查询语句是否经过索引,如果没有经过索引就建立索引,第二种则是不那么紧急的情况,可以在平时就关注负载、cpu等信息,当发现有异常时时就进行检查,这些都是可以通过修改配置文件来将慢查询的语句输入到sllow.log里面,然后通过分析工具进行分析,以邮件的形式发给开发人员和dba人员让他们来介入处理。

 

排查方法:首先查看web负载高不高,存储压力大不大,还有数据库负载和磁盘IO,还有cpu等原因。负载高的话常规情况下是数据库慢查询导致的,下面是查询数据库慢查询如下:

 

(1抓慢查询SQL语句方法

在数据库查找慢查询(紧急处理)




mysql> show full processlist;                            
+----+------+-----------+--------+---------+------+-------+-----------------------+
| Id | User | Host      | db     | Command | Time | State |Info                   |
+----+------+-----------+--------+---------+------+-------+-----------------------+
|  9 | root | localhost | oldboy | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+--------+---------+------+-------+-----------------------+
1 row in set (0.00sec)



说明:如果数据库正在对外提供访问,访问量大时。连续执行此命令会有很多语句,建议每隔两秒执行两次,当发现同一个命令连续出现时,就可能是慢查询语句。

 

在命令行查询慢查询(日常处理)




[root@db01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showfull processlist;"|egrep -vi "sleep"
Id   User   Host       db    Command  Time    State  Info
11   root   localhost   NULL   Query      0     NULL    show full processlist



说明:此命令可以说是未雨绸缪;重要不紧急,分析慢查询日志


配置参数记录慢查询语句




long_query_time = 2                          
log_queries_not_using_indexes                 
log-slow-queries = /data/3306/slow.log




(2抓到慢查询后,使用explain语句检查索引执行情况(看下有没有走索引)




explain select * from test where name='oldgirl'\G
explain select * from test where name='oldgirl'\G



提示:这两条命令是查询的结果是一样的!SQL_NO_CACHE是防止有缓存


(3)对需要建索引的条件列建立索引

  大表不能高峰期建立索引,300万记录就是达标


(4)分析man查询工具mysqlsla(每天早晨发邮件)。

切割慢查询日志

1.mv切割、reload进程(和mginx日志切割一个原理)



[root@db01 3306]# vim /data/3306/my.cnf       
log-slow-queries = /data/3306/slow.log        
 
[root@db01 3306]# /data/3306/mysql restart    
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
 
[root@db01 3306]# ll slow.log                 
-rw-rw---- 1 mysqlmysql 380 Aug 27 15:59 slow.log
 
[root@db01 3306]# mv /data/3306/slow.log /opt/$(date +%F)_slow.log      
 
[root@db01 3306]# ll /opt/                                              
-rw-rw----  1 mysql mysql 190 Aug 27 16:26 2016-08-27_slow.log
 
[root@db01 3306]# mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-logs
                                     
[root@db01 3306]# ll /data/3306/slow.log
-rw-rw---- 1 mysqlmysql 190 Aug 27 16:28 /data/3306/slow.log




2. 写成脚本(定时任务)




mv /data/3306/slow.log/opt/$(date +%F)_slow.log mysqladmin -uroot -poldboy123 -S/data/3306/mysql.sock flush-logs




提示:可以将以上命令写成脚本,每天进行切割,在使用相应的工具进行分析


切割完之后可能会有很多数据,所以需要工具来帮我们分析