Mysql中二进制日志操作方法说明

目录
  • 二进制日志
  • 开启二进制日志
  • 查看二进制日志
  • 删除二进制日志
    • 1.根据编号删除二进制日志
    • 2.根据时间删除二进制日志
    • 3.删除所有二进制日志
  • 临时开启与关闭二进制

二进制日志

二进制日志中以“事件”的形式记录了数据库中数据的变化情况,对于MySQL数据库的灾难恢复起着重要的作用。

开启二进制日志

可以在 my.cnf 文件或者 my.ini 文件中进行如下配置来开启二进制日志。

[mysqld]
log_bin = /data/mysql/log/bin_log/mysql-bin
binlog_format= mixed
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
expire_logs_days = 10

各项配置说明如下:

log_bin:表示开启二进制日志。如果没有为此项赋值,则 MySQL 会在 DATADIR 选项指定的目录(MySQL的数据存放目录)下创建二进制文件。

binlog_format:二进制文件的格式。取值可以是STATEMENTROWMIXED

STATEMENT

记录SQL语句。日志文件小,节约IO,但是对一些系统函数不能准确复制或不能复制,如now()、uuid()等

ROW

记录表的行更改情况,可以为数据库的恢复、复制带来更好的可靠性,但是二进制文件的大小相较于STATEMENT会有所增加

MIXED

STATEMENT和ROW模式的混合。默认采用STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用ROW格式。

业内目前推荐使用的是 ROW 模式,准确性高,虽然说文件大,但是现在有SSD和万兆光纤网络,这些磁盘IO和网络IO都是可以接受的。

  • binlog_cache_size:二进制日志的缓存大小。
  • max_binlog_cache_size:二进制日志的最大缓存大小。
  • max_binlog_size:单个二进制日志文件的最大大小,当文件大小超过此选项配置的值时,会发生日志滚动,重新生成一个新的二进制文件。
  • expire_logs_days:二进制日志的过期时间。如果配置了此选项,则 MySQL 会自动清理过期的二进制日志。此选项的默认值为 0 ,表示 MySQL 不会清理过期日志。

配置完成后,重启 MySQL 才能使配置生效。此时,会在 /data/mysql/log/bin_log 目录下生成 MySQL 的二进制文件。

重启之后,可以看到我们配置的二进制日志的相关信息。

show variables like '%log_bin%';

±--------------------------------±-----------------------------------------+

| Variable_name | Value |

±--------------------------------±-----------------------------------------+

| log_bin | ON |

| log_bin_basename | D:\mysql-8.0.28-winx64\data\binlog |

| log_bin_index | D:\mysql-8.0.28-winx64\data\binlog.index |

| log_bin_trust_function_creators | ON |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

±--------------------------------±-----------------------------------------+

6 rows in set

查看二进制日志

二进制日志文件不能以纯文本文件的形式来查看,可以使用 MySQL 的 mysqlbinlog 命令进行查看。接下来简单介绍一下查看 MySQL 二进制日志的步骤。

注:我提前创建的测试表

CREATE TABLE `test1` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

(1)向 test1 表中插入两条测试数据。

INSERT INTO test1 (id,name) VALUES(5,"赵山河");
INSERT INTO test1 (id,name) VALUES(6,"跟着学编程");

(2)使用 mysqlbinlog 命令查看二进制日志

mysqlbinlog --no-defaults /data/mysql/log/bin_log/mysql-bin.000001

二进制日志中记录了向 test1 数据表中插入数据的 SQL 语句。

注意:查看 /data/mysql/log/bin_log 目录下生成的 MySQL 二进制文件时,发现有一个 mysql-bin.index 文件,这个文件不记录二进制内容,其中记录的是当前目录下存在的所有二进制文件的完整路径。可以以纯文本文件的形式来查看 mysql-bin.index 文件。

cat /data/mysql/log/bin_log/mysql-bin.index

删除二进制日志

MySQL中除了通过配置二进制日志的过期时间,由 MySQL 自动删除过期的二进制日志外,还提供了3种安全的手动删除二进制日志的方法。

在正式介绍手动删除 MySQL 二进制日志的方法之前,先对 MySQL 进行多次重启操作,使 MySQL 能够生成多个二进制日志文件,以便进行删除测试。

多次重启MySQL后,再次查看 /data/mysql/log/bin_log 目录下的文件。

1.根据编号删除二进制日志

根据编号删除二进制日志,语法格式如下:

PURGE { BINARY | MASTER } LOGS TO 'log_name'

在MySQL命令行执行此语法格式的SQL语句,会删除比指定文件名编号小的所有二进制日志文件。例如,删除比mysql-bin.000003文件编号小的所有二进制日志文件。

mysql> PURGE MASTER LOGS TO 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)

