Linux之以SQL方式读取MySQL的GTID格式Binlog

将GTID格式的binlog转换为SQL的命令:

  1. mysqlbinlog --start-datetime='2017-03-11 00:00:00' --stop-datetime='2017-03-12 00:00:00' -v --base64-output=DECODE-ROWS mysql-bin.001760 >/data/temp/123.binlog

mysqlbinlog是MySQL官方提供的读取binlog的工具。其执行方法如下:
mysqlbinlog [options] log_file ...
--no-defaults参数
直接执行mysqlbinlog命令如下:

  1. mysqlbinlog mysql-bin.000858

执行失败,报如下错误:

  1. mysqlbinlog: unknown variable 'default-character-set=utf8mb4'

因为使用了默认编码,但是mysqlbinlog又无法正常解析。增加--no-defaults参数直接跳过默认编码格式。
手册中关于该参数的说明:

--no-defaults
Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.
The exception is that the .mylogin.cnf file, if it exists, is read in all cases. This permits passwords to be specified in a safer way than on the command line even when --no-defaults is used. (.mylogin.cnf is created by the mysql_config_editor utility. See mysql_config_editor(1).)

-v参数
增加--no-defaults参数,执行以下命令:

  1. mysqlbinlog --no-defaults mysql-bin.000858

输出如下:

  1. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #161123 4:17:00 server id 1 end_log_pos 120 CRC32 0x095b60fa Start: binlog v 4, server v 5.6.27-0ubuntu0.14.04.1-log created 161123 4:17:00
  7. BINLOG '
  8. vKc0WA8BAAAAdAAAAHgAAAAAAAQANS42LjI3LTB1YnVudHUwLjE0LjA0LjEtbG9nAAAAAAAAAAAA
  9. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfpg
  10. Wwk=
  11. '/*!*/;
  12. # at 120
  13. #161123 4:17:00 server id 1 end_log_pos 231 CRC32 0xbbec3fba Previous-GTIDs
  14. # 9dc1a422-aeb6-11e5-80cd-00163e001e5d:1-5846827,
  15. # fd076ada-69cf-11e6-84d8-00163e0c1dbe:1-381819
  16. # at 231
  17. #161123 4:17:00 server id 1 end_log_pos 279 CRC32 0x3f0812c4 GTID [commit=yes]
  18. SET @@SESSION.GTID_NEXT= '9dc1a422-aeb6-11e5-80cd-00163e001e5d:5846828'/*!*/;
  19. # at 279
  20. #161123 4:17:00 server id 1 end_log_pos 353 CRC32 0x6982c05a Query thread_id=2662386 exec_time=0 error_code=0
  21. SET TIMESTAMP=1479845820/*!*/;
  22. SET @@session.pseudo_thread_id=2662386/*!*/;
  23. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  24. SET @@session.sql_mode=1075838976/*!*/;
  25. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  26. /*!\C utf8mb4 *//*!*/;
  27. SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=224/*!*/;
  28. SET @@session.lc_time_names=0/*!*/;
  29. SET @@session.collation_database=DEFAULT/*!*/;
  30. BEGIN
  31. /*!*/;
  32. # at 353
  33. #161123 4:17:00 server id 1 end_log_pos 450 CRC32 0xb1d11f47 Table_map: `linahr`.`qrtz_fired_triggers` mapped to number 877
  34. # at 450
  35. #161123 4:17:00 server id 1 end_log_pos 825 CRC32 0x0d1392a7 Update_rows: table id 877 flags: STMT_END_F
  36. BINLOG '
  37. vKc0WBMBAAAAYQAAAMIBAAAAAG0DAAAAAAEABmxpbmFocgATcXJ0el9maXJlZF90cmlnZ2VycwAN
  38. Dw8PDw8ICAMPDw8PDxRoAR0BWAJYAlgCMABYAlgCAwADAAAeRx/RsQ==
  39. vKc0WB8BAAAAdwEAADkDAAAAAG0DAAAAAAEAAgAN/////wDmDABDUk1zY2hlZHVsZXImADEyYzZl
  40. ZWNkMDgwZTE0Nzc1ODMzMjQ0ODYxNDc3NTgzMzc2NjQyGABxY2xvdWRWaWRlb1VwbG9hZFRyaWdn
  41. ZXIHAERFRkFVTFQZADEyYzZlZWNkMDgwZTE0Nzc1ODMzMjQ0ODZTEa+NWAEAAGA2r41YAQAAAAAA

