不小心删除数据--MySQL数据恢复

一、windows系统搭建的数据库：

第一步：cmd 登陆mysql

mysql -uroot -p

第二步：选择一个数据库lxq

mysql> use  lxq;

第三步：创建一个表：sn_test

mysql> CREATE TABLE  `lxq`.`sn_test` ( `name` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL , `age` INT( 3 ) NOT NULL ) ENGINE = MYISAM;

第四步：插入表：sn_test 数据

INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq1', '1');
INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq2', '2'); 
INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq3', '3'); 
INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq4', '4');

第五步：查看数据并删除

mysql> select * from sn_test;
mysql> delete from sn_test;
mysql> select * from sn_test;

第六步：查看自己的操作被记录在了那个binlog文件中（一般存放在标号最大的ID下）

1.查询所有日志 Position 值；

mysql> show master logs;   
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       790 |
| mysql-bin.000002 |       126 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       126 |
| mysql-bin.000757 |       126 |
| mysql-bin.000758 |       126 |
| mysql-bin.000759 |       150 |
| mysql-bin.000760 |      2073 |
| mysql-bin.000761 |       107 |
+------------------+-----------+

2.查询最近一次也就是当前日志的 Position 值；（这步操作适用于刚刚删除数据 ）

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000761 |     1151 |              |                  |
+------------------+----------+--------------+------------------+

第七步：拿最新日志文件做案例，查看 mysql-bin.000761 这个日志文件中记录了那些操作执行：

mysql> show binlog events in 'mysql-bin.000761';

| mysql-bin.000761 |  390 | Query       |         1 |         593 | use `lxq`; CREATE TABLE  `lxq`.`sn_test` ( `name` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8
_bin NOT NULL , `age` INT( 3 ) NOT NULL ) ENGINE = MYISAM |
| mysql-bin.000761 |  593 | Query       |         1 |         660 | BEGIN
                                                          |
| mysql-bin.000761 |  660 | Query       |         1 |         786 | use `lxq`; INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq1', '1')
                                                          |
| mysql-bin.000761 |  786 | Query       |         1 |         854 | COMMIT
                                                          |
| mysql-bin.000761 |  854 | Query       |         1 |         921 | BEGIN
                                                          |
| mysql-bin.000761 |  921 | Query       |         1 |        1047 | use `lxq`; INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq2', '2')
                                                          |
| mysql-bin.000761 | 1047 | Query       |         1 |        1115 | COMMIT
                                                          |
| mysql-bin.000761 | 1115 | Query       |         1 |        1182 | BEGIN
                                                          |
| mysql-bin.000761 | 1182 | Query       |         1 |        1308 | use `lxq`; INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq3', '3')
                                                          |
| mysql-bin.000761 | 1308 | Query       |         1 |        1376 | COMMIT
                                                          |
| mysql-bin.000761 | 1376 | Query       |         1 |        1443 | BEGIN
                                                          |
| mysql-bin.000761 | 1443 | Query       |         1 |        1569 | use `lxq`; INSERT INTO `lxq`.`sn_test` (`name`, `age`) VALUES ('lxq4', '4')
                                                          |
 mysql-bin.000761 | 1704 | Query       |         1 |        1785 | use `lxq`; delete from sn_test
                                                          

第八步： 从上面查询结果中找到被删除的表在创建时的sql 语句的 position值 593 和 删除数据时候的sql语句前一个 position 值 1569 。下面开始最重要的一步使用 mysqlbinlog 进行恢复数据操作, 需要在路径 D:\BtSoft\WebSoft\mysql\MySQL5.5\data\ 下执行：

D:\BtSoft\WebSoft\mysql\MySQL5.5\data\ >  mysqlbinlog mysql-bin.000761 --start-position 593 --stop-position 1569 |mysql -u root -p

第九步：输入mysql数据库密码：

****

第十步：重新登录，查看数据，OK，已经成功恢复了。

mysql> select * from sn_test;
+------+-----+
| name | age |
+------+-----+
| lxq1 |   1 |
| lxq2 |   2 |
| lxq3 |   3 |
| lxq4 |   4 |
+------+-----+

注意：
对于数据库操作，应该注意如下问题：
1、要常备份（全备，增量备份），出了问题可以最快恢复数据；
2、操作数据库前，要把需要操作的数据库或者表dump出来；
3、需要把bin-log打开，就算没有做上面的两步，也可以通过日志恢复数据（不小心删除恢复操作）；

二、windows利用mysqldump 备份所建立数据库 'lxq' 的所有表

1.备份。把数据库lxq备份到windows 系统路径：D:\下，命名为：test.sql

C:\ User\Administrator>   mysqldump -u root -p lxq >"D:\test.sql"

2.删除数据库lxq

mysql>  drop databse lxq;

3.恢复。先在数据库创建库名lxq ,然后在路劲：D:\ cmd 下恢复数据库数据：

D:\>mysql -uroot -p lxq < test.sql

三、Linux系统数据库恢复：

1.备份

[root@mysql /]  mysqldump -u root -p lxq > / test.sql

2.删除数据库lxq

mysql>  drop database lxq;

3.创建数据库lxq

mysql>  create database lxq;

4.恢复

/ mysql -uroot -p lxq < / test.sql

四 、mysql查询表创建的时间

#进入information_schema

mysql>   use information_schema;

#查询表 sn_test 的信息

mysql>  SELECT *  FROM `information_schema`.`TABLES` where TABLE_NAME='sn_test'\G

*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: lxq
     TABLE_NAME: sn_test
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4
 AVG_ROW_LENGTH: 20
    DATA_LENGTH: 80
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2021-04-13 20:27:45
    UPDATE_TIME: 2021-04-14 20:06:16
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:

本文参考链接：
https://blog.csdn.net/weixin_41513917/article/details/89207475?utm_source=app
https://blog.csdn.net/jh1141233305/article/details/105198911?utm_source=app
http://blog.chinaunix.net/uid-26602509-id-4102099.html
https://blog.csdn.net/weixin_39214481/article/details/90690659
https://blog.csdn.net/weixin_44090237/article/details/115707225?spm=1001.2014.3001.5501