binlog内容详解及重放

binlog内容详解及重放

binlog内容详解

# at 4
#190526 11:15:37 server id 1  end_log_pos 123 CRC32 0x8a8715f9  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1558892137/*!*/;
BEGIN
/*!*/;
# at 123
#190526 11:15:37 server id 1  end_log_pos 154 CRC32 0xb2b13677  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1558892137/*!*/;
INSERT INTO `users` (`id`, `name`, `email`) VALUES (1, 'rc', 'rc@example.com')
/*!*/;
# at 154
#190526 11:15:37 server id 1  end_log_pos 184 CRC32 0x873c7864  Xid = 1
COMMIT/*!*/;
# at 185
#190526 11:15:37 server id 1  end_log_pos 216 CRC32 0x37b84fcc  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1558892137/*!*/;
BEGIN
/*!*/;
# at 216
#190526 11:15:37 server id 1  end_log_pos 247 CRC32 0x6e08ebef  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1558892137/*!*/;
INSERT INTO `users` (`id`, `name`, `email`) VALUES (2, 'rc', 'rc@example.com')
/*!*/;
# at 247
#190526 11:15:37 server id 1  end_log_pos 277 CRC32 0x4a9929d4  Xid = 2
COMMIT/*!*/;

上面的内容包含了两个事务，每个事务包含了一个插入操作。下面是对上述内容的详细解释：

• #at：表示写入binlog文件的位置。
• #190526 11:15:37：表示操作发生的时间。
• server id 1：表示MySQL实例的ID。
• end_log_pos：表示这个事件结束时在binlog文件中的位置。
• CRC32：用于校验数据的循环冗余校验值。
• Query：表示这个事件是由一条SQL语句触发的。
• thread_id：表示执行这个事件的线程ID。
• exec_time：表示执行这个事件所花费的时间。
• error_code：表示执行这个事件是否产生错误。
• use：表示使用某个数据库。
• BEGIN：表示开始一个事务。
• INSERT INTO：表示进行一条插入操作。
• Xid：表示一个事务的唯一标识。
• COMMIT：表示提交一个事务。

mysqlbinlog 的常用参数

• --database 仅仅列出配置的数据库信息
• --no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令
• --offset 跳过 log 中 N 个条目
• --verbose 将日志信息重建为原始的 SQL 陈述。
  • -v 仅仅解释行信息
  • -vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来
• --start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
• --stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
  • 接收 DATETIME 或者 TIMESTRAMP 格式。
• --base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽。
  • AUTO 默认参数，自动显示 BINLOG 中的必要的语句
  • NEVER不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。
  • DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据。
• --start-position：从二进制日志中读取指定position 事件位置作为开始。
• --stop-position：从二进制日志中读取指定position 事件位置作为事件截至

重放MySQL的binlog文件

使用mysqlbinlog工具重放

重放MySQL的binlog文件可以使用mysqlbinlog工具。下面是一个简单的步骤：

1. 确定要恢复的时间点和操作，并找到相应的binlog文件。

2. 在MySQL服务器上flush logs 从新生成日志文件。

3. 将所需binlog文件复制到恢复数据库

执行以下命令来查看当前的binlog文件：

SHOW MASTER STATUS;

使用以下命令来重放binlog文件：

mysqlbinlog binlog-file | mysql -h server -u root -p

其中，binlog-file指代要重放的binlog文件，server表示MySQL服务器的IP地址。

请注意，在执行mysqlbinlog命令时，可能需要使用一些选项来确保成功重放binlog文件。例如，您可能需要使用–base64-output=decode-rows选项来解码二进制数据，或者使用–database选项来指定要重放的数据库。

• 指定起止位置重放
  可以使用--start-position和--stop-position选项来指定要重放的binlog文件的起始和结束位置。
  例如，要从binlog文件的第100个字符位置开始重放，并在第200个字符位置结束，可以使用以下命令：

mysqlbinlog --start-position=100 --stop-position=200 bin-log-file | mysql -h server -u root -p

其中，bin-log-file是要重放的binlog文件的名称，server是MySQL服务器的IP地址。

• 指定数据库重放
  是的，如果要从binlog文件中重放指定数据库的操作，可以使用--database选项来指定要重放的数据库。
  例如，要重放名为test的数据库在binlog文件中的操作，可以使用以下命令：

mysqlbinlog --database=test bin-log-file | mysql -h server -u root -p test

其中，test 是数据库名称，bin-log-file是要重放的binlog文件的名称，server是MySQL服务器的IP地址。

• 指定起止时间重放
  可以使用--start-datetime和--stop-datetime选项来指定要重放的时间段。这些选项允许您仅重放在指定时间段内发生的操作。
  以下是一个示例命令：

mysqlbinlog --start-datetime="2022-01-01 00:00:00" --stop-datetime="2022-01-01 02:00:00" bin-log-file | mysql -h server -u root -p

其中，bin-log-file是要重放的binlog文件的名称，server是MySQL服务器的IP地址。该命令将仅重放在2022年1月1日00:00:00至02:00:00期间发生的操作。

请注意，–start-datetime和–stop-datetime选项需要以YYYY-MM-DD HH:MM:SS格式指定日期和时间。

使用MySQL Cli重放

1. 确认新实例已经启用binlog功能，可以在新实例的my.cnf配置文件中添加以下参数：

[mysqld]
log-bin=/var/lib/mysql/mysql-bin

2. 将下载的binlog复制到新实例的logdir中：

cp /path/to/mysql-bin.* /var/lib/mysql/

3. 将除最后一个binlog之外的binlog重命名为relaylog：

cd /var/lib/mysql
for file in $(ls -1 mysql-bin.* | head -n -1); do
    mv $file MySQL2-relay-bin.$(echo $file | awk -F "." '{print $NF}');
done

4. 使用mysqlbinlog命令生成index文件：

mysqlbinlog --no-defaults --base64-output=never MySQL2-relay-bin.* > MySQL2-relay-bin.index

5. 将这些文件复制到data文件中：

cp MySQL2-relay-bin.* /var/lib/mysql/

6. 文件赋权：

chown -R mysql:mysql /var/lib/mysql/

7. 启动mysql实例：

systemctl start mysql

8. 执行CHANGE MASTER TO命令，指定一个空的主库地址创建SQL线程,然后根据备份记录的binlogfile和binlogpos来设置。
   mysql> CHANGE MASTER TO
   MASTER_HOST=‘1.1.1.1’,
   RELAY_LOG_FILE=‘MySQL2-relay-bin.xxxxxx’,
   RELAY_LOG_POS=xxxxxx;
9. 启动slave sql_thread：

mysql> START SLAVE SQL_THREAD;

10. 检查重载状态：

mysql> show slave status\G

参考文档

1. Mysql数据库按时间点恢复实战
2. 使用binlog日志恢复误删的MySQL数据
3. MySQL 5.7 - 通过 BINLOG 恢复数据