MySQL delete forgot add where condition mistakenly remove recovery

First, the MySQL environment introduction:

The MySQL database specifies the character set bit UTF8, and the table's character set is UTF8, while MySQL will turn on the Bin-log log for row mode

/ETC/MY.CNF file Character Set parameter settings:

[[email protected] ~]# grep character-set /etc/my.cnf[client]port = 3306socket = /tmp/mysql.sockdefault-character-set=utf8[mysqld]port = 3306socket = /tmp/mysql.sockcharacter-set-server = utf8

Also requires that the table's character set is also UTF8

MySQL [zixun3]> show create table  zixun3.zx_scores\G*************************** 1. row ***************************       Table: zx_scoresCreate Table: CREATE TABLE `zx_scores` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `titles` char(15) NOT NULL,  `icon` smallint(6) unsigned DEFAULT ‘0‘,  `integral` int(10) NOT NULL DEFAULT ‘0‘,  `isdefault` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,  PRIMARY KEY (`id`),  KEY `integral` (`integral`)) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf81 row in set (0.00 sec)

If the character set of the table is not uft8, modify the character set bit uft8 of the table, otherwise the SQL file parsed out in Mysqlbinlog will appear garbled for Chinese characters, resulting in the final return to the online table error

Modify the character set bit UTF8 of the table:
MySQL [zixun3]> alter table zx_scores convert to character set utf8;

Second, the accident treatment:

2018-05-16 development forget to add conditions when delete deletes data, directly execute delete from Zx_scores, causing all data in this table to be deleted

MySQL [zixun3]> delete from zx_scores;Query OK, 6 rows affected (0.00 sec)MySQL [zixun3]> select * from zx_scores;Empty set (0.00 sec)

The following is the process of recovering the data that was mistakenly deleted:

First, make sure that the delete error is written into the Binlog binary log file, and then resume the related row operation from the binary log. Because the row row format is enabled, each record

Changes, and so on, are logged in the binary log.

MySQL [zixun3]> show master status\G*************************** 1. row ***************************             File: mysql-bin.000036         Position: 7620     Binlog_Do_DB:  

If no one else flush logs, the error is recorded in the mysql_bin.000036 binary log file. If there is someone else flush logs. Just go to the above. Until a record of the misoperation is found.
Start recovery, on-line words, should be more complex, to first lock the table, so that the data is again contaminated. (lock table to see which binary log is being written)

mysql> lock table zixun3.zx_scores read;Query OK, 0 rows affected (0.00 sec)MySQL [zixun3]> show master status\G*************************** 1. row ***************************             File: mysql-bin.000036         Position: 7620     Binlog_Do_DB:  

Extract the May 16, 2018 DELETE statement from the Binlog log for data recovery

