mysql 普通日志和错误日志

谁践踏了优雅 2024-04-08 08:38 75阅读 0赞

## 普通日志 ##

记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来 ，记录的格式为 \{Time ，Id ，Command，Argument \}。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。 因此，Mysql默认是把General log关闭的。

### 开启普通日志 ###

vim /etc/my.cnf

general-log= ON
    general-log-file=/var/lib/mysql/lys-mysql.log

### 日志输出，不包含错误日志信息 ###

![在这里插入图片描述][55847b970ee24b818323e5ee58114208.png]

## 错误日志 ##

错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出  
新参数log\_error\_verbosity更简单，它有三个可选值， 分别对应：

*  1.  错误信息；
 *  1.  错误信息和告警信息；
 *  1.  错误信息、告警信息和通知信息  
        ![在这里插入图片描述][d822adaa709a4b4bb76e9b77f4ca58f4.png]

### 开启错误日志 ###

log-error=/var/log/mysqld.log
    log-error-verbosity=3

### 错误日志输出 ###

2022-08-24T03:36:04.297163Z 1 [Note] [MY-012203] [InnoDB] Directories to scan './'
    2022-08-24T03:36:04.297313Z 1 [Note] [MY-012204] [InnoDB] Scanning './'
    2022-08-24T03:36:04.354017Z 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 7 files.
    2022-08-24T03:36:04.356027Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
    2022-08-24T03:36:04.378810Z 1 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
    2022-08-24T03:36:04.383834Z 0 [Note] [MY-011952] [InnoDB] If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2022-08-24T03:36:04.385086Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
    2022-08-24T03:36:04.386061Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
    2022-08-24T03:36:04.522167Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
    2022-08-24T03:36:04.522240Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 4
    2022-08-24T03:36:04.522283Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
    2022-08-24T03:36:04.522330Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
    2022-08-24T03:36:04.523130Z 1 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 19016291, whereas checkpoint_lsn = 19016473 and start_lsn = 19016192
    2022-08-24T03:36:04.530887Z 1 [Note] [MY-013083] [InnoDB] Log background threads are being started...
    2022-08-24T03:36:04.532461Z 1 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
    2022-08-24T03:36:04.532506Z 1 [Note] [MY-012535] [InnoDB] Apply batch completed!
    2022-08-24T03:36:04.532871Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
    2022-08-24T03:36:04.534499Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
    2022-08-24T03:36:04.536334Z 1 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
    2022-08-24T03:36:04.536415Z 1 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 9229
    2022-08-24T03:36:04.547986Z 1 [Note] [MY-013776] [InnoDB] Parallel initialization of rseg complete
    2022-08-24T03:36:04.548063Z 1 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 4 thread: 11667 ms.
    2022-08-24T03:36:04.548201Z 1 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
    2022-08-24T03:36:04.548342Z 1 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    2022-08-24T03:36:04.614066Z 1 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
    2022-08-24T03:36:04.614355Z 1 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
    2022-08-24T03:36:04.661191Z 1 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
    2022-08-24T03:36:04.661568Z 1 [Note] [MY-012976] [InnoDB] 8.0.28 started; log sequence number 19016483
    2022-08-24T03:36:04.665114Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    2022-08-24T03:36:04.677229Z 1 [Note] [MY-011089] [Server] Data dictionary restarting version '80023'.
    2022-08-24T03:36:04.784260Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
    2022-08-24T03:36:04.785579Z 1 [Note] [MY-012356] [InnoDB] Scanned 9 tablespaces. Validated 9.
    2022-08-24T03:36:04.808819Z 1 [Note] [MY-010006] [Server] Using data dictionary with version '80023'.
    2022-08-24T03:36:04.818071Z 0 [Note] [MY-011332] [Server] Plugin mysqlx reported: 'IPv6 is available'
    2022-08-24T03:36:04.818371Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. bind-address: '::' port: 33060'
    2022-08-24T03:36:04.818407Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. socket: '/var/run/mysqld/mysqlx.sock''
    2022-08-24T03:36:04.839128Z 0 [Note] [MY-010902] [Server] Thread priority attribute setting in Resource Group SQL shall be ignored due to unsupported platform or insufficient privilege.
    2022-08-24T03:36:04.883430Z 0 [Note] [MY-012487] [InnoDB] DDL log recovery : begin
    2022-08-24T03:36:04.883595Z 0 [Note] [MY-012488] [InnoDB] DDL log recovery : end
    2022-08-24T03:36:04.883858Z 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    2022-08-24T03:36:04.884320Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 220824  3:36:04
    2022-08-24T03:36:04.891314Z 0 [Note] [MY-012922] [InnoDB] Waiting for purge to start
    2022-08-24T03:36:04.961448Z 0 [Note] [MY-010182] [Server] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
    2022-08-24T03:36:04.961507Z 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory.
    2022-08-24T03:36:04.963109Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
    2022-08-24T03:36:04.963182Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
    2022-08-24T03:36:04.963248Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
    2022-08-24T03:36:04.963272Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
    2022-08-24T03:36:04.963515Z 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 3306
    2022-08-24T03:36:04.963572Z 0 [Note] [MY-010253] [Server] IPv6 is available.
    2022-08-24T03:36:04.963594Z 0 [Note] [MY-010264] [Server]   - '::' resolves to '::';
    2022-08-24T03:36:04.963631Z 0 [Note] [MY-010251] [Server] Server socket created on IP: '::'.
    2022-08-24T03:36:04.989908Z 0 [Note] [MY-011025] [Repl] Failed to start slave threads for channel ''.
    2022-08-24T03:36:04.992371Z 5 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 5
    2022-08-24T03:36:04.993972Z 0 [Note] [MY-011240] [Server] Plugin mysqlx reported: 'Using SSL configuration from MySQL Server'
    2022-08-24T03:36:04.994464Z 0 [Note] [MY-011243] [Server] Plugin mysqlx reported: 'Using OpenSSL for TLS connections'
    2022-08-24T03:36:04.994806Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
    2022-08-24T03:36:04.994810Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

## 慢日志 ##

### 开启慢日志 ###

vim /etc/my.cnf

long-query-time=10
    slow-query-log=on
    slow-query-log-file=/var/lib/mysql/lys-mysql-slow.log

执行sql

select sleep(11);
    select *,sleep(11) from db01.tb_user;

### 慢日志输出 ###

/usr/sbin/mysqld, Version: 8.0.28 (MySQL Community Server - GPL). started with:
    Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
    Time                 Id Command    Argument
    # Time: 2022-08-24T03:36:37.325849Z
    # User@Host: user0001[user0001] @ host-10-6-8-238.openstacklocal [10.6.8.238]  Id:     8
    # Query_time: 11.000655  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
    use db01;
    SET timestamp=1661312186;
    select sleep(11);
    # Time: 2022-08-24T03:37:25.988992Z
    # User@Host: user0001[user0001] @ host-10-6-8-238.openstacklocal [10.6.8.238]  Id:     8
    # Query_time: 44.006414  Lock_time: 0.000017 Rows_sent: 4  Rows_examined: 4
    SET timestamp=1661312201;
    select *,sleep(11) from db01.tb_user;

[55847b970ee24b818323e5ee58114208.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/cc2820a62db64e598f68f9dfbfb9edd2.png
[d822adaa709a4b4bb76e9b77f4ca58f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/4fd4cfdbcbab412597989c6ca2ab67fa.png