flyinweb's blog

使用mysqlbinlog工具来恢复数据

　　如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog：用于处理二进制日志文件的实用工具”。
　　要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。启用二进制日志的选项为—— log-bin。要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：

　　你还可以从命令行输入下面的内容：

　　将密码my_pwd替换为服务器的root密码。
1. 指定恢复时间
　　对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2006年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：

　　该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

　　在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。
2. 指定恢复位置
　　也可以不指定日期和时间，而使用mysqlbinlog的选项——start-position和——stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

　　该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

　　上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。

附：

#mysqlbinlog --help
mysqlbinlog Ver 3.0 for redhat-linux-gnu at i686
By Monty and Sasha, for your professional use
This software comes with NO WARRANTY: This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client

Usage: mysqlbinlog [options] log-files
-d, --database=name List entries for just this database (local log only).
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-f, --force-read Force reading unknown binlog events.
-?, --help Display this help and exit.
-h, --host=name Get the binlog from server.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
-P, --port=# Use port to connect to the remote server.
-j, --position=# Deprecated. Use --start-position instead.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-r, --result-file=name
Direct output to a given file.
-R, --read-from-remote-server
Read binary logs from a MySQL server
--open_files_limit=#
Used to reserve file descriptors for usage by this
program
-s, --short-form Just show the queries, no extra info.
-S, --socket=name Socket file to use for connection.
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-V, --version Print version and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
database (No default value)
disable-log-bin FALSE
force-read FALSE
host (No default value)
offset 0
port 3306
position 4
read-from-remote-server FALSE
open_files_limit 64
short-form FALSE
socket /tmp/mysql.sock
start-datetime (No default value)
stop-datetime (No default value)
start-position 4
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
local-load (No default value)

mysqlbinlog：用于处理二进制日志文件的实用工具
服务器生成的二进制日志文件写成二进制格式。要想检查这些文本格式的文件，应使用mysqlbinlog实用工具。

应这样调用mysqlbinlog：

例如，要想显示二进制日志binlog.000003的内容，使用下面的命令：

输出包括在binlog.000003中包含的所有语句，以及其它信息例如每个语句花费的时间、客户发出的线程ID、发出线程时的时间戳等等。

通常情况，可以使用mysqlbinlog直接读取二进制日志文件并将它们用于本地MySQL服务器。也可以使用--read-from-remote-server选项从远程服务器读取二进制日志。

当读取远程二进制日志时，可以通过连接参数选项来指示如何连接服务器，但它们经常被忽略掉，除非你还指定了--read-from-remote-server选项。这些选项是--host、--password、--port、--protocol、--socket和--user。

还可以使用mysqlbinlog来读取在复制过程中从服务器所写的中继日志文件。中继日志格式与二进制日志文件相同。

在5.11.3节，“二进制日志”中详细讨论了二进制日志。

mysqlbinlog支持下面的选项：

· ---help，-？

显示帮助消息并退出。

· ---database=db_name，-d db_name

只列出该数据库的条目(只用本地日志)。

· --force-read，-f

使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。

· --hexdump，-H

在注释中显示日志的十六进制转储。该输出可以帮助复制过程中的调试。在MySQL 5.1.2中添加了该选项。

· --host=host_name，-h host_name

获取给定主机上的MySQL服务器的二进制日志。

· --local-load=path，-l pat

为指定目录中的LOAD DATA INFILE预处理本地临时文件。

· --offset=N，-o N

跳过前N个条目。

· --password[=password]，-p[password]

当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中--password或-p选项后面没有 密码值，则提示输入一个密码。

· --port=port_num，-P port_num

用于连接远程服务器的TCP/IP端口号。

· --position=N，-j N

不赞成使用，应使用--start-position。

