MySQL系列之十一日志记录

程序员文章站 2022-06-18 14:33:51

系列教程mysql系列之开篇 mysql关系型数据库基础概念 mysql系列之一 mariadb-server安装 mysql系列之二多实例配置 mysql系列之三基础篇 mysql系列之四 sq...

系列教程

mysql系列之开篇 mysql关系型数据库基础概念
 mysql系列之一 mariadb-server安装
 mysql系列之二多实例配置
 mysql系列之三基础篇
 mysql系列之四 sql语法
 mysql系列之五视图、存储函数、存储过程、触发器
 mysql系列之六用户与授权
 mysql系列之七 mysql存储引擎
 mysql系列之八 mysql服务器变量
 mysql系列之九 mysql查询缓存及索引
 mysql系列之十 mysql事务隔离实现并发控制
 mysql系列之十一日志记录
 mysql系列之十二备份与恢复
 mysql系列之十三 mysql的复制
 mysql系列之十四 mysql的高可用实现
 mysql系列之十五 mysql常用配置和性能压力测试

一、sql命令历史

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

二、事务日志

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

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

相关变量

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时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

四、查询日志

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

mariadb [school]> show variables like 'general_log%';
+------------------+-------------+
| variable_name    | value       |
+------------------+-------------+
| general_log      | off         | -- 开启关闭通用日志，默认关闭
| general_log_file | centos7.log | -- 通用日志文件的文件名，/var/lib/mysql/hostname.log
+------------------+-------------+

mariadb [school]> show variables like 'log_output';  
+---------------+-------+
| variable_name | value |
+---------------+-------+
| log_output    | file  | -- 以什么方式存放日志，(table|file|none)
+---------------+-------+

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

五、慢查询日志

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

1、慢查询相关变量

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` 跟踪查询语句所用的详细时间

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        |
+----------+------------+------------------------------------------------------------+

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, 让系统自行判定该基于哪种方式进行

mariadb [(none)]> show variables like 'binlog_format';  
+---------------+-----------+
| variable_name | value     |
+---------------+-----------+
| binlog_format | statement | -- 记录方式，系统默认基于语句方式
+---------------+-----------+

set binlog_format='row|statement|mixed'; -- 修改二进制日志记录方式

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

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 -- 查看二进制文件中的指定内容

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 显示详细信息

[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、二进制日志的管理

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 ：主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的事件

总结

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

MySQL系列之十一日志记录

系列教程

一、sql命令历史

二、事务日志

三、错误日志

四、查询日志

五、慢查询日志

1、慢查询相关变量

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

六、二进制日志

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

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

3、mysqlbinlog命令

4、二进制日志的管理

七、中继日志

总结

死磕 java同步系列之mysql分布式锁

自己动手写MiniBBS系列（MVC篇）之远程链接mysql

MySQL慢查询之pt-query-digest分析慢查询日志

mysql 开发进阶篇系列 33 工具篇(mysqlbinlog日志管理工具)

MySQL之SQL优化实战记录

MySQL 日志系统之 redo log 和 binlog

MySQL问答系列之什么情况下会用到临时表

mysql 开发进阶篇系列 40 mysql日志之二进制日志下以及查询日志

MySQL问答系列之如何避免ibdata1文件大小暴涨

[系列] go-gin-api 路由中间件 - 日志记录（三）

MySQL系列之十一 日志记录

系列教程

一、sql命令历史

二、事务日志

三、错误日志

四、查询日志

五、慢查询日志

1、慢查询相关变量

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

六、二进制日志

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

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

3、mysqlbinlog命令

4、二进制日志的管理

七、中继日志

总结

死磕 java同步系列之mysql分布式锁

自己动手写MiniBBS系列（MVC篇）之远程链接mysql

MySQL慢查询之pt-query-digest分析慢查询日志

mysql 开发进阶篇系列 33 工具篇(mysqlbinlog日志管理工具)

MySQL之SQL优化实战记录

MySQL 日志系统之 redo log 和 binlog

MySQL问答系列之什么情况下会用到临时表

mysql 开发进阶篇系列 40 mysql日志之二进制日志下以及查询日志

MySQL问答系列之如何避免ibdata1文件大小暴涨

[系列] go-gin-api 路由中间件 - 日志记录（三）

MySQL系列之十一日志记录

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