mysql学习8：第四章:数据库文件--日志文件

1.1.   错误日志文件（error log ）

默认存放路径在数据目录下，以error.log结尾的文件。

查看文件位置：

show variables like ‘%og_error%’;

注意warnings信息

TIMESTAMP类型特点

1.2.   二进制日志文件（binary log）

记录提交后的所有DML语句。如需记录select 和show，需要开启全量日志功能。

二进制日志的主要作用：

l   完成主从复制功能，binlog记录主服务器上所有修改数据的记录，发送到从服务器。

l   进行恢复操作，通过binlog，使用mysqlbinlog命令，实现基于时间点和位置的恢复。

配置参数log-bin=ON启动二进制日志，如没有命名文件名则默认mysql-binlog.xxxx为binlog文件名，默认存储在数据目录下。

[mysql]>show variables like '%log_bin%';

+---------------------------------+--------------------------------+

| Variable_name                   | Value                          |

+---------------------------------+--------------------------------+

| log_bin                         | ON                             |

| log_bin_basename                | /data/mysql/mysql-binlog       |

| log_bin_index                   | /data/mysql/mysql-binlog.index |

| log_bin_trust_function_creators | OFF                            |

| log_bin_use_v1_row_events       | OFF                            |

| sql_log_bin                     | ON                             |

+---------------------------------+--------------------------------+

6 rows in set (0.00 sec)

查看当前binlog文件及值大小

[mysql]>show binary logs;

+---------------------+-----------+

| Log_name            | File_size |

+---------------------+-----------+

| mysql-binlog.000001 |       177 |

| mysql-binlog.000002 |       177 |

| mysql-binlog.000003 |      1844 |

| mysql-binlog.000004 |       369 |

| mysql-binlog.000005 |       592 |

| mysql-binlog.000006 |      1065 |

| mysql-binlog.000007 |       615 |

| mysql-binlog.000008 |       217 |

| mysql-binlog.000009 |       194 |

+---------------------+-----------+

9 rows in set (0.00 sec)

查看mysql当前日志及状态

[mysql]>show master status;

+---------------------+----------+--------------+------------------+-------------------------------------------+

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                    |

+---------------------+----------+--------------+------------------+-------------------------------------------+

| mysql-binlog.000009 |      194 |              |                  | 9c21d663-e09d-11e8-b535-000c29899e07:1-13 |

+---------------------+----------+--------------+------------------+-------------------------------------------+

1 row in set (0.00 sec)

数据目录下的 mysql-bin.index 文件时二进制日志的索引文件，用来记录产生的二进制日志的序号。

binlog参数介绍

max_binlog_size: 制定单个binlog的最大值，默认1G；生产环境控制生成时间小于5分钟，建议调整为256MB。

binlog_cache_size :所有未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓存中的二进制日志写入binlog。每个新会话默认32K；每个线程开始一个事务自动分配一个缓存。缓冲区太小将会使用磁盘临时文件。可通过如下命令判断。

[mysql]>show global status like '%binlog_cache%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 0     |

| Binlog_cache_use      | 0     |

+-----------------------+-------+

主要看这两个值得使用情况；生产环境一般4MB。

binlog_format 代表二进制文件格式，有statement,row,mixed三种。

l   statement:基于操作的SQL语句记录到binlog中，简称SBR,5.1前的默认格式，节约IO，但有时master-slave数据不一致，废弃。

l   mixed:混合使用row和statement格式，不建议使用。

l   row:基于行的变更情况记录，记录变更前后的内容，简称RBR。不记录SQL语句上下文信息，只记录数据变更，任何情况下都可以被复制，但产生日志多。推荐使用。

mysql5.6后新增binlog_rows_query_log_events参数，可在row模式下看见完整sql语句。5.7默认关闭。

[mysql]>show variables like '%binlog_rows%';

+------------------------------+-------+

| Variable_name                | Value |

+------------------------------+-------+

| binlog_rows_query_log_events | OFF   |

+------------------------------+-------+

sync_binlog: 决定binlog的刷新机制。

expire_logs_days :binlog过期时间（天）；

binlog-do-db或binlog-ignore-db： 表示写入或忽略哪些库的日志，默认空；

log_slave_updates :配置m>s1>s2的架构时，需要在s1上设置为1，才能s1同步s2;

binlog_checksum: 对写入binlog进行校验，默认crc32.

binlog_row_imag 决定记录范围，默认full,记录全部记录；minimal只记录要修改列的信息；noblob记录除了blog和text的所有字段。

mysqlbinlog命令查看binlog示范

l   查看当前binlog format

[mysql]>show variables like '%binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

l   创建测试表t

CREATE TABLE `t` (

`id`  int(11) NOT NULL auto_increment,

`name`  varchar(10) DEFAULT NULL ,

`city`  varchar(10) DEFAULT NULL ,

PRIMARY KEY (`id`))

ENGINE=INNODB auto_increment=1 ;

