MySQL之常用工具及基本操作（完）

4tr32

十、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 for AUTO_INCREMENT values or when the LAST_INSERT_ID() function is used in the statement.
06LOAD_EVENTUsed for LOAD DATA INFILE in MySQL 3.23.
07SLAVE_EVENTReserved for future use.
08CREATE_FILE_EVENTUsed 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.
09APPEND_BLOCK_EVENTContains data for use in a LOAD DATA INFILE statement. The data is stored in the temporary file on the slave.
0aEXEC_LOAD_EVENTUsed for LOAD DATA INFILE statements. The contents of the temporary file is stored in the table on the slave. Used in MySQL 4 only.
0bDELETE_FILE_EVENTRollback of a LOAD DATA INFILE statement. The temporary file should be deleted on the slave.
0cNEW_LOAD_EVENTUsed for LOAD DATA INFILE in MySQL 4 and earlier.
0dRAND_EVENTUsed to send information about random values if the RAND() 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 for LOAD DATA INFILE statements in MySQL 5 and later.
12EXECUTE_LOAD_QUERY_EVENTUsed for LOAD DATA INFILE statements 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: 标记

FlagNameMeaning
01LOG_EVENT_BINLOG_IN_USE_FLog file correctly closed. (Used only in FORMAT_DESCRIPTION_EVENT.) If this flag is set (if the flags are, for example, '01 00') in a FORMAT_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.
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

    --raw option 告诉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。

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