mysql-schema-size

#!/usr/local/bin/php
<?php
/*
 * Munin plugin mysql_schema_size
 */

$dbname   = getenv('dbname');
$dsn      = getenv('dsn');
$username = getenv('mysqluser');
$password = getenv('mysqlpassword');
$text = "";

if($argc > 1 && $argv[1] == 'authconf') {
  exit('yes');
}

$dbh = new PDO($dsn,$username,$password);
$mysql = new MysqlTblSize($dbh);

$stmt = $mysql->getSchemaList();
$schema_list = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $schema = $row['table_schema'];
  $text .= "$schema.label $schema\n";
  $schema_list[] = $schema;
}

if($argc > 1 && $argv[1] == 'config') {
  $text = <<<EOT
graph_title $dbname Schema Size
graph_category db
graph_vlabel Size
graph_args --base 1024 -l 0
$text
EOT;
  die($text);
}

$stmt = $mysql->getSchemaSize();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $data_length = $row['sum_data_length'];
  $schema      = $row['table_schema'];
  echo "$schema.value $data_length\n";
}

class MysqlTblSize {

  private $dbh;

  public function __construct($dbh) {
    $this->dbh = $dbh;
  }

  public function getSchemaList() {
    $sql = <<<EOT
select distinct(table_schema)
from information_schema.tables
EOT;
    $stmt = $this->dbh->prepare($sql);
    $stmt->execute();
    return $stmt;
  }

  public function getSchemaSize() {
    $sql = <<<EOT
select sum( data_length ) sum_data_length,table_schema
from information_schema.tables
group by table_schema
EOT;
    $stmt = $this->dbh->prepare($sql);
    $stmt->execute();
    return $stmt;
  }


}