MySQL 日志管理 (一)

错误日志(log_error)

记录启动关闭日常运行过程中,状态信息,警告,错误
默认就是开启的: /数据路径下/hostname.err
配置:

vi /etc/my.cnf
log_error=/var/log/mysql.log
log_timestamps=system
# 重启生效

查看方式

select @@log_error;
+---------------------+
| @@log_error         |
+---------------------+
| /var/log/mysqld.log |
+---------------------+

show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+

binlog(binary logs):二进制日志

作用

(1)备份恢复必须依赖二进制日志
(2)主从环境必须依赖二进制日志

binlog配置 (5.7必须加server_id)

# 是否开启 log_bin 日志
select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

# 日志的名字及位置
select @@log_bin_basename;
+--------------------------+
| @@log_bin_basename       |
+--------------------------+
| /var/lib/mysql/mysql-bin |
+--------------------------+

# 服务ID号
select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+

# 二进制日志格式:
select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+

# 双一标准之二:
select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+

binlog记录了什么

binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。
记录SQL语句种类：
(1) DDL ：原封不动的记录当前DDL(statement语句方式)。
(2) DCL ：原封不动的记录当前DCL(statement语句方式)。
(3) DML ：只记录已经提交的事务DML

DML三种记录方式
binlog_format（binlog的记录格式）参数影响
(1) statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML。
(2) ROW(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)
(3) mixed（混合）MBR(mixed based replication)模式 ：以上两种模式的混合

binlog的记录格式对比：
STATEMENT：可读性较高，日志量少，但是不够严谨，例如：语句中有 now()
ROW ：可读性很低，日志量大，足够严谨
建议使用：row记录模式

event（事件）是什么

事件的简介

二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
begin;      120  - 340
DML1        340  - 460
DML2        460  - 550
commit;     550  - 760

event的组成

三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
位置号的作用是什么？为了方便我们截取事件

日志文件查看

根据 log_bin 参数设置的路径,可以找到二进制日志

show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

查看一共多少个binlog

show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |      1476 |
| mysql-bin.000003 |       217 |
| mysql-bin.000004 |       376 |
| mysql-bin.000005 |       257 |
| mysql-bin.000006 |       416 |
| mysql-bin.000007 |       257 |
| mysql-bin.000008 |       257 |
| mysql-bin.000009 |       257 |
| mysql-bin.000010 |       257 |
| mysql-bin.000011 |       722 |
| mysql-bin.000012 |       257 |
| mysql-bin.000013 |      1663 |
| mysql-bin.000014 |       257 |
| mysql-bin.000015 |       257 |
| mysql-bin.000016 |       530 |
| mysql-bin.000017 |       530 |
| mysql-bin.000018 |       234 |
+------------------+-----------+

可以使用下面语句来刷新 bin_log 日志

flush logs;

查看mysql正在使用的日志文件

# file：当前MySQL正在使用的文件名
# Position：最后一个事件的结束位置号
show master status;

日志内容查看

event查看

show binlog events in 'mysql-bin.000006';

Log_name：    bin_log 文件名
Pos：         开始的position    *****
Event_type：  事件类型
Format_desc： 格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：   mysql 服务号标识
End_log_pos： 事件的结束位置号 *****
Info：        事件内容*****
补充:
SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]
eg: mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop

# 获取前 5 条记录
show binlog events in 'mysql-bin.000013' limit 5;

# 从第 5 条记录开始，获取 5 条记录
show binlog events in 'mysql-bin.000013' limit 5,5;

# 从位置 911 开始，获取 3 条记录
show binlog events in 'mysql-bin.000013' from 911 limit 3;

binlog文件内容详细查看

mysqlbinlog  /var/lib/mysql/mysql-bin.00001

mysqlbinlog --base64-output=decode-rows -vvv /var/lib/mysql/mysql-bin.00001

# 查看指定库的日志
mysqlbinlog  -d klvchen /var/lib/mysql/mysql-bin.00001

# 指定时段查看日志
mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00'  /var/lib/mysql/mysql-bin.000001

基于 Position 号进行日志截取

核心就是找截取的起点和终点
--start-position=321
--stop-position=513

mysqlbinlog --start-position=219 --stop-position=1347 /var/lib/mysql/mysql-bin.000013 >/tmp/bin.sql