MySQL表信息统计

HH MySQL python215,3158字数 4403阅读14分40秒阅读模式

啰哩八说

在上一篇文章中简单的介绍了使用python-mysql-replication 来解析 MySQL binlog来完成实时统计的业务,当然,在现实的业务中不可能用的那么简单的。

今天的目的不是介绍真实的业务场景如何使用 python-mysql-replication,而是推出一枚<MySQL表信息统计>小工具(笔者通过python-mysql-replication实现的)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

工具链接: https://github.com/daiguadaidai/mysql_binlog_stat文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

在之前我也是使用 @吴炳锡 大神的工具, 因为在了的时候感觉显示的有点生涩因此在他的基础上修改了一些重新展示了, 可是在用的时候还是不尽人意。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

具体看之前笔者的文章: https://www.ttlsa.com/mysql/mysql-fields-when-time-split/文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

大神的工具: https://github.com/wubx/mysql-binlog-statistic文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

笔者重新展示的工具: https://github.com/daiguadaidai/mysql-binlog-statistic文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

二话不说直接展示如何使用

1、查看帮助文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

[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

主要参数介绍:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

--log-file: binlog 文件名称文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

--log-pos: binlog 文件位置(从哪个位置开始解析)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

--blocking: 是否需要使用阻塞的方式进行解析始终为 False 就好(默认就是False)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

--start-time: 从什么时间开始解析文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

--sorted-by: 展示的结果通过什么来排序, 默认是通过 insert 的行数的多少降序排列, 设置的值有 insert/update/delete文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

2、解析 MySQL binlog文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

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分钟时间速度,感觉速度还是不行啊 ^_^。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

这边说一下为什么不提供 --stop-log-file, --stop-log-pos, --stop-time 参数文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

主要是因为 pymysqlreplication 的解析如果是没有解析到结尾,它在 master 上创建的链接会一直存在不会消失,需要人工去master kill掉相关的thread. 我问了作者但是并没有得到很好的回应(估计也是我问问题的方式不对吧)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

相关问题请看以下链接,(同时希望大家一起来解决一这问题, 来完善以下这个小程序)。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

https://github.com/noplay/python-mysql-replication/issues/177#issuecomment-265069799文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

昵称: HH文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/

QQ: 275258836

ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207(新) 微信公众号: ttlsacom)

感觉本文内容不错,读后有收获?

逛逛衣服店,鼓励作者写出更好文章。

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
HH
  • 本文由 发表于 04/01/2017 00:29:50
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/mysql-table-columns-statistics-python-mysql-replication/
评论  2  访客  2
    • 匿名
      匿名 9

      姓名链接出错

      • 凯哥自媒体
        凯哥自媒体 0

        挺好的,感谢博主的分享。

      评论已关闭!