MySQL 日志文件有一下几种：
- 错误日志（error logs）：It contains information about errors that occur while the server is running (also server start and stop)
- 一般日志（general logs）：This is a general record of what mysqld is doing (connect, disconnect, queries)
- 慢查询日志（slow query logs）：Ιt consists of “slow” SQL statements (as indicated by its name).
- 二进制日志（binlog）
- 重做日志（Redo Logs）
- 回滚日志（Undo Logs）
- Relay Logs
Error Logs
查看Error Log目录
MariaDB [(none)]> show variables like "log_error";
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
Enable Error Logs
Go to mysql conf file (/etc/mysql/my.cnf) and add following lines
[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log
[mysqld]
log_error=/var/log/mysql/mysql_error.log
如果想修改 Error Log 的路径，也可以直接修改/etc/mysql/my.cnf。
Demo
可以从下面Log看到，MySQL是由于 Out of memory。
更准确的说，当MySQL被初始化时，我们尝试为MySQL的 buffer pool分配128M的内存，但是失败了（cannot allocate memory for the buffer pool）。
$ cat /var/log/mariadb/mariadb.log
200120 23:18:06 mysqld_safe Number of processes running now: 0
200120 23:18:06 mysqld_safe mysqld restarted
200120 23:18:07 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 20856 ...
200120 23:18:07 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
200120 23:18:07 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
200120 23:18:07 InnoDB: The InnoDB memory heap is disabled
200120 23:18:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
200120 23:18:07 InnoDB: Compressed tables use zlib 1.2.7
200120 23:18:07 InnoDB: Using Linux native AIO
200120 23:18:07 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
200120 23:18:07 InnoDB: Completed initialization of buffer pool
200120 23:18:07 InnoDB: Fatal error: cannot allocate memory for the buffer pool
200120 23:18:07 [ERROR] Plugin 'InnoDB' init function returned error.
200120 23:18:07 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
200120 23:18:07 [Note] Plugin 'FEEDBACK' is disabled.
200120 23:18:07 [ERROR] Unknown/unsupported storage engine: InnoDB
200120 23:18:07 [ERROR] Aborting
200120 23:18:07 [Note] /usr/libexec/mysqld: Shutdown complete
200120 23:18:07 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
$ sudo systemctl restart mariadb.service
重启后log：
$ cat /var/log/mariadb/mariadb.log
200329 04:09:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
200329 4:09:47 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 13375 ...
200329 4:09:47 InnoDB: The InnoDB memory heap is disabled
200329 4:09:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
200329 4:09:47 InnoDB: Compressed tables use zlib 1.2.7
200329 4:09:47 InnoDB: Using Linux native AIO
200329 4:09:47 InnoDB: Initializing buffer pool, size = 128.0M
200329 4:09:47 InnoDB: Completed initialization of buffer pool
200329 4:09:47 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
200329 4:09:47 InnoDB: Waiting for the background threads to start
200329 4:09:48 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 4289175227
200329 4:09:48 [Note] Plugin 'FEEDBACK' is disabled.
200329 4:09:48 [Note] Server socket created on IP: '0.0.0.0'.
200329 4:09:48 [Note] Event Scheduler: Loaded 0 events
200329 4:09:48 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
General Logs
General Log 记录了服务器接收到的每一个查询或是命令。
无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来 ，记录的格式为 {Time ，Id ，Command，Argument }。
也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。 因此，MySQL 默认是把General log关闭的。 我们可以通过修改MySQL全局变量来开启General log功能或是更改日志存放路径。
查看 General Log 是否打开
查看 MySQL 是否启用了查询日志: show global variables like “%genera%”;
mysql> show global variables like "%genera%";
+----------------------------------------+----------------------+
| Variable_name | Value |
+----------------------------------------+----------------------+
| auto_generate_certs | ON |
| general_log | OFF |
| general_log_file | /tmp/mysql_query.log |
| sha256_password_auto_generate_rsa_keys | ON |
+----------------------------------------+----------------------+
4 rows in set (0.00 sec)
我这里是配置了日志输出文件：/tmp/mysql_query.log，并且日志功能关闭
Enable General Logs
Go to mysql conf file (/etc/mysql/my.cnf) and add following lines
general_log_file = /var/log/mysql/mysql.log
general_log = 1
MySQL打开general log日志后，所有的查询语句都可以在general log文件中输出，如果打开，文件会非常大，建议调试的时候打开，平时关闭。
开启方法
查看当前状态
mysql> show variables like 'general%';
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | OFF |
| general_log_file | /data/mysql/data/localhost.log |
+------------------+--------------------------------+
2 rows in set (0.00 sec)
开启方法 1
可以设置变量那样更改，1开启（0关闭），即时生效，不用重启，首选当然是通过这样的方式了。
log = /log/mysql_query.log路径
set global general_log=1;
#这个日志对于操作频繁的库,产生的数据量会很快增长,出于对硬盘的保护,可以设置其他存放路径
set global general_log_file=/tmp/general_log.log;
开启方法 2
也可以在my.cnf里添加,1开启（0关闭），当然了，这样要重启才能生效。
general-log = 1
查看 Logs
当执行 sql 语句时，你可以在log 文件中实时看到记录：
$ tail -f /usr/local/mysql/data/weishi-mac.log
/usr/local/mysql/bin/mysqld, Version: 5.7.28 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2019-11-17T07:57:40.619929Z 16 Query show databases
2019-11-17T07:57:53.393615Z 16 Query SELECT DATABASE()
2019-11-17T07:57:53.394086Z 16 Init DB test
2019-11-17T07:57:53.395022Z 16 Query show databases
2019-11-17T07:57:53.396496Z 16 Query show tables
2019-11-17T07:57:53.397506Z 16 Field List tb
2019-11-17T07:58:34.496162Z 16 Query SELECT DATABASE()
2019-11-17T07:58:34.497292Z 16 Init DB test
2019-11-17T07:58:39.336043Z 16 Query show databases
2019-11-17T07:59:28.009072Z 16 Query show databases
Slow Query Logs
Enable Slow Query Logs
Go to mysql conf file (/etc/mysql/my.cnf) and add following lines
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
查看一下与慢日志相关的全局变量：
mysql> show global variables like '%slow%';
+---------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/weishi-mac-slow.log |
+---------------------------+-------------------------------------------+
变量slow_launch_time的值代表着捕获所有执行时间超过2秒的查询。
slow log可以记录没有使用索引的查询。开启log_queries_not_using_indexes，将会记录没有使用索引的查询到slow日志里。
mysql> show global variables like '%not_using%';
+----------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
+----------------------------------------+-------+
Binary Log（二进制日志）
Refer to https://swsmile.info/post/mysql-binary-logs/
Redo Logs
Refer to https://swsmile.info/post/mysql-redo-logs/
Undo Logs
Refer to https://swsmile.info/post/mysql-undo-logs/
Relay Logs
Refer to https://swsmile.info/post/mysql-relay-logs/
事务日志
事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。
Reference
- https://dev.mysql.com/doc/refman/8.0/en/query-log.html
- https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html
- https://blog.51cto.com/arthur376/1853924
- https://blog.csdn.net/yumushui/article/details/40820763
- https://stackoverflow.com/questions/5441972/how-to-see-log-files-in-mysql
- https://blog.51cto.com/pangge/1319304
- https://www.jianshu.com/p/db19a1d384bc