Shulou(Shulou.com)06/01 Report--

Recovery after misoperation of MySQL

Scene:

1. The database is fully backed up every day.

2. Enable bin-log in the database

3. Accurately locate misoperation statements

1. Create a full backup. It is recommended to have-- 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

Second, normal use of the database

[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 > 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 > select count (*) from t_idb_big +-+ | count (*) | +-+ | 30161 | +-+ 1 row in set (sec) mysql >

Third, misoperation occurred, but other operations are still in progress.

Mysql > mysql > delete from test6;Query OK, 30161 rows affected (1.14 sec) 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 finding misoperation, lock the library in time and repair it as soon as possible.

Mysql > flush tables with read lock;Query OK, 0 rows affected (0.46 sec) mysql > exitBye [root@localhost backup] # ls-ralttotal 35940drwxrwxr-x. 2 mysql mysql 6 Sep 15 16:14 3306 RW Murray RW Murray. 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 test.sql.2019 Jan 27 15:42 test.sql.2019 5568897 Jan 27 15:43 test.sql.2019-01-27 Mustang Ruki-1 mysql mysql 180873 Jan 27 15:54 mysql. 2019-01-27.sql.gzql.gzkashi-1 mysql mysql 422535 Jan 27 15:54 test.2019-01-27.sql.gzqql.gzkashi rwql.gzkashi-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 mysql mysql 59 test.2019 24.sql.gzmurr RW Murray-1 test.2019 5568942 Jan 27 15:59 sql-01-sql. 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-31mm 18 purl 51purl 43.full.sqlmuri-CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=120

Fifth, 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

Find the misoperation statement and delete it

6. Full backup recovery + binlog quantity recovery

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.