mysql工具使用意义_细聊MySQL之常用工具及基本操作（完）

十、binlog查看工具mysqlbinlog的使用。

在讲解mysqlbinlog时，我先提供mysqlbinlog的相关参数给大家熟悉下：参数描述适用弃用--base64-output=value该选项指定BINLOG如何被显示，有以下几个值供选择。AUTO，自动显示BINLOG声明NEVER，不显示BINLOG声明，如果有一个使用BINLOG显示的行事件，产生错误并退出。DECODE-ROWS，指定解码显示行事件

--bind-address=ip_address同mysql命令

--binlog-row-event-max-size=#二进制日志记录的最大事件尺寸

--character-sets-dir=path同mysql命令

--database=db_name只显示指定的数据库条目

--debug[=debug_options]同mysql命令

--debug-check同mysql命令

--debug-info同mysql命令

--defaults-extra-file=file_name同mysql命令

--defaults-file=file_name同mysql命令

--defaults-group-suffix=str同mysql命令

--disable-log-bin关闭binlog

--force-read强制读二进制文件，不管该文件是否打开或关闭是否正常

--help同mysql命令

--hexdump使用16进制显示binlog

--host=host_name同mysql命令

--idempotent告诉服务器使用幂等模式使用更新5.7.0

--local-load=path在指定的目录中为LOAD DATA INFILE准备临时文件

--no-defaults同mysql命令

--offset=#设置读取binlog的偏移量

--password[=password]同mysql命令

--plugin-dir=path同mysql命令

--port=port_num同mysql命令

--print-defaults同mysql命令

--protocol=type同mysql命令

--raw写二进制格式到输出文件

--read-from-remote-master=type从主MYSQL服务器读二进制文件，而不是从本地。该选项有 BINLOG-DUMP-NON-GTIDS 和 BINLOG-DUMP-GTIDS两种。GTIDS功能后续会有介绍

--read-from-remote-server从服务器读二进制文件

--result-file=name将结果直接输出到文件

--server-id=id提取被指定server id创建的事件

--set-charset=charset_name同mysql命令

--shared-memory-base-name=name同mysql命令

--short-form仅仅显示包含在log里的声明语句

--skip-gtids[=true|false]不打印任何GTID

--socket=path同mysql命令

--ssl同mysql命令5.7.3

--ssl-ca=file_name同mysql命令5.7.3

--ssl-capath=dir_name同mysql命令5.7.3

--ssl-cert=file_name同mysql命令5.7.3

--ssl-cipher=cipher_list同mysql命令5.7.3

--ssl-crl=file_name同mysql命令

--ssl-crlpath=dir_name同mysql命令

--ssl-key=file_name同mysql命令5.7.3

--ssl-verify-server-cert同mysql命令5.7.3

--start-datetime=datetime从指定的时间戳开始读binlog

--start-position=#从指定的位置开始读binlog

--stop-datetime=datetime读binlog直到指定的时间戳为止

--stop-never读取完binlog后保持与服务器的连接

--stop-never-slave-server-id=#连接服务器时报告从服务器的ID

--stop-position=#读binlog直到指定的位置为止

--to-last-log该选项需要 --read-from-remote-server选项，一直读取知道最新的binlog的结尾

--user=user_name,同mysql命令

--verbose用SQL语句显示行事件

--verify-binlog-checksum验证binlog内的校验和

--version同mysql命令

要查看binlog，首先得有。如果服务器用默认的启动方式，mysql将不会生成binlog。所以在mysqld启动时，必须加上--log-bin参数。binlog文件通常在数据文件目录内，如/usr/local/mysql/data内，以xxx-bin.0000xx的形式命名。为了适用mysqlbinlog查看binlog，首先我们在test库执行一段SQL：

CREATE TABLE t ( id INT NOT NULL, name VARCHAR(20) NOT NULL, date DATE NULL ) ENGINE = InnoDB;

START TRANSACTION;

INSERT INTO t VALUES(1, 'apple', NULL);

UPDATE t SET name = 'pear', date = '2009-01-01' WHERE id = 1;

