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 //事件类型

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=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...]

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

转载于:https://www.cnblogs.com/shymen/p/8748499.html