`

MySQL二进制日志备份和恢复

 
阅读更多
基本概念
定义:
二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个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
这样数据库就可以完全的恢复到崩溃前的完全状态
分享到:
评论

相关推荐

    MySQL二进制日志查看工具

    MySQL二进制日志是数据库管理系统中用于记录所有更改数据的事件序列的一种机制,它对于数据库的备份、恢复以及故障排查至关重要。二进制日志查看工具则为开发者和DBA提供了一种方便的方式来分析、理解和操作这些日志...

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

     mysql二进制日志文件用来记录所有用户对数据库操作,即记录用户对数据库操作的sql语句。如果有此文件,当数据库发生意外时,可以通过此文件查看到用户在此文件记录的时间段内用户所做的操作,再和数据库备份配合...

    mysql重做2进制日志.doc

    总之,MySQL的二进制日志功能对于数据恢复和高可用性有着关键作用。通过正确地使用`mysqlbinlog`工具和管理二进制日志,可以有效地实现数据的回溯与恢复,同时在主从复制环境中解决冲突和错误。

    MySQL二进制日志:数据恢复的瑞士军刀

    - **可靠性**:MySQL提供了多种机制来确保数据的完整性和可靠性,包括事务支持、备份和恢复功能。 - **易于使用**:MySQL提供了简单直观的界面和丰富的文档,便于用户学习和使用。 - **可扩展性**:MySQL支持从小型...

    mysqlbinlog二进制日志资料.pdf

    MySQL的二进制日志(Binary Log)是数据库系统中用于记录所有更改数据的SQL语句的重要工具,它对于数据库的备份、恢复以及故障排查至关重要。`mysqlbinlog`是MySQL提供的一款命令行实用程序,专门用于处理和解析二...

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

    MySQL的二进制日志(Binary Log)是数据库系统中用于记录所有更改数据的SQL语句的一种机制,它对于数据库的恢复和复制至关重要。当数据意外丢失或需要回滚到某一特定时间点时,二进制日志就成为恢复数据的关键工具。...

    从MySQL ROW二进制日志还原更新。Undelete-Mysql.zip

    MySQL的ROW格式二进制...在实际应用中,理解MySQL的ROW格式二进制日志以及如何使用Python解析和恢复数据是一项关键技能。通过掌握这些知识,数据库管理员和开发者能够在面临数据丢失时快速、准确地执行数据恢复操作。

    mysqlbinlog二进制日志.pdf

    MySQL二进制日志文件的使用和mysqlbinlog工具的应用 MySQL二进制日志文件是MySQL数据库中的一种日志文件,用于记录数据库的所有操作,包括增、删、改、查等操作。二进制日志文件的主要作用是用于数据库的备份和恢复...

    MySQL数据库备份和恢复

    - **InnoDB表**:所有数据存储在一个或多个共享的文件中,备份复杂度较高,推荐使用二进制日志备份或mysqldump进行逻辑备份。 #### 最佳实践 1. **定期备份**:根据业务需求制定合理的备份策略,如每日、每周或每...

    mysql 日志备份恢复2

    本文将详细介绍MySQL中的几种日志类型,以及如何利用这些日志进行备份和恢复。 首先,MySQL的日志系统包括错误日志、二进制日志、普通查询日志、慢查询日志和中继日志。错误日志记录了启动、关闭过程中的信息,运行...

    07.26MySQL日志 备份与恢复1

    MySQL 有多种日志机制,包括错误日志、事务日志、二进制日志和查询日志。错误日志记录了服务器启动和关闭中的信息、服务器运行中的错误信息和时间调度运行的一个时间时产生的信息。事务日志记录了事务性的存储引擎...

    MySQL日志和数据恢复

    启用二进制日志需要在MySQL配置文件中设置`log-bin`选项。 3. 查询日志 (General Query Log) 查询日志记录了服务器接收到的所有SQL语句,包括成功执行的和失败的。这对于调试和性能分析非常有用,但因为会产生大量...

    mysql根据日志恢复数据详细步骤

    本文将详细介绍如何利用MySQL的二进制日志(Binlog)来恢复丢失的数据,并通过具体案例进行说明。 #### 二、基础知识介绍 1. **二进制日志(Binlog)** - **概念**:二进制日志是MySQL的一种日志类型,它记录了...

    mysql binlog二进制日志详解

    二进制日志的主要目的是在恢复使能够最大可能地更新数据库,因为二进制日志包含备份后进行的所有更新。 2。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。 不良影响: 运行服务器时若启用二进制...

    mysql日志管理、备份与恢复.pdf

    接下来我将详细介绍MySQL中不同日志的作用、如何管理和配置这些日志、以及数据库备份和恢复的相关知识。 首先,MySQL的日志系统非常强大,主要包括错误日志、二进制日志、中继日志和慢查询日志。 1. 错误日志:...

    实验报告六 MYSQL备份与恢复.docx

    - 使用`mysqlbinlog`命令将二进制日志中的操作恢复到数据库。 - **示例命令**: - `mysqldump -u root -p --lock-tables=false database_name &gt; database.sql`: 增量备份。 - `mysqlbinlog binlog_file | mysql -u...

Global site tag (gtag.js) - Google Analytics