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

數據恢復

上一篇文章MySQL基于binlog實現數據增量恢復實踐 我們大概講解了下當數據表發生全表更新后，如何使用冷備份數據和基于MySQL的binlog實現增量式恢復數據，這種增量恢復數據可能存在一些弊端，效率可能也是不是太高，主要存在如下缺點：

• 基于備份的數據進行恢復，如果對數據備份不及時，可能達不到理想的效果；
• 可能會需要停止線上業務進行數據恢復，因為對表有drop操作；
• 如果全表更新發現不及時，恢復數據可能需要更長的時間；
• 如果有多個binlog文件，需要對每一個binlog進行恢復；
• 恢復數據效率不高；

通過查閱資料和閱讀mysql官方文檔，還有一種應該算比較高效和可靠的方式來恢復全表更新后的數據。這種方法同樣還是基于mysql的binlog和sed命令可以提取出當時執行全表更新的sql，然后對update的sql語句進行逆向操作，將更新后的數據再更新回之前的數據。

下面我們就通過一個例子來研究下，這種方法如何到達快速恢復數據的目的。同時把恢復數據的過程記錄下來，方便以后遇到類似問題可以查閱參考，快速解決問題，提高工作效率。

恢復數據

我們還是基于上一篇文章MySQL基于binlog實現數據增量恢復實踐 的環境，創建一個user表：

mysql> select * from user;
+----+-----------+------+-----------+
| id | name      | age  | address   |
+----+-----------+------+-----------+
|  1 | test      |    1 | test      |
|  2 | zhangsan  |    2 | address1  |
|  3 | lisi      |    3 | addr1     |
|  4 | test1     |    4 | addr2     |
|  5 | test2     |    5 | addr3     |
|  6 | test3     |    6 | address4  |
|  7 | b1        |    7 | bb        |
|  8 | a1        |   10 | add1      |
|  9 | a2        |   11 | add2      |
| 10 | a3        |   12 | add3      |
| 11 | a4        |   13 | add4      |
| 12 | a5        |   14 | add5      |
+----+-----------+------+-----------+
12 rows in set (0.00 sec)

執行mysql命令重新生成一個binlog文件：

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

mysql> show binlog events in 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000003 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000003 | 125 | Previous_gtids |         1 |         156 |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)

新插入一條數據：

mysql> INSERT INTO `demo`.`user`(`name`,`age`,`address`) VALUES ('c1',10,'c1');
Query OK, 1 row affected (0.03 sec)

mysql> show binlog events in 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000003 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4    |
| binlog.000003 | 125 | Previous_gtids |         1 |         156 |                                      |
| binlog.000003 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 235 | Query          |         1 |         310 | BEGIN                                |
| binlog.000003 | 310 | Table_map      |         1 |         370 | table_id: 101 (demo.user)            |
| binlog.000003 | 370 | Write_rows     |         1 |         422 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 | 422 | Xid            |         1 |         453 | COMMIT /* xid=174 */                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.00 sec)

下面我們模擬一個誤操作全表更新，全表更新后再插入一條數據：

mysql> update user set age=100;
Query OK, 13 rows affected (0.03 sec)
Rows matched: 13  Changed: 13  Warnings: 0

