mysql binlog 使用指南

2023-11-07

MySQL binlog 详解

1.前言
     日志是把数据库的每一个变化都记载到一个专用的文件里,这种文件就叫做日志文件。Mysql默认只打开出错日志,因为过多的日志将会影响系统的处理性能。
  在5.0前支持文本格式和二进制格式,5.0后只支持二进制格式,因为二进制日志在性能、信息处理方面有更多的优点。


2.基础知识

  2.1、二进制日志的启用
    二进制日志由配置文件的log-bin选项负责启用,Mysql服务器将在数据根目录创建两个新文件XXX-bin.001和XXX-bin.index,若配置选项没有给出文件名,Mysql将使用主机名称命名这两个文件,其中.index文件包含一份全体日志文件的清单。
    Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件,而不会记录SELECT和没有实际
 2.2、更新的UPDATE语句。
  日志文件的扩展
    当停止或重启时,服务器会把日志文件记入下一个日志文件,Mysql会在重启时生成一个新的日志文件,文件序号递增,此外,如果日志文件超过max_binlog_size系统变量配置的上限时,也会生成新的日志文件。
  2.3、日志文件的查看
    Mysql提供了mysqlbinlog命令来查看日志文件,如mysqlbinlog xxx-bin.001 | more。在记录每条变更日志的时候,日志文件都会把当前时间给记录下来,以便进行数据库恢复。    
  2.4、日志文件的停用
    可以使用SET SQL_LOG_BIN=0命令停止使用日志文件,然后可以通过SET SQL_LOG_BIN=1命令来启用。
  2.5、使用日志进行数据库恢复
    如果遇到灾难事件,应该用最近一次制作的完整备份恢复数据库,然后使用备份之后的日志
  文件把数据库恢复到最接近现在的可用状态。
    使用日志进行恢复时需要依次进行,即最早生成的日志文件要最先恢复:
      mysqlbinlog xxx-bin.00001 | mysql -u root -p
      mysqlbinlog xxx-bin.00002 | mysql -u root -p


3.日志跟换策略
  使用索引来循环文件,在以下条件将循环至下一个索引

 a. 服务器重启
  b.服务器被更新
  c.日志达到了最大日志长度max_binlog_size ,一般配置文件中指定
  d.日志被刷新mysql> flush logs;    手动刷新


4.日志格式
  从官网文档中看到,之前的MySQL一直都只有基于statement的复制模式,直到5.1.5版本的MySQL才开始支持row level的复制。从5.0开始,MySQL的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现,给MySQL Replication复制又带来了更大的新挑战。另外,看到官方文档说,从5.1.8版本开始,MySQL提供了除Statement Level和Row Level之外的第三种复制模式:Mixed,实际上就前两种模式的结合。在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。新版本中的Statement Level还是和以前一样,仅仅记录执行的语句。而新版本的MySQL中对row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。


  --基于SQL语句的复制(statement-based replication,SBR),
  --基于行的复制(row-based replication,RBR),
  --混合模式复制(mixed-based replication,MBR)。

  三种格式的优缺点请参考:

 http://douya.blog.51cto.com/6173221/1605114 


 静态设置binlog格式:--永久生效

  log-bin = mysql-bin
  #binlog_format = "STATEMENT"
  #binlog_format = "ROW"
  binlog_format = "MIXED"

  动态修改binlog格式: --重启失效

  mysql> SET SESSION binlog_format = 'STATEMENT';
  mysql> SET SESSION binlog_format = 'ROW';
  mysql> SET SESSION binlog_format = 'MIXED'; 
  mysql> SET GLOBAL binlog_format = 'STATEMENT';
  mysql> SET GLOBAL binlog_format = 'ROW';
  mysql> SET GLOBAL binlog_format = 'MIXED';

5.binary log相关变量和参数

  5.1、命令行参数

  --log-bin [=file_name]

  设置此参数表示启用binlog功能,并制定路径名称,名称不写的话默认是主机名

  --log-bin-index[=file]

  设置此参数是指定二进制索引文件的路径与名称,默认在datadir ,可以不再配置文件中配置

  --max_binlog_size  推荐500M 

 Binlog最大值,最大和默认值是1GB,当binlog日志达到这个最大值时候,将会被自动更新出一个新的日志。

该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,

