日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

原文鏈接：
https://www.modb.pro/db/26098（復制鏈接至瀏覽器，即可查看）

本文詳述MySQL 5.7 模擬update誤操作后進行數據恢復的全過程，希望對大家有幫助。

背景介紹

MySQL目前還沒有像Oracle數據庫那樣強大有閃回的功能，MySQL只能通過挖去binlog日志的方法來獲取數據，但是有個前提就是binlog_format必須設置成row。下面通過一個案例來演示。

注意：嚴禁在生產環境測試

表結構

測試的表結構如下：

CREATE TABLE `update_test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間' ,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

修改數據

插入和修改測試數據，對user_id字段的內容進行了修改。

insert into update_test (Select * from recordss_memory limit 20);

查看修改前的數據：

root@localhost#mysql.sock : tc0110:56:12>select * from update_test;
+----+-------------+----------+----------+--------+---------------------+
| id | user_id     | vote_num | group_id | status | create_time         |
+----+-------------+----------+----------+--------+---------------------+
|  1 | ddddddddddd |     4502 |        2 |      1 | 2020-06-04 11:34:17 |
|  2 | ddddddddddd |     5564 |        1 |      1 | 2020-06-04 11:34:17 |
|  3 | ddddddddddd |     3521 |        2 |      1 | 2020-06-04 11:34:17 |
|  4 | ddddddddddd |     1414 |        0 |      1 | 2020-06-04 11:34:17 |
|  5 | ddddddddddd |     8047 |        1 |      1 | 2020-06-04 11:34:17 |
|  6 | ddddddddddd |     5556 |        1 |      1 | 2020-06-04 11:34:17 |
|  7 | ddddddddddd |     7166 |        1 |      2 | 2020-06-04 11:34:17 |
|  8 | ddddddddddd |     3277 |        2 |      2 | 2020-06-04 11:34:17 |
|  9 | ddddddddddd |     8658 |        2 |      1 | 2020-06-04 11:34:17 |
| 10 | ddddddddddd |     4146 |        0 |      2 | 2020-06-04 11:34:17 |
| 11 | ddddddddddd |     7906 |        2 |      1 | 2020-06-04 11:34:17 |
| 12 | ddddddddddd |      512 |        0 |      2 | 2020-06-04 11:34:17 |
| 13 | ddddddddddd |     7493 |        0 |      1 | 2020-06-04 11:34:17 |
| 14 | ddddddddddd |     5583 |        1 |      1 | 2020-06-04 11:34:17 |
| 15 | ddddddddddd |     4273 |        2 |      1 | 2020-06-04 11:34:17 |
| 16 | ddddddddddd |     1117 |        0 |      1 | 2020-06-04 11:34:17 |
| 17 | ddddddddddd |     3936 |        2 |      1 | 2020-06-04 11:34:17 |
| 18 | ddddddddddd |     4735 |        2 |      1 | 2020-06-04 11:34:17 |
| 19 | ddddddddddd |     2505 |        0 |      1 | 2020-06-04 11:34:17 |
| 20 | ddddddddddd |     2523 |        2 |      1 | 2020-06-04 11:34:17 |
+----+-------------+----------+----------+--------+---------------------+
20 rows in set (0.00 sec)

root@localhost#mysql.sock : tc0110:56:49>show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 536871032 |
| mysql-bin.000002 | 536871341 |
| mysql-bin.000003 | 197210338 |
+------------------+-----------+
3 rows in set (0.00 sec)

root@localhost#mysql.sock : tc0110:57:29> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 536871032 |
| mysql-bin.000002 | 536871341 |
| mysql-bin.000003 | 197210338 |
+------------------+-----------+
3 rows in set (0.00 sec)

root@localhost#mysql.sock : tc0110:57:54> update update_test set user_id='ture';

root@localhost#mysql.sock : tc0110:59:05>select * from update_test;
+----+---------+----------+----------+--------+---------------------+
| id | user_id | vote_num | group_id | status | create_time         |
+----+---------+----------+----------+--------+---------------------+
|  1 | ture    |     4502 |        2 |      1 | 2020-06-04 11:34:17 |
|  2 | ture    |     5564 |        1 |      1 | 2020-06-04 11:34:17 |
|  3 | ture    |     3521 |        2 |      1 | 2020-06-04 11:34:17 |
|  4 | ture    |     1414 |        0 |      1 | 2020-06-04 11:34:17 |
|  5 | ture    |     8047 |        1 |      1 | 2020-06-04 11:34:17 |
|  6 | ture    |     5556 |        1 |      1 | 2020-06-04 11:34:17 |
|  7 | ture    |     7166 |        1 |      2 | 2020-06-04 11:34:17 |
|  8 | ture    |     3277 |        2 |      2 | 2020-06-04 11:34:17 |
|  9 | ture    |     8658 |        2 |      1 | 2020-06-04 11:34:17 |
| 10 | ture    |     4146 |        0 |      2 | 2020-06-04 11:34:17 |
| 11 | ture    |     7906 |        2 |      1 | 2020-06-04 11:34:17 |
| 12 | ture    |      512 |        0 |      2 | 2020-06-04 11:34:17 |
| 13 | ture    |     7493 |        0 |      1 | 2020-06-04 11:34:17 |
| 14 | ture    |     5583 |        1 |      1 | 2020-06-04 11:34:17 |
| 15 | ture    |     4273 |        2 |      1 | 2020-06-04 11:34:17 |
| 16 | ture    |     1117 |        0 |      1 | 2020-06-04 11:34:17 |
| 17 | ture    |     3936 |        2 |      1 | 2020-06-04 11:34:17 |
| 18 | ture    |     4735 |        2 |      1 | 2020-06-04 11:34:17 |
| 19 | ture    |     2505 |        0 |      1 | 2020-06-04 11:34:17 |
| 20 | ture    |     2523 |        2 |      1 | 2020-06-04 11:34:17 |
+----+---------+----------+----------+--------+---------------------+
20 rows in set (0.00 sec)
root@localhost#mysql.sock : tc0110:59:11>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 536871032 |
| mysql-bin.000002 | 536871341 |
| mysql-bin.000003 | 197211765 |
+------------------+-----------+
3 rows in set (0.00 sec)

挖取binlog日志

通過mysqlbinlog命令來查看修改的內容。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS  mysql-bin.000003 | grep -B 15 'ture'| more

內容如下

# at 197210475
#200610 10:59:05 server id 1023306  end_log_pos 197210536 CRC32 0xee919b04      Rows_query
# update update_test set user_id='ture'
# at 197210536
#200610 10:59:05 server id 1023306  end_log_pos 197210598 CRC32 0xeb431251      Table_map: `tc01`.`update_test` mApped to number 120
# at 197210598
#200610 10:59:05 server id 1023306  end_log_pos 197211734 CRC32 0x5f211a8d      Update_rows: table id 120 flags: STMT_END_F

### UPDATE `tc01`.`update_test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddddddddd' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=4502 /* INT meta=0 nullable=0 is_null=0 */
###   @4=2 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ture' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=4502 /* INT meta=0 nullable=0 is_null=0 */
###   @4=2 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### UPDATE `tc01`.`update_test`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddddddddd' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=5564 /* INT meta=0 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ture' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=5564 /* INT meta=0 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
..........................

