MySQL schema和binary log磁盘空间趋势分析

客官°小女子只卖身不卖艺 2022-08-06 07:29 168阅读 0赞

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][_--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][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][_--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----

[_--load_root_root_10.20.0.55_3306_test6]: mailto:--load=root/root@10.20.0.55:3306:test6
[http_pan.baidu.com_s_1hqGKyHY]: http://pan.baidu.com/s/1hqGKyHY