mysql backup table_MySQL 常见的几种恢复------table的删除（有备份情况）

1.误删除表的恢复

测试：mysqldump -uroot -p123 -A -B --events --master-data=2 --single-transaction >/tmp/sql全备

一。删除表

mysql> show tables;

+---------------+

| Tables_in_liu |

+---------------+

| t1 |

| t2 |

| t3 |

| t4 |

+---------------+

mysql> desc t2;

+-----------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+----------------+

| test_code | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | YES | | NULL | |

+-----------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

mysql> insert into t2 (name) values('aa');

Query OK, 1 row affected (5.06 sec)

mysql> drop table t2;

Query OK, 0 rows affected (0.06 sec)

mysql> select * from t2;

ERROR 1146 (42S02): Table 'liu.t2' doesn't exist

mysql>

mysql> use test;

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| a |

| li |

| user |

| xiaobai |

| xiaohe |

+----------------+

5 rows in set (0.00 sec)

mysql> select * from a;

+----+------+

| a | b |

+----+------+

| 1 | 1 |

| 3 | 1 |

| 5 | 3 |

| 7 | 6 |

| 10 | 8 |

+----+------+

5 rows in set (0.00 sec)

mysql> drop table a;

Query OK, 0 rows affected (0.10 sec)

mysql> select * from a;

ERROR 1146 (42S02): Table 'test.a' doesn't exist

+----+------+

| id | name |

+----+------+

| 2 | b |

+----+------+

1 row in set (0.00 sec)

mysql> insert into li(id,name) values(3,'c');

Query OK, 1 row affected (0.10 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> drop table li;

Query OK, 0 rows affected (0.05 sec)

二，找binglog

mysqlbinlog --start-datetime='2020-07-08 19:00:00'  --stop-datetime='2020-07-08 20:00:00' -d liu mysql-bin.000001

mysqlbinlog  mysql-bin.000001 |grep -5a "DROP TABLE"

三。

执行drop 之前pos

四恢复数据库

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| a |

| li |

| t4 |

| user |

| xiaobai |

| xiaohe |

+----------------+

6 rows in set (0.00 sec)

mysql> select * from li;

+----+------+

| id | name |

+----+------+

| 2 | b |

+----+------+

1 row in set (0.00 sec)

5.恢复drop之前的修改

mysql> source /tmp/li.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from li;

+----+------+

| id | name |

+----+------+

| 2 | b |

| 3 | c |

+----+------+

2 rows in set (0.00 sec)

mysql>