MySQL 日志

事务日志 transaction log

MariaDB [(none)]> show variables like "%innodb_log%";
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_block_size     | 512     |
| innodb_log_buffer_size    | 8388608 |
| innodb_log_file_size      | 5242880 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+---------------------------+---------+

参数 innodb_flush_log_at_trx_commit

  • 选择参数2可以提高log io性能。
  • 高性能磁盘放置事务日志,或者单独的分区


    image.png

错误日志 error log

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
MariaDB [(none)]> show variables like "%log_error%";
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+

通用日志 general log

记录对数据库的通用操作,包括错误的SQL语句,默认关闭,一般用作排错,不用在生产环境

相关设置

  • general_log=ON|OFF
  • general_log_file=HOSTNAME.log
  • log_output=TABLE|FILE|NONE

慢查询日志 slow query log

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

  • slow_query_log=ON|OFF 开启或关闭慢查询
  • long_query_time=N 慢查询的阀值,单位秒,
  • slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
# 上述查询类型且查询时长超过long_query_time,则记录日志
  • log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描,不论
    是否达到慢查询阀值的语句是否记录日志,默认OFF,即不记录
  • log_slow_rate_limit = 1 多少次查询才记录,mariadb特有
  • log_slow_verbosity= Query_plan,explain 记录内容

利用profile分析sql查询语句

MariaDB [hellodb]> show variables like "%profiling";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| have_profiling | YES   |
| profiling      | OFF   |
+----------------+-------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> set profiling=ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
|        0 |
|        0 |
|        0 |
|        0 |
+----------+
4 rows in set (4.01 sec)

MariaDB [hellodb]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 4.00351854 | select sleep(1) from teachers |
+----------+------------+-------------------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000086 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000020 |
| After opening tables | 0.000005 |
| System lock          | 0.000004 |
| Table lock           | 0.000002 |
| After table lock     | 0.000005 |
| init                 | 0.000013 |
| optimizing           | 0.000008 |
| statistics           | 0.000029 |
| preparing            | 0.000009 |
| executing            | 0.000003 |
| Sending data         | 0.000030 |
| User sleep           | 1.000813 |
| User sleep           | 1.000540 |
| User sleep           | 1.000857 |
| User sleep           | 1.001032 |
| end                  | 0.000018 |
| query end            | 0.000010 |
| closing tables       | 0.000006 |
| freeing items        | 0.000004 |
| updating status      | 0.000015 |
| cleaning up          | 0.000002 |
+----------------------+----------+

数据库表整理

MariaDB [hellodb]> optimize table testlog;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| hellodb.testlog | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| hellodb.testlog | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+

二进制日志 binary log

二进制日志服务器变量:

sql_log_bin=ON|OFF  # 是否开启二进制文件
log_bin=/path/bin_log_file    # 指定二进制文件位置,写入配置文件
binlog_format=STATEMENT|ROW|MIXED  #二进制日志记录的格式,默认为STATEMENT,生产环境建议ROW格式
max_binlog_size=1073741824  #单个二进制文件最大大小为1G,超过回滚
sync_binlog=1|0  #是否启动二进制日志即时同步磁盘功能,默认为0
expire_logs_days=N    #二进制日志可以自动删除的天数。默认为0,不删除

查看二进制日志

#使用中的二进制日志文件列表,及大小
MariaDB [hellodb]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
# 使用中的二进制日志文件
MariaDB [hellodb]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      245 |              |                  |
+--------------------+----------+--------------+------------------+

# 查看二进制日志中的内容 
# SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
# 查看从23775889后面的log
show binlog events in 'mysql-bin.000001' from 23775889
# 查看从23775889后跳过2个看下3个记录
show binlog events in 'mysql-bin.000001' from 23775889 limit 2,3

二进制日志客户端工具mysqlbinlog

mysqlbinlog --start-position=67687 --stop-position=7527 /path/to/bin_log_file -v
mysqlbinlog --start-datetime="2010-03-30 23:12:00" --stop-datetime="2018-01-30 20:35:22" /path/to/bin_log_file/ -vvv
# 生成还原文件
mysqlbinlog binlogfile -v > /data/backup.sql
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成,浏览时请结合常识与多方信息审慎甄别。
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

友情链接更多精彩内容