MySQL-日志

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、 错误日志 、 通用查询日志和 慢查询日志 ，这也是常用的4种。

MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。

使用这些日志文件，可以查看MySQL内部发生的事情。

除二进制日志外，其他日志都是 文本文件 。 默认情况下，所有日志创建于 MySQL数据目录 中，即/var/lib/mysql。

1.通用查询日志

通用查询日志用来 记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。

---

查看通用查询日志当前的状态

#查看通用查询日志当前的状态
mysql> SHOW VARIABLES LIKE '%general%';
+------------------+------------------------------+
| Variable_name  | Value            |
+------------------+------------------------------+
| general_log   | OFF             | #通用查询日志处于关闭状态
| general_log_file | /var/lib/mysql/atguigu01.log | #通用查询日志文件的名称是atguigu01.log，atguigu01是linux下的主机名
+------------------+------------------------------+
2 rows in set (0.03 sec)

#关闭通用查询日志
SET GLOBAL general_log=off; 

---

默认是关闭的状态，所以需要先启动日志

方式1：永久性方式：
修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项，并重启MySQL服务。格式如下：

[mysqld]
general_log=ON
general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名

如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。

方式2：临时性方式：

SET GLOBAL general_log=on;  # 开启通用查询日志

SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置

---

查看日志

[root@iz2zeip0loevltpebhi0txz mysql]# cat iz2zeip0loevltpebhi0txz.log
/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
2022-04-07T08:44:50.256726Z	  149 Query	select * from tb_user9
2022-04-07T08:45:26.927677Z	  149 Query	insert into tb_user9 values(16,'赵六')

---

停止日志

方式1：永久性方式：
修改 my.cnf 或者 my.ini 文件，把[mysqld]组下的 general_log 值设置为 OFF 或者把general_log一项注释掉。修改保存后，再 重启MySQL服务 ，即可生效。

[mysqld]
general_log=OFF #或者将这一行注释

刷新MySQL数据目录

mysqladmin -uroot -p flush-logs

2.错误日志(error log)

启动日志

在MySQL数据库中，错误日志功能是 默认开启 的。而且，错误日志 无法被禁止 。

默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为 mysqld.log （Linux系统）或hostname.err （mac系统）。

---

查看日志

MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看。

查看错误日志的存储路径

SHOW VARIABLES LIKE 'log_err%';

3.二进制日志(bin log)

3.1概念以及具体命令

它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

bin log主要应用场景：

• 用于数据恢复
• 用于 数据复制

查看记录二进制日志是否开启：在MySQL8中默认情况下，二进制文件是开启的。

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 | ON                          |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)
###############################################
[root@iz2zeip0loevltpebhi0txz mysql]# ll
total 225064
-rw-r----- 1 mysql mysql       56 Mar 22 08:39 auto.cnf
-rw-r----- 1 mysql mysql      478 Mar 22 09:13 binlog.000001
-rw-r----- 1 mysql mysql  3484717 Apr  5 14:50 binlog.000002

MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。

systemctl restart mysqld;

也可以修改这个名字

[mysqld]
#启用二进制日志
log-bin=atguigu-bin #这就是binlog文件的名字
binlog_expire_logs_seconds=600 #二进制文件保存时间
max_binlog_size=100M #文件大小，超过这个大小就会默认再生成一个文件

临时性方式开启或关闭binlog

# session级别
# 在mysql8中只有会话级别的设置，没有了global级别的设置。
mysql> SET session sql_log_bin=0;
Query OK, 0 rows affected (0.01 秒)

---

查看日志

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       478 | No        |
| binlog.000002 |   3485034 | No        |
| binlog.000003 |       157 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> insert into tb_user9 values(17,'钱七');
Query OK, 1 row affected (0.01 sec)

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

[IN ‘log_name’]：指定要查询的binlog文件名，不指定就是第一个

[FROM pos]：指定从哪个pos位置开始查，不指定就是从文件的第一个pos开始

[LIMIT [offset,] row_count]：偏移量和查询的总行数

---

用日志恢复数据

mysqlbinlog [option] filename|mysql –uuser -ppass;

使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。

---

删除部分二进制文件

#删除创建时间比binlog.000002早的所有日志
purge master logs to 'binlog.000002'

3.2 原理

binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把binlog cache写到binlog文件中。（类比redo log）

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。

为了安全起见，可以设置为 1，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。

最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

---

binlog与redolog对比

redo log是在物理层面，涉及索引和页，修改了什么

binlog是你执行了什么sql命令，记录的是这个命令

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的 写入时机 不一样。

4.中继日志

中继日志只在主从服务器架构的从服务器上存在，从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志，然后从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

中继日志默认保存在从服务器的数据目录下，文件名格式是从服务器名-relay-bin.序号

还有一个索引文件，从服务器名-relay-bin.index来定位正在使用的中继日志

中继日志与二进制日志的格式相同。

5.主从复制

为什么会出现主从复制？

一般对于数据库都是读多写少，也就是对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做主从架构，进行读写分离，这样也可以提高数据库的并发处理能力。

主从复制的作用：

读写分离、数据备份、高可用性

---

5.1原理

Slave (从服务器)会从 Master (主服务器)读取 binlog 来进行数据同步。

1. 二进制日志转储dump线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 (因为数据更新需要写入binlog，而这时候如果dump线程进行读的话就会造成并发问题，因此需要加锁)，读取完成之后，再将锁释放掉。
2. 从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
3. 从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

5.2 保证主机从机数据一致

为什么会出现主从数据不一致问题？

进行主从同步的内容是二进制日志，它是一个文件，在进行 网络传输 的过程中就一定会 存在主从延迟（比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的 数据不一致性 问题。

1. 从机的机器性能比主机要差
2. 从机的压力大
3. 事务比较大(binlog文件大)

---

如何解决一致性问题

1. 异步复制
2. 半同步复制
3. 组复制