MySQL Reference Manual - 4.0.20

[ < ]

[ > ]

[ << ]

[ Up ]

[ >> ]

[Top]

[Contents]

[Index]

[ ? ]

8.5 The `mysqlbinlog` Binary Log Utility

The binary log files that the server generates are written in binary format. To examine these files in text format, use the mysqlbinlog utility. It is available as of MySQL 3.23.14.

Invoke mysqlbinlog like this:

shell> mysqlbinlog [options] log-file ...

For example, to display the contents of the binary log `binlog.000003', use this command:

shell> mysqlbinlog binlog.0000003

The output includes all statements contained in `binlog.000003', together with other information such as the time each statement took, the thread ID of the client that issued it, the timestamp when it was issued, and so forth.

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. However, this is deprecated because we instead want to make it easy to apply binary logs to a local MySQL server.

When you read remote binary logs, the connection parameter options can be given to indicate how to connect to the server, but they are ignored unless you also specify the --read-from-remote-server option. These options are --host, --password, --port, --protocol, --socket, and --user.

You can also use mysqlbinlog to read relay log files written by a slave server in a replication setup. Relay logs have the same format as binary log files.

The binary log is discussed further in The Binary Log.

mysqlbinlog supports the following options:

--help, -?: Display a help message and exit.
--database=db_name, -d db_name: List entries for just this database (local log only).
--force-read, -f: Force reading of unknown binary log events.
--host=host_name, -h host_name: Get the binary log from the MySQL server on the given host.
--local-load=path, -l path: Prepare local temporary files for LOAD DATA INFILE in the specified directory.
--offset=N, -o N: Skip the first N entries.
--password[=password], -p[password]: The password to use when connecting to the server. Note that if you use the short option form (-p), you cannot have a space between the option and the password. If no password is given on the command line, you will be prompted for one.
--port=port_num, -P port_num: The TCP/IP port number to use for connecting to a remote server.
--position=N, -j N: Start reading the binary log at position N.
--protocol={TCP | SOCKET | PIPE | MEMORY}: The connection protocol to use. New in MySQL 4.1.
--read-from-remote-server, -R: Read the binary log from a MySQL server. Any connection parameter options are ignored unless this option is given as well. These options are --host, --password, --port, --protocol, --socket, and --user.
--result-file=name, -r name: Direct output to the given file.
--short-form, -s: Display only the statements contained in the log, without any extra information.
--socket=path, -S path: The socket file to use for the connection.
--user=user_name, -u user_name: The MySQL username to use when connecting to a remote server.
--version, -V: Display version information and exit.

You can also set the following variable by using --var_name=value options:

open_files_limit: Specify the number of open file descriptors to reserve.

You can pipe the output of mysqlbinlog into a mysql client to execute the statements contained in the binary log. This is used to recover from a crash when you have an old backup (see section Database Backups):

shell> mysqlbinlog hostname-bin.000001 | mysql

Or:

shell> mysqlbinlog hostname-bin.[0-9]* | mysql

You can also redirect the output of mysqlbinlog to a text file instead, if you need to modify the statement log first (for example, to remove statements that you don't want to execute for some reason). After editing the file, execute the statements that it contains by using it as input to the mysql program.

mysqlbinlog has the --position option, which prints only those statements with an offset in the binary log greater than or equal to a given position.

If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:

shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!

Processing binary logs this way using different connections to the server will cause problems if the first log file contains a CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server will drop the temporary table. When the second mysql process attempts to use the table, the server will report "unknown table."

To avoid problems like this, use a single connection to execute the contents of all binary logs that you want to process. Here is one way to do that:

shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql

Another approach is to do this:

shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql
shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql
shell> mysql -e "source /tmp/statements.sql"

In MySQL 3.23, the binary log did not contain the data to load for LOAD DATA INFILE statements. To execute such a statement from a binary log file, the original data file was needed. Starting from MySQL 4.0.14, the binary log does contain the data, so mysqlbinlog can produce output that reproduces the LOAD DATA INFILE operation without the original data file. mysqlbinlog copies the data to a temporary file and writes a LOAD DATA LOCAL INFILE statement that refers to the file. The default location of the directory where these files are written is system-specific. To specify a directory explicitly, use the --local-load option.

Because mysqlbinlog converts LOAD DATA INFILE statements to LOAD DATA LOCAL INFILE statements (that is, it adds LOCAL), both the client and the server that you use to process the statements must be configured to allow LOCAL capability. See section LOAD DATA LOCAL.

Warning: The temporary files created for LOAD DATA LOCAL statements are not automatically deleted because they are needed until you actually execute those statements. You should delete the temporary files yourself after you no longer need the statement log. The files can be found in the temporary file directory and have names like `original_file_name-#-#'.

In the future, we will fix this problem by allowing mysqlbinlog to connect directly to a mysqld server. Then it will be possible to safely remove the log files automatically as soon as the LOAD DATA INFILE statements have been executed.

Before MySQL 4.1, mysqlbinlog could not prepare output suitable for mysql if the binary log contained intertwined statements originating from different clients that used temporary tables of the same name. This is fixed in MySQL 4.1.