7.5 The mysqlbinlog Binary Log Utility

7.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 Section 4.8.4, "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 4.6.1, "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.

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.