MySQL系列之十一 日志记录

这篇文章主要介绍了MySQL日志文件详解,本文分别讲解了错误日志、二进制日志、通用查询日志、慢查询日志、Innodb的在线redo日志、更新日志等日志类型和作用介绍,需要的朋友可以参考下

一、SQL命令历史

~/.mysql_history记录了在mysql中执行的命令历史

二、事务日志

transaction log:事务型存储引擎自行管理和使用

在一个事务提交后还没有存到磁盘的情况下会记录到事务日志,这个时候如果系统断电,再次开机后会自动将已提交的事务重做(redo log)写入磁盘;如果事务还没有提交的情况下断电重启后会执行撤销操作(undo log)

相关变量

?

1

2

3

4

5

6

7

8

9

10

MariaDB [school]> SHOW VARIABLES LIKE 'innodb_log%';

+—————————+————+

| Variable_name | Value |

+—————————+————+

| innodb_log_block_size | 512 | — 块大小

| innodb_log_buffer_size | 8388608 | — 缓存大小

| innodb_log_file_size | 1073741824 | — 每个日志文件大小

| innodb_log_files_in_group | 3 | — 日志组成员个数,也就是有几个文件

| innodb_log_group_home_dir | ./ | — 事务文件路径,相对于数据目录

+—————————+————+

innodb_log_file_size默认大小为5M,innodb_log_files_in_group个数默认2个;强烈建议调大这俩个值;

如果事务操作的数据大于事务日志文件总大小,在执行ROLLBACK时:可以撤销,但是数据文件会被占用,需要使用OPTIMIZE TABLE命令来释放数据空间;

使用TRUNCATE删除表记录可以缩小占用空间,DELETE命令不会释放磁盘空间,如果需要释放需要使用OPTIMIZE命令从新整理碎片来释放空间。

注意:修改innodb_log_file_size时需要先删除原来的日志文件

三、错误日志

