MySQL数据库日志介绍

第1章 binlog日志

1.1 binlog日志介绍

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

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

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@test3 ~<span style="color:#999999">]</span><span style="color:slategray"># ll /data/3306/</span>
total 88
-rw-rw---- 1 mysql mysql   703 Feb  3 14:21 mysql-bin.000001        <span style="color:slategray">#<==binlog日志文件</span>
-rw-rw---- 1 mysql mysql   126 Feb  3 14:23 mysql-bin.000002
-rw-rw---- 1 mysql mysql   126 Feb  3 14:24 mysql-bin.000003
-rw-rw---- 1 mysql mysql   479 Feb  3 14:36 mysql-bin.000004
-rw-rw---- 1 mysql mysql   477 Feb  3 14:39 mysql-bin.000005
-rw-rw---- 1 mysql mysql 18699 Feb  4 03:05 mysql-bin.000006
-rw-rw---- 1 mysql mysql   150 Feb  4 03:05 mysql-bin.000007
-rw-rw---- 1 mysql mysql 13334 Feb  4 04:08 mysql-bin.000008</code></span>

 

1.2 binlog日志功能的开启

需要在配置文件my.cnf中打开log-bin功能，才会生成对应的binlog日志文件

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@localhost 3306<span style="color:#999999">]</span><span style="color:slategray"># grep "log-bin" /data/3306/my.cnf</span>
<span style="color:#999999">[</span>mysqld<span style="color:#999999">]</span>
log-bin <span style="color:#9a6e3a">=</span> /data/3306/mysql-bin      <span style="color:slategray">#<==在mysqld配置里面添加log-bin功能</span></code></span>

 

查看bin-log功能开启状况：

<span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> show variables like <span style="color:#669900">'%log_bin%'</span><span style="color:#999999">;</span>
+---------------------------------+-------+
<span style="color:#9a6e3a">|</span> Variable_name                   <span style="color:#9a6e3a">|</span> Value <span style="color:#9a6e3a">|</span>
+---------------------------------+-------+
<span style="color:#9a6e3a">|</span> log_bin                         <span style="color:#9a6e3a">|</span> ON    <span style="color:#9a6e3a">|</span>             <span style="color:slategray">#<==记录binlog开关</span>
<span style="color:#9a6e3a">|</span> log_bin_trust_function_creators <span style="color:#9a6e3a">|</span> OFF   <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> sql_log_bin                     <span style="color:#9a6e3a">|</span> ON    <span style="color:#9a6e3a">|</span>             <span style="color:slategray">#<==临时不记录binlog开关</span>
+---------------------------------+-------+
3 rows <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span>

 

临时不记录bin-log:

<span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">set</span> session sql_log_bin <span style="color:#9a6e3a">=</span> OFF<span style="color:#999999">;</span></code></span>

 

1.3 binlog日志的三种模式

1.3.1 Row Level

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。

优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题。

缺点：row level下，所有的执行语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有一条这样的update语句：update product set owner_member_id=‘b’ where owner_member_id=’a’，执行了之后，日志中记录的不是这条update语句所对应的事件（MySQL以事件的形式来记录bin-log日志），而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的时间。自然，bin-log日志的量就会很大。尤其是当执行alter table之类的语句的时候，产生的日志量是惊人的，因为MySQL对于alter table之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表，那么该表的每一条记录都会被记录到日志中。

1.3.2 Statement Level

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过程相同的sql来再次执行。

优点：statement level下的优点首先就是解决了row level下的缺点，不要需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。因为它只需要记录在master上所执行的语句细节，以及执行语句时候的上下文信息。

缺点：由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么它还必须记录每条语句在执行时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于MySQL发展很快，很多新功能不断的加入，使MySQL的复制遇到了不小的挑战。自然复制的时候涉及到的内容越复杂，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会照成MySQL的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()函数在有些版本中就不能正确复制；在存储过程中使用了last_insert_id()函数，就可能会使slave和master上得到不一致的id等等。由于row level是基于每一行来记录的变化，所以不会出现类似的问题。

1.3.3 Mixed

    实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和Row之间选择一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中对row level模式也做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

1.3.4 如何选择使用binlog的模式

1、互联网公司，使用MySQL的功能相对少（存储过程、触发器、函数）