为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束。

  --binlog-do-db=db_name

  此参数表示只记录指定数据库的二进制日志

  --binlog-ignore-db=db_name

  此参数表示不记录指定的数据库的二进制日志

      -- expire_logs_days  =N             推荐一般保留7天

        此参数表示保留N天的binlog,超过这个时间将被自动删除  推荐使用此参数清除过期的日志 ,而不是手动

  5.2、系统变量

  log_bin

  binlog_cache_size

  此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

  max_binlog_cache_size

  此参数表示binlog使用的内存最大的尺寸

  binlog_cache_use

  使用二进制日志缓存的事务数量

  binlog_cache_disk_use

  使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。

  sync_binlog

  这个参数直接影响mysql的性能和完整性。

  sync_binlog=0:   推荐使用默认的参数 0 

  当事务提交后,Mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘,同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。

  sync_binlog=n,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,通知文件系统将Binlog文件缓存刷新到磁盘。

  Mysql中默认的设置是sync_binlog=0,即不做任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统Crash,在文件系统缓存中的所有Binlog信息都会丢失。

  

6.常见问题

   6.1实例:

> PURGE MASTER LOGS TO 'MySQL-bin.010';  //清除MySQL-bin.010日志
> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';   //清除2008-06-22 13:00:00前binlog日志
> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  //清除3天前binlog日志BEFORE,变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。

 
      6.2.清除binlog时,对从mysql的影响
       如果您有一个活性的从属服务器,该服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是会失败,并伴随一个错误。不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。

  --或使用命令:
  RESET MASTER   谨慎操作

  删除之前所有的binlog,并重新生成新的binlog,后缀从000001开始。

  注:如果您有一个活性的从属服务器,该服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是失败,并伴随一个错误。

  不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。

  当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。

 

  6.3、二进制日志不准确的处理

   默认情况下,并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能二进制日志中最后的语句丢失。 要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使二进制日志在每N次二进制日志写入后与硬盘同步。 即使sync_binlog设置为1,出现崩溃时,也有可能表内容和二进制日志内容之间存在不一致性。

   如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务), 如果sync_binlog =1并且硬盘/文件系统的确能根据需要进行同步(有些不需要)则不会发生,则输出错误消息 (“二进制日志<名>比期望的要小”)。 在这种情况下,二进制日志不准确,复制应从主服务器的数据快照开始。   

 

一般线上环境中,MySQL binlog可以使用以下配置文件

1
2
3
4
5
6
7
#binlog
log-bin = $dir/mysql-bin
binlog_format =  "STATEMENT"
max_binlog_size = 500M
binlog_cache_size = 64M
expire_logs_days  = 7 
sync_binlog=0:



######################基于mysqlbinlog的数据恢复

1,先使用全备进行恢复

1
mysql -uroot -p <back_up.sql

2,使用mysqlbinlog增量恢复

1
2
mysqlbinlog   --start-datetime="2015-07-01 06:00:03" --stop-datetime="2015-07-01 14:10:27"
binmysql-bin_.000003 |mysql -uroot -p







      本文转自crazy_charles 51CTO博客,原文链接:http://blog.51cto.com/douya/1788730,如需转载请自行联系原作者







本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)

mysql binlog 使用指南 的相关文章

  • Datawhale-数据类型、关键字、标识符

    Go 语言的空格 Go 语言中变量的声明必须使用空格隔开 如 var age int 语句中适当使用空格能让程序更易阅读 无空格 fruit apples oranges 在变量与运算符间加入空格 程序看起来更加美观 如 fruit app
  • Spring Bean xml定义说明

    转自 Spring Bean xml定义说明 在前一篇文章中 我们知道了Spring IoC容器管理的对象称之为 Spring Bean Spring Bean根据配置文件或注解创建 那么这个配置文件该如何编写呢首先Spring Bean的
  • 区间预测

    区间预测 MATLAB实现基于QRCNN BiGRU卷积双向门控循环单元多变量时间序列区间预测 目录 区间预测 MATLAB实现基于QRCNN BiGRU卷积双向门控循环单元多变量时间序列区间预测 效果一览 基本介绍 模型描述 程序设计 参

