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----