其中并没有出现我们想要的SQL语句。这时候就需要增加-v参数了。
-v参数在手册中的描述如下:

--verbose, -v
Reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata.
For examples that show the effect of --base64-output and --verbose on row event output, see the section called “MYSQLBINLOG ROW EVENT DISPLAY”.

增加-v参数,可以看到如下的输出:

  1. BINLOG '
  2. vKc0WBMBAAAAXQAAAJYDAAAAAHQDAAAAAAMABmxpbmFocgANcXJ0el90cmlnZ2VycwAQDw8PDw8P
  3. CAgDDw8ICA8C/BNoAVgCWAJYAlgC7gIwABgAWAIC4PGAPOuQ
  4. vKc0WB8BAAAAOwEAANEEAAAAAHQDAAAAAAEAAgAQ/////yAgDABDUk1zY2hlZHVsZXIYAHFjbG91
  5. ZFZpZGVvVXBsb2FkVHJpZ2dlcgcAREVGQVVMVBcAcWNsb3VkVmlkZW9VcGxvYWREZXRhaWwHAERF
  6. RkFVTFRgNq+NWAEAAABMro1YAQAAAAAAAAhBQ1FVSVJFRARDUk9OeD/UBlgBAAAAAAAAAAAAAAAA
  7. AAAgIAwAQ1JNc2NoZWR1bGVyGABxY2xvdWRWaWRlb1VwbG9hZFRyaWdnZXIHAERFRkFVTFQXAHFj
  8. bG91ZFZpZGVvVXBsb2FkRGV0YWlsBwBERUZBVUxUwCCwjVgBAABgNq+NWAEAAAAAAAAHV0FJVElO
  9. RwRDUk9OeD/UBlgBAAAAAAAAAAAAAAAAAAAIicmV
  10. '/*!*/;
  11. ### UPDATE `linahr`.`qrtz_triggers`
  12. ### WHERE
  13. ### @1='CRMscheduler'
  14. ### @2='qcloudVideoUploadTrigger'
  15. ### @3='DEFAULT'
  16. ### @4='qcloudVideoUploadDetail'
  17. ### @5='DEFAULT'
  18. ### @6=NULL
  19. ### @7=1479845820000
  20. ### @8=1479845760000
  21. ### @9=0
  22. ### @10='ACQUIRED'
  23. ### @11='CRON'
  24. ### @12=1477583323000
  25. ### @13=0
  26. ### @14=NULL
  27. ### @15=0
  28. ### @16=''
  29. ### SET
  30. ### @1='CRMscheduler'
  31. ### @2='qcloudVideoUploadTrigger'
  32. ### @3='DEFAULT'
  33. ### @4='qcloudVideoUploadDetail'
  34. ### @5='DEFAULT'
  35. ### @6=NULL
  36. ### @7=1479845880000
  37. ### @8=1479845820000
  38. ### @9=0
  39. ### @10='WAITING'
  40. ### @11='CRON'
  41. ### @12=1477583323000
  42. ### @13=0
  43. ### @14=NULL
  44. ### @15=0
  45. ### @16=''
  46. # at 1233
  47. #161123 4:17:00 server id 1 end_log_pos 1264 CRC32 0x484eacf5 Xid = 447774161
  48. COMMIT/*!*/;
  49. # at 1264
  50. #161123 4:17:00 server id 1 end_log_pos 1312 CRC32 0xc364953c GTID [commit=yes]
  51. SET @@SESSION.GTID_NEXT= '9dc1a422-aeb6-11e5-80cd-00163e001e5d:5846829'/*!*/;
  52. # at 1312
  53. #161123 4:17:00 server id 1 end_log_pos 1386 CRC32 0xbf84bd5c Query thread_id=2662325 exec_time=0 error_code=0
  54. SET TIMESTAMP=1479845820/*!*/;
  55. BEGIN
  56. /*!*/;
  57. # at 1386
  58. #161123 4:17:00 server id 1 end_log_pos 1483 CRC32 0xb85e9cff Table_map: `linahr`.`qrtz_fired_triggers` mapped to number 877
  59. # at 1483
  60. #161123 4:17:00 server id 1 end_log_pos 1691 CRC32 0x1447d61d Delete_rows: table id 877 flags: STMT_END_F

