二进制日志文件包含了修改数据库内容的信息,这些内容都是二进制的格式,要想按文本的方式显示,需要使用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把指定文件之后所有的文件传输过来