MySQL-日志

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

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

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

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

1.通用查询日志

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


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

1
2
3
4
5
6
7
8
9
10
11
12
#查看通用查询日志当前的状态
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服务。格式如下:

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

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

方式2:临时性方式

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

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

查看日志

1
2
3
4
5
6
[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服务 ,即可生效。

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

刷新MySQL数据目录

1
mysqladmin -uroot -p flush-logs

2.错误日志(error log)

启动日志

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

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


查看日志

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

查看错误日志的存储路径

1
SHOW VARIABLES LIKE 'log_err%';

3.二进制日志(bin log)

3.1概念以及具体命令

它记录了数据库所有执行的DDLDML 等数据库更新事件的语句,但是不包含没有修改任何数据的语句(如数据查询语句select、show等)。

bin log主要应用场景:

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;

也可以修改这个名字

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

临时性方式开启或关闭binlog

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

查看日志

1
2
3
4
5
6
7
8
9
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)

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

1
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]:偏移量和查询的总行数


用日志恢复数据

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

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


删除部分二进制文件

1
2
#删除创建时间比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. 组复制

MySQL-日志
https://vickkkyz.fun/2022/04/08/计算机/mysql/日志/
作者
Vickkkyz
发布于
2022年4月8日
许可协议