十年网站开发经验 + 多家企业客户 + 靠谱的建站团队
量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决
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.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>
三、发生误操作,单其他操作还在进行
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> 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;
五,定位误操作,并找到位置点,对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