MySQL数据库三种日志的特点和使用-创新互联

下面讲讲关于MySQL数据库三种日志的特点和使用，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL数据库三种日志的特点和使用这篇文章你一定会有所受益。

成都创新互联拥有网站维护技术和项目管理团队，建立的售前、实施和售后服务体系，为客户提供定制化的网站设计、成都做网站、网站维护、四川雅安服务器托管解决方案。为客户网站安全和日常运维提供整体管家式外包优质服务。我们的网站维护服务覆盖集团企业、上市公司、外企网站、商城网站制作、政府网站等各类型客户群体，为全球成百上千企业提供全方位网站维护、服务器维护解决方案。

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’)

官方资料

按照位置截取：精确

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日志的方法

2. MySQL数据库服务日志

2.1 错误日志（error.log）介绍与调整

1.错误日志（error.log）介绍

 MySQL的错误日志记录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数据库起不起来排查步骤

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

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

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

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

对于以上MySQL数据库三种日志的特点和使用相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

分享标题：MySQL数据库三种日志的特点和使用-创新互联
转载来源：http://cqcxhl.cn/article/dpgico.html