复制

前往程序员福利页.......

1 binlog介绍

MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:
其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 因为有了数据更新的binlog,所以可以用于实时备份,与master/slave主从复制结合。

其二:自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。
二进制日志包括两类文件:二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

  1. binlog,即二进制日志,它记录了数据库上的所有改变.
  2. 改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.
  3. binlog格式
  • 基于语句,无法保证所有语句都在从库执行成功,比如update … limit 1;
  • 基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.

2 binlog参数介绍

log_bin
设置此参数表示启用binlog功能,并指定路径名称
log_bin_index
设置此参数是指定二进制索引文件的路径与名称
binlog_do_db
此参数表示只记录指定数据库的二进制日志
binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
binlog_cache_size
此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
binlog_cache_use:使用二进制日志缓存的事务数量
binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size
Binlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束
sync_binlog
这个参数直接影响mysql的性能和完整性
sync_binlog=0
当事务提交后,Mysql仅仅是将binlog_cache中的数据写入Binlog文件,但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
sync_binlog=n,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认设置sync_binlog=0,即不作任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统绷Crash,在文件系统缓存中的所有Binlog信息都会丢失

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。
 
sync_binlog选项控制mysql怎么刷新二进制日志到磁盘,默认是0,意味着mysql并不刷新,由操作系统自己决定什么时候刷新缓存到持久化设置,如果
这个值比0大,它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作
 
如果没有设置它为1,那么崩溃后可能导致二进制日志没有同步事务数据,这可以轻而易举的导致复制中断,并且使得及时恢复变得不可能,无论如何,可以
把这个值设置为1来获得安全的保障,这样就会要求mysql同步把二进制日志和事务日志这两个文件刷新到两个不同的位置,这可能需要磁盘寻道,相对来说
是个很慢的操作。
 
====================================================================================
 
默认sync_binlog=0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,但是风险也是最大的。
因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。
 
如果sync_binlog>0,表示每sync_binlog次事务提交,MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了,表示
每次事务提交,MySQL都会把binlog刷下去,是最安全但是性能损耗最大的设置。这样的话,在数据库所在的主机操作系统损坏或者突然掉电的情
况下,系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO,但是设置sync_binlog=1,多个事务同时提交,同样很大的影响MySQL和IO
性能。虽然可以通过group commit的补丁缓解,但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说,"sync_binlog"设置为0和
设置为1的系统写入性能差距可能高达5倍甚至更多。
 
所以很多MySQL DBA设置的sync_binlog并不是最安全的1,而是100或者是0。这样牺牲一定的一致性,可以获得更高的并发和性能。
 
====================================================================================
 
binlog二进制日志文件并不是每次写的时候同步到磁盘。因此当数据库所在操作系统发生宕机时,可能会有最后一部分数据没有写入二进制日志文件中,
这给恢复和复制带来了问题。
 
参数sync_binlog=[N]表示每写缓冲多次就同步到磁盘。如果将N设为1,即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志,这时写操作
不使用才做系统的缓冲来写二进制日志。(备注:该值默认为0,采用操作系统机制进行缓冲数据同步)。
    
当sync_binlog=1,还会存在另外问题。当使用InnoDB存储引擎时,在一个事务发出commit动作之前,由于sync_binlog设为1,因此会将二进制日
志立即写入磁盘。如果这时已经写入了二进制日志,但是提交还没有发生,并且此时发生了宕机,那么在Mysql数据库下次启动时,由于commit操作并
没有发生,所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息,不能被回滚。
 
