MySQL Undo/Redo Log使用详解

# 查看redo log配置
show variables like 'innodb_log%';
# 重要参数：
# innodb_log_file_size = 每个文件大小（默认48m-2g）
# innodb_log_files_in_group = 文件数量（默认2）
# innodb_log_buffer_size = 缓冲区大小（默认16m）

# checkpoint过程示意
def checkpoint_process():
    # 1. 找到最老的脏页lsn（log sequence number）
    oldest_dirty_lsn = find_oldest_dirty_page()
    
    # 2. 将这个lsn写入redo log头
    write_checkpoint_to_redo(oldest_dirty_lsn)
    
    # 3. 刷新脏页到磁盘
    flush_dirty_pages_to_disk()
    
    # 4. 清理redo log空间
    # 从checkpoint前的redo log可以安全覆盖

-- undo log段管理
create table t (
    id int primary key,
    name varchar(20),
    balance decimal(10,2)
) engine=innodb;

-- undo log包含：
-- 1. 回滚指针：db_roll_ptr（指向旧版本）
-- 2. 事务id：db_trx_id（最近修改的事务id）
-- 3. 删除标记：deleted_bit

-- 读已提交（read committed）隔离级别下的可见性判断
select * from users where id=1;

-- innodb判断流程：
-- 1. 获取当前事务id: current_trx_id
-- 2. 获取当前活跃事务列表: active_trx_list
-- 3. 从当前行开始遍历版本链：
--    - 如果 trx_id < current_trx_id 且 trx_id不在活跃列表中
--      且 trx_id <= up_limit_id（快照上限）
--    - 则该版本对当前事务可见
-- 4. 否则继续查找更早版本

start transaction;
-- 步骤1：a账户扣款
update accounts set balance=balance-100 where id=1;
-- undo log记录: (balance=原始值, trx_id=当前事务)
-- redo log记录: 页面修改信息

-- 步骤2：b账户入账  
update accounts set balance=balance+100 where id=2;
-- undo log记录: (balance=原始值, trx_id=当前事务)
-- redo log记录: 页面修改信息

commit;
-- binlog写入 → redo log状态改为commit

-- 危险的长时间查询
start transaction;
select * from large_table where ... for update;
-- 执行复杂业务逻辑（耗时10分钟）
-- ... 
commit;

-- 问题：
-- 1. undo log无法清理，导致undo表空间膨胀
-- 2. 阻塞purge线程
-- 3. 可能触发“事务id耗尽”问题

-- 监控长时间运行的事务
select 
    trx_id,
    trx_started,
    timediff(now(), trx_started) as duration,
    trx_state,
    trx_operation_state
from information_schema.innodb_trx
where timediff(now(), trx_started) > '00:05:00'
order by trx_started;

-- 监控undo log使用
show engine innodb status\g
-- 查看"transactions"部分

-- 错误的批量更新（产生大量undo）
start transaction;
update large_table set status=1 where create_date < '2024-01-01';
-- 影响100万行，产生大量undo log
commit;

-- 优化的批量更新
set autocommit=0;
set unique_checks=0;
set foreign_key_checks=0;

-- 分批次处理
declare done int default false;
declare batch_size int default 1000;
declare last_id int default 0;

repeat
    start transaction;
    
    update large_table 
    set status=1 
    where id > last_id 
    and create_date < '2024-01-01'
    limit batch_size;
    
    set last_id = last_insert_id();
    
    commit;  -- 每个批次提交，释放undo
    
    select sleep(0.1);  -- 避免过度消耗资源
    
until row_count() = 0 end repeat;

set autocommit=1;
set unique_checks=1;
set foreign_key_checks=1;

# my.cnf配置示例
[mysqld]
# redo log文件大小，建议设置为缓冲池的1/4到1/2
# 对于8g内存，buffer pool通常6g，redo log设为2g
innodb_log_file_size = 2g
innodb_log_files_in_group = 4  # 总大小=2g*4=8g