找出位置后，把binlog的記錄導出來。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS  mysql-bin.000003 | sed -n '/# at 197210598/,/COMMIT/p' > ./update_test.txt

這些是誤操作之前的數據

###   @1=19 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddddddddd' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=2505 /* INT meta=0 nullable=0 is_null=0 */
###   @4=0 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

這些是誤操作之后的數據

###   @1=19 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ture' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=2505 /* INT meta=0 nullable=0 is_null=0 */
###   @4=0 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

替換成需要的SQL語句

下一步轉換成需要的SQL語句：

sed '/WHERE/{:a;N;/SET/!ba;s/([^n]*)n(.*)n(.*)/3n2n1/}' update_test.txt|sed -r '/WHERE/{:a;N;/@6/!ba;s/###   @2.*//g}'|sed 's/### //g;s//*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g'|sed '/^$/d' > ./update_test_recover.sql

SQL內容如下：

[mysql@tc02 binlog]$ cat update_test_recover.sql
UPDATE `tc01`.`update_test`
SET
  @1=1 ,
  @2='ddddddddddd' ,
  @3=4502 ,
  @4=2 ,
  @5=1 ,
  @6='2020-06-04 11:34:17' ,
WHERE
  @1=1 ;
UPDATE `tc01`.`update_test`
SET
  @1=2 ,
  @2='ddddddddddd' ,
  @3=5564 ,
  @4=1 ,
  @5=1 ,
  @6='2020-06-04 11:34:17' ,
WHERE
  @1=2 ;
UPDATE `tc01`.`update_test`
SET
  @1=3 ,
  @2='ddddddddddd' ,
  @3=3521 ,
  @4=2 ,
  @5=1 ,
  @6='2020-06-04 11:34:17' ,
WHERE
  @1=3 ;
.........................
.........................

