MySQL schema and binary log disk space trend analysis

Author:skate
Time:2015/01/05

MySQL schema and binary log disk space trend analysis

[Email protected] 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 the 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
[Email protected] dist]#

Parameter description:

--load connection information for////database Eg:user/[email protected]:p ort:dbname
--dbtype types of////databases MySQL or Oracle
--CFG////For reading data files and binary log files (datadir,log-bin), default read "/etc/my.cnf", of course, you can also define a temporary configuration file
--field///config file field, default is [mysqld]
--datadir////In the configuration file, the key value of the data file path
--logdir////In the configuration file, the key value of the binary file path

Local monitoring of space usage and increment changes for MySQL schema and binary log

[Email protected] 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
[Email protected] dist]#

Load monitoring data into the monitoring Center for subsequent analysis and use

[Email protected] 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

Software Description:
This tool is written in Python, from the OS layer to quickly calculate the MySQL schema and binary log size changes, and can be recorded in the database (not separately statistics data and index),
Although you can query directly from the MySQL database view, the query will be unresponsive for a long time when the database is particularly large.

How to use:
1. Download files directly to the monitored side to use

Download Link: Http://pan.baidu.com/s/1hqGKyHY Password: gc5x

2. Check whether the/ETC/MY.CNF shows the parameters DataDir and Log-bin defined, or define the configuration file directly, as follows:

[Email protected] 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 a table structure

CREATE TABLE ' dbsize ' (
  ' id ' INT (one-by-one) not NULL auto_increment,
  ' schema_name ' VARCHAR (+) NULL Default NULL,
  ' exectime ' DATETIME null default NULL,
  ' schema_size ' INT (one-by-one) null default null COMMENT ' U Nit:k ',
  ' incre_size ' INT (one) null default null COMMENT ' Unit:k ',
  ' type ' VARCHAR () null default NULL,
  ' dbtype ' varchar ($) NULL DEFAULT null COMMENT ' database type mysql;oracle ',
  ' hostname ' varchar ($) NULL Default NULL,
  ' ipaddr ' VARCHAR ($) null DEFAULT null,
 primary KEY (' id '),
 index ' Exectime ' ( ' Exectime '),
 index ' hostname ' (' hostname ')
)
Engine=innodb;

CREATE TABLE ' Binlogsize ' (
' ID ' INT (one) not NULL auto_increment,
' LogSize ' INT (one) null DEFAULT null COMMENT ' Unit:k ',
' LogTime ' datetime null DEFAULT NULL COMMENT ' statistics log Time ',
' Hostname ' VARCHAR (+) null DEFAULT null,
' IPAddr ' VARCHAR (+) null DEFAULT null,
PRIMARY KEY (' id '),
INDEX ' hostname ' (' hostname ')
)
Engine=innodb;

4. You can test the tool.

Displaying data locally
[Email protected] 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
[Email protected] dist]#

Mount to Database
[Email protected] 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
[Email protected] dist]#

5. Querying 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>

----END----

MySQL schema and binary log disk space trend analysis