MySQL mysqlbinlog utility

mysql binlog utility cheatsheet

mysql bin log --> binary log processing utility in MySQL database

mysqlbinlog log_file

mysqlbinlog -v log_file
mysqlbinlog -vv log_file

mysqlbinlog logfile_1 logfile_2 ... logfile_n > out.sql
mysqlbinlog logfile_1 logfile_2 ... logfile_n | mysql

mysqlbinlog mysql-binlog.003510 | more

/usr/local/mysql/bin/mysqlbinlog --stop-datetime="2020-11-10 05:00:00" /home/mysql/binlog/mysql-bin.000033

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2020-09-21 16:30:00" /home/mysql/binlog/mysql-bin.000006 /home/mysql/binlog/mysql-bin.000008 /home/mysql/binlog/mysql-bin.000009 | /usr/local/mysql/bin/mysql -u mysqldba -p test

mysqlbinlog -v --base64-output=decode-rows mysql-bin-log.00052

mysqlbinlog --base64-output=decode-rows --verbose /logs/mysql-bin-log.00052 > 52.log

mysqlbinlog --no-defaults --base64-output=decode-rows --verbose --verbose /logs/dba-mysql-bin.0000395 > 395.log

mysqlbinlog --base64-output=never mysqld-bin.000001

mysqlbinlog --base64-output=always mysqld-bin.000001

mysqlbinlog --base64-output=auto mysqld-bin.000001

sudo mysqlbinlog --start-position=3214331 /mysql/mysql_logs/mysql-bin.00920 | more
mysqlbinlog --start-position=12330 mysql-binlog.001002 mysql-binlog.001003 mysql-binlog.001004 | mysql --host=host_name -u root -p

mysqlbinlog --stop-position=15028 mysqld-bin.000001 > upto-15028.out

mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out

mysqlbinlog --start-datetime="2021-08-16 15:00:00" mysqld-bin.000001 

mysqlbinlog --stop-datetime="2021-08-16 19:00:00" mysqld-bin.000001 

mysqlbinlog /mysql/binlog/mysql-bin-log.000999 | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr

mysqlbinlog --read-from-remote-server --host=host_name --raw mysql-binlog.000130 mysql-binlog.000131 mysql-binlog.000132

mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never 
mysql-binlog.000190

mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001

mysqlbinlog -D mysqld-bin.000001

mysqlbinlog --disable-log-bin mysqld-bin.000001 

mysqlbinlog --debug-check mysqld-bin.000001

mysqlbinlog --debug-info mysqld-bin.000001

mysqlbinlog -o 10 mysqld-bin.000001

mysqlbinlog -r output.log mysqld-bin.000001

mysqlbinlog --no-defaults -vvv --base64-output=decode-rows --include-gtids='bd8eb8-adea-2839a:112' /var/lib/mysql/mysql-bin.0004;

mysqlbinlog --no-defaults -vvv --base64-output=decode-rows --include-gtids='c21907b4-f9d2-11eb-9b8a-080027272b51:123' binlog.000014;

