基本概念
定义:
二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句。
作用:
1。二进制日志的主要目的是在恢复使能够最大可能地更新数据库,因为二进制日志包含备份后进行的所有更新。
2。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
不良影响:
运行服务器时若启用二进制日志则性能大约慢1%。
MySQL默认二进制日志是关闭状态,先手动改动配置文件打开二进制日志
在my.cnf(windows下是my.ini)中的mysqld下 添加
log-bin=mysql-bin
binlog_format=mixed //这行是来描述模式的,这两行直接复制到mysqld下即可
重启服务后便可以看到新增的日志文件了
日志位置
>>如果没有指定文件名,则Mysql使用hostname-bin文件.
>>如果指定了相对路径,则假定该路径相对于数据目录
>>Mysql在文件名后添加了数字索引.所以该文件最后的形式为filename.number
如果你在日志名中提供了扩展名(例如,–log-bin=file_name.extension),则扩展名被悄悄除掉并忽略。
更换策略:
使用索引来循环文件,在以下条件将循环至下一个索引
1。服务器重启
2。服务器被更新
3。日志到达了最大日志长度 max_binlog_size
4。日志被刷新 mysql> flush logs;
工具介绍:
shell>>mysqlbinlog [option] binlogFile> newfile
如: D:\mysql\log>mysqlbinlog binlog.000001 > 1.txt
一个例子:
log-bin=”D:/mysql/log/binlog” 那么,在该文件夹下就会有文件D:/mysql/log/binlog.000001等
常见问题
1.如何清除binlog
>>>使用下面的两个命令
PURGE {MASTER | BINARY} LOGS TO ‘log_name' //log_name不会被清除
PURGE {MASTER | BINARY} LOGS BEFORE ‘date' //date不会被清除
实例如下:
mysql> purge master logs to ‘binlog.000004′;
Query OK, 0 rows affected (0.01 sec)
mysql> purge master logs before '2009-09-22 00:00:00′;
Query OK, 0 rows affected (0.05 sec)
>>>或使用命令
RESET MASTER
删除之前所有的binlog,并重新生成新的binlog
后缀从000001开始
注:如果您有一个活性的从属服务器,该服务器当前正在读取您正在试图删除的日志之一,
则本语句不会起作用,而是会失败,并伴随一个错误。
不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。
当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。
2.记录到二进制日志知的内容配置
binlog-do-db=sales 只记录sales库
binlog-ignore-db=sales 除sales库不记录,其他都记录
但是如果在操作数据库之前,不使用use $dbname 那么所有的SQL都不会记录
如果使用了use $dbname,那么判断规则取决于这里的$dbname,而不是SQL中操作的库
3.二进制日志不准确的处理
默认情况下,并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能二进制日志中最后的语句丢失。
要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使二进制日志在每N次二进制日志写入后与硬盘同步。
即使sync_binlog设置为1,出现崩溃时,也有可能表内容和二进制日志内容之间存在不一致性。
如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务),
如果sync_binlog =1并且硬盘/文件系统的确能根据需要进行同步(有些不需要)则不会发生,则输出错误消息 (“二进制日志<名>比期望的要小”)。
在这种情况下,二进制日志不准确,复制应从主服务器的数据快照开始。
mysqldump --databases repltest --flush-logs --opt repltest>q:/repltest_full_01.sql
上面这句话可以在备份的同时刷新日志,生成新的日志,这样新日志里面的内容相当于这次备份之后的增量部分,可以用这种方式来实现mysql的增量备份,即先恢复数据库到之前备份的某个数据备份节点上,然后执行之后的二进制日志,因为二进制日志实质上记录的是所有数据变更的操作,可以来实现增量备份或者对某个时间点的还原
清理日志
如果每天都会生成大量的二进制日志,这些日志长时间不清理的话,将会对磁盘空间带来很大的浪费,所以定期清理日志是DBA维护mysql的一个重要工作
1)RESET MASTER
在上面查看日志存放的文件夹中,二进制日志命名的格式是以mysql-bin.*,*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志,新的日志从头开始
2)PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
3)-EXPIRE_LOGS_DAYS
此参数是设置日志的过期天数,过期的日志将会被自动删除,这有利于减少我们管理日志的工作量,需要修改my.cnf
EXPIRE_LOGS_DAYS = 3 //即为日志保存三天,三天之后过期的日志自动删除
恢复
bin-log是记录着mysql所有事件的操作,当mysql发生灾难性错误时,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复
完整恢复,假定我们每天凌晨2点都会使用mysqldump备份数据库,但在第二天早上9点由于数据库出现了故障,数据无法访问,需要恢复数据,先使用昨天凌晨备份的文件进行恢复到凌晨2点的状态,在使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
定义:
二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句。
作用:
1。二进制日志的主要目的是在恢复使能够最大可能地更新数据库,因为二进制日志包含备份后进行的所有更新。
2。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
不良影响:
运行服务器时若启用二进制日志则性能大约慢1%。
MySQL默认二进制日志是关闭状态,先手动改动配置文件打开二进制日志
在my.cnf(windows下是my.ini)中的mysqld下 添加
log-bin=mysql-bin
binlog_format=mixed //这行是来描述模式的,这两行直接复制到mysqld下即可
重启服务后便可以看到新增的日志文件了
日志位置
>>如果没有指定文件名,则Mysql使用hostname-bin文件.
>>如果指定了相对路径,则假定该路径相对于数据目录
>>Mysql在文件名后添加了数字索引.所以该文件最后的形式为filename.number
如果你在日志名中提供了扩展名(例如,–log-bin=file_name.extension),则扩展名被悄悄除掉并忽略。
更换策略:
使用索引来循环文件,在以下条件将循环至下一个索引
1。服务器重启
2。服务器被更新
3。日志到达了最大日志长度 max_binlog_size
4。日志被刷新 mysql> flush logs;
工具介绍:
shell>>mysqlbinlog [option] binlogFile> newfile
如: D:\mysql\log>mysqlbinlog binlog.000001 > 1.txt
一个例子:
log-bin=”D:/mysql/log/binlog” 那么,在该文件夹下就会有文件D:/mysql/log/binlog.000001等
常见问题
1.如何清除binlog
>>>使用下面的两个命令
PURGE {MASTER | BINARY} LOGS TO ‘log_name' //log_name不会被清除
PURGE {MASTER | BINARY} LOGS BEFORE ‘date' //date不会被清除
实例如下:
mysql> purge master logs to ‘binlog.000004′;
Query OK, 0 rows affected (0.01 sec)
mysql> purge master logs before '2009-09-22 00:00:00′;
Query OK, 0 rows affected (0.05 sec)
>>>或使用命令
RESET MASTER
删除之前所有的binlog,并重新生成新的binlog
后缀从000001开始
注:如果您有一个活性的从属服务器,该服务器当前正在读取您正在试图删除的日志之一,
则本语句不会起作用,而是会失败,并伴随一个错误。
不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。
当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。
2.记录到二进制日志知的内容配置
binlog-do-db=sales 只记录sales库
binlog-ignore-db=sales 除sales库不记录,其他都记录
但是如果在操作数据库之前,不使用use $dbname 那么所有的SQL都不会记录
如果使用了use $dbname,那么判断规则取决于这里的$dbname,而不是SQL中操作的库
3.二进制日志不准确的处理
默认情况下,并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能二进制日志中最后的语句丢失。
要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使二进制日志在每N次二进制日志写入后与硬盘同步。
即使sync_binlog设置为1,出现崩溃时,也有可能表内容和二进制日志内容之间存在不一致性。
如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务),
如果sync_binlog =1并且硬盘/文件系统的确能根据需要进行同步(有些不需要)则不会发生,则输出错误消息 (“二进制日志<名>比期望的要小”)。
在这种情况下,二进制日志不准确,复制应从主服务器的数据快照开始。
mysqldump --databases repltest --flush-logs --opt repltest>q:/repltest_full_01.sql
上面这句话可以在备份的同时刷新日志,生成新的日志,这样新日志里面的内容相当于这次备份之后的增量部分,可以用这种方式来实现mysql的增量备份,即先恢复数据库到之前备份的某个数据备份节点上,然后执行之后的二进制日志,因为二进制日志实质上记录的是所有数据变更的操作,可以来实现增量备份或者对某个时间点的还原
清理日志
如果每天都会生成大量的二进制日志,这些日志长时间不清理的话,将会对磁盘空间带来很大的浪费,所以定期清理日志是DBA维护mysql的一个重要工作
1)RESET MASTER
在上面查看日志存放的文件夹中,二进制日志命名的格式是以mysql-bin.*,*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志,新的日志从头开始
2)PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
3)-EXPIRE_LOGS_DAYS
此参数是设置日志的过期天数,过期的日志将会被自动删除,这有利于减少我们管理日志的工作量,需要修改my.cnf
EXPIRE_LOGS_DAYS = 3 //即为日志保存三天,三天之后过期的日志自动删除
恢复
bin-log是记录着mysql所有事件的操作,当mysql发生灾难性错误时,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复
完整恢复,假定我们每天凌晨2点都会使用mysqldump备份数据库,但在第二天早上9点由于数据库出现了故障,数据无法访问,需要恢复数据,先使用昨天凌晨备份的文件进行恢复到凌晨2点的状态,在使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
发表评论
-
(ZZ)Oracle约束的关键字Enable/Disable/Validate/Novalidate
2015-09-16 11:34 7241 组合特性说明 Validate ... -
MySQL的权限
2014-07-09 17:49 516一.权限表 mysql数据 ... -
CentOS5.4安装源码MySQL5.5.38
2014-07-06 15:16 2205安装环境 系统 CentOS 5.4 ,安装系统时顺带安 ... -
(ZZ)MySQL日期时间函数大全
2014-03-21 14:32 665DAYOFWEEK(date) 返回 ... -
(ZZ)使用Percona Xtrabackup对数据库进行部分备份
2014-02-28 14:21 584今天同事问我用Xtrabackup工具怎么对某些表进行备份 ... -
mysql实现Oracle中row_number over partition by
2014-02-25 17:26 1199SELECT t1.empno,t1.sal,t1.dept ... -
根据生日计算年龄
2014-02-24 18:58 801select (year(now())-year('1 ... -
(ZZ)mysql创建定时任务
2014-02-17 19:55 550一、前言 自 MySQL5.1.6起,增加了一个非常有特色 ... -
(ZZ)提高MySQL效率与性能的技巧
2014-01-24 16:26 489今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于 ... -
MySQL 数据类型
2014-01-23 13:25 582MySQL 的数值数据类型可以大致划分为两个类别,一个是整数 ... -
(ZZ)phpMyAdmin设置mysql存储过程及其问题
2014-01-22 21:23 653这几天需要用到数据库,使用的是MySQL,数据库 ... -
(ZZ)MYSQL SQL_NO_CACHE的真正含义
2013-02-17 11:29 1178当我们想用SQL_NO_CACHE来禁止结果缓存时发现结果 ... -
(zz)skip-grant-tables:非常有用的mysql启动参数
2013-02-05 14:20 1004介绍一个非常有用的mysql启动参数—— --skip-gr ... -
前缀索引
2013-02-04 15:51 764应用场景: 数据库里有个地址(address)字段, ... -
mysql常用操作
2013-02-01 10:39 7171:打开mysql数据库 mysql -h主机地址 -u用 ... -
(ZZ)oracle与mysql的批量插入
2013-01-29 16:34 692oracle与mysql的批量插入 oracle 批量插入 ... -
(ZZ)MySQL主从复制
2012-12-12 11:17 740ysql主从复制原理: mysql的主从复制是一个异步复制的过 ... -
(ZZ)msyql索引类型
2012-11-27 16:36 870【转】http://hi.baidu.com/mesong1/ ... -
mysql的hibernate配置
2012-05-14 18:35 728<hibernate-configuratio ... -
insert...on duplicate key update
2012-02-16 17:53 3944生产环境收集到bug,在小组管理员进行ban/unban小组成 ...
相关推荐
mysql二进制日志文件用来记录所有用户对数据库操作,即记录用户对数据库操作的sql语句。如果有此文件,当数据库发生意外时,可以通过此文件查看到用户在此文件记录的时间段内用户所做的操作,再和数据库备份配合...
二进制日志的主要目的是在恢复使能够最大可能地更新数据库,因为二进制日志包含备份后进行的所有更新。 2。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。 不良影响: 运行服务器时若启用二进制...
增量备份:使用二进制日志增量备份,使用mysqlbinlog 命令恢复二进制日志。SQL 语法备份及恢复。拷贝数据文件备份(对Innodb 还需拷贝日志文件)。MyISAM 表的检查与修复(另见《MySql 存储引擎》)。Innodb 表的...
1、利用Mysqldump+二进制日志实现备份 2、利用LVM快照+二进制日志实现备份 3、使用Xtrabackup备份 一:实验环境介绍: 系统介绍:CentOS6.4_X64 数据库版本:mysql-5.5.33 二:基于Mysqldump命令实现备份恢复 2.1、...
目前MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志,还可以是直接拷贝数据文件和相关的配置文件。MyISAM 表是...
所以本文我们将和大家一起分享学习下如何通过Mysql的二机制日志(binlog)来恢复数据。 系统环境: 操作系统:CentOS 6.5 X64 (虚拟机); WEB服务:PHP+Mysql+apache; 网站:为方便,直接在本地用蝉知系统搭建一个...
mysql完整备份和增量备份脚本,本文档采用mysqldump 对数据库进行备份,mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这...mysql需要开启二进制日志。
本软件Mysql定时备份器,目前为beta v1.0版(测试版),基于JAVA编写,运行时需JVM1.5版本以上支持.可以帮助windows环境下的mysql用户实现自动...具体可查阅'mysql二进制日志'相关内容. 此版本暂不支持触发器,视图和索引.
目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件。...
对 MySQL Cluster 集群数据库误操作的一些预防措施和误操作后进行数据恢复的三种方案:通过解析二进制日志生成逆向操作脚本进行恢复、通过全库备份及二进制日志进行恢复、通过延迟备库及二进制日志进行恢复,并给出...
1、利用mysqldump实现从逻辑角度完全备份mysql,配合二进制日志备份实现增量备份 2、利用lvs快照从物理角度实现几乎热备的完全备份,配合二进制日志备份实现增量备份 3、利用percona公司的xrabackup实现完全...
简介 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份与前一次...通过mysql的二进制日志间接实现增量备份: 二进制日志保存了所有更新或可能更新的数据 二进制日志在mysql启动时开始记录,且会重
最近在学习MySQL技术内幕 InnoDB存储引擎 第2版,整理了一些文档分享出来,同时也方便以后查看。若有不当之处,烦请批评指正。 1. MySQL体系结构和存储...8.4 二进制日志备份与恢复 8.5 热备 8.6 快照备份 8.7 复制
因为公司数据库服务器要进行新的重组,其中涉及的一项技术即是关于主从服务器...即使事务在执行期间是交错的,MySQL会串行地把事务写入到二进制日志中。在把事务写入二进制日志之后,主服务器告诉存储引擎提交事务。
如果MySQL服务器启用了二进制日志,你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如,从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog:用于处理二进制日志文件的实用工具”。 要...
C此命令非常小心,删除主数据库没用的二进制日志文件。如果误删除,那么从库就没有办法自动更新了。 change master; 从服务器上修改参数使用。 如果数据库表是myisam类型,并且权限够 grant replication slave,...
如果MySQL服务器启用了二进制日志,你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如,从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog:用于处理二进制日志文件的实用工具”。 要想...
二进制日志文件:保存更新数据的所有语句。 数据库复制:MySQL内部复制功能建立在两个或两个以上服务器之间,通过设定它们之间的主从关系来实现的。其中一个作为主服务器,其他的作为从服务器。 ;数据库恢复就是当...
使用事务日志:MySQL 的二进制日志(binlog)记录了数据库的所有变更操作,包括删除操作。你可以通过分析这些日志,找到误删数据的时间点和具体操作,然后通过回滚或者手动重放这些操作来进行数据恢复。 数据恢复...
二进制日志: 实时记录主库的更新操作,并且形式二进制码. 中继日志: 保存主库的更新信息 调用原理 1.当主库更新数据后,会实时的写入到二进制日志文件中 2.从库的IO线程,实时的监听主库的二进制文件,如果二进制文件...