错误日志中记录项目:

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
  • 相关变量

    ?

    1

    2

    3

    4

    5

    6

    MariaDB [school]> SHOW GLOBAL VARIABLES LIKE 'log_error'; #错误日志文件的路径

    +—————+——————————+

    | Variable_name | Value |

    +—————+——————————+

    | log_error | /var/log/mariadb/mariadb.log |

    +—————+——————————+

    log_warnings=1|0默认值1(是):是否记录警告信息至错误日志文件

    四、查询日志

    记录用户的操作日志,一般不建议启用

    ?

    1

    2

    3

    4

    5

    6

    7

    MariaDB [school]> SHOW VARIABLES LIKE 'general_log%';

    +——————+————-+

    | Variable_name | Value |

    +——————+————-+

    | general_log | OFF | — 开启关闭通用日志,默认关闭

    | general_log_file | centos7.log | — 通用日志文件的文件名,/var/lib/mysql/HOSTNAME.log

    +——————+————-+

    ?

    1

    2

    3

    4

    5

    6

    MariaDB [school]> SHOW VARIABLES LIKE 'log_output';

    +—————+——-+

    | Variable_name | Value |

    +—————+——-+

    | log_output | FILE | — 以什么方式存放日志,(TABLE|FILE|NONE)

    +—————+——-+

    mysql.general_log:表存放查询日志,前提是以表方式存放的

    五、慢查询日志

    记录执行查询时长超出指定时长的操作

    1、慢查询相关变量

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    MariaDB [school]> SHOW VARIABLES LIKE 'slow_query%';

    +———————+——————+

    | Variable_name | Value |

    +———————+——————+

    | slow_query_log | OFF | — 开启或关闭慢查询日志

    | slow_query_log_file | centos7-slow.log | — 慢查询日志文件

    +———————+——————+

    MariaDB [school]> SHOW VARIABLES LIKE 'long_query_time';

    +—————–+———–+

    | Variable_name | Value |

    +—————–+———–+

    | long_query_time | 10.000000 | — 慢查询的阀值,单位秒,查询时间超过10则记录到慢查询日志

    +—————–+———–+

    MariaDB [school]> SHOW VARIABLES LIKE 'log_slow%';

    +———————+——-+

    | Variable_name | Value |

    +———————+——-+

    | log_slow_rate_limit | 1 | — 多少次查询才记录,mariadb特有

    | log_slow_verbosity | | — 记录内容的详细程度(Query_plan,explain)

    +———————+——-+

    MariaDB [school]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

    +——————————-+——-+

    | Variable_name | Value |

    +——————————-+——-+

    | log_queries_not_using_indexes | OFF | — 记录没有利用索引查询,而使用全文扫描的语句;默认关闭,建议开启

    +——————————-+——-+

    log_slow_filter :根据查询结果过滤

    • admin
    • filesort
    • filesort_on_disk
    • full_join
    • full_scan
    • query_cache
    • query_cache_miss
    • tmp_table
    • tmp_table_on_disk

    2、补充:利用profiling跟踪查询语句所用的详细时间

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    MariaDB [school]> SHOW VARIABLES LIKE 'profiling';

    +—————+——-+

    | Variable_name | Value |

    +—————+——-+

    | profiling | OFF | — 开启或关闭,默认关闭

    +—————+——-+

    MariaDB [school]> SET profiling=ON; #开启查询语句跟踪功能

    MariaDB [school]> SHOW profiles; #查询语句执行的时间列表

    +———-+————+————————————————————+

    | Query_ID | Duration | Query |

    +———-+————+————————————————————+

    | 1 | 0.00024497 | SELECT * FROM students |

    | 2 | 0.00038528 | SELECT stuid,name,age FROM students WHERE stuid = 2 |

    +———-+————+————————————————————+

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    MariaDB [school]> SHOW profile FOR query 2; #查询指定编号的SQL语句的详细执行过程

    +———————-+———-+

    | Status | Duration |

    +———————-+———-+

    | starting | 0.000035 |

    | Opening tables | 0.000003 |

    | After opening tables | 0.000006 |

    | query end | 0.000003 |

    | closing tables | 0.000002 |

    | freeing items | 0.000011 |

    | updating status | 0.000006 |

    | cleaning up | 0.000001 |

    +———————-+———-+

    六、二进制日志

    记录已提交事务导致数据改变或潜在导致数据改变的SQL语句,通过“重放”日志文件中的事件来生成数据副本,不依赖存储引擎类型。

    开启二进制日志,默认是关闭的,二进制日志和数据分开存放

    开启记录二进制文件的功能:在my.cnf的[mysqld]下加入log_bin[=/path/somefile]

    默认二进制日志在数据库目录下,mariadb-bin.000001为二进制日志数据文件,mariadb-bin.index为二进制日志索引文件

    1、记录二进制日志的三种方式:

    • 基于语句的记录方式,每一条改变数据的语句记录为一条语句,节省空间,系统默认为此模式,但是不推荐使用,会有隐藏的风险
    • 基于行的记录方式,每一行的改变都会将使其改变的语句记录为一条语句,日志量会很大,但是对数据的安全保护非常高
    • 混合模式:mixed, 让系统自行判定该基于哪种方式进行

    ?

    1

    2

    3

    4

    5

    6

    MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format';

    +—————+———–+

    | Variable_name | Value |

    +—————+———–+

    | binlog_format | STATEMENT | — 记录方式,系统默认基于语句方式

    +—————+———–+

    ?

    1

    SET binlog_format='ROW|STATEMENT|MIXED'; — 修改二进制日志记录方式

    2、二进制日志的相关变量

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    MariaDB [(none)]> SHOW MASTER|BINARY LOGS; — 查看mariadb自行管理使用中的二进制日志文件列表

    +——————–+———–+

    | Log_name | File_size |

    +——————–+———–+

    | mariadb-bin.000002 | 290 |

    | mariadb-bin.000003 | 264 |

    | mariadb-bin.000004 | 529038 |

    | mariadb-bin.000005 | 245 |

    +——————–+———–+

    MariaDB [(none)]> SHOW MASTER STATUS; — 查看使用中的二进制日志文件

    +——————–+———-+————–+——————+

    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

    +——————–+———-+————–+——————+

    | mariadb-bin.000005 | 245 | | |

    +——————–+———-+————–+——————+

    MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000004' FROM 1 LIMIT 2,3\\G — 查看二进制文件中的指定内容

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; — 是否记录二进制日志,默认ON

    MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin'; — 指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可

    MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size';

    +—————–+————+

    | Variable_name | Value |

    +—————–+————+

    | max_binlog_size | 1073741824 | — 单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G

    +—————–+————+

    MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog';

    +—————+——-+

    | Variable_name | Value |

    +—————+——-+

    | sync_binlog | 0 | — 设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘

    +—————+——-+

    MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';

    +——————+——-+

    | Variable_name | Value |

    +——————+——-+

    | expire_logs_days | 0 | — 二进制日志可以自动删除的天数。 默认为0,即不自动删除

    +——————+——-+

    3、mysqlbinlog命令

    • 二进制日志的客户端命令工具
    • –start-position=# 指定开始位置
    • –stop-position=# 指定结束位置
    • –start-datetime=(YYYY-MM-DD hh:mm:ss) 指定开始时间
    • –stop-datetime=(YYYY-MM-DD hh:mm:ss) 指定结束时间
    • –base64-output=decode-row
    • -v |-vv |-vvv |-vvvv 显示详细信息

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    [root@centos7 mysql]# mysqlbinlog –start-position=528864 –stop-position=529019 mariadb-bin.000004 –base64-output=decode-row -v

    # at 528864

    #180611 20:59:46 server id 1 end_log_pos 528992 Query thread_id=29 exec_time=0 error_code=0

    use `school`/*!*/;

    SET TIMESTAMP=1528721986/*!*/;

    INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M') #改变数据的SQL语句

    /*!*/;

    事件发生的日期和时间:180611 20:59:46

    事件发生的服务器标识:server id 1

    事件的结束位置:end_log_pos 528992

    事件的类型:Query

    事件发生时所在服务器执行此事件的线程的ID:thread_id=29

    语句的时间戳与将其写入二进制文件中的时间差:exec_time=0

    错误代码:error_code=0

    事件内容:

    GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID

    使用mysqlbinlog mariadb-bin.000001 > file.sql命令重定向到SQL文件中,再使用mysql -uroot -p < file.sql命令可以直接导入数据,实现了备份还原的功能

    4、二进制日志的管理

    ?

    1

    2

    3

    4

    5

    MariaDB [(none)]> PURGE BINARY LOGS TO 'mariadb.000002'; — 删除到02,注意:02不删除

    MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2018-01-23'; — 删除2018-01-23之前的日志

    MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

    MariaDB [(none)]> RESET MASTER; — 删除所有二进制日志,index文件重新记数

    MariaDB [(none)]> FLUSH LOGS; — 手动触发日志滚动

    七、中继日志

    relay log :主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取到的事件

    总结

    本篇文章就到这里了,希望可以给你带来一些帮助,也希望您能够多多关注钦钦技术栈的更多内容!

    原文链接:https://www.cnblogs.com/L-dongf/p/9172008.html

    版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/6535/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

    (0)
    上一篇 2022-07-28 12:13:32
    下一篇 2022-07-28 12:13:43

    软件定制开发公司

    相关阅读

    发表回复

    登录后才能评论
    通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!