0

I wanted to have database backup cron in webserver, I use following codes to output sql file:

<?php
include('connectdb.php');

$backupFile = 'database_backup_'.date("YmdHis").'.sql';
$command = 'mysqldump $dbname --password=$dbpass --user=$dbuser --single-transaction >'.$backupFile;
system($command);

include('closedb.php');
?>

but when I open the specific sql file, its only show text like this:

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

What's wrong with my code?

Thanks.

Improve this question
4
  • 2
    Why don't you just run the mysqldump command from the cron job, rather than having a PHP script do it? Commented Jun 17, 2013 at 9:03
  • Yea, this file will be throw in cron job Commented Jun 17, 2013 at 9:07
  • Possible duplicate of PHP regular backup of mysql data Commented Aug 19, 2016 at 4:05
  • Please add the solution as an answer instead. Commented Mar 17, 2017 at 18:26

4 Answers 4

Reset to default
2

This line should be quoted with double-quotes:

$command = 'mysqldump $dbname --password=$dbpass --user=$dbuser --single-transaction >'.$backupFile;

like this:

$command = "mysqldump $dbname --password=$dbpass --user=$dbuser --single-transaction >".$backupFile;

If not, the variable substitution that sets $dbnam, $dbpass etc won't happen

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

after I changed to double quot, my sql file show blank this time.
1

Try your mysqldump command from the command line with the correct parameters. Does it work? What errors does it report, if any (use the --verbose switch)? Fix them and try again.

When you've debugged your command, submit it directly to cron - no need to run it through a PHP script.

Improve this answer

5 Comments

I have mysqldump run in my local XAMPP, and the sql file is created with this command line mysqldump --opt -u dbuser -pdbpass dbname > backupFile.sql. when I run the same command in php cron, it show Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] For more options, use mysqldump --help, does it means by directory write permission causes the issue?
Quite possibly. It doesn't matter. Submit your working mysqldump directly to cron - no PHP script.
where is backupFile.sql goes to if doesn't specify which directory to save? and how could I assign current date time to every new file creation?
The backup file goes to where you specify - just specify it. You should be able to set the date with a simple shell script - still better than PHP. There's a sample bash script here. Set the user, password and target directory and test, then submit this to cron instead of mysqldump.
my backup file can generated in a specify directory, but still in blank file with 0kb.
1

The output you are getting us because you have an error in the mysqldump syntax.

This is a good syntax to follow:

mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

I would run this as a cron job by itself instead of using PHP. Especially if the db is large. You might then get time outs depending on your php settings.

Improve this answer

Comments

1

If your site is live and using Database then it is very important to take backup of you Database in regular time interval. But it is not possible to take the backup manually every time. So lets create a simple PHP function to do this job for us and we can call that function using Cron Job in regular time interval.

<?php
include("connection.php");
function backup_db(){
  /* Store All Table name in an Array */

$allTables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result)){
 $allTables[] = $row[0];
}
foreach($allTables as $table){
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);
$return.= 'DROP TABLE IF EXISTS '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";
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";
}
// Create Backup Folder
$folder = 'DB_Backup/';
if (!is_dir($folder))
mkdir($folder, 0777, true);
chmod($folder, 0777);
$date = date('m-d-Y-H-i-s', time()); 
$filename = $folder."db-backup-".$date; 
$handle = fopen($filename.'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
// Call the function
backup_db();
?>

And for your connection you can use this file.

connection.php File

<?php
  $host="localhost";
  $uname="YourUserName";
  $pass="YourPassword";
  $database = "YourDatabaseName";
  $connection=mysql_connect($host,$uname,$pass);
or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or die("Database could not be selected"); 
$result=mysql_select_db($database)
or die("database cannot be selected <br>");
?>

Save the above code in a PHP file and upload the file to your web server then run the file. And check if it create a folder called DB_Backup and create the backup .sql file inside DB_Backup folder. If the file works fine then you can assign the file to your Cron Job.

Improve this answer

1 Comment

That's not really helping, OP is trying to use the mysqldump command, which is exactly the right way to dump a mySql database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.