8.MySQL-日志文件-错误日志-慢日志-通用日志-二进制日志-redo log-undo log-事物的执行过程

最新推荐文章于 2024-07-24 16:18:52 发布

Zengkaijie

最新推荐文章于 2024-07-24 16:18:52 发布

阅读量151

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Zengkaijie/article/details/126684232

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

MySQL-日志文件-错误日志-慢日志-通用日志-二进制日志-redo log-undo log-事物的执行过程

文章目录

MySQL-日志文件-错误日志-慢日志-通用日志-二进制日志-redo log-undo log-事物的执行过程
@[toc]
一、日志
1.1 MySQL日志文件
1.2 错误日志
1.3 慢日志 slow_query_log
1.4 通用日志 general log
1.5 二进制日志 binary log
localhost-bin.index 文件：记录了累计共有多少个二进制文件
查看当前使用的是哪个二进制文件 show master status
查看所有二进制文件和大小 show binary logs
哪些行为会产生新的二进制文件？
二进制日志是如何存入磁盘的
刷盘时机，是指什么时候，通过什么策略将日志内容写入到磁盘中？
查看二进制文件 mysqlbinlog

1.6 Innodb存储引擎架构
1.7 redo log
1.8 undo log
1.9 事务的执行过程

一、日志

1.1 MySQL日志文件

日志信息用来做什么事？

用来排错。
用来做数据分析，提升数据库性能等
了解程序的运行情况，是否健康，了解mysql性能，运行情况

1.2 错误日志

一般错误日志的名字是：主机名.err --》 zkj-mysql.err

错误日志是Mysq里面默认开启的日志。

示例：查看mysql里面的日志变量信息。

root@(none) 10:28  mysql>show variables like "%log%";
+--------------------------------------------+---------------------------------------+
| Variable_name                              | Value                                 |
+--------------------------------------------+---------------------------------------+
| back_log                                   | 80                                    |
| binlog_cache_size                          | 32768                                 |
| binlog_checksum                            | CRC32                                 |
| binlog_direct_non_transactional_updates    | OFF                                   |
| binlog_error_action                        | ABORT_SERVER                          |
| binlog_format                              | ROW                                   |
| binlog_group_commit_sync_delay             | 0                                     |
| binlog_group_commit_sync_no_delay_count    | 0                                     |
| binlog_gtid_simple_recovery                | ON                                    |
| binlog_max_flush_queue_time                | 0                                     |
| binlog_order_commits                       | ON                                    |
| binlog_row_image                           | FULL                                  |
| binlog_rows_query_log_events               | OFF                                   |
| binlog_stmt_cache_size                     | 32768                                 |
| binlog_transaction_dependency_history_size | 25000                                 |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER                          |
| expire_logs_days                           | 0                                     |
| general_log                                | OFF                                   |
| general_log_file                           | /data/mysql/localhost.log             |
| innodb_api_enable_binlog                   | OFF                                   |
| innodb_flush_log_at_timeout                | 1                                     |
| innodb_flush_log_at_trx_commit             | 1                                     |
| innodb_locks_unsafe_for_binlog             | OFF                                   |
| 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                                  |
| innodb_max_undo_log_size                   | 1073741824                            |
| innodb_online_alter_log_max_size           | 134217728                             |
| innodb_undo_log_truncate                   | OFF                                   |
| innodb_undo_logs                           | 128                                   |
| log_bin                                    | OFF                                   |
| log_bin_basename                           |                                       |
| log_bin_index                              |                                       |
| log_bin_trust_function_creators            | OFF                                   |
| log_bin_use_v1_row_events                  | OFF                                   |
| log_builtin_as_identified_by_password      | OFF                                   |
| log_error                                  | ./localhost.localdomain.err           |
| log_error_verbosity                        | 3                                     |
| log_output                                 | FILE                                  |
| log_queries_not_using_indexes              | OFF                                   |
| log_slave_updates                          | OFF                                   |
| log_slow_admin_statements                  | OFF                                   |
| log_slow_slave_statements                  | OFF                                   |
| log_statements_unsafe_for_binlog           | ON                                    |
| log_syslog                                 | OFF                                   |
| log_syslog_facility                        | daemon                                |
| log_syslog_include_pid                     | ON                                    |
| log_syslog_tag                             |                                       |
| log_throttle_queries_not_using_indexes     | 0                                     |
| log_timestamps                             | UTC                                   |
| log_warnings                               | 2                                     |
| max_binlog_cache_size                      | 18446744073709547520                  |
| max_binlog_size                            | 1073741824                            |
| max_binlog_stmt_cache_size                 | 18446744073709547520                  |
| max_relay_log_size                         | 0                                     |
| relay_log                                  |                                       |
| relay_log_basename                         | /data/mysql/localhost-relay-bin       |
| relay_log_index                            | /data/mysql/localhost-relay-bin.index |
| relay_log_info_file                        | relay-log.info                        |
| relay_log_info_repository                  | FILE                                  |
| relay_log_purge                            | ON                                    |
| relay_log_recovery                         | OFF                                   |
| relay_log_space_limit                      | 0                                     |
| slow_query_log                             | OFF                                   |
| slow_query_log_file                        | /data/mysql/localhost-slow.log        |
| sql_log_bin                                | ON                                    |
| sql_log_off                                | OFF                                   |
| sync_binlog                                | 1                                     |
| sync_relay_log                             | 10000                                 |
| sync_relay_log_info                        | 10000                                 |
+--------------------------------------------+---------------------------------------+
74 rows in set (0.00 sec)

