服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Mysql中二进制日志操作方法说明

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

2023-03-19 16:53跟着飞哥学编程 Mysql

这篇文章主要介绍了Mysql中二进制日志操作方法，二进制日志中以“事件”的形式记录了数据库中数据的变化情况，对于MySQL数据库的灾难恢复起着重要的作用

二进制日志

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

开启二进制日志

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

?
1
2
3
4
5
6
7
[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：二进制文件的格式。取值可以是STATEMENT、ROW和MIXED。

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 的二进制文件。

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

?
1
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 二进制日志的步骤。

注：我提前创建的测试表

?
1
2
3
4
5
CREATE TABLE `test1` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

（1）向 test1 表中插入两条测试数据。

?
1
2
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．根据编号删除二进制日志

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

?
1
PURGE { BINARY | MASTER } LOGS TO 'log_name'

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
[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．根据时间删除二进制日志

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

?
1
PURGE { BINARY | MASTER } LOGS BEFORE datetime_expr

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

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

?
1
2
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目录下的文件。

?
1
2
3
4
5
6
7
[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命令行执行如下命令即可删除所有二进制日志文件。

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

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

?
1
2
3
4
[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 命令行执行如下命令暂时关闭二进制日志：

?
1
2
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二进制日志内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://zhaoyanfei.blog.csdn.net/article/details/129265101

  • 日志
  • 二进制
  • mysql

    • 延伸 · 阅读

    精彩推荐
    • Mysql分享几个简单MySQL优化小妙招

      分享几个简单MySQL优化小妙招

      这篇文章主要介绍了分享几个简单MySQL优化小妙招，分享内容有、设置大小写不敏感、MySql 的用户和权限管理等内容，需要的小伙伴可以参考一下,需要的朋...

      苏州程序大白6722022-10-14
    • Mysql浅谈MySQL函数

      浅谈MySQL函数

      这篇文章主要介绍MySQL函数，主要解说数学函数、字符串函数、 时间函数、加密函数,需要的朋友可以参考下面文章具体内容...

      嘻嘻硕9452021-11-19
    • Mysqlmysql 5.7 的 /etc/my.cnf 参数介绍

      mysql 5.7 的 /etc/my.cnf 参数介绍

      这篇文章主要介绍了mysql 5.7 的 /etc/my.cnf 参数介绍，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧 ...

      数据库人生6402021-03-25
    • Mysql减少mysql主从数据同步延迟问题的详解

      减少mysql主从数据同步延迟问题的详解

      本篇文章是对减少mysql主从数据同步延迟的问题进行了详细的分析介绍，需要的朋友参考下 ...

      MYSQL教程网2802019-12-30
    • MysqlMysql 如何实现多张无关联表查询数据并分页

      Mysql 如何实现多张无关联表查询数据并分页

      这篇文章主要介绍了Mysql 实现多张无关联表查询数据并分页的操作，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐...

      青春荒芜了流年8542021-08-12
    • MysqlMySQL binlog_ignore_db 参数的具体使用

      MySQL binlog_ignore_db 参数的具体使用

      这篇文章主要介绍了MySQL binlog_ignore_db 参数的具体作用，帮助大家更好的理解和使用MySQL数据库，感兴趣的朋友可以了解下...

      MySQL技术9592021-03-15
    • Mysqlmysql第一次安装成功后初始化密码操作步骤

      mysql第一次安装成功后初始化密码操作步骤

      在本篇文章里小编给大家整理了关于mysql第一次安装成功后初始化密码操作步骤以及相关知识点，有兴趣的朋友们可以学习下。...

      夏目友人喵17982020-12-01
    • MysqlMySQL与Oracle的语法区别详细对比

      MySQL与Oracle的语法区别详细对比

      Oracle和mysql的一些简单命令对比在本文中将会涉及到很多的实例，感兴趣的你不妨学习一下，就当巩固自己的知识了 ...

      MYSQL教程网3932019-12-19
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1819 　 1290 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 1615 　 2006 　 1175 　 1215 　 10048 　 2003 　 1366 　 3306 　 28000 　 1577 　 1135 　 1093 　 1786 　 1130 　 1067 　
    © 2019-2023 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图