PHP MySQL database backup cron job

Question

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.

Why don't you just run the mysqldump command from the cron job, rather than having a PHP script do it? — , Jun 17 '13 at 09:03
Possible duplicate of [PHP regular backup of mysql data](http://stackoverflow.com/questions/38916163/php-regular-backup-of-mysql-data) — e4c5, Aug 19 '16 at 04:05

score 2 · Answer 1 · answered Jun 17 '13 at 09:02

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

answered Jun 17 '13 at 09:02

after I changed to double quot, my sql file show blank this time. – conmen Jun 17 '13 at 09:10

score 1 · Answer 2 · answered Jun 17 '13 at 09:03

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.

score 1 · Accepted Answer · answered Jun 18 '13 at 02:50

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.

answered Jun 18 '13 at 02:50

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? – conmen Jun 18 '13 at 03:11
Quite possibly. It doesn't matter. Submit your working `mysqldump` directly to cron - no PHP script. – Jun 18 '13 at 03:23
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? – conmen Jun 18 '13 at 03:33
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](http://www.dzone.com/snippets/simple-mysql-backup-script). Set the user, password and target directory and test, then submit this to cron instead of mysqldump. – Jun 18 '13 at 03:42
my backup file can generated in a specify directory, but still in blank file with 0kb. – conmen Jun 18 '13 at 06:23

score 1 · Answer 4 · answered Feb 18 '16 at 09:34

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.

That's not really helping, OP is trying to use the `mysqldump` command, which is exactly the right way to dump a mySql database. — Johannes Jander, Feb 18 '16 at 09:57

PHP MySQL database backup cron job

4 Answers4