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

最新推荐文章于 2024-09-20 16:24:01 发布
最新推荐文章于 2024-09-20 16:24:01 发布
阅读量1.1k 收藏
点赞数
文章标签： mysql 数据库 java
原文链接：https://blog.51cto.com/90xpy/2722549
版权

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表。
 

小菜菜-K
关注
mysql--使用binlog 来恢复数据
ylx814056815的博客
08-22 259
mysql–使用binlog 来恢复数据 一、数据准备 CREATE TABLE `course2` ( `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '课程id', `name` varchar(10) DEFAULT NULL COMMENT '课程名称', `tid` bigint(0) DEFAULT NULL COMMENT '教师id', PRIMARY KEY (`id`) USING BTREE ) ENGINE = I
利用binlog进行数据库的还原
cltt980248285的博客
11-28 139
前言：在学习mysql备份的时候，深深的感受到mysql的备份还原功能没有oracle强大；比如一个很常见的恢复场景：基于时间点的恢复，oracle通过rman工具就能够很快的实现数据库的恢复，但是mysql在进行不完全恢复...
mysql通过binlog做数据恢复
最新发布
weixin_49702090的博客
09-20 1078
mysql通过binlog做数据恢复
MySQL通过Binlog恢复删除的表
ciqxcl15963093886的博客
08-14 86
查看log-bin是否开启： mysql> show variables like '%log%bin%'; +---------------------------------+-------+ | Variabl...
MYSQL之binlog恢复数据
yeYingXuan的博客
07-31 2505
可以看到有一条新增的sql和一条删除的sql，新增的时间点是 240720 8:06:13，结束的时间点是240720 8:06:13，只要我们恢复的时间段包含了这个时间段即可。对某一条执行了错误的更新/删除操作：这种恢复是最简单的，我们直接在binlog找到这条数据的操作记录，找到原数据的值，然后执行新增/更新操作即可。由上得知，我们的binlog文件名叫binlog.000012，位置在/var/lib/mysql目录下。既然他会记录sql，那我们是不是把需要恢复数据的sql拿出来就好了？
利用MySQL的binlog恢复误删的数据库
qq_45228323的博客
05-21 1071
备份数据mysqldump - uroot - p123456 test - l - F '/tmp/test.sql' - l:读锁（只能读取，不能更新） - F:即flush logs，可以重新生成新的日志文件，当然包括log - bin日志查看binlog日志导入之前备份数据mysql - uroot - p t - v - f < / mnt / t . sql - v查看导入的详细信息 - f是当中间遇到错误时，可以skip过去，继续执行下面的语句恢复binlog-file二进制日志文件。
使用binlog日志恢复MySQL数据库删除数据的方法
01-11
MySQL数据库的binlog日志是数据安全的重要保障，它记录了所有的DDL（Data Definition Language，如CREATE、ALTER、DROP等）和DML（Data Manipulation Language，如INSERT、UPDATE、DELETE等）语句，但不包括SELECT...
MySQL使用mysqldump+binlog完整恢复被删除的数据库原理解析
09-08
5. 执行drop database命令删除数据库，并尝试通过全备和binlog恢复。 通过这样的实验，我们可以验证mysqldump配合binlog的恢复机制是否有效。在实际应用中，确保定期备份和binlog记录完整是防止数据丢失的关键。...
Mysql的Binlog数据恢复:不小心删除数据库详解
12-16
3. **应用日志**：将`mysqlbinlog`生成的SQL语句应用到临时数据库中，这通常通过`mysql`客户端来实现。 4. **对比和迁移**：对比临时数据库和原始数据库的差异，然后将丢失的数据迁移到原始数据库中。 需要注意的是...
Linux上通过binlog文件恢复mysql数据库详细步骤
09-10
- 在使用binlog恢复时，务必确保binlog文件没有被意外删除或覆盖，因为它们是按照顺序写入且不可追加的。 - 精确恢复需要依赖binlog文件中的时间戳或位置信息，因此在日常管理中，应定期备份binlog位置信息。 - ...
使用mysqlbinlog恢复指定表
weixin_33882443的博客
04-26 1151
从整库备份的sql文件中导出某个表的sql语句时，vim查找到表的第一条INSERT语句后，按上下换行键计数INSERT语句的条数，然后按n yy复制，退出vim后，再新建一个文件，按p粘贴刚才的n条INSERT语句，再进行source导入。 ---------------------------------------------------------------- 一、登录数据库刷新...
MySQL利用binlog恢复库表
11-18
利用binlog恢复库表，要求如下： 1. 启用binlog日志 2. 创建db1库tb1表，插入3条记录 3. 删除tb1表中刚插入的3条记录 4. 使用mysqlbinlog恢复删除的3条记录
binlog恢复数据
开心就好
04-28 6231
文章目录mysql之binlog查看是否开启binlog日志：查找最新的binlog:查询偏移量：数据恢复（执行报错）：查看官方文档：binlog生成sql：grep搜索sql语句：binlog解析mysqlbinlog 常用参数：开启 binlog 日志my.ini配置：其中 binlog-format 有三种选项：其他：恢复单条记录：恢复单表记录：恢复数据库记录：sql语法在线检查： mysq...
MYSQL通过binlog来恢复被删除的数据库
一座孤岛
03-17 396
查询： 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 'bin
数据库binlog恢复数据
狂躁的辣条的博客
04-07 385
数据库binlog恢复数据 由于不想影响生产库的数据，所以我们将binlog 日志迁移到其他机器上进行恢复数据 将mysql的所有数据复制出来 docker cp b3cb037be179:/var/lib/mysql/ /home 重新命名后 然后另外创建一个mysql 进行查看数据 vim docker-compose.yaml version: '3' services: mysql: restart: always image: mysql:5.7.30 conta
mysql binlog 恢复 表_mysqldump+binlog完整恢复被删除的表/数据库
weixin_42117032的博客
01-27 556
使用mysqldump对全库进行完全备份mysqldump-h127.0.0.1 -uroot -pJimstars -P3306 --single-transaction --master-data=2 --events --routines --all-databases > /data/backup/20200820-1352-all_bak.sql创建测试库和测试表mysql-h12...
mysql binlog录入_MySQL binlog的几种日志录入格式以及区别
weixin_39775428的博客
02-04 190
MySQL binlog的几种日志录入格式以及区别(1)各种日志格式的涵义1.Statement：每一条会修改数据的sql都会记录在binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能 与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条 ...
使用binlog还原mysql数据库
Yfw的博客
03-08 984
(1) 开启二进制日志 要是日志没有启开，必须启用binlog，打开/etc/my.cnf，加入以下几行： [mysqld] log-bin=mariadb-bin 然后重新启动systemctl restart mariadb.service，会产生mariadb-bin.000001以及mariadb-bin.index，前面的日志文件是记录所有对数据的更新操作，后面的文件是存储所有二进制文...
mysql 如何通过binlog 来恢复删除的数据
05-10
使用 MySQL 的 binlog 可以恢复删除的数据，可以按照以下步骤进行操作： 1. 确认 MySQL 是否启用了 binlog，可以在 my.cnf 文件中查看是否开启了 binlog。 2. 找到需要恢复的数据所在的时间点，可以通过 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值