mysql schema和binary log磁盘空间趋势分析
Author：Skate
Time:2015/01/05
MySQL schema和binary log磁盘空间趋势分析
[root@skatedb55 dist]# ./mysqlsize –help
usage: Database diskspace usage v0.1 ,(C) Copyright Skate 2014
[-h] [–load LOAD] –dbtype DBTYPE –cfg CFG –field FIELD –datadir
DATADIR –logdir LOGDIR
optional arguments:
-h, –help show this help message and exit
–load LOAD = The connection information of database
–dbtype DBTYPE = The database type
–cfg CFG = The path to configution file
–field FIELD = The field of configution file
–datadir DATADIR = The key of data directory in configuration file
–logdir LOGDIR = The key of binlog directory in configuration file
[root@skatedb55 dist]#
参数说明：
–load ////数据库的连接信息 eg:user/passwd@ip:port:dbname
–dbtype ////数据库的类型mysql或oracle
–cfg ///用于读取数据文件和Binary log文件的路径（datadir，log-bin），默认读取“/etc/my.cnf”,当然你自己也可以定义一个临时配置文件
–field ///配置文件field，默认是[mysqld]
–datadir ///在配置文件中，数据文件路径的key值
–logdir ///在配置文件中，二进制文件路径的key值
本地监控MySQL schema和Binary log的空间使用率和增量变化
[root@skatedb55 dist]# ./mysqlsize –dbtype=mysql –cfg=/tmp/my.cnf –field=mysqld –datadir=innodb_data_home_dir –logdir=innodb_log_group_home_dir
The parameter load is not provided.So don’t load into database
Schema: test2 total: 528540K Increment: 0K
Schema: test5 total: 368352K Increment: 0K
Schema: test4 total: 4K Increment: 0K
Schema: performance_schema total: 212K Increment: 0K
Schema: test6 total: 1012K Increment: 0K
Schema: test30 total: 680K Increment: 0K
Schema: testa total: 592216K Increment: 0K
Schema: testb total: 592340K Increment: 0K
Schema: testprod total: 804K Increment: 0K
Schema: ty_database total: 488K Increment: 0K
Schema: mysql total: 1016K Increment: 0K
Schema: test total: 55344K Increment: 0K
Schema: salt total: 272K Increment: 0K
2015-01-04 Binary log:10620000 K
You have new mail in /var/spool/mail/root
[root@skatedb55 dist]#
把监控数据装载到监控中心，供后续分析使用
[root@skatedb55 dist]# ./mysqlsize –load=root/[email protected]:3306:test6 –dbtype=mysql –cfg=/tmp/my.cnf –field=mysqld –datadir=innodb_data_home_dir –logdir=innodb_log_group_home_dir
软件说明：
本工具是用python写的，从os层快速计算MySQL的schema和Binary log的大小变化，并可以记录到数据库中（没有分别统计data和index），
虽然从MySQL数据库视图中可以直接查询，但是当数据库特别大时，查询会很久没有响应。
使用方法：
1. 直接下载文件到被监控端即可使用
下载链接: http://pan.baidu.com/s/1hqGKyHY 密码: gc5x
2. 检查/etc/my.cnf是否显示定义了参数datadir和log-bin,或者直接自己定义配置文件，如下：
[root@skatedb55 dist]# more /tmp/my.cnf
[mysqld]
innodb_data_home_dir=/mysql/data/mysql5529/data
innodb_log_group_home_dir=/mysql/data/mysql5529/data
3.创建表结构
CREATE TABLE `dbsize` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`schema_name` VARCHAR(100) NULL DEFAULT NULL,
`exectime` DATETIME NULL DEFAULT NULL,
`schema_size` INT(11) NULL DEFAULT NULL COMMENT ‘Unit：k’,
`incre_size` INT(11) NULL DEFAULT NULL COMMENT ‘Unit：k’,
`type` VARCHAR(50) NULL DEFAULT NULL,
`dbtype` VARCHAR(50) NULL DEFAULT NULL COMMENT ‘数据库类型 mysql；oracle’,
`hostname` VARCHAR(50) NULL DEFAULT NULL,
`ipaddr` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `exectime` (`exectime`),
INDEX `hostname` (`hostname`)
)
ENGINE=InnoDB;
CREATE TABLE `binlogsize` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`logsize` INT(11) NULL DEFAULT NULL COMMENT ‘Unit：k’,
`logtime` DATETIME NULL DEFAULT NULL COMMENT ‘统计日志的时间’,
`hostname` VARCHAR(50) NULL DEFAULT NULL,
`ipaddr` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `hostname` (`hostname`)
)
ENGINE=InnoDB;
4.可以测试工具了
本地显示数据
[root@skatedb55 dist]# ./mysqlsize –dbtype=mysql –cfg=/tmp/my.cnf –field=mysqld –datadir=innodb_data_home_dir –logdir=innodb_log_group_home_dir
The parameter load is not provided.So don’t load into database
Schema: test2 total: 528540K Increment: 0K
Schema: test5 total: 368352K Increment: 0K
Schema: test4 total: 4K Increment: 0K
Schema: performance_schema total: 212K Increment: 0K
Schema: test6 total: 1060K Increment: 48K
Schema: test30 total: 680K Increment: 0K
Schema: testa total: 592216K Increment: 0K
Schema: testb total: 592340K Increment: 0K
Schema: testprod total: 804K Increment: 0K
Schema: ty_database total: 488K Increment: 0K
Schema: mysql total: 1016K Increment: 0K
Schema: test total: 55344K Increment: 0K
Schema: salt total: 272K Increment: 0K
2015-01-04 Binary log:10620000 K
You have new mail in /var/spool/mail/root
[root@skatedb55 dist]#
装载到数据库
[root@skatedb55 dist]# ./mysqlsize –load=root/[email protected]:3306:test6 –dbtype=mysql –cfg=/tmp/my.cnf –field=mysqld –datadir=innodb_data_home_dir –logdir=innodb_log_group_home_dir
[root@skatedb55 dist]#
5. 查询历史数据
mysql> select b.* ,a.logsize from binlogsize a ,
-> (select date_format(exectime,’%Y-%m-%d’) as exectime,hostname,sum(schema_size),sum(incre_size) from dbsize where exectime > date_add(now(),interval -1 day)
-> and dbtype=’mysql’
-> and hostname=’skatedb55′
-> group by hostname,date_format(exectime,’%Y-%m-%d’)
-> ) b
-> where a.hostname=b.hostname
-> and b.exectime=date_format(a.logtime,’%Y-%m-%d’)
-> ;
+————+———–+——————+—————–+———-+
| exectime | hostname | sum(schema_size) | sum(incre_size) | logsize |
+————+———–+——————+—————–+———-+
| 2015-01-05 | skatedb55 | 27836040 | 648 | 10620000 |
| 2015-01-05 | skatedb55 | 27836040 | 648 | 10620000 |
+————+———–+——————+—————–+———-+
2 rows in set (0.00 sec)
mysql>
—-end—-
欢迎大家阅读《MySQL schema跟binary log磁盘空间趋势分析_mysql》,跪求各位点评，by 搞代码
转载请注明原文链接：MySQL schema跟binary log磁盘空间趋势分析_mysql
