<?php

date_default_timezone_set('Europe/Berlin');

$status_vars = array(
  "Bytes_received",    // The number of bytes received from all clients. 
  "Bytes_sent",        // The number of bytes sent to all clients. 
  "Com_select",        // The number of select Statements except "Qcache_hits".
  "Qcache_hits",       // The number of query cache hits. 
  "Questions",         // The number of statements executed by the server.
  "Slow_queries",      // The number of queries that have taken more than long_query_time seconds.
  "Threads_connected", // The number of currently open connections. 
  "Uptime"             // The number of seconds that the server has been up. 
);


//3391 SB, 3511 HT, 3611 Möbler, 3651 HTM, 3711 intern
foreach (array("3391") as $port) {

  $log_name = "/root/votian_admin/mysql_status_" . $port . "_" . date("Y-m") . ".csv";
  $prev_vars = array();
  $prev_output = tail($log_name, 1);
  $prev_vars = explode(";", str_replace(",", "", $prev_output));
  //print_r($prev_vars);
  if (count($prev_vars) < 21)
    writeLog("Date;Uptime;Queries;Select_no_hit;qps;qps_now;Slow;Slow_qps;Slow_now;Threads;Cache_hits;Hits_s;Hits_now;Ratio;Ratio_now;Bytes_in;Bytes_out;KBps_in;KBps_out;KBps_in_now;KBps_out_now");

  mysql_connect("127.0.0.1:$port", "root", "%Fidewz37%") or die (mysql_error());
  //mysql_select_db("phoenix") or die (mysql_error());
  //mysql_query('SET NAMES latin1') or die ('SET NAMES latin1');
  $sql_query = "SHOW /*!50002 GLOBAL */ STATUS";
  $res = mysql_query($sql_query) or die($sql_query . ": " . mysql_error());
  $log_text = date("Y-m-d H:i:s") . ";";
  while ($row = mysql_fetch_assoc($res)) {
	  if (in_array($row['Variable_name'], $status_vars)) {
      $vars[$row['Variable_name']] = $row['Value'];
  	}  
  }
  mysql_free_result($res);

  $uptime_now = $vars['Uptime'] - $prev_vars[1];
  $queries_now = $vars['Questions'] - $prev_vars[2];
  $select_now = $vars['Com_select'] - $prev_vars[3];
  $slow_now = $vars['Slow_queries'] - $prev_vars[6];
  $hits_now = $vars['Qcache_hits'] - $prev_vars[10];
  $bytes_in_now = $vars['Bytes_received'] - $prev_vars[15];
  $bytes_out_now = $vars['Bytes_sent'] - $prev_vars[16];

  $log_text .= number_format($vars['Uptime'], 0, '.', ',') . ";";
  $log_text .= number_format($vars['Questions'], 0, '.', ',') . ";";
  $log_text .= number_format($vars['Com_select'], 0, '.', ',') . ";";
  $log_text .= number_format(round($vars['Questions'] / $vars['Uptime']), 0, '.', ',') . ";";
  $log_text .= number_format(round($queries_now / $uptime_now), 0, '.', ',') . ";";
  $log_text .= number_format($vars['Slow_queries'], 0, '.', ',') . ";";
  $log_text .= sprintf("%01.1f", round($vars['Slow_queries'] / $vars['Uptime'], 1)) . ";";
  $log_text .= number_format($slow_now, 0, '.', ',') . ";";
  $log_text .= number_format($vars['Threads_connected'], 0, '.', ',') . ";";
  $log_text .= number_format($vars['Qcache_hits'], 0, '.', ',') . ";";
  $log_text .= sprintf("%01.1f", round($vars['Qcache_hits'] / $vars['Uptime'], 1)) . ";";
  $log_text .= sprintf("%01.1f", round($hits_now / $uptime_now, 1)) . ";";
  $log_text .= sprintf("%01.1f", round($vars['Qcache_hits'] / ($vars['Qcache_hits'] + $vars['Com_select']) * 100, 1)) . ";";
  $log_text .= sprintf("%01.1f", round($hits_now / ($hits_now + $select_now) * 100, 1)) . ";";
  $log_text .= number_format($vars['Bytes_received'], 0, '.', ',') . ";";
  $log_text .= number_format($vars['Bytes_sent'], 0, '.', ',') . ";";
  $log_text .= sprintf("%01.1f", round($vars['Bytes_received'] / $vars['Uptime'] / 1024, 1)) . ";";
  $log_text .= sprintf("%01.1f", round($vars['Bytes_sent'] / $vars['Uptime'] / 1024, 1)) . ";";
  $log_text .= sprintf("%01.1f", round($bytes_in_now / $uptime_now / 1024, 1)) . ";";
  $log_text .= sprintf("%01.1f", round($bytes_out_now / $uptime_now / 1024, 1)) . "";
  writeLog($log_text);
}

// http://www.codediesel.com/php/tail-functionality-in-php/
function tail($filename, $lines = 10)
{
  $data = '';
  $fp = @fopen($filename, "r");
  $block = 4096;
  $max = @filesize($filename);

  for($len = 0; $len < $max; $len += $block) 
  {
    $seekSize = ($max - $len > $block) ? $block : $max - $len;
    fseek($fp, ($len + $seekSize) * -1, SEEK_END);
    $data = fread($fp, $seekSize) . $data;

    if(substr_count($data, "\n") >= $lines + 1) 
    {
      /* Make sure that the last line ends with a '\n' */
      if(substr($data, strlen($data)-1, 1) !== "\n") {
          $data .= "\n";
      }

      preg_match("!(.*?\n){". $lines ."}$!", $data, $match);
      fclose($fp);
      return $match[0];
    }
  }
  @fclose($fp);
  return $data; 
}

function writeLog($log_text)
{
  global $log_name;
	
  $fileHandle = @fopen($log_name, 'a');
  @fwrite($fileHandle, $log_text .  "\n");
  @fclose($fileHandle);
  return;
}

?>