DELETE FROM t WHERE id = 1;

COMMIT;

以上步骤概括了创建表、插入数据、更新数据、删除数据的操作，并且是在一个事务内完成。

1、mysqlbinlog 16进制格式查看，语法如下：

shell>mysqlbinlog --hexdump master-bin.000001

输出大致如下：

#141203 0:03:50 server id 1 end_log_pos 300 CRC32 0xda824ff6

# Position Timestamp Type Master ID Size Master Pos Flags

# 78 e6 c3 7e 54 02 01 00 00 00 b4 00 00 00 2c 01 00 00 00 00

# 8b 04 00 00 00 00 00 00 00 04 00 00 21 00 00 00 00 |................|

# 9b 00 00 01 00 00 20 40 00 00 00 00 06 03 73 74 64 |.............std|

# ab 04 21 00 21 00 08 00 0c 01 74 65 73 74 00 74 65 |.........test.te|

# bb 73 74 00 43 52 45 41 54 45 20 54 41 42 4c 45 20 |st.CREATE.TABLE.|

# cb 74 0d 0a 28 0d 0a 20 20 69 64 20 20 20 49 4e 54 |t.......id...INT|

# db 20 4e 4f 54 20 4e 55 4c 4c 2c 0d 0a 20 20 6e 61 |.NOT.NULL.....na|

# eb 6d 65 20 56 41 52 43 48 41 52 28 32 30 29 20 4e |me.VARCHAR.20..N|

# fb 4f 54 20 4e 55 4c 4c 2c 0d 0a 20 20 64 61 74 65 |OT.NULL.....date|

# 10b 20 44 41 54 45 20 4e 55 4c 4c 0d 0a 29 20 45 4e |.DATE.NULL....EN|

# 11b 47 49 4e 45 20 3d 20 49 6e 6e 6f 44 42 f6 4f 82 |GINE...InnoDB.O.|

# 12b da |.|

# Query thread_id=4 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1417593830/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE TABLE t^M

(^M

id INT NOT NULL,^M

name VARCHAR(20) NOT NULL,^M

date DATE NULL^M

) ENGINE = InnoDB

/*!*/;

这里只截取了创建表t的这一段log。可以看到，结果中有Position Timestamp Type Master ID Size Master Pos Flags这几列。下面说明下这几列的意义。

Position: LOG文件的字节位置

Timestamp: 事件发生的时间戳

Type: 事件类型(以下表格是所有的事件类型)

类型名称意义00UNKNOWN_EVENT未知事件

01START_EVENT_V3This indicates the start of a log file written by MySQL 4 or earlier.

02QUERY_EVENTThe most common type of events. These contain statements executed on the master.

03STOP_EVENTIndicates that master has stopped.

04ROTATE_EVENTWritten when the master switches to a new log file.

05INTVAR_EVENTUsed forAUTO_INCREMENTvalues or when theLAST_INSERT_ID()function is used in the statement.

06LOAD_EVENTUsed forLOAD DATA INFILEin MySQL 3.23.

07SLAVE_EVENTReserved for future use.

08CREATE_FILE_EVENTUsed forLOAD DATA INFILEstatements. This indicates the start of execution of such a statement. A temporary file is created on the slave. Used in MySQL 4 only.

09APPEND_BLOCK_EVENTContains data for use in aLOAD DATA INFILEstatement. The data is stored in the temporary file on the slave.

0aEXEC_LOAD_EVENTUsed forLOAD DATA INFILEstatements. The contents of the temporary file is stored in the table on the slave. Used in MySQL 4 only.

0bDELETE_FILE_EVENTRollback of aLOAD DATA INFILEstatement. The temporary file should be deleted on the slave.

0cNEW_LOAD_EVENTUsed forLOAD DATA INFILEin MySQL 4 and earlier.

0dRAND_EVENTUsed to send information about random values if theRAND()function is used in the statement.

0eUSER_VAR_EVENTUsed to replicate user variables.

0fFORMAT_DESCRIPTION_EVENTMYSQL5及以上版本log文件开始

