mysql中truncate删除记录恢复笔记
truncate命令是mysql中删除记录一个命令它可以一次性删除当前表中所有记录并且不留任何日志了,同时这个表的ID就自动初化从1开始,今天我就来给大家尝试一个利用truncate清除记录之后恢复过程,希望给各位朋友解决你的问题.
实际线上的场景比较复杂,当时涉及了truncate,delete 两个操作,经确认丢数据差不多7万多行,等停下来时,差不多又有共计1万多行数据写入,这里为了简单说明,只拿弄一个简单的业务场景举例.
测试环境:Percona-Server-5.6.16
日志格式:mixed 没起用gtid
表结构如下:
CREATETABLE`tb_wubx`(`id`INT(11)NOTNULLAUTO_INCREMENT,`name`VARCHAR(32)DEFAULTNULL,PRIMARYKEY(`id`)) ENGINE=InnoDB AUTO_INCREMENT=2DEFAULT CHARSET=utf8
基于某个时间点有一个备份或是有全量的binlog是能恢复数据的一个唯一保证,例如我们的备份就是一个表结构创建语句,binlog pos相关信息,mysql-bin.000004,4,然后进行了如下:
-t1时间 程序写入:
insert into tb_wubx(name) values(‘张三’),(‘李四’);
insert into tb_wubx(name) values(‘隔壁老王’);
-t2时间 某个人员失误
truncate table tb_wubx;
-t3时间 程序写入
insert into tb_wubx(name) values(‘老赵’);
update tb_wubx set name=’老赵赵’ where id=1;
现在表里的数据情况,代码如下:
mysql>SELECT*FROM tb_wubx;
+----+-----------+| id | name |+----+-----------+|1| 老赵赵 |+----+-----------+1ROWINSET(0.00 sec)
可以见truncate table操作后,表的自增id又变更为从1开始,原来写入的数据应该是:
- +—-+———-+
- |id|name|
- +—-+———-+
- |1|张三|
- +—-+———-+
- |2|李四|
- +—-+———-+
- |3|隔壁老王|
- +—-+———-+
如果没生truncate table操作,实际的数据应该为:
- +—-+———-+
- |id|name|
- +—-+———-+
- |1|张三|
- +—-+———-+
- |2|李四|
- +—-+———-+
- |3|隔壁老王|
- +—-+———-+
- |4|老赵赵|
- +—-+———-+
而且线上的恢复那个表时和序序开发人员了解才知道,原来那个id和缓存及其它地方有依赖,因为id乱了，也会造成程序错乱。这个时间修复id在程序层错乱的事，留给开发人员了关建是给他们讲明白恢复的结果是什么样，我们的关建任务是把数据恢复出来。好，接下来的工作是开始从binlog中恢复数据。
利用:show binary logs; 查看当的log文件分布， 然后利用show binlog events in ‘binary log文件’; 查看log文件的内容，目的是找到truncate发生的日志位置。
另外因为基于备份（由log的启始位置）或是从量log, 如果基于备份有log的起始位置，我们需要处理的log文件是启始位置到发生truncate的日值（后面的数据处理不了，会发生主建冲突的错误造成truncate后的数据不能恢复）.
如果是全量日志，需要从创建完mysql后库后的日志去处理到当前的发生truncate的位置（后面数据会因为主建冲突写不进去）
恢复准备工作,创建一个库用于恢复数据,这里创建了一个re_wubx, 及原结构的表: tb_wubx ,相当于恢复了备份,过程省略,代码如下:
- mysql>SHOWBINARYlogs;
- +------------------+-----------+|Log_name|File_size|+------------------+-----------+|mysql-bin.000001|143||mysql-bin.000002|261||mysql-bin.000003|562||mysql-bin.000004|1144|+------------------+-----------+4ROWSINSET(0.00sec)
我这里有一个备份文件就是那个创建表的sql语句,位置是mysql-bin.000004,4在这个案例里我只用cover住mysql-bin.000004这个文件,代码如下:
- mysql>SHOWbinlogeventsIN'mysql-bin.000004';
- +------------------+------+-------------+-----------+-------------+----------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+------+-------------+-----------+-------------+----------------------------------------------------+|mysql-bin.000004|4|Format_desc|753306|120|Serverver:5.6.16-64.2-rel64.2-log,Binlogver:4||mysql-bin.000004|120|Query|753306|209|USE`wubx`;TRUNCATETABLEtb_wubx||mysql-bin.000004|209|Query|753306|281|BEGIN||mysql-bin.000004|281|Table_map|753306|334|table_id:91(wubx.tb_wubx)||mysql-bin.000004|334|Write_rows|753306|393|table_id:91flags:STMT_END_F||mysql-bin.000004|393|Xid|753306|424|COMMIT/*xid=1073*/||mysql-bin.000004|424|Query|753306|496|BEGIN||mysql-bin.000004|496|Table_map|753306|549|table_id:91(wubx.tb_wubx)||mysql-bin.000004|549|Write_rows|753306|602|table_id:91flags:STMT_END_F||mysql-bin.000004|602|Xid|753306|633|COMMIT/*xid=1074*/||mysql-bin.000004|633|Query|753306|722|USE`wubx`;TRUNCATETABLEtb_wubx||mysql-bin.000004|722|Query|753306|794|BEGIN||mysql-bin.000004|794|Table_map|753306|847|table_id:92(wubx.tb_wubx)||mysql-bin.000004|847|Write_rows|753306|894|table_id:92flags:STMT_END_F||mysql-bin.000004|894|Xid|753306|925|COMMIT/*xid=1081*/||mysql-bin.000004|925|Query|753306|997|BEGIN||mysql-bin.000004|997|Table_map|753306|1050|table_id:92(wubx.tb_wubx)||mysql-bin.000004|1050|Update_rows|753306|1113|table_id:92flags:STMT_END_F||mysql-bin.000004|1113|Xid|753306|1144|COMMIT/*xid=1084*/|+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19ROWSINSET(0.00sec)
- //开源代码phpfensi.com
看到这个表刚开始就发生一次truncate,那其实也可以说明我就恢复刚开始那个truncate到后来那个误操作的truncate table的语句之间的数据就是丢失的数据,这个恢复可以从mysql-bin.000004 pos: 4到mysql-bin.000004 pos:633,代码如下:
mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx
恢复结果如下:
- mysql-S/tmp/mysql.sockre_wubx;
- mysql>SELECTCOUNT(*)FROMtb_wubx;
- +----------+|COUNT(*)|+----------+|3|+----------+1ROWINSET(0.02sec)
- mysql>SELECT*FROMtb_wubx;
- +----+--------------+|id|name|+----+--------------+|1|张三||2|李四||3|隔壁老王|+----+--------------+3ROWSINSET(0.00sec)
- mysql>INSERTINTOtb_wubx(name)SELECTnameFROMwubx.tb_wubx;
- QueryOK,1ROWaffected(0.00sec)
- Records:1Duplicates:0Warnings:0
- mysql>RENAMETABLEwubx.tb_wubxTOwubx.bak_tb_wubx;
- QueryOK,0ROWSaffected(0.04sec)
- mysql>RENAMETABLEre_wubx.tb_wubxTOwubx.tb_wubx;
- QueryOK,0ROWSaffected(0.03sec)
- mysql>SELECT*FROMwubx.tb_wubx;
- +----+--------------+|id|name|+----+--------------+|1|张三||2|李四||3|隔壁老王||4|老赵赵|+----+--------------+4ROWSINSET(0.00sec)
恢复完成,想一想,如果我跳过那个truncate继续执行那些binlog会怎么样.
总结:从这次数据丢失给删除之后得到一些感想了我们还是要经常对数据库进行备份了,这样可以保证我们的数据不给丢失了,同时也可以保证我们网站数据安全.
热门评论