· --protocol={TCP | SOCKET | PIPE | -position

使用的连接协议。

· --read-from-remote-server，-R

从MySQL服务器读二进制日志。如果未给出该选项，任何连接参数选项将被忽略。这些选项是--host、--password、--port、--protocol、--socket和--user。

· --result-file=name, -r name

将输出指向给定的文件。

· --short-form，-s

只显示日志中包含的语句，不显示其它信息。

· --socket=path，-S path

用于连接的套接字文件。

· --start-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：

该选项可以帮助点对点恢复。

· --stop-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见--start-datetime选项。该选项可以帮助及时恢复。

· --start-position=N

从二进制日志中第1个位置等于N参量时的事件开始读。

· --stop-position=N

从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

· --to-last-logs，-t

在MySQL服务器中请求的二进制日志的结尾处不停止，而是继续打印直到最后一个二进制日志的结尾。如果将输出发送给同一台MySQL服务器，会导致无限循环。该选项要求--read-from-remote-server。

· --disable-logs-bin，-D

禁用二进制日志。如果使用--to-last-logs选项将输出发送给同一台MySQL服务器，可以避免无限循环。该选项在崩溃恢复时也很有用，可以避免复制已经记录的语句。注释：该选项要求有SUPER权限。

· --user=user_name，-u user_name
连接远程服务器时使用的MySQL用户名。
· --version，-V
显示版本信息并退出。
还可以使用--var_name=value选项设置下面的变量：
· open_files_limit
指定要保留的打开的文件描述符的数量。

可以将mysqlbinlog的输出传到mysql客户端以执行包含在二进制日志中的语句。如果你有一个旧的备份，该选项在崩溃恢复时也很有用(参见5.9.1节，“数据库备份”)：

或：

如果你需要先修改含语句的日志，还可以将mysqlbinlog的输出重新指向一个文本文件。(例如，想删除由于某种原因而不想执行的语句)。编辑好文件后，将它输入到mysql程序并执行它包含的语句。

mysqlbinlog有一个--position选项，只打印那些在二进制日志中的偏移量大于或等于某个给定位置的语句(给出的位置必须匹配一个事件的开始)。它还有在看见给定日期和时间的事件后停止或启动的选项。这样可以使用--stop-datetime选项进行点对点恢复(例如，能够说“将数据库前滚动到今天10:30 AM的位置”)。

如果MySQL服务器上有多个要执行的二进制日志，安全的方法是在一个连接中处理它们。下面是一个说明什么是不安全的例子：

使用与服务器的不同连接来处理二进制日志时，如果第1个日志文件包含一个CREATE TEMPORARY TABLE语句，第2个日志包含一个使用该临时表的语句，则会造成问题。当第1个mysql进程结束时，服务器撤销临时表。当第2个mysql进程想使用该表时，服务器报告 “不知道该表”。

要想避免此类问题，使用一个连接来执行想要处理的所有二进制日志中的内容。下面提供了一种方法：

另一个方法是：

mysqlbinlog产生的输出可以不需要原数据文件即可重新生成一个LOAD DATA INFILE操作。mysqlbinlog将数据复制到一个临时文件并写一个引用该文件的LOAD DATA LOCAL INFILE语句。由系统确定写入这些文件的目录的默认位置。要想显式指定一个目录，使用--local-load选项。

因为mysqlbinlog可以将LOAD DATA INFILE语句转换为LOAD DATA LOCAL INFILE语句(也就是说，它添加了LOCAL)，用于处理语句的客户端和服务器必须配置为允许LOCAL操作。参见5.6.4节，“LOAD DATA LOCAL安全问题”。

警告：为LOAD DATA LOCAL语句创建的临时文件不会自动删除，因为在实际执行完那些语句前需要它们。不再需要语句日志后应自己删除临时文件。文件位于临时文件目录中，文件名类似original_file_name-#-#。

--hexdump选项可以在注释中产生日志内容的十六进制转储：

上述命令的输出应类似：

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051024 17:24:13 server id 1 end_log_pos 98
# Position Timestamp Type Master ID Size Master Pos Flags
# 00000004 9d fc 5c 43 0f 01 00 00 00 5e 00 00 00 62 00 00 00 00 00
# 00000017 04 00 35 2e 30 2e 31 35 2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
# 00000027 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |og..............|
# 00000037 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
# 00000047 00 00 00 00 9d fc 5c 43 13 38 0d 00 08 00 12 00 |.......C.8......|
# 00000057 04 04 04 04 12 00 00 4b 00 04 1a |.......K...|
# Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
# at startup
ROLLBACK;
十六进制转储的输出包含下面的元素：

· Position: The byte position within the log file.

· Timestamp: The event timestamp. In the example just shown, '9d fc 5c 43' is the representation of '051024 17:24:13' in hexadecimal.

· Type: The type of the log event. '0f' means that the example event is a FORMAT_DESCRIPTION_EVENT. The types are:

· 00 UNKNOWN_EVENT
· This event should never be present in the log.
· 01 START_EVENT_V3
· This indicates the start of a log file written by MySQL 4 or earlier.
· 02 QUERY_EVENT
· The most common type of events. These contain queries executed
· on the master.
· 03 STOP_EVENT
· Indicates that master has stopped.
· 04 ROTATE_EVENT
· Written when the master switches to a new log file.
· 05 INTVAR_EVENT
· Used mainly for AUTO_INCREMENT values and if the LAST_INSERT_ID()
· function is used in the statement.
· 06 LOAD_EVENT
· Used for LOAD DATA INFILE in MySQL 3.23.
· 07 SLAVE_EVENT
· Reserved for future use.
· 08 CREATE_FILE_EVENT
· Used for LOAD DATA INFILE statements. This indicates the start
· of execution of such a statement. A temporary file is created
· on the slave. Used in MySQL 4 only.
· 09 APPEND_BLOCK_EVENT
· Contains data for use in a LOAD DATA INFILE statement. The
· data is stored in the temporary file on the slave.
· 0a EXEC_LOAD_EVENT
· Used for LOAD DATA INFILE statements. The contents of the
· temporary file is stored in the table on the slave.
· Used in MySQL 4 only.
· 0b DELETE_FILE_EVENT
· Rollback of LOAD DATA INFILE statement. The temporary file
· should be deleted on slave.
· 0c NEW_LOAD_EVENT
· Used for LOAD DATA INFILE in MySQL 4 and earlier.
· 0d RAND_EVENT
· Used to send information about random values if the RAND()
· function is used in the query.
· 0e USER_VAR_EVENT
· Used to replicate user variables.
· 0f FORMAT_DESCRIPTION_EVENT
· This indicates the start of a log file written by MySQL 5 or later.
· 10 XID_EVENT
· Event indicating commit of XA transaction
· 11 BEGIN_LOAD_QUERY_EVENT
· Used for LOAD DATA statements in MySQL 5 and later.
· 12 EXECUTE_LOAD_QUERY_EVENT
· Used for LOAD DATA statements in MySQL 5 and later.
· 13 TABLE_MAP_EVENT
· Reserved for future use
· 14 WRITE_ROWS_EVENT
· Reserved for future use
· 15 UPDATE_ROWS_EVENT
· Reserved for future use
· 16 DELETE_ROWS_EVENT
· Reserved for future use
· Master ID: The server id of the master that created the event.

· Size: The size in bytes of the event.

· Master Pos: The position of the event in the original master log file.

· Flags: 16 flags.

· 01 LOG_EVENT_BINLOG_IN_USE_F
· Log file correctly closed (Used only in FORMAT_DESCRIPTION_EVENT)
· If this flag is set (if the flags are e.g. '01 00') in an
· FORMAT_DESCRIPTION_EVENT, then the log file has not been
· properly closed. Most probably because of a master crash (for
· example, due to power failure).
· 02 Reserved for future use.
· 04 LOG_EVENT_THREAD_SPECIFIC_F
· Set if the event is dependent on the connection it was
· executed in (example '04 00'), e.g. if the event uses
· temporary tables.
· 08 LOG_EVENT_SUPPRESS_USE_F
· Set in some circumstances when the event is not dependent on
· the current database
其它标志保留用于将来使用。
在以后的版本中十六进制转储输出的格式可能会改变。

安全删除MySQL二进制日志的3种方法

1、二进制日志
二进制日志记录了所有的DDL（数据定义语言）语句和DML（数据操作语言）语句，但是不记录包括数据查询的语句。语句以“事件”的形式保存，它描述了数据的更改过程，此日志对于灾难时的数据恢复起着极其重要的作用

2、日志的位置和格式
当用—log-bin[=file_name]选项启动时，mysqld将包含所有更新数据的SQL命令写入日志文件。如果没有给出file_name值，默认名为主机名后面跟_bin，如果给出了文件名，但没有包含路劲，则文件默认被写入参数DATADIR（数据目录）指定的目录

3、日志的读取
由于日志以二进制的方式存储，不能直接读取，需要用mysqlbinlog工具来查看，语法如下：
#mysqlbinlog log_file

4、日志的删除
对于比较繁忙的OLTP系统，由于每天生产日志量大，这些日志如果长时间不清理，将会对磁盘空间带来很大的浪费，因此，定期删除日志是DBA维护Mysql数据库的一个重要工作内容，下面将介绍几种删除日志的常见方法

在linux 下,先进入 mysql 如 /usr/local/mysql/bin/mysql -uroot -ppwd
mysql> SHOW MASTER LOGS; //查看所有的二进制日志列表
1.RESET MASTER
可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个新的二进制日志文件
执行“reset master;”命令，该命令将删除所有二进制日志，新日志的编号从“000001” 开始，命令如下
Mysql>reset master;
2.PURGE MASTER LOGS
语法
PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。
例如：
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';
BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。MASTER和BINARY是同义词。
如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。
要清理日志，需按照以下步骤：
1.在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
2.使用SHOW MASTER LOGS获得主服务器上的一系列日志。
3.在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。
4.制作您将要删除的所有日志的备份。（这个步骤是自选的，但是建议采用。）
5.清理所有的日志，但是不包括目标日志。
 
3. expire_logs_days参数
二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除
expire_logs_days numeric GLOBAL
 
在my.cnf配置文件【mysqld】段中加入，重启MySQL服务，例：
expire_logs_days＝3
删除3天以前的日志
 
注：当然需要考虑有slave情况下的影响
（2）：执行“Purge master logs to ‘mysql-bin.*****’” 命令，该命令将删除“*****” 编号之前的所有日志，下列中删除了“mysql-bin.000001”之前编号的所有日志
Mysql>purge master logs to ‘mysql-bin.000015;
从结果中发现，编号000015之前的所有日志都已经删除
（3）：执行“purge master logs before ‘yyyy-mm-dd hh24:min:ss’”命令，该命令将删除日期为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志，下列中删除了日期在“2010-05-22 01:00:00”之前的所有日志
mysql>purge master logs before ‘2010-05-22 01:00:00’’;
（4）：设置参数—expire_logs_days=#(days)，此参数的含义是设置日志的过期天数，过来指定的天数后日志将会被自动删除，这样将有利于减少DBA管理日志的工作量。
 
#vi /etc/my.cnf
[mysqld]
expire_logs_days=3
 
这样，3天前的日志都会被删除，系统自动删除

设置自动清理mysql binlog日志和手动删除的方法

涉及参数 
expire_logs_days （官方参数说明）
二进制日志自动删除的天数。默认值为0,表示”没有自动删除“。启动时和二进制日志循环时可能删除。
 
1. 设置自动清理mysql binlog日志，配置my.cnf：

expire_logs_days = 10 #10为天数 

2. 在运行时修改：（不重启mysql ，热设置）
1) show binary logs; #查看现有的二进制文件
2) show variables like '%log%'; #查看当前全局变量里的log设置
3) set global expire_logs_days = 10; #设置自动保留几天的二进制文件
4) flush logs #重新生成二进制文件,就会把10天前的删除掉
 
3. 手动删除10天前的mysql binlog日志：
1) PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
2) show master logs;
 
手动删除全部mysql binlog日志保留当前的二进制：
1.show master status;
2.purge master logs to 'mysql-bin.000003'; #只保留'mysql-bin.000003'文件
MASTER和BINARY是同义词。