CODEWITHSUNDEEP
phpmysqlbackup
user3604203
user3604203

Reputation:

PHP MySql Backup not working - no errors showing

I've been asked to set up a scheduled backup on an old vps server. The website runs on classic ASP, I've been trying to find code to backup using ASP but can't see an easy bit of code for it.

I've now tried doing the backup using PHP code I've used on PHP websites, it doesn't display an errors or create a backup file. The version of PHP running on the website is 5.2.17

Here's the PHP code I've tried.

$fileNameBackup  = 'daily-db-backup-'.date( 'Y-m-d' ).'.sql.gz';
$return_var = NULL;
$output = NULL;
$uniqueFilename = uniqid();
$command = "mysqldump -u ".$user." -h ".$host." -p ".$password." ".$database." | gzip > ".$fileNameBackup;
exec($command, $output, $return_var);

Where do I go from here? If there were errors I'd have something to fight against but am out of ideas, can someone help me please?

Upvotes: 0

Views: 239

Answers (3)

CB_Ron
CB_Ron

Reputation: 599

I would check to make sure that the user account IIS is running under (usually IIS_IUSRS) or that MySQL is running under has write access to the folder you are trying to backup to. In ASP code that would throw an Access Denied error but I don't know about PHP.

Upvotes: 0

karunakaran c
karunakaran c

Reputation: 27

<?php
set_time_limit (0);
!defined('DB_HOST_NAME') ? define('DB_HOST_NAME',"localhost") : '' ;
!defined('DATABASE') ? define('DATABASE',"yourdb") : '' ;
!defined('USERNAME') ? define('USERNAME',"root") : '' ;
!defined('PASSWORD') ? define('PASSWORD',"") : '' ;

$DBCONN = new PDO("mysql:host=".DB_HOST_NAME.";dbname=".DATABASE,USERNAME,PASSWORD);
$DBCONN->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$sql = "SHOW TABLES FROM ".DATABASE;
$result = $DBCONN->prepare($sql);
$result->execute();
while($tblnms=$result->fetch()){
  $table_name[] = $tblnms[0];
}
function backup_tables($host,$user,$pass,$name,$tables)
{
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    $return = "";
    // Get all of the tables
    if($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        if (is_array($tables)) {
            $tables = explode(',', $tables);
        }
    }

    // Cycle through each provided table
    foreach($tables as $table) {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        // First part of the output – remove the table
       // $return .= 'DROP TABLE ' . $table . ';<|||||||>';

        // Second part of the output – create table
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return .= "\n\n" . $row2[1] . ";\n\n";

        // Third part of the output – insert values into new table
        for ($i = 0; $i < $num_fields; $i++) {
            while($row = mysql_fetch_row($result)) {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = str_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j<($num_fields-1)) {
                        $return.= ',';
                    }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }

    // Generate the filename for the sql file
    $filess ='uploads/dbbackup_' . date('dmY').'_'.time() . '.sql';
    $zipfilenm='dbbackup_' . date('dmY').'_'.time() . '.zip'; 
    // Save the sql file
    $handle = fopen($filess,'w+');
    fwrite($handle,$return);
    fclose($handle);

    if(extension_loaded('zip'))
    { 
        // Checking ZIP extension is available
        $zip = new ZipArchive(); // Load zip library 
        $zip_name = 'uploads/'.$zipfilenm;
        if($zip->open($zip_name, ZIPARCHIVE::CREATE)!==TRUE)
        { 
            // Opening zip file to load files
            $error .= "* Sorry ZIP creation failed at this time";
        } 

        $zip->addFile($filess); // Adding files into zip

        $zip->close();
        if(file_exists($zip_name))
        {
            // push to download the zip
            header('Content-type: application/zip');
            header('Content-Disposition: attachment; filename="'.$zip_name.'"');
            readfile($zip_name);
            // remove zip file is exists in temp path
            unlink($zip_name);
        }
    }
    else
    $error .= "* You dont have ZIP extension";

    mysql_close();
}
backup_tables('localhost','root','','yourdb',"*");

?>

Upvotes: 0

gaganshera
gaganshera

Reputation: 2639

Try re-writing the mysqldump command to this:

$command = "mysqldump -u ".$user." -h ".$host." -p".$password." ".$database." | gzip > ".$fileNameBackup;

Notice I've removed the space after -p, since it should be continuous.

From the docs -

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysql prompts for one.

Upvotes: 1

Related Questions