增加-vv参数,还可以输出一些额外的数据类型信息。如下:

  1. BINLOG '
  2. vKc0WBMBAAAAYQAAAMIBAAAAAG0DAAAAAAEABmxpbmFocgATcXJ0el9maXJlZF90cmlnZ2VycwAN
  3. Dw8PDw8ICAMPDw8PDxRoAR0BWAJYAlgCMABYAlgCAwADAAAeRx/RsQ==
  4. vKc0WB8BAAAAdwEAADkDAAAAAG0DAAAAAAEAAgAN/////wDmDABDUk1zY2hlZHVsZXImADEyYzZl
  5. ZWNkMDgwZTE0Nzc1ODMzMjQ0ODYxNDc3NTgzMzc2NjQyGABxY2xvdWRWaWRlb1VwbG9hZFRyaWdn
  6. ZXIHAERFRkFVTFQZADEyYzZlZWNkMDgwZTE0Nzc1ODMzMjQ0ODZTEa+NWAEAAGA2r41YAQAAAAAA
  7. AAhBQ1FVSVJFRAEwATAA4AwAQ1JNc2NoZWR1bGVyJgAxMmM2ZWVjZDA4MGUxNDc3NTgzMzI0NDg2
  8. MTQ3NzU4MzM3NjY0MhgAcWNsb3VkVmlkZW9VcGxvYWRUcmlnZ2VyBwBERUZBVUxUGQAxMmM2ZWVj
  9. ZDA4MGUxNDc3NTgzMzI0NDg2dTavjVgBAABgNq+NWAEAAAAAAAAJRVhFQ1VUSU5HFwBxY2xvdWRW
  10. aWRlb1VwbG9hZERldGFpbAcAREVGQVVMVAEwATCnkhMN
  11. '/*!*/;
  12. ### UPDATE `linahr`.`qrtz_fired_triggers`
  13. ### WHERE
  14. ### @1='CRMscheduler' /* VARSTRING(360) meta=360 nullable=0 is_null=0 */
  15. ### @2='12c6eecd080e14775833244861477583376642' /* VARSTRING(285) meta=285 nullable=0 is_null=0 */
  16. ### @3='qcloudVideoUploadTrigger' /* VARSTRING(600) meta=600 nullable=0 is_null=0 */
  17. ### @4='DEFAULT' /* VARSTRING(600) meta=600 nullable=0 is_null=0 */
  18. ### @5='12c6eecd080e1477583324486' /* VARSTRING(600) meta=600 nullable=0 is_null=0 */
  19. ### @6=1479845810515 /* LONGINT meta=0 nullable=0 is_null=0 */
  20. ### @7=1479845820000 /* LONGINT meta=0 nullable=0 is_null=0 */
  21. ### @8=0 /* INT meta=0 nullable=0 is_null=0 */
  22. ### @9='ACQUIRED' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
  23. ### @10=NULL /* VARSTRING(48) meta=600 nullable=1 is_null=1 */
  24. ### @11=NULL /* VARSTRING(48) meta=600 nullable=1 is_null=1 */
  25. ### @12='0' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
  26. ### @13='0' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
  27. ### SET
  28. ### @1='CRMscheduler' /* VARSTRING(360) meta=360 nullable=0 is_null=0 */
  29. ### @2='12c6eecd080e14775833244861477583376642' /* VARSTRING(285) meta=285 nullable=0 is_null=0 */
  30. ### @3='qcloudVideoUploadTrigger' /* VARSTRING(600) meta=600 nullable=0 is_null=0 */
  31. ### @4='DEFAULT' /* VARSTRING(600) meta=600 nullable=0 is_null=0 */
  32. ### @5='12c6eecd080e1477583324486' /* VARSTRING(600) meta=600 nullable=0 is_null=0 */
  33. ### @6=1479845820021 /* LONGINT meta=0 nullable=0 is_null=0 */
  34. ### @7=1479845820000 /* LONGINT meta=0 nullable=0 is_null=0 */
  35. ### @8=0 /* INT meta=0 nullable=0 is_null=0 */
  36. ### @9='EXECUTING' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
  37. ### @10='qcloudVideoUploadDetail' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
  38. ### @11='DEFAULT' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
  39. ### @12='0' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
  40. ### @13='0' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
  41. # at 825
  42. #161123 4:17:00 server id 1 end_log_pos 918 CRC32 0x90eb3c80 Table_map: `linahr`.`qrtz_triggers` mapped to number 884
  43. # at 918
  44. #161123 4:17:00 server id 1 end_log_pos 1233 CRC32 0x95c98908 Update_rows: table id 884 flags: STMT_END_F

