日志类型

以Windows示例 系统变量参考https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

error_log

描述：记录MySQL在启动、运行或停止时出现的问题；
位置：my.ini中log-error的配置项
级别：log_error_verbosity=[1,2,3]

verbosity 作用
1 记录error messages
2 记录error and warning messages
3 记录error，warning,and note messages

相关指令

  1. #或使用指令查看
  2. mysql> show variables like 'log_error';
  3. +---------------+-----------------------+
  4. | Variable_name | Value                 |
  5. +---------------+-----------------------+
  6. | log_error     | .\DESKTOP-121VVI6.err |
  7. +---------------+-----------------------+
  8. 1 row in set, 1 warning (0.00 sec)
  11. # .当前目录->为数据目录
  12. mysql> select @@log_error;
  13. +-----------------------+
  14. | @@log_error           |
  15. +-----------------------+
  16. | .\DESKTOP-121VVI6.err |
  17. +-----------------------+
  18. 1 row in set (0.00 sec)
  20. # 查看日志级别
  21. mysql> select @@log_error_verbosity;
  22. +-----------------------+
  23. | @@log_error_verbosity |
  24. +-----------------------+
  25. |                     3 |
  26. +-----------------------+
  27. 1 row in set (0.00 sec)

general_log

记录所有发向MySQL的请求

位置：my.ini中general-log配置项
输出：log_output=[FILE | TABLE | NONE]
FILE：由my.ini中general_log_file配置
TABLE： mysql.general_log表中

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
|......                     |
+---------------------------+

mysql> show create table general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)

相关指令

mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> select @@general_log_file;
+---------------------+
| @@general_log_file  |
+---------------------+
| DESKTOP-121VVI6.log |
+---------------------+
1 row in set (0.00 sec)

# 开启日志记录
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)


#执行操作
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ......             |

mysql> use sampdb;
Database changed
mysql> select * from members\G
ERROR 1146 (42S02): Table 'sampdb.members' doesn't exist  
ERROR 1146 (42S02): Table 'sampdb.members' doesn't exist

mysql> show tables;
+------------------+
| Tables_in_sampdb |
+------------------+
| absence          |
| grade_event      |
| member           |
| president        |
| score            |
| .......          |

mysql> select * from member\G
*************************** 1. row ***************************
 member_id: 1
 last_name: Solow
first_name: Jeanne
    suffix: NULL
expiration: 2013-11-15
     email: jeanne_s@earth.com
    street: 16 Ludden Dr.
      city: Austin
     state: TX
       zip: 33347
     phone: 964-665-8735
 interests: Great Depression,Spanish-American War,Westward movement,Civil Rights,Sports
*************************** 2. row ***************************

###查看日志文件
C:\ProgramData\MySQL\MySQL Server 5.7\Data>type DESKTOP-121VVI6.log
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.21-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
2021-06-02T02:18:41.555347Z        58 Connect   root@localhost on  using TCP/IP
2021-06-02T02:18:41.556081Z        58 Query     select @@version_comment limit 1
2021-06-02T02:21:25.561430Z        58 Quit
2021-06-02T02:23:09.239145Z        57 Query     show databases
2021-06-02T02:23:24.991294Z        57 Query     SELECT DATABASE()
2021-06-02T02:23:24.991562Z        57 Init DB   sampdb
2021-06-02T02:23:29.819556Z        57 Query     select * from members
2021-06-02T02:23:36.115010Z        57 Query     show tables
2021-06-02T02:23:40.531785Z        57 Query     select * from member


######注意日志文件中时间差了8个时区

mysql> select @@log_timestamps;
+------------------+
| @@log_timestamps |
+------------------+
| UTC              |
+------------------+
1 row in set (0.00 sec)

mysql> set global log_timestamps='SYSTEM';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-06-02 10:34:26 |
+---------------------+
1 row in set (0.00 sec)

#####再次执行操作进行查看

slow_query_log

记录成功执行并符合条件的查询记录； 确定需要优化的SQL；

相关配置：
slow-query-log：慢查询日志开关
slow_query_log_file：慢查询日志
long_query_time：时长/秒
log-queries-not-using-indexes：没有使用索引的查询

相关指令

##设定慢查询时间

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> set global long_query_time=0.1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> quit
Bye

#######重新建立会话
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

mysql>

####查看慢查询状态

mysql> show variables like 'slow_query_log%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log      | ON                       |
| slow_query_log_file | DESKTOP-121VVI6-slow.log |
+---------------------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

####开启
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)


###mysqldumpslow指令的使用
参考https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
mysqldumpslow -s -r -t 10  "C:\ProgramData\MySQL\MySQL Server 5.7\Data\DESKTOP-121VVI6-slow.log"

binary_log

记录全部有效的数据修改日志 基于时间点的备份和恢复 主从复制->see 高可用MySQL chapter3

配置：my.ini中log-bin
相关指令：

##默认不开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)




mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set, 1 warning (0.00 sec)
## log_bin_basename  bin存储目录