MySQL schema and binary log disk space trend analysis, schemabinary

MySQL schema and binary log disk space trend analysis, schemabinary

Author: Skate
Time: 2015/01/05

 

Disk Space Trend Analysis of MySQL schema and 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] #

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_dir
The parameter load is not provided. So don't load into database

Schema: test2 total: 528540 K Increment: 0 K
Schema: test5 total: 368352 K Increment: 0 K
Schema: test4 total: 4 K Increment: 0 K
Schema: performance_schema total: 212 K Increment: 0 K
Schema: test6 total: 1012 K Increment: 0 K
Schema: test30 total: 680 K Increment: 0 K
Schema: testa total: 592216 K Increment: 0 K
Schema: testb total: 592340 K Increment: 0 K
Schema: testprod total: 804 K Increment: 0 K
Schema: ty_database total: 488 K Increment: 0 K
Schema: mysqltotal: 1016 K Increment: 0 K
Schema: test total: 55344 K Increment: 0 K
Schema: salt total: 272 K Increment: 0 K

Binary log: 10620000 K
You 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/data
Innodb_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,
'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' 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_dir
The parameter load is not provided. So don't load into database

Schema: test2 total: 528540 K Increment: 0 K
Schema: test5 total: 368352 K Increment: 0 K
Schema: test4 total: 4 K Increment: 0 K
Schema: performance_schema total: 212 K Increment: 0 K
Schema: test6 total: 1060 K Increment: 48 K
Schema: test30 total: 680 K Increment: 0 K
Schema: testa total: 592216 K Increment: 0 K
Schema: testb total: 592340 K Increment: 0 K
Schema: testprod total: 804 K Increment: 0 K
Schema: ty_database total: 488 K Increment: 0 K
Schema: mysqltotal: 1016 K Increment: 0 K
Schema: test total: 55344 K Increment: 0 K
Schema: salt total: 272 K Increment: 0 K

Binary log: 10620000 K
You 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,
-> (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 .exe ctime = date_format (a. logtime, '% Y-% m-% D ')
->;
+ ------------ + ----------- + ------------------ + ----------------- + ---------- +
| Exectime | hostname | sum (schema_size) | sum (incre_size) | logsize |
+ ------------ + ----------- + ------------------ + ----------------- + ---------- +
| Skatedb55 | 27836040 | 648 | 10620000 |
| Skatedb55 | 27836040 | 648 | 10620000 |
+ ------------ + ----------- + ------------------ + ----------------- + ---------- +
2 rows in set (0.00 sec)

Mysql>

 

---- End ----