程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQLschema和binarylog磁盤空間趨勢分析

MySQLschema和binarylog磁盤空間趨勢分析

編輯：MySQL綜合教程

MySQLschema和binarylog磁盤空間趨勢分析

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>