MySQL schema和binary log磁碟空間趨勢分析,schemabinary

來源:互聯網
上載者:User

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

 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.