Mysql中二进制日志操作方法说明
作者:跟着飞哥学编程 发布时间:2024-01-25 23:08:28
二进制日志
二进制日志中以“事件”的形式记录了数据库中数据的变化情况,对于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
:二进制文件的格式。取值可以是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 的二进制文件。
重启之后,可以看到我们配置的二进制日志的相关信息。
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)
来源:https://zhaoyanfei.blog.csdn.net/article/details/129265101
猜你喜欢
- MYSQL对大小写敏感见字如面,见标题知内容。你有遇到过因为MYSQL对大小写敏感而被坑的体验吗?之前看过阿里巴巴Java开发手册,在MyS
- 在CSS中,模式(pattern)匹配规则决定那种样式规则应用于文档树(document tree)的哪个元素。这些模式叫着选择符(sele
- 译者按:原文写于2011年末,虽然文中关于Python 3的一些说法可以说已经不成立了,但是作为一篇面向从其他语言转型到Python的程序员
- 前言大家应该都知道在编程语言中,定时任务是常用的一种调度形式,在Python中也涌现了非常多的调度模块,本文将简要介绍APScheduler
- 无图形界面的代码编写python代码作为脚本语言,其实最好直接使用vim在字符界面里面直接编写,这才是正道,但是作初学者,其实直接在服务器上
- 当一个页面上有一百个表单项,你是怎么获取上面的值勤的?这是一段简单的代码,你试试这段代码,试过后,欢迎留言说一下你的想法?index.asp
- 目录完整项目地址:首页安装特点完整项目地址:https://github.com/zsjtoby/DevOpsCloud欢迎使用极云监控系统
- 参照资料:selenium webdriver添加cookie: https://www.jb51.net/article/193102.h
- counter 是一种特殊的字典,主要方便用来计数,key 是要计数的 item,value 保存的是个数。from collections
- 应该只是一个简单的层的定位及鼠标事件吧<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.
- udf_WeekDayName 代码如下:CREATE FUNCTION [dbo].[udf_WeekDayName] ( ) RETUR
- 举个例子:q=[0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15]我想获取其中值等于7的那个值的下标,以便于用于其
- 我就废话不多说了,大家还是直接看代码吧~# 用一行代码实现for循环初始化数组o = 10b = [ o + u for u in rang
- 大家知道,mailto是网页设计制作中的一个非常实用的html标签,许多拥有个人网页的朋友都喜欢在网站的醒目位置处写上自己的电子邮件地址,这
- 前言在字典中查找某一个值的时候,若key不存在就会返回一个keyerror错误而不是一个默认值,如果想要返回一个默认值可以使用default
- 1、表示乘号2、表示倍数,例如:def T(msg,time=1): print((msg+' ')*time)
- 通常情况下,定义函数时都会选择有参数的函数形式,函数参数的作用是传递数据给函数,令其对接收的数据做具体的操作处理。在使用函数时,经常会用到形
- python与C的区别如下:1、语言类型不同。Python是一种动态类型语言,又是强类型语言。它们确定一个变量的类型是在您第一次给它赋值的时
- 怎样才能将在表A取得的数据插入另一个表B中?(1)对于表A和表B两个表结构完全相同的话〔字段个数,相应字段的类型等等〕,可以使用 inser
- Neo4j是一款开源图数据库,Py2neo提供了使用Python语言访问Neo4j的接口。本文介绍了使用Py2neo的NodeMatcher