需要進一步替換@1，@2，@3…@6.

CREATE TABLE `update_test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間' ,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

根據這個表結構替換：

sed -i 's/@1/id/g;s/@2/user_id/g;s/@3/vote_num/g;s/@4/group_id/g;s/@5/status/g;s/@6/create_time/g' update_test_recover.sql

修改后的內容如下：

UPDATE `tc01`.`update_test`
SET
  id=1 ,
  user_id='ddddddddddd' ,
  vote_num=4502 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17' ,
WHERE
  id=1 ;
UPDATE `tc01`.`update_test`
SET
  id=2 ,
  user_id='ddddddddddd' ,
  vote_num=5564 ,
  group_id=1 ,
  status=1 ,
  create_time='2020-06-04 11:34:17' ,
WHERE
  id=2 ;
UPDATE `tc01`.`update_test`
SET
  id=3 ,
  user_id='ddddddddddd' ,
  vote_num=3521 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17' ,
WHERE
  id=3 ;
  ....................................
  .....................................

生成恢復用的SQL語句

還有一個問題,就是需要把create_time后面的逗號去掉。使用下面的命令：

sed -i -r 's/(create_time=.*),/1/g' update_test_recover.sql

查看內容如下：

UPDATE `tc01`.`update_test`
SET
  id=1 ,
  user_id='ddddddddddd' ,
  vote_num=4502 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=1 ;
UPDATE `tc01`.`update_test`
SET
  id=2 ,
  user_id='ddddddddddd' ,
  vote_num=5564 ,
  group_id=1 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=2 ;
UPDATE `tc01`.`update_test`
SET
  id=3 ,
  user_id='ddddddddddd' ,
  vote_num=3521 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=3 ;
UPDATE `tc01`.`update_test`
SET
  id=4 ,
  user_id='ddddddddddd' ,
  vote_num=1414 ,
  group_id=0 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=4 ;
  ............................
  ..............................

滿足了條件之后，我們執行語句：

root@localhost#mysql.sock : tc0111:33:29>source update_test_recover.sql

root@localhost#mysql.sock : tc0111:34:14>select * from `tc01`.`update_test`;
+----+-------------+----------+----------+--------+---------------------+
| id | user_id     | vote_num | group_id | status | create_time         |
+----+-------------+----------+----------+--------+---------------------+
|  1 | ddddddddddd |     4502 |        2 |      1 | 2020-06-04 11:34:17 |
|  2 | ddddddddddd |     5564 |        1 |      1 | 2020-06-04 11:34:17 |
|  3 | ddddddddddd |     3521 |        2 |      1 | 2020-06-04 11:34:17 |
|  4 | ddddddddddd |     1414 |        0 |      1 | 2020-06-04 11:34:17 |
|  5 | ddddddddddd |     8047 |        1 |      1 | 2020-06-04 11:34:17 |
|  6 | ddddddddddd |     5556 |        1 |      1 | 2020-06-04 11:34:17 |
|  7 | ddddddddddd |     7166 |        1 |      2 | 2020-06-04 11:34:17 |
|  8 | ddddddddddd |     3277 |        2 |      2 | 2020-06-04 11:34:17 |
|  9 | ddddddddddd |     8658 |        2 |      1 | 2020-06-04 11:34:17 |
| 10 | ddddddddddd |     4146 |        0 |      2 | 2020-06-04 11:34:17 |
| 11 | ddddddddddd |     7906 |        2 |      1 | 2020-06-04 11:34:17 |
| 12 | ddddddddddd |      512 |        0 |      2 | 2020-06-04 11:34:17 |
| 13 | ddddddddddd |     7493 |        0 |      1 | 2020-06-04 11:34:17 |
| 14 | ddddddddddd |     5583 |        1 |      1 | 2020-06-04 11:34:17 |
| 15 | ddddddddddd |     4273 |        2 |      1 | 2020-06-04 11:34:17 |
| 16 | ddddddddddd |     1117 |        0 |      1 | 2020-06-04 11:34:17 |
| 17 | ddddddddddd |     3936 |        2 |      1 | 2020-06-04 11:34:17 |
| 18 | ddddddddddd |     4735 |        2 |      1 | 2020-06-04 11:34:17 |
| 19 | ddddddddddd |     2505 |        0 |      1 | 2020-06-04 11:34:17 |
| 20 | ddddddddddd |     2523 |        2 |      1 | 2020-06-04 11:34:17 |
+----+-------------+----------+----------+--------+---------------------+
20 rows in set (0.00 sec)

恢復到了修改之前的記錄，本次恢復測試完成。