Mysqlbinlog Parameters and usage notes

Mysqlbinlog Usage Notes

The binary log file generated by the server is written in binary format. To check these text-formatted files, you should use the Mysqlbinlog utility.
This should be called Mysqlbinlog:
Shell> mysqlbinlog [options] log-files ... For example, to display the contents of the binary log binlog.000003, use the following command:
The shell> mysqlbinlog binlog.0000003 output includes all the statements contained in the BINLOG.000003, as well as other information such as the time spent on each statement, the thread ID issued by the customer, the timestamp at the time of the outgoing thread, and so on.

Mysqlbinlog options:
·
-help,-?
Displays the help message and exits.
·
-database=db_name,-d db_name
Lists only the entries for the database (local logs only).
·
–force-read,-f
With this option, if Mysqlbinlog reads a binary log event that it does not recognize, it prints a warning, ignores the event, and continues. Without this option, if Mysqlbinlog reads such an event, it stops.
·
–hexdump,-h
Displays the hexadecimal dump of the log in the note. This output can help with debugging during the copy process. This option was added in MySQL 5.1.2.
·
–host=host_name,-h host_name
Gets the binary log of the MySQL server on the given host.
·
–local-load=path,-l Pat
Preprocess the local temporary file for the load DATA infile in the specified directory.
·
–offset=n,-o N
Skips the first n entries.
·
–password[=password],-p[password]
The password to use when connecting to the server. If you use the Short option form (-p), there can be no spaces between options and passwords. If you do not have a password value after the –password or-P option on the command line, you are prompted to enter a password.
·
–port=port_num,-p Port_num
The TCP/IP port number used to connect to the remote server.
·
–position=n,-j N
Do not agree to use, should use –start-position.
·
–protocol={tcp | sockets | PIPE | -position

The connection protocol used.
·
–read-from-remote-server,-r
Read the binary log from the MySQL server. If this option is not given, any connection parameter options will be ignored. These options are –host, –password, –port, –protocol, –socket, and –user.
·
–result-file=name,-R name
Points the output to the given file.
·
–short-form,-s
Only the statements contained in the log are displayed, and no additional information is displayed.
·
–socket=path,-s Path
The socket file used for the connection.
·
–start-datetime=datetime
Reads from an event in the binary log where the 1th datetime is equal to or later than the DateTime parameter. The datetime value is relative to the local time zone on the machine that is running Mysqlbinlog. The value format should conform to the datetime or timestamp data type. For example:
Shell> mysqlbinlog–start-datetime= "2004-12-25 11:25:56″binlog.000003 this option can help point-to-point recovery.
·
–stop-datetime=datetime
Stop reading from the binary log for events that are equal to or later than the DateTime parameter in the 1th date time. See the –start-datetime option for a description of the datetime value. This option can help you recover in a timely manner.
·
–start-position=n
Start reading from the event that the 1th position in the binary log equals the n parameter.
·
–stop-position=n
Stops reading from the event that the 1th position in the binary log is equal to and greater than the N parameter.
·
–to-last-logs,-t
Instead of stopping at the end of the binary log requested in the MySQL server, continue printing until the end of the last binary log. If the output is sent to the same MySQL server, it will cause an infinite loop. This option requires –read-from-remote-server.
·
–disable-logs-bin,-d
Disables binary logging. If you use the –to-last-logs option to send the output to the same MySQL server, you can avoid an infinite loop. This option is also useful for crash recovery and avoids copying statements that have already been recorded. Note: This option requires Super permissions.
·
–user=user_name,-u user_name
The MySQL user name to use when connecting to the remote server.
·
–version,-v
Displays the version information and exits.
You can also set the following variables using the –var_name=value option:
·
Open_files_limit
Specifies the number of open file descriptors to keep.
The output of the Mysqlbinlog can be uploaded to the MySQL client to execute the statements contained in the binary log. If you have an old backup, this option is also useful in crash recovery:
shell> Mysqlbinlog hostname-bin.000001 | MySQL or:
Shell> Mysqlbinlog Hostname-bin. [0-9]* | MySQL If you need to modify the log with the statement first, you can also re-point the output of Mysqlbinlog to a text file. (for example, to delete a statement that you do not want to execute for some reason). After editing the file, enter it into the MySQL program and execute the statements it contains.
Mysqlbinlog has a –position option to print only those statements in the binary log whose offsets are greater than or equal to a given location (the given location must match the start of an event). It also has options for stopping or starting after seeing events for a given date and time. This allows for point-to-point recovery using the –stop-datetime option (for example, the ability to say "scroll the database to the location of the day").
If there are multiple binary logs to be executed on the MySQL server, the security method is to handle them in a single connection. Here is an example of what is unsafe:

shell> mysqlbinlog hostname-bin.000001 | MySQL # danger!! shell> mysqlbinlog hostname-bin.000002 | MySQL # danger!! When working with a binary log using a different connection to the server, if the 1th log file contains a Create temporary table statement, the 2nd log contains a statement that uses the temporary table, which can cause problems. When the 1th MySQL process ends, the server revokes the temporary table. When the 2nd MySQL process wants to use the table, the server reports "do not know the table."
To avoid such problems, use a connection to perform the content in all the binary logs that you want to process. Here's one way:
shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | MySQL Another method is:
shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sqlshell>  mysqlbinlog hostname-bin.000002 >>/tmp/statements.sqlshell> mysql-e "source/tmp/ Statements.sql "The output generated by Mysqlbinlog can regenerate a load data infile operation without needing the original data file. Mysqlbinlog Copy the data to a temporary file and write a load data LOCAL infile statement that references the file. The default location of the directory to which these files are written is determined by the system. To explicitly specify a directory, use the –local-load option.
because Mysqlbinlog can convert the load data infile statement to the load data LOCAL infile statement (that is, it adds local), the client and server that handles the statement must be configured to allow LOCAL operations.

Warning: Temporary files created for the load DATA Local statement are not automatically deleted because they are required before those statements are actually executed. You should delete the temporary file yourself after you no longer need the statement log. The file is located in the temp file directory and has a file name similar to original_file_name-#-#.
The –hexdump option produces a hexadecimal dump of the log content in the comments:

Mysqlbinlog Parameters and usage notes