{"action":"opened","issue":{"url":"https://api.github.com/repos/dimitri/pgloader/issues/166","labels_url":"https://api.github.com/repos/dimitri/pgloader/issues/166/labels{/name}","comments_url":"https://api.github.com/repos/dimitri/pgloader/issues/166/comments","events_url":"https://api.github.com/repos/dimitri/pgloader/issues/166/events","html_url":"https://github.com/dimitri/pgloader/issues/166","id":56432826,"number":166,"title":"Add support for MySQL binlogs","user":{"login":"dveeden","id":1272980,"avatar_url":"https://avatars.githubusercontent.com/u/1272980?v=3","gravatar_id":"","url":"https://api.github.com/users/dveeden","html_url":"https://github.com/dveeden","followers_url":"https://api.github.com/users/dveeden/followers","following_url":"https://api.github.com/users/dveeden/following{/other_user}","gists_url":"https://api.github.com/users/dveeden/gists{/gist_id}","starred_url":"https://api.github.com/users/dveeden/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dveeden/subscriptions","organizations_url":"https://api.github.com/users/dveeden/orgs","repos_url":"https://api.github.com/users/dveeden/repos","events_url":"https://api.github.com/users/dveeden/events{/privacy}","received_events_url":"https://api.github.com/users/dveeden/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-02-03T20:32:39Z","updated_at":"2015-02-03T20:32:39Z","closed_at":null,"body":"Here are some notes which might be helpful if you try to get pgloader to\r\nunderstand the MySQL Binlog format, which would be a great feature.\r\n\r\nMySQL binlogs are binary and can be decoded with the `mysqlbinlog` tool.\r\nThe binlogs contains a series of events. There are a number of different\r\nevent types. The binlog version 4 is what have been in used for a long\r\ntime. Unfortunately there have been some incremental changes within\r\nformat 4.\r\n\r\nThe `mysqlbinlog` tool converts it to statements which can be ran.\r\n\r\nFor statement based binlogs `mysqlbinlog` returns the SQL statement and\r\nfor row based events it returns a BINLOG command. It can can create\r\npseudo-SQL for row based events: `mysqlbinlog -v` (or `-vv` to also get type\r\ninfo). This also depends on the `--decode-rows` option, but the default\r\nfor it should be ok.\r\n\r\nTo get MySQL (or MariaDB) to write binlogs this is what needs to be\r\nconfigured:\r\n```\r\nbinlog_format=(ROW|MIXED|STATEMENT)\r\nlog_bin=/path/to/binlog\r\n```\r\nThe server also can show some information about the binlogs.\r\n\r\n```\r\nmysql-5.6.22-debug-log [test] > show master status;\r\n+------------------------------+----------+--------------+------------------+-------------------+\r\n| File                         | Position | Binlog_Do_DB |\r\nBinlog_Ignore_DB | Executed_Gtid_Set |\r\n+------------------------------+----------+--------------+------------------+-------------------+\r\n| mysql_sandbox5622-bin.000043 |      316 |             \r\n|                  |                   |\r\n+------------------------------+----------+--------------+------------------+-------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql-5.6.22-debug-log [test] > show binlog events in\r\n'mysql_sandbox5622-bin.000043';\r\n+------------------------------+-----+-------------+-----------+-------------+---------------------------------------------+\r\n| Log_name                     | Pos | Event_type  | Server_id |\r\nEnd_log_pos | Info                                        |\r\n+------------------------------+-----+-------------+-----------+-------------+---------------------------------------------+\r\n| mysql_sandbox5622-bin.000043 |   4 | Format_desc |        10 |        \r\n120 | Server ver: 5.6.22-debug-log, Binlog ver: 4 |\r\n| mysql_sandbox5622-bin.000043 | 120 | Query       |        10 |        \r\n192 | BEGIN                                       |\r\n| mysql_sandbox5622-bin.000043 | 192 | Table_map   |        10 |        \r\n239 | table_id: 72 (test.t1)                      |\r\n| mysql_sandbox5622-bin.000043 | 239 | Write_rows  |        10 |        \r\n285 | table_id: 72 flags: STMT_END_F              |\r\n| mysql_sandbox5622-bin.000043 | 285 | Xid         |        10 |        \r\n316 | COMMIT /* xid=17 */                         |\r\n+------------------------------+-----+-------------+-----------+-------------+---------------------------------------------+\r\n5 rows in set (0.00 sec)\r\n```\r\n\r\nSome important variables:\r\n\r\nThe `binlog_row_image` variable is used to set if full before/after images\r\nfor each row are sent to the binlog or a minimal format (changed rows + pk)\r\n\r\nThe `binlog_rows_query_log_events` variable is used to also log the\r\nstatement which caused the RBR events.\r\n\r\n`binlog-checksum` controls if and what format of checksums are written for\r\nthe events.\r\n\r\nExample `mysqlbinlog` output for RBR:\r\n```\r\n# at 571\r\n#150131 12:53:08 server id 10  end_log_pos 643 CRC32 0x58df2de8   \r\n Query    thread_id=1    exec_time=0    error_code=0\r\nSET TIMESTAMP=1422705188/*!*/;\r\nBEGIN\r\n/*!*/;\r\n# at 643\r\n#150131 12:53:08 server id 10  end_log_pos 700 CRC32 0xa9c712d1   \r\n Rows_query\r\n# insert into t1 values(1,'test 3')\r\n# at 700\r\n#150131 12:53:08 server id 10  end_log_pos 747 CRC32 0xc5677e39   \r\n Table_map: `test`.`t1` mapped to number 72\r\n# at 747\r\n#150131 12:53:08 server id 10  end_log_pos 795 CRC32 0xe2586765   \r\n Write_rows: table id 72 flags: STMT_END_F\r\n\r\nBINLOG '\r\nJMLMVB0KAAAAOQAAALwCAACAACFpbnNlcnQgaW50byB0MSB2YWx1ZXMoMSwndGVzdCAzJynREsep\r\nJMLMVBMKAAAALwAAAOsCAAAAAEgAAAAAAAEABHRlc3QAAnQxAAID/AECAzl+Z8U=\r\nJMLMVB4KAAAAMAAAABsDAAAAAEgAAAAAAAEAAgAC//wBAAAABgB0ZXN0IDNlZ1ji\r\n'/*!*/;\r\n### INSERT INTO `test`.`t1`\r\n### SET\r\n###   @1=1 /* INT meta=0 nullable=1 is_null=0 */\r\n###   @2='test 3' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */\r\n# at 795\r\n#150131 12:53:08 server id 10  end_log_pos 826 CRC32 0xd26aad78     Xid = 26\r\nCOMMIT/*!*/;\r\n```\r\n\r\nThe `row_query` is information only (`binlog_rows_query_log_events` is set\r\nhere). The important ones are `table_map` and `write_rows`.\r\n\r\nThere is https://github.com/mysql/mysql-server/blob/5.7/client/mysqlbinlog.cc and there are some ruby tools written by\r\nJeremy Cole.\r\n\r\nI think the RBR entries uses the MyISAM row format.\r\n\r\nThere seems not to be any info the signedness of integer columns. But\r\nthis shouldn't be an issue as you already have the original definion of\r\nthe table.\r\n\r\nAll DDL is sent as statements to the binlog.\r\n\r\nI use MySQL Sandbox to test with multiple versions...\r\n\r\nPlease let me know if there is any other info you need.\r\n\r\nhttps://dev.mysql.com/doc/internals/en/binary-log.html\r\nhttps://github.com/jeremycole/mysql_binlog"}}