0

I'm trying to could backup one of tables from database using php only. I'd like to save result in some directory at my server or (the best) send it via e-mail.

My problem is that:
- exec() is disabled
- system() is disabled

I'm tried to use "SELECT * INTO OUTFILE" and "LOAD DATA INFILE" but I got blank page (with my "error" message) each time.

I use PHP 5.2 (but I checked it also under version: 4, 5.3.2 and 6). There's no error raport from PHP side (all errors display options are turned on).

"Backup" directory have chmod 777 so that's not problem.

My files looks this way:

<?php

$host = "localhost"; // Host name
$username = "username"; // Mysql username
$password = "password"; // Mysql password
$db_name = "MyDbName"; // Database name
$tableName = "MyTestTable"; // Table name

// Connect to server and select database.
mysql_connect($host, $username, $password)or die("cannot connect");
mysql_select_db($db_name)or die("cannot select DB");

$backupFile = "/home/myusername/domains/mydomain.com/public_html/admin/backup/mybackup.sql";
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

    if($result){
    
    echo "success";
    
    } else {
    
    echo "error";
    }
    
// close connection
mysql_close();

?>
<?php

$host = "localhost"; // Host name
$username = "username"; // Mysql username
$password = "password"; // Mysql password
$db_name = "MyDbName"; // Database name
$tableName = "MyTestTable"; // Table name

// Connect to server and select database.
mysql_connect($host, $username, $password)or die("cannot connect");
mysql_select_db($db_name)or die("cannot select DB");

$backupFile = "/home/myusername/domains/mydomain.com/public_html/admin/backup/mybackup.sql";
$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);

    if($result){
    
    echo "success";
    
    } else {
    
    echo "error";
    }
    
// close connection
mysql_close();

?>
2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by sallecpt
0

there are loads of scripts on the net for this. google for the site phpclasses - they have loads of scripts too.

however, here is one that I use.

there are 4 files in total. it will backup the database en if you want, store a copy on the server, and if you want too, email it to an address.

#######################################

action_dbexport_run.php

<?php
$vr="1.0";
$db_server = '';
$db = '';
$mysql_username = '';
$mysql_password = ''; 
$from_emailaddress = 'doe@mail.com';
$to_emailaddress = 'jen@mail.com';
$save_backup_zip_file_to_server = 1;
$limit_to=10000000;
$limit_from=0;
$time_internal=5;
error_reporting(0);
define('LOCATION', dirname(__FILE__) ."/");
include(LOCATION."action_dbexport_sqlbackup.php");
?>

action_dbexport_schemaforexport.php

<?php
$vr="1.0";
$link = mysql_connect($db_server,$mysql_username,$mysql_password);
if ($link) mysql_select_db($db);
if (mysql_error()) exit(mysql_error($link));
if(mysql_num_rows(mysql_query("SHOW TABLES LIKE 'phpmysqlautobackup' "))==0)
{
   $query = "
    CREATE TABLE phpmysqlautobackup (
    id int(11) NOT NULL,
    version varchar(6) default NULL,
    time_last_run int(11) NOT NULL,
    PRIMARY KEY (id)
    ) TYPE=MyISAM;";
   $result=mysql_query($query);
   $query="INSERT INTO phpmysqlautobackup (id, version, time_last_run)
             VALUES ('1', '$vr', '0');";
   $result=mysql_query($query);
}
$query="SELECT * from phpmysqlautobackup WHERE id=1 LIMIT 1 ;";
$result=mysql_query($query);
$time_last_run=mysql_result($result,0,'time_last_run');
if (time() < ($time_last_run+$time_internal)) exit();
$query="UPDATE phpmysqlautobackup SET time_last_run = '".time()."' WHERE id=1 LIMIT 1 ;";
$result=mysql_query($query);
if (!isset($table_select))
{
  $t_query = mysql_query('show tables');
  $i=0;
  $table="";
  while ($tables = mysql_fetch_array($t_query, MYSQL_ASSOC) )
        {
         list(,$table) = each($tables);
         $table_select[$i]=$table;
         $i++;
        }
}
$thedomain = $_SERVER['HTTP_HOST'];
if (substr($thedomain,0,4)=="www.") $thedomain=substr($thedomain,4,strlen($thedomain));
$buffer = '# MySQL backup created by xxx type your stuff here' . "\r\n" .
          '#' . "\r\n" .
          '# ' "\r\n" .
          '#' . "\r\n" .
          '# Database: '. $db . "\r\n" .
          '# Domain name: ' . $thedomain . "\r\n" .
          '# (c)' . date('Y') . ' ' . $thedomain . "\r\n" .
          '#' . "\r\n" .
          '# Backup Date: ' . strftime("%d %b %Y",time()) . "\r\n\r\n";