错误日志的位置：

因为博主的MySQL服务是二进制安装的，我们指定了数据目录，错误日志会放在数据目录 /data/mysql 里面

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ge7jUmKu-1662219232019)(D:\Typora\photo\image-20220817152857811.png)]$

[root@zkj-mysql mysql]# ls
auto.cnf         ibdata1      mysql.sock.lock     server-key.pem
ca-key.pem       ib_logfile0  performance_schema  sys
ca.pem           ib_logfile1  private_key.pem     zkj
client-cert.pem  ibtmp1       public_key.pem      zkj-mysql.err
client-key.pem   mysql        sc                  zkj-mysql.pid
ib_buffer_pool   mysql.sock   server-cert.pem

如果不知道错误日志的位置可以登录MySQL服务查找错误日志变量

root@(none) 10:34  mysql>show variables like "log_error";
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| log_error     | ./localhost.localdomain.err |
+---------------+-----------------------------+
1 row in set (0.01 sec)

错误日志记录了什么内容？

登录失败会记录到错误日志
配置文件出错也会记录
启动过程出问题也会记录

示例：mysql登录失败会记录错误日志。

使用tail -f 监控日志文件

[root@zkj-mysql mysql]# tail -f zkj-mysql.err

错误输入密码：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iwEv6z2b-1662219232020)(D:\Typora\photo\image-20220817154037415.png)]$

生成错误日志：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-argfo6KW-1662219232021)(D:\Typora\photo\image-20220817153923922.png)]$

示例：日志文件配置错误：

日志文件配置错误：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I3ZNFbwU-1662219232021)(D:\Typora\photo\image-20220817154234683.png)]$

生成警告

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-khUf6wBW-1662219232022)(D:\Typora\photo\image-20220817154337404.png)]$

1.3 慢日志 slow_query_log

慢日志有什么作用？

作用：记录消耗时间比较长的SQL语句，为数据库性能提升提供了线索

例题：最近数据库压力（负载特别高），客户反应网站或者应用使用特别慢，领导要求你查明原因？
1.SQL语句需要优化，在数据库里启用慢日志，找出执行时间比较长的SQL
2.业务量太大了，硬件已经达到极限了，top、glances、dstat

慢日志默认是关闭的

如果打开慢日志会将慢日志放在我们定义的数据目录下面。名字是主机名 + slow.log

root@mysql 15:27  mysql>show variables like "%slow_query%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: mysql

+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /data/mysql/zkj-mysql-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.02 sec)

sql语句执行时间多长算慢日志？

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-57SgqIh0-1662219232022)(D:\Typora\photo\image-20220817154657357.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TCrwcT25-1662219232022)(D:\Typora\photo\image-20220817154717891.png)]$

默认sql语句超过10秒就是慢sql，会记录到慢日志

