MySQL系列之十一 日志记录

 更新时间:2021年7月3日 10:00  点击:2209

系列教程

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时需要先删除原来的日志文件

三、错误日志

错误日志中记录项目:

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

相关变量

MariaDB [school]> SHOW GLOBAL VARIABLES LIKE 'log_error';  #错误日志文件的路径
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+

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

四、查询日志

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

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

总结

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

[!--infotagslink--]

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
  • Laravel 调试工具 laravel-debugbar 打印日志消息

    laravel-debugbar 调试工具的教程小编整理了几篇不错的教程,今天我们来看一篇Laravel 调试工具 laravel-debugbar 打印日志消息例子,希望文章对各位有帮助。 其实不...2016-11-25
  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15
  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
  • mysql的3种分表方案

    一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
  • 对MySQL日志操作的一些基本命令总结

    MySQL日志主要包含:错误日志、查询日志、慢查询日志、事务日志、二进制日志;日志是mysql数据库的重要组成部分。日志文件中记录着mysql数据库运行期间发生的变化;也就是说用来记录mysql数据库的客户端连接状况、SQL语句...2015-11-24
  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
  • Powershell 查询 Windows 日志的方法

    这篇文章主要介绍了Powershell 查询 Windows 日志的方法,需要的朋友可以参考下...2020-06-30
  • MySQL数据库备份还原方法

    MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
  • Mysql命令大全(详细篇)

    一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
  • Mysql中 show table status 获取表信息的方法

    这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12
  • 基于PostgreSQL和mysql数据类型对比兼容

    这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
  • 20分钟MySQL基础入门

    这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
  • RHEL6.5编译安装MySQL5.6.26教程

    一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21