-1

My site is controlled via cpanel. I want to automate the backup of my databases and then automate their download to my development machine. The first step was to set up the cron jobs as follows

0   3   *   *   4   mysqldump -u username -p password database1 > backup/db_biz_directory.sql
0   4   *   *   4   mysqldump -u username -p password database2 > backup/db_swr_software.sql
  1. There are two empty files in the backup folder timestamped 11th, that is last Thursday.
  2. There are no errors in the errors section of cPanel.
  3. There is nothing under etc, logs or var folders.
  4. I did not receive any emails, they are set up and do work.
  5. There are tables and data in those databases.

Since the files exist, the jobs obviously ran. What is wrong with my job? Could the errors be logged somewhere else?

Rohit Gupta
  • 356
  • 2
  • 4
  • 14
  • If you run that command in your home directory, does it produce the desired backup file? – Stephen Ostermiller Aug 14 '22 at 09:43
  • Had to set up ssh first. No, it gives 1044, so about to check database access rights. – Rohit Gupta Aug 14 '22 at 12:44
  • I also discovered that it doesn't like a space between -p and password. – Rohit Gupta Aug 14 '22 at 12:50
  • Care to explain the downvote. It wasn't my idea to transfer it here. The question is laid out well, it shows what I did and what the result was. It was able to be answered, which fixed my problem. What is the criteria to downvote ? – Rohit Gupta Aug 24 '22 at 11:33
  • Probably because it was inappropriate to migrate the question here. This question isn't about administering a server. You merely have a user account of the server, not root access. [The help center](https://serverfault.com/help/on-topic) specifically states that questions should not be about "working with a service provider's management interface, such as cPanel." – Stephen Ostermiller Aug 24 '22 at 11:39
  • Great, so I get penalized for it. :-) And it really wasnt about how to do it in cpanel, just that I set the crontab job in cpanel. – Rohit Gupta Aug 24 '22 at 11:40
  • Yeah, sorry. I would have been happy to leave this on Webmasters. – Stephen Ostermiller Aug 24 '22 at 11:41

2 Answers2

2

You may not have provided enough information for us to be sure. You may want to advise WHICH cron file this is in (ie system crons - /etc/crontab for example, have an extra field with a username - although I suspect that's not the problem. There are also different implementations of cron - it would be useful to know which you used, alongside which OS)

First thing I would do is add a path - ie change mysqldump to /usr/bin/mysqldump assuming that that is the correct path to it for your system.

It is not clear why you would expect to receive emails from this command. Do you have a command higher up in the crontab with a line like MAILTO=yourname@example.com? Even with this though, if the mysqldump command does not produce any output it won't send you an email.

You might want to try bang this into a batch file and call that instead, along with some echo statements. Also, redirecting stderr to stdout can be useful (ie add 2>&1 to the end of the command in the batch file).

You say there is nothing under etc logs or var folders - but have you looked in the system logs (eg /var/log/messages, /var/log/syslog) for output from crontab?

As an aside, I put to you that hard coding your password on the command line is a bad idea from a security POV. You should drop the username and password from the command line and ad it to .my.cnf - see https://serverfault.com/questions/358903/store-the-mysql-client-password-or-setup-password-less-authentication

davidgo
  • 6,222
  • 3
  • 23
  • 41
  • 2
    Adding `2>&1` will put error messages into the backup SQL file. It could be useful for finding the problem, but if you leave it in there it could corrupt future dumps. – Stephen Ostermiller Aug 14 '22 at 09:40
  • The syntax was correct as its from cpanel gui. I was trying to do it in steps. I am rusty with my unix (last used 40 years ago). I hadnt needed ssh so far, just set it up. As it turns out it is crontab. I tried the mysql command and it gives me 1044 error. The username and password are correct. I have the right prefix for cpanel. About to check database access rights. – Rohit Gupta Aug 14 '22 at 12:42
  • I did look under all subfolders of var, etc and logs. When googling for mysqldump with cpanel, the posts said I would get an email. – Rohit Gupta Aug 14 '22 at 12:48
  • I had a read of that link and the links it referred to. I am not convinced. If someone has root access, they have access to the database anyway. Although at the moment I only have a client user/pwd, I was planning on having a different one for backup. And I don't **yet** see how the my.cfg will work with multiple users. Finally I was planning to have a script that would zip them as well. One step at a time, since I have not used scripts etc for 40 years. – Rohit Gupta Aug 14 '22 at 13:57
  • 1 mysql.cnf file per user, and use mysql permissions to allow access. Having password on command line means anything tjat can do a process list can see it - which could include bad php scripts. – davidgo Aug 14 '22 at 20:02
  • I dont think you get emails unless thete is output. (at least thats how it works with every cron Ive ever used). As you have surmised I think 1044 is a MySQL error, so more likely an issue with connecting to MySQL. You dont need a script to compress a file - just pipe it through gzip - eg - mydqldump mysq opts | /path/to/gzip > /path/to/backupfile.gz – davidgo Aug 14 '22 at 20:10
0

It turns out there were a number of issues.

  1. davidgo's answer fixes one, the normal path information is not there when cron is run. So I needed to put in the complete path for mysqldump.
  2. mysqldump does not like a space between -p and password
  3. mysqldump needed table lock access, which I had not given to the user.
  4. As mentioned in davidgo's answer, the error does not go anywhere by itself (contrary to many posts that are around the web).
  5. cpanel help says that I should have received an email - this is obviously incorrect.

I had to wait until the job ran, its all good now.

The new command is

0   3   *   *   4   /usr/bin/mysqldump -u username -ppassword database1 > backup/db_biz_directory.sql

And I have finished the script to backup multiple databases and email the status/errors to me.

Rohit Gupta
  • 356
  • 2
  • 4
  • 14