MySQL's binlog detailed

Binlog is a binary log file of MySQL record operation, there are three formats to choose from, but the old SBR is not suitable for most business needs now, so most of them are recommended to use MBR and RBR, that is, mixed or row, and the reason for parsing him, almost only one, is to restore the database, or anti-compilation to restore the database, the purpose is the same.

The following is a description of the Mysqlbinlog based on mysql5.6, please be informed.

Turn to the following:

A simple look at how to use Binlog

View only the contents of the first Binlog file (not recommended)

Show Binlog events;

View the contents of a specified Binlog file (not recommended)

Show Binlog events in ' mysql-bin.000002 ';

View the Binlog file that is currently being written

Show Master Status\g

Get a list of binlog files

Show binary logs;

Can be opened directly, may not be intuitive, and will not stop brushing the screen, with caution

Mysqlbinlog mysql-bin.000002

View binary log in row format

MYSQLBINLOG-VV--base64-output=decode-rows filename

Based on start/end time

Mysqlbinlog--start-datetime= ' 2013-09-10 00:00:00 '--stop-datetime= ' 2013-09-10 01:01:01 '-d library name binary file

Based on POS value

Mysqlbinlog--start-postion=107--stop-position=1000-d Library name binary file

#恢复单一数据库的binlog

Mysqlbinlog-d dbname binlog.00002

A completed case reference:

MYSQLBINLOG-VV--base64-output=decode-rows--start-datetime= ' 2016-06-15 20:00:00 '--stop-datetime= ' 2016-06-15 21:00:00 '-D baiyang mysql-bin.000011 > T.txt

Export the statement of a specific library in Binlog to T.txt

Here's a look at the parameter description :

#参数说明

--base64-output=value displaying binary log content in BASE-64 encoded format
Auto ("Automatic") or Unspec ("unspecified") parameters automatically display the contents of the binary statement, if the--base64-output parameter is not specified, the effect is equivalent to--base64-output=auto;
The never parameter causes the binary statement to not be displayed;
Decode-rows decoding the binary log processing.

The more common is--base64-output=decode-rows: The SQL changes brought by the row pattern are displayed

--bind-address=name binding IP address (for some cases of unusual IP access)

directory path to the--character-sets-dir=name character set file (used in a non-normal character set)

-C,--compress enable compression mode (reduce bandwidth usage)

-D db_name,--database=db_name lists only entries for this database (only local logs).

--debug-check Check the memory and open file usage and exit.

--debug-info Print some debugging information and exit.

--default-auth=name default client Authentication plug-in Path

-d,disable-logs-bin 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.

-F,--force-if-open Force open a binary log file that does not shut down normally (enabled by default, closed by--skip-force-if-open)

-F,--force-read forces an unrecognized binary log event to open, and if this option is used, mysqlbinlog the binary log event that is not recognized, it prints a warning, ignores the event, and continues. Without this option, Mysqlbinlog reads this type of event and stops.

-H,--hexdump displays the hexadecimal dump of the log in the note. This output can help with debugging during the copy process.

-H,--host=name gets the binary log of the MySQL server on the given host.

-L,--local-load=name preprocessing the local temporary file for load DATA infile in the specified directory.

-O,--offset=# skips the first n entries.

-P,--password[=name] use a password 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 thepassword or-p option on the command line, you are prompted to enter a password.

--plugin-dir=name client Plug-in directory

-P,--port=# the TCP/IP port number used to connect to the remote server.

--protocol=name uses that connection protocol, which can be: TCP, Socket, pipe,memory

-R,--read-from-remote-server reads the binary log from the MySQL server. If you do not include this option, the parameter options for any connection server will be invalid. These options are –host, –password, –port, –protocol, –socket, and –user. Can replace Read-from-remote-master=binlog-dump-non-gtids.

--read-from-remote-master=name read binary logs from MySQL server. By setting the Com_binlog_dump or Com_binlog_dump_ Gtid command to set parameters Binlog-dump-non-gtids or Binlog-dump-gtids, if set--read-from-remote-master= Binlog-dump-gtids and combined with--exclude-gtids, you can save some network traffic

--raw needs to be used with-R. Output raw binary log data, not SQL, and output to the log file.

-R,--result-file=name the output to the given file. When used with--raw, this is a file pre-order.

--secure-auth If the client is lower than pre-4.1.1, the connection will be rejected and turned on by default and can be turned off by--skip-secure-auth

--server-id=# only extracts the binary log for the given server ID, which is the ID value of the my.cnf Server-id

--server-id-bits=# set Server-id in a prominent position

