Clean up binary log files
5 stars based on
Description The server's binary log consists of files containing "events" that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a slave server in a replication setup because relay logs have the same format as binary logs. The binary log and relay log are discussed further in Section 5. Invoke mysqlbinlog like this: For example, to display the contents of the binary log file named binlog.
For statement-based logging, event information includes the SQL statement, the ID of the server on which mysqlbinlog bin log files mysql remove was executed, the timestamp when the statement was executed, how much time it took, and so forth.
For row-based logging, the event indicates a row change rather than an SQL statement. Events are preceded by header comments that provide additional information. The second line starts with a date and time indicating when the statement started on the server where the event originated.
For replication, this timestamp is propagated to slave servers. On a slave, it is the difference of the end execution time on the slave minus the beginning execution time on the master. The difference serves as an indicator of how much replication lags behind the master. Zero means that no error occurred. The output from mysqlbinlog can be re-executed for example, by using it as input to mysql to redo the statements in the log. This is useful for recovery operations after a server crash.
For other usage examples, see the discussion later in this section and in Section mysqlbinlog bin log files mysql remove. Normally, you use mysqlbinlog to read binary log files directly and apply them to the local MySQL server.
It is also possible to read binary logs from a remote server by using the --read-from-remote-server option. To read remote binary logs, the connection parameter options can be given to indicate how to connect to the server.
These options are --host--password--port--protocol--socketand --user ; they are ignored except when you also use the --read-from-remote-server option.
Display a help message and exit. The option has these permissible values not case sensitive: Note Automatic BINLOG display is the only safe behavior if you intend to use the output of mysqlbinlog to re-execute binary log file contents.
The other option values are intended only for debugging or testing purposes because they may produce output that does not include all events in executable form. The --baseoutput option was introduced in MySQL 5. The option values described in the preceding list may be used as of MySQL 5.
It is not available in standard MySQL 5. The --database option for mysqlbinlog is similar to the --binlog-do-db option for mysqldbut can be used to specify only one database. If --database is given multiple times, only the last instance is used. The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the mysqlbinlog bin log files mysql remove of --binlog-do-db depend on whether statement-based or row-based logging is in use.
The --database option works as follows: The database being created, altered, or dropped is considered to be the default database when determining whether to output the statement. Suppose that the binary log was created by executing these statements using statement-based-logging: The default database has no effect on this.
Suppose that the binary log just described was created using row-based logging rather than statement-based logging. In particular, no cross-database updates should be used. This option did not work correctly for mysqlbinlog with row-based logging prior to MySQL 5. The default is 'd: This option was added in MySQL 5. This is useful for avoiding an endless loop if you use the --to-last-log option and are sending the output to the same MySQL server. This option also is useful when restoring after a crash to avoid duplication of the statements you have logged.
Without this option, mysqlbinlog stops if it reads such an event. The hex output can be helpful for replication debugging. Important These temporary files are not automatically removed by mysqlbinlog or any other MySQL program.
If you use mysqlbinlog bin log files mysql remove short option form -pyou cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysqlbinlog prompts for one. Mysqlbinlog bin log files mysql remove a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line. It is useful when the other connection mysqlbinlog bin log files mysql remove normally would cause a protocol to be used other than the one you want.
For details on the permissible values, see Section 4. Any connection parameter options are ignored mysqlbinlog bin log files mysql remove this option is given as well. These options are --host--password--port--protocol--socketand --user.
This option requires that the remote server be running. It works only for binary log files on the remote server, not relay log files. This option is available as of MySQL 5. If the binary log was written by a mysqld with server-id-bits set to less than 32 and user data stored in the most significant bit, running mysqlbinlog with --server-id-bits set to 32 enables this data to be seen. This is for testing only, and should not be used in production systems. The datetime value is relative to the local time zone on the machine where you run mysqlbinlog.
This option applies to the first log file named on the command line. This option mysqlbinlog bin log files mysql remove useful for point-in-time recovery. See the description of the --start-datetime option for information about the datetime value.
This option applies to the last log file named on the mysqlbinlog bin log files mysql remove line. If you send the output to the same MySQL server, this may lead to an mysqlbinlog bin log files mysql remove loop.
This option requires --read-from-remote-server. If this option is given twice, the output includes mysqlbinlog bin log files mysql remove to indicate column data types and some metadata.
You can pipe the output of mysqlbinlog into the mysql client to execute the events contained in the binary log. This technique is used to recover from a crash when you have an old backup see Section 7.
After editing the file, execute the statements that it contains by using it as input to the mysql program: It also has options to stop and start when it sees an event with a given date and time. This enables you to perform point-in-time recovery using the --stop-datetime option to be able to say, for example, "roll forward my databases to how they were today at If you have more than one binary log to execute on the MySQL server, the safe method is to process them mysqlbinlog bin log files mysql remove using a single mysqlbinlog bin log files mysql remove to the server.
Here is an example that demonstrates what may be unsafe: When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports "unknown table.
Here is one way to do so: The default location of the directory where these files are written is system-specific. To specify a directory explicitly, use the --local-load option. You should delete the temporary files yourself after you no longer need the statement log. Mysqlbinlog Hex Dump Format The --hexdump option causes mysqlbinlog to produce a hex dump of the binary log contents: This format is subject to change.
The Binary Log . The byte position within the log file. In the example shown, '9d fc 5c 43' is the representation of ' The event type code. The following table lists the possible type codes. The server ID of the master that created the event. The size in bytes of the event. The position of the next event in the original master log file. Currently, the following flags are used. The others mysqlbinlog bin log files mysql remove reserved for future use.
Mysqlbinlog Row Event Display The following examples illustrate how mysqlbinlog displays row events that specify data modifications. These options are available as of MySQL 5. Suppose that the server is using row-based binary logging and that you execute the following sequence of statements: Mysqlbinlog bin log files mysql remove extraneous lines, the output for the row events produced by the preceding statement sequence looks like this: The output will contain lines beginning with: The output will contain an additional comment following each column change: However, they do not correspond exactly to the original SQL statements that generated the events.