MySQL日志管理之事务日志和错误日志

日志的概念：

MySQL的日志是用来记录用户的相关操作、系统的运行状态等信息的文件结合。

MySQL的事务日志：transaction log

事务日志就是mysql执行事务过程中产生的日志。

mysql事务日志的分类：

• redo log：基于WAl机制在数据更改前先把对应操作记录下来。

• undo log：记录和日志操作相反的操作。（例如撤销以前的操作）

注意：

• 事务日志一般是存储引擎自行管理和使用的，默认存放在mysql数据文件夹里面的。

• 因为事务日志是追加写入的，所以推荐将事务日志单独放在一个磁盘上面。

innodb事务日志的相关配置：

• 查看和日志相关的信息（日志大小、所在位置、有几个日志等）

mysql> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 | --- #每个日志文件大小（字节为单位）
| 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     |
+-----------------------------+----------+
7 rows in set (0.00 sec)

[root@CentOS8 mysql]# ll -h /var/lib/mysql/
total 121M
-rw-r----- 1 mysql mysql   56 Sep 16 23:36 auto.cnf
-rw------- 1 mysql mysql 1.7K Sep 16 23:36 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Sep 16 23:36 ca.pem
-rw-r--r-- 1 mysql mysql 1.1K Sep 16 23:36 client-cert.pem
-rw------- 1 mysql mysql 1.7K Sep 16 23:36 client-key.pem
-rw-r----- 1 mysql mysql  277 Sep 16 23:49 ib_buffer_pool
-rw-r----- 1 mysql mysql  12M Sep 16 23:49 ibdata1
-rw-r----- 1 mysql mysql  48M Sep 16 23:49 ib_logfile0
-rw-r----- 1 mysql mysql  48M Sep 16 23:36 ib_logfile1
-rw-r----- 1 mysql mysql  12M Sep 16 23:49 ibtmp1
drwxr-x--- 2 mysql mysql 4.0K Sep 16 23:36 mysql
srwxrwxrwx 1 mysql mysql    0 Sep 16 23:49 mysql.sock
-rw------- 1 mysql mysql    6 Sep 16 23:49 mysql.sock.lock
drwxr-x--- 2 mysql mysql 4.0K Sep 16 23:36 performance_schema
-rw------- 1 mysql mysql 1.7K Sep 16 23:36 private_key.pem
-rw-r--r-- 1 mysql mysql  452 Sep 16 23:36 public_key.pem
-rw-r--r-- 1 mysql mysql 1.1K Sep 16 23:36 server-cert.pem
-rw------- 1 mysql mysql 1.7K Sep 16 23:36 server-key.pem
drwxr-x--- 2 mysql mysql  12K Sep 16 23:36 sys

优化建议：

• 调整日志文件的个数和单个日志文件的大小

• 将事务日志等文件迁移到一个新的分区中，和其他文件分开，因为事务日志文件写入方式是追加写入的。放在独立的分区中使得数据存放连续，可以减少磁盘的碎片。

事务日志的优化；

MySQL写文件有两块缓存，一块是MySQL的缓冲区，还有一块是操作系统内核的缓冲区。

• 通过 innodb_flush_log_at_trx_commit 定义事务的提交方式：

innodb_flush_log_at_trx_commit=0 --- 1S写一次磁盘

每隔一秒把MySQL缓冲区（log buffer）刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。

也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据


innodb_flush_log_at_trx_commit=1：只要提交数据就把数据写入磁盘，但是会带来比较多的ip操作。

表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。

这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升


innodb_flush_log_at_trx_commit=2 --- 1S写一次磁盘

表示在每次事务提交的时候会把mysql缓冲区（log buffer）刷到文件系统（os buffer）中去，但并不会立即刷写到磁盘。

如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。

# mysql崩溃的概率比操作系统崩溃的概率高，所以安全性比0要强一点

优化建议：

高并发业务行业最佳实践，是使用第三种折衷配置（=2）

MySQL错误日志

MySQL错误日志记录的就是mysql启动、运行过程中产生的错误信息。

MySQL错误日志的存放位置：

查看MySQL错误日志的存放位置：

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';

例如：

mysql5.6：
mysql> show variables like 'log_error';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| log_error     | ./CentOS8.cc.err |
+---------------+------------------+
1 row in set (0.01 sec)

mysql5.7：
mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)


mysql8.0：
mysql> show global variables like 'log_error';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_error     | /var/log/mysql/mysqld.log |
+---------------+---------------------------+
1 row in set (0.01 sec)

注意：（MySQL5.6）

• 默认情况下，错误日志存储在mysql数据库的数据文件中

• 错误日志文件通常的名称为hostname.err

MySQL的三个目录：
二进制/源码包目录:网上下载的安装包存放位置

mysql的数据存放目录：MySQL数据存放位置

mysql程序存放目录：MySQL应用程序的安装位置

更改mysql错误日志的存放路径：

log-err是定义是否启用错误日志的功能和错误日志的存储位置

例如：

[root@CentOS8 mysql]# cat /etc/my.cnf | grep -v -E '^#';

[mysqld]
datadir=/var/lib/mysql
log-error=/var/log/mysqld.log

mysql记录哪些警告信息至错误日志文件

log_warnings用于标识警告信息是否一并记录到错误日志中

• log_warnings的值为0，表示不记录警告信息。

• log_warnings的值为1，表示警告信息一并记录到错误日志中。

• log_warnings的值大于1，表示”失败的连接”的信息和创建新连接时”拒绝访问”类的错误信息也会被记录到错误日志中。

mysq5.6默认值：
mysql> SHOW GLOBAL VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql8.0或5.7默认值：
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_error_verbosity | 2     |
+---------------------+-------+
1 row in set (0.00 sec)

注意：mysql8.0将log_warnings变成了long_error_verbosity