Problem With A Php Script Using Mysqldump And Cron Job

[azw]

Thanks for taking a look. Please let me know if there is more info I can provide you.

 

MySQL server version: 5.5.28-cll

PHP version: 5.3.18

Apache version: 2.2.23

 

Raw MySQL statement:

mysqldump --user=$dbuser --password=$dbpswd --host=$host $mysqldb | gzip > $filename_with_path"

 

Description of purpose: This script is used to create a backup of the entire database every night. The script then emails me a message if there is an error.

 

Errors MySQL returns: using Cron job, so no errors are returned, except that I'm receiving the email, which says that the size of the backup is 0.

What's wrong: It was working fine on a PHP4 server. I moved it to a new server running PHP5 at the same host. The back up is no longer being created.

To help isolate the problem, I've marked the complete script (below) with this: // IT SEEMS TO WORK UNTIL THIS POINT

 

Things I've attempted: I've made sure the backup file path is correct, and tried to add the mysql path (I'm not entirely sure that's correct, but I've tried several options). I've changed to single quotes in many places where double quotes were not needed, I've also tried rewriting the system(mysqldump...) command line. I'm stumped!

 

Complete script:

<?php
$email_recipient = 'XXX';
$email_sender = 'XXX';
$website = 'XXX';
$host = 'localhost'; // database host
// Must CHANGE all of these to use for a different DB
$dbuser = 'XXX'; // database user name
$dbpswd = 'XXXl'; // database password
$mysqldb = 'XXX'; // name of database
$db_used_with = 'XXX';
$script_name = 'backupMysql.php';
$file_path = '/home/XXX/backups/dbname/';

// Create the backup file name
if ( date(w) == 0 ) // Sunday = 0
{
$week = intval (( date(j) + 6 ) / 7 ); // integer_value((numeric_day_of_month + 6) / 7) = week of month: 1-5
$filename_with_path = $file_path . 'backupWeek' . $week . '.sql.gz';
}
else
{
// date("D"): 3-letter day of week (MON, TUE)
$filename_with_path = $file_path . 'backup' . date("D") . '.sql.gz';
}

// IT SEEMS TO WORK UNTIL THIS POINT

if ( file_exists($filename_with_path) ) unlink($filename_with_path);
system("mysqldump --user=$dbuser --password=$dbpswd --host=$host $mysqldb | gzip > $filename_with_path", $result);
$size = filesize($filename_path);
switch ($size) {
case ($size>=1048576): $size = round($size/1048576) . ' MB'; break;
case ($size>=1024): $size = round($size/1024) . ' KB'; break;
default: $size = $size . ' bytes'; break;
}
// $Result returns the last line of the command output on success (0) or failure (FALSE, usually?).
// or send a report weekly on Sundays (w = 0)
if (( $result !== 0 ) || ( date(w) == 0 ))
{
// prepare success or failure/error email notice
if ( $result === 0 )
{
$result_text = 'SUCCESS';
}
else
{
$result_text = 'ERROR';
}
$title = "Database Backup Report: $result_text";
$subject = "$title -- Website: $website -- Database for: $db_used_with -- Database: $mysqldb";
$msg = "$title\n\n\n";
$msg .= 'The database backup for ' . $mysqldb . " has been run.\n\n";
$msg .= "Website: $website\n";
$msg .= 'The return code was: ' . $result . " (typically FALSE on failure, 0 on success).\n";
$msg .= "Script: $script_name\n";
$msg .= 'The file & path is: ' . $filename_with_path . "\n";
$msg .= 'Size of the backup: ' . $size . "\n\n";
$msg .= 'Server time of the backup: ' . date("F d h:ia") . "\n\n";
$msg .= "Sender: $email_sender\n";
$msg .= "Recipient: $email_recipient\n\n";
$msg = wordwrap($msg, 70);
mail($email_recipient, $subject, $msg, 'From ' . $email_sender);
}
?>

[trq]

cron has a very limited environment and might not be able to locate the mysqldump command. Try using the full path to it.

[azw]

Thanks for you help.

 

I've run the whereis mysql command and gotten this response:

 

mysql: /usr/bin/mysql /usr/lib/mysql /usr/local/lib/mysql.sock /usr/include/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gzth

 

Which of those is the correct path?

 

Would it be one of these?

/usr/bin/mysql/mysqldump
or
/usr/bin/mysqldump

 

I've tried both (and others) without success yet.

Edited January 1, 2013 by azw

[trq]

You should do a whereis on mysqldump, not mysql. They are two different things.

 

The path is likely /usr/bin/mysqldump though.

[azw]

Oh, that makes it easier!

You're right. It is /usr/bin/mysqldump.

 

I've striped most of the code to simplify it.

 

$host = 'XXX'; // database host
$dbuser = 'XXX'; // database user name
$dbpswd = 'XXX'; // database password
$mysqldb = 'XXX'; // name of database

$filename_with_path = '/home/XXX/backups/XXX/tst.sql';

echo 'filename with path: ' . $filename_with_path;

$command = "/usr/bin/mysqldump --host=$host --user=$dbuser --password=$dbpswd $mysqldb > $filename_with_path";
echo 'command: ' . $command;

// IT WORKS OKAY UP UNTIL THIS POINT

system ($command, $result);
echo 'result: ' . $result;

 

The command prints out to be what I expected with the correct values. I've tried putting single quotes around the password, too, without success.

command: /usr/bin/mysqldump --host=localhost --user=XXX --password=XXX XXX

 

Do you see anything suspicious in this code?

Edited January 1, 2013 by azw

[jazzman1]

Are you sure that php has permissions to write into "/home/XXX/backups/XXX/" ?

[Christian F.]

If the error log doesn't contain any clues as to what's happening, you should log into the server and check the local mail. Crontab always sends mails to either the owning user or root whenever there's any output from its jobs, so any errors should be available there.

[jazzman1]

My question is, why do you want to use php to do this?

 

In that example I'm using ssh to connect to the remote server and dumping a mysql database into my local machine, every midnight at 2am.

 

Take a look for a minute at this.

 

If you don't understand something about that, don't hesitate to ask me.

 

#!/bin/bash

DBUSER=dbUser
DBHOST=dbHost
DBPASS=dbPass
DBNAME=dbName
NOW=`/bin/date +%Y%m%d`
DIR=$(dirname $0)

if [ ! -d $DIR/$NOW ];then mkdir -p $DIR/$NOW;fi

ssh hostName@domainName -p 22 mysqldump --host=$DBHOST --user=$DBUSER --password=$DBPASS --protocol=TCP --port=3306 --single-transaction ${DBNAME} > $DIR/$NOW/$NOW.sql

tar zcf ${DIR}/db-$NOW.tgz ${DIR}/${NOW}/$NOW.sql

rm -rf ${DIR}/${NOW}

 

Cron job:

 

0 */2 * * * /home/username/db_backUp

[azw]

Thanks for the replies, Jazzman and Christian.

 

I did a quick test of the script. The output of the SYSTEM() command is FALSE. So, the error is probably in that line.

 

I have the folders' permissions set to 755. What should the permissions be set to?

 

I've searched the mail folders and found only the emails that my php script sends. I'm on a virtual server, so I don't think I have access to the true root level for the mysql and php logs. (I could be wrong about that, but I didn't find them.)

 

