Mysql误删表中数据与误删表的恢复方法

数据库误删某表某数据恢复方法，这个前提是针对每天有备份的数据库和开启binlog日志的

/查看数据库是否开启binlog日志/

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /home/mysql/mysql/lib/mysql-bin       |
| log_bin_index                   | /home/mysql/mysql/lib/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

以上为开启状态

开启binlog日志的方法
需要在/etc/my.cnf中【mysqld】下添加开启binlog的配置

log-bin=mysql-bin
#设置日志格式
binlog-format=ROW
#配置serverid
server_id=1

添加完成后重启MySQL
service mysqld restart

/首先查看一下数据表中的数据/

mysql> select * from a;
+----+-----------+------------+
| id | name      | age        |
+----+-----------+------------+
|  1 | zhangsan     |         23 |
|  2 | lia         |          26 |
+----+-----------+------------+
2 rows in set (0.00 sec)

备份数据库

mysqldump -uroot -p -B text >text.sql

/再插入数据后删除数据库/

mysql> insert into a (name,age)values("lisi",45);
Query OK, 1 row affected (0.02 sec)

mysql> insert into a (name,age)values("wangwu",43);
Query OK, 1 row affected (0.01 sec)

删除数据库

mysql> drop database text;
Query OK, 1 row affected (0.02 sec)

查看当前的binlog

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 835
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

/这个时候要将当前的binlog日志拷贝到其他目录，以免后续操作对binlog日志产生影响/

cp /var/lib/mysql/mysql-bin.000001 /home

/执行命令/ 转换binlog日志为sql,要进入/home目录下，不然会报错

mysqlbinlog: File ‘mysql-bin.000001’ not found (Errcode: 2 - No such
file or directory)

mysqlbinlog -d text mysql-bin.000001 >001bin.sql

进入/home执行报这个，没理会

WARNING: The option --database has been used. It may filter parts of
transactions, but will include the GTIDs in any case. If you want to
exclude or include transactions, you should use the options
–exclude-gtids or --include-gtids, respectively, instead.

编辑001bin.sql将里面的误操作命令(DROP命令)全部删除

保存后开始进行恢复数据

/首先恢复数据库和表/前提是得线创建一个text数据库

mysql -uroot -p text < text.sql 

备份的数据已经恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_text |
+----------------+
| a              |
+----------------+
1 row in set (0.00 sec)

接下来恢复备份之后被删除的数据

mysql -uroot -p text < 001bin.sql
遇到报错问题

[root@master home]# mysql -uroot -p text < 001bin.sql Enter password:
ERROR 1790 (HY000) at line 74: @@SESSION.GTID_NEXT cannot be changed
by a client that owns a GTID. The client owns ANONYMOUS. Ownership is
released on COMMIT or ROLLBACK.

编辑001bin.sql文件,将报错信息中提示的74行（包括）以下文件全部删除

然后重新导入
/恢复后查看数据/

mysql> select * from a;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | as       |   12 |
|    2 | zhangsan |   23 |
| 3| lisi     |   45 |
| 4 | wangwu   |   43 |
+------+----------+------+
4 rows in set (0.00 sec)

以上就是数据库表被误删或数据被误删的恢复方法！