SQL语句执行成功,查看/data/mysql/log/bin_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
total 36
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000003
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000004
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000005
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000006
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
-rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
-rw-r----- 1 mysql mysql 328 Jan 17 16:29 mysql-bin.index

发现mysql-bin.000001文件和mysql-bin.000002文件被删除了。说明根据编号删除二进制日志时,只会删除比当前指定的文件编号小的二进制日志文件,不会删除当前指定的二进制日志文件。

2.根据时间删除二进制日志

根据时间删除二进制日志,语法格式如下:

PURGE { BINARY | MASTER } LOGS BEFORE datetime_expr

执行此语法格式的SQL语句时,MySQL会删除指定时间以前的二进制日志。

例如,删除“2020-01-17 16:21:00”之前的二进制日志文件。

mysql> PURGE MASTER LOGS BEFORE '2020-01-17 16:21:00';
Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功,查看/data/mysql/log/bin_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
total 20
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
-rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
-rw-r----- 1 mysql mysql 164 Jan 17 16:37 mysql-bin.index

“2020-01-17 16:21:00”之前的二进制日志文件已经被删除了,但不会删除“2020-01-17 16:21:00”时间点的二进制日志文件。

3.删除所有二进制日志

在MySQL命令行执行如下命令即可删除所有二进制日志文件。

mysql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)

SQL语句执行成功,再次查看/data/mysql/log/bin_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
total 8
-rw-r----- 1 mysql mysql 155 Jan 17 16:41 mysql-bin.000001
-rw-r----- 1 mysql mysql  41 Jan 17 16:41 mysql-bin.index

此时/data/mysql/log/bin_log目录下的所有二进制文件已经被删除,并且二进制文件重新从000001开始编号。

临时开启与关闭二进制

在 Mysql 命令行执行如下命令暂时关闭二进制日志:

mysql> SET sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

暂时开启二进制日志,则需要在MySQL命令行执行如下命令:

mysql> SET sql_log_bin = 1;

Query OK, 0 rows affected (0.00 sec)

