-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?

7
  • If you run that command in your home directory, does it produce the desired backup file? Aug 14 at 9:43
  • Had to set up ssh first. No, it gives 1044, so about to check database access rights. Aug 14 at 12:44
  • I also discovered that it doesn't like a space between -p and password. Aug 14 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 ? Aug 24 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 specifically states that questions should not be about "working with a service provider's management interface, such as cPanel." Aug 24 at 11:39

2 Answers 2

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

6
  • 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. Aug 14 at 9: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. Aug 14 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. Aug 14 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. Aug 14 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 at 20:02
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.