I've checked to see if there were changes in PHP5 that would make it not work. The only possibility I've seen is the MySQL has to be added manually to the configuration command. In my case, '--with-myql=/usr' was included in the configuration command for PHP.

 

It's been years since I've used ssh. I may have to try that approach, since this one is failing.

Edited January 3, 2013 by azw

[trq]

It's been years since I've used ssh. I may have to try that approach, since this one is failing.

 

SSH is a network protocol.

[azw]

Yep. And my memory of SSH and bash is really rusty!

[jazzman1]

I did a quick test of the script. The output of the SYSTEM() command is FALSE. So, the error is probably in that line.

 

I have the folders' permissions set to 755. What should the permissions be set to?

 

It should be work! You have to change ownership of "/home/XXX/backups/XXX/".

 

Which is a default user in the linux server responsible to run everything in php?

[azw]

Hello, Jazzman,

I'm not sure I understand your last message. I've changed the permissions to 777 on both the backups and XXX files. It still does not work.

Any other thoughts?

[jazzman1]

Hello, Jazzman,

I'm not sure I understand your last message. I've changed the permissions to 777 on both the backups and XXX files. It still does not work.

Any other thoughts?

 

Yep, b/s "/home/XXX/backups/XXX/" is not a part of web root directory, and the user named - "apache" or "www-data" cannot write down outside of its own directory, to do this the best way for me is to create a symbolic link.

 

http://www.cyberciti...hard-links.html

[azw]

I wrote to my web host to ask whether I could use SSH and bash. Because I explained the situation, she checked and saw that the SYSTEM() function had been disabled on the server. She enabled it and the problem was fixed! Amazing.

 

Thanks to you all for your persistence in helping me think about this problem. You're good!

[jazzman1]

Ah.., I was thinking that this one is a dedicated server

The script that I wrote in replay #8 works just fine.

If you want to use SSH to connect to the remote server without asking you for a password (otherwise bash will be stuck) you need to install a trusted certificate between the client and a server.

Take a look at that guide, it will help you - http://www.linuxproblem.org/art_9.html