#!/usr/bin/env php
<?php
date_default_timezone_set('UTC');

// Removes the script
array_shift($argv);


$shorts = "d";
$longs = [
  "dry",
  /*
  "compress",
  "ignore-schemas:",
  "dump-schemas:",
  "ignore-tables:",
  "dump-tables:",
  "output-folder:",
  "output-mode:",
  "mysql-host:",
  "mysql-port:",
  "mysql-user:",
  "mysql-pass:",
  "ssh:",
  "ssh-host:",
  "ssh-port:",
  "ssh-user:",
  */
  ];
$options = getopt($shorts, $longs);
foreach($options as $opt=>$val) {
  $param = (strlen($opt)==1) ? '-'.$opt : '--'.$opt;
  $pos = array_search($param, $argv);
  if($pos !== false) {
    unset($argv[$pos]);
  }
}

// Checks configs
if(empty($argv)) {
  echo "Please pass configuration files as parameters.\nExample: ./".basename(__FILE__)." server1.ini server2.ini\n";
  exit(1);
}

foreach($argv as $configFile) {
  try {
    $bkp = new MyBkp($configFile, $options);
  } catch(Exception $e) {
    echo "ERROR\n";
    echo "    ".$e->getMessage();
    echo "\n\n";
    exit(1);
  }
}


final class MyBkp {


  private $serverVersion;
  private $config;
  private $tables = [];
  private $schemas = [];

  private $options = [];

  public function __construct($configFile, $options=[]) {
    if(!is_file($configFile)) {
      throw new Exception('CONFIG FILE MISSING: '.$configFile);
    }
    $config = parse_ini_file($configFile);
    $this->options = $options;

    // TODO: Check configuration requirements
    $defaults = [
      'mysql_host' => 'localhost',
      'output_mode' => 'full',
      'compress' => true,
      'ignore_tables' => [],
      'dump_tables' => [],
      'ignore_schemas' => [],
      'dump_schemas' => [],
      'table_dump_limit' => 500,
    ]; 
    $this->config = array_merge($defaults, $config);

    $this->init();
    $this->printPreCheck();

    $this->dump();
  }




  // Initialize
  public function init() {

    // Delete previous backups if any
    $this->cleanBackups();

    // Test the connection
    $this->getDatabaseVersion();

    // Retrieve table sizes
    $this->getDatabaseTables();

    // Check if there's enough space
    // TODO: Handle mounts, use the final output path to calculate disk space
    $bkpSize = $this->getBackupSize();
    $diskSpace = $this->getOutputSpace();
    if($diskSpace !== false && $bkpSize > $diskSpace) {
      throw new Exception("NOT ENOUGH DISK SPACE ON: ".$this->getOutputPath());
    }
  }




  // Cleans previous backups
  public function cleanBackups() {
    if(isset($this->config['keep']) && $this->config['keep'] > 0) {
      $keepDays = (int) $this->config['keep'];
      $keepLimit = strtotime('- '.$keepDays.' days');

      $dir = $this->getOutputPath();
      $backupDirs = glob( $dir.'*/*', GLOB_ONLYDIR);
      foreach($backupDirs as $backupDir) {
        $dirTime = filemtime($backupDir);
        if($dirTime < $keepLimit) {
          exec('rm -rf '.$backupDir);
        }
      }

    }
  }



  // Prints prechecks
  public function printPreCheck() {
$out = "
Database
========
 Connection: ".(empty($this->config['mysql_user'])?'':$this->config['mysql_user'].'@').$this->config['mysql_host'].(empty($this->config['mysql_port'])?'':':'.$this->config['mysql_port'])."
     Server: ".$this->getDatabaseVersion()."
    Schemas: ".count($this->schemas)."
     Tables: ".$this->countTables()."
       Size: ".$this->getDatabaseSize()." MB
";
    if(!empty($this->config['ssh'])) {
$out .= "        SSH: ".(empty($this->config['ssh_user'])?'':$this->config['ssh_user'].'@').$this->config['ssh_host'].(empty($this->config['ssh_port'])?'':':'.$this->config['ssh_port'])."
";
    }
$out .= "
Backup
======
    Schemas: ".count($this->getBackupSchemas())."
     Tables: ".$this->countBackupTables()."
       Size: ".$this->getBackupSize()." MB
     Output: ".$this->getOutputPath()."
 Disk space: ".$this->getOutputSpace()." MB
       Mode: ".$this->config['output_mode']."
   Compress: ".($this->config['compress']?'Yes':'No')."

";
    echo $out;
  }



