mysql通过binlog来恢复被删除的数据库

binlog日志


查询:
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)


设置:
log_bin/data/mysql/mysql-bin
binlog_format=row


详细:
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log-bin=/data/mysql/mysql-bin
server-id=1
port=3306

log_bin=/data/mysql/mysql-bin
binlog_format=row
[client]
socket=/tmp/mysql.sock


设置完成后检查:
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |     <------ binlog日志开启
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |   <------ binlog日志开启 
+---------------+-------+
1 row in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |     97002 |
| mysql-bin.000004 |     97002 |
| mysql-bin.000005 |     97002 |
| mysql-bin.000006 |       143 |
| mysql-bin.000007 |       120 |
+------------------+-----------+
7 rows in set (0.01 sec)


binlog开启后,可以通过命令查看到哪些日志是正在使用的
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#这里的日志 只有一个是在被使用的


查看一个binlog日志详情:
mysql> show binlog events in 'mysql-bin.000004' limit 5;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000004 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 219 | Query          |         6 |         317 | CREATE DATABASE mysql;                |
| mysql-bin.000004 | 317 | Anonymous_Gtid |         6 |         382 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
5 rows in set (0.00 sec)
这是查看这个binlog日志的前五个事件。

记住: 在binlog最小的记录单元叫event,一个事务会被拆分为多个event。
event特性: 每个event都有一个开始位置[start-position]和一个结束位置[stop-position]

所谓的位置就是: event对于整个二进制文件的相对位置
在一个二进制日志中,前120个 position是文件格式信息的预留空间
也就是mysql第一个记录的时间,都是从120开始的

--row模式下的二进制日志分析案例:
1. 查看日志量
mysql> show master status;  
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2. 查看日志量
mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      220 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)   ## 说并已经记录了二进制日志了

再添加数据:
mysql> use binlog;
Database changed
mysql> create table bt(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      321 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

插入一个ddl内容:
mysql> insert into bt values(1);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      513 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[自动提交,如果没有变化可以使用 commit 提交一次]
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      705 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


主要命令:
mysql> show master status;
mysql> create database binlog;
mysql> show master status;
mysql> use binlog;


#更新数据
mysql> update bt set id=2 where id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     1862 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


此时感觉错了,想恢复到delete之前的操作发现不会,所以我果断直接删了:
mysql> select * from bt;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> drop table bt;
Query OK, 0 rows affected (0.00 sec)

此时通过binlog查看数据:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     1981 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

此时。。连库也直接删了:
mysql> drop database binlog;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| world              |
+--------------------+
5 rows in set (0.00 sec)
这时候,整个binlog库都被删除了。


此时来了个大神需要恢复数据:
此时需要用二进制日志进行恢复

恢复思路:
二进制日志是以时间进记录的.
1. 通过show master status; 查看到记录的日志文件。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     2070 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2. 查看这个日志:
mysql> show binlog events in 'mysql-bin.000007';
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                    |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------+
| mysql-bin.000007 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.38-log, Binlog ver: 4                   |
| mysql-bin.000007 |  120 | Query       |         1 |         220 | create database binlog                                  |
| mysql-bin.000007 |  220 | Query       |         1 |         321 | use `binlog`; create table bt(id int)                   |
| mysql-bin.000007 |  321 | Query       |         1 |         395 | BEGIN                                                   |
| mysql-bin.000007 |  395 | Table_map   |         1 |         442 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 |  442 | Write_rows  |         1 |         482 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 |  482 | Xid         |         1 |         513 | COMMIT /* xid=46 */                                     |
| mysql-bin.000007 |  513 | Query       |         1 |         587 | BEGIN                                                   |
| mysql-bin.000007 |  587 | Table_map   |         1 |         634 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 |  634 | Write_rows  |         1 |         674 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 |  674 | Xid         |         1 |         705 | COMMIT /* xid=48 */                                     |
| mysql-bin.000007 |  705 | Query       |         1 |         779 | BEGIN                                                   |
| mysql-bin.000007 |  779 | Table_map   |         1 |         826 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 |  826 | Write_rows  |         1 |         866 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 |  866 | Xid         |         1 |         897 | COMMIT /* xid=79 */                                     |
| mysql-bin.000007 |  897 | Query       |         1 |         971 | BEGIN                                                   |
| mysql-bin.000007 |  971 | Table_map   |         1 |        1018 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 | 1018 | Write_rows  |         1 |        1058 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 | 1058 | Xid         |         1 |        1089 | COMMIT /* xid=81 */                                     |
| mysql-bin.000007 | 1089 | Query       |         1 |        1163 | BEGIN                                                   |
| mysql-bin.000007 | 1163 | Table_map   |         1 |        1210 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 | 1210 | Write_rows  |         1 |        1250 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 | 1250 | Xid         |         1 |        1281 | COMMIT /* xid=87 */                                     |
| mysql-bin.000007 | 1281 | Query       |         1 |        1355 | BEGIN                                                   |
| mysql-bin.000007 | 1355 | Table_map   |         1 |        1402 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 | 1402 | Delete_rows |         1 |        1442 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 | 1442 | Xid         |         1 |        1473 | COMMIT /* xid=89 */                                     |
| mysql-bin.000007 | 1473 | Query       |         1 |        1547 | BEGIN                                                   |
| mysql-bin.000007 | 1547 | Table_map   |         1 |        1594 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 | 1594 | Delete_rows |         1 |        1634 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 | 1634 | Xid         |         1 |        1665 | COMMIT /* xid=91 */                                     |
| mysql-bin.000007 | 1665 | Query       |         1 |        1739 | BEGIN                                                   |
| mysql-bin.000007 | 1739 | Table_map   |         1 |        1786 | table_id: 70 (binlog.bt)                                |
| mysql-bin.000007 | 1786 | Delete_rows |         1 |        1831 | table_id: 70 flags: STMT_END_F                          |
| mysql-bin.000007 | 1831 | Xid         |         1 |        1862 | COMMIT /* xid=93 */                                     |
| mysql-bin.000007 | 1862 | Query       |         1 |        1981 | use `binlog`; DROP TABLE `bt` /* generated by server */ |
| mysql-bin.000007 | 1981 | Query       |         1 |        2070 | drop database binlog                                    |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------+
37 rows in set (0.00 sec)

发现binlog也记录了删除数据的操作。但是又很多看不懂,此时需要使用binlog的专门工具进行查看
查看:
mysqlbinlog --base64-output=decode-rows  -vvv /data/mysql/mysql-bin.000007


截取删除之前的日志:
mysqlbinlog --start-position=120 --stop-position=1402 /data/mysql/mysql-bin.000007 >bin.sql


在数据库中暂时关闭日志记录:
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.01 sec)


开始恢复数据:
mysql> source /root/bin.sql

验证数据:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| binlog             |
| mysql              |
| performance_schema |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)

已经恢复了 被删除的  binlog  数据库

mysql> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| bt               |
+------------------+
1 row in set (0.00 sec)
经过检查 也恢复了被删除的BT表。
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值