Add set NAMES Character_set prompt to set character set when--set-charset=name output

-S,--short-form displays only the statements contained in the log and does not display additional information. This is for testing only and should not be used in production systems. If you want to suppress base64-output output, consider using--base64-output=never instead.

-S,--socket=name uses socket sockets to connect, usually native

--ssl Connect using the SSL protocol (typically not used)

--ssl-ca=name Use CA certificate method of SLL to connect, similarly, the following several options are various certificates and key authentication method of SSL login, do not introduce each.

--ssl-capath=name,--ssl-cert=name,--ssl-cipher=name,--ssl-key=name,--ssl-crl=name,--ssl-crlpath=name,-- Ssl-verify-server-cert

--start-datetime=datetime starts reading from the binary log of an event that is equal to or later than the DateTime parameter in the 1th date time. 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. Example: Mysqlbinlog–start-datetime= "2004-12-2511:25:56" binlog.000003

-j,--start-position=# starts reading from the event that the 1th position in the binary log equals the n parameter.

--stop-datetime=datetime stops reading from the binary log for events that are equal to or later than the DateTime parameter in the 1th date time. And the--start-datetime option uses settings to export specific time period information to help reduce the result output.

--stop-never waits for more log transfers instead of exiting the current command until the server is disconnected, implicitly setting--to-last-log

--stop-never-slave-server-id=# the value of the From library ID of the current command, when used with--read-from-remote-server or--stop-never, the command will pull the binlog data as if it were from the library. So a unique Server-id value is needed to identify him.

--stop-position=# stops reading from the event that the 1th position in the binary log is equal to and greater than the N parameter. With the--start-position option, you can use settings to export binlog information for a specific position interval to help reduce the result output.

-T,--to-last-log does not stop at the end of the binary log requested in the MySQL server, but continues 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.

-U,--user=name the MySQL user name to use when connecting to the remote server.

-V,--verbose verbose mode, displays the SQL statement brought by the statement mode,-VV the comment that adds the column type.

-V,--version displays version information and exits

--open-files-limit=# Specifies the number of open file descriptors to keep

-C,--verify-binlog-checksum verify checksum binlog log

--binlog-row-event-max-size=# maximum binlog log file capacity size, this value must be a multiple of 256.

--skip-gtids does not print global transaction identifier information (GTID), for example: SET gtid_next= ... etc

--include-gtids=name Print Gtid information for a given value

--exclude-gtids=name printing information for gtid other than a given value

--rewrite-db=name a database name that is different from the original when updating the database, for example: rewrite-db= ' from->to '.

-help,-?

Displays the help message and exits.

About the creation of Binlog server:

MySQL used to know that MySQL support master-slave architecture, and sometimes we just want to backup Binlog log, but specifically to take a library seemingly unscientific, and the MySQL mechanism is not to binlog down even if, but also write into the database occupy a certain space, Previously introduced Blackhold engine, and now more convenient, because mysql5.6 introduced the MySQL binlog server, quite convenient, quite simple, Binlog was pulled over, and as long as the process does not fall, always pull.

Turn to the point, because the method is very simple, is a command past, so do not be very nervous, as to the meaning of the parameters, please see above

The command is as follows:

Mysqlbinlog-r--raw--host=*.*.*.*--user=root--password=x xxxxxxxxx--stop-never--stop-never-slave-server-id=56789 mysql-bin.000007

That's it, Binlog's here, look at the catalogue.

-RW-RW----1 root root 1073741921 May 19:15 mysql-bin.000007

-RW-RW----1 root root 1073741954 June 2 12:14 mysql-bin.000008

-RW-RW----1 root root 1073743557 June 8 10:03 mysql-bin.000009

-RW-RW----1 root root 1073742035 June 16:33 mysql-bin.000010

-RW-RW----1 root root 1073742000 June 10:38 mysql-bin.000011

-RW-RW----1 root root 1073742053 June 09:18 mysql-bin.000012

Originally I have run so much, hehe ~!

And look at the database status

| 66379 | Tencentroot | 10.*.*.*:47303 | NULL | Binlog Dump | 6932150 | Master has sent all binlog to slave; Waiting for Binlog to be updated | NULL |

| 14277762 | Tencentroot | 127.0.0.1:53878 | NULL |      Sleep |                                                                       25 | | NULL |

| 20949882 | Root | 10.*.*.*:2080 | NULL | Binlog Dump | 2937110 | Master has sent all binlog to slave; Waiting for Binlog to be updated | Null

There are two processes in pull binlog, no doubt, one of them is real from the library and the other is my binlogserver.

Complete.

MySQL's binlog detailed