\u5570\u54e9\u516b\u8bf4<\/strong><\/div>\n
\u4eca\u5929\u7684\u76ee\u7684\u4e0d\u662f\u4ecb\u7ecd\u771f\u5b9e\u7684\u4e1a\u52a1\u573a\u666f\u5982\u4f55\u4f7f\u7528 python-mysql-replication\uff0c\u800c\u662f\u63a8\u51fa\u4e00\u679a<MySQL\u8868\u4fe1\u606f\u7edf\u8ba1>\u5c0f\u5de5\u5177(\u7b14\u8005\u901a\u8fc7python-mysql-replication\u5b9e\u73b0\u7684)<\/p>\n
\u5de5\u5177\u94fe\u63a5:<\/b><\/strong>\u00a0https:\/\/github.com\/daiguadaidai\/mysql_binlog_stat<\/a><\/p>\n
\u5728\u4e4b\u524d\u6211\u4e5f\u662f\u4f7f\u7528 @\u5434\u70b3\u9521 \u5927\u795e\u7684\u5de5\u5177, \u56e0\u4e3a\u5728\u4e86\u7684\u65f6\u5019\u611f\u89c9\u663e\u793a\u7684\u6709\u70b9\u751f\u6da9\u56e0\u6b64\u5728\u4ed6\u7684\u57fa\u7840\u4e0a\u4fee\u6539\u4e86\u4e00\u4e9b\u91cd\u65b0\u5c55\u793a\u4e86, \u53ef\u662f\u5728\u7528\u7684\u65f6\u5019\u8fd8\u662f\u4e0d\u5c3d\u4eba\u610f\u3002<\/p>\n
\u5177\u4f53\u770b\u4e4b\u524d\u7b14\u8005\u7684\u6587\u7ae0: <\/b><\/strong>http:\/\/www.ttlsa.com\/mysql\/mysql-fields-when-time-split\/<\/a><\/p>\n
\u5927\u795e\u7684\u5de5\u5177: <\/b><\/strong>https:\/\/github.com\/wubx\/mysql-binlog-statistic<\/a><\/p>\n
\u7b14\u8005\u91cd\u65b0\u5c55\u793a\u7684\u5de5\u5177: <\/b><\/strong>https:\/\/github.com\/daiguadaidai\/mysql-binlog-statistic<\/a><\/p>\n
\u4e8c\u8bdd\u4e0d\u8bf4\u76f4\u63a5\u5c55\u793a\u5982\u4f55\u4f7f\u7528<\/strong><\/div>\n
1\u3001\u67e5\u770b\u5e2e\u52a9<\/strong><\/span><\/div>\n
[root@centos7 tmp]# python mysql_binlog_stat.py --help\r\nusage: mysql_binlog_stat.py [-h] [--host HOST] [--port PORT]\r\n [--username USERNAME] [--password PASSWORD]\r\n [--log-file binlog-file-name]\r\n [--log-pos binlog-file-pos]\r\n [--server-id server-id] [--slave-uuid slave-uuid]\r\n [--blocking False\/True] [--start-time start-time]\r\n [--sorted-by insert\/update\/delete]\r\n \r\nDescription: The script parse MySQL binlog and statistic column.\r\n \r\noptional arguments:\r\n -h, --help show this help message and exit\r\n --host HOST Connect MySQL host\r\n --port PORT Connect MySQL port\r\n --username USERNAME Connect MySQL username\r\n --password PASSWORD Connect MySQL password\r\n --log-file binlog-file-name\r\n Specify a binlog name\r\n --log-pos binlog-file-pos\r\n Specify a binlog file pos\r\n --server-id server-id\r\n Specify a slave server server-id\r\n --slave-uuid slave-uuid\r\n Specify a slave server uuid\r\n --blocking False\/True\r\n Specify is bloking and parse, default False\r\n --start-time start-time\r\n Specify is start parse timestamp, default None,\r\n example: 2016-11-01 00:00:00\r\n --sorted-by insert\/update\/delete\r\n Specify show statistic sort by, default: insert<\/pre>\n
\u4e3b\u8981\u53c2\u6570\u4ecb\u7ecd:<\/b><\/strong><\/p>\n
--log-file: <\/b><\/strong>binlog \u6587\u4ef6\u540d\u79f0<\/p>\n
--log-pos:<\/b><\/strong>\u00a0binlog \u6587\u4ef6\u4f4d\u7f6e(\u4ece\u54ea\u4e2a\u4f4d\u7f6e\u5f00\u59cb\u89e3\u6790)<\/p>\n
--blocking:<\/b><\/strong>\u00a0\u662f\u5426\u9700\u8981\u4f7f\u7528\u963b\u585e\u7684\u65b9\u5f0f\u8fdb\u884c\u89e3\u6790\u59cb\u7ec8\u4e3a False \u5c31\u597d(\u9ed8\u8ba4\u5c31\u662fFalse)<\/p>\n
--start-time: <\/b><\/strong>\u4ece\u4ec0\u4e48\u65f6\u95f4\u5f00\u59cb\u89e3\u6790<\/p>\n
--sorted-by:<\/b><\/strong>\u00a0\u5c55\u793a\u7684\u7ed3\u679c\u901a\u8fc7\u4ec0\u4e48\u6765\u6392\u5e8f, \u9ed8\u8ba4\u662f\u901a\u8fc7 insert \u7684\u884c\u6570\u7684\u591a\u5c11\u964d\u5e8f\u6392\u5217, \u8bbe\u7f6e\u7684\u503c\u6709 insert\/update\/delete<\/p>\n
2\u3001\u89e3\u6790 MySQL binlog<\/strong><\/span><\/div>\nroot@(none) 09:17:12>show binary logs;\r\n ------------------ ----------- \r\n| Log_name | File_size |\r\n ------------------ ----------- \r\n| mysql-bin.000012 | 437066170 |\r\n| mysql-bin.000013 | 536884582 |\r\n| mysql-bin.000014 | 537032563 |\r\n| mysql-bin.000015 | 536950457 |\r\n| mysql-bin.000016 | 87791004 |\r\n| mysql-bin.000017 | 143 |\r\n| mysql-bin.000018 | 143 |\r\n| mysql-bin.000019 | 143 |\r\n| mysql-bin.000020 | 143 |\r\n| mysql-bin.000021 | 1426 |\r\n ------------------ ----------- \r\n10 rows in set (0.01 sec)\r\n \r\n \r\n# \u4f7f\u7528\u547d\u4ee4\r\n[root@centos7 tmp]# time python mysql_binlog_stat.py --log-file=mysql-bin.000012 --log-pos=120 --username=root --password=root --sorted-by='insert' \r\n[\r\n {\r\n \"app_db.business_item_sku_detail\": {\r\n \"row_insert_count\": {\r\n \"market_price\": 273453,\r\n \"sku_id\": 273453,\r\n \"weight\": 273453\r\n },\r\n \"table_dml_count\": {\r\n \"insert\": 273453,\r\n \"update\": 0,\r\n \"delete\": 0\r\n },\r\n \"row_update_count\": {}\r\n }\r\n },\r\n {\r\n \"app_db.business_item_sku_property\": {\r\n \"row_insert_count\": {\r\n \"sku_id\": 273112,\r\n \"created\": 273112,\r\n \"property_value_id\": 273112,\r\n \"business_item_id\": 273112,\r\n \"record_id\": 273112,\r\n \"property_id\": 273112\r\n },\r\n \"table_dml_count\": {\r\n \"insert\": 273112,\r\n \"update\": 0,\r\n \"delete\": 0\r\n },\r\n \"row_update_count\": {}\r\n }\r\n },\r\n {\r\n \"app_db.business_item_pic\": {\r\n \"row_insert_count\": {\r\n \"created\": 270993,\r\n \"business_item_id\": 270993,\r\n \"pic_id\": 270993,\r\n \"pic_no\": 270993,\r\n \"tmall_shop_id\": 270993,\r\n \"pic_url\": 270993\r\n },\r\n \"table_dml_count\": {\r\n \"insert\": 270993,\r\n \"update\": 0,\r\n \"delete\": 0\r\n },\r\n \"row_update_count\": {}\r\n }\r\n },\r\n {\r\n \"app_db.business_item\": {\r\n \"row_insert_count\": {\r\n \"guide_commission\": 264803,\r\n \"commission_type\": 264803,\r\n \"pstatus\": 264803\r\n },\r\n \"table_dml_count\": {\r\n \"insert\": 264803,\r\n \"update\": 0,\r\n \"delete\": 0\r\n },\r\n \"row_update_count\": {}\r\n }\r\n },\r\n {\r\n \"test.t_binlog_event\": {\r\n \"row_insert_count\": {\r\n \"auto_id\": 5926,\r\n \"dml_sql\": 5926,\r\n \"dml_start_time\": 5926,\r\n \"dml_end_time\": 5926,\r\n \"start_log_pos\": 5926,\r\n \"db_name\": 5926,\r\n \"binlog_name\": 5926,\r\n \"undo_sql\": 5926,\r\n \"table_name\": 5926,\r\n \"end_log_pos\": 5926\r\n },\r\n \"table_dml_count\": {\r\n \"insert\": 5926,\r\n \"update\": 0,\r\n \"delete\": 4017\r\n },\r\n \"row_update_count\": {}\r\n }\r\n },\r\n {\r\n \"test.ord_order\": {\r\n \"row_insert_count\": {\r\n \"order_id\": 184,\r\n \"pay_type\": 181,\r\n \"amount\": 184,\r\n \"create_time\": 184,\r\n \"serial_num\": 181\r\n },\r\n \"table_dml_count\": {\r\n \"insert\": 184,\r\n \"update\": 0,\r\n \"delete\": 0\r\n },\r\n \"row_update_count\": {}\r\n }\r\n },\r\n {\r\n \"test.t1\": {\r\n \"row_insert_count\": {\r\n \"id\": 7,\r\n \"name\": 7\r\n },\r\n \"table_dml_count\": {\r\n \"insert\": 7,\r\n \"update\": 2,\r\n \"delete\": 2\r\n },\r\n \"row_update_count\": {\r\n \"name\": 2\r\n }\r\n }\r\n },\r\n {\r\n \"test.area\": {\r\n \"row_insert_count\": {},\r\n \"table_dml_count\": {\r\n \"insert\": 0,\r\n \"update\": 0,\r\n \"delete\": 0\r\n },\r\n \"row_update_count\": {}\r\n }\r\n }\r\n]\r\n \r\nreal 5m42.982s\r\nuser 5m26.080s\r\nsys 0m8.958s<\/pre>\n
\u5206\u6790\u4e862G\u591a\u7684binlog\u6570\u636e\u82b1\u4e86\u5927\u69826\u5206\u949f\u65f6\u95f4\u901f\u5ea6\uff0c\u611f\u89c9\u901f\u5ea6\u8fd8\u662f\u4e0d\u884c\u554a ^_^\u3002<\/p>\n
\u8fd9\u8fb9\u8bf4\u4e00\u4e0b\u4e3a\u4ec0\u4e48\u4e0d\u63d0\u4f9b --stop-log-file, --stop-log-pos, --stop-time \u53c2\u6570<\/p>\n
\u4e3b\u8981\u662f\u56e0\u4e3a pymysqlreplication \u7684\u89e3\u6790\u5982\u679c\u662f\u6ca1\u6709\u89e3\u6790\u5230\u7ed3\u5c3e\uff0c\u5b83\u5728 master \u4e0a\u521b\u5efa\u7684\u94fe\u63a5\u4f1a\u4e00\u76f4\u5b58\u5728\u4e0d\u4f1a\u6d88\u5931\uff0c\u9700\u8981\u4eba\u5de5\u53bbmaster kill\u6389\u76f8\u5173\u7684thread. \u6211\u95ee\u4e86\u4f5c\u8005\u4f46\u662f\u5e76\u6ca1\u6709\u5f97\u5230\u5f88\u597d\u7684\u56de\u5e94(\u4f30\u8ba1\u4e5f\u662f\u6211\u95ee\u95ee\u9898\u7684\u65b9\u5f0f\u4e0d\u5bf9\u5427)<\/p>\n
\u76f8\u5173\u95ee\u9898\u8bf7\u770b\u4ee5\u4e0b\u94fe\u63a5,(\u540c\u65f6\u5e0c\u671b\u5927\u5bb6\u4e00\u8d77\u6765\u89e3\u51b3\u4e00\u8fd9\u95ee\u9898, \u6765\u5b8c\u5584\u4ee5\u4e0b\u8fd9\u4e2a\u5c0f\u7a0b\u5e8f)\u3002<\/p>\n
https:\/\/github.com\/noplay\/python-mysql-replication\/issues\/177#issuecomment-265069799<\/a><\/p>\n
\n
\u539f\u6587\u6765\u81ea\uff1a http:\/\/www.ttlsa.com\/mysql\/mysql-table-columns-statistics-python-mysql-replication\/<\/a><\/p>\n
\u672c\u6587\u5730\u5740\uff1a http:\/\/lrxjmw.cn\/mysql-table-columns-statistics.html <\/a>\u7f16\u8f91\uff1a\u6768\u9e4f\u98de\uff0c\u5ba1\u6838\u5458\uff1a\u9004\u589e\u5b9d<\/span><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
\u5bfc\u8bfb \u5728\u4e0a\u4e00\u7bc7\u6587\u7ae0\u4e2d\u7b80\u5355\u7684\u4ecb\u7ecd\u4e86\u4f7f\u7528python-mysql-replication \u6765\u89e3\u6790 MySQL bi […]<\/p>\n","protected":false},"author":63,"featured_media":33501,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-47276","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/47276","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/users\/63"}],"replies":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/comments?post=47276"}],"version-history":[{"count":11,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/47276\/revisions"}],"predecessor-version":[{"id":271529,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/47276\/revisions\/271529"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media\/33501"}],"wp:attachment":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media?parent=47276"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/categories?post=47276"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/tags?post=47276"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}