  // Count tables
  public function countTables($schema=null) {
    if($schema) {
      return count($this->tables[$schema]);
    }
    $size = 0;
    foreach($this->schemas as $schema) {
      $size += count($this->tables[$schema]);
    }
    return $size;
  }



  // Gets the output path
  public function getOutputPath() {
    $output = __DIR__.'/dumps/';
    if(!empty($this->config['output_folder'])) {
      $output = $this->config['output_folder'];
    }
    if(substr($output,-1) != '/') {
      $output .= '/';
    }
    if($this->config['mysql_host'] == 'localhost' && !empty($this->config['ssh']) && !empty($this->config['ssh_host'])) {
      $output .= preg_replace('/[^a-z0-9]+/','_', $this->config['ssh_host']).'/';
    } else {
      $output .= $this->config['mysql_host'].'/';
    }
    return $output;
  }



  // Returns the free space of the disk to output
  public function getOutputSpace() {
    $outputPath = $this->getOutputPath();
    $output = [];
    exec('df -m '.$outputPath, $output);
    $data = [];
    $heads = [];
    foreach($output as $line) {
      $parts = explode(' ',$line);
      $parts = array_filter($parts);
      $parts = array_values($parts);
      if(empty($heads)) {
        foreach($parts as $pos=>$col) {
          $heads[$pos] = strtoupper($col);
        }
      } else {
        foreach($parts as $pos=>$col) {
          if(is_numeric($col) && isset($data[ $heads[$pos] ])) {
            $data[ $heads[$pos] ] += $col;
          } else {
            $data[ $heads[$pos] ] = $col;
          }
        }
      }
    }
    return isset($data['AVAILABLE']) ? $data['AVAILABLE'] : false;
  }



  // Count backup tables
  public function countBackupTables($schema=null) {
    $tables = $this->getBackupTables($schema);
    if($schema) {
      return count($tables);
    }
    $size = 0;
    foreach($tables as $schema=>$subtables) {
      $size += count( $this->getBackupTables($schema) );
    }
    return $size;
  }



  // Gets the tables to backup (table names)
  public function getBackupTables($schema=null) {
    if($schema) {
      $tables = array_keys($this->tables[$schema]);
      return $this->filterList( $tables, $this->config['ignore_tables'], $this->config['dump_tables'] );
    }
    $result = [];
    $schemas = $this->getBackupSchemas();
    foreach($schemas as $schema) {
      $tables = array_keys($this->tables[$schema]);
      $result[ $schema ] = $this->filterList( $tables, $this->config['ignore_tables'], $this->config['dump_tables']);
    }
    return $result;
  }



 // Gets large tables
 public function getBackupLargeTables($schema) {
   $larges = [];
   $backupTables = $this->getBackupTables($schema);
   foreach($backupTables as $table) {
     $size = $this->tables[$schema][$table];
     if($size > $this->config['table_dump_limit']) {
       $larges[] = $table;
     }
   }
   return $larges;
 }



  // Gets the schemas to backup
  public function getBackupSchemas() {
    return $this->filterList( $this->schemas, $this->config['ignore_schemas'], $this->config['dump_schemas'] );
  }



  // Filters a list of ignores and force
  private function filterList($values, array $ignores=[], array $forces=[]) {
    if(!empty($ignores)) {
      foreach($ignores as $exp) {
        $exp = str_replace('*', '[^\s,]+', $exp);
        $exp = '/^'.$exp.'$/';
        foreach($values as $pos=>$value) {
          if(preg_match($exp, $value)) {
            unset($values[$pos]);
          }
        }
      }
    }
    if(!empty($forces)) {
      $exps = [];
      foreach($forces as $exp) {
        $exp = str_replace('*', '[^\s,]+', $exp);
        $exp = '/^'.$exp.'$/';
        $exps[] = $exp;
      }
      foreach($values as $pos=>$value) {
        $found = false;
        foreach($exps as $exp) {
          if(preg_match($exp, $value)) {
            $found = $pos;
          }
        }
        if($found === false) {
          unset($values[$pos]);
        }
      }
    }
    return $values;
  }