到此这篇关于Mysql中二进制日志操作方法说明的文章就介绍到这了,更多相关Mysql二进制日志内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL中Binary Log二进制日志文件的基本操作命令小结

    MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库.用于slave端执行增删改,保持与master同步. 1.开启binary log功能 需要修改mysql的配置文件,本篇的实验环境是win7,配置文件为mysql安装目录\MySQL Server 5.1下的my.ini,添加一句log_bin = mysql_bin即可 eg: [mysqld] ...... log_bin = mysq

  • mysql binlog二进制日志详解

    基本概念 定义: 二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句. 作用: 1.二进制日志的主要目的是在恢复使能够最大可能地更新数据库,因为二进制日志包含备份后进行的所有更新. 2.二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句. 不良影响: 运行服务器时若启用二进制日志则性能大约慢1%. 如何启动: 通过 –log-bin=file选项可以启用 (更改my.ini文件) 日志位置 >>如果没有指定文件名,则Mysql使

  • mysql binlog(二进制日志)查看方法

    例如,在创建新表或更新现有表上的数据时,这些事件将存储在mysql binlog中,也就是MySQL数据库的二进制日志. 二进制日志在MySQL复制中非常有用,主服务器将数据从二进制日志发送到远程服务器. 当您在MySQL中执行任何类型的恢复操作时,您还将处理二进制日志文件. mysqlbinlog命令用于以可读的用户友好格式查看二进制日志的内容. 那么我们怎么获取查看mysql binlog(二进制日志)? 下面给大家介绍获取当前二进制日志列表的命令方法. 从mysql执行以下show bin

  • 详解如何通过Mysql的二进制日志恢复数据库数据

    经常有网站管理员因为各种原因和操作,导致网站数据误删,而且又没有做网站备份,结果不知所措,甚至给网站运营和盈利带来负面影响.所以本文我们将和大家一起分享学习下如何通过Mysql的二机制日志(binlog)来恢复数据. 系统环境: 操作系统:CentOS 6.5 X64  (虚拟机): WEB服务:PHP+Mysql+apache: 网站:为方便,直接在本地用蝉知系统搭建一个DEMO站点: 操作步骤: 1.开启binlog功能及基本操作: 2.往站点添加数据: 3.刷新binlog日志: 4.删除

  • mysql二进制日志文件恢复数据库

    二进制日志的文件的作用 mysql二进制日志文件用来记录所有用户对数据库操作,即记录用户对数据库操作的sql语句.如果有此文件,当数据库发生意外时,可以通过此文件查看到用户在此文件记录的时间段内用户所做的操作,再和数据库备份配合使用,即可再现用户操作,使数据库恢复. 二进制日志文件的弊端 二进制日志文件开启后,所有对数据库操作的记录均会被记录到此文件, 所以,当长时间开启之后,日志文件会变得很大,占用磁盘空间. 使用二进制日志文件恢复数据库 开启日志文件 mysql默认是不开启日志文件的功能的,

  • 解析mysql二进制日志处理事务与非事务性语句的区别

    在事务性语句执行过程中,服务器将会进行额外的处理,在服务器执行时多个事务是并行执行的,为了把他们的记录在一起,需要引入事务缓存的概念.在事务完成被提交的时候一同刷新到二进制日志.对于非事务性语句的处理.遵循以下3条规则: 1)如果非事务性语句被标记为事务性,那么将被写入事务缓冲. 2)如果没有标记为事务性语句,而且事务缓存中没有,那么直接写入二进制日志. 3)如果没有标记为事务性的,但是事务缓存中有,那么写入事务缓冲. 注意如果在一个事务中有非事务性语句,那么将会利用规则2,优先将该影响非事务表

  • MySQL中二进制与重做日志文件的基本概念学习教程

    二进制日志 二进制日志记录了所有对数据库执行更改的操作,二进制主要有以下两种作用: 1.恢复(recovery)2.复制(replication) 二进制日志的启动:配置参数log-bin[=name],如果不指定name,则默认二进制日志文件名为主机名,后缀名为二级制日志的序列号,所在路径为数据库所在目录. 以index为后缀的文件为二进制日志的索引文件,用来存储过往生产的二进制日志. 和二进制日志相关的参数: max_binlog_size.binlog_cache_size.sync_bi

  • Mysql中二进制日志操作方法说明

    目录 二进制日志 开启二进制日志 查看二进制日志 删除二进制日志 1.根据编号删除二进制日志 2.根据时间删除二进制日志 3.删除所有二进制日志 临时开启与关闭二进制 二进制日志 二进制日志中以“事件”的形式记录了数据库中数据的变化情况,对于MySQL数据库的灾难恢复起着重要的作用. 开启二进制日志 可以在 my.cnf 文件或者 my.ini 文件中进行如下配置来开启二进制日志. [mysqld] log_bin = /data/mysql/log/bin_log/mysql-bin binl

  • MySQL中查询日志与慢查询日志的基本学习教程

    一.查询日志 查询日志记录MySQL中所有的query,通过"--log[=file_name]"来打开该功能.由于记录了所有的query,包括所有的select,体积比较大,开启后对性能也有比较大的影响,所以请大家慎用该功能.一般只用于跟踪某些特殊的sql性能问题才会短暂打开该功能.默认的查询日志文件名为:hostname.log.  ----默认情况下查看是否启用查询日志: [root@node4 mysql5.5]# service mysql start Starting My

  • mysql中general_log日志知识点介绍

    以下操作演示都是基于mysql5.6.36版本: 工作中经常遇到这样的问题:mysql数据访问能量很大,想要从sql方面优化.研发经常会问到能看到哪些SQL执行比较频繁吗?回道:不能哦,只能看到当前正在运行的SQL和慢日志里记录的SQL.因为为了性能考虑,一般general log不会开启.slow log可以定位一些有性能问题的sql,而general log会记录所有的SQL.然而有时候生产上的mysql出现性能问题,短时间开启general log,来获取sql执行的情况,对排查和分析my

  • 全面盘点MySQL中的那些重要日志文件

    目录 简介 日志分类 参数文件 错误日志文件 全量日志文件 慢查询日志 二进制日志文件 审计日志 中继日志 PID文件 Socket文件 数据库与表 存储引擎文件 简介 该篇文章对MySQL中的日志进行总结与简单介绍,不会涉及的太深.主要的目的是为了对MySQL中的日志文件有一个体系化的了解. 日志分类 MySQL中的日志文件,配置文件.错误日志文件.二进制文件(binary log).慢查询日志(slow-query-log).全量日志(genera log).审计日志(audit log).

  • 解说mysql之binlog日志以及利用binlog日志恢复数据的方法

    众所周知,binlog日志对于mysql数据库来说是十分重要的.在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷! 废话不多说,下面是梳理的binlog日志操作解说: 一.初步了解binlog MySQL的二进制日志binlog可以说是MySQL最重要的日志,它记录了所有的DDL和DML语句(除了数据查询语句select),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的. ---

  • MySQL中的 Binlog 深度解析及使用详情

    目录 配置文件参数说明 常用的Binlog操作命令 写Binlog的时机 Binlog文件以及扩展 Binlog与Redo log区别 Binlog写入过程 二阶段提交 redo 与 binlog 的刷盘时机 能否只用 redo log 不要 binlog? Binlog 组提交机制 Binlog的日志格式 Statement Row Mixed Binlog 相关参数 清理过期的Binlog日志 手工删除binlog 自动删除binlog 用途 主从同步 复制线程 主从复制优化 数据恢复 my

随机推荐