Usage: mysqlbinlog [options] log-files

  -?, --help          Display this help and exit.

  --base64-output=name  Determine when the output statements should be base64-encoded BINLOG statements: 'never' disables it and works only for binlogs without row-based events; 'decode-rows' decodes row events into commented pseudo-SQL statements if the --verbose option is also given; 'auto' prints base64 only when necessary (i.e., for row-based events and format description events).  If no --base64-output[=name] option is given at all, the default is 'auto'.

  --bind-address=name IP address to bind to.

  --character-sets-dir=name  Directory for character set files.

  -d, --database=name List entries for just this database (local log only).

  --rewrite-db=name    Rewrite the row event to point so that it can be applied to a new database

  -#, --debug[=#]      This is a non-debug version. Catch this and exit.

  --debug-check        This is a non-debug version. Catch this and exit.

  --debug-info        This is a non-debug version. Catch this and exit.

  --default-auth=name Default authentication client-side plugin to use.

  -D, --disable-log-bin  Disable binary log. This is useful, if you enabled --to-last-log and are sending the output to the same MySQL server. This way you could avoid an endless loop. You would also like to use it when restoring after a crash to avoid duplication of the statements you already have. NOTE: you will need a SUPER privilege to use this option.

  -F, --force-if-open Force if binlog was not closed properly.  (Defaults to on; use --skip-force-if-open to disable.)

  -f, --force-read    Force reading unknown binlog events.

  -H, --hexdump        Augment output with hexadecimal and ASCII event dump.

  -h, --host=name      Get the binlog from server.

  -i, --idempotent    Notify the server to use idempotent mode before applying Row Events

  -l, --local-load=name           Prepare local temporary files for LOAD DATA INFILE in the specified directory.

  -o, --offset=#      Skip the first N entries.

  -p, --password[=name]  Password to connect to remote server.

  --plugin-dir=name    Directory for client-side plugins.

  -P, --port=#        Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).

  --protocol=name      The protocol to use for connection (tcp, socket, pipe, memory).

  -R, --read-from-remote-server  Read binary logs from a MySQL server. This is an alias for read-from-remote-source=BINLOG-DUMP-NON-GTIDS.

  --read-from-remote-master=name This option is deprecated and will be removed in a future version. Use read-from-remote-source instead.

  --read-from-remote-source=name  Read binary logs from a MySQL server through the COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by setting the option to either BINLOG-DUMP-NON-GTIDS or BINLOG-DUMP-GTIDS, respectively. If --read-from-remote-source=BINLOG-DUMP-GTIDS is combined with --exclude-gtids, transactions are filtered out on the source, to avoid unnecessary network traffic.

  --raw               Requires -R. Output raw binlog data instead of SQL statements, output is to log files.

  -r, --result-file=name   Direct output to a given file. With --raw this is a prefix for the file names.

  --server-id=#        Extract only binlog entries created by the server having the given id.

  --server-id-bits=#  Set number of significant bits in server-id

  --set-charset=name  Add 'SET NAMES character_set' to the output.

  -s, --short-form    Just show regular queries: no extra info and no row-based events. This is for testing only, and should not be used in production systems. If you want to suppress base64-output, consider using --base64-output=never instead.

  -S, --socket=name  The socket file to use for connection.

  --server-public-key-path=name  File path to the server public RSA key in PEM format.

  --get-server-public-key    Get server public key

  --ssl-mode=name      SSL connection mode.

  --ssl-ca=name        CA file in PEM format.

  --ssl-capath=name    CA directory.

  --ssl-cert=name      X509 cert in PEM format.

  --ssl-cipher=name    SSL cipher to use.

  --ssl-key=name      X509 key in PEM format.

  --ssl-crl=name      Certificate revocation list.

  --ssl-crlpath=name  Certificate revocation list path.

  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3

  --ssl-fips-mode=name   SSL FIPS mode (applies only for OpenSSL); permitted values are: OFF, ON, STRICT

  --tls-ciphersuites=name   TLS v1.3 cipher to use.

  --start-datetime=name    Start reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly).

  -j, --start-position=#  Start reading the binlog at position N. Applies to the first binlog passed on the command line.

  --stop-datetime=name  Stop reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should  probably use quotes for your shell to set it properly).

  --stop-never        Wait for more data from the server instead of stopping at the end of the last log. Implicitly sets --to-last-log but instead of stopping at the end of the last log it continues to wait till the server disconnects.

  --stop-never-slave-server-id=#  The server_id that is reported when connecting to a source server when using --read-from-remote-server --stop-never. This option is deprecated and will be removed in a future version. Use connection-server-id instead.

  --connection-server-id=#  The server_id that will be reported when connecting to a source server when using --read-from-remote-server. This option cannot be used together with stop-never-slave-server-id.

  --stop-position=#    Stop reading the binlog at position N. Applies to the last binlog passed on the command line.

  -t, --to-last-log    Requires -R. Will not stop at the end of the requested binlog but rather continue printing until the end of the last binlog of the MySQL server. If you send the output to the same MySQL server, that may lead to an endless loop.

  -u, --user=name     Connect to the remote server as username.

  -v, --verbose        Reconstruct pseudo-SQL statements out of row events.  -v adds comments on column data types.

  -V, --version        Print version and exit.

  --open-files-limit=#    Used to reserve file descriptors for use by this program.

  -c, --verify-binlog-checksum  Verify checksum binlog events.

  --binlog-row-event-max-size=#  The maximum size of a row-based binary log event in bytes. Rows will be grouped into events smaller than this size if possible. This value must be a multiple of 256.

  --skip-gtids        Do not preserve Global Transaction Identifiers; instead make the server execute the transactions as if they were new.

  --include-gtids=name  Print events whose Global Transaction Identifiers were provided.

  --exclude-gtids=name Print all events but those whose Global Transaction Identifiers were provided.

  --print-table-metadata  Print metadata stored in Table_map_log_event

  -C, --compress      Use compression in server/client protocol.

  --compression-algorithms=name    Use compression algorithm in server/client protocol. Valid values are any combination of 'zstd','zlib','uncompressed'.

  --zstd-compression-level=#  Use this compression level in the client/server protocol, in case --compression-algorithms=zstd. Valid range is between 1 and 22, inclusive. Default is 3.

  --require-row-format  Fail when printing an event that was not logged using row format or other forbidden events like Load instructions or the creation/deletion of temporary tables.

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

