美文网首页
MySQL-日志管理

MySQL-日志管理

作者: 文娟_狼剩 | 来源:发表于2019-08-14 21:24 被阅读0次

    1、错误日志

    1.1 作用

    记录MySQL启动及工作过程中,状态、报错、警告。

    1.2 怎么设置?

    1> 修改配置文件,并重启MySQL
    -- 配置日志
    vim /etc/my.cnf
    log_error=/data/3306/data/mysql.log   #这里的路径和文件名称可以随便定义
    
    -- 重启MySQL生效
    /etc/init.d/mysqld restart
    
    2> 查看错误日志
    wenjuan[(none)]>select @@log_error;
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    2
    Current database: *** NONE ***
    
    +---------------------------+
    | @@log_error               |
    +---------------------------+
    | /data/3306/data/mysql.log |
    +---------------------------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>
    

    1.3 如何查看错误日志?

    关注[ERROR]的上下文.

    2、二进制日志(重要)

    2.1 作用

    数据恢复必备的日志。
    主从复制依赖的日志。

    2.2 怎么设置?

    2.2.1 修改配置文件
    vim /etc/my.server_id
    server_id=6
    log_bin=/data/3306/binlog/mysql-bin
    
    说明:
    server_id      是5.7之后开二进制日志必加的参数
    log_bin=       /data/3306/binlog/    mysql-bin    
    打开二进制功能     指定存放路径          文件名前缀
    
    2.2.2 创建目录并授权
    [root@db01 /data/3306]# mkdir -p /data/3306/binlog/
    [root@db01 /data/3306]# chown -R mysql.mysql /data/3306/*
    
    2.2.3 重启数据库
    [root@db01 /data/3306]# /etc/init.d/mysqld restart 
    Shutting down MySQL.... SUCCESS! 
    Starting MySQL. SUCCESS! 
    
    [root@db01 /data/3306]# ll binlog/
    total 8
    -rw-r----- 1 mysql mysql 768 Aug 14 20:02 mysql-bin.000001
    -rw-r----- 1 mysql mysql  35 Aug 14 18:18 mysql-bin.index
    [root@db01 /data/3306]# 
    
    说明:
    mysql-bin    是在配置文件配置的前缀
    000001      MySQL每次重启,重新生成新的
    

    2.3 二进制日志记录了什么?

    2.3.1 引入

    除了查询类的语句,都会记录,即所有数据库变更类的语句。

    2.3.2 记录语句的种类

    DDL(数据定义语言):create、drop
    DCL(数据控制语言)
    DML(数据操作语言):insert、update、delete

    2.3.3 不同语句的记录格式说明

    DDL、DCL直接以语句(statement)方式记录 .
    DML语句有三种模式:SBR、RBR、MBR

    wenjuan[(none)]>select @@binlog_format;
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    2
    Current database: *** NONE ***
    
    +-----------------+
    | @@binlog_format |
    +-----------------+
    | ROW             |
    +-----------------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>
    
    说明:
    statement---->SBR:做什么记录什么,即SQL语句
    row---------->RBR:记录数据行的变化(默认模式,推荐)
    mixed-------->MBR:自动判断记录模式
    

    面试题:说明SBR和RBR的区别?

    区别项 SBR RBR(默认、推荐)
    记录内容 SQL语句 记录数据行的变化
    可读性 较强
    日志量
    日志记录准确性 数据误差 没有误差
    2.3.4 binlog events(二进制日志事件)

    1> 简介

    二进制日志内容以事件为最小记录单元。
    对于DDL和DCL,一个语句就是一个事件。
    对于DML(标准的事务语句),只记录已提交的事务的DML语句

    begin ;    事件1
    a          事件2
    b          事件3
    commit;    事件4
    

    2> 事件的构成(为了截取日志)

    [root@db01 /data/3306/binlog]# mysqlbinlog mysql-bin.000001 
    # at 219               事件开始的位置(position)
    end_log_pos 319        事件结束的位置(position)
    #190814 18:46:35       事件发生的时间
    create database oldboy 事件内容
    
    2.3.4 二进制日志的基础查看

    1> 查看二进制日志的配置信息

    wenjuan[(none)]>show variables like '%log_bin%';
    +---------------------------------+-----------------------------------+
    | Variable_name                   | Value                             |
    +---------------------------------+-----------------------------------+
    | log_bin                         | ON                                |
    | log_bin_basename                | /data/3306/binlog/mysql-bin       |
    | log_bin_index                   | /data/3306/binlog/mysql-bin.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)
    
    wenjuan[(none)]>
    
    说明:
    log_bin              开启二进制日志的开关
    log_bin_basename     位置
    sql_log_bin          临时开启或关闭二进制日志的小开关
    

    2> 查看二进制日志的基本信息
    (1)打印出当前MySQL的所有二进制日志,并且显示最后使用到的position

    -----
    wenjuan[(none)]>show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       154 |
    +------------------+-----------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>
    

    (2)查看当前正在使用的二进制日志

    show binary logs;
    show master status;(常用)

    wenjuan[(none)]>show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       154 |
    +------------------+-----------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 |      154 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>
    

    3> 查看二进制日志的事件信息

    wenjuan[(none)]>show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 |      154 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>
    
    wenjuan[(none)]>show binlog events in 'mysql-bin.000001';
    +------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
    +------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | mysql-bin.000001 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
    | mysql-bin.000001 | 123 | Previous_gtids |         6 |         154 |                                       |
    | mysql-bin.000001 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
    | mysql-bin.000001 | 219 | Query          |         6 |         322 | create database wwjtest               |
    | mysql-bin.000001 | 322 | Anonymous_Gtid |         6 |         387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
    | mysql-bin.000001 | 387 | Query          |         6 |         481 | create database heee                  |
    +------------------+-----+----------------+-----------+-------------+---------------------------------------+
    6 rows in set (0.00 sec)
    

    2.4 内容的查看和截取

    2.4.1 内容查看命令
    [root@db01 ~]# mysqlbinlog  /data/3306/binlog/mysql-bin.000003
    [root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/binlog/mysql-bin.000003
    
    说明:记不住参数可以去mysqlbinlog --help中查看
    
    2.4.2 日志的截取
    --start-position
    --stop-position
    
    语法:
    mysqlbinlog --start-position=xxx  --stop-position=xxx /data/3306/binlog/mysql-bin.000003>/data/bin.sql
    

    ================演练:===================
    1> 准备数据

    wenjuan[(none)]>create database binlog charset utf8mb4;
    Query OK, 1 row affected (0.00 sec)
    
    wenjuan[(none)]>use binlog;
    Database changed
    wenjuan[binlog]>create table t1(id int) engine=innodb charset=utf8mb4;
    Query OK, 0 rows affected (0.02 sec)
    
    wenjuan[binlog]>
    wenjuan[binlog]>insert into t1 values(1),(2),(3);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[binlog]>insert into t1 values(11),(12),(13);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[binlog]>commit;
    Query OK, 0 rows affected (0.01 sec)
    
    wenjuan[binlog]>
    
    wenjuan[binlog]>update t1 set id=10 where id>10;
    
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
    wenjuan[binlog]>commit;
    Query OK, 0 rows affected (0.01 sec)
    
    wenjuan[binlog]>
    
    wenjuan[binlog]>select * from t1;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |   10 |
    |   10 |
    |   10 |
    +------+
    6 rows in set (0.00 sec)
    
    wenjuan[binlog]>
    

    2> 搞破坏

    wenjuan[binlog]>drop database binlog;
    Query OK, 1 row affected (0.00 sec)
    
    wenjuan[(none)]>
    

    3> 数据恢复
    (1)确认起点和终点

    wenjuan[(none)]>show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000003 |     1610 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>
    
    wenjuan[(none)]>show binlog events in 'mysql-bin.000003';
    
    起点:
    | mysql-bin.000003 |  488 | Query          |         6 |         604 | create database binlog charset utf8mb4                              |
    
    终点:
    | mysql-bin.000003 | 1512 | Query          |         6 |        1610 | drop database binlog                                                |
    

    (2)截取日志

    [root@db01 ~]# mysqlbinlog --start-position=488  --stop-position=1512 /data/3306/binlog/mysql-bin.000003>/data/bin.sql
    

    (3)恢复日志

    wenjuan[(none)]>set sql_log_bin=0;   ##  临时关闭当前会话的binlog记录
    
    wenjuan[(none)]>source /data/bin.sql;
    wenjuan[(none)]>set sql_log_bin=1;   ##  打开当前会话的binlog记录
    

    2.5 基于gtid的binlog管理(扩展)

    2.5.1 什么是gtid(Global Transaction ID)?

    全局唯一的事务编号。
    幂等性。
    GtID包括两部分:
    Server_uuid:
    Tx_id:

    2.5.2 配置
    wenjuan[(none)]>show variables like '%gtid%';
    
    vim /etc/init.d/my.cnf
    
    gtid_mode=on                  ----开启开关
    enforce_gtid_consistency=true    ----强制GTID一致性
    log_slave_updates=1           ----主从复制中从库记录binlog,并统一GTID信息
    
    重启数据库:/etc/init.d/mysqld restart
    
    2.5.3 基于gtid截取日志

    对于DDL和DCL一个操作就是一个GTID。
    对于DML,一个完整的事务就是已给GTID。

    wenjuan[(none)]>wenjuan[(none)]>show master status;
    +------------------+----------+--------------+------------------+------------------------------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
    +------------------+----------+--------------+------------------+------------------------------------------+
    | mysql-bin.000005 |      489 |              |                  | 936b9a3f-b75a-11e9-bd16-000c290143b9:1-2 |
    +------------------+----------+--------------+------------------+------------------------------------------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>
    
    
    wenjuan[(none)]>wenjuan[(none)]>show binlog events in 'mysql-bin.000005';
    +------------------+-----+----------------+-----------+-------------+-------------------------
    | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                    
    +------------------+-----+----------------+-----------+-------------+-------------------------
    | mysql-bin.000005 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, 
    | mysql-bin.000005 | 123 | Previous_gtids |         6 |         154 |                         
    | mysql-bin.000005 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT=
    | mysql-bin.000005 | 219 | Query          |         6 |         308 | drop database dbtest    
    | mysql-bin.000005 | 308 | Gtid           |         6 |         373 | SET @@SESSION.GTID_NEXT=
    | mysql-bin.000005 | 373 | Query          |         6 |         489 | create database dbtest c
    +------------------+-----+----------------+-----------+-------------+-------------------------
    6 rows in set (0.00 sec)
    
    wenjuan[(none)]>
    
    2.5.4 基于gtid截取日志
    --include-gtids=       ----包含
    --exclude-gtids=       ------排除
    --skip-gtids
    
    截取1-3号事务:
    [root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-3' /data/binlog/mysql-bin.000009>/data/gtid.sql
    
    截取 1-10 gtid事务,跳过6号和8号事务.
    [root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10 --exclude-gtids='545fd699-be48-11e9-8f0a-000c2980e248:6,545fd699-be48-11e9-8f0a-000c2980e248:8'    /data/binlog/mysql-bin.000009>/data/gtid.sql
    
    2.5.5 演练

    1>准备环境

    wenjuan[(none)]>create database gtid charset utf8mb4;
    Query OK, 1 row affected (0.00 sec)
    
    wenjuan[(none)]>use gtid;
    Database changed
    wenjuan[gtid]>create table t1(id int) engine=innodb charset=utf8mb4;
    Query OK, 0 rows affected (0.02 sec)
    
    wenjuan[gtid]>insert into t1 values(1),(2),(3);
    Query OK, 3 rows affected (0.06 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[gtid]>commit;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[gtid]>insert into t1 values(11),(12),(13);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    wenjuan[gtid]>commit;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[gtid]>select * from t1;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |   11 |
    |   12 |
    |   13 |
    +------+
    6 rows in set (0.00 sec)
    
    wenjuan[gtid]>
    

    2>搞破坏

    wenjuan[gtid]>drop database gtid;
    Query OK, 1 row affected (0.01 sec)
    
    wenjuan[(none)]>
    

    3> 找起点和终端(gtid)

    wenjuan[(none)]>show master status;
    +------------------+----------+--------------+------------------+-------------------------------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
    +------------------+----------+--------------+------------------+-------------------------------------------+
    | mysql-bin.000008 |     1244 |              |                  | 936b9a3f-b75a-11e9-bd16-000c290143b9:1-11 |
    +------------------+----------+--------------+------------------+-------------------------------------------+
    1 row in set (0.00 sec)
    
    wenjuan[(none)]>show binlog events in 'mysql-bin.000008';
    | mysql-bin.000008 |  194 | Gtid           |         6 |         259 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:7'  |
    | mysql-bin.000008 |  259 | Query          |         6 |         369 | create database gtid charset utf8mb4                               |
    
    | mysql-bin.000008 | 1087 | Gtid           |         6 |        1152 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:11' |
    | mysql-bin.000008 | 1152 | Query          |         6 |        1244 | drop database gtid                                                 |
    

    4> 截取日志(仅供参考)

    [root@db01 ~]# mysqlbinlog  --skip-gtids --include-gtids='936b9a3f-b75a-11e9-bd16-000c290143b9:7-10' /data/3306/binlog/mysql-bin.000008>/data/gtid.sql
    

    4> 恢复数据

    wenjuan[(none)]>set sql_log_bin=0;
    Query OK, 0 rows affected (0.00 sec)
    
    wenjuan[(none)]>
    
    wenjuan[(none)]>source /data/gtid.sql
    

    2.6 二进制日志其他操作

    2.6.1 自动清理日志
    show variables like '%expire%';
    expire_logs_days  0  
    
    自动清理时间,是要按照全备周期+1
    set global expire_logs_days=8;
    永久生效:
    my.cnf
    expire_logs_days=15;
    企业建议,至少保留两个全备周期+1的binlog
    
    2.6.2 手工清理
    PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
    PURGE BINARY LOGS TO 'mysql-bin.000009';
    
    注意:不要手工 rm binlog文件
    \1. my.cnf binlog关闭掉,启动数据库
    2.把数据库关闭,开启binlog,启动数据库
    删除所有binlog,并从000001开始重新记录日志
    *reset master;     主从关系中,主库执行此操作,主从环境必崩 
    
    2.6.3 binlog 的滚动
    wenjuan[(none)]>flush logs;
    重启数据库
     select @@max_binlog_size;
    备份时,某些参数会触发
    

    3、慢日志(slow-log)

    3.1 简介

    记录运行较慢的语句记录slowlog中。
    功能是辅助优化的工具日志。
    应激性的慢---------->可以通过show processlist进行监控
    一段时间的慢------>可以进行slow记录、统计

    3.2 配置

    wenjuan[(none)]>show variables like '%slow_query%';
    wenjuan[(none)]>select @@long_query_time;
    wenjuan[(none)]>show variables like '%log_queries_not_using_indexes%';
    
    vim /etc/my.cnf
    slow_query_log=1
    slow_query_log_file=/data/3306/data/db01-slow.log
    long_query_time=0.1  默认配置10秒钟
    log_queries_not_using_indexes=1
    
    重启mysql:
    [root@db01 ~]# /etc/init.d/mysqld restart
    Shutting down MySQL.... SUCCESS! 
    Starting MySQL. SUCCESS! 
    [root@db01 ~]# 
    

    3.3 慢语句模拟

    set sql_log_bin=0;
    source /tmp/t100w.sql; 
    set sql_log_bin=1;
    

    3.4 分析处理慢语句

    [root@db01 ~]# mysqldumpslow -s c -t 5 /data/3306/data/db01-slow.log 
    
    参数:
        -t   top前几个
        -s   order的排序
    

    相关文章

      网友评论

          本文标题:MySQL-日志管理

          本文链接:https://www.haomeiwen.com/subject/jbonjctx.html