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 15.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 15.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 6.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.3.1, “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
120 given as --base64-output or --skip-base64-output (with the
121 sense of AUTO or NEVER). The option values described in the
122 preceding list may be used as of MySQL 5.1.24, with the
123 exception of UNSPEC and DECODE-ROWS, which are available as of
124 MySQL 5.1.28.
125 For examples that show the effect of --base64-output and
127 --verbose on row event output, see the section called
128 “MYSQLBINLOG ROW EVENT DISPLAY”.
129 · --bind-address=ip_address
131 On a computer having multiple network interfaces, this option
133 can be used to select which interface is employed when
134 connecting to the MySQL server.
135 This option is supported only in the version of mysqlbinlog
137 that is supplied with MySQL Cluster, beginning with MySQL
138 Cluster NDB 6.3.4. It is not available in standard MySQL 5.1
139 releases.
140 · --character-sets-dir=path
142 The directory where character sets are installed. See
144 Section 9.5, “Character Set Configuration”.
145 · --database=db_name, -d db_name
147 This option causes mysqlbinlog to output entries from the
149 binary log (local log only) that occur while db_name is been
150 selected as the default database by USE.
151 The --database option for mysqlbinlog is similar to the
153 --binlog-do-db option for mysqld, but can be used to specify
154 only one database. If --database is given multiple times, only
155 the last instance is used.
156 The effects of this option depend on whether the
158 statement-based or row-based logging format is in use, in the
159 same way that the effects of --binlog-do-db depend on whether
160 statement-based or row-based logging is in use.
161 Statement-based logging. The --database option works as
163 follows:
164 · While db_name is the default database, statements are
166 output whether they modify tables in db_name or a different
167 database.
168 · Unless db_name is selected as the default database,
170 statements are not output, even if they modify tables in
171 db_name.
172 · There is an exception for CREATE DATABASE, ALTER DATABASE,
174 and DROP DATABASE. The database being created, altered, or
175 dropped is considered to be the default database when
176 determining whether to output the statement.
177 Suppose that the binary log was created by executing these
178 statements using statement-based-logging:
179 INSERT INTO test.t1 (i) VALUES(100);
181 INSERT INTO db2.t2 (j) VALUES(200);
182 USE test;
183 INSERT INTO test.t1 (i) VALUES(101);
184 INSERT INTO t1 (i) VALUES(102);
185 INSERT INTO db2.t2 (j) VALUES(201);
186 USE db2;
187 INSERT INTO test.t1 (i) VALUES(103);
188 INSERT INTO db2.t2 (j) VALUES(202);
189 INSERT INTO t2 (j) VALUES(203);
190 mysqlbinlog --database=test does not output the first two
192 INSERT statements because there is no default database. It
193 outputs the three INSERT statements following USE test, but
194 not the three INSERT statements following USE db2.
195 mysqlbinlog --database=db2 does not output the first two
197 INSERT statements because there is no default database. It
198 does not output the three INSERT statements following USE
199 test, but does output the three INSERT statements following
200 USE db2.
201 Row-based logging. mysqlbinlog outputs only entries that
203 change tables belonging to db_name. The default database
204 has no effect on this. Suppose that the binary log just
205 described was created using row-based logging rather than
206 statement-based logging. mysqlbinlog --database=test
207 outputs only those entries that modify t1 in the test
208 database, regardless of whether USE was issued or what the
209 default database is. If a server is running with
210 binlog_format set to MIXED and you want it to be possible
211 to use mysqlbinlog with the --database option, you must
212 ensure that tables that are modified are in the database
213 selected by USE. (In particular, no cross-database updates
214 should be used.)
215 Note
217 This option did not work correctly for mysqlbinlog with
218 row-based logging prior to MySQL 5.1.37. (Bug #42941)
219 · --debug[=debug_options], -# [debug_options]
221 Write a debugging log. A typical debug_options string is
223 ´d:t:o,file_name´. The default is
224 ´d:t:o,/tmp/mysqlbinlog.trace´.
225 · --debug-check
227 Print some debugging information when the program exits.
229 This option was added in MySQL 5.1.21.
230 · --debug-info
232 Print debugging information and memory and CPU usage
234 statistics when the program exits. This option was added in
235 MySQL 5.1.21.
236 · --disable-log-bin, -D
238 Disable binary logging. This is useful for avoiding an
240 endless loop if you use the --to-last-log option and are
241 sending the output to the same MySQL server. This option
242 also is useful when restoring after a crash to avoid
243 duplication of the statements you have logged.
244 This option requires that you have the SUPER privilege. It
246 causes mysqlbinlog to include a SET sql_log_bin = 0
247 statement in its output to disable binary logging of the
248 remaining output. The SET statement is ineffective unless
249 you have the SUPER privilege.
250 · --force-read, -f
252 With this option, if mysqlbinlog reads a binary log event
254 that it does not recognize, it prints a warning, ignores
255 the event, and continues. Without this option, mysqlbinlog
256 stops if it reads such an event.
257 · --hexdump, -H
259 Display a hex dump of the log in comments, as described in
261 the section called “MYSQLBINLOG HEX DUMP FORMAT”. The hex
262 output can be helpful for replication debugging. This
263 option was added in MySQL 5.1.2.
264 · --host=host_name, -h host_name
266 Get the binary log from the MySQL server on the given host.
268 · --local-load=path, -l path
270 Prepare local temporary files for LOAD DATA INFILE in the
272 specified directory.
273 Important
275 These temporary files are not automatically removed by
276 mysqlbinlog or any other MySQL program.
277 · --offset=N, -o N
279 Skip the first N entries in the log.
281 · --password[=password], -p[password]
283 The password to use when connecting to the server. If you
285 use the short option form (-p), you cannot have a space
286 between the option and the password. If you omit the
287 password value following the --password or -p option on the
288 command line, mysqlbinlog prompts for one.
289 Specifying a password on the command line should be
291 considered insecure. See Section 5.3.2.2, “End-User
292 Guidelines for Password Security”. You can use an option
293 file to avoid giving the password on the command line.
294 · --port=port_num, -P port_num
296 The TCP/IP port number to use for connecting to a remote
298 server.
299 · --position=N
301 Deprecated. Use --start-position instead. --position is
303 removed in MySQL 5.5.
304 · --protocol={TCP|SOCKET|PIPE|MEMORY}
306 The connection protocol to use for connecting to the
308 server. It is useful when the other connection parameters
309 normally would cause a protocol to be used other than the
310 one you want. For details on the permissible values, see
311 Section 4.2.2, “Connecting to the MySQL Server”.
312 · --read-from-remote-server, -R
314 Read the binary log from a MySQL server rather than reading
316 a local log file. Any connection parameter options are
317 ignored unless this option is given as well. These options
318 are --host, --password, --port, --protocol, --socket, and
319 --user.
320 This option requires that the remote server be running. It
322 works only for binary log files on the remote server, not
323 relay log files.
324 · --result-file=name, -r name
326 Direct output to the given file.
328 · --server-id=id
330 Display only those events created by the server having the
332 given server ID. This option is available as of MySQL
333 5.1.4.
334 · --server-id-bits=N
336 Use only the first N bits of the server_id to identify the
338 server. If the binary log was written by a mysqld with
339 server-id-bits set to less than 32 and user data stored in
340 the most significant bit, running mysqlbinlog with
341 --server-id-bits set to 32 enables this data to be seen.
342 This option was added in MySQL Cluster NDB 7.0.17 and MySQL
344 Cluster NDB 7.1.6, and is supported only by the versions of
345 mysqlbinlog supplied with these and later releases of MySQL
346 Cluster.
347 · --set-charset=charset_name
349 Add a SET NAMES charset_name statement to the output to
351 specify the character set to be used for processing log
352 files. This option was added in MySQL 5.1.12.
353 · --short-form, -s
355 Display only the statements contained in the log, without
357 any extra information or row-based events. This is for
358 testing only, and should not be used in production systems.
359 · --socket=path, -S path
361 For connections to localhost, the Unix socket file to use,
363 or, on Windows, the name of the named pipe to use.
364 · --start-datetime=datetime
366 Start reading the binary log at the first event having a
368 timestamp equal to or later than the datetime argument. The
369 datetime value is relative to the local time zone on the
370 machine where you run mysqlbinlog. The value should be in a
371 format accepted for the DATETIME or TIMESTAMP data types.
372 For example:
373 shell> mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003
375 This option is useful for point-in-time recovery. See
377 Section 6.3, “Example Backup and Recovery Strategy”.
378 · --start-position=N, -j N
380 Start reading the binary log at the first event having a
382 position equal to or greater than N. This option applies to
383 the first log file named on the command line.
384 This option is useful for point-in-time recovery. See
386 Section 6.3, “Example Backup and Recovery Strategy”.
387 · --stop-datetime=datetime
389 Stop reading the binary log at the first event having a
391 timestamp equal to or later than the datetime argument.
392 This option is useful for point-in-time recovery. See the
393 description of the --start-datetime option for information
394 about the datetime value.
395 This option is useful for point-in-time recovery. See
397 Section 6.3, “Example Backup and Recovery Strategy”.
398 · --stop-position=N
400 Stop reading the binary log at the first event having a
402 position equal to or greater than N. This option applies to
403 the last log file named on the command line.
404 This option is useful for point-in-time recovery. See
406 Section 6.3, “Example Backup and Recovery Strategy”.
407 · --to-last-log, -t
409 Do not stop at the end of the requested binary log from a
411 MySQL server, but rather continue printing until the end of
412 the last binary log. If you send the output to the same
413 MySQL server, this may lead to an endless loop. This option
414 requires --read-from-remote-server.
415 · --user=user_name, -u user_name
417 The MySQL user name to use when connecting to a remote
419 server.
420 · --verbose, -v
422 Reconstruct row events and display them as commented SQL
424 statements. If this option is given twice, the output
425 includes comments to indicate column data types and some
426 metadata. This option was added in MySQL 5.1.28.
427 For examples that show the effect of --base64-output and
429 --verbose on row event output, see the section called
430 “MYSQLBINLOG ROW EVENT DISPLAY”.
431 · --version, -V
433 Display version information and exit.
435 You can also set the following variable by using
437 --var_name=value syntax:
438 · open_files_limit
440 Specify the number of open file descriptors to reserve.
442 You can pipe the output of mysqlbinlog into the mysql client to
444 execute the events contained in the binary log. This technique
445 is used to recover from a crash when you have an old backup
446 (see Section 6.5, “Point-in-Time (Incremental) Recovery Using
447 the Binary Log”). For example:
448 shell> mysqlbinlog binlog.000001 | mysql -u root -p
450 Or:
452 shell> mysqlbinlog binlog.[0-9]* | mysql -u root -p
454 You can also redirect the output of mysqlbinlog to a text file
456 instead, if you need to modify the statement log first (for
457 example, to remove statements that you do not want to execute
458 for some reason). After editing the file, execute the
459 statements that it contains by using it as input to the mysql
460 program:
461 shell> mysqlbinlog binlog.000001 > tmpfile
463 shell> ... edit tmpfile ...
464 shell> mysql -u root -p < tmpfile
465 When mysqlbinlog is invoked with the --start-position option,
467 it displays only those events with an offset in the binary log
468 greater than or equal to a given position (the given position
469 must match the start of one event). It also has options to stop
470 and start when it sees an event with a given date and time.
471 This enables you to perform point-in-time recovery using the
472 --stop-datetime option (to be able to say, for example, “roll
473 forward my databases to how they were today at 10:30 a.m.”).
474 If you have more than one binary log to execute on the MySQL
476 server, the safe method is to process them all using a single
477 connection to the server. Here is an example that demonstrates
478 what may be unsafe:
479 shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
481 shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
482 Processing binary logs this way using multiple connections to
484 the server causes problems if the first log file contains a
485 CREATE TEMPORARY TABLE statement and the second log contains a
486 statement that uses the temporary table. When the first mysql
487 process terminates, the server drops the temporary table. When
488 the second mysql process attempts to use the table, the server
489 reports “unknown table.”
490 To avoid problems like this, use a single mysql process to
492 execute the contents of all binary logs that you want to
493 process. Here is one way to do so:
494 shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
496 Another approach is to write all the logs to a single file and
498 then process the file:
499 shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
501 shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
502 shell> mysql -u root -p -e "source /tmp/statements.sql"
503 mysqlbinlog can produce output that reproduces a LOAD DATA
505 INFILE operation without the original data file. mysqlbinlog
506 copies the data to a temporary file and writes a LOAD DATA
507 LOCAL INFILE statement that refers to the file. The default
508 location of the directory where these files are written is
509 system-specific. To specify a directory explicitly, use the
510 --local-load option.
511 Because mysqlbinlog converts LOAD DATA INFILE statements to
513 LOAD DATA LOCAL INFILE statements (that is, it adds LOCAL),
514 both the client and the server that you use to process the
515 statements must be configured with the LOCAL capability
516 enabled. See Section 5.3.5, “Security Issues with LOAD DATA
517 LOCAL”.
518 Warning
520 The temporary files created for LOAD DATA LOCAL statements
521 are not automatically deleted because they are needed until
522 you actually execute those statements. You should delete
523 the temporary files yourself after you no longer need the
524 statement log. The files can be found in the temporary file
525 directory and have names like original_file_name-#-#.
526
528 The --hexdump option causes mysqlbinlog to produce a hex dump of the
529 binary log contents:
530 shell> mysqlbinlog --hexdump master-bin.000001
532 The hex output consists of comment lines beginning with #, so the
534 output might look like this for the preceding command:
535 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
537 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
538 # at 4
539 #051024 17:24:13 server id 1 end_log_pos 98
540 # Position Timestamp Type Master ID Size Master Pos Flags
541 # 00000004 9d fc 5c 43 0f 01 00 00 00 5e 00 00 00 62 00 00 00 00 00
542 # 00000017 04 00 35 2e 30 2e 31 35 2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
543 # 00000027 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |og..............|
544 # 00000037 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
545 # 00000047 00 00 00 00 9d fc 5c 43 13 38 0d 00 08 00 12 00 |.......C.8......|
546 # 00000057 04 04 04 04 12 00 00 4b 00 04 1a |.......K...|
547 # Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
548 # at startup
549 ROLLBACK;
550 Hex dump output currently contains the elements in the following list.
552 This format is subject to change. (For more information about binary
553 log format, see
554 http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log.)
555 · Position: The byte position within the log file.
557 · Timestamp: The event timestamp. In the example shown, ´9d fc 5c 43´
559 is the representation of ´051024 17:24:13´ in hexadecimal.
560 · Type: The event type code. In the example shown, ´0f´ indicates a
562 FORMAT_DESCRIPTION_EVENT. The following table lists the possible
563 type codes.
564 ┌─────┬──────────────────────────┬──────────────────────────────────────┐
566 │Type │ Name │ Meaning │
567 ├─────┼──────────────────────────┼──────────────────────────────────────┤
568 │00 │ UNKNOWN_EVENT │ This event should │
569 │ │ │ never be present in │
570 │ │ │ the log. │
571 ├─────┼──────────────────────────┼──────────────────────────────────────┤
572 │01 │ START_EVENT_V3 │ This indicates the │
573 │ │ │ start of a log file │
574 │ │ │ written by MySQL 4 │
575 │ │ │ or earlier. │
576 ├─────┼──────────────────────────┼──────────────────────────────────────┤
577 │02 │ QUERY_EVENT │ The most common │
578 │ │ │ type of events. │
579 │ │ │ These contain │
580 │ │ │ statements executed │
581 │ │ │ on the │
582 │ │ │ master. │
583 ├─────┼──────────────────────────┼──────────────────────────────────────┤
584 │03 │ STOP_EVENT │ Indicates that master has │
585 │ │ │ stopped. │
586 ├─────┼──────────────────────────┼──────────────────────────────────────┤
587 │04 │ ROTATE_EVENT │ Written when the master │
588 │ │ │ switches to a new log file. │
589 ├─────┼──────────────────────────┼──────────────────────────────────────┤
590 │05 │ INTVAR_EVENT │ Used for AUTO_INCREMENT │
591 │ │ │ values or when the │
592 │ │ │ LAST_INSERT_ID() │
593 │ │ │ function │
594 │ │ │ is used in the statement. │
595 ├─────┼──────────────────────────┼──────────────────────────────────────┤
596 │06 │ LOAD_EVENT │ Used for LOAD DATA │
597 │ │ │ INFILE in MySQL │
598 │ │ │ 3.23. │
599 ├─────┼──────────────────────────┼──────────────────────────────────────┤
600 │07 │ SLAVE_EVENT │ Reserved for future use. │
601 ├─────┼──────────────────────────┼──────────────────────────────────────┤
602 │08 │ CREATE_FILE_EVENT │ Used for LOAD DATA │
603 │ │ │ INFILE │
604 │ │ │ statements. This indicates the │
605 │ │ │ start of │
606 │ │ │ execution of such a statement. A │
607 │ │ │ temporary │
608 │ │ │ file is created │
609 │ │ │ on the slave. Used in MySQL 4 only. │
610 ├─────┼──────────────────────────┼──────────────────────────────────────┤
611 │09 │ APPEND_BLOCK_EVENT │ Contains data for use in a │
612 │ │ │ LOAD DATA │
613 │ │ │ INFILE │
614 │ │ │ statement. The data is stored in │
615 │ │ │ the temporary │
616 │ │ │ file on the slave. │
617 ├─────┼──────────────────────────┼──────────────────────────────────────┤
618 │0a │ EXEC_LOAD_EVENT │ Used for LOAD DATA │
619 │ │ │ INFILE │
620 │ │ │ statements. The contents of the │
621 │ │ │ temporary file │
622 │ │ │ is stored in the table on the slave. │
623 │ │ │ Used in MySQL 4 │
624 │ │ │ only. │
625 ├─────┼──────────────────────────┼──────────────────────────────────────┤
626 │0b │ DELETE_FILE_EVENT │ Rollback of a LOAD DATA │
627 │ │ │ INFILE │
628 │ │ │ statement. The temporary file │
629 │ │ │ should be │
630 │ │ │ deleted on the slave. │
631 ├─────┼──────────────────────────┼──────────────────────────────────────┤
632 │0c │ NEW_LOAD_EVENT │ Used for LOAD DATA │
633 │ │ │ INFILE in MySQL │
634 │ │ │ 4 and earlier. │
635 ├─────┼──────────────────────────┼──────────────────────────────────────┤
636 │0d │ RAND_EVENT │ Used to send information about │
637 │ │ │ random values if the │
638 │ │ │ RAND() function │
639 │ │ │ is │
640 │ │ │ used in the │
641 │ │ │ statement. │
642 ├─────┼──────────────────────────┼──────────────────────────────────────┤
643 │0e │ USER_VAR_EVENT │ Used to replicate user variables. │
644 ├─────┼──────────────────────────┼──────────────────────────────────────┤
645 │0f │ FORMAT_DESCRIPTION_EVENT │ This indicates the start of a log │
646 │ │ │ file written by MySQL 5 or later. │
647 ├─────┼──────────────────────────┼──────────────────────────────────────┤
648 │10 │ XID_EVENT │ Event indicating commit of an XA │
649 │ │ │ transaction. │
650 ├─────┼──────────────────────────┼──────────────────────────────────────┤
651 │11 │ BEGIN_LOAD_QUERY_EVENT │ Used for LOAD DATA │
652 │ │ │ INFILE │
653 │ │ │ statements in MySQL 5 and later. │
654 ├─────┼──────────────────────────┼──────────────────────────────────────┤
655 │12 │ EXECUTE_LOAD_QUERY_EVENT │ Used for LOAD DATA │
656 │ │ │ INFILE │
657 │ │ │ statements in MySQL 5 and later. │
658 ├─────┼──────────────────────────┼──────────────────────────────────────┤
659 │13 │ TABLE_MAP_EVENT │ Information about a table │
660 │ │ │ definition. Used in MySQL 5.1.5 and │
661 │ │ │ later. │
662 ├─────┼──────────────────────────┼──────────────────────────────────────┤
663 │14 │ PRE_GA_WRITE_ROWS_EVENT │ Row data for a single table that │
664 │ │ │ should be created. Used in MySQL │
665 │ │ │ 5.1.5 │
666 │ │ │ to 5.1.17. │
667 ├─────┼──────────────────────────┼──────────────────────────────────────┤
668 │15 │ PRE_GA_UPDATE_ROWS_EVENT │ Row data for a single table that │
669 │ │ │ needs to be updated. Used in MySQL │
670 │ │ │ 5.1.5 to 5.1.17. │
671 ├─────┼──────────────────────────┼──────────────────────────────────────┤
672 │16 │ PRE_GA_DELETE_ROWS_EVENT │ Row data for a single table that │
673 │ │ │ should be deleted. Used in MySQL │
674 │ │ │ 5.1.5 │
675 │ │ │ to 5.1.17. │
676 ├─────┼──────────────────────────┼──────────────────────────────────────┤
677 │17 │ WRITE_ROWS_EVENT │ Row data for a single table that │
678 │ │ │ should be created. Used in MySQL │
679 │ │ │ 5.1.18 │
680 │ │ │ and later. │
681 ├─────┼──────────────────────────┼──────────────────────────────────────┤
682 │18 │ UPDATE_ROWS_EVENT │ Row data for a single table that │
683 │ │ │ needs to be updated. Used in MySQL │
684 │ │ │ 5.1.18 and │
685 │ │ │ later. │
686 ├─────┼──────────────────────────┼──────────────────────────────────────┤
687 │19 │ DELETE_ROWS_EVENT │ Row data for a single table that │
688 │ │ │ should be deleted. Used in MySQL │
689 │ │ │ 5.1.18 │
690 │ │ │ and later. │
691 ├─────┼──────────────────────────┼──────────────────────────────────────┤
692 │1a │ INCIDENT_EVENT │ Something out of the ordinary │
693 │ │ │ happened. Added in MySQL 5.1.18. │
694 └─────┴──────────────────────────┴──────────────────────────────────────┘
695 · Master ID: The server ID of the master that created the event.
697 · Size: The size in bytes of the event.
699 · Master Pos: The position of the next event in the original master
701 log file.
702 · Flags: 16 flags. Currently, the following flags are used. The
704 others are reserved for future use.
705 ┌─────┬─────────────────────────────┬────────────────────────────────────────────────┐
707 │Flag │ Name │ Meaning │
708 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
709 │01 │ LOG_EVENT_BINLOG_IN_USE_F │ Log file correctly │
710 │ │ │ closed. (Used only │
711 │ │ │ in │
712 │ │ │ FORMAT_DESCRIPTION_EVENT.) │
713 │ │ │ If │
714 │ │ │ this │
715 │ │ │ flag is set (if the │
716 │ │ │ flags are, for │
717 │ │ │ example, │
718 │ │ │ ´01 │
719 │ │ │ 00´) in a │
720 │ │ │ FORMAT_DESCRIPTION_EVENT, │
721 │ │ │ the log │
722 │ │ │ file │
723 │ │ │ has not been │
724 │ │ │ properly closed. │
725 │ │ │ Most probably │
726 │ │ │ this │
727 │ │ │ is because of a │
728 │ │ │ master crash (for │
729 │ │ │ example, due │
730 │ │ │ to │
731 │ │ │ power failure). │
732 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
733 │02 │ │ Reserved for future use. │
734 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
735 │04 │ LOG_EVENT_THREAD_SPECIFIC_F │ Set if the event is dependent on the │
736 │ │ │ connection it was executed in (for │
737 │ │ │ example, ´04 00´), for │
738 │ │ │ example, │
739 │ │ │ if the event uses │
740 │ │ │ temporary tables. │
741 ├─────┼─────────────────────────────┼────────────────────────────────────────────────┤
742 │08 │ LOG_EVENT_SUPPRESS_USE_F │ Set in some circumstances when the event is │
743 │ │ │ not dependent on the default │
744 │ │ │ database. │
745 └─────┴─────────────────────────────┴────────────────────────────────────────────────┘
746
748 The following examples illustrate how mysqlbinlog displays row events
749 that specify data modifications. These correspond to events with the
750 WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT type codes.
751 The --base64-output=DECODE-ROWS and --verbose options may be used to
752 affect row event output. These options are available as of MySQL
753 5.1.28.
754 Suppose that the server is using row-based binary logging and that you
756 execute the following sequence of statements:
757 CREATE TABLE t
759 (
760 id INT NOT NULL,
761 name VARCHAR(20) NOT NULL,
762 date DATE NULL
763 ) ENGINE = InnoDB;
764 START TRANSACTION;
765 INSERT INTO t VALUES(1, ´apple´, NULL);
766 UPDATE t SET name = ´pear´, date = ´2009-01-01´ WHERE id = 1;
767 DELETE FROM t WHERE id = 1;
768 COMMIT;
769 By default, mysqlbinlog displays row events encoded as base-64 strings
771 using BINLOG statements. Omitting extraneous lines, the output for the
772 row events produced by the preceding statement sequence looks like
773 this:
774 shell> mysqlbinlog log_file
776 ...
777 # at 218
778 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
779 BINLOG ´
780 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
781 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
782 ´/*!*/;
783 ...
784 # at 302
785 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
786 BINLOG ´
787 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
788 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
789 ´/*!*/;
790 ...
791 # at 400
792 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
793 BINLOG ´
794 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
795 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
796 ´/*!*/;
797 To see the row events as comments in the form of “pseudo-SQL”
799 statements, run mysqlbinlog with the --verbose or -v option. The output
800 will contain lines beginning with ###:
801 shell> mysqlbinlog -v log_file
803 ...
804 # at 218
805 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
806 BINLOG ´
807 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
808 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
809 ´/*!*/;
810 ### INSERT INTO test.t
811 ### SET
812 ### @1=1
813 ### @2=´apple´
814 ### @3=NULL
815 ...
816 # at 302
817 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
818 BINLOG ´
819 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
820 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
821 ´/*!*/;
822 ### UPDATE test.t
823 ### WHERE
824 ### @1=1
825 ### @2=´apple´
826 ### @3=NULL
827 ### SET
828 ### @1=1
829 ### @2=´pear´
830 ### @3=´2009:01:01´
831 ...
832 # at 400
833 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
834 BINLOG ´
835 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
836 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
837 ´/*!*/;
838 ### DELETE FROM test.t
839 ### WHERE
840 ### @1=1
841 ### @2=´pear´
842 ### @3=´2009:01:01´
843 Specify --verbose or -v twice to also display data types and some
845 metadata for each column. The output will contain an additional comment
846 following each column change:
847 shell> mysqlbinlog -vv log_file
849 ...
850 # at 218
851 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
852 BINLOG ´
853 fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
854 fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
855 ´/*!*/;
856 ### INSERT INTO test.t
857 ### SET
858 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
859 ### @2=´apple´ /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
860 ### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
861 ...
862 # at 302
863 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
864 BINLOG ´
865 fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
866 fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
867 ´/*!*/;
868 ### UPDATE test.t
869 ### WHERE
870 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
871 ### @2=´apple´ /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
872 ### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
873 ### SET
874 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
875 ### @2=´pear´ /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
876 ### @3=´2009:01:01´ /* DATE meta=0 nullable=1 is_null=0 */
877 ...
878 # at 400
879 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
880 BINLOG ´
881 fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
882 fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
883 ´/*!*/;
884 ### DELETE FROM test.t
885 ### WHERE
886 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
887 ### @2=´pear´ /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
888 ### @3=´2009:01:01´ /* DATE meta=0 nullable=1 is_null=0 */
889 You can tell mysqlbinlog to suppress the BINLOG statements for row
891 events by using the --base64-output=DECODE-ROWS option. This is similar
892 to --base64-output=NEVER but does not exit with an error if a row event
893 is found. The combination of --base64-output=DECODE-ROWS and --verbose
894 provides a convenient way to see row events only as SQL statements:
895 shell> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
897 ...
898 # at 218
899 #080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
900 ### INSERT INTO test.t
901 ### SET
902 ### @1=1
903 ### @2=´apple´
904 ### @3=NULL
905 ...
906 # at 302
907 #080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
908 ### UPDATE test.t
909 ### WHERE
910 ### @1=1
911 ### @2=´apple´
912 ### @3=NULL
913 ### SET
914 ### @1=1
915 ### @2=´pear´
916 ### @3=´2009:01:01´
917 ...
918 # at 400
919 #080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
920 ### DELETE FROM test.t
921 ### WHERE
922 ### @1=1
923 ### @2=´pear´
924 ### @3=´2009:01:01´
925 Note
928 You should not suppress BINLOG statements if you intend to
929 re-execute mysqlbinlog output.
930 The SQL statements produced by --verbose for row events are much more
932 readable than the corresponding BINLOG statements. However, they do not
933 correspond exactly to the original SQL statements that generated the
934 events. The following limitations apply:
935 · The original column names are lost and replaced by @N, where N is a
937 column number.
938 · Character set information is not available in the binary log, which
940 affects string column display:
941 · There is no distinction made between corresponding binary and
943 nonbinary string types (BINARY and CHAR, VARBINARY and VARCHAR,
944 BLOB and TEXT). The output uses a data type of STRING for
945 fixed-length strings and VARSTRING for variable-length strings.
946 · For multi-byte character sets, the maximum number of bytes per
948 character is not present in the binary log, so the length for
949 string types is displayed in bytes rather than in characters.
950 For example, STRING(4) will be used as the data type for values
951 from either of these column types:
952 CHAR(4) CHARACTER SET latin1
954 CHAR(2) CHARACTER SET ucs2
955 · Due to the storage format for events of type UPDATE_ROWS_EVENT,
957 UPDATE statements are displayed with the WHERE clause preceding
958 the SET clause.
959 Proper interpretation of row events requires the information from the
961 format description event at the beginning of the binary log. Because
962 mysqlbinlog does not know in advance whether the rest of the log
963 contains row events, by default it displays the format description
964 event using a BINLOG statement in the initial part of the output.
965 If the binary log is known not to contain any events requiring a BINLOG
967 statement (that is, no row events), the --base64-output=NEVER option
968 can be used to prevent this header from being written.
969
971 Copyright © 1997, 2011, Oracle and/or its affiliates. All rights
972 reserved.
973 This documentation is free software; you can redistribute it and/or
975 modify it only under the terms of the GNU General Public License as
976 published by the Free Software Foundation; version 2 of the License.
977 This documentation is distributed in the hope that it will be useful,
979 but WITHOUT ANY WARRANTY; without even the implied warranty of
980 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
981 General Public License for more details.
982 You should have received a copy of the GNU General Public License along
984 with the program; if not, write to the Free Software Foundation, Inc.,
985 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
986 http://www.gnu.org/licenses/.
987
990 For more information, please refer to the MySQL Reference Manual, which
991 may already be installed locally and which is also available online at
992 http://dev.mysql.com/doc/.
993
995 Oracle Corporation (http://dev.mysql.com/).
996MySQL 5.1 10/26/2011 MYSQLBINLOG(1)