MySQL05-锁

InnoDB锁

MyISAM和InnoDB存储引擎使⽤的锁:

  • MyISAM采⽤表级锁(table-level locking)。
  • InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁
  • 表级锁: MySQL中锁定 粒度最⼤ 的⼀种锁,对当前操作的整张表加锁,实现简单,资源消耗也⽐j较少,加锁快,不会出现死锁。其锁定粒度最⼤,触发锁冲突的概率最⾼,并发度最低,MyISAM和 InnoDB引擎都⽀持表级锁。
  • ⾏级锁: MySQL中锁定 粒度最⼩ 的⼀种锁,只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩,并发度⾼,但加锁的开销也最⼤,加锁慢,会出现死锁

InnoDB锁类型

实现了两种标准的行级锁

  • 共享锁(S Lock):允许事务读一行数据
  • 排他锁(X Lock):允许事务删除或更新一行数据

S 锁仅与 S 锁兼容

一致性非锁定读

InnoDB 存储引擎通过多版本控制的方式来读取当前执行时间数据库中的行数据。如果读取的行正在执行 DLETE 或 UPDARA 操作,这时读操作不会因此去等待行上锁的释放,相反InnoDB存储引擎会去读取行的一个快照数据。

一个行记录可能不止一个快照数据(通过undo 段来实现),一般称这种技术为行多版本技术,由此带来的并发技术称为多版本并发控制(MVCC)

事务隔离级别中
READ-COMMITTED(读取已提交),REPEATABLE-READ(可重复读)下,使用一致性非锁定读。然而他们对于快照数据的定义却不同
READ-COMMITTED(读取已提交):总是读取锁定行的最新一份的快照数据。
REPEATABLE-READ(可重复读):对于快照数据,总是读取事务开始时的行数据版本
在这里插入图片描述

一致性锁定读

用户想要显式地对数据库的 SELECT 操作进行加锁以保证数据逻辑的一致性。
在这里插入图片描述

行锁的三种算法

  • Record Lock:单个行记录上的锁
  • Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
  • Next-Key Lock:Gap Lock+Record Lock,锁定一个范围,并且锁定记录本身

锁问题/并发事务带来的问题

通过锁机制可以实现事务的隔离要求,使得事务可以并发的工作。锁提高了并发,但是却会带来潜在的问题。不过好在事务隔离性的要求,锁只会带来三种问题

  • 脏读(Dirty read): 当⼀个事务正在访问数据并且对数据进⾏了修改,⽽这种修改还没有提交到数据库中,这时另外⼀个事务也访问了这个数据,然后使⽤了这个数据。因为这个数据是还没有提交的数据,那么另外⼀个事务读到的这个数据是“脏数据”,依据“脏数据”所做的操作可能是不正确的。

READ-UNCOMMITTED(读取未提交)

  • 丢失修改(Lost to modify): 简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖,从而导致数据不一致。例如
    1)事务1将行记录r 更新为v1。但是事务1并未提交
    2)与此同时,事务2将行记录r 更新为 v2,事务2未提交
    3)事务1提交
    4)事务2提交
    但是在当前数据库的任何隔离级别下,都不会导致数据库理论意义上的丢失更新问题,这是因为,对于行的DML操作,需要对行或更粗粒度的对象加锁,因此上述中,事务2不能对行记录r 进行更新操作,其会被阻塞,直到事务1提交、

我们遇到的一般是逻辑意义上的丢失更新问题
1)指在⼀个事务T1查询⼀个数据,放入本地内存,并显示给一个终端用户user1
2)另外⼀个事务T2也查询了该数据,放入本地内存,并显示给一个终端用户user2
3)user1修改了这个数据后,更新数据库并提交
4)user2修改了这个数据后,更新数据库并提交
这样user1的修改结果就被丢失。
例如:事务1读取某表中的数据A=20,事务2也读取A=20,事务1修改A=A-1,事务2也修改A=A-1,最终结果A=19,事务1的修改被丢失。

解决方法:就是让事务在这种情况下的操作变成串行化,而不是并行操作。即在步骤1)中,对用户读取的记录加上一个排他 X 锁。同样步骤2)中,用户同样需要加一个排他 X锁。这样user2就必须等

  • 不可重复读(Unrepeatableread): 指在⼀个事务内多次读同⼀数据集合。在这个事务还没有结束时,另⼀个事务也访问该同⼀数据集合,并做了一些DML操作。因此,在第⼀个事务中的两次读数据之间,由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况,因此称为不可重复读。

不可重复读,是因为在读取已提交的隔离级别下,读取的是该记录的最新的快照。

一般来说不可重复读是可以接受的,因为其读到的是已经提交的数据,本身并不会带来很大的问题,因此很多厂商将其数据库的默认隔离级别设置为 READ-COMMITTED(读取已提交)

InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ(可重读)
解决方案:通过Next-Key Lock 算法来避免不可重复读的问题

脏读和不可重复读的区别是:脏读是读到未提交的数据,而不可重复读读到的却是已经提交的数据,但是其实违反了数据库事务一致性的要求

在这里插入图片描述

<think>我们正在讨论MySQL的二进制日志(Binary Log)用于数据恢复。根据引用内容,我们可以总结出以下步骤: 1. 确保MySQL已经开启了二进制日志(如果没有开启,需要修改配置文件并重启MySQL)。 2. 使用mysqlbinlog工具查看二进制日志内容,定位需要恢复的操作位置(通过时间或位置点)。 3. 使用mysqlbinlog导出特定位置或时间段的日志内容(可以输出到文件,也可以直接通过管道导入mysql)。 4. 执恢复(如果导出到文件,则使用source命令或mysql命令导入;如果通过管道,则直接执)。 具体步骤: 步骤1:检查二进制日志是否开启 - 登录MySQL,执:`show variables like 'log_bin';`,如果Value为ON则表示开启。 步骤2:查看二进制日志文件列表 -:`show binary logs;` 步骤3:查看具体的二进制日志内容,确定要恢复的位置 - 使用mysqlbinlog工具(在命令中执,不是在MySQL中): ```bash mysqlbinlog /var/lib/mysql/binlog.000001 ``` 如果日志文件很大,可以结合less或grep进查找。 步骤4:根据位置点或时间点导出日志 - 按位置点导出: ```bash mysqlbinlog --start-position=107 --stop-position=2345 /var/lib/mysql/binlog.000001 > recovery.sql ``` - 按时间点导出: ```bash mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-02 00:00:00" /var/lib/mysql/binlog.000001 > recovery.sql ``` 步骤5:执恢复 - 方法1:使用source命令(在MySQL命令中): ```sql source recovery.sql; ``` - 方法2:使用mysql命令工具: ```bash mysql -u root -p < recovery.sql ``` 注意事项: - 恢复前最好备份当前数据,以防恢复操作带来新的问题。 - 如果恢复的数据涉及多个二进制日志文件,需要按顺序导出并执。 引用[3][4]提供了具体的操作示例。 另外,引用[5]提到了重做日志(Redo Log),但重做日志是InnoDB存储引擎层面的日志,用于保证事务的持久性崩溃恢复,而二进制日志是MySQL服务器层面的,主要用于复制数据恢复。两者不同,我们这里讨论的是二进制日志。 生成相关问题: 1. 如何开启MySQL的二进制日志? 2. 二进制日志重做日志有什么区别? 3. 如何清理旧的二进制日志文件? 请根据以上步骤注意事项编写回答。</think>### MySQL 二进制日志(Binary Log)数据恢复详解 Binary Log 是 MySQL 的核心日志机制,记录所有修改数据库的操作(增删改)。通过解析日志可实现精确数据恢复,以下是完整操作流程: --- #### 一、前提条件 1. **开启 Binary Log** 在 MySQL 配置文件 `my.cnf` 中添加: ```ini [mysqld] log-bin = /var/log/mysql/mysql-bin.log # 日志路径 server-id = 1 # 主从复制需唯一ID binlog_format = ROW # 推荐ROW格式[^1] ``` ```bash sudo systemctl restart mysql # 重启生效 ``` 2. **验证日志状态** ```sql SHOW VARIABLES LIKE 'log_bin'; -- 需返回 ON SHOW MASTER STATUS; -- 查看当前日志文件及位置[^1] ``` --- #### 二、恢复数据核心步骤 ##### ▶ 场景1:按时间点恢复(误删数据) ```bash # 导出特定时间段的日志 mysqlbinlog \ --start-datetime="2024-06-01 09:00:00" \ --stop-datetime="2024-06-01 10:00:00" \ mysql-bin.000001 > recovery.sql # 执恢复 mysql -u root -p < recovery.sql ``` ##### ▶ 场景2:按位置点恢复(精确操作) ```bash # 解析日志找到操作位置(如误删前的位置) mysqlbinlog mysql-bin.000001 | less # 导出指定位置区间(如 start-position=107, stop-position=2345) mysqlbinlog \ --start-position=107 \ --stop-position=2345 \ mysql-bin.000001 | mysql -u root -p[^4] ``` ##### ▶ 场景3:恢复单库/单表 ```bash # 通过库名过滤日志 mysqlbinlog \ --database=your_db_name \ mysql-bin.000001 | mysql -u root -p ``` --- #### 三、关键技巧与注意事项 1. **日志解析工具** ```bash mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 # 解码ROW格式日志 ``` - 输出内容包含实际执的 SQL 语句[^3] 2. **安全恢复建议** - 恢复前备份当前数据:`mysqldump -u root -p --all-databases > full_backup.sql` - 测试环境验证恢复脚本 - 大事务操作分段恢复,避免表 3. **常见错误处理** - **日志文件不存在**:检查 `show binary logs;` 确认有效文件 - **权限问题**:确保 `mysqlbinlog` MySQL 用户有日志读取权限 - **GTID 冲突**:添加 `--skip-gtids` 参数忽略 GTID 限制 --- #### 四、Binary Log 维护 1. **清理旧日志** ```sql PURGE BINARY LOGS BEFORE '2024-05-01'; -- 删除指定日期前的日志[^2] SET GLOBAL expire_logs_days = 7; -- 自动保留7天日志 ``` 2. **磁盘空间监控** ```sql SHOW BINARY LOGS; -- 查看日志文件大小 ``` --- #### 五、与 Redo Log 的区别[^5] | **特性** | **Binary Log** | **Redo Log** | |----------------|------------------------------------|----------------------------------| | 作用层级 | MySQL 服务层 | InnoDB 存储引擎层 | | 记录内容 | 逻辑操作(SQL 语句) | 物理页修改(数据页变化) | | 主要用途 | 数据恢复、主从复制 | 崩溃恢复(Crash Recovery) | | 文件位置 | 自定义路径(如 /var/log/mysql/) | 固定路径(ib_logfile0,1) | > 提示:数据恢复优先使用 Binary Log;系统崩溃恢复由 Redo Log 自动完成。 --- ### 相关问题 1. 如何基于 GTID 实现更安全的二进制日志恢复? 2. 主从复制中断时,如何利用 Binary Log 修复数据一致性? 3. Binary Log 的 ROW/STATEMENT/MIXED 格式对恢复有何影响? 4. 如何监控 Binary Log 的生成速率磁盘占用? [^1]: Binary log 是 MySQL 数据库的重要日志类型,它能够记录对数据库的所有修改操作[^1]。 [^2]: 删除旧的 Binary Log 文件可释放磁盘空间或维护数据库[^2]。 [^3]: 通过 `mysqlbinlog` 解析日志并定位操作位置[^3]。 [^4]: 基于位置点恢复不限定于某个特定的库或表[^4]。 [^5]: Redo Log 用于在 MySQL 崩溃恢复期间纠正由不完整事务写入的数据[^5]。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值