简单误操作恢复

MySQL误操作后的恢复

场景：
1、数据库每天都有全备份。
2、数据库开启bin-log
3、准确定位误操作语句

一、 创建全备份，建议带有 --master-data=2参数
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

二、正常使用数据库

[root@localhost backup]# mysql -uroot -ppassword123 -S /data/mysqldata/3306/mysql.sockWarning: 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 43Server version: 5.6.41-log Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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> 

三、发生误操作，单其他操作还在进行

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)

四、发现误操作后，及时锁库，尽快修复

mysql> mysql> mysql> mysql> flush tables with read lock;Query OK, 0 rows affected (0.46 sec)mysql> mysql> mysql> exitBye[root@localhost backup]# ls -ralttotal 35940drwxrwxr-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.sqldrwxrwxr-x. 2 mysql mysql       73 Sep 15 17:27 mysql_fulldrwxrwxr-x. 3 mysql mysql       50 Sep 15 18:45 mysql_full_by_dbsdrwxrwxr-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.sqldrwxr-xr-x. 8 mysql mysql       83 Jan 30 13:54 ..-rw-r--r--  1 root  root    658544 Jan 30 16:50 rep.sqldrwxr-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;

五，定位误操作，并找到位置点，对binlog做拆分操作

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

vi bin.sql
找到误操作语句，并删除它

六、全备份恢复+binlog曾量恢复

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