MYSQL LOGBIN 数据日志恢复数据库随笔

查看指定的二进制日志中的事件（MYSQL命令行）
mysql> show binlog events in 'binlogfullpath';

查看二进制日志中的事件（MYSQL命令行）
mysql> show binlog events;

查看服务器上的二进制日志（MYSQL命令行）
mysql> show binary logs;

重新开始一个新的日志文件

mysql> flush logs;

 

 


======================================================
BINLOG导出SQL文件
======================================================

C:Program FilesMySQLMySQL Server 5.7in>mysqlbinlog binlogpath > sqlfilepath  -d dbname -f --stop-datetime stopdatetime  --start-datetime startdatetime
-------------参数解释----------------
binlogpath BINLOG完整路径（D:data_backupmysql_binlogin-log.000013）
sqlfilepath 导出到SQL文件的完整路径：（d:a.13.11.sql）
stopdatetime BINLOG导出筛选结束时间：（2017/07/17T13:33:26）
startdatetime BINLOG导出筛选开始时间：（2017/07/17T09:43:02）
dbname BINLOG导出筛选数据名称



======================================================
命令行执行SQL文件
======================================================

mysql -f -uroot -ppwd -Ddbname < file

---------- 参数解释-------------
-f 忽略错误，继续执行
-u 数据库username
-p 数据pwd 特殊字符转义符^ 
-D 执行文件的目标数据库
file 执行的sql文件完整路径

 
======================================================
另新启一个BINLOG文件 MYSQL COMMAND 下执行
======================================================
flush logs;

======================================================
COMMAND 下执行；导出可阅读SQL文件
======================================================

C:MySQL Server 5.7in>mysqlbinlog "D:dataW83201-bin.000009" -d "xxxdb" --start-datetime="2018-03-25 00:00:00" --base64-output=decode-rows -v > "xxxdb.sql"

-d database
--start-datetime 开始时间筛选



======================================================
BAT 为MYSQL执行多个文件，并记录完成时间日志，有待优化为 FOR
======================================================

@echo off 
mysql -uroot -ppwd -Ddbname<D:ccin-log.000010.sql
set d=%date:~0,10%
set t=%time:~0,8%
echo %d%%t% bin-log.000010.sql execed >> d:cc
esult.txt

mysql -uroot -p1q2w3e4r5t^^Y^&U*I(O)P -Dleizuwenhuadb<D:ccin-log.000011.sql
set d1=%date:~0,10%
set t1=%time:~0,8%
echo %d1%%t1% bin-log.000011.sql execed >> d:cc
esult.txt

mysql -uroot -p1q2w3e4r5t^^Y^&U*I(O)P -Dleizuwenhuadb<D:ccin-log.000012.sql
set d2=%date:~0,10%
set t2=%time:~0,8%
echo %d2%%t2% bin-log.000012.sql execed >> d:cc
esult.txt

mysql -uroot -p1q2w3e4r5t^^Y^&U*I(O)P -Dleizuwenhuadb<D:ccin-log.000013.sql
set d3=%date:~0,10%
set t3=%time:~0,8%

echo %d3%%t3% bin-log.000013.sql execed >> d:cc
esult.txt
echo %d3%%t3% all exec complate >> d:cc
esult.txt

pause
　　

-----------------------------------
mysqlbinlog 工具命令详解
-----------------------------------

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-master=BINLOG-DUMP-NON-GTIDS.

  --read-from-remote-master=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-master=BINLOG-DUMP-GTIDS is combined

                      with --exclude-gtids, transactions can be filtered out on

                      the master avoiding 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.

  --secure-auth       Refuse client connecting to server if it uses old

                      (pre-4.1.1) protocol. Deprecated. Always TRUE

  --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.

  --shared-memory-base-name=name

                      Base name of shared memory.

  -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.

  --ssl-mode=name     SSL connection mode.

  --ssl               Deprecated. Use --ssl-mode instead.

                      (Defaults to on; use --skip-ssl to disable.)

  --ssl-verify-server-cert

                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.

  --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

  --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 slave server_id used for --read-from-remote-server

                      --stop-never. This option cannot be used together with

                      connection-server-id.

  --connection-server-id=#

                      The slave server_id used for --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

                      -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.

 

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)

raw                               FALSE

result-file                       (No default value)

secure-auth                       TRUE

server-id                         0

server-id-bits                    32

set-charset                       (No default value)

shared-memory-base-name           (No default value)

short-form                        FALSE

socket                            (No default value)

ssl                               TRUE

ssl-verify-server-cert            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)

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                  18432

verify-binlog-checksum            FALSE

binlog-row-event-max-size         4294967040

skip-gtids                        FALSE

include-gtids                     (No default value)

exclude-gtids                     (No default value)

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-master=BINLOG-DUMP-NON-GTIDS.  --read-from-remote-master=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-master=BINLOG-DUMP-GTIDS is combined                      with --exclude-gtids, transactions can be filtered out on                      the master avoiding 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.  --secure-auth       Refuse client connecting to server if it uses old                      (pre-4.1.1) protocol. Deprecated. Always TRUE  --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.  --shared-memory-base-name=name                      Base name of shared memory.  -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.  --ssl-mode=name     SSL connection mode.  --ssl               Deprecated. Use --ssl-mode instead.                      (Defaults to on; use --skip-ssl to disable.)  --ssl-verify-server-cert                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.  --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  --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 slave server_id used for --read-from-remote-server                      --stop-never. This option cannot be used together with                      connection-server-id.  --connection-server-id=#                      The slave server_id used for --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                      -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.
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                   FALSEforce-if-open                     TRUEforce-read                        FALSEhexdump                           FALSEhost                              (No default value)idempotent                        FALSElocal-load                        (No default value)offset                            0plugin-dir                        (No default value)port                              0read-from-remote-server           FALSEread-from-remote-master           (No default value)raw                               FALSEresult-file                       (No default value)secure-auth                       TRUEserver-id                         0server-id-bits                    32set-charset                       (No default value)shared-memory-base-name           (No default value)short-form                        FALSEsocket                            (No default value)ssl                               TRUEssl-verify-server-cert            FALSEssl-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)start-datetime                    (No default value)start-position                    4stop-datetime                     (No default value)stop-never                        FALSEstop-never-slave-server-id        -1connection-server-id              -1stop-position                     18446744073709551615to-last-log                       FALSEuser                              (No default value)open-files-limit                  18432verify-binlog-checksum            FALSEbinlog-row-event-max-size         4294967040skip-gtids                        FALSEinclude-gtids                     (No default value)exclude-gtids                     (No default value)