root@mysql 15:46  mysql>show variables like "%long_query%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

在配置文件里面开启慢日志

# slow log
slow_query_log = 1
long_query_time = 0.001 # 标准
# 在写入慢日志中包含慢管理语句。
log_slow_admin_statements = 1

重启配置文件以后就会出现慢日志文件

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5l1HCWR8-1662219232022)(D:\Typora\photo\image-20220817155217780.png)]$

root@mysql 15:47  mysql>show variables like "%slow%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: mysql

+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | ON                             |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | ON                             |
| slow_query_log_file       | /data/mysql/zkj-mysql-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.02 sec)

我们设置了慢日志标准为0.001秒，如果有sql语句超过这个时间的话就会记录到慢日志里面

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q7cBR1jB-1662219232023)(D:\Typora\photo\image-20220817155513215.png)]$

1.4 通用日志 general log

general 日志，会记录所有的sql操作
缺点：会消耗大量的磁盘空间，消耗cpu，内存，磁盘资源
通用日志名字为主机名 + .log

root@mysql 15:52  mysql>show variables like "%general%";
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /data/mysql/zkj-mysql.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)

通用日志默认是关闭的，我们可以在配置文件里面打开通用日志。

# general 日志
general_log

root@mysql 16:05  mysql>show variables like "%general%";
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | ON                        |
| general_log_file | /data/mysql/zkj-mysql.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)

可以看到，开启general通用日志以后，所有使用过的sql语句都会记录在zkj-mysql.log文件里面

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HF9EkwrC-1662219232023)(D:\Typora\photo\image-20220817160639047.png)]$

1.5 二进制日志 binary log

二进制日志的作用？

可以用来做数据恢复
主从复制

二进制日志默认也是没有开启的。

示例：在配置文件里面开启二进制日志

# binary log
log_bin
server_id = 1 （每台服务器都不一样 一般master=1）

root@mysql 16:05  mysql>show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: mysql

+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /data/mysql/zkj-mysql-bin       |
| log_bin_index                   | /data/mysql/zkj-mysql-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+
6 rows in set (0.03 sec)

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E17ZM425-1662219232023)(D:\Typora\photo\image-20220817160952256.png)]$

localhost-bin.index 文件：记录了累计共有多少个二进制文件

mysqld服务每刷新一次，二进制文件就会增加一个，index文件里面也会新加一个

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DyS1aOBq-1662219232023)(D:\Typora\photo\image-20220817161215105.png)]$

查看当前使用的是哪个二进制文件 show master status

root@mysql 16:08  mysql>show master status;

+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| zkj-mysql-bin.000002 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

查看所有二进制文件和大小 show binary logs

root@mysql 16:12  mysql>show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| zkj-mysql-bin.000001 |       177 |
| zkj-mysql-bin.000002 |       154 |
+----------------------+-----------+
2 rows in set (0.01 sec)

一个二进制文件是否记录了整个mysqld进程里所有的库的操作？

是的，对所有库的操作都会记录到一个二进制文件里面，如果需要记录到不同的日志文件里面，可以采用多实例

mysq实例：

正在运行的一个mysql进程，这个进程里面有哪些库可以操作，二进制日志就记录哪些库的操作

多实例：

多启动几个mysqld的进程，一个mysqld的进程对应一个库

隔离应用，避免一个库使用特别频繁，影响其他的库

多实例任然受到整个机器整体系统资源的限制

哪些行为会产生新的二进制文件？

1.service mysqld restart

2.flush logs；

root@mysql 16:13  mysql>flush logs;
Query OK, 0 rows affected (0.02 sec)

root@mysql 16:29  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| zkj-mysql-bin.000003 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ym3tn9Qd-1662219232023)(D:\Typora\photo\image-20220817162948879.png)]$

3.当日志达到最大值1G的时候，也会产生新的日志

root@mysql 16:29  mysql>show variables like "max_binlog_size";
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)

root@mysql 16:30  mysql>select 1073741824/1024/1024;
+----------------------+
| 1073741824/1024/1024 |
+----------------------+
|        1024.00000000 |
+----------------------+
1 row in set (0.01 sec)