mysql> INSERT INTO `demo`.`user`(`name`,`age`,`address`) VALUES ('d1',20,'d1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+----------+-----+----------+
| id | name     | age | address  |
+----+----------+-----+----------+
|  1 | test     | 100 | test     |
|  2 | zhangsan | 100 | address1 |
|  3 | lisi     | 100 | addr1    |
|  4 | test1    | 100 | addr2    |
|  5 | test2    | 100 | addr3    |
|  6 | test3    | 100 | address4 |
|  7 | b1       | 100 | bb       |
|  8 | a1       | 100 | add1     |
|  9 | a2       | 100 | add2     |
| 10 | a3       | 100 | add3     |
| 11 | a4       | 100 | add4     |
| 12 | a5       | 100 | add5     |
| 18 | c1       | 100 | c1       |
| 19 | d1       |  20 | d1       |
+----+----------+-----+----------+
14 rows in set (0.00 sec)

查看mysql的binlog發生的變化：

mysql> show binlog events in 'binlog.000003';
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| binlog.000003 |    4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4    |
| binlog.000003 |  125 | Previous_gtids |         1 |         156 |                                      |
| binlog.000003 |  156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 |  235 | Query          |         1 |         310 | BEGIN                                |
| binlog.000003 |  310 | Table_map      |         1 |         370 | table_id: 101 (demo.user)            |
| binlog.000003 |  370 | Write_rows     |         1 |         422 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 |  422 | Xid            |         1 |         453 | COMMIT /* xid=174 */                 |
| binlog.000003 |  453 | Anonymous_Gtid |         1 |         532 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 |  532 | Query          |         1 |         616 | BEGIN                                |
| binlog.000003 |  616 | Table_map      |         1 |         676 | table_id: 101 (demo.user)            |
| binlog.000003 |  676 | Update_rows    |         1 |        1490 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 | 1490 | Xid            |         1 |        1521 | COMMIT /* xid=176 */                 |
| binlog.000003 | 1521 | Anonymous_Gtid |         1 |        1600 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 1600 | Query          |         1 |        1675 | BEGIN                                |
| binlog.000003 | 1675 | Table_map      |         1 |        1735 | table_id: 101 (demo.user)            |
| binlog.000003 | 1735 | Write_rows     |         1 |        1787 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 | 1787 | Xid            |         1 |        1818 | COMMIT /* xid=178 */                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
17 rows in set (0.00 sec)

一個基本的誤操作全表更新已經完成，它還是比較符合我們在實際工作中的場景，當發生數據誤操作后，可能還有其他業務在誤操作之后對數據進行了修改，我們在這里模擬一種全表更新后，還插入了一條新數據，接下來我們的預期目的是將全表更新的數據恢復到更新前的狀態。

恢復數據

首先，備份mysql的binlog文件，重新生成一個binlog：

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

然后，從mysql的binlog中找出發生全表更新的事件點

mysql的binlog

從binlog中可以看到，發生全表更新的事件點發生在453，1521

mysqlbinlog  --base64-output=decode-rows -v -v --start-position=532 --stop-position=1521 /var/lib/mysql/binlog.000003 | grep -C 30 'UPDATE `demo`.`user`'

全表更新sql

記住# at 676這個關鍵字，然后使用sed命令從這里開始提取全表更新的sql語句：

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/lib/mysql/binlog.000003 | sed -n '/# at 676/,/COMMIT/p' > update.sql

使用sed命令對update sql進行替換：

sed '/WHERE/{:a;N;/SET/!ba;s/([^n]*)n(.*)n(.*)/3n2n1/}' update.sql | sed 's/### //g;s//*.*/,/g' | sed  /@4/s/,//g | sed '/WHERE/{:a;N;/@4/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > rollback.sql
 root@a157a03eb2a7:~# cat rollback.sql
UPDATE `demo`.`user`
SET
  @1=1 ,
  @2='test     ' ,
  @3=1 ,
  @4='test     ' ,
WHERE
  @1=1 ,
  @2='test     ' ,
  @3=100 ,
  @4='test     ' ,
UPDATE `demo`.`user`
SET
  @1=2 ,
  @2='zhangsan ' ,
  @3=2 ,
  @4='address1 ' ,
WHERE
  @1=2 ,
  @2='zhangsan ' ,
  @3=100 ,
  @4='address1 ' ,

可以看到進行替換后的sql語句變的基本可讀了，再次使用sed命令進行替換和優化：

root@a157a03eb2a7:~# sed  -i -r  '/WHERE/{:a;N;/@4/!ba;s/(@4=.*)/1;/g}' rollback.sql
root@a157a03eb2a7:~# cat rollback-1.sql
UPDATE `demo`.`user`
SET
  @1=1 ,
  @2='test     ' ,
  @3=1 ,
  @4='test     '
WHERE
  @1=1 AND
  @2='test     ' AND
  @3=100 AND
  @4='test     ' ;

這是為了給@4后面的“，”替換成“；”

root@a157a03eb2a7:~# sed -i 's/@1/id/g;s/@2/name/g;s/@3/age/g;s/@4/address/g' rollback.sql
root@a157a03eb2a7:~# cat rollback.sql
UPDATE `demo`.`user`
SET
  id=1 ,
  name='test     ' ,
  age=1 ,
  address='test     '
WHERE
  id=1 AND
  name='test     ' AND
  age=100 AND
  address='test     ' ;

使用這個sed將@1，@2，@3，@4替換成實際的字段名（表列名）。這個時候rollback.sql文件中的sql語句已經被優化成全表更新之前的update語句了，并且sql語句的可讀性更強了。

接下來我們需要將rollback.sql導入到mysql中，把發生全表更新的數據再次更新回之前的數據狀態

mysql> source /root/rollback.sql
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

恢復后的數據

通過上面截圖我們可以看到，當rollback.sql文件導入到user表后，被全表更新的數據已經恢復到更新前的數據，而且發生全表更新后，我們還插入了一條數據，恢復數據后對新插入的數據并沒有影響。

這種恢復數據的方法，相比上一篇MySQL基于binlog實現數據增量恢復實踐的方法有以下優勢：

• 恢復數據影響范圍更小，只恢復誤操作的sql語句；
• 恢復數據更安全，不會影響誤操作前的數據，也不會影響誤操作后的數據；
• 可能對線上的業務不會造成太多的影響；
• 這種恢復數據的方式不依賴備份數據，只依賴mysql的binlog；
• 這種恢復數據的方式相當于對全表更新后的一次逆向更新數據，完全使用sql的update語句；

這種全表更新的誤操作可以通過兩種方法進行數據恢復：

1. 備份數據+mysql的binlog事件；
2. mysql的binlog事件+update sql語句；

但是如果發生刪庫和刪表誤操作，可能第一種方法是最優的恢復數據的方式；如果是發生全表更新后第二種方法應該是最優的恢復數據的方式。

參考文檔：

https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html