MySQL日志
一条update的执行流程
执行流程分为在server层和存储引擎层;server是MySQL都有的,其日志文件是binlog;存储引擎是不同的,undolog,redolog是innodb特有的。
首先是客户端创建请求,然后去服务层请求;
然后是server的连接器,连接器的作用是校验用户是否有权限进行查询等等。
(在8.0版本之前有查询缓存,主要是针对select语句,但是8.0已经拿掉了这个功能,作用不大)
然后是分析器,分析器对词法语法进行分析,先词法分析判断是什么请求,要执行的操作是什么;然后是语法分析,看是否符合该操作的语法;
然后是优化器,优化器针对SQL进行优化,看是否要走索引等等;
然后就是执行器,执行器再次校验权限,权限满足就开始去调用innodb引擎提供的接口进行后续一系列的操作;
存储引擎中的操作:
- 先去存储引擎的缓存buffer pool中查询是否有这条数据;如果没有就从数据库磁盘中加载到缓存中,是在内存中的;加载是整页加载,根据就近性原则加载多条数据;
- 然后将这条数据的旧址写入到undo log日志版本链中,便于后续的回滚提交等操作,为了保证原子性;
- 然后在缓存buffer中将数据修改,此时可以认为数据修改完成;然后将数据写入到page cache中;
- 然后就是写redo log,这个redo log是分两阶段提交的,刚开始提交到redo log buffer(缓存中), 还要写入到操作系统缓存page cache中,然后才能刷盘。完整的刷盘是三步。
- 此时redo log写入到buffer中,这里是顺序写,效率比较高,此时的提交是半事务,prepare阶段;
- 然后就是执行器写biglog文件,biglog文件没有buffer,就直接写入到操作系统的page cache,然后是刷盘;
- biglog刷盘成功,然后执行器触发执行一个commit标记到redo log中,事务完全提交;也会令undo log中的状态变成commit;
- 然后就是操作系统根据配置信息将page cache中的数据库真正要修改的值刷新到磁盘中。
redo和binlog都是为了保证数据的持久性;redo在数据库宕机,buffer pool中的数据还没有写入到磁盘中进行数据恢复的;
binlog,主要是为了数据库的主从复制;还有就是误删了数据,可以根据binlog的备份日志将数据恢复。所以binlog也成为备份日志。
redo的写是顺序写的,因为默认设置好的一个或多个文件,都是从尾部追加写的,有两个指针,一个是追加写,另外一个是用来标记是否被刷到磁盘,两个标记中间的就是可用空间,当追加写指针赶上标记指针时,先暂停,将redo 中的部分数据刷盘,清理一下文件,然后继续写入。
为什么要搞的这么复杂?
因为修改可能是多张表,分别在不同的ibd文件中,这些在磁盘中的存储位置可能是不连续的,如果直接写,那就是随机写,要先找到位置,然后才能修改,这样比较耗时;使用redo的顺序写可用大幅的提高效率,这个也是异步的经典体现。
redo log重做日志关键参数
show variables like '%innodb_log_buffer_size%';
show variables like '%innodb_log_group_home_dir%';
show variables like '%innodb_log_files_in_group%';
show variables like '%innodb_log_file_size%';
redo log 写入磁盘过程分析:
默认redo在写的时候分4块,从0开始写,一直写到3,然后再从0开始继续写。一直循环往复。
- 设置为0:表示每次事务提交时都只是把 redo log 留在 redo log buffer 中,之后就立刻commit事务,效率最高,但是数据库宕机可能会丢失数据。
- 设置为1(默认值):表示每次事务提交时都将 redo log 直接持久化到磁盘,数据最安全,不会因为数据库宕机丢失数据,但是效率稍微差一点,线上系统推荐这个设置。
- 设置为2:表示每次事务提交时都只是把 redo log 先写到buffer中,然后写到操作系统的缓存page cache里,这种情况如果数据库宕机是不会丢失数据的,但是操作系统如果宕机了,page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。如果对数据一致性要求不是很高,就可以认为写入到page cache就是写入到磁盘了。
redo log,redo log buffer这两个都是MySQL管理的,page cache是操作系统管理的内存空间。
从redo log buffer到操作系统缓存page cache,再从page cache到真正的磁盘文件;这是经过了两个步骤,这也是异步的经典体现。
page cache也是内存的一部分,其作用就是为了解决内存和磁盘交互速度瓶颈的,类似CPU的高速缓存。
binlog二进制归档日志
# 查看binlog相关参数
show variables like '%log_bin%';
- log_bin:binlog日志是否打开状态
- log_bin_basename:是binlog日志的基本文件名,后面会追加标识来表示每一个文件,binlog日志文件会滚动增加
- log_bin_index:指定的是binlog文件的索引文件,这个文件管理了所有的binlog文件的目录。
- sql_log_bin:sql语句是否写入binlog文件,ON代表需要写入,OFF代表不需要写入。如果想在主库上执行一些操作,但不复制到slave库上,可以通过修改参数sql_log_bin来实现。比如说,模拟主从同步复制异常。
MySQL5.7 版本中,binlog默认是关闭的,8.0版本默认是打开的。上图中log_bin的值是ON就代表binlog是打开状态,打开binlog功能,需要修改配置文件my.ini(windows)或my.cnf(linux),然后重启数据库。
一些重要的配置项:
show binary logs;
binlog 的日志格式
- STATEMENT:基于SQL语句的复制,每一条会修改数据的sql都会记录到master机器的bin-log中,这种方式日志量小,节约IO开销,提高性能,但是对于一些执行过程中才能确定结果的函数,比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行,则结果跟master机器执行的不一样。这种方式只是记录了一条SQL,具体SQL的改动量并没有记录。类似逻辑记录
- ROW:基于行的复制,日志中会记录成每一行数据被修改的形式,然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节,可以解决函数、存储过程等在slave机器的复制问题,但这种方式日志量较大,性能不如Statement。举个例子,假设update语句更新10行数据,Statement方式就记录这条update语句,Row方式会记录被修改的10行数据。
- MIXED:混合模式复制,实际就是前两种模式的结合,在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种,如果sql里有函数或一些在执行时才知道结果的情况,会选择Row,其它情况选择Statement,推荐使用这一种。
binlog写入磁盘机制
- 为0的时候,表示每次提交事务都只 write 到page cache,由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升,但是机器宕机,page cache里面的 binlog 会丢失。
- 也可以设置为1,表示每次提交事务都会执行 fsync 写入磁盘,这种方式最安全。
- 还有一种折中方式,可以设置为N(N>1),表示每次提交事务都write 到page cache,但累积N个事务后才 fsync 写入磁盘,这种如果机器宕机会丢失N个事务的binlog。
- 服务器启动或重新启动
- 服务器刷新日志,执行命令flush logs
- 日志文件大小达到 max_binlog_size 值,默认值为 1GB
删除 binlog 日志文件
# 删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件,下面这个是删除6之前的所有日志文件,当前这个文件不删除
purge master logs to 'mysql‐binlog.000006';
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2023‐01‐21 14:00:00';
可用通过binlog进行数据的恢复。
mysqldump ‐u root 数据库名>备份文件名; #备份整个数据库
mysqldump ‐u root 数据库名 表名字>备份文件名; #备份整个表
mysql ‐u root test < 备份文件名 #恢复整个数据库,test为数据库名称,需要自己先建一个数据库test
为什么会有redo log和binlog两份日志呢?
undo log回滚日志
- innodb_undo_directory:设置undo log文件所在的路径。该参数的默认值为"./",即innodb数据文件存储位置,目录下ibdata1文件就是undo log存储的位置。
- innodb_undo_logs: 设置undo log文件内部回滚段的个数,默认值为128。
- innodb_undo_tablespaces: 设置undo log文件的数量,这样回滚段可以较为平均地分布在多个文件中。设置该参数后,会在路径innodb_undo_directory看到undo为前缀的文件。
undo log日志什么时候删除
为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了?
错误日志
# 查看错误日志存放位置
show variables like '%log_error%';
通用查询日志
show variables like '%general_log%';
# 打开通用查询日志
SET GLOBAL general_log=on;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· 【.NET】调用本地 Deepseek 模型
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
· DeepSeek “源神”启动!「GitHub 热点速览」