mysql学习总结（二进制日志、服务器变量、事务日志）

最新推荐文章于 2022-06-24 16:53:43 发布

陌上繁花绽

最新推荐文章于 2022-06-24 16:53:43 发布

阅读量978

点赞数

分类专栏： linux基础，运维企业

本文链接：https://blog.csdn.net/qq_40419698/article/details/79794225

版权

企业同时被 2 个专栏收录

22 篇文章 0 订阅

订阅专栏

linux基础，运维

18 篇文章 0 订阅

订阅专栏

1、mysql的二进制日志：

记录导致数据改变或潜在导致数据带变的SQL语句；
功能：用于“重放“日志中的事务
这里写图片描述
Log_name ：日志名
Pos：起始位置
Event_type：事件类型
server_id：服务器id
End_log_pos：结束位置（当前事件结束的下一个位置，下一个事务从245开始）

查看mariadb自行管理使用中的二进制文件列表

MariaDB [(none)]> show BINARY LOGS;
MariaDB [(none)]> show MASTER LOGS;

这里写图片描述

查看正在使用中的二进制日志

MariaDB [(none)]> show master status;

查看指定的二进制日志的具体信息

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

关于更多show命令可以使用show help 查看
这里写图片描述

二进制日志的记录格式：
基于”语句”记录：statement
基于“行“记录：row （基于数据）
混合模式：mixed (让系统自行判断基于哪种方式进行)
基于数据记录可能会造成数据量很大，但数据精准。
举个栗子：
对于将全班所有人的年龄加1事务：
基于“语句“：只需记录“年龄+1“这么一条语句
基于”行“ ：需要记录所有人年龄加1的最终结果

二进制文件的构成：
两类文件：
日志文件：mysql-bin.文件后缀名。二进制格式
索引文件:：mysql-bin.index 。文本格式（显示可用的二进制文件序列）
这里写图片描述

2、服务器变量

MariaDB [(none)]> show global variables like '%log%';

sql_log_bin=ON| OFF是否记录二进制日志 
log_bin=ON|OFF
binlog_format=STATEMENT | ROW | MIXED  二进制日志记录的格式max_binlog_size=1073741824  单个二进制日志的最大体积，默认为1G
  注意：（1）到达最大值会自动回滚  （2）文件达到上线时的大小未必为指定的精确值
sync_binlog=0|n  默认情况下，并不是每次写入时立即将binlog的数据同步到磁盘中。而是有操作系统决定什么时候同步。
设置为n：每进行n次事务提交之后，binlog在每n次binlog写入后与硬盘同步
设置为1：性能消耗大，但数据安全
设置为0：性能消耗最小，但是风险也是最大

mysqlbinlog：读取并显示二进制日志 (客户端工具)

mysqlbinlog [options] log_file
    --srart-position
    --stop-position

     --start-datetime=
     --stop-datetime=
    YYYY-MM-DD hh:mm:ss

这里写图片描述

二进制日志格式
这里写图片描述

起始位置：# at 245
事件发生的时间和日期：#180403 11:07:59
事件发生的服务器标识：server id 1
事件结束的位置：end_log_pos 328
事件类型：Query
事件发生时所在服务器执行此事件的线程ID ： thread_id=4
语句的时间戳与将其写入二进制日志的时间差：exec_time=0
错误i代码： error_code=0
事件内容：create database xixi

事务型存储引擎：InnoDB
mysql的所有数据都会保存在数据文件中
对于数据文件来说，如果数据修改操作太多，会导致写入太慢

为了解决磁盘写入过慢的问题（非固态）：

事务日志：用来记录数据库更新情况的文件，它可以记录针对数据库的任何操作，并将记录的结果保存到独立的文件中。对于每一次数据库更新的过程，事务日志文件都有非常全面的记录。根据这些记录可以恢复数据库更新前的状态。（百度百科）
建议重新找一块磁盘存储事务日志，这样可以分散IO

这样就产生了一个问题：当数据修改操作仅保存在事务日志中，并没有来得及保存在数据中，这时有客户过来查询。查询若只针对原数据文件进行查询的话，那么新修改的操作就查不到了。

因此innodb引入了这样一种机制：
buffer pool：在主机内存中开辟一个非常大的内存空间，将事务日志中记录的数据做缓存，并将原数据文件读进来，合并供用户查询。
事务日志的工作方式：事务日志是多个文件一组一起工作的，写满了第一个就写第二个，这时候把第一个同步到磁盘上去，依次循环，所以组内至少有两个文件

如图：id_logfile0 和 id_logfile1就是事务日志文件，可以用mysqlbinlog查看
这里写图片描述

插播：什么是事务回滚：(参考：https://www.cnblogs.com/ld-swust/p/5607983.html)
事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销，在事务中，每个正确的原子操作都会被顺序执行，直到遇到错误的原子操作。回滚的意思其实即使如果之前是插入操作的话，那么会执行删除之前插入的记录，如果是修改操作的话，那么会执行将update之前的记录还原。
因此，正确的原子操作是真正被执行过的，是物理执行。
事务的特性：
1.原子性，是一个不可分割的逻辑单元，一组sql语句，要么都执行，要么都不执行。
　　2.隔离性，事务中的执行过程是不可见的。
　　3.持久性，事务一旦提交，就不可撤销。
　　4.一致性，事务在发生之前和发生之后，数据是一致。(能量守恒)