$i=0;
foreach ($table_select as $table)
        {
          $i++;
          $export = "\r\n" .'drop table if exists ' . $table . ';' . "\r\n\r\n" .
                    'create table ' . $table . ' (' . "\r\n";
          $table_list = array();
          $fields_query = mysql_query("show fields from " . $table);
          while ($fields = mysql_fetch_array($fields_query)) {
            $table_list[] = $fields['Field'];
            $export .= '  ' . $fields['Field'] . ' ' . $fields['Type'];
            if (strlen($fields['Default']) > 0) $export .= ' default \'' . $fields['Default'] . '\'';
            if ($fields['Null'] != 'YES') $export .= ' not null';
            if (isset($fields['Extra'])) $export .= ' ' . $fields['Extra'];
            $export .= ',' . "\r\n";
          }
          $export = ereg_replace(",\r\n$", '', $export);
          $index = array();
          $keys_query = mysql_query("show keys from " . $table);
          while ($keys = mysql_fetch_array($keys_query)) {
            $kname = $keys['Key_name'];
            if (!isset($index[$kname])) {
              $index[$kname] = array('unique' => !$keys['Non_unique'],
                                     'columns' => array());
            }
            $index[$kname]['columns'][] = $keys['Column_name'];
          }
          while (list($kname, $info) = each($index)) {
            $export .= ',' . "\r\n";
            $columns = implode($info['columns'], ', ');
            if ($kname == 'PRIMARY') {
              $export .= '  PRIMARY KEY (' . $columns . ')';
            } elseif ($info['unique']) {
              $export .= '  UNIQUE ' . $kname . ' (' . $columns . ')';
            } else {
              $export .= '  KEY ' . $kname . ' (' . $columns . ')';
            }
          }
          $export .= "\r\n" . ');' . "\r\n\r\n";
          $buffer.=$export;
          $query="select * from " . $table ." LIMIT ". $limit_from .", ". $limit_to." ";
          $rows_query = mysql_query($query);
          while ($rows = mysql_fetch_array($rows_query)) {
            $export = 'insert into ' . $table . ' (' . implode(', ', $table_list) . ') values (';
            reset($table_list);
            while (list(,$i) = each($table_list)) {
              if (!isset($rows[$i])) {
                $export .= 'NULL, ';
              } elseif (has_data($rows[$i])) {
                $row = addslashes($rows[$i]);
                $row = ereg_replace("\r\n#", "\r\n".'\#', $row);
                $export .= '\'' . $row . '\', ';
              } else {
                $export .= '\'\', ';
              }
            }
            $export = ereg_replace(', $', '', $export) . ');' . "\r\n";
            $buffer.= $export;
          }
        }
mysql_close();
?>

action_dbexport_sqlbackup.php

<?php
$vr="1.0";
if(($db=="")OR($mysql_username==""))
{
 echo "Incorrect Configuration for xxx type your stuff here";
 exit;
}
if (isset($table_select))
{
 $backup_type="\nBACKUP Type: partial, includes tables:\n";
 foreach ($table_select as $key => $value) $backup_type.= "$value;\n";
}
else $backup_type="\nBACKUP Type: Full database backup (all tables included)\n\n";
include(LOCATION."action_dbexport_sqlextras.php");
include(LOCATION."action_dbexport_schemaforexport.php");
$backup_file_name = 'mysql_'.$db.strftime("_%d_%b_%Y_time_%H_%M_%S.sql",time()).'.gz';
$dump_buffer = gzencode($buffer);
if ($from_emailaddress>"") xmail($to_emailaddress,$from_emailaddress, $row_meta['SiteTitle']." Database Backup: $backup_file_name", $dump_buffer, $backup_file_name, $backup_type);
if ($save_backup_zip_file_to_server) write_backup($dump_buffer, $backup_file_name);
?>

action_dbexport_sqlextras.php

<?php
$vr="1.0";
function has_data($value)
{
 if (is_array($value)) return (sizeof($value) > 0)? true : false;
 else return (($value != '') && (strtolower($value) != 'null') && (strlen(trim($value)) > 0)) ? true : false;
}
function xmail ($to_emailaddress,$from_emailaddress, $subject, $content, $file_name, $backup_type)
{
 $mail_attached = "";
 $boundary = "----=_NextPart_000_01FB_010".md5($to_emailaddress);
 $mail_attached.="--".$boundary."\n"
                       ."Content-Type: application/octet-stream;\n name=\"$file_name\"\n"
                       ."Content-Transfer-Encoding: base64\n"
                       ."Content-Disposition: attachment; \n filename=\"$file_name\"\n\n"
                       .chunk_split(base64_encode($content))."\n";
 $mail_attached .= "--".$boundary."--\n";
 $add_header ="MIME-Version: 1.0\nContent-Type: multipart/mixed;\n        boundary=\"$boundary\" \n\n";
 $mail_content="--".$boundary."\n"."Content-Type: text/plain; \n charset=\"iso-8859-1\"\n"."Content-Transfer-Encoding: 7bit\n\n Database Backup Successful\n\nPlease see attached for your zipped Backup file; $backup_type \n\n---type your own stuff here for the email contents\n".$mail_attached;
 return mail($to_emailaddress, $subject, $mail_content, "From: $from_emailaddress\nReply-To:$from_emailaddress\n".$add_header);
}

function write_backup($gzdata, $backup_file_name)
{
 $fp = fopen(LOCATION."../sqlbackups/".$backup_file_name, "w");
 fwrite($fp, $gzdata);
 fclose($fp);
 if (!file_exists(".htaccess"))
 {
  $fp = fopen(LOCATION."../sqlbackups/.htaccess", "w");
  fwrite($fp, "deny from all");
  fclose($fp);
 }
}
?>

hope this helps
please just rename the files to what you want. also, you just run the run.php file, and it will do the job.

currently the backup dir to store the files in is sqlbackups in an upper dir, change there values to what you need.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.