一、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时需要先删除原来的日志文件
三、错误日志
错误日志中记录项目：
相关变量
?
|
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 举报，一经查实，本站将立刻删除。