这个问题,可以将innodb_support_xa设为1来解决,确保二进制日志和InnoDB存储引擎数据文件的同步。从官方解释来看,innodb_support_xa的
作用是分两类:
-  支持多实例分布式事务(外部xa事务),这个一般在分布式数据库环境中用得较多。
-  支持内部xa事务,说白了也就是说支持binlog与innodb redo log之间数据一致性。
mysql> show variables like '%log%'; 
+-----------------------------------------+---------------------------------------+
| Variable_name                           | Value                                 |
+-----------------------------------------+---------------------------------------+
| binlog_cache_size                       | 32768                                 |
| binlog_checksum                         | CRC32                                 |
| binlog_direct_non_transactional_updates | OFF                                   |
| binlog_error_action                     | IGNORE_ERROR                          |
| binlog_format                           | ROW                                   |
| binlog_gtid_simple_recovery             | OFF                                   |
| binlog_max_flush_queue_time             | 0                                     |
| binlog_order_commits                    | ON                                    |
| binlog_row_image                        | FULL                                  |
| binlog_rows_query_log_events            | OFF                                   |
| binlog_stmt_cache_size                  | 32768                                 |
| binlogging_impossible_mode              | IGNORE_ERROR                          |
| expire_logs_days                        | 5                                     |
| log_bin                                 | ON                                    |
| log_bin_basename                        | /data/mysql/mysql-bin                 |
| log_bin_index                           | /data/mysql/mysql-bin.index           |
| log_bin_trust_function_creators         | ON                                    |
| log_bin_use_v1_row_events               | OFF                                   |
| log_error                               | /var/log/mysql/error.log              |
| log_output                              | FILE                                  |
| log_queries_not_using_indexes           | OFF                                   |
| log_slave_updates                       | OFF                                   |
| log_slow_admin_statements               | OFF                                   |
| log_slow_slave_statements               | OFF                                   |
| log_throttle_queries_not_using_indexes  | 0                                     |
| log_warnings                            | 1                                     |
| max_binlog_cache_size                   | 18446744073709547520                  |
| max_binlog_size                         | 104857600                             |
| max_binlog_stmt_cache_size              | 18446744073709547520                  |
| max_relay_log_size                      | 0                                     |
| simplified_binlog_gtid_recovery         | OFF                                   |
| sql_log_bin                             | ON                                    |
| sql_log_off                             | OFF                                   |
| sync_binlog                             | 0                                     |
+-----------------------------------------+---------------------------------------+

3 binlog日志格式

Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW!

3.1.Statement:每一条会修改数据的sql都会记录在binlog中。

优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的IO性能问题。)

缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题(如sleep()函数, last_insert_id(),以及user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制:

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

同时在INSERT …SELECT 会产生比 RBR 更多的行级锁

3.2.Row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。

优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

3.3Mixedlevel: 是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。

 

Binlog基本配制与格式设定

1.基本配制

Mysql BInlog日志格式可以通过mysql的my.cnf文件的属性binlog_format指定。如以下:

binlog_format = MIXED //binlog日志格式

log_bin =目录/mysql-bin.log //binlog日志名

expire_logs_days = 7 //binlog过期清理时间

max_binlog_size 100m //binlog每个日志文件大小

2.Binlog日志格式选择

Mysql默认是使用Statement日志格式,推荐使用MIXED.

由于一些特殊使用,可以考虑使用ROWED,如自己通过binlog日志来同步数据的修改,这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed,解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。

3.mysqlbinlog格式选择

mysql对于日志格式的选定原则:如果是采用 INSERT,UPDATE,DELETE 等直接操作表的情况,则日志格式根据 binlog_format 的设定而记录,如果是采用 GRANT,REVOKE,SET PASSWORD 等管理语句来做的话,那么无论如何 都采用 SBR 模式记录。

 

4  mysql查看binlog日志(实战)

4.1.获取Binlog文件列表 show binary logs /show master logs

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+

| mysql-bin.004728 | 184627959 |
| mysql-bin.004729 |  35724565 |
| mysql-bin.004730 | 106011990 |
| mysql-bin.004731 | 104858226 |
| mysql-bin.004732 | 101918930 |
+------------------+-----------+

 4.2 查看当前正在写入的binlog文件show master status

mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.004733 | 11147762 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

4.3 查看指定binlog文件的内容语法

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.004733' FROM 11555130 LIMIT 10 ,10 \G;
*************************** 1. row ***************************
   Log_name: mysql-bin.004733
        Pos: 11556078
 Event_type: Table_map
  Server_id: 1
End_log_pos: 11556164
       Info: table_id: 133757289 (xxl-job-test.xxl_job_qrtz_scheduler_state)
*************************** 2. row ***************************
   Log_name: mysql-bin.004733
        Pos: 11556164
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 11556334
       Info: table_id: 133757289 flags: STMT_END_F
*************************** 3. row ***************************
   Log_name: mysql-bin.004733
        Pos: 11556334
 Event_type: Xid
  Server_id: 1
End_log_pos: 11556365
       Info: COMMIT /* xid=3705209152 */

5  mysqlbinlog查看binlog日志(实战)

hehaibolocal:~ hehaibo$ mysqlbinlog
mysqlbinlog Ver 3.4 for osx10.11 at x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.

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.
  -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)
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                  64
verify-binlog-checksum            FALSE
binlog-row-event-max-size         4294967040
skip-gtids                        FALSE
include-gtids                     (No default value)
exclude-gtids                     (No default value)
mysqlbinlog -u用户名 -p密码 -h远程机器IP --read-from-remote-server --start-datetime='2019-02-21 00:30:00' --stop-datetime='2019-02-21 19:00:00' --base64-output=decode-rows -v  -d 数据库名 mysql-bin.004726 >test2.sql

