共计 4337 个字符，预计需要花费 11 分钟才能阅读完成。
| 导读 | 在上一篇文章中简单的介绍了使用 python-mysql-replication 来解析 MySQL binlog 来完成实时统计的业务，当然，在现实的业务中不可能用的那么简单的。 |
今天的目的不是介绍真实的业务场景如何使用 python-mysql-replication，而是推出一枚 <MySQL 表信息统计 > 小工具 (笔者通过 python-mysql-replication 实现的)
工具链接: https://github.com/daiguadaidai/mysql_binlog_stat
在之前我也是使用 @吴炳锡 大神的工具, 因为在了的时候感觉显示的有点生涩因此在他的基础上修改了一些重新展示了, 可是在用的时候还是不尽人意。
具体看之前笔者的文章: http://www.ttlsa.com/mysql/mysql-fields-when-time-split/
大神的工具: https://github.com/wubx/mysql-binlog-statistic
笔者重新展示的工具: https://github.com/daiguadaidai/mysql-binlog-statistic
| [root@centos7 tmp]# python mysql_binlog_stat.py --help | |
| usage: mysql_binlog_stat.py [-h] [--host HOST] [--port PORT] | |
| [--username USERNAME] [--password PASSWORD] | |
| [--log-file binlog-file-name] | |
| [--log-pos binlog-file-pos] | |
| [--server-id server-id] [--slave-uuid slave-uuid] | |
| [--blocking False/True] [--start-time start-time] | |
| [--sorted-by insert/update/delete] | |
| Description: The script parse MySQL binlog and statistic column. | |
| optional arguments: | |
| -h, --help show this help message and exit | |
| --host HOST Connect MySQL host | |
| --port PORT Connect MySQL port | |
| --username USERNAME Connect MySQL username | |
| --password PASSWORD Connect MySQL password | |
| --log-file binlog-file-name | |
| Specify a binlog name | |
| --log-pos binlog-file-pos | |
| Specify a binlog file pos | |
| --server-id server-id | |
| Specify a slave server server-id | |
| --slave-uuid slave-uuid | |
| Specify a slave server uuid | |
| --blocking False/True | |
| Specify is bloking and parse, default False | |
| --start-time start-time | |
| Specify is start parse timestamp, default None, | |
| example: 2016-11-01 00:00:00 | |
| --sorted-by insert/update/delete | |
| Specify show statistic sort by, default: insert |
主要参数介绍:
–log-file: binlog 文件名称
–log-pos: binlog 文件位置 (从哪个位置开始解析)
–blocking: 是否需要使用阻塞的方式进行解析始终为 False 就好 (默认就是 False)
–start-time: 从什么时间开始解析
–sorted-by: 展示的结果通过什么来排序, 默认是通过 insert 的行数的多少降序排列, 设置的值有 insert/update/delete
| root@(none) 09:17:12>show binary logs; | |
| ------------------ ----------- | |
| | Log_name | File_size | | |
| ------------------ ----------- | |
| | mysql-bin.000012 | 437066170 | | |
| | mysql-bin.000013 | 536884582 | | |
| | mysql-bin.000014 | 537032563 | | |
| | mysql-bin.000015 | 536950457 | | |
| | mysql-bin.000016 | 87791004 | | |
| | mysql-bin.000017 | 143 | | |
| | mysql-bin.000018 | 143 | | |
| | mysql-bin.000019 | 143 | | |
| | mysql-bin.000020 | 143 | | |
| | mysql-bin.000021 | 1426 | | |
| ------------------ ----------- | |
| 10 rows in set (0.01 sec) | |
| # 使用命令 | |
| [root@centos7 tmp]# time python mysql_binlog_stat.py --log-file=mysql-bin.000012 --log-pos=120 --username=root --password=root --sorted-by='insert' | |
| [ | |
| { | |
| "app_db.business_item_sku_detail": { | |
| "row_insert_count": { | |
| "market_price": 273453, | |
| "sku_id": 273453, | |
| "weight": 273453 | |
| }, | |
| "table_dml_count": { | |
| "insert": 273453, | |
| "update": 0, | |
| "delete": 0 | |
| }, | |
| "row_update_count": {}} | |
| }, | |
| { | |
| "app_db.business_item_sku_property": { | |
| "row_insert_count": { | |
| "sku_id": 273112, | |
| "created": 273112, | |
| "property_value_id": 273112, | |
| "business_item_id": 273112, | |
| "record_id": 273112, | |
| "property_id": 273112 | |
| }, | |
| "table_dml_count": { | |
| "insert": 273112, | |
| "update": 0, | |
| "delete": 0 | |
| }, | |
| "row_update_count": {}} | |
| }, | |
| { | |
| "app_db.business_item_pic": { | |
| "row_insert_count": { | |
| "created": 270993, | |
| "business_item_id": 270993, | |
| "pic_id": 270993, | |
| "pic_no": 270993, | |
| "tmall_shop_id": 270993, | |
| "pic_url": 270993 | |
| }, | |
| "table_dml_count": { | |
| "insert": 270993, | |
| "update": 0, | |
| "delete": 0 | |
| }, | |
| "row_update_count": {}} | |
| }, | |
| { | |
| "app_db.business_item": { | |
| "row_insert_count": { | |
| "guide_commission": 264803, | |
| "commission_type": 264803, | |
| "pstatus": 264803 | |
| }, | |
| "table_dml_count": { | |
| "insert": 264803, | |
| "update": 0, | |
| "delete": 0 | |
| }, | |
| "row_update_count": {}} | |
| }, | |
| { | |
| "test.t_binlog_event": { | |
| "row_insert_count": { | |
| "auto_id": 5926, | |
| "dml_sql": 5926, | |
| "dml_start_time": 5926, | |
| "dml_end_time": 5926, | |
| "start_log_pos": 5926, | |
| "db_name": 5926, | |
| "binlog_name": 5926, | |
| "undo_sql": 5926, | |
| "table_name": 5926, | |
| "end_log_pos": 5926 | |
| }, | |
| "table_dml_count": { | |
| "insert": 5926, | |
| "update": 0, | |
| "delete": 4017 | |
| }, | |
| "row_update_count": {}} | |
| }, | |
| { | |
| "test.ord_order": { | |
| "row_insert_count": { | |
| "order_id": 184, | |
| "pay_type": 181, | |
| "amount": 184, | |
| "create_time": 184, | |
| "serial_num": 181 | |
| }, | |
| "table_dml_count": { | |
| "insert": 184, | |
| "update": 0, | |
| "delete": 0 | |
| }, | |
| "row_update_count": {}} | |
| }, | |
| { | |
| "test.t1": { | |
| "row_insert_count": { | |
| "id": 7, | |
| "name": 7 | |
| }, | |
| "table_dml_count": { | |
| "insert": 7, | |
| "update": 2, | |
| "delete": 2 | |
| }, | |
| "row_update_count": {"name": 2} | |
| } | |
| }, | |
| { | |
| "test.area": {"row_insert_count": {}, | |
| "table_dml_count": { | |
| "insert": 0, | |
| "update": 0, | |
| "delete": 0 | |
| }, | |
| "row_update_count": {}} | |
| } | |
| ] | |
| real 5m42.982s | |
| user 5m26.080s | |
| sys 0m8.958s |
分析了 2G 多的 binlog 数据花了大概 6 分钟时间速度，感觉速度还是不行啊 ^_^。
这边说一下为什么不提供 –stop-log-file, –stop-log-pos, –stop-time 参数
主要是因为 pymysqlreplication 的解析如果是没有解析到结尾，它在 master 上创建的链接会一直存在不会消失，需要人工去 master kill 掉相关的 thread. 我问了作者但是并没有得到很好的回应 (估计也是我问问题的方式不对吧)
相关问题请看以下链接,(同时希望大家一起来解决一这问题, 来完善以下这个小程序)。
https://github.com/noplay/python-mysql-replication/issues/177#issuecomment-265069799