随机推荐

  • 三种振幅调制AM、DSB、SSB

    1 调制与解调 调制 用调制信号去控制载波信号的某一个参量的过程 解调 调制的逆过程 即从已调波中恢复原调制信号 2 振幅调制AM AM调幅波的数学表达式 波形 频谱 单一频率信号的频谱 限带信号调幅波的频谱 AM信号的生成原理框图 调幅波
  • 【山河送书第四期】:《Python之光:Python编程入门与实战》参与活动,免费送书五本!!

    山河送书第四期 Python之光 Python编程入门与实战 参与活动 免费送书五本 书本简介 本书亮点 配套丰富 购买链接 参与方式 往期赠书回顾 书本简介 作为一种极其流行的编程语言 Python已经成为了当今最为重要的生产力工具之一
  • Error: Rule can only have one resource source (provided resource and test + include + exclude)

    前言 vue3 0报错记录 报错描述和过程 1 在使用vue3 0的时候 经常会报错 vue loader v16 找不到 2 在使用中发现一个很奇怪的问题 刚报错找不到 vue loader v16 我使用 cnpm i save vue
  • 计算机桌面无法中英文切换,解决中文和英文无法正常切换问题

    近日多特小编的电脑系统重装了 安装上搜狗拼音输入法后 使用以前常用按键 ctrl space 切换中英文输入法状态 发现无法切换到英文输入状态 小编从网上寻找解决方法时 发现很多网友都遇到这个问题 网上的解决方法都不能解决问题 于是小编 决
  • Ubuntu 学习笔记

    1 实时监视系统log输出 tail f var log syslog 2
  • 学习 Python 之 Pandas库

    学习 Python 之 Pandas库 Pandas库 什么是Pandas库 DataFrame 创建和存储 1 使用DataFrame构造函数 1 使用列表创建 2 使用字典创建 3 从另一个DataFrame对象创建 2 从文件读取 1
  • 【正则表达式】去除首尾空格

    function Trim str return str replace s s g
  • 【华为oj】蛇形矩阵

    问题描述 题目说明 蛇形矩阵是由1开始的自然数依次排列成的一个矩阵上三角形 样例输入 5 样例输出 1 3 6 10 15 2 5 9 14 4 8 13 7 12 11 接口说明 原型 void GetResult int Num cha
  • GridLayout 居中显示

    GridLayout 居中显示 如果大家有使用过GridLayout来给RecyclerView使用 会发现其实是没有居中的 而官方也没有给出居中的方法 这里就讲讲如何让其居中 原理 要做的这一点其实很简单 只要我们自己计算每一个item的
  • 什么是 CSRF 、原理及其解决方式

    什么是 CSRF 跨站请求伪造 CSRF Cross site request forgery 也称为 XSRF Sea Surf 或Session Riding 是一个攻击向量 它欺骗 Web 浏览器在登录用户的应用程序中执行不需要的动作
  • selenium4新版本使用指南

    提示 本文默认你已经学会使用selenium的旧版本 因此对一些可以直接搜索到很多的内容 你应该是已经学会的 文章目录 前言 一 设置驱动 1 selenium4推荐方法 2 传统方法 二 查找元素 等待元素出现 三 动作API 暂停 pa
  • Qt + QR-Code-generator 生成二维码

    0 前言 之前使用 libgrencode 生成二维码 LGPL 协议实在不方便 所以需要找一个 github 星星多的 代码简单最好 header only 协议最好是 MIT 或者兼容协议而不是 GPL 或者 LPGL QR Code
  • 【无法联网】电脑wifi列表为空的解决方案

    打开电脑 发现wifi列表为空 点击设置显示未连接 首先检查是不是网卡驱动有问题 cmd devmgmt msc 找到网络适配器 看看网卡前面是否有感叹号 如果没有则说明网卡没问题 有问题则重装驱动 看看网络协议是否设置正确 找到 控制面板
  • android开机自启动APP及使用adb命令测试

    android开机自动运行APP实现方式其实很简单 在android系统运行时 会发出 android intent action BOOT COMPLETED 这个系统广播 因此我们监听它 并进行打开APP的操作即可 现在大多数的andr
  • Allegro导入网表报错

    文章首发于同名微信公众号 DigCore 欢迎关注同名微信公众号 DigCore 及时获取最新技术博文 E SPMHGE 82 Pin numbers do not match between symbol and component Ru
  • Linux 查看java进程的命令

    刚才去了微众税银面试 面试官赶时间 导致我的语速也快了起来 其中有个问题没答上 那就是 Linux下查看java进程的命令 回来做个记录 以防还有公司问到 之前工作上遇到Linux还是太少了 服 Linux下查看和停止所有java进程 在L
  • 教务管理系统乱码服务器不可,青果教务管理系统Post登录(二)

    承接上一篇贴子的后续 这次成功完成了预想功能 其实本来对学校的教务系统已经没什么兴趣了 但是前两天从吾爱上面看到一篇帖子 在post登录后获取自己的成绩直接对接短信平台 实现每当有新成绩公布的时候可以直接短信通知自己 这一下就又激起了我的兴
  • redis远程连接不上(转)

    解决redis远程连接不上的问题 redis现在的版本开启redis server后 redis cli只能访问到127 0 0 1 因为在配置文件中固定了ip 因此需要修改redis conf 有的版本不是这个文件名 只要找到相对应的co
  • jquery数组求和

    fn sum function fun var v 0 if this length gt 0 this each function index item if fun null fun undefined typeof fun funct
  • mysql binlog 使用指南

    MySQL binlog 详解 1 前言 日志是把数据库的每一个变化都记载到一个专用的文件里 这种文件就叫做日志文件 Mysql默认只打开出错日志 因为过多的日志将会影响系统的处理性能 在5 0前支持文本格式和二进制格式 5 0后只支持二进