Disk Space Trend Analysis of MySQLschema and binarylog

Disk Space Trend Analysis of MySQLschema and binarylog

Disk Space Trend Analysis of MySQL schema and binary log

[root@skatedb55 dist]# ./mysqlsize --helpusage: Database diskspace usage v0.1 ,(C) Copyright Skate 2014[-h] [--load LOAD] --dbtype DBTYPE --cfg CFG --field FIELD --datadirDATADIR --logdir LOGDIRoptional 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]#

Parameter description:

-- Load // database connection information eg: user/passwd @ ip: port: dbname
-- Dbtype // The Database Type mysql or oracle
-- Cfg // The path (datadir, log-bin) used to read data files and Binary log files. By default, "/etc/my. cnf ", of course, you can also define a temporary configuration file
-- Field // the configuration file field. The default value is [mysqld].
-- Datadir // the key value of the data file path in the configuration file
-- Logdir // the key value of the binary file path in the configuration file

 

 

Local Monitoring of space usage and incremental changes of MySQL schema and Binary log

[root@skatedb55 dist]# ./mysqlsize --dbtype=mysql --cfg=/tmp/my.cnf --field=mysqld --datadir=innodb_data_home_dir --logdir=innodb_log_group_home_dirThe parameter load is not provided.So don't load into databaseSchema: test2 total: 528540K Increment: 0KSchema: test5 total: 368352K Increment: 0KSchema: test4 total: 4K Increment: 0KSchema: performance_schema total: 212K Increment: 0KSchema: test6 total: 1012K Increment: 0KSchema: test30 total: 680K Increment: 0KSchema: testa total: 592216K Increment: 0KSchema: testb total: 592340K Increment: 0KSchema: testprod total: 804K Increment: 0KSchema: ty_database total: 488K Increment: 0KSchema: mysql total: 1016K Increment: 0KSchema: test total: 55344K Increment: 0KSchema: salt total: 272K Increment: 0K2015-01-04 Binary log:10620000 KYou have new mail in /var/spool/mail/root[root@skatedb55 dist]#

Load monitoring data to the monitoring center for subsequent analysis

[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

Software Description:
This tool is written in python. The OS layer quickly calculates the size changes of MySQL schema and Binary log, and records the changes to the database (data and index are not counted separately ),
Although it can be directly queried from the MySQL database view, when the database is very large, the query will not respond for a long time.

Usage:
1. Directly download the file to the monitored end.

Download link: http://pan.baidu.com/s/1hqGKyHY password: gc5x

2. Check/etc/my. cnf whether the datadir and log-bin parameters are defined, or directly define the configuration file by yourself, as shown below:

[root@skatedb55 dist]# more /tmp/my.cnf[mysqld]innodb_data_home_dir=/mysql/data/mysql5529/datainnodb_log_group_home_dir=/mysql/data/mysql5529/data

3. Create a table structure

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', 'encre _ size' INT (11) null default null comment 'unit: k ', 'type' VARCHAR (50) null default null, 'dbtype' VARCHAR (50) null default null comment' Database type mysql; oracle ', 'hostname' VARCHAR (50) null default null, 'ipadd' 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' log statistics time', 'hostname' VARCHAR (50) null default null, 'ipaddr 'VARCHAR (50) null default null, primary key ('id'), INDEX 'hostname' ('hostname') ENGINE = InnoDB;

4. You can test the tool.

Local display data
 

[root@skatedb55 dist]# ./mysqlsize --dbtype=mysql --cfg=/tmp/my.cnf --field=mysqld --datadir=innodb_data_home_dir --logdir=innodb_log_group_home_dirThe parameter load is not provided.So don't load into databaseSchema: test2 total: 528540K Increment: 0KSchema: test5 total: 368352K Increment: 0KSchema: test4 total: 4K Increment: 0KSchema: performance_schema total: 212K Increment: 0KSchema: test6 total: 1060K Increment: 48KSchema: test30 total: 680K Increment: 0KSchema: testa total: 592216K Increment: 0KSchema: testb total: 592340K Increment: 0KSchema: testprod total: 804K Increment: 0KSchema: ty_database total: 488K Increment: 0KSchema: mysql total: 1016K Increment: 0KSchema: test total: 55344K Increment: 0KSchema: salt total: 272K Increment: 0K2015-01-04 Binary log:10620000 KYou have new mail in /var/spool/mail/root[root@skatedb55 dist]#

Load to database
 

[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. query historical data

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>