mysql日志

一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 //事件类型
TypeNameMeaning
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 //标识
FlagNameMeaning
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=xxbinlog.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...]
FormatDescription
-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





posted @ 2018-04-12 23:36  shymen  阅读(175)  评论(0)  编辑  收藏  举报