如何利用MySQL的binlog恢复误删数据库详解
16lz
2021-12-09
1 查看当前数据库内容并备份数据库
查看数据库信息：
备份数据库：
[root@localhost ~]# mysqldump -u root -p t > /mnt/t.sqlEnter password: [root@localhost ~]# ll /mnt/t.sql -rw-r--r-- 1 root root 1771 Aug 25 11:56 /mnt/t.sql
mysql> show variables like "%log_bin%";
3 模拟误操作（插入3条数据，删除数据库）
mysql> insert into t1 values (3);Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values (4);Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values (5);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------+| id |+------+| 1 || 2 || 5 || 4 || 3 |+------+5 rows in set (0.00 sec)mysql> flush logs;Query OK, 0 rows affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql_bin.000003 | 106 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)
mysql> truncate t1;Query OK, 0 rows affected (0.00 sec)mysql> select * from t1;Empty set (0.00 sec)
mysql> drop table t1;Query OK, 0 rows affected (0.00 sec)mysql> show tables;Empty set (0.00 sec)
mysql> source /mnt/t.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)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)Query OK, 0 rows affected (0.00 sec)Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0Query 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)mysql> show tables;+-------------+| Tables_in_t |+-------------+| t1 |+-------------+1 row in set (0.00 sec)mysql> select * from t1;+------+| id |+------+| 1 || 2 |+------+2 rows in set (0.00 sec)
[root@localhost ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000002 | mysql -u root -p123.com t
mysqldump -uroot -p123456 test -l -F '/tmp/test.sql'-l:读锁（只能读取，不能更新）-F:即flush logs，可以重新生成新的日志文件，当然包括log-bin日志
mysql>show master status;
mysql -uroot -p t -v -f </mnt/t.sql-v查看导入的详细信息-f是当中间遇到错误时，可以skip过去，继续执行下面的语句
mysqlbinlog --no-defaults binlog-file | mysql -uroot -p t
mysqlbinlog --no-defaults --stop-position="367" mysql-bin.000001| mysql -uroot -p t
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | more
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p t
mysql> reset master;Query OK, 0 rows affected (0.01 sec)mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 106 | | |+------------------+----------+--------------+------------------+
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Android系统配置数据库注释(settings.db)
- Android(安卓)- Manifest 文件 详解
- Android的Handler机制详解3_Looper.looper()不会卡死主线程
- Selector、shape详解（一）
- android2.2资源文件详解4--menu文件夹下的菜单定义
- Android(安卓)中数据库查询方法 query() 中的 select
- [置顶] android orm映射框架（类似hibernate）基本使用
- Android发送短信方法实例详解