MySQL 开启二进制日志相关操作

MySQL 开启二进制日志相关操作

1、开启bin-log二进制日志

my.ini 配置文件 查到或添加:
log-bin=mysql-bin

my.cnf配置文件添加:

root@cnetos7 etc]# vim my.cnf
server-id = 1
log-bin=mysql-bin

注意:配置文件修改保存后,一定要重启mysql;

2、查看bin-log二进制日志
a、查看是否启用了二进制日志

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

b、查看所有二进制日志文件的列表,请使用以下语句:

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

c、查看当前二进制日志文件的名称,请使用以下语句:

mysql> SHOW MASTER STATUS; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3、使用bin-log二进制日志(恢复数据)

下面介绍几个bin-log二进制日志常用的sql命令:
(1)、flush logs; 会多一个最新的bin-log日志
(2)、show master status; 查看最后一个bin-log日志的相关信息
(3)、reset master; 清空所有的bin-log日志

1、我们先看一下bin-log日志放在哪里?
windos服务器,一般直接放在mysql/data目录下;
linux服务器,有的是放在mysql/data目录下的,有的却是在/var/lib/mysql;而且data目录可以自定义的。

2、查看bin-log二进制日志文件具体内容:
二进制日志用记事本、编辑器、vi或vim等等打开都是乱码的,必须用mysqlbinlog才能正确打开。

windos下直接“开始-运行-cmd”,执行:

F:\wamp64\bin\mysql\mysql5.7.14\bin\mysqlbinlog F:\wamp64\bin\mysql\mysql5.7.14\data\mysql-bin.000001

linux下直接执行:

[root@cnetos7 etc]# cd /var/lib/mysql
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001

注意:上面路径请根据自己的情况做修改;二进日志我把最主要的东西截图如下,因为这些东西对后面的恢复数据有着重要的依据;

4、利用bin-log二进制日志文件恢复数据
mysql二进制日志文件恢复数据有两种方式:使用事件时间的时间点恢复和使用事件位置的时间点恢复;

(1)使用事件时间的时间点恢复:
要指明恢复的开始和结束时间,两个选项参数是: --start-datetime和 --stop-datetime;

语法格式:mysqlbinlog [参数] DATETIME
例如,假设在2021年1月24日上午10:00,执行了一个删除了一个大表的SQL语句。要还原表和数据,可以恢复前一天的备份,然后执行以下命令:

shell> mysqlbinlog --stop-datetime="2021-01-24 9:59:59" \  
         /var/lib/mysql/mysql-bin.000001 | mysql -u root -p  
 

如果您还需要恢复2017年4月20日上午10:00之后发生的活动。可以用命令:

shell> mysqlbinlog --start-datetime="2021-01-24 10:01:00" \  
         /var/lib/mysql/mysql-bin.000001 | mysql -u root -p  

(2)使用事件位置的时间点恢复:
而不是指定日期和时间, --start-position 和–stop-position选项 可用于指定日志位置。它们的工作方式与–start-datetime和 --stop-datetime选项相同,只是指定日志位置数字而不是日期。使用可能会使您更准确地了解日志的哪个部分进行恢复,特别是如果许多事务发生在与SQL语句相同的时间内。

要确定位置号码,请在执行不需要的事务的时间范围内运行 mysqlbinlog,但将结果重定向到文本文件进行检查。你可以这样做:

shell> mysqlbinlog --start-datetime="2021-01-24 9:55:00" \  
         --stop-datetime="2021-01-24 10:05:00" \  
           /var/lib/mysql/mysql-bin.000001 > /tmp/mysql_restore.sql  

该命令/tmp在执行有害SQL语句的时间内在包含SQL语句的目录中创建一个小文本文件 。使用文本编辑器打开此文件,并查找您不想重复的语句。

确定二进制日志中的位置以停止并恢复恢复并记下它们。位置被标记为 log_pos后跟一个数字。恢复以前的备份文件后,使用位置号来处理二进制日志文件。例如,您将使用如下命令:

shell> mysqlbinlog --stop-position=368312  /var/lib/mysql/mysql-bin.000001 \  
         | mysql -u root -p  
shell> mysqlbinlog --start-position=368315  /var/lib/mysql/mysql-bin.000001 \  
         | mysql -u root -p 

第一个命令恢复所有交易,直到给定的停止位置。
第二个命令从所给出的起始位置恢复所有事务,直到二进制日志结束。由于mysqlbinlog的输出 SET TIMESTAMP在每个SQL语句记录之前都包含语句,恢复的数据和相关的MySQL日志将反映执行事务的原始时间。

  • 0
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

xiongyingli

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值