数据库进阶教学——数据库故障恢复（日志文件）

目录

一、日志简介

二、日志文件操作

1、查看日志状态

2、开启日志功能

 3、查看日志文件

4、查看当前日志

5、查看日志中的事件

6、删除日志文件

7、查看和修改日志文件有效期

8、查看日志文件详细信息

三、删除的数据库恢复

---

一、日志简介

• 日志是记录所有数据库表结果变更以及表数据修改的二进制日志，而不会记录SELECT和SHOW这类的操作。
• Binlog文件名默认为“主机名_binlog-序列号”格式，例如：stu_binlog-000001，也可以在配置文件中指定名称。
• 文件记录模式有STATEMENT、ROW和MIXED三种：
  • ROW（row-based replication,RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
  • STATEMENT（statement-based replication，SBR）：每一条被修改数据SQL都会记录到master的日志中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行，简称SQL语句复制。
  • MIXED（mixed-based replication，MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存日志，对于STATEMENT模式无法复制的操作则使用ROW模式来保存日志，MySQL会根据执行的SQL语句来选择写入的模式。

二、日志文件操作

1、查看日志状态

• show variables like "log_bin";
  或
  show variables like "%log_bin%";

• 

2、开启日志功能

• set global log_bin=1;

• 【注】如遇到下面错误。
  • 
  • 原因：mysql二进制日志记录(一个问题牵扯出更多问题) - 简书 (jianshu.com)

 3、查看日志文件

• show binary logs;
  或
  show master logs;

•   

4、查看当前日志

• show master status;

• 

5、查看日志中的事件

• # 查看当前日志文件中的事件
  show binlog events;
  # 查看指定日志文件中的事件
  show binlog events in 'DESKTOP-81MU6JA-bin.000012';

• 
• 
• 【注】上一个事件的结束位置，就是下一个事件的开始位置。
  • 

6、删除日志文件

• # 删除指定的日志文件
  purge binary logs to 'DESKTOP-81MU6JA-bin.000012';
  # 删除指定时间之前的文件
  purge binary logs before '2023-11-02 00:00:00';
  # 清除所有日志文件
  reset master;

7、查看和修改日志文件有效期

• # 查看日志文件的有效期
  show variables like '%binlog_expire_logs_seconds';
  # 默认有效期为0，表示日志的自动清理功能没有启用
  # 设置日志文件的有效期（启用日志自动清理）,设置为1，表示超出1天，日志文件会自动删除（单位为秒）
  set global binlog_expire_logs_seconds=60*60*24;

•  
• 【注】原本是show variables like '%expire_logs_days%'，即按天设置，但是已经弃用了。

8、查看日志文件详细信息

•  找到data文件夹（一般在mysql的安装路径下），在data文件夹下以管理员身份打开终端。输入下列命令：

  • mysqlbinlog DESKTOP-81MU6JA-bin.000012

• 【注】data文件夹如果不在mysql的安装路径，参考：mysql没有data和my.ini文件怎么办？_mysql没有data文件夹_云边的快乐猫的博客-CSDN博客
• 

三、删除的数据库恢复

•  查看要删除的数据库。
  • 
• 查看该数据库中的内容。
  • 
• 查看当前日志文件。
  • 
• 查看该日志文件中的事件。
  • 
• 删除advanced_database数据库。
  • 
• 恢复该数据库，有两种方式。
• 方式一：利用事件的起始号和结束号位置进行恢复。
  • 查看日志文件中的信息，找到需要取得事件的起始号和结束号。
    • 
    • 
  • 恢复数据库。
    • 找到data文件夹（一般在mysql的安装路径下），在data文件夹下打开终端。输入下列命令：

      • mysqlbinlog --start-position=157 --stop-position=4696 DESKTOP-81MU6JA-bin.000012 | mysql -u root -p
        Enter password: 数据库密码

    • 【注】data文件夹如果不在mysql的安装路径，参考：mysql没有data和my.ini文件怎么办？_mysql没有data文件夹_云边的快乐猫的博客-CSDN博客
    • 
    • 再次查看，该数据库已经恢复。
    • 
• 方式二：利用指定的时间来恢复。
  •  删除advanced_database数据库。
    • 
  • 查看日志文件详细信息，找到对应的时间点。
    • 
    • 
    • 
  • 恢复数据库。
    • 在data文件夹下打开终端，输入下列命令：

    • mysqlbinlog --start-datetime="2023-11-02 19:03:18" --stop-datetime="2023-11-03 21:23:09" DESKTOP-81MU6JA-bin.000012 | mysql -u root -p
      Enter password: 数据库密码

    • 
    • 再次查看，该数据库已经恢复。       
    •