  // Gets the backup size (+/- compression/indexes/etc)
  public function getBackupSize($schema=null) {
    $schemas = empty($schema) ? $this->getBackupSchemas() : [ $schema ];
    $backupSize = 0;
    foreach($schemas as $schema) {
      $backupTables = $this->getBackupTables($schema);
      foreach($this->tables[$schema] as $table=>$size) {
        if(in_array($table,$backupTables)) {
          $backupSize += $size;
        }
      }
    }
    return round($backupSize,1);
  }
 


  // Returns the size of the database
  // The actual value might be smaller or larger, depending on indexes and compression
  public function getDatabaseSize($schema=null) {
    if($schema) {
      if(isset($this->tables[$schema])) {
        return array_sum($this->tables[$schema]);
      }
      return 0;
    }
    $size = 0;
    foreach($this->tables as $schema=>$tables) {
      $size+=array_sum($tables);
    }
    return $size;
  }



  // Gets the path for a backup
  public function getBackupPath($schema, $table=null) {
    $date = gmdate('Ymd');
    $dir = $this->getOutputPath().$schema.'/'.$date.'/';
    if(!is_dir($dir)) {
      mkdir($dir, 0700, true);
    }
    if(is_string($table)) {
      $name = 'table-'.$table.'.sql';
    } else {
      $name = 'db-'.$schema.'-'.$date.'.sql';
    }
    $i = 1;
    while(is_file($dir.$name)) {
      if(is_string($table)) {
        $name = 'table-'.$table.'-'.$i.'.sql';
      } else {
        $name = 'db-'.$schema.'-'.$date.'-'.$i.'.sql';
      }
      $i++;
    }
    $path = $dir.$name;
    if(!empty($this->config['compress'])) {
      $path .= '.gz';
    }
    return $path;
  }



  // Retrieves server info
  public function getDatabaseVersion() {
    if(empty($this->serverVersion)) {
      $sql = "SHOW VARIABLES LIKE 'version%'";
      $cmd = $this->prepareQuery($sql);
      $output = [];
      exec($cmd, $output, $return);
      switch($return) {
        case 1:
          throw new Exception('DATABASE CONNECTION FAILED');
          break;
        case 255:
          throw new Exception('SSH CONNECTION FAILED');
          break;
      }
      array_shift($output);
      $vars = [];
      foreach($output as $line) {
        $parts = explode("\t",$line,2);
        if(count($parts) == 2) {
          $vars[ $parts[0] ] = $parts[1];
        }
      }
      if(empty($vars)) {
        throw new Exception('NON-COMPATIBLE MYSQL VERSION');
      }
      $this->serverVersion = implode(' ', $vars);
    }
    return $this->serverVersion;
  }



  // Retrieves tables and their size
  public function getDatabaseTables($schema=null) {
    if(empty($this->tables)) {
      $sql = "SELECT table_schema, table_name, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size FROM information_schema.tables ";
      $sql .= " WHERE table_schema!='information_schema' AND table_schema!='performance_schema' ";
      if($schema) {
        $sql .= " AND table_schema='$schema' ";
      }
      $sql .= " GROUP BY table_schema, table_name";
      $cmd = $this->prepareQuery($sql, 'information_schema');
      $output = [];
      exec($cmd, $output);
      array_shift($output);
      foreach($output as $line) {
        $parts = explode("\t",$line,3);
        if(!in_array($parts[0], $this->schemas)) {
          $this->schemas[] = $parts[0];
          $this->tables[$parts[0]] = [];
        }
        $this->tables[$parts[0]][$parts[1]] = $parts[2];
      }
    }
    return $this->tables;
  }



  // Prepares for remote execution
  private function remotify($query) {
    if(!empty($this->config['ssh'])) {
      $cmd = [];
      $cmd[] = 'ssh';
      $cmd[] = $this->config['ssh_host'];
      if(!empty($this->config['ssh_user'])) {
        $cmd[] = '-l '.$this->config['ssh_user'];
      }
      if(!empty($this->config['ssh_port'])) {
        $cmd[] = '-p '.$this->config['ssh_port'];
      }
      $cmd[] = '"'.str_replace('"','\"',$query).'"';
      $query = implode(' ',$cmd);
    }
    return $query;
  }



