0

Using the following script to backup a database (not all, just one of them).

#!/usr/bin/php
<?php
$file = "/home/southel2/public_html/archives/BACKUPS/backup.sql";
$backupFile = $dbname.date("Y-m-d-H-i-s").".zip";
$command = "mysqldump user=###_### --password=### --databases=###_### > $file";
system($command);
?>

Getting this error in cron email:

Content-type: text/html; charset=UTF-8


Warning: mysqldump: ignoring option '--databases' due to invalid value 'southel2_archives'
mysqldump: Got error: 1045: Access denied for user 'southel2'@'localhost' (using password: YES) when trying to connect

No entries added to the error log.

Any advise?

D.Davis
  • 15
  • 1
  • 10
  • It's literally telling you exactly what the problem is... – Alex Howansky May 26 '17 at 15:27
  • I added --host="local_host". Although I found found that it added the following to the destination sql file, it did not zip the sql file, and it did not add any new error log entries: 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 – D.Davis May 26 '17 at 15:30
  • Where and how does it tell me the problem when the user name and password are verified as correct and the database name is correct. So when it says that it is not, then I can't see the problem when it is saying something that isn't true. – D.Davis May 26 '17 at 15:33

1 Answers1

0

It's a permission error. You or someone with access to MySQL needs to show the grants for 'southel2'@'localhost': 

mysql> SHOW GRANTS for 'southel2'@'localhost';

Since you say the username and password are correct, one possibility is that the host is not correct. Grants for 'southel2'@'1.2.3.4', for example, won't allow you to connect from a different host, like 5.6.7.8.

Another possibility is that you don't have the necessary permissions to do what mysqldump needs to do. In addition to SELECT, you need LOCK TABLES and possibly more, depending on what's in the database.

Brian from QuantRocket
  • 5,268
  • 1
  • 21
  • 18
  • finally got it to work with [ #!/usr/bin/php $file"; system($command); ?> ] It will do all dbs but not specific ones. I'm running the script via cron on the same host. According to my config file local_host is it. If it were a permissions issue I would not think that it would be willing to do all dbs but not an individual db, would you not think so? – D.Davis May 26 '17 at 19:11
  • If it works will --all-databases but not with --databases then are you sure you're passing the correct database names to --databases? – Brian from QuantRocket May 26 '17 at 20:59
  • I copied and pasted it right from the db itself via myphpadmin. – D.Davis May 27 '17 at 21:40