首页 > 代码库 > MySQL schema和binary log磁盘空间趋势分析
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/root@10.20.0.55: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/root@10.20.0.55: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磁盘空间趋势分析