MySQL/MariaDB-Dumps mit php

Meine Webseite und auch andere Programme nutzen PHP zusammen mit MySQL/MariaDB um Daten zu speichern und damit zu arbeiten.

Manchmal muss man die Daten auf einen anderen Computer mit umziehen. Da bietet es sich an PHP zu nutzen um die Tabellen zu sichern.
Leider bietet mysqli keinen dump an wie man den von der Commandozeile kennt, weshalb ich hier mal meine Export-Funktion für Datenbanken bereitgestellt habe.

Die Besonderheit ist, dass CREATE TABLE und CONSTRAINTS in separaten Dateien gespeichert werden, so dass beim importieren nicht auf die Reihenfolge der CREATE TABLEs geachtet werden muss.

/**
 * @brief create a database dump and store CREATE TABLE, CONSTRAINTS and DATA in separate files.
 * @param mysqli $oMysqli:               Already connected mysqli database
 * @param string $sPathToCreateFile:     Output file for CREATE TABLE instructions
 * @param string $sPathToConstraintFile: Output file for ADDING CONSTRAINTS like foreigen keys
 * @param string $sPathToDataFile:       Output file for INSERT TABLE values. This file could get very big.
 */
function exportDatabase($oMysqli, $sPathToCreateFile, $sPathToConstraintFile, $sPathToDataFile)
{
  $oFileCreate = fopen($sPathToCreateFile, "w");
  $oFileConstraint = fopen($sPathToConstraintFile, "w");
  $oFileData = fopen($sPathToDataFile, "w");
  if($oFileCreate && $oFileConstraint && $oFileData)
  {
    $queryTables    = $oMysqli->query('SHOW TABLES');
    $target_tables  = array();
    while($row = $queryTables->fetch_row())
    {
      $target_tables[] = $row[0];
    }
    foreach($target_tables as $table)
    {
      $res            =   $oMysqli->query('SHOW CREATE TABLE '.$table);
      $TableMLine     =   $res->fetch_row();
      $content        =   $TableMLine[1].";\n\n";
      $aLines = explode("\n", $content);
      $content = "";
      foreach($aLines as $sLine)
      {
        if(startsWith($sLine, "CREATE TABLE"))
        {
          $sLine = "CREATE TABLE IF NOT EXISTS ".substr($sLine, strlen("CREATE TABLE"));
        }
        else if(startsWith($sLine, ")"))
        {
          if(endsWith($content, ",\n"))
          {
            $content = substr($content, 0,-2)."\n";
          }
        }
        else
        {
          $sTempLine = trim($sLine);
          if(startsWith($sTempLine, "CONSTRAINT"))
          {
            $sLine = "";
            if(endsWith($sTempLine, ",")) $sTempLine = substr($sTempLine, 0,-1);
            $sAlterString ="ALTER TABLE `$table` ADD ".$sTempLine.";\n";
            fputs($oFileConstraint, $sAlterString);
          }
        }
        if(strlen($sLine) > 0)
        {
          $content .= $sLine."\n";
        }
      }
      $content .="\n\n";
      fputs($oFileCreate, $content);
    }

    foreach($target_tables as $table)
    {
      $content = "";
      $result         =   $oMysqli->query('SELECT * FROM '.$table);
      $fields_amount  =   $result->field_count;
      $rows_num       =   $oMysqli->affected_rows;
      for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0)
      {
        while($row = $result->fetch_row())
        { //when started (and every after 100 command cycle):
          if ($st_counter%100 == 0 || $st_counter == 0 )
          {
            $content .= "\nINSERT INTO ".$table." VALUES";
          }
          $content .= "\n(";
          for($j=0; $j<$fields_amount; $j++)
          {
            if (isset($row[$j]))
            {
              $row[$j] = $oMysqli->real_escape_string($row[$j]);
              $content .= "'".$row[$j]."'" ;
            }
            else
            {
              $content .= 'NULL';
            }
            if ($j<($fields_amount-1))
            {
              $content.= ',';
            }
          }
          $content .=")";
          //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
          if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num)
          {
            $content .= ";";
          }
          else
          {
            $content .= ",";
          }
          $st_counter=$st_counter+1;
        }
      }
      $content .="\n\n";
      fputs($oFileData, $content);
    }
  }
  if($oFile)
  {
    fclose($oFile);
  }
  if($oFileConstraint)
  {
    fclose($oFileConstraint);
  }
  if($oFileData)
  {
    fclose($oFileData);
  }
}

/**
 * @brief Check if a string is starting with a specific value.
 * @param string $haystack: String to check
 * @param string $needle:   Needle to search in beginning of $haystack
 * @return boolean true if $haystack starts with $needle
 */
function startsWith($haystack, $needle)
{
   $length = strlen($needle);
   return (substr($haystack, 0, $length) === $needle);
}

/**
 * @brief Check if a string is ending with a specific value.
 * @param string $haystack: String to check
 * @param string $needle:   Needle to search at the end of $haystack
 * @return boolean true if $haystack ends with $needle
 */
function endsWith($haystack, $needle)
{
  $length = strlen($needle);
  if ($length == 0) 
  {
    return true;
  }

  return (substr($haystack, -$length) === $needle);
}