l   插入数据

insert into t (name,city) values('zs','bj'),('zz','sh');

l   更新数据

update  t set name ='zs1' where name='zs';

l   复制binlog文件

# cp mysql-binlog.000009 /tmp/mysqlbinlog9bk

l   转换binlog

# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /tmp/mysqlbinlog9bk > /tmp/binlog.txt

参数解释:

-v 可以看到具体的执行信息；

--base64-output:把二进制日志文件转化格式；

l   查看转换后的文件，可看到更新前后的内容。

### UPDATE `mysql`.`t`

### WHERE

###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

###   @2='zs' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */

###   @3='bj' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */

### SET

###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

###   @2='zs1' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */

###   @3='bj' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */

1.3.   慢查询日志slow log  

把超过参数long_query_time时间的所有sql语句记录下来,5.7默认0.5；

查看slow log位置：[mysql]>show variables like '%slow_query_log_file%';

查看slow log的工具：percona-toolkit，参考p84

下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/

书中示例版本为3.0.3，官网当前最新版本为3.0.12,可下载对应系统的rpm包，或linux-generic通用版本，安装包7.8M。

下载后解压：

# tar -zxvf percona-toolkit-3.0.12_x86_64.tar.gz

通过如下命令生成慢SQL报告：

# pwd

/usr/local/percona-toolkit-3.0.12/bin

# ./pt-query-digest --since=24th /data/mysql/slow.log >slowquery.log

Invalid --since value at ./pt-query-digest line 13813.

安装rpm包同样的报错；后续再研究。

1.4.   全量日志general log

记录mysql 数据库所有操作的sql，包含select和show，默认关闭。

查看general log配置情况：

[mysql]>show variables like '%general%';

+------------------+---------------------------+

| Variable_name    | Value                     |

+------------------+---------------------------+

| general_log      | OFF                       |

| general_log_file | /data/mysql/localhost.log |

+------------------+---------------------------+

设置使重启数据库前生效：

[mysql]>set global general_log='ON';

日志内容：

[root@localhost mysql]# tail -f -n 100 localhost.log

/usr/local/mysql/bin/mysqld, Version: 5.7.24-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306  Unix socket: /tmp/mysql.sock

Time                 Id Command    Argument

2018-11-06T06:14:26.924983Z       67 Query  commit

2018-11-06T06:14:29.037015Z       67 Query  set global general_log='ON'

2018-11-06T06:14:32.715302Z       67 Query  show variables like '%general%'

2018-11-06T06:15:04.075786Z       67 Quit

log_output:全局动态变量 ，决定全量日志保存方式，该参数也决定了slow log的保存方式，可选：

l   File,默认文件存储，建议；

l   table:存放到新建表general_log表；

l   NONE:不保存；

1.5.   审计日志 audit log

数据库审计（简称DBAudit）能够实时记录网络上数据库活动，对数据库操作进行细粒度审计的合规性管理，对遭受到的风险行为进行告警，对攻击行为进行阻断。通过对用户访问数据库行为的记分析和汇报，用来帮助用户事后生成合规报、事故追根溯源，同时加强内外部数据库网络行为记录，提高数据资产安全。

企业版才可使用审计功能。可使用第三方开源插件libaudit_plugin.so在mysql5.7社区版中wa内存审计工作。参考p88

官方下载地址：https://bintray.com/mcafee/mysql-audit-plugin/

github:https://github.com/studywellnow/mysql-audit

书中版本：1.1.4-725   当前下载最新版本为1.1.6-784

1.5.1.   mysql-audit-plugin安装踩雷

l   解压插件包：

# unzip audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip

l   把解压好的插件复制到mysqllib库的插件目录下：

[root@localhost lib]# pwd

/orasoft/audit-plugin-mysql-5.7-1.1.6-784/lib

[root@localhost lib]# cp libaudit_plugin.so /usr/local/mysql/lib/plugin/

l   数据库命令行安装插件

INSTALL PLUGIN AUDIT SONANEM ‘libaudit_plugin.so’;

[mysql]>install plugin audit soname 'libaudit_plugin.so';

ERROR 1123 (HY000): Can't initialize function 'audit'; Plugin initialization function failed.

查github，当前版本支持到mysql 5.7.19，而当前数据库版本为5.7.24，看来不能使用了。

数据库日志提示：2018-11-06T06:48:10.122754Z 5 [Note] McAfee Audit Plugin: Couldn't find proper THD offsets for: 5.7.24-log

下面按书记录，有空再研究

l   查看插件功能是否开启；

show variables like ‘%audit%’;

l   开启插件功能：

set global audit_json_file=1;

l   查看日志

mysql数据目录下产生一个mysql-audit.json审计日志，可查操作SQL的用户名，IP。

1.6.   中继日志 relay log

主从复制中，从服务器IO线程将主服务器的binlog读取并记录到本地文件relay log中，然后从服务器SQL线程读取应用。