mysql误删数据恢复方案
<h2>一、开启二进制日志（已开启忽略）</h2>
<p>linux环境：
vim /etc/my.cnf</p>
<pre><code class="language-java">#开启bin-log
log_bin=/risen/soft/database/mysql/mysql_bin/bin/mysql-bin
server-id=231
#定期清理二进制日志binlog
expire_logs_days = 1
##binlog大小
max_binlog_size = 100M</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/6a684989338ee97ae068292b73f0a4cb" alt="" />
Windows环境：
编辑安装目录下my.ini文件，添加以下内容</p>
<pre><code class="language-java">#开启bin-log
log_bin=D:\access\mysql-5.7.28-winx64/binlog
server-id=231
#定期清理二进制日志binlog
expire_logs_days = 1
##binlog大小
max_binlog_size = 100M</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/75cdc1ef0ed1cfd7270f6163152a9e51" alt="" /></p>
<h2>二、重启mysql服务配置生效，查看二进制配置信息</h2>
<p>使用命令show VARIABLES like '%log_bin%';查看
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/7dd1613ce0c075cb4cb8aaf1cbe00721" alt="" /></p>
<h2>三、全库备份（恢复之前一定记得全库备份）</h2>
<h2>四、模拟误删场景</h2>
<p>1.创建测试库</p>
<pre><code class="language-java">CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;</code></pre>
<p>2.创建测试表core_account</p>
<pre><code class="language-java">CREATE TABLE `core_account` (
`CAMH_UNID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unid',
`CAMH_UUID` varchar(48) NOT NULL COMMENT 'Uuid',
`CAMH_CREATOR` varchar(48) DEFAULT NULL COMMENT '创建者',
PRIMARY KEY (`CAMH_UNID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='账户表';</code></pre>
<p>3.批量插入数据
创建函数</p>
<pre><code class="language-java">create PROCEDURE 批量插入数据()
BEGIN
DECLARE i int;
set i = 1;
WHILE i &lt; 10 DO
insert into core_account(CAMH_UNID,CAMH_UUID,CAMH_CREATOR) values(i+1,i+2,i+3);
set i = i+1;
END WHILE;
END</code></pre>
<p>执行函数
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/2c2aa9819b92faba5481c930fadd99ad" alt="" />
4.查看数据
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/18d81adb21cbb9058fd337980a747538" alt="" />
5.删除id为5的数据
DELETE from core_account WHERE CAMH_UNID='5';
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/7e9ce9415441e321470b6270a918c697" alt="" />
6.找到最新的二进制日志文件，记录文件名
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/fdf9bc20464c99ec86ac766abc25d57a" alt="" />
7.打印二进制文件内容,并输出到mysqlbin.log日志文件中
mysqlbinlog --no-defaults mysql-bin.000022 >/opt/mysqlbin.log
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/be5aaf8e341de9de65be53edbb9b799a" alt="" />
记录创建库前id：219
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/3c2c279580a2f8dea70e472246ca3011" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/83e9863e62ec8132d32bf6da71cef259" alt="" />
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/953cd9d9b8826d3868ddb976b44eee50" alt="" />
记录删除数据前end_log_pos的id为4895
8.指定时间点还原
删除库test，回到最原始的地方</p>
<pre><code class="language-java">drop database test;</code></pre>
<p>指定时间段恢复</p>
<pre><code class="language-java">mysqlbinlog --no-defaults --start-position=&quot;219&quot; --stop-position=&quot;4895&quot; mysql-bin.000022 |mysql -uroot -pxmzcb*2021</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/3e36e62370035ac24fe6c38060057f7e" alt="" />
重新查询表数据，已恢复
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/9c4f83cf7e32dbc5aaa3e5461ba8e769" alt="" /></p>