选择默认的语句模式,statement Level模式

2、如果用到MySQL的特殊功能（存储过程、触发器、函数）则选择Mixed模式。

3、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数），数据最大化一致，此时最好行模式。

1.3.5 如何设置binlog的模式

<span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> show global variables like <span style="color:#669900">'%binlog_format%'</span><span style="color:#999999">;</span>
+---------------+-----------+
<span style="color:#9a6e3a">|</span> Variable_name <span style="color:#9a6e3a">|</span> Value     <span style="color:#9a6e3a">|</span>
+---------------+-----------+
<span style="color:#9a6e3a">|</span> binlog_format <span style="color:#9a6e3a">|</span> STATEMENT <span style="color:#9a6e3a">|</span>
+---------------+-----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span>

 

q  在配置文件里面修改

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>mysqld<span style="color:#999999">]</span>
log-bin<span style="color:#9a6e3a">=</span>mysql-bin
<span style="color:slategray">#binlog_format="STATEMENT"</span>
<span style="color:slategray">#binlog_format="ROW"</span>
binlog_format<span style="color:#9a6e3a">=</span><span style="color:#669900">"MIXED"</span></code></span>

 

q  直接修改

<span style="color:black"><code class="language-bash">SET GLOBAL binlog_format <span style="color:#9a6e3a">=</span> <span style="color:#669900">'STATEMENT'</span>
SET GLOBAL binlog_format <span style="color:#9a6e3a">=</span> <span style="color:#669900">'ROW'</span>
SET GLOBAL binlog_format <span style="color:#9a6e3a">=</span> <span style="color:#669900">'MIXED'</span></code></span>

 

1.4 binlog文件解析工具mysqlbinlog

默认情况下，binlog是二进制格式的，不能使用查看文本工具的命令查看，例如：cat,vi,find等

1.4.1 解析整个log-bin文件

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@test3 3306<span style="color:#999999">]</span><span style="color:slategray"># mysqlbinlog mysql-bin.000001</span></code></span>

 

1.4.2 解析指定的数据库

q  -d 只解析对应的数据库

使用-d参数，能够解析出对应log-bin文件里面的数据库，如下所示：

<span style="color:black"><code class="language-bash"> <span style="color:#999999">[</span>root@test3 3306<span style="color:#999999">]</span><span style="color:slategray"># mysqlbinlog -d oldboy mysql-bin.000001</span></code></span>

 

q  -r 数据导出到指定文件

将解析的结果存入另一个sql文件：

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@test3 3306<span style="color:#999999">]</span><span style="color:slategray"># mysqlbinlog -d oldboy mysql-bin.000001 -r oldboy.sql</span></code></span>

 

mysqlbinlog工具分库导出binlog，如果使用-d参数，那更新数据时，必须有use database，才能分出指定库的binlog，例如：''

<span style="color:black"><code class="language-bash">use oldboy<span style="color:#999999">;</span>
insert into student values<span style="color:#999999">(</span>1,<span style="color:#669900">'oldboy '</span><span style="color:#999999">)</span></code></span>

 

下面的写法就不行：

<span style="color:black"><code class="language-bash">insert into oldboy.student values<span style="color:#999999">(</span>2, <span style="color:#669900">'oldboy'</span> <span style="color:#999999">)</span></code></span>

 

1.4.3 按照位置截取

mysqlbinlog mysqlbin.000001 --start-position=365 --stop-position=456 -r pos.sql

指定开始位置，不指定结束位置：

mysqlbinlog mysqlbin.000001 --start-position=365  -r pos.sql

指定结束位置，不指定开始位置：

mysqlbinlog mysqlbin.000001 --stop-position=456 -r pos.sql

1.4.4 按照时间截取

mysqlbinlog mysql-bin000020 --start-datatime='2017-10-16 17:14:15' --stop-datetime='2017-10-16 18:14:15' -r time.sql

指定开始时间，不指定结束时间：

mysqlbinlog mysql-bin000020 --start-datatime='2017-10-16 17:14:15' -r time.sql

指定结束时间，不指定开始时间：

mysqlbinlog mysql-bin000020 --stop-datetime='2017-10-16 18:14:15' -r time.sql

