Mysql Binlog

最新推荐文章于 2024-04-23 14:45:19 发布
最新推荐文章于 2024-04-23 14:45:19 发布
阅读量153 收藏
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ycd500756/article/details/104725542
版权

  • 原文：https://www.cnblogs.com/martinzhang/p/3454358.html

  • 参考阿里云《自建数据库使用RDS for MySQL备份按时间点恢复数据》：
    https://help.aliyun.com/knowledge_detail/41738.html?spm=5176.13910061.0.0.50af60ac2luY8p&aly_as=XJ7v6lD2

  • 参考《linux中mysql的安装》：
    https://blog.csdn.net/qq_34354257/article/details/90475791

  • 参考《binlog配置失败》：
    https://www.cnblogs.com/jpfss/p/11112283.html

概要
最近需要对阿里云的RDS的binlog文件，进行数据恢复。以本博客记录mysql的binlog的基本恢复过程。

mysql版本：5.7.22-log

一 开启binlog功能

1.查看当前MySQL 慢查询日志是否开启

mysql> show variables like 'log_%'; 
+----------------------------------------+---------------------+
| Variable_name                          | Value               |
+----------------------------------------+---------------------+
| log_bin                                | OFF                 |

结果:没有开启慢查询

2.开启慢查询

2.1 修改 /etc/my.cnf 配置文件，在[mysqld]区块下添加下面内容
注：我测试时发现需要把log-bin放到server-id后面，否则启动mysql服务失败

server-id=1
log-bin=mysql-bin

2.2 重启mysql服务

systemlctl restart mysqld

3.验证是否开启

mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| log_bin                                | ON                             |

4.默认binlog的存放路径在/var/lib/mysql 下

[root@Alibaba-Mark mysql]# ls  /var/lib/mysql | grep mysql-bin
mysql-bin.000001
mysql-bin.index

二 相关命令
1.查看所有binlog日志列表

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+

2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |

3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
补充：binlog文件的生成方式

  • 执行flush logs；
  • mysqld服务重启；
  • mysqlddump备份数据时加-F选项；
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

三 实验 逻辑备份+binlog备份恢复数据

1.数据准备，创建一个db_test数据库，创建一张tb_test的表，然后插入一下数据；

mysql> create database db_test;
Query OK, 1 row affected (0.02 sec)

