1MYSQLBINLOG(1) MySQL Database System MYSQLBINLOG(1)
2
6 mysqlbinlog - utility for processing binary log files
7
9 mysqlbinlog [options] log_file ...
10
12 The server's binary log consists of files containing “events” that
13 describe modifications to database contents. The server writes these
14 files in binary format. To display their contents in text format, use
15 the mysqlbinlog utility. You can also use mysqlbinlog to display the
16 contents of relay log files written by a slave server in a replication
17 setup because relay logs have the same format as binary logs. The
18 binary log and relay log are discussed further in Section 5.2.4, “The
19 Binary Log”, and Section 16.2.2, “Replication Relay and Status Logs”.
20 Invoke mysqlbinlog like this:
22 shell> mysqlbinlog [options] log_file ...
24 For example, to display the contents of the binary log file named
26 binlog.000003, use this command:
27 shell> mysqlbinlog binlog.0000003
29 The output includes events contained in binlog.000003. For
31 statement-based logging, event information includes the SQL statement,
32 the ID of the server on which it was executed, the timestamp when the
33 statement was executed, how much time it took, and so forth. For
34 row-based logging, the event indicates a row change rather than an SQL
35 statement. See Section 16.1.2, “Replication Formats”, for information
36 about logging modes.
37 Events are preceded by header comments that provide additional
39 information. For example:
40 # at 141
42 #100309 9:28:36 server id 123 end_log_pos 245
43 Query thread_id=3350 exec_time=11 error_code=0
44 In the first line, the number following at indicates the starting
46 position of the event in the binary log file.
47 The second line starts with a date and time indicating when the
49 statement started on the server where the event originated. For
50 replication, this timestamp is propagated to slave servers. server id
51 is the server_id value of the server where the event originated.
52 end_log_pos indicates where the next event starts (that is, it is the
53 end position of the current event + 1). thread_id indicates which
54 thread executed the event. exec_time is the time spent executing the
55 event, on a master server. On a slave, it is the difference of the end
56 execution time on the slave minus the beginning execution time on the
57 master. The difference serves as an indicator of how much replication
58 lags behind the master. error_code indicates the result from executing
59 the event. Zero means that no error occurred.
60 The output from mysqlbinlog can be re-executed (for example, by using
62 it as input to mysql) to redo the statements in the log. This is useful
63 for recovery operations after a server crash. For other usage examples,
64 see the discussion later in this section and in Section 7.5, “Point-in-
65 Time (Incremental) Recovery Using the Binary Log”.
66 Normally, you use mysqlbinlog to read binary log files directly and
68 apply them to the local MySQL server. It is also possible to read
69 binary logs from a remote server by using the --read-from-remote-server
70 option. To read remote binary logs, the connection parameter options
71 can be given to indicate how to connect to the server. These options
72 are --host, --password, --port, --protocol, --socket, and --user; they
73 are ignored except when you also use the --read-from-remote-server
74 option.
75 mysqlbinlog supports the following options, which can be specified on
77 the command line or in the [mysqlbinlog] and [client] groups of an
78 option file. mysqlbinlog also supports the options for processing
79 option files described at Section 4.2.3.4, “Command-Line Options that
80 Affect Option-File Handling”.
81 · --help, -?
83 Display a help message and exit.
85 · --base64-output[=value]
87 This option determines when events should be displayed encoded as
89 base-64 strings using BINLOG statements. The option has these
90 permissible values (not case sensitive):
91 · AUTO ("automatic") or UNSPEC ("unspecified") displays BINLOG
93 statements automatically when necessary (that is, for format
94 description events and row events). If no --base64-output
95 option is given, the effect is the same as
96 --base64-output=AUTO.
97 Note
99 Automatic BINLOG display is the only safe behavior if you
100 intend to use the output of mysqlbinlog to re-execute
101 binary log file contents. The other option values are
102 intended only for debugging or testing purposes because
103 they may produce output that does not include all events in
104 executable form.
105 · ALWAYS displays BINLOG statements whenever possible. If the
107 --base64-output option is given without a value, the effect is
108 the same as --base64-output=ALWAYS.
109 · NEVER causes BINLOG statements not to be displayed.
111 mysqlbinlog exits with an error if a row event is found that
112 must be displayed using BINLOG.
113 · DECODE-ROWS specifies to mysqlbinlog that you intend for row
115 events to be decoded and displayed as commented SQL statements
116 by also specifying the --verbose option. Like NEVER,
117 DECODE-ROWS suppresses display of BINLOG statements, but unlike
118 NEVER, it does not exit with an error if a row event is found.
119 The --base64-output option was introduced in MySQL 5.1.5, to be
121 given as --base64-output or --skip-base64-output (with the sense of
122 AUTO or NEVER). The option values described in the preceding list
123 may be used as of MySQL 5.1.24, with the exception of UNSPEC and
124 DECODE-ROWS, which are available as of MySQL 5.1.28.
125 For examples that show the effect of --base64-output and --verbose
127 on row event output, see the section called “MYSQLBINLOG ROW EVENT
128 DISPLAY”.
129 · --bind-address=ip_address
131 On a computer having multiple network interfaces, this option can
133 be used to select which interface is employed when connecting to
134 the MySQL server.
135 This option is supported only in the version of mysqlbinlog that is
137 supplied with MySQL Cluster, beginning with MySQL Cluster NDB
138 6.3.4. It is not available in standard MySQL 5.1 releases.
139 · --character-sets-dir=path
141 The directory where character sets are installed. See Section 10.5,
143 “Character Set Configuration”.
144 · --database=db_name, -d db_name
146 This option causes mysqlbinlog to output entries from the binary
148 log (local log only) that occur while db_name is been selected as
149 the default database by USE.
150 The --database option for mysqlbinlog is similar to the
152 --binlog-do-db option for mysqld, but can be used to specify only
153 one database. If --database is given multiple times, only the last
154 instance is used.
155 The effects of this option depend on whether the statement-based or
157 row-based logging format is in use, in the same way that the
158 effects of --binlog-do-db depend on whether statement-based or
159 row-based logging is in use.
160 Statement-based logging. The --database option works as follows:
162 · While db_name is the default database, statements are output
164 whether they modify tables in db_name or a different database.
165 · Unless db_name is selected as the default database, statements
167 are not output, even if they modify tables in db_name.
168 · There is an exception for CREATE DATABASE, ALTER DATABASE, and
170 DROP DATABASE. The database being created, altered, or dropped
171 is considered to be the default database when determining
172 whether to output the statement.
173 Suppose that the binary log was created by executing these
175 statements using statement-based-logging:
176 INSERT INTO test.t1 (i) VALUES(100);
178 INSERT INTO db2.t2 (j) VALUES(200);
179 USE test;
180 INSERT INTO test.t1 (i) VALUES(101);
181 INSERT INTO t1 (i) VALUES(102);
182 INSERT INTO db2.t2 (j) VALUES(201);
183 USE db2;
184 INSERT INTO test.t1 (i) VALUES(103);
185 INSERT INTO db2.t2 (j) VALUES(202);
186 INSERT INTO t2 (j) VALUES(203);
187 mysqlbinlog --database=test does not output the first two INSERT
189 statements because there is no default database. It outputs the
190 three INSERT statements following USE test, but not the three
191 INSERT statements following USE db2.
192 mysqlbinlog --database=db2 does not output the first two INSERT
194 statements because there is no default database. It does not output
195 the three INSERT statements following USE test, but does output the
196 three INSERT statements following USE db2.
197 Row-based logging. mysqlbinlog outputs only entries that change
199 tables belonging to db_name. The default database has no effect on
200 this. Suppose that the binary log just described was created using
201 row-based logging rather than statement-based logging. mysqlbinlog
202 --database=test outputs only those entries that modify t1 in the
203 test database, regardless of whether USE was issued or what the
204 default database is. If a server is running with binlog_format set
205 to MIXED and you want it to be possible to use mysqlbinlog with the
206 --database option, you must ensure that tables that are modified
207 are in the database selected by USE. (In particular, no
208 cross-database updates should be used.)
209 This option did not work correctly for mysqlbinlog with row-based
211 logging prior to MySQL 5.1.37. (Bug #42941)
212 Note
214 Prior to MySQL Cluster NDB 7.0.28 and MySQL Cluster NDB 7.1.17,
215 this option did not work correctly with MySQL Cluster tables
216 unless, unless the binary log was generated using
217 --log-bin-use-v1-row-events=0. (Bug #13067813)
218 · --debug[=debug_options], -# [debug_options]
220 Write a debugging log. A typical debug_options string is
222 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysqlbinlog.trace'.
223 · --debug-check
225 Print some debugging information when the program exits. This
227 option was added in MySQL 5.1.21.
228 · --debug-info
230 Print debugging information and memory and CPU usage statistics
232 when the program exits. This option was added in MySQL 5.1.21.
233 · --disable-log-bin, -D
235 Disable binary logging. This is useful for avoiding an endless loop
237 if you use the --to-last-log option and are sending the output to
238 the same MySQL server. This option also is useful when restoring
239 after a crash to avoid duplication of the statements you have
240 logged.
241 This option requires that you have the SUPER privilege. It causes
243 mysqlbinlog to include a SET sql_log_bin = 0 statement in its
244 output to disable binary logging of the remaining output. The SET
245 statement is ineffective unless you have the SUPER privilege.
246 · --force-if-open, -F
248 Read binary log files even if they are open or were not closed
250 properly. This option was added in MySQL 5.1.15.
251 · --force-read, -f
253 With this option, if mysqlbinlog reads a binary log event that it
255 does not recognize, it prints a warning, ignores the event, and
256 continues. Without this option, mysqlbinlog stops if it reads such
257 an event.
258 · --hexdump, -H
260 Display a hex dump of the log in comments, as described in the
262 section called “MYSQLBINLOG HEX DUMP FORMAT”. The hex output can be
263 helpful for replication debugging. This option was added in MySQL
264 5.1.2.
265 · --host=host_name, -h host_name
267 Get the binary log from the MySQL server on the given host.
269 · --local-load=path, -l path
271 Prepare local temporary files for LOAD DATA INFILE in the specified
273 directory.
274 Important
276 These temporary files are not automatically removed by
277 mysqlbinlog or any other MySQL program.
278 · --offset=N, -o N
280 Skip the first N entries in the log.
282 · --password[=password], -p[password]
284 The password to use when connecting to the server. If you use the
286 short option form (-p), you cannot have a space between the option
287 and the password. If you omit the password value following the
288 --password or -p option on the command line, mysqlbinlog prompts
289 for one.
290 Specifying a password on the command line should be considered
292 insecure. See Section 6.1.2.1, “End-User Guidelines for Password
293 Security”. You can use an option file to avoid giving the password
294 on the command line.
295 · --port=port_num, -P port_num
297 The TCP/IP port number to use for connecting to a remote server.
299 · --position=N
301 Deprecated. Use --start-position instead. --position is removed in
303 MySQL 5.5.
304 · --protocol={TCP|SOCKET|PIPE|MEMORY}
306 The connection protocol to use for connecting to the server. It is
308 useful when the other connection parameters normally would cause a
309 protocol to be used other than the one you want. For details on the
310 permissible values, see Section 4.2.2, “Connecting to the MySQL
311 Server”.
312 · --read-from-remote-server, -R
314 Read the binary log from a MySQL server rather than reading a local
316 log file. Any connection parameter options are ignored unless this
317 option is given as well. These options are --host, --password,
318 --port, --protocol, --socket, and --user.
319 This option requires that the remote server be running. It works
321 only for binary log files on the remote server, not relay log
322 files.
323 · --result-file=name, -r name
325 Direct output to the given file.
327 · --server-id=id
329 Display only those events created by the server having the given
331 server ID. This option is available as of MySQL 5.1.4.
332 · --server-id-bits=N
334 Use only the first N bits of the server_id to identify the server.
336 If the binary log was written by a mysqld with server-id-bits set
337 to less than 32 and user data stored in the most significant bit,
338 running mysqlbinlog with --server-id-bits set to 32 enables this
339 data to be seen.
340 This option was added in MySQL Cluster NDB 7.0.17 and MySQL Cluster
342 NDB 7.1.6, and is supported only by the versions of mysqlbinlog
343 supplied with these and later releases of MySQL Cluster.
344 · --set-charset=charset_name
346 Add a SET NAMES charset_name statement to the output to specify the
348 character set to be used for processing log files. This option was
349 added in MySQL 5.1.12.
350 · --short-form, -s
352 Display only the statements contained in the log, without any extra
354 information or row-based events. This is for testing only, and
355 should not be used in production systems.
356 · --socket=path, -S path
358 For connections to localhost, the Unix socket file to use, or, on
360 Windows, the name of the named pipe to use.
361 · --start-datetime=datetime
363 Start reading the binary log at the first event having a timestamp
365 equal to or later than the datetime argument. The datetime value is
366 relative to the local time zone on the machine where you run
367 mysqlbinlog. The value should be in a format accepted for the
368 DATETIME or TIMESTAMP data types. For example:
369 shell> mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003
371 This option is useful for point-in-time recovery. See Section 7.3,
373 “Example Backup and Recovery Strategy”.
374 · --start-position=N, -j N
376 Start reading the binary log at the first event having a position
378 equal to or greater than N. This option applies to the first log
379 file named on the command line.
380 This option is useful for point-in-time recovery. See Section 7.3,
382 “Example Backup and Recovery Strategy”.
383 · --stop-datetime=datetime
385 Stop reading the binary log at the first event having a timestamp
387 equal to or later than the datetime argument. This option is useful
388 for point-in-time recovery. See the description of the
389 --start-datetime option for information about the datetime value.
390 This option is useful for point-in-time recovery. See Section 7.3,
392 “Example Backup and Recovery Strategy”.
393 · --stop-position=N
395 Stop reading the binary log at the first event having a position
397 equal to or greater than N. This option applies to the last log
398 file named on the command line.
399 This option is useful for point-in-time recovery. See Section 7.3,
401 “Example Backup and Recovery Strategy”.
402 · --to-last-log, -t
404 Do not stop at the end of the requested binary log from a MySQL
406 server, but rather continue printing until the end of the last
407 binary log. If you send the output to the same MySQL server, this
408 may lead to an endless loop. This option requires
409 --read-from-remote-server.
410 · --user=user_name, -u user_name
412 The MySQL user name to use when connecting to a remote server.
414 · --verbose, -v
416 Reconstruct row events and display them as commented SQL
418 statements. If this option is given twice, the output includes
419 comments to indicate column data types and some metadata. This
420 option was added in MySQL 5.1.28.
421 For examples that show the effect of --base64-output and --verbose
423 on row event output, see the section called “MYSQLBINLOG ROW EVENT
424 DISPLAY”.
425 · --version, -V
427 Display version information and exit.
429 In MySQL 5.1, the version number shown for mysqlbinlog is always
431 3.3.
432 You can also set the following variable by using --var_name=value
434 syntax:
435 · open_files_limit
437 Specify the number of open file descriptors to reserve.
439 You can pipe the output of mysqlbinlog into the mysql client to execute
441 the events contained in the binary log. This technique is used to
442 recover from a crash when you have an old backup (see Section 7.5,
443 “Point-in-Time (Incremental) Recovery Using the Binary Log”). For
444 example:
445 shell> mysqlbinlog binlog.000001 | mysql -u root -p
447 Or:
449 shell> mysqlbinlog binlog.[0-9]* | mysql -u root -p
451 You can also redirect the output of mysqlbinlog to a text file instead,
453 if you need to modify the statement log first (for example, to remove
454 statements that you do not want to execute for some reason). After
455 editing the file, execute the statements that it contains by using it
456 as input to the mysql program:
457 shell> mysqlbinlog binlog.000001 > tmpfile
459 shell> ... edit tmpfile ...
460 shell> mysql -u root -p < tmpfile
461 When mysqlbinlog is invoked with the --start-position option, it
463 displays only those events with an offset in the binary log greater
464 than or equal to a given position (the given position must match the
465 start of one event). It also has options to stop and start when it sees
466 an event with a given date and time. This enables you to perform
467 point-in-time recovery using the --stop-datetime option (to be able to
468 say, for example, “roll forward my databases to how they were today at
469 10:30 a.m.”).
470 If you have more than one binary log to execute on the MySQL server,
472 the safe method is to process them all using a single connection to the
473 server. Here is an example that demonstrates what may be unsafe:
474 shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
476 shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
477 Processing binary logs this way using multiple connections to the
479 server causes problems if the first log file contains a CREATE
480 TEMPORARY TABLE statement and the second log contains a statement that
481 uses the temporary table. When the first mysql process terminates, the
482 server drops the temporary table. When the second mysql process
483 attempts to use the table, the server reports “unknown table.”
484 To avoid problems like this, use a single mysql process to execute the
486 contents of all binary logs that you want to process. Here is one way
487 to do so:
488 shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
490 Another approach is to write all the logs to a single file and then
492 process the file:
493 shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
495 shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
496 shell> mysql -u root -p -e "source /tmp/statements.sql"
497 mysqlbinlog can produce output that reproduces a LOAD DATA INFILE
499 operation without the original data file. mysqlbinlog copies the data
500 to a temporary file and writes a LOAD DATA LOCAL INFILE statement that
501 refers to the file. The default location of the directory where these
502 files are written is system-specific. To specify a directory
503 explicitly, use the --local-load option.
504 Because mysqlbinlog converts LOAD DATA INFILE statements to LOAD DATA
506 LOCAL INFILE statements (that is, it adds LOCAL), both the client and
507 the server that you use to process the statements must be configured
508 with the LOCAL capability enabled. See Section 6.1.6, “Security Issues
509 with LOAD DATA LOCAL”.
510 Warning
512 The temporary files created for LOAD DATA LOCAL statements are not
513 automatically deleted because they are needed until you actually
514 execute those statements. You should delete the temporary files
515 yourself after you no longer need the statement log. The files can
516 be found in the temporary file directory and have names like
517 original_file_name-#-#.
518
520 The --hexdump option causes mysqlbinlog to produce a hex dump of the
521 binary log contents:
522 shell> mysqlbinlog --hexdump master-bin.000001
524 The hex output consists of comment lines beginning with #, so the
526 output might look like this for the preceding command:
527 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
529 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
530 # at 4
531 #051024 17:24:13 server id 1 end_log_pos 98
532 # Position Timestamp Type Master ID Size Master Pos Flags
533 # 00000004 9d fc 5c 43 0f 01 00 00 00 5e 00 00 00 62 00 00 00 00 00
534 # 00000017 04 00 35 2e 30 2e 31 35 2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
535 # 00000027 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |og..............|
536 # 00000037 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
537 # 00000047 00 00 00 00 9d fc 5c 43 13 38 0d 00 08 00 12 00 |.......C.8......|
538 # 00000057 04 04 04 04 12 00 00 4b 00 04 1a |.......K...|
539 # Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
540 # at startup
541 ROLLBACK;
542 Hex dump output currently contains the elements in the following list.
544 This format is subject to change. (For more information about binary
545 log format, see MySQL Internals: The Binary Log[1].
546 · Position: The byte position within the log file.
548 · Timestamp: The event timestamp. In the example shown, '9d fc 5c 43'
550 is the representation of '051024 17:24:13' in hexadecimal.
551 · Type: The event type code. In the example shown, '0f' indicates a
553 FORMAT_DESCRIPTION_EVENT. The following table lists the possible
554 type codes.
555 ┌─────┬──────────────────────────┬──────────────────────────────────────┐
557 │Type │ Name │ Meaning │
558 ├─────┼──────────────────────────┼──────────────────────────────────────┤
559 │00 │ UNKNOWN_EVENT │ This event should │
560 │ │ │ never be present in │
561 │ │ │ the log. │
562 ├─────┼──────────────────────────┼──────────────────────────────────────┤
563 │01 │ START_EVENT_V3 │ This indicates the │
564 │ │ │ start of a log file │
565 │ │ │ written by MySQL 4 │
566 │ │ │ or earlier. │
567 ├─────┼──────────────────────────┼──────────────────────────────────────┤
568 │02 │ QUERY_EVENT │ The most common │
569 │ │ │ type of events. │
570 │ │ │ These contain │
571 │ │ │ statements executed │
572 │ │ │ on the │
573 │ │ │ master. │
574 ├─────┼──────────────────────────┼──────────────────────────────────────┤
575 │03 │ STOP_EVENT │ Indicates that master has │
576 │ │ │ stopped. │
577 ├─────┼──────────────────────────┼──────────────────────────────────────┤
578 │04 │ ROTATE_EVENT │ Written when the master │
579 │ │ │ switches to a new log file. │
580 ├─────┼──────────────────────────┼──────────────────────────────────────┤
581 │05 │ INTVAR_EVENT │ Used for AUTO_INCREMENT │
582 │ │ │ values or when the │
583 │ │ │ LAST_INSERT_ID() │
584 │ │ │ function │
585 │ │ │ is used in the statement. │
586 ├─────┼──────────────────────────┼──────────────────────────────────────┤
587 │06 │ LOAD_EVENT │ Used for LOAD DATA │
588 │ │ │ INFILE in MySQL │
589 │ │ │ 3.23. │
590 ├─────┼──────────────────────────┼──────────────────────────────────────┤
591 │07 │ SLAVE_EVENT │ Reserved for future use. │
592 ├─────┼──────────────────────────┼──────────────────────────────────────┤
593 │08 │ CREATE_FILE_EVENT │ Used for LOAD DATA │
594 │ │ │ INFILE │
595 │ │ │ statements. This indicates the │
596 │ │ │ start of │
597 │ │ │ execution of such a statement. A │
598 │ │ │ temporary │
599 │ │ │ file is created │
600 │ │ │ on the slave. Used in MySQL 4 only. │
601 ├─────┼──────────────────────────┼──────────────────────────────────────┤
602 │09 │ APPEND_BLOCK_EVENT │ Contains data for use in a │
603 │ │ │ LOAD DATA │
604 │ │ │ INFILE │
605 │ │ │ statement. The data is stored in │
606 │ │ │ the temporary │
607 │ │ │ file on the slave. │
608 ├─────┼──────────────────────────┼──────────────────────────────────────┤
609 │0a │ EXEC_LOAD_EVENT │ Used for LOAD DATA │
610 │ │ │ INFILE │
611 │ │ │ statements. The contents of the │
612 │ │ │ temporary file │
613 │ │ │ is stored in the table on the slave. │
614 │ │ │ Used in MySQL 4 │
615 │ │ │ only. │
616 ├─────┼──────────────────────────┼──────────────────────────────────────┤
617 │0b │ DELETE_FILE_EVENT │ Rollback of a LOAD DATA │
618 │ │ │ INFILE │
619 │ │ │ statement. The temporary file │
620 │ │ │ should be │
621 │ │ │ deleted on the slave. │
622 ├─────┼──────────────────────────┼──────────────────────────────────────┤
623 │0c │ NEW_LOAD_EVENT │ Used for LOAD DATA │
624 │ │ │ INFILE in MySQL │
625 │ │ │ 4 and earlier. │
626 ├─────┼──────────────────────────┼──────────────────────────────────────┤
627 │0d │ RAND_EVENT │ Used to send information about │
628 │ │ │ random values if the │
629 │ │ │ RAND() function │
630 │ │ │ is │
631 │ │ │ used in the │
632 │ │ │ statement. │
633 ├─────┼──────────────────────────┼──────────────────────────────────────┤
634 │0e │ USER_VAR_EVENT │ Used to replicate user variables. │
635 ├─────┼──────────────────────────┼──────────────────────────────────────┤
636 │0f │ FORMAT_DESCRIPTION_EVENT │ This indicates the start of a log │
637 │ │ │ file written by MySQL 5 or later. │
638 ├─────┼──────────────────────────┼──────────────────────────────────────┤
639 │10 │ XID_EVENT │ Event indicating commit of an XA │
640 │ │ │ transaction. │
641 ├─────┼──────────────────────────┼──────────────────────────────────────┤
642 │11 │ BEGIN_LOAD_QUERY_EVENT │ Used for LOAD DATA │
643 │ │ │ INFILE │
644 │ │ │ statements in MySQL 5 and later. │
645 ├─────┼──────────────────────────┼──────────────────────────────────────┤
646 │12 │ EXECUTE_LOAD_QUERY_EVENT │ Used for LOAD DATA │
647 │ │ │ INFILE │
648 │ │ │ statements in MySQL 5 and later. │
649 ├─────┼──────────────────────────┼──────────────────────────────────────┤
650 │13 │ TABLE_MAP_EVENT │ Information about a table │
651 │ │ │ definition. Used in MySQL 5.1.5 and │
652 │ │ │ later. │
653 ├─────┼──────────────────────────┼──────────────────────────────────────┤
654 │14 │ PRE_GA_WRITE_ROWS_EVENT │ Row data for a single table that │
655 │ │ │ should be created. Used in MySQL │
656 │ │ │ 5.1.5 │
657 │ │ │ to 5.1.17. │
658 ├─────┼──────────────────────────┼──────────────────────────────────────┤
659 │15 │ PRE_GA_UPDATE_ROWS_EVENT │ Row data for a single table that │
660 │ │ │ needs to be updated. Used in MySQL │
661 │ │ │ 5.1.5 to 5.1.17. │
662 ├─────┼──────────────────────────┼──────────────────────────────────────┤
663 │16 │ PRE_GA_DELETE_ROWS_EVENT │ Row data for a single table that │
664 │ │ │ should be deleted. Used in MySQL │
665 │ │ │ 5.1.5 │
666 │ │ │ to 5.1.17. │
667 ├─────┼──────────────────────────┼──────────────────────────────────────┤
668 │17 │ WRITE_ROWS_EVENT │ Row data for a single table that │
669 │ │ │ should be created. Used in MySQL │
670 │ │ │ 5.1.18 │
671 │ │ │ and later. │
672 ├─────┼──────────────────────────┼──────────────────────────────────────┤
673 │18 │ UPDATE_ROWS_EVENT │ Row data for a single table that │
674 │ │ │ needs to be updated. Used in MySQL │
675 │ │ │ 5.1.18 and │
676 │ │ │ later. │
677 ├─────┼──────────────────────────┼──────────────────────────────────────┤
678 │19 │ DELETE_ROWS_EVENT │ Row data for a single table that │
679 │ │ │ should be deleted. Used in MySQL │
680 │ │ │ 5.1.18 │
681 │ │ │ and later. │
682 ├─────┼──────────────────────────┼──────────────────────────────────────┤
683 │1a │ INCIDENT_EVENT │ Something out of the ordinary │
684 │ │ │ happened. Added in MySQL 5.1.18. │
685 └─────┴──────────────────────────┴──────────────────────────────────────┘
686 · Master ID: The server ID of the master that created the event.
688 · Size: The size in bytes of the event.
690 · Master Pos: The position of the next event in the original master
692 log file.
693 · Flags: 16 flags. Currently, the following flags are used. The
695 others are reserved for future use.
696 ┌─────┬─────────────────────────────┬────────────────────────────────────────────────┐
698 │Flag │ Name │ Meaning │
699 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
700 │01 │ LOG_EVENT_BINLOG_IN_USE_F │ Log file correctly │
701 │ │ │ closed. (Used only │
702 │ │ │ in │
703 │ │ │ FORMAT_DESCRIPTION_EVENT.) │
704 │ │ │ If │
705 │ │ │ this │
706 │ │ │ flag is set (if the │
707 │ │ │ flags are, for │
708 │ │ │ example, │
709 │ │ │ '01 │
710 │ │ │ 00') in a │
711 │ │ │ FORMAT_DESCRIPTION_EVENT, │
712 │ │ │ the log │
713 │ │ │ file │
714 │ │ │ has not been │
715 │ │ │ properly closed. │
716 │ │ │ Most probably │
717 │ │ │ this │
718 │ │ │ is because of a │
719 │ │ │ master crash (for │
720 │ │ │ example, due │
721 │ │ │ to │
722 │ │ │ power failure). │
723 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
724 │02 │ │ Reserved for future use. │
725 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
726 │04 │ LOG_EVENT_THREAD_SPECIFIC_F │ Set if the event is dependent on the │
727 │ │ │ connection it was executed in (for │
728 │ │ │ example, '04 00'), for │
729 │ │ │ example, │
730 │ │ │ if the event uses │
731 │ │ │ temporary tables. │
732 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
733 │08 │ LOG_EVENT_SUPPRESS_USE_F │ Set in some circumstances when the event is │
734 │ │ │ not dependent on the default │
735 │ │ │ database. │
736 └─────┴─────────────────────────────┴────────────────────────────────────────────────┘
737
739 The following examples illustrate how mysqlbinlog displays row events
740 that specify data modifications. These correspond to events with the
741 WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT type codes.
742 The --base64-output=DECODE-ROWS and --verbose options may be used to
743 affect row event output. These options are available as of MySQL
744 5.1.28.
745 Suppose that the server is using row-based binary logging and that you
747 execute the following sequence of statements:
748 CREATE TABLE t
750 (
751 id INT NOT NULL,
752 name VARCHAR(20) NOT NULL,
753 date DATE NULL
754 ) ENGINE = InnoDB;
755 START TRANSACTION;
756 INSERT INTO t VALUES(1, 'apple', NULL);
757 UPDATE t SET name = 'pear', date = '2009-01-01' WHERE id = 1;
758 DELETE FROM t WHERE id = 1;
759 COMMIT;
760 By default, mysqlbinlog displays row events encoded as base-64 strings
762 using BINLOG statements. Omitting extraneous lines, the output for the
763 row events produced by the preceding statement sequence looks like
764 this:
765 shell> mysqlbinlog log_file
767 ...
768 # at 218
769 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
770 BINLOG '
771 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
772 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
773 '/*!*/;
774 ...
775 # at 302
776 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
777 BINLOG '
778 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
779 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
780 '/*!*/;
781 ...
782 # at 400
783 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
784 BINLOG '
785 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
786 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
787 '/*!*/;
788 To see the row events as comments in the form of “pseudo-SQL”
790 statements, run mysqlbinlog with the --verbose or -v option. The output
791 will contain lines beginning with ###:
792 shell> mysqlbinlog -v log_file
794 ...
795 # at 218
796 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
797 BINLOG '
798 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
799 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
800 '/*!*/;
801 ### INSERT INTO test.t
802 ### SET
803 ### @1=1
804 ### @2='apple'
805 ### @3=NULL
806 ...
807 # at 302
808 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
809 BINLOG '
810 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
811 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
812 '/*!*/;
813 ### UPDATE test.t
814 ### WHERE
815 ### @1=1
816 ### @2='apple'
817 ### @3=NULL
818 ### SET
819 ### @1=1
820 ### @2='pear'
821 ### @3='2009:01:01'
822 ...
823 # at 400
824 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
825 BINLOG '
826 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
827 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
828 '/*!*/;
829 ### DELETE FROM test.t
830 ### WHERE
831 ### @1=1
832 ### @2='pear'
833 ### @3='2009:01:01'
834 Specify --verbose or -v twice to also display data types and some
836 metadata for each column. The output will contain an additional comment
837 following each column change:
838 shell> mysqlbinlog -vv log_file
840 ...
841 # at 218
842 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
843 BINLOG '
844 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
845 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
846 '/*!*/;
847 ### INSERT INTO test.t
848 ### SET
849 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
850 ### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
851 ### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
852 ...
853 # at 302
854 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
855 BINLOG '
856 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
857 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
858 '/*!*/;
859 ### UPDATE test.t
860 ### WHERE
861 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
862 ### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
863 ### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
864 ### SET
865 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
866 ### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
867 ### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
868 ...
869 # at 400
870 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
871 BINLOG '
872 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
873 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
874 '/*!*/;
875 ### DELETE FROM test.t
876 ### WHERE
877 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
878 ### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
879 ### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
880 You can tell mysqlbinlog to suppress the BINLOG statements for row
882 events by using the --base64-output=DECODE-ROWS option. This is similar
883 to --base64-output=NEVER but does not exit with an error if a row event
884 is found. The combination of --base64-output=DECODE-ROWS and --verbose
885 provides a convenient way to see row events only as SQL statements:
886 shell> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
888 ...
889 # at 218
890 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
891 ### INSERT INTO test.t
892 ### SET
893 ### @1=1
894 ### @2='apple'
895 ### @3=NULL
896 ...
897 # at 302
898 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
899 ### UPDATE test.t
900 ### WHERE
901 ### @1=1
902 ### @2='apple'
903 ### @3=NULL
904 ### SET
905 ### @1=1
906 ### @2='pear'
907 ### @3='2009:01:01'
908 ...
909 # at 400
910 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
911 ### DELETE FROM test.t
912 ### WHERE
913 ### @1=1
914 ### @2='pear'
915 ### @3='2009:01:01'
916 Note
919 You should not suppress BINLOG statements if you intend to
920 re-execute mysqlbinlog output.
921 The SQL statements produced by --verbose for row events are much more
923 readable than the corresponding BINLOG statements. However, they do not
924 correspond exactly to the original SQL statements that generated the
925 events. The following limitations apply:
926 · The original column names are lost and replaced by @N, where N is a
928 column number.
929 · Character set information is not available in the binary log, which
931 affects string column display:
932 · There is no distinction made between corresponding binary and
934 nonbinary string types (BINARY and CHAR, VARBINARY and VARCHAR,
935 BLOB and TEXT). The output uses a data type of STRING for
936 fixed-length strings and VARSTRING for variable-length strings.
937 · For multi-byte character sets, the maximum number of bytes per
939 character is not present in the binary log, so the length for
940 string types is displayed in bytes rather than in characters.
941 For example, STRING(4) will be used as the data type for values
942 from either of these column types:
943 CHAR(4) CHARACTER SET latin1
945 CHAR(2) CHARACTER SET ucs2
946 · Due to the storage format for events of type UPDATE_ROWS_EVENT,
948 UPDATE statements are displayed with the WHERE clause preceding
949 the SET clause.
950 Proper interpretation of row events requires the information from the
952 format description event at the beginning of the binary log. Because
953 mysqlbinlog does not know in advance whether the rest of the log
954 contains row events, by default it displays the format description
955 event using a BINLOG statement in the initial part of the output.
956 If the binary log is known not to contain any events requiring a BINLOG
958 statement (that is, no row events), the --base64-output=NEVER option
959 can be used to prevent this header from being written.
960
962 Copyright © 1997, 2013, Oracle and/or its affiliates. All rights
963 reserved.
964 This documentation is free software; you can redistribute it and/or
966 modify it only under the terms of the GNU General Public License as
967 published by the Free Software Foundation; version 2 of the License.
968 This documentation is distributed in the hope that it will be useful,
970 but WITHOUT ANY WARRANTY; without even the implied warranty of
971 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
972 General Public License for more details.
973 You should have received a copy of the GNU General Public License along
975 with the program; if not, write to the Free Software Foundation, Inc.,
976 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
977 http://www.gnu.org/licenses/.
978
981 1. MySQL Internals: The Binary Log
982 http://dev.mysql.com/doc/internals/en/binary-log.html
983
985 For more information, please refer to the MySQL Reference Manual, which
986 may already be installed locally and which is also available online at
987 http://dev.mysql.com/doc/.
988
990 Oracle Corporation (http://dev.mysql.com/).
991MySQL 5.1 11/04/2013 MYSQLBINLOG(1)