10XID_EVENTEvent indicating commit of an XA transaction.

11BEGIN_LOAD_QUERY_EVENTUsed forLOAD DATA INFILEstatements in MySQL 5 and later.

12EXECUTE_LOAD_QUERY_EVENTUsed forLOAD DATA INFILEstatements in MySQL 5 and later.

13TABLE_MAP_EVENTInformation about a table definition. Used in MySQL 5.1.5 and later.

14PRE_GA_WRITE_ROWS_EVENTRow data for a single table that should be created. Used in MySQL 5.1.5 to 5.1.17.

15PRE_GA_UPDATE_ROWS_EVENTRow data for a single table that needs to be updated. Used in MySQL 5.1.5 to 5.1.17.

16PRE_GA_DELETE_ROWS_EVENTRow data for a single table that should be deleted. Used in MySQL 5.1.5 to 5.1.17.

17WRITE_ROWS_EVENT写事件

18UPDATE_ROWS_EVENT更新事件

19DELETE_ROWS_EVENT删除事件

1aINCIDENT_EVENTSomething out of the ordinary happened. Added in MySQL 5.1.18.

Master ID: 创建事件的服务ID

Size: 事件的字节大小

Master Pos: 原始日志文件下一个事件的位置

Flags: 标记FlagNameMeaning01LOG_EVENT_BINLOG_IN_USE_FLog 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).

02Reserved for future use.

04LOG_EVENT_THREAD_SPECIFIC_FSet if the event is dependent on the connection it was executed in (for example,'04 00'), for example, if the event uses temporary tables.

08LOG_EVENT_SUPPRESS_USE_FSet in some circumstances when the event is not dependent on

这里我就没翻译了，毕竟英语也不是太好，怕有差错。

2、事件的显示

shell>mysqlbinlog log_file

结果如下所示：

# at 379

#141203 0:03:50 server id 1 end_log_pos 491 CRC32 0xab45c565 Query     thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1417593830/*!*/;

INSERT INTO t VALUES(1, 'apple', NULL)

/*!*/;

at代表log文件内字节位置

CRC32 0xab45c565 代表校验和

Query     thread_id=4 代表执行的线程ID

end_log_pos 491 代表结束的字节位置

error_code=0 代表错误码

141203 可能代表事务标识，未验证

注释下面是具体的SQL操作记录

3、使用mysqlbinlog备份二进制文件

mysqlbinlog工具可以读取并显示二进制文件的内容，这种能力可以用来备份。mysqlbinlog能进行静态备份或者不间断的连续备份。mysqlbinlog能够一直运行知道服务器关闭，或者mysqlbinlog强制退出。当连接关闭时，mysqlbinlog不会等待或者重连，如果需要继续进行备份，必须重新启动mysqlbinlog。

二进制日志的备份需要调用mysqlbinlog的至少两个选项：

--read-from-remote-server(or-R) 选项告诉mysqlbinlog连接到一个服务器和请求binlog

--rawoption 告诉mysqlbinlog写二进制输出而不是文本输出

在使用--read-from-remote-server选项时，通常要搭配--user、--host、--password等一起使用。

一些其它的选项也是比较有用的，与--raw一起搭配使用，如

--stop-never：一直保持连接

--stop-never-slave-server-id=id：当--stop-never开启时报告server id

--result-file：指定输出的文件名

下面用例子来描述下：

mysqlbinlog --read-from-remote-server --host=host_name --raw --to-last-log --result-file=xxx binlog.000130

此为静态备份，一次性备份binlog到xxx文件

mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never --result-file=xxx binlog.000130

加了--stop-never后为连续备份，直到服务器shutdown或强制退出mysqlbinlog

在运行此命令前需注意两点：

1、检查binlog.index内的路径是否正确，否则会出现找不到二进制日志的错误。

2、保证binlog文件没有异常，否则会出现备份不了的情况。

3、mysqld运行时必须指定server id。

数据库的常用工具与基本操作就介绍到这里。后续将会有一些更深入的研究和见解与大家分享。