MySQL schema和binary log磁碟空間趨勢分析,schemabinary
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----