Mysql log_bin 日志文件恢复

mysql8.0默认有开启了logbin日志。如果是5.6需要手动开启logbin

修改my.cnf

[mysqld]

log_bin=/mysql_binlog/

增加此配置后就开启了logbin

1.登录mysql，查看当前的binlog日志文件

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000005 | 1602 | | | |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2.用mysqlbinlog查看日志文件里的内容：

查看POS值查找需要恢复的内容的区间。，或者根据时间搓来确定需要恢复的pos值的区间段。

mysqlbinlog /var/lib/mysql/binlog.000005

# The proper term is pseudo_replica_mode, but we use this compatibility alias

# to make the statement usable on server versions 8.0.24 and older.

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#231115 6:02:42 server id 1 end_log_pos 126 CRC32 0x4be0ed67 Start: binlog v 4, server v 8.0.35 created 231115 6:02:42

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

UqVUZQ8BAAAAegAAAH4AAAABAAQAOC4wLjM1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA

CigAAWft4Es=

'/*!*/;

# at 126

#231115 6:02:42 server id 1 end_log_pos 157 CRC32 0x0c482694 Previous-GTIDs

# [empty]

# at 157

#231115 6:03:33 server id 1 end_log_pos 236 CRC32 0x6b7a2c29 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1700046213441876 immediate_commit_timestamp=1700046213441876 transaction_length=279

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

# original_commit_timestamp=1700046213441876 (2023-11-15 06:03:33.441876 EST)

# immediate_commit_timestamp=1700046213441876 (2023-11-15 06:03:33.441876 EST)

/*!80001 SET @@session.original_commit_timestamp=1700046213441876*//*!*/;

/*!80014 SET @@session.original_server_version=80035*//*!*/;

/*!80014 SET @@session.immediate_server_version=80035*//*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 236

#231115 6:03:33 server id 1 end_log_pos 311 CRC32 0xc0866072 Query thread_id=16 exec_time=0 error_code=0

SET TIMESTAMP=1700046213/*!*/;

SET @@session.pseudo_thread_id=16/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1168113696/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

3.根据POS值区间进行恢复，将具体内容导出到指定文件。

[root@localhost.localdomain ~]#mysqlbinlog --start-position=1154 --stop-position=1301 /var/lib/mysql/binlog.000005 > /tmp/3.sql

4.进入mysql用source进行恢复。

恢复前先暂停事务

[root@localhost.localdomain ~]#mysqlbinlog --start-position=1154 --stop-position=1301 /var/lib/mysql/binlog.000005 > /tmp/3.sql

先查看目标内容，后续验证是否恢复成功。

mysql> use test

Database changed

mysql> select * from stu;

+------+-----------------+------+

| id | name | age |

+------+-----------------+------+

| 1 | 222东方闪电 | 22 |

| NULL | NULL | NULL |

| NULL | NULL | NULL |

| 1 | 1 | 222 |

+------+-----------------+------+

4 rows in set (0.00 sec)

使用source命令恢复

mysql> source /tmp/3.sql

Query OK, 0 rows affected, 1 warning (0.00 sec)

查看目标内容

最后将事务恢复。