/usr/local/mysql/bin/mysqlbinlog  --no-defaults  --base64-output=decode-rows  -v -v -v /data/mysql/data/mysql-bin.000036 >/tmp/info.txtcat /tmp/info.txt |awk -F ‘[/*]+‘ ‘{print $1}‘ >/tmp/info1.txt

after processing the above 2 commands, the resulting SQL for the final binlog log file is as follows :

[[email protected] ~]# cat/tmp/info1.txt DELIMITER #at 4#180516 23:15:16 server ID 1 end_log_pos CRC32 0x183f43  Start:binlog v 4, Server v 5.6.36-log created 180516 23:15:16 at Startup#warning:this Binlog are either in use or was Not closed properly. Rollback#at 120#180516 23:15:39 Server ID 1 end_log_pos 194 CRC32 0x0a6c78ee Query thread_id=1 exec_time=0 error_code= 0SET timestamp=1526483739set @ @session. pseudo_thread_id=1set @ @session. Foreign_key_checks=1, @ @session. sql_auto_is _null=0, @ @session Unique_checks=1, @ @session. autocommit=1set @ @session. sql_mode=1075838976set @ @session. auto_ Increment_increment=1, @ @session. auto_increment_offset=1set @ @session. character_set_client=33,@@ session.collation_connection=33,@ @session. Collation_server=33set @ @session. Lc_time_names=0set @@ Session.collation_database=defaultbegin#at 194#180516 23:15:39 Server ID 1 end_log_pos 254 CRC32 0x10198556 Table_map: ' Zixun3 '. ' zx_scores ' mapped to number 70#at 254#180516 23:15:39 server ID 1 End_log_pos 517 CRC32 0x59f30b1c delete_rows:table ID flags:stmt_end_f### Delete from ' zixun3 '. ' Zx_scores ' # # # WHERE # # # @1=15 # # # # @2= ' General ' # # # # @3=12 # # # # # # @4=29000 # # @5=1 ' zixun3 '. ' Zx_scores ' # # # where### @1=12 # # # @2= ' # # # # @3=11 # # # # # # # # # # @4=24000 # # # # @5=1 # # DELETE from ' zixun3 '. ' Zx_scores ' # # where### @1=11 # # @2= ' # # # @3=10 # # # # # # # @4=19000 # # # @5=1 # # # DELETE from ' zixun3 '. ' Zx_scores ' # # where### @1=10 # # @2= ' Major General ' # # #    # @4=14000 # # # # @5=1 # # DELETE from ' zixun3 '. ' Zx_scores ' # # # where### @1=9 # # @2= ' Colonel ' # # # @3=8 # # # @4=9000 # # # @5=1 # # # DELETE from ' zixun3 '. ' Zx_scores ' # # # where### @1=8 # # @2= ' Lieutenant Colonel ' # # # @3=7 # # @4=6000 # # # @5=1 TE from ' zixun3 '. ' Zx_scores ' # # # # # where### @1=7 # # # @2= ' Major ' # # # @3=6 # # @4=5000 # # # @5=1 # # # DELETE from ' Zixun3 ' . ' Zx_scores ' # # # # where### @1=6 # # # @2= ' Captain ' # # # @3=5 # # @4=4000 # # @5=1 # # # DELETE from ' zixun3 '. ' Zx_scores ' # # # where### @1=5 # # # @2= ' Lieutenant ' # # # # # # # @3=4 # # # # # @4=3000 # # # @5=1 # # DELETE from ' zixun3 '. ' Zx_scores ' # # where### @1=4 # # @2= ' Ensign ' # # # # # # @4=2000 # # # # # @5=1 # # DELETE from ' zixun3 '. ' Zx_scores ' # # # where### @1=3 # # @2= ' Squad Leader ' # # # @3=2 # @4=1000 # # # @5=1 # # # DELETE from ' zixun3 '. ' Zx_scores ' # # # where### @1=2 # # @2= ' Private ' # # # @3=1 # # @4=0 # @5=1 #at 517#18 0516 23:15:39 Server ID 1 end_log_pos 592 CRC32 0x36661206 Query thread_id=1 exec_time=0 error_code=0set timestamp=152  6483739COMMITDELIMITER; #End of Log Filerollback

Iii. extract script for deleted data

[[email protected] ~]# cat /tmp/test.sh #!/bin/bash#bl表列数(表字段) #语句yjbl=5yj=DELETEzs1=`awk ‘/#180516/,/#180517/ {print $0}‘ /tmp/info1.txt|awk ‘"/$yj/",/# at/ {print $0}‘|grep ^###|grep "@"|cut -d"=" -f2`zs2=`echo $zs1|awk ‘{print NF}‘`zt=`echo "$zs2/$bl"|bc`hs=0##databa指数据库  ###tab指表 ###a1--a5指的是此表一共有5个字段databa=zixun3tab=zx_scoresztt=$(($zt+0))ii=0a1=1a2=2a3=3a4=4a5=5  while [[ $ii -lt $ztt ]];dol1=`echo $zs1|awk ‘{print $‘"$a1"‘}‘`l2=`echo $zs1|awk ‘{print $‘"$a2"‘}‘`l3=`echo $zs1|awk ‘{print $‘"$a3"‘}‘`l4=`echo $zs1|awk ‘{print $‘"$a4"‘}‘`echo "use $databa;insert into $tab values($l1,$l2,$l3,$l4,$l5)" >>/tmp/hfa1=$(($a1+$bl))a2=$(($a2+$bl))a3=$(($a3+$bl))a4=$(($a4+$bl))a5=$(($a5+$bl))ii=$(($ii+1));  done

Iv. successful script extraction of data

[[email protected] ~]# sh /tmp/test.sh [[email protected] ~]# cat /tmp/hf use zizun3;insert into zx_scores values(15,‘大将‘,12,29000,1);use zizun3;insert into zx_scores values(12,‘上将‘,11,24000,1);use zizun3;insert into zx_scores values(11,‘中将‘,10,19000,1);use zizun3;insert into zx_scores values(10,‘少将‘,9,14000,1);use zizun3;insert into zx_scores values(9,‘上校‘,8,9000,1);use zizun3;insert into zx_scores values(8,‘中校‘,7,6000,1);use zizun3;insert into zx_scores values(7,‘少校‘,6,5000,1);use zizun3;insert into zx_scores values(6,‘上尉‘,5,4000,1);use zizun3;insert into zx_scores values(5,‘中尉‘,4,3000,1);use zizun3;insert into zx_scores values(4,‘少尉‘,3,2000,1);use zizun3;insert into zx_scores values(3,‘班长‘,2,1000,1);use zizun3;insert into zx_scores values(2,‘列兵‘,1,0,1);

V. Recovering data into the database

MySQL [zixun3]> INSERT INTO zx_scores values (12, ' Admiral ', 11,24000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (11, ' will ', 10,19000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (10, ' Major General ', 9,14000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (9, ' Colonel ', 8,9000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (8, ' Lieutenant Colonel ', 7,6000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (7, ' major ', 6,5000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (6, ' Capt ', 5,4000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (5, ' Lieutenant ', 4,3000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (4, ' Ensign ', 3,2000,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> insert INTO zx_scores values (3, ' Monitor ', 2,1000,1); Query OK, 1 row affected (0. xx sec) MySQL [zixun3]> insert INTO zx_scores values (2, ' Pvt ', 1,0,1); Query OK, 1 row Affected (0.00 sec) MySQL [zixun3]> select * from Zx_scores, +----+--------+------+----------+---------- -+| ID | Titles | icon | Integral |  IsDefault |+----+--------+------+----------+-----------+| 2 |    Pvt |        1 |         0 |  1 | | 3 |    Monitor |     2 |         1000 |  1 | | 4 |    Ensign |     3 |         2000 |  1 | | 5 |    Lieutenant |     4 |         3000 |  1 | | 6 |    Captain |     5 |         4000 |  1 | | 7 |    Major |     6 |         5000 |  1 | | 8 |    Lieutenant Colonel |     7 |         6000 |  1 | | 9 |    Colonel |     8 |         9000 | 1 | | 10 |    Major General |    9 |         14000 | 1 | | 11 |   Lieutenant |    10 |         19000 | 1 | | 12 |   Admiral |    11 |         24000 | 1 | | 15 |   Generals |    12 |         29000 | 1 |+----+--------+------+----------+-----------+12 rows in Set (0.00 sec)

The data deleted here has been restored to completion.
Tip: After many tests, this script in the recovery of data, the database table structure is required, not mistakenly delete any table structure of the data in order to use this script can be recovered, so this script has great limitations.
The self-script can only be deleted by mistake in the table that restores simple tables fields

MySQL delete forgot add where condition mistakenly remove recovery