mysqlbinlog的使用

二进制日志文件包含了修改数据库内容的信息，这些内容都是二进制的格式，要想按文本的方式显示，需要使用mysqlbinlog工具，可以查看中继日志的内容，他们的格式都是一样的，使用命令的方式如下：

shell> mysqlbinlog [options] log_file ...

对于基于语句的复制，事件包含sql语句，语句执行的server的id，语句执行的开始时间，执行时长等，对于基于行的复制，事件包含行的改变而不是sql语句。

显示的信息可能是这样的

# at 141
#100309  9:28:36 server id 123  end_log_pos 245
  Query thread_id=3350  exec_time=11  error_code=0

at表示文件中的偏移量，或事件在二进制文件中的开始的位置。

end_log_pos代表下个事件开始的位置，exec_time在master上代表执行事件花费的时间，在slave上，是slave上执行结束的时间减去master上执行的开始时间，代表落后master多久。

mysqlbinlog的输出可以重新执行来重做操作，对于恢复是有帮助的，

通常情况，可以使用mysqlbinlog直接读取二进制日志文件并将它们用于本地MySQL服务器。也可以使用–read-from-remote-server选项从远程服务器读取二进制日志。
当读取远程二进制日志时，可以通过连接参数选项来指示如何连接服务器，但它们经常被忽略掉，除非你还指定了–read-from-remote-server选项。这些选项是–host、–password、–port、–protocol、–socket和–user。选项有下面这些

Format	Description	Introduced
--base64-output	Print binary log entries using base-64 encoding
--bind-address	Use specified network interface to connect to MySQL Server	5.6.1
--binlog-row-event-max-size	Binary log max event size
--character-sets-dir	Directory where character sets are installed
--connection-server-id	Used for testing and debugging. See text for applicable default values and other particulars.	5.6.20
--database	List entries for just this database
--debug	Write debugging log
--debug-check	Print debugging information when program exits
--debug-info	Print debugging information, memory, and CPU statistics when program exits
--default-auth	Authentication plugin to use	5.6.2
--defaults-extra-file	Read named option file in addition to usual option files
--defaults-file	Read only named option file
--defaults-group-suffix	Option group suffix value
--disable-log-bin	Disable binary logging
--exclude-gtids	Do not show any of the groups in the GTID set provided	5.6.5
--force-if-open	Read binary log files even if open or not closed properly
--force-read	If mysqlbinlog reads a binary log event that it does not recognize, it prints a warning
--help	Display help message and exit
--hexdump	Display a hex dump of the log in comments
--host	Connect to MySQL server on given host
--idempotent	Cause the server to use idempotent mode while processing binary log updates from this session only
--include-gtids	Show only the groups in the GTID set provided	5.6.5
--local-load	Prepare local temporary files for LOAD DATA INFILE in the specified directory
--login-path	Read login path options from .mylogin.cnf	5.6.6
--no-defaults	Read no option files
--offset	Skip the first N entries in the log
--password	Password to use when connecting to server
--plugin-dir	Directory where plugins are installed	5.6.2
--port	TCP/IP port number to use for connection
--print-defaults	Print default options
--protocol	Connection protocol to use
--raw	Write events in raw (binary) format to output files
--read-from-remote-master	Read the binary log from a MySQL master rather than reading a local log file	5.6.5
--read-from-remote-server	Read binary log from MySQL server rather than local log file
--result-file	Direct output to named file
--secure-auth	Do not send passwords to server in old (pre-4.1) format	5.6.17
--server-id	Extract only those events created by the server having the given server ID
--server-id-bits	Tell mysqlbinlog how to interpret server IDs in binary log when log was written by a mysqld having its server-id-bits set to less than the maximum; supported only by MySQL Cluster version of mysqlbinlog
--set-charset	Add a SET NAMES charset_name statement to the output
--shared-memory-base-name	The name of shared memory to use for shared-memory connections
--short-form	Display only the statements contained in the log
--skip-gtids	Do not print any GTIDs; use this when writing a dump file from binary logs containing GTIDs.	5.6.5
--socket	For connections to localhost, the Unix socket file to use
--ssl-crl	Path of file that contains certificate revocation lists	5.6.3
--ssl-crlpath	Path of directory that contains certificate revocation list files	5.6.3
--start-datetime	Read binary log from first event with timestamp equal to or later than datetime argument
--start-position	Read binary log from first event with position equal to or greater than argument
--stop-datetime	Stop reading binary log at first event with timestamp equal to or greater than datetime argument
--stop-never	Stay connected to server after reading last binary log file
--stop-never-slave-server-id	Slave server ID to report when connecting to server
--stop-position	Stop reading binary log at first event with position equal to or greater than argument
--to-last-log	Do not stop at the end of requested binary log from a MySQL server, but rather continue printing to end of last binary log
--user	MySQL user name to use when connecting to server
--verbose	Reconstruct row events as SQL statements
--verify-binlog-checksum	Verify checksums in binary log	5.6.1
--version	Display version information and exit

基于语句的复制的使用：

mysqlbinlog --short-form --force-if-open --base64-output=never mysql-bin.000003 > output4.txt

读取远程机器上的日志

mysqlbinlog --read-from-remote-server --host=127.0.0.1 --base64-output=never --user=rep_user --password=xxx --start-position=434 file_name

读取远程机器的二进制日志，只做为备份

mysqlbinlog --raw --read-from-remote-server --host=127.0.0.1 --user=rep_user file1 file2 file3  可以使用--to-last-log把指定文件之后所有的文件传输过来