MySQL（六）

MySQL总结（六）日志

通用查询日志（General query log）

mysql> show variables like '%general%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /var/lib/mysql/Lang01.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)

默认是关闭的。主要记录用户的所有操作 ，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给服务器的所有 SQL 指令等。

Time                 Id Command    Argument
2023-07-03T11:39:15.084195Z         9 Query     SELECT * FROM employees
2023-07-03T11:40:43.317476Z         9 Query     SET global  general_log = off

set global general_log = on / off # 开启或关闭

set global general_log_file = 'path' #文件保存位置

慢查询日志（slow query log）

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/Lang01-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.01 sec)

慢查询的阈值

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 | # 10s
+-----------------+-----------+
1 row in set (0.01 sec)

mysql > set global long_query_time = 1; #修改

错误日志（error log）

mysql> show variables like 'log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
4 rows in set (0.01 sec)

可以用vim直接打开

==二进制日志（bin log）==

mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.01 sec)

binlog日志的格式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

一共有三种

Statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

Row：不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

Mixed：实际上就是Statement与Row的，自动判断用哪种记录

写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。binlog日志刷盘流程如下：

• 上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快
• 上图的fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。

sync_binlog = 0，每次事务提交就会write（写入page cache）

sync_binlog = 1，表示每次提交事务都会执行fsync

sync_binlog = N（N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

binlog 和 redo的区别

• redo log 它是 物理日志 ，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
• 而 binlog 是 逻辑日志 ，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于 MySQL Server 层。
• 虽然它们都属于持久化的保证，但是侧重点不同。

  • redo log让InnoDB存储引擎拥有了崩溃恢复能力。
  • binlog保证了MySQL集群架构的数据一致性。

两阶段提交

先介绍一下，redo log和 binlog的写入时机

redo log 是执行事务时不断写入

binlog 是事务提交后server层写入的

那么就有一个问题？如果redo log 写入了，但是binlog写的时候，服务器出问题了，会发生什么

在主从复制的时候？会有问题

update table set c = 1 where id = 2;写入了redo，但是没有写入binlog，所以主服务器启动后会发现redo的值是 c =1，会改变。但是主服务器的binlog是c = 0，所以从服务器的c = 0。主从不一致。

针对这种情况，MySQL对redo采用两阶段提交。Preare和Commit。

如果这时候，binlog写入时服务器异常，会发生什么？

重启服务器后，发现redo属于prepare阶段，也没有binlog，所以会直接回滚

如果reod log commit的时候异常，会发生什么？

重启服务器后，发现redo属于prepare，有binlog，提交事务

本文由 mdnice 多平台发布