mysql恢复delete的数据

1.定位日志文件

登录到mysql终端:
mysql -h127.0.0.1 -P3306 -uUSERNAME -p'dDATABASE_NAME'

1.1 show master logs查日志文件列表:

mysql> show master logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000807 | 1073742045 |
| mysql-bin.000808 |  771435652 |
+------------------+------------+

1.2 SHOW VARIABLES LIKE 'log_bin_basename'查日志文件目录:

mysql> SHOW VARIABLES LIKE 'log_bin_basename';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| log_bin_basename | /var/lib/mysql/mysql-bin |
+------------------+--------------------------+

2.下载操作binlog日志(非必需)

下载delete所在大致时间范围内的binlog日志, 确认删除数据的具体时间(保险起见)

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2025-03-29 15:42:10" --stop-datetime="2025-03-29 15:42:11" mysql-bin.000808 > binlog4.sql

3.确认时间点后生成恢复语句sql(binlog2sql)

python binlog2sql.py -h127.0.0.1 -P3306 -uUSERNAME -p'PASSWORD' -dDATABASE_NAME -tTABLE_NAME --start-file='mysql-bin.000808' --start-datetime="2025-03-29 15:42:00" --stop-datetime="2025-03-29 15:43:00" -B > rollback.sql

附:binlog2sql使用
附:由于我的linux安装binlog2sql不能直接用pip安装(下载不来),是手动找的requirements.txt里的依赖下载上传到linux一个一个离线安装的:

mysql-replication-0.13:     pip3 setup.py install
sudo pip install wheel-0.29.0-py2.py3-none-any.whl 
sudo pip install PyMySQL-0.7.11-py2.py3-none-any.whl 
chmod u+x binlog2sql.py

4.执行恢复语句rollback.sql

mysql -h127.0.0.1 -P3306 -uUSERNAME -p'dDATABASE_NAME' < rollback.sql