1.4.5 mysqlbinlog命令小结

1、该命令作用是把binlog解析为sql语句（包含位置和时间点）

2、-d参数根据指定库拆分binlog（拆分单表binlog可以通过SQL关键字过滤）

3、通过位置参数截取部分binlog：--start-position=265 --stop-position=345，精确定位取部分内容。

4、通过时间参数截取部分binlog：--start-datetime= 180203 14:21:56 --stop-datetime= 180203 14:21:56，模糊取内容

5、-r文件名，相当于重定向”>”

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

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000016

mysqlbinlog --base64-output=”decode-rows” --verbose mysql-bin.000004

第2章 错误日志error log

2.1 error log日志介绍

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

2.2 error log日志功能的开启

在配置文件中调整参数:

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@test3 3306<span style="color:#999999">]</span><span style="color:slategray"># grep log-error my.cnf</span>
<span style="color:#999999">[</span>mysqld<span style="color:#999999">]</span>
log-error <span style="color:#9a6e3a">=</span> /data/3306/mysql_3306.err       <span style="color:slategray">#<==在mysqld配置里面添加log-error功能</span></code></span>

 

   在启动命令里加入：

<span style="color:black"><code class="language-bash">mysqld_safe --defaults-file<span style="color:#9a6e3a">=</span>/data/3306/my.cnf --log-error<span style="color:#9a6e3a">=</span>/data/3306/mysql_3306.err <span style="color:#9a6e3a">&</span></code></span>

 

   查看error log：

<span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> show variables like <span style="color:#669900">'%log_error%'</span><span style="color:#999999">;</span>
+---------------+---------------------------+
<span style="color:#9a6e3a">|</span> Variable_name <span style="color:#9a6e3a">|</span> Value                     <span style="color:#9a6e3a">|</span>
+---------------+---------------------------+
<span style="color:#9a6e3a">|</span> log_error     <span style="color:#9a6e3a">|</span> /data/3306/mysql_3306.err <span style="color:#9a6e3a">|</span>
+---------------+---------------------------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span>

 

第3章 普通查询日志

3.1 general query log日志介绍

普通查询日志（general query log）：记录客户端连接信息和执行的SQL语句信息；

3.2 general query log日志功能的卡其

在配置文件中调整参数:

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@test3 3306<span style="color:#999999">]</span><span style="color:slategray"># grep general_log my.cnf</span>
<span style="color:#999999">[</span>mysqld<span style="color:#999999">]</span>
general_log <span style="color:#9a6e3a">=</span> on
general_log_file <span style="color:#9a6e3a">=</span> /data/3306/data/MySQL.log    <span style="color:slategray">#<==在mysqld配置里面添加general_log功能</span></code></span>

 

第4章 慢查询日志

4.1 slow query log介绍

慢查询日志（slow query log）：记录执行时间超出指定值（long_query_time）的SQL语句。

4.2 slow query log日志功能的开启

<span style="color:black"><code class="language-bash">long_query_time <span style="color:#9a6e3a">=</span> 1
log-slow-queries <span style="color:#9a6e3a">=</span> /data/3306/slow.log
log_queries_not_using_indexes</code></span>

 

慢查询日志的设置，对于数据库的SQL的优化非常重要。

4.3 切割慢查询日志

将慢查询日志按天切割，脚本如下：

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@test3 scripts<span style="color:#999999">]</span><span style="color:slategray"># cat cut_slow_log.sh</span>
<span style="color:slategray">#/bin/bash</span>
<span style="color:#dd4a68">cd</span> /data/3306/ <span style="color:#9a6e3a">&&</span>\
/bin/mv slow.log.<span style="color:#ee9900"><span style="color:#ee9900">$(</span><span style="color:#dd4a68">date</span> +%F<span style="color:#ee9900">)</span></span> <span style="color:#9a6e3a">&&</span>\
mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-log</code></span>

 

   设置切割日志文件的定时任务：

<span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@test3 scripts<span style="color:#999999">]</span><span style="color:slategray"># tail -2 /var/spool/cron/root</span>
<span style="color:slategray">#cut mysql slow log</span>
00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh <span style="color:#9a6e3a">&</span><span style="color:#9a6e3a">></span>/dev/null</code></span>