mysql> use db_test;
Database changed
mysql> 
mysql> CREATE TABLE IF NOT EXISTS `tb_test`(
    ->    `id` INT UNSIGNED AUTO_INCREMENT,
    ->    `title` VARCHAR(100) NOT NULL,
    ->    `author` VARCHAR(40) NOT NULL,
    ->    `date` DATE,
    ->    PRIMARY KEY ( `id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO tb_test
    -> (title, author, date)
    -> VALUES
    -> ("test1", "mark'", NOW());
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> INSERT INTO tb_test
    -> (title, author, date)
    -> VALUES
    -> ("test2", "mark'", NOW());
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from tb_test;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | test1 | mark'  | 2020-03-08 |
|  2 | test2 | mark'  | 2020-03-08 |
+----+-------+--------+------------+
2 rows in set (0.00 sec)

3.备份文件（模拟定时备份数据）

[root@Alibaba-Mark ~]# mysqldump -u[数据库用户名] -p[用户名密码] -lF  -B db_test > /root/BAK.db_test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@Alibaba-Mark ~]# ls /root |grep BAK
BAK.db_test.sql

4.插入或修改数据（模拟备份完成后有数据的修改操作）

mysql> INSERT INTO tb_test
    -> (title, author, date)
    -> VALUES
    -> ("test3", "mark'", NOW());
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> 
mysql> update tb_test set title = "new title" where id =1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_test;
+----+-----------+--------+------------+
| id | title     | author | date       |
+----+-----------+--------+------------+
|  1 | new title | mark'  | 2020-03-08 |
|  2 | test2     | mark'  | 2020-03-08 |
|  3 | test3     | mark'  | 2020-03-08 |
+----+-----------+--------+------------+
3 rows in set (0.01 sec)

5.删除db_test(模拟数据库被误删除了)

mysql> drop database db_test;
Query OK, 1 row affected (0.11 sec)

mysql> select * from tb_test;
ERROR 1046 (3D000): No database selected

6.通过备份文件恢复主要的数据

[root@Alibaba-Mark ~]# mysql -u[数据用户名] -p[数据库用户密码] -h127.0.0.1 < /root/BAK.db_test.sql

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| db_test             |
+---------------------+

mysql> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from tb_test;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | test1 | mark'  | 2020-03-08 |
|  2 | test2 | mark'  | 2020-03-08 |
+----+-------+--------+------------+
2 rows in set (0.00 sec)

3.通过binlog恢复

mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                              |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.22-log, Binlog ver: 4                                                                                                                                                                                                                             |
| mysql-bin.000003 |  123 | Previous_gtids |         1 |         154 |                                                                                                                                                                                                                                                                   |
| mysql-bin.000003 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                              |
| mysql-bin.000003 |  219 | Query          |         1 |         302 | BEGIN                                                                                                                                                                                                                                                             |
| mysql-bin.000003 |  302 | Table_map      |         1 |         362 | table_id: 108 (db_test.tb_test)                                                                                                                                                                                                                                   |
| mysql-bin.000003 |  362 | Write_rows     |         1 |         418 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                   |
| mysql-bin.000003 |  418 | Xid            |         1 |         449 | COMMIT /* xid=455 */                                                                                                                                                                                                                                              |
| mysql-bin.000003 |  449 | Anonymous_Gtid |         1 |         514 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                              |
| mysql-bin.000003 |  514 | Query          |         1 |         589 | BEGIN                                                                                                                                                                                                                                                             |
| mysql-bin.000003 |  589 | Table_map      |         1 |         649 | table_id: 108 (db_test.tb_test)                                                                                                                                                                                                                                   |
| mysql-bin.000003 |  649 | Update_rows    |         1 |         731 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                   |
| mysql-bin.000003 |  731 | Xid            |         1 |         762 | COMMIT /* xid=463 */                                                                                                                                                                                                                                              |
| mysql-bin.000003 |  762 | Anonymous_Gtid |         1 |         827 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                              |
| mysql-bin.000003 |  827 | Query          |         1 |         928 | drop database db_test                                                                                                                                                                                                                                             |


[root@Alibaba-Mark ~]# mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/mysql-bin.000003 > /root/mysql-binlog.log
[root@Alibaba-Mark ~]# ls /root |grep .log
mysql-binlog.log

[root@Alibaba-Mark ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000003 --start-position=4 --stop-position=827 | mysql -umark -p7561669Dong! -P3306 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> selct * from tb_test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selct * from tb_test' at line 1
mysql> 
mysql> 
mysql> select * from tb_test;
+----+-----------+--------+------------+
| id | title     | author | date       |
+----+-----------+--------+------------+
|  1 | new title | mark'  | 2020-03-08 |
|  2 | test2     | mark'  | 2020-03-08 |
|  3 | test3     | mark'  | 2020-03-08 |
+----+-----------+--------+------------+
3 rows in set (0.00 sec)

补充：binlog文件的查看

查看所有的binlog文件

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |      1322 |
| mysql-bin.000003 |      3025 |
+------------------+-----------+
3 rows in set (0.01 sec)

mysql> show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

  • IN ‘log_name’:指定要查询的binlog文件名（不指定就是第一个binlog文件）
  • FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
  • LIMIT【offset】：偏移量(不指定就是0)
  • row_count :查询总条数（不指定就是所有行）

1.查询第一个binlog日志(我的是mysql-bin.000001)

mysql> show binlog events\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.22-log, Binlog ver: 4

2.查询指定的binlog日志

mysql> show binlog events in 'mysql-bin.000003'\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000003
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.22-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000003
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:

3.查询指定的binlog日志和pos位置

mysql> show binlog events in 'mysql-bin.000003' from 123\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000003
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:

4.查询指定的binlog日志和pos位置,并限制条数；

mysql> show binlog events in 'mysql-bin.000003' from 123 limit 2\G ;
*************************** 1. row ***************************
   Log_name: mysql-bin.000003
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 2. row ***************************
   Log_name: mysql-bin.000003
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
2 rows in set (0.01 sec)

5.查询指定的binlog日志和pos位置,设置偏移量并限制条数（类似于分页查询）；

mysql> show binlog events in 'mysql-bin.000003' from 123 limit 1,2\G ;
*************************** 1. row ***************************
   Log_name: mysql-bin.000003
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 2. row ***************************
   Log_name: mysql-bin.000003
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 302
       Info: BEGIN
2 rows in set (0.01 sec)
GZ-豆浆油条
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL Binlog Digger 4.28 + Mysql Binlog分析 + 数据库
08-19
MySQL Binlog Digger是一款免费的，且基于图形界面的binlog挖掘分析工具与sql审计工具。当发生误删、误增、误改时，它可以帮助我们从binlog中快速定位到误操作的重做语句(redo sql)，同时推理出回滚语句(undo sql)。...
mysql binlog日志恢复【亲测有效，有例子实测】
03-22
MySQL的二进制日志（binlog）是数据库系统中至关重要的组件，它记录了所有对MySQL数据库进行的改变操作，包括表结构的修改（如CREATE、ALTER TABLE等）和表数据的更新（INSERT、UPDATE、DELETE等）。binlog不记录...
mysql数据恢复，binlog详解
weixin_30678349的博客
07-25 148
个人博客:mysql数据恢复，binlog详解 binlog日志恢复数据,是挽救错误操作和数据损坏一根救命稻草，所以认识和使用binglog对于技术人员还是很有必要的 binlog一般用于 主从复制 中 master节点开启binlog把二进制日志传递给slave节点达到主从数据一致 第二点自然是用于数据恢复了，使用mysqlbinlog工具来恢复数据 因为我自己的网站遇到过mysql表...
看下mysql这个报错什么意思 You have an error in your SQL syntax; check the manual that corresponds to your MyS...
weixin_35750483的博客
01-09 2926
这个错误的意思是你的SQL语句中存在语法错误，检查一下你的SQL语句是否正确，特别注意是否有在应该输入关键字或者值的地方输入了NULL值。这个错误通常是由于在SQL语句中输入了一些不合法的字符或者语法导致的。建议检查一下你的SQL语句，确保没有语法错误。 ...
【MySQL 所遇问题】
最新发布
qq_51776197的博客
04-23 1700
不可见字符。某些文本处理软件可能会添加不可见的字符，从这些软件中复制过来的代码可能因此出现问题。解决方案：尝试在纯文本编辑器（如 Notepad++ 或 Sublime Text）中重新键入SQL 语句，而不是复制粘贴。MySQL各版本服务器的语法区别。字符集不匹配。解决方案：修改数据库、表的字符集；配置文件恢复默认。
关于mysql提示error in your SQL syntax的解决办法
zhtxilyj的博客
05-11 1364
提示这种很可能是出现了insert语句中value缺少值的错误。应该在插入之前判断值是否为空。
MySQL · 捉虫动态 · 字符集相关变量介绍及binlog中字符集相关缺陷分析
weixin_33882443的博客
01-24 115
MySQL字符集相关变量介绍及binlog中字符集相关缺陷分析 MySQL支持多种字符集（character set）提供用户存储数据，同时允许用不同排序规则（collation）做比较。 本文基于MySQL5.7介绍了字符集相关变量的使用，通过例子描述了这些变量具体意义。分析了MySQL binlog中字符集相关处理的缺陷，这些缺陷会导致复制中断或...
mysql binlog禁用
zengliguang的专栏
03-04 949
最后，确保在做出任何更改之前备份你的数据和配置文件，以防万一出现问题。这只会影响当前的会话。当你开始一个新的会话时，这个设置会被重置。要永久禁用二进制日志，你需要在 MySQL 的配置文件（通常是。（或删除该行），然后重启 MySQL 服务器。然后，重启 MySQL 服务器以使更改生效。如果你决定重新启用二进制日志，只需将。
MySQL Binlog Digger 4.8.0
12-14
MySQL Binlog Digger 4.8.0 是一个专为MySQL数据库设计的强大的日志挖掘和分析工具，尤其适用于数据恢复场景。它采用图形用户界面，使得操作更加直观易用。该工具支持在线和离线两种模式的binlog分析，能够帮助用户...
Mysql binlog查看工具
07-29
MySQL的二进制日志（Binary Log，简称binlog）是数据库系统中非常重要的一个功能，主要用于数据恢复、数据同步以及审计。它记录了所有改变数据库状态的事务，包括INSERT、UPDATE、DELETE等操作，为数据库提供了事务...
MySQL Binlog Digger 4.10
02-14
MySQL Binlog Digger基于图形界面，免安装的日志分析工具，能对在线binlog与离线binlog进行分析，在选定在线binlog或离线binlog日志后，可对数据库、表、binlog开始时间、binlog结束时间、误操作的重做类型进行信息...
mysql报错系列持续整理1064 - You have an error in your SQL syntax；...near...
Aldeo
08-05 3万+
前言 怎么又是这个错，感觉似曾相识之前遇到过多次了。是的这就是mysql语句报错1064 - You have an error in your SQL 。syntax;...near...感觉每次不管什么原因，都是报的类似错误呢。。。 本文旨在整理开发过程中遇到的类似的错误，并持续更新。 1.新建表时报错 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL se
mysql 储存过程 You have an error in your SQL syntax；
inn_ruin的博客
10-05 558
在编写一个MySQL储存过程的时候，出现了以上的报错 检查了一下发现自己既没有拼写错误，也应该没有语法错误，上网查找资料也没有找到相似的问题。然后在大佬帮助下写一些小的demo找出了问题所在。 放结论 需要在使用变量前，把所有变量都声明了 然后就不会有问题了。 找出问题的demo如下 然后就能确定为什么错误了…… ...
解决Mysql (1064) 错误: 1064 - You have an error in your SQL syntax；
热门推荐
迷途的羔羊的博客
09-14 6万+
我在给数据库中的表添加数据的时候， 写的语句是：INSERT INTO order VALUES (2, '编号B') 表结构： 出现了错误： INSERT INTO order VALUES (2, '编号B') 1064 - You have an error in your SQL syntax; check the manual that corresponds to your M...
Presto常用函数
ycd500756的博客
02-14 8157
Presto 0.216 官方文档 Presto 0.216 官方文档-时间 1.时间相关函数 1.1 ：date_parse date_parse（String，format）： 把字符格式的時間格式化為时间戳 SELECT date_parse('1995/01/07','%Y/%m/%d') 输出结果为：789408000000，即对应的时间为：1995-01-07 00:00:00 ...
流程控制：case when
ycd500756的博客
09-12 1747
1.定义 在编程语言中我们通过switch语句或者if-else if语句可以实现程序的流程控制。在sql语句中使用case 语法实现流程控制的效果。 2.用法 2.1用于等值判断 第一种用法用于等值判断，类似Java中的switch语句；模板语法如下 CASE 变量|表达式|字段 WHEN 要判断的值1 THEN 返回的值1或语句1 WHEN 要判断的值2 THE...
MySQL函数：字符函数
ycd500756的博客
09-08 740
背景 最近项目中的需求经常会使用到对查询到的字符串进行处理，所以就整理了一下MySQL中经常会用到的字符函数。 字符函数 函数名称 作用 CONCAT（） 字符串的拼接 CONCAT_WS() 使用指定的字符将目标字符串进行拼接 FORMAT() 数字格式化 LOWER() 字符串全部转换为小写 UPPER() 字符...
Mybatis的批量查询
ycd500756的博客
10-28 637
官网关于批量查询语法的介绍： 官网：
mysql binlog
08-08
MySQL binlog是MySQL数据库中的二进制日志文件，记录了数据库的所有更改操作，包括插入、更新和删除等操作。它可以用于数据恢复、主从复制和数据审计等场景。 你可以通过以下步骤来启用和使用MySQL binlog： 1. ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值