  // Prepares a query for execution
  private function prepareQuery($sql, $schema=null) {
    $cmd = [];
    $cmd[] = 'mysql';
    if(!empty($this->config['mysql_host'])) {
      $cmd[] = '--host='.$this->config['mysql_host'];
    }
    if(!empty($this->config['mysql_port'])) {
      $cmd[] = '--port='.$this->config['mysql_port'];
    }
    if(!empty($this->config['mysql_user'])) {
      $cmd[] = '--user='.$this->config['mysql_user'];
    }
    if(!empty($this->config['mysql_pass'])) {
      $cmd[] = '--password='.$this->config['mysql_pass'];
    }
    if($schema) {
      $cmd[] = $schema;
    }
    $cmd[] = '-e';
    $cmd[] = '"'.str_replace('"','\"',$sql).'"';
    $cmd[] = ' 2> /dev/null';
    $query = implode(' ',$cmd);
    $query = $this->remotify($query);
    return $query;
  }



  // Starts the dump
  public function dump() {
    $schemas = $this->getBackupSchemas();
    $optionKeys = array_keys($this->options);
    $dryOptions = array_intersect(['d','dry'], $optionKeys);
    $isDry = empty($dryOptions) ? false : true;
    foreach($schemas as $schema) {
      $rows = $this->getBackupTables($schema);
      $larges = $this->getBackupLargeTables($schema);
      $cmds = [];
      if($this->config['output_mode'] != 'single') {
        foreach($rows as $pos=>$row) {
          if(in_array($row, $larges)) {
            unset($rows[$pos]);
          }
        }
        $tables = [$rows];
        foreach($larges as $large) {
          $tables[] = $large;
        }
      } else {
        $tables = $rows;
      }
      foreach($tables as $row) {
        if(is_array($row)) {
          echo "> BACKING UP SCHEMA $schema - ";
        } else {
          echo "> BACKING UP SCHEMA $schema TABLE $row - ";
        }
        if($isDry) {
          echo "READY\n";
          continue;
        }
        exec( $this->prepareDump($schema, $row, $outfile ) , $out, $ret);
        if($ret) {
          switch($ret) {
            case 1:
              echo "DB ERROR\n";
              break;
            case 255:
              echo "SSH ERROR\n";
              break;
            default:
              echo "UNKNOWN ERROR\n";
              break;
          }
          if(!empty($out)) {
            echo "    ".implode("\n    ", $out);
          }
        } else {
          echo "OK\n";
          echo "    $outfile [".round(ceil(filesize($outfile)/1024/1024*10)/10, 1, PHP_ROUND_HALF_UP)." MB]\n";
        }
        echo "\n";
      }
    }
  }



  // Prepares a dump for execution
  private function prepareDump($schema, $tables, &$path) {
    $flags = [
        '--skip-add-drop-table',
        '--add-locks',
        '--skip-comments',
        '--compress',
        '--create-options',
        '--disable-keys',
        '--extended-insert',
        '--skip-lock-tables',
        '--quick',
        '--quote-names',
        '--set-charset',
        '--single-transaction',
        '--dump-date',
        '--triggers',
        '--tz-utc',
      ]; 
    $cmd = [];
    $cmd[] = 'mysqldump';
    foreach($flags as $flag) {
      $cmd[] = $flag;
    }
    if(!empty($this->config['mysql_protocol'])) {
      $cmd[] = '--protocol='.$this->config['mysql_protocol'];
    }
    if(!empty($this->config['mysql_host'])) {
      $cmd[] = '--host='.$this->config['mysql_host'];
    }
    if(!empty($this->config['mysql_port'])) {
      $cmd[] = '--port='.$this->config['mysql_port'];
    }
    if(!empty($this->config['mysql_user'])) {
      $cmd[] = '--user='.$this->config['mysql_user'];
    }
    if(!empty($this->config['mysql_pass'])) {
      $cmd[] = '--password='.$this->config['mysql_pass'];
    }
    $cmd[] = $schema;
    if(is_array($tables)) {
      $cmd[] = implode(' ', $tables);
    } else {
      $cmd[] = $tables;
    }
    $query = implode(' ', $cmd);
    $query = $this->remotify($query);
    if(!empty($this->config['compress'])) {
      $query .= ' | gzip --best ';
    }
    $path = $this->getBackupPath($schema, $tables);
    $query .= ' > '.$path;
    $query .= ' && chmod 0600 '.$path;
    return $query;
  }


}


