MySQL誤操作后的恢復(fù)

為蕪湖縣等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及蕪湖縣網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為網(wǎng)站制作、成都網(wǎng)站制作、蕪湖縣網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！

場(chǎng)景：
1、數(shù)據(jù)庫(kù)每天都有全備份。
2、數(shù)據(jù)庫(kù)開(kāi)啟bin-log
3、準(zhǔn)確定位誤操作語(yǔ)句

一、 創(chuàng)建全備份，建議帶有 --master-data=2參數(shù)
mysqldump -uroot -ppassword123 -S /data/mysqldata/3306/mysql.sock -F -R --triggers --lock-tables --master-data=2 -B test > /data/mysqldata/backup/test.$(date "+%F_%H:%M:%S").full.sql

二、正常使用數(shù)據(jù)庫(kù)

[root@localhost backup]# mysql -uroot -ppassword123 -S /data/mysqldata/3306/mysql.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.6.41-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> use test6;
ERROR 1049 (42000): Unknown database 'test6'
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_idb_big      |
| test1          |
| test2          |
| test3          |
| test4          |
| test5          |
| test6          |
+----------------+
7 rows in set (0.00 sec)

mysql> select count from test6;
ERROR 1054 (42S22): Unknown column 'count' in 'field list'
mysql> select count(*) from test6;
+----------+
| count(*) |
+----------+
|    30161 |
+----------+
1 row in set (0.02 sec)

mysql> 
mysql> 
mysql> select count(*) from t_idb_big;
+----------+
| count(*) |
+----------+
|    30161 |
+----------+
1 row in set (0.01 sec)

mysql> 

三、發(fā)生誤操作，單其他操作還在進(jìn)行

mysql> 
mysql> delete from test6;
Query OK, 30161 rows affected (1.14 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> desc test5;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(20)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from test5;
+----+------+
| id | name |
+----+------+
|  3 | a    |
|  4 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into test5 (name) values ('c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test5 (name) values ('d');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test5;
+----+------+
| id | name |
+----+------+
|  3 | a    |
|  4 | b    |
|  5 | c    |
|  6 | d    |
+----+------+
4 rows in set (0.00 sec)

四、發(fā)現(xiàn)誤操作后，及時(shí)鎖庫(kù)，盡快修復(fù)

mysql> 
mysql> 
mysql> 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.46 sec)

mysql> 
mysql> 
mysql> exit
Bye
[root@localhost backup]# ls -ralt
total 35940
drwxrwxr-x. 2 mysql mysql        6 Sep 15 16:14 3306
-rw-rw-r--. 1 mysql mysql  5568942 Sep 15 16:56 test_3306_2018-09-15.sql
drwxrwxr-x. 2 mysql mysql       73 Sep 15 17:27 mysql_full
drwxrwxr-x. 3 mysql mysql       50 Sep 15 18:45 mysql_full_by_dbs
drwxrwxr-x. 3 mysql mysql       18 Sep 15 19:25 mysql_full_by_tbs
-rw-r--r--  1 mysql mysql  6227100 Jan 27 15:24 all.sql
-rw-r--r--  1 mysql mysql   659215 Jan 27 15:42 mysql.sql.2019-01-27
-rw-r--r--  1 mysql mysql  5568897 Jan 27 15:43 test.sql.2019-01-27
-rw-r--r--  1 mysql mysql   180873 Jan 27 15:54 mysql.2019-01-27.sql.gz
-rw-r--r--  1 mysql mysql   422535 Jan 27 15:54 test.2019-01-27.sql.gz
-rw-r--r--  1 mysql mysql   180873 Jan 27 15:59 mysql..sql.gz
-rw-r--r--  1 mysql mysql   422535 Jan 27 15:59 test..sql.gz
-rw-r--r--  1 mysql mysql   180873 Jan 27 15:59 mysql.2019-01-27_15:59:24.sql.gz
-rw-r--r--  1 mysql mysql  5568942 Jan 27 15:59 test.2019-01-27_15:59:25.sql
drwxr-xr-x. 8 mysql mysql       83 Jan 30 13:54 ..
-rw-r--r--  1 root  root    658544 Jan 30 16:50 rep.sql
drwxr-xr-x. 6 mysql mysql     4096 Jan 31 18:51 .
-rw-r--r--  1 root  root  11131595 Jan 31 18:51 test.2019-01-31_18:51:43.full.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=120;

五，定位誤操作，并找到位置點(diǎn)，對(duì)binlog做拆分操作

mysqlbinlog mysql-bin.000027 -d test --start_position=120 -r bin.sql

vi bin.sql
找到誤操作語(yǔ)句，并刪除它

六、全備份恢復(fù)+binlog曾量恢復(fù)

[root@localhost backup]# mysql -uroot -ppassword123 -S /data/mysqldata/3306/mysql.sock