二进制日志是如何存入磁盘的

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-db7dxu7A-1662219232024)(D:\Typora\photo\image-20220817161629025.png)]$

刷盘时机，是指什么时候，通过什么策略将日志内容写入到磁盘中？

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4YipK3Lj-1662219232024)(D:\Typora\photo\image-20220817162557715.png)]$

查看二进制文件 mysqlbinlog

此时我们使用的二进制文件是 Localhost-bin.000003

对数据库进行操作

root@ucar_cloud 16:33  mysql>use student;
Database changed
root@student 16:34  mysql>shwo tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'shwo tables' at line 1
root@student 16:34  mysql>show tables;
Empty set (0.00 sec)
 
root@student 16:34  mysql>create table test(id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
 
root@student 16:35  mysql>insert into test(id,name) values(1,'zeng');
Query OK, 1 row affected (0.00 sec)

[root@zkj-mysql mysql]# mysqlbinlog -v localhost-bin.000003 
# at 535
#220812 16:35:34 server id 1  end_log_pos 580 CRC32 0xdd2e8bd1 	Write_rows: table id 113 flags: STMT_END_F
 
BINLOG '
xgL2YhMBAAAANQAAABcCAAAAAHEAAAAAAAEAB3N0dWRlbnQABHRlc3QAAgMPAjwAA5EKdrY=
xgL2Yh4BAAAALQAAAEQCAAAAAHEAAAAAAAEAAgAC//wBAAAABHdhbmfRiy7d
'/*!*/;
### INSERT INTO `student`.`test`
### SET
###   @1=1
###   @2='zeng'

at 535 – 从535位置号开始
220812 15:35:34 – 时间
end_log_pos 580 – 到580位置号结束

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FpUHDbak-1662219232024)(D:\Typora\photo\image-20220817155651277.png)]$

1.6 Innodb存储引擎架构

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VHby3pYM-1662219232024)(D:\Typora\photo\image-20220817163627636.png)]$

1.7 redo log

redo log的作用：

redo log的主要作用是用于数据库的崩溃恢复
就是当数据从内存刷到磁盘的时候，突然停电，导致刷盘失败，然后Mysql服务器重启后就会首先看redolog的日志，重新再做一遍，保障数据一致性，不丢失

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tAj8lAsL-1662219232025)(D:\Typora\photo\image-20220817172318109.png)]$

redo log可以简单分为以下两个部分：

一是内存中重做日志缓冲 (redo log buffer),是易失的，在内存中
二是重做日志文件 (redo log file)，是持久的，保存在磁盘中

redo的整体流程

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mOiZqoN3-1662219232026)(D:\Typora\photo\image-20220817164705224.png)]$

redo日志是比数据页先写入磁盘的。

第一步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
第二步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第三步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
第四步：定期将内存中修改的数据刷新到磁盘中

1.8 undo log

undolog的作用

undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。
服务器运行时的回滚rollback、MVCC（多版本控制）就是基于此log

1.9 事务的执行过程

示例：状态1：A：10，B：10，状态2：A：0，B：20

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7oNew3hl-1662219232026)(D:\Typora\photo\image-20220817164840134.png)]$

过程：

从磁盘中读取数据A到内存中
记录数据到undo.log
修改A的值为0
记录修改后的数据到redo.log buffer 里面
从磁盘中读取数据B到内存中
记录数据到undo.log
修改B的值为20
记录修改后的数据到redo.log buffer 里面，server层标记prepare，准备提交
bin_log 写入磁盘中
事务提交，redo.log 在server层标记 commit，数据刷入磁盘中，提交成功，
如果中途失败的话，会根据undo.log回滚。

参考资料：

浅析MySQL事务中的redo与undo - 简书 (jianshu.com)

mysql之事务执行过程和ACID分析；一文读懂undolog、redolog、binlog刷盘时机和意义 - 知乎 (zhihu.com)

Zengkaijie

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
8.MySQL-日志文件-错误日志-慢日志-通用日志-二进制日志-redo log-undo log-事物的执行过程

MySQL-日志文件-错误日志-慢日志-通用日志-二进制日志-redo log-undo log-事物的执行过程
复制链接

扫一扫

专栏目录