Linux下MySQL数据库二进制日志恢复方法

Linux下MySQL数据库二进制日志恢复方法

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

SHOW BINLOG EVENTS \G


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

mysql ——user=root -pmy_pwd -e 'SHOW BINLOG EVENTS \G'


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

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd


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

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \


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

mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" \
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql


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

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \


上面的第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:

shell> mysqlbinlog [options] log-files...


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

shell> mysqlbinlog binlog.0000003


输出包括在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数据类型。例如:

shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003


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

·         –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节,“数据库备份”):

shell> mysqlbinlog hostname-bin.000001 | mysql


或:

shell> mysqlbinlog hostname-bin.[0-9]* | mysql


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

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

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

shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!


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

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

shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql


另一个方法是:

shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql
shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql
shell> mysql -e "source /tmp/statements.sql"


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选项可以在注释中产生日志内容的十六进制转储:

shell> mysqlbinlog --hexdump master-bin.000001


上述命令的输出应类似:

/*!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

其它标志保留用于将来使用。
在以后的版本中十六进制转储输出的格式可能会改变。

发表回复