SET TIMESTAMP=1550742540/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;
# at 85640
#190221 17:49:00 server id 1  end_log_pos 85783 CRC32 0xbaa0d667 	Table_map: `数据库`.`表名` mapped to number 133123041
# at 85783
#190221 17:49:00 server id 1  end_log_pos 85829 CRC32 0x258ac362 	Write_rows: table id 133123041 flags: STMT_END_F
### INSERT INTO `数据库`.`表名`
### SET
###   @1=2147483642
###   @2=NULL
###   @28=NULL
# at 85829
#190221 17:49:00 server id 1  end_log_pos 85860 CRC32 0x1c8b2a4f 	Xid = 3687599058
COMMIT/*!*/;
# at 85860
#190221 17:49:00 server id 1  end_log_pos 85951 CRC32 0x9a2fa902 	Query	thread_id=34571619	exec_time=0	error_code=0
hehaibolocal:~ hehaibo$ mysqlbinlog-u用户名 -p密码 -h远程机器IP --read-from-remote-server --start-datetime='2019-02-21 00:30:00' --stop-datetime='2019-02-21 19:00:00' --start-position=975 --stop-position=1636 --base64-output=decode-rows -v  -d 数据库名  mysql-bin.004726|grep Query|more
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
#190221 17:48:37 server id 1  end_log_pos 1066 CRC32 0x9a32ba45         Query   thread_id=34571619      exec_time=0     error_code=0
#190221 17:48:38 server id 1  end_log_pos 1351 CRC32 0x8e7702ed         Query   thread_id=34571619      exec_time=0     error_code=0
#190221 17:48:38 server id 1  end_log_pos 1636 CRC32 0x3a6e8c23         Query   thread_id=34571619      exec_time=0     error_code=0

 

mysqlbinlog -u用户名

-p密码

-h远程机器IP

–read-from-remote-server 从远程服务器读取

–start-datetime=’2019-02-21 00:30:00’从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间

–stop-datetime=’2019-02-21 19:00:00’从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样

–base64-output=decode-rows -v 对输出结果解码

–start-position=975 从二进制日志中读取指定position 事件位置作为开始。

–stop-position=1636 从二进制日志中读取指定position 事件位置作为事件截至

-d 数据库名

mysql-bin.004726 binlog文件名

6 binlog日志分析

1.开始事务的时间

SET TIMESTAMP=1550742540/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;

2.sqlevent起点

# at 85640 为事件的起点,是以85640字节开始。

3.sqlevent 发生的时间点 #190221 17:49:00 是事件发生的时间

4.serverId  server id 1  为master 的serverId

5.sqlevent终点及花费时间,错误码

end_log_pos 85783:为事件的终点,是以1643885 字节结束。

execTime 0: 花费的时间

error_code=0:错误码

Xid:事件指示提交的XA事务

#190221 17:49:00 server id 1 end_log_pos 85783 CRC32 0xbaa0d667 Table_map: `数据库`.`表名` mapped to number 133123041
# at 85783
#190221 17:49:00 server id 1 end_log_pos 85829 CRC32 0x258ac362 Write_rows: table id 133123041 flags: STMT_END_F
### INSERT INTO `数据库`.`表名`
### SET
### @1=2147483642
### @28=NULL
# at 85829
#190221 17:49:00 server id 1 end_log_pos 85860 CRC32 0x1c8b2a4f Xid = 3687599058
COMMIT/*!*/;
# at 85860
#190221 17:49:00 server id 1 end_log_pos 85951 CRC32 0x9a2fa902 Query thread_id=34571619 exec_time=0 error_code=0

 

Mixed日志说明:

在slave日志同步过程中,对于使用now这样的时间函数,MIXED日志格式,会在日志中产生对应的unix_timestamp()*1000的时间字符串,slave在完成同步时,取用的是sqlEvent发生的时间来保证数据的准确性。另外对于一些功能性函数slave能完成相应的数据同步,而对于上面指定的一些类似于UDF函数,导致Slave无法知晓的情况,则会采用ROW格式存储这些Binlog,以保证产生的Binlog可以供Slave完成数据同步。

阿里云双12

发表评论

电子邮件地址不会被公开。 必填项已用*标注

− 3 = 2