MySQL系列之十一|MySQL系列之十一日志记录 MySQL系列之十一日志记录

系列教程 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 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'; -- 是否记录二进制日志，默认ONMariaDB [(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 1end_log_pos 528992Querythread_id=29exec_time=0error_code=0use `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系列之十一|MySQL系列之十一日志记录】本篇文章就到这里了，希望可以给你带来一些帮助，也希望您能够多多关注脚本之家的更多内容！