一mysqlbinlog 处理mysql二进制日志文件的工具
mysqlbinlog读取配置文件[mysqlbinlog][client]组
mysqlbinlog [options] log_file...
# at 141 //event在日志文件的偏移或开始位置 #100309 9:28:36 server id 123 end_log_pos 245 Query thread_id=3350 exec_time=11 error_code=0
event在服务器发生的日期和时间,当主从复制的时候,时间戳传播到slave服务器
server id是服务器ID
end_log_pos是下一个event的开始位置,当前event的结束位置+1
thread_id是执行event的线程ID
exec_time在主服务器是执行event的时间,从服务器是执行结束时间-主服务器执行开始时间
error_code是event执行结果
--base64-output // 何时event应该作为base64字符串编码显示
AUTO,UNSPEC //在需要的时候显示,格式化描述事件或行事件
NEVER //从不显示
DECODE-ROWS //行事件被解码,作为注释的sql语句显示
--binlog-row-event-max-size //以row为基础的二进制日志文件的最大size
Type (64-bit platforms) | numeric |
Default (64-bit platforms) | 4294967040 |
Minimum (64-bit platforms) | 256 |
Maximum (64-bit platforms) | 18446744073709547520 |
-d //--database
以statement为基础时,输出数据库用use语句被选作默认数据库后的二进制日志
以row为基础时,输出和选择数据库相关的语句,和当前默认数据库无关
-D //--disable-log-bin 无效二进制日志
--exclude-gtids //不显示gtid_set里面列出的任何组
-F //--force-if-open 即使文件是打开或没有正确关闭也读取
-f //--forec-read 当遇到不认识的event的时候,打印警告消息,跳过这个event,继续读下一个
-H //--hexdump 在注释里显示16进制格式的日志
--idempotent //告诉服务器使用idempotent模式,当更新遇到错误时忽略
--include-gtids //显示仅列在gtid_set里面的任何组
-l //--local-load 在指定目录为了LOAD DATA INFILE准备临时文件
-o //--offset 跳过日志的前N个入口
--raw //用二进制日志原始的二进制格式写日志,需要 --read-from-remote-server选项一起使用
--read-from-remote-master //BINLOG-DUMP-NON-GTIDS
or BINLOG-DUMP-GTIDS,
--read-from-remote-master=BINLOG-DUMP-GTIDS
配合 --exclude-gtids一起使用,可以过滤出master
-R //--read-from-remote-server 从远端mysql服务器读取二进制日志,类似--read-from-remote-master=BINLOG-DUMP-NON-GTIDS
.
-r //--result-file 如果没有--raw,文本输出的文件名,如果有--raw,二进制输出文件名的前缀
--rewrite-db='from_name'->'to_name'
--server-id //仅显示创建这些event的服务器ID
--server-id-bits //仅使用服务器ID的前N位标识服务器
-s //--short-form 仅显示日志中的语句,不包含额外信息或row基础的event
--skip-gtids //不输出任何gtids
--start-datetime //读这个时间及以后的日志
-j //--start-position 读这个位置及以后的日志
--stop-datetime //停止都这个时间及以后的日志
--stop-never //请求的log传输完成后不停止,持续读新的event,和--raw一起使用做实时的备份,不和--raw一起使用做持续的日志显示
--stop-never-slave-server-id //显示指示serverID
--stop-position //停止读这个位置及以后的日志
-t //--to-last-log 不停止请求日志,直到最后一个日志完成
-v //--verbose 重构行事件,显示作为注释的sql语句,-vv会输出数据类型和列元数据
-c //--verify-binlog-checksum 验证日志的checksum
也可以指定下面的变量--var_name = value
open-files-limit 打开文件描述符的数量限制
mysqlbinlog binlog.000001 | mysql -u root -p
mysqlbinlog binlog.[0-9]* | mysql -u root -p
如果包含BLOB值,使用--binary-mode
mysqlbinlog binlog.000001 > tmpfile
mysql -u root -p < tmpfile
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
mysqlbinlog binlog.000001 > /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"
mysqlbinlog --hexdump master-bin.000001 //16进制输入
Position //日志文件的字节位置
Timestamp //事件时间戳
Type //事件类型
Type | Name | Meaning |
---|---|---|
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 statements 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 for AUTO_INCREMENT values or when theLAST_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 a LOAD DATA INFILE statement. The temporary file should be deleted on the 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 statement. |
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 an XA transaction. |
11 | BEGIN_LOAD_QUERY_EVENT | Used for LOAD DATA INFILE statements in MySQL 5 and later. |
12 | EXECUTE_LOAD_QUERY_EVENT | Used for LOAD DATA INFILE statements in MySQL 5 and later. |
13 | TABLE_MAP_EVENT | Information about a table definition. Used in MySQL 5.1.5 and later. |
14 | PRE_GA_WRITE_ROWS_EVENT | Row data for a single table that should be created. Used in MySQL 5.1.5 to 5.1.17. |
15 | PRE_GA_UPDATE_ROWS_EVENT | Row data for a single table that needs to be updated. Used in MySQL 5.1.5 to 5.1.17. |
16 | PRE_GA_DELETE_ROWS_EVENT | Row data for a single table that should be deleted. Used in MySQL 5.1.5 to 5.1.17. |
17 | WRITE_ROWS_EVENT | Row data for a single table that should be created. Used in MySQL 5.1.18 and later. |
18 | UPDATE_ROWS_EVENT | Row data for a single table that needs to be updated. Used in MySQL 5.1.18 and later. |
19 | DELETE_ROWS_EVENT | Row data for a single table that should be deleted. Used in MySQL 5.1.18 and later. |
1a | INCIDENT_EVENT | Something out of the ordinary happened. Added in MySQL 5.1.18. |
Master ID //产生这个事件的主服务器ID
Size //事件的字节size
Master Pos //下一个事件的位置
Flag //标识
Flag | Name | Meaning |
---|---|---|
01 | LOG_EVENT_BINLOG_IN_USE_F | Log file correctly closed. (Used only inFORMAT_DESCRIPTION_EVENT .) If this flag is set (if the flags are, for example, '01 00' ) in aFORMAT_DESCRIPTION_EVENT , the log file has not been properly closed. Most probably this is 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 (for example, '04 00' ), for example, if the event uses temporary tables. |
08 | LOG_EVENT_SUPPRESS_USE_F | Set in some circumstances when the event is not dependent on the default database. |
SHOW BINARY LOGS;
mysqlbinlog --read-from-remote-server --host=host_name
--raw binlog.000130 binlog.000131 binlog.000132 mysqlbinlog --read-from-remote-server --host=host_name
--raw --to-last-log binlog.000130
mysqlbinlog --read-from-remote-server --host=host_name
--raw --stop-never binlog.000130--result-file
OptionOutput File Names--result-file=x
xbinlog.000999
and up--result-file=/tmp/
/tmp/binlog.000999
and up--result-file=/tmp/x
/tmp/xbinlog.000999
and up
mysqlbinlog --read-from-remote-server --host=host_name
--raw
--stop-never binlog.000999 //持续备份
mysqldump --host=host_name
--all-databases --events --routines --master-data=2>dump_file //保存数据
mysql --host=host_name
-u root -p <dump_file //恢复数据
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001002', MASTER_LOG_POS=27284; //dump时候的二进制文件记录位置
mysqlbinlog --start-position=27284 binlog.001002 binlog.001003 binlog.001004 | mysql --host=host_name
-u root -p //恢复dump后产生的数据
二mysqldumpshow显示慢查询日志
mysqldumpshow [options] [log_file...]
Format | Description |
---|---|
-a | Do not abstract all numbers to N and strings to S |
-n | Abstract numbers with at least the specified digits |
--debug -d | Write debugging information |
-g | Only consider statements that match the pattern |
--help | Display help message and exit |
-h | Host name of the server in the log file name |
-i | Name of the server instance |
-l | Do not subtract lock time from total time |
-r | Reverse the sort order |
-s | How to sort output |
-t | Display only first num queries |
--verbose | Verbose mode |
-
t
,at
: Sort by query time or average query time -
l
,al
: Sort by lock time or average lock time -
r
,ar
: Sort by rows sent or average rows sent -
c
: Sort by count