The following groups are read: mysqlbinlog client

The following options may be given as the first argument:

--print-defaults        Print the program argument list and exit.

--no-defaults            Don't read default options from any option file, except for login file.

--defaults-file=#        Only read default options from the given file #.

--defaults-extra-file=# Read this file after the global files are read.

--defaults-group-suffix=# Also read groups with concat(group, suffix)

--login-path=#          Read this path from the login file.

Variables (--variable-name=value)

and boolean options {FALSE|TRUE}  Value (after reading options)

--------------------------------- ----------------------------------------

base64-output                     (No default value)

bind-address                      (No default value)

character-sets-dir                (No default value)

database                          (No default value)

rewrite-db                        (No default value)

default-auth                      (No default value)

disable-log-bin                   FALSE

force-if-open                     TRUE

force-read                        FALSE

hexdump                           FALSE

host                              (No default value)

idempotent                        FALSE

local-load                        (No default value)

offset                            0

plugin-dir                        (No default value)

port                              0

read-from-remote-server           FALSE

read-from-remote-master           (No default value)

read-from-remote-source           (No default value)

raw                               FALSE

result-file                       (No default value)

server-id                         0

server-id-bits                    32

set-charset                       (No default value)

short-form                        FALSE

socket                            (No default value)

server-public-key-path            (No default value)

get-server-public-key             FALSE

ssl-ca                            (No default value)

ssl-capath                        (No default value)

ssl-cert                          (No default value)

ssl-cipher                        (No default value)

ssl-key                           (No default value)

ssl-crl                           (No default value)

ssl-crlpath                       (No default value)

tls-version                       (No default value)

tls-ciphersuites                  (No default value)

start-datetime                    (No default value)

start-position                    4

stop-datetime                     (No default value)

stop-never                        FALSE

stop-never-slave-server-id        -1

connection-server-id              -1

stop-position                     18446744073709551615

to-last-log                       FALSE

user                              (No default value)

open-files-limit                  64

verify-binlog-checksum            FALSE

binlog-row-event-max-size         4294967040

skip-gtids                        FALSE

include-gtids                     (No default value)

exclude-gtids                     (No default value)

print-table-metadata              FALSE

compress                          FALSE

compression-algorithms            (No default value)

zstd-compression-level            3

require-row-format                FALSE

Related MySQL Articles:  mysqlimport commands  mysql_config_editor commands