Mysql backup using php script

When you make backup using mysqldump, you will get one big file in your backup folder. If you want to move the backup file to another server or just want to save it in your local computer, it will take time because of the size of the file. Indeed, you can split it per table. This script can help you to backup your mysql database, and splits the backup file every 10 tables.

#!/usr/bin/php

$host = 'dbserver';
$user = 'username';
$pass = 'password';
$name = 'databasename';

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
$tables = array();
$result = mysql_query('SHOW TABLES');
$num_rows = mysql_num_rows($result);
$t = 1;

    while($row = mysql_fetch_row($result))
    {
        $tables[] = $row[0];

        if (($t % 10) == 0){
                $newtable = implode(',',$tables);
                backup_tables($host,$user,$pass,$name,$newtable);
                $tables = array();
        }
        if ($t == $num_rows){
               $newtable = implode(',',$tables);
               backup_tables($host,$user,$pass,$name,$newtable);
        }

        $t++;
    }

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

  $maxrec = 100;  // put insert statement every 100 record
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);

  //get all of the tables
  if($tables == '*')
  {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
      $tables[] = $row[0];
    }
  }
  else
  {
    $tables = is_array($tables) ? $tables : explode(',',$tables);
  }
  $return = '';
  //cycle through
  foreach($tables as $table)
  {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);
    $num_rows = mysql_num_rows($result);

    $xx = 0;

    for ($i = 0; $i < $num_fields; $i++)
    {

      while($row = mysql_fetch_row($result))
      {

        if ($xx % $maxrec == 0) $return.= 'INSERT INTO '.$table.' VALUES';
        $xx++;
        $return.= '(';

        for($j=0; $j<$num_fields; $j++)
        {
          $row[$j] = addslashes($row[$j]);
          $row[$j] = ereg_replace("\n","\\n",$row[$j]);
          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
          if ($j<($num_fields-1)) { $return.= ','; }
        }

        if ($xx % $maxrec == 0)  $return.= ");\n";
        else  {
                if ($xx == $num_rows)   $return.= ");\n"; else $return.= "),";
        }
      }
    }
    $return.="\n\n";
  }

  //save file
  $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
  fwrite($handle,$return);
  fclose($handle);
}

Reference

http://davidwalsh.name/backup-mysql-database-php