1

Am trying to get a auto backup of our mysql database via a cron job that runs daily.

We have:

$database_user = 'VALUE';
$database_pass = 'VALUE';
$database_server = 'localhost';
// Name of the database to backup
$database_target = 'VALUE';
// Get Database dump
$sql_backup_file = $temp_dir . '/sql_backup.sql';
$backup_command = "mysqldump -u" . $database_user . " -p" . $database_pass . " -h " .      $database_server . " " . $database_target . " > " . $sql_backup_file;
system($backup_command);
// End Database dump

Problem is we get a message back from the Cron Daemon with:

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

sh: -h: command not found

So it looks like it has something to do with the -h

~~~

Anyone have any thoughts on how to fix?

Palemo
  • 217
  • 1
  • 5
  • 19
  • 1
    if its localhost then just remove the -h option something as mysqldump -u user -p pass dbname > backup.sql – Abhik Chakraborty Mar 11 '14 at 06:28
  • None of the options mentioned below have made any difference. I have tried various things and am now just getting the Usage message. – Palemo Mar 11 '14 at 08:51

6 Answers6

1

First, I recommend you upgrade to mysql 5.6+ so that you can keep your database passwords more secure. First, you would follow the instructions from this stackoverflow answer to set up the more-secure mysql login method for command line scripts.

You should probably write a bash script instead of using PHP. Here's a full backup script, very simple. db_name is the name of your database, and /path/to/backup_folder/ is obviously where you want to store backups. The --login-path=local switch will look in the home directory of whoever is running this bash script and see if there is a login file there (must be readable by the current user and accessible by no one else).

#!/bin/bash
#$NOW will provide you with a timestamp in the filename for the backup
NOW=$(date +"%Y%m%d-%H%M%S")

DB=/path/to/backup_folder/"$NOW"_db_name.sql.gz
mysqldump --login-path=local db_name | gzip > "$DB"

#You could change permissions on the created file if you want
chmod 640 "$DB"

I save that file as db_backup.sh inside of the /usr/local/bin/ folder and make sure it is readable/executable by the user who is going to be doing the db backups. Now I can run # db_backup.sh from anywhere on the system and it will work.

To make it a cron, I put a file called 'db_backup' (the name doesn't really matter) inside my /etc/crond.d/ folder that looks like this (user_name is the user who is supposed to run the backup script)

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:usr/local/bin
MAILTO=root
HOME=/
# "user_name" will try to run "db-backup.sh" every day at 3AM
0 3 * * * user_name db-backup.sh
Community
  • 1
  • 1
Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
  • I implemented your solution - but as you stated yourself in this [post](http://dba.stackexchange.com/questions/98099/are-certain-environment-variables-required-for-mysql-login-path/98104) there is always an error when I execute the script from the crontab: `mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect`. Is there really no solution to use the `--login-path` feature with cron? – Mel_T Feb 24 '16 at 14:30
  • @Mel_T I think there must be but I haven't come back to searching for it. But now that you reminded me I might try again. – Buttle Butkus Feb 24 '16 at 17:35
0

I think that you have some strange char on your password string. Maybe a "`" character and this why you are getting both errors. The first from mysqldump because you don't have specified any database (because the broken string) and the second from shell saying that cannot find -h command (next string after password)

Try to escape $backup_command before system_call (http://www.php.net/escapeshellcmd) 

system(escapeshellcmd($backup_command));
Olvathar
  • 551
  • 3
  • 10
0

You should not use -p option anyway, as this can be read out by anybody on the system via ps aux.

It is a much better way to specify your credentials for mysqldump in an option file:

[client]
host = localhost
user = root
password = "password here"

Then you use call your script like this:

mysqldump --defaults-file=/etc/my-option-file.cnf  ...other-args

This way, there is no password exposure on your system to other users. It might even also fix your proglem with unescaped complex passwords.

I would also recommend you to look at this project to get some more ideas: mysqldump-secure

lockdoc
  • 1,539
  • 1
  • 18
  • 31
-1
 Do not keep space between -p and $database_pass. You need to write the password 

   immediately after the -p, without a space.

$backup_command = "mysqldump -u" . $database_user . " -p" . $database_pass . " -h " . 
     $database_server . " " . $database_target . " > " . $sql_backup_file;


 hope will work for you
Deo
  • 82
  • 5
-1

You need to supply database name. If you want to ahve backup for all please add --all-databases and Also remove spacing between -p and password

"mysqldump -u" . $database_user . " -p" . $database_pass . " -h " .
$database_server . " --all-databases" . $database_target . " > " . 
$sql_backup_file;
zzlalani
  • 22,960
  • 16
  • 44
  • 73
prasoon
  • 901
  • 8
  • 25
-1

Try this by adding the space between -u and username

$backup_command = "mysqldump -u " . $database_user . " -p" . $database_pass . " -h " .      $database_server . " " . $database_target . " > " . $sql_backup_file;
                               ^

If the database server located at the localhost remove the host param

$backup_command = "mysqldump -u " . $database_user . " -p" . $database_pass . " " . $database_target . " > " . $sql_backup_file;
zzlalani
  • 22,960
  • 16
  • 44
  • 73