MySQL/mariadb知识点——日志记录(1)
SQL命令历史
~/.mysql_history
记录了在MySQL中执行的命令历史
事务日志
transaction log：事务型存储引擎自行管理和使用
在一个事务提交后还没有存到磁盘的情况下会记录到事务日志，这个时候如果系统断电，再次开机后会自动将已提交的事务重做（redo log）写入磁盘；如果事务还没有提交的情况下断电重启后会执行撤销操作（undo log）
相关变量
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log%'; +-------------------------------+------------+ | Variable_name | Value | +-------------------------------+------------+ | innodb_log_arch_dir | | | innodb_log_arch_expire_sec | 0 | | innodb_log_archive | OFF | | innodb_log_block_size | 0 | | innodb_log_buffer_size | 16777216 | | innodb_log_checksum_algorithm | DEPRECATED | | innodb_log_checksums | ON | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_log_write_ahead_size | 8192 | +-------------------------------+------------+
其中：
innodb_log_block_size 块大小
innodb_log_buffer_size 缓存大小
innodb_log_file_size 每个日志文件大小
innodb_log_files_in_group 日志组成员个数，也就是有几个文件
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 [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_error'; +---------------+-----------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------+ | log_error | /data/mysqldb/localhost.localdomain.err | +---------------+-----------------------------------------+
log_warnings=1|0
默认值为1：是否记录警告信息至错误日志文件
查询日志
general log（通用日志）记录用户的操作日志，一般不建议启用
MariaDB [(none)]> SHOW VARIABLES LIKE 'general_log%'; +------------------+---------------+ | Variable_name | Value | +------------------+---------------+ | general_log | OFF | | general_log_file | localhost.log | +------------------+---------------+
其中：
general_log OFF：开启关闭通用日志，默认关闭
general_log_file centos7.log：通用日志文件的文件名，/var/lib/mysql/HOSTNAME.log
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+
其中
log_output FILE：以什么方式存放日志，(TABLE|FILE|NONE)
慢查询日志
记录执行查询时长超出指定时长的操作
相关变量
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
利用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.00006267 | SELECT * FROM STUDENTS | | 2 | 0.00034706 | SHOW DATABASES | | 3 | 0.00009898 | SELECT DATABASE() | | 4 | 0.00026200 | SHOW TABLES | | 5 | 0.00025329 | SELECT * FROM students | | 6 | 0.00072061 | SELECT stuid,name,age FROM students WHERE stuid=3 | +----------+------------+---------------------------------------------------+
查询指定编号的SQL语句的详细执行过程
MariaDB [hellodb]> SHOW profile FOR query 6; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000015 | | Waiting for query cache lock | 0.000002 | | init | 0.000002 | | checking query cache for query | 0.000210 | | checking permissions | 0.000008 | | Opening tables | 0.000014 | | After opening tables | 0.000003 | | System lock | 0.000003 | | Table lock | 0.000004 | | Waiting for query cache lock | 0.000012 | | init | 0.000056 | | optimizing | 0.000115 | | statistics | 0.000112 | | preparing | 0.000005 | | Unlocking tables | 0.000027 | | executing | 0.000003 | | Sending data | 0.000019 | | end | 0.000004 | | query end | 0.000005 | | closing tables | 0.000002 | | Unlocking tables | 0.000006 | | freeing items | 0.000045 | | updating status | 0.000005 | | Waiting for query cache lock | 0.000002 | | updating status | 0.000033 | | Waiting for query cache lock | 0.000003 | | updating status | 0.000001 | | storing result in query cache | 0.000003 | | cleaning up | 0.000002 | +--------------------------------+----------+
中继日志
relay log ：主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的事件
