mysql backup cron

backup_mysql.php

#! /usr/bin/php -q
<?php

  define('_STATEDIR', '/data/state/');
  define('_ROOTPASS', 'root password for mysql');

  # connexion a la base
  $conn = mysql_connect('localhost', 'root', _ROOTPASS);
    if (!$conn) die ('erreur connexion DB '.mysql_error());

  $f = mysql_query("SHOW DATABASES");
    if (!$f) die ('erreur SHOW DATABASES '.mysql_error());

  while ($t = mysql_fetch_row($f)) {
    if (!in_array($t[0], array('mysql')))
      backup_base($t[0]);
  }
  exit(0);



  function backup_base($base) {
    $f = mysql_query("SHOW TABLES FROM $base");
      if (!$f) die ('erreur SHOW TABLES '.mysql_error());

    printf ("\n% 20s: ", $base);
    while ($t = mysql_fetch_row($f)) {
      backup_table($t[0], $base);
    }

    # regler les droits des shim_**
    if (preg_match(',^shim_,', $base)
    AND is_dir(_STATEDIR."mysql/$base"))
      system("chown -R $base.$base "._STATEDIR."mysql/$base");
  }

  function backup_table($table, $base) {
    # ne pas backuper spip_index***
    if (preg_match(',^spip_index,', $table)) {
      echo "-";
      return;
    }
    
    # dumper la table dans le repertoire _STATEDIR/mysql/$base/
    @mkdir (_STATEDIR."mysql/$base");
    chdir (_STATEDIR."mysql/$base")
      OR die ("erreur chdir "._STATEDIR."mysql/$base");

    system("mysqldump --opt -uroot -p"._ROOTPASS." '$base' '$table'| gzip - > "._STATEDIR."mysql/$base/$table.gz");
    echo "+";
  }

?>