--base64-output=DECODE-ROWS参数
--base64-decode参数在手册上的说明如下:

--base64-output=value
This option determines when events should be displayed encoded as base-64 strings using BINLOG statements. The option has these permissible values (not case sensitive):
· AUTO ("automatic") or UNSPEC ("unspecified") displays BINLOG statements automatically when necessary (that is, for format description events and row events). If no --base64-output option is given, the effect is the same as --base64-output=AUTO.
   Note
   Automatic BINLOG display is the only safe behavior if you intend to use the output of mysqlbinlog to re-execute binary log file contents. The other option values are intended only for debugging or testing purposes because they may produce output that does not include all events in executable form.
· NEVER causes BINLOG statements not to be displayed. mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG.
· DECODE-ROWS specifies to mysqlbinlog that you intend for row events to be decoded and displayed as commented SQL statements by also specifying the --verbose option. Like NEVER, DECODE-ROWS suppresses display of BINLOG statements, but unlike NEVER, it does not exit with an error if a row event is found.
For examples that show the effect of --base64-output and --verbose on row event output, see the section called “MYSQLBINLOG ROW EVENT DISPLAY”.

增加--base64-output=DECODE-ROWS参数执行:

  1. mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS mysql-bin.000858 > tmp.txt

输出如下,可以看到BINLOG '开头,以base64编码的数据没有了:

  1. SET TIMESTAMP=1479845826/*!*/;
  2. BEGIN
  3. /*!*/;
  4. # at 2697
  5. #161123 4:17:06 server id 1 end_log_pos 2773 CRC32 0x3b984e8d Table_map: `linahr_pay`.`qrtz_scheduler_state` mapped to number 1180
  6. # at 2773
  7. #161123 4:17:06 server id 1 end_log_pos 2925 CRC32 0x394f0f8b Update_rows: table id 1180 flags: STMT_END_F
  8. ### UPDATE `linahr_pay`.`qrtz_scheduler_state`
  9. ### WHERE
  10. ### @1='CRMscheduler'
  11. ### @2='f5589e5e5a731477582086079'
  12. ### @3=1479845806492
  13. ### @4=20000
  14. ### SET
  15. ### @1='CRMscheduler'
  16. ### @2='f5589e5e5a731477582086079'
  17. ### @3=1479845826494
  18. ### @4=20000
  19. # at 2925
  20. #161123 4:17:06 server id 1 end_log_pos 2956 CRC32 0x7d1d1eab Xid = 447774290
  21. COMMIT/*!*/;

--start-datetime=name参数在手册上的说明如下:

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

--stop-datetime=name参数在手册上的说明如下:

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

--skip-gtids参数

--skip-gtids[=(true|false)]
Do not display any GTIDs in the output. This is needed when writing to a dump file from one or more binary logs containing GTIDs, as shown in this example:
  shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
  shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
  shell> mysql -u root -p -e "source /tmp/dump.sql"
The use of this option is otherwise not normally recommended in production.