Recovery after MySQL misoperation
Scene:
1. The database is backed up every day.
2. Enable bin log of database
3. Accurately locate misoperation statements
1、 To create a full backup, it is recommended to use the -- master data=2 parameter
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
2、 Use the database normally
[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 43Server 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>
3、 Misoperation occurs, and other operations are still in progress
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)
4、 After misoperation is found, lock the warehouse in time and repair it as soon as possible
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;
5、 Locate the misoperation, find the location point, and split the binlog
mysqlbinlog mysql-bin.000027 -d test --start_position=120 -r bin.sql
vi bin.sql
Locate the misoperation statement and delete it
6、 Full backup recovery+binlog recovery
[root@localhost backup]# mysql -uroot -ppassword123 -S /data/mysqldata/3306/mysql.sock <test.2019-01-31_18:51:43.full.sqlWarning: Using a password on the command line interface can be insecure. [root@localhost backup]# [root@localhost backup]# [root@localhost backup]# mysql -uroot -ppassword123 -S /data/mysqldata/3306/mysql.sock < /data/mysqldata/3306/binlog/bin.sql
Copyright Description:No reproduction without permission。
Knowledge sharing community for developers。
Let more developers benefit from it。
Help developers share knowledge through the Internet。
Follow us
2022-12-06