# log buffer大小，大事务可适当调大
innodb_log_buffer_size = 64m

# 刷盘策略，根据数据安全需求选择
# 1-最安全，每次提交都刷盘（默认）
# 2-折中，每次提交写os缓存，每秒刷盘
# 0-性能最好，每秒刷盘，可能丢失1秒数据
innodb_flush_log_at_trx_commit = 1

-- mysql 8.0+ 独立undo表空间
-- 查看undo配置
select * from information_schema.innodb_tablespaces 
where space_type = 'undo';

-- 监控undo空间使用
select 
    tablespace_name,
    file_size / 1024 / 1024 as file_size_mb,
    allocated_size / 1024 / 1024 as allocated_mb
from information_schema.files
where file_name like '%undo%';

-- 设置undo表空间自动清理
set global innodb_undo_log_truncate = on;
set global innodb_max_undo_log_size = 1 * 1024 * 1024 * 1024;  -- 1gb
set global innodb_purge_rseg_truncate_frequency = 128;

-- 场景：秒杀活动，大量并发写入
-- 问题：redo log成为瓶颈

-- 解决方案1：临时调整刷盘策略（活动期间）
set global innodb_flush_log_at_trx_commit = 2;
-- 活动结束后恢复为1

-- 解决方案2：组提交优化
-- mysql已自动支持，确保参数合理
show variables like 'binlog_group%';
show variables like 'innodb_flush_log_at_timeout';

-- 解决方案3：拆分热点数据
-- 将热点账户分散到不同数据页
update account_001 set ... where user_id=1;  -- 分表

# 1. 检查redo log状态
mysql> show engine innodb status\g

# 2. 如果有备份，优先使用备份恢复
# 使用percona xtrabackup或mysqldump备份

# 3. 强制恢复模式（谨慎使用）
# 在my.cnf中添加
[mysqld]
innodb_force_recovery = 1  # 1-6，数字越大越激进

# 4. 恢复步骤
# 4.1 停止mysql
sudo systemctl stop mysql

# 4.2 备份原数据文件
cp -r /var/lib/mysql /var/lib/mysql_backup

# 4.3 移除损坏的redo log
mv /var/lib/mysql/ib_logfile* /tmp/

# 4.4 启动mysql（会自动创建新的redo log）
sudo systemctl start mysql

# 4.5 使用mysqlbinlog恢复未同步的数据
mysqlbinlog mysql-bin.000001 | mysql -u root -p

-- 1. redo log监控
select 
    'redo log' as metric,
    concat(round(sum(length)/1024/1024, 2), ' mb') as current_size,
    concat(round(variable_value/1024/1024, 2), ' mb') as configured_size,
    round(sum(length)*100/variable_value, 2) as usage_percent
from information_schema.innodb_buffer_page
join information_schema.global_variables 
    on variable_name = 'innodb_log_file_size'
where page_type like 'ibuf%'
group by variable_value;

-- 2. undo空间监控
select 
    format_bytes(sum(current_size)) as active_undo_size,
    format_bytes(sum(undo_size)) as total_undo_size,
    count(*) as undo_segments,
    round(sum(current_size)*100/sum(undo_size), 2) as usage_pct
from information_schema.innodb_metrics
where name like '%undo%';

-- 3. 长事务和undo关联查询
select 
    r.trx_id as waiting_trx_id,
    r.trx_mysql_thread_id as waiting_thread,
    timediff(now(), r.trx_started) as wait_time,
    b.trx_id as blocking_trx_id,
    b.trx_mysql_thread_id as blocking_thread,
    bl.lock_table as locked_table
from information_schema.innodb_lock_waits w
inner join information_schema.innodb_trx b 
    on b.trx_id = w.blocking_trx_id
inner join information_schema.innodb_trx r 
    on r.trx_id = w.requesting_trx_id
inner join information_schema.innodb_locks bl 
    on bl.lock_id = w.blocking_lock_id;