Centos backup mysql using cron job

Question

According to this tutorial http://www.a2hosting.com/kb/getting-started-guide/backing-up-your-data/backups-on-dedicated-servers-and-vps trying to backup mysql database

I did:

1) created folders in home directory. /home/USERNAME/backup/

2) in folder backup created file, named backup-cron-mysql.sh

3) created content of backup-cron-mysql.sh

#!/bin/bash
db_name=dabase_name
db_user=user
db_password=password
backup_filename=$db_name-`date +%F`

mysqldump -h localhost -u $db_user -p$db_password $db_name | gzip > /home/USERNAME/dbbackup/$backup_filename.sql.gz

Made backup-cron-mysql.sh executable (Octal: 0755)

4) created folder dbbackup. path: /home/USERNAME/dbbackup/

5) Using putty.exe logged in and typed crontab –e, then Enter. Latter found that cron configuration file is located in directory /var/spool/cron/ and file name is root

6) opened root file with Notepad++ and pasted following code

45 14 * * *     /bin/sh /home/USERNAME/backup/backup-cron-mysql.sh
[email protected]

As result in /home/USERNAME/dbbackup/ must see some file. But see nothing (empty folder).

Please advice what need to correct, to backup mysql

Solution 1

Check the below file for the cron log output. /var/log/syslog

You can also filter the log using this command:

grep CRON /var/log/syslog

To configure log in your cron job:

 45 14 * * *     /bin/sh /home/USERNAME/backup/backup-cron-mysql.sh 2>&1 >> /var/log/myjob.log

https://askubuntu.com/questions/56683/where-is-the-cron-crontab-log

Solution 2

A few thoughts:

1. Since you're using CentOS, the default location for Cron's logs should be /var/log/cron. There may not be much information in there, but it should at least tell you whether or not the Cron daemon is attempting to run your script.

2. Zeeshan's output redirection above might work successfully but a simpler notation would be to use the "redirect all output to file"... e.g.

   45 14 * * * /bin/sh /home/USERNAME/backup/backup-cron-mysql.sh &> /some/file

   A good overview of Bash redirection methods is located here:
   http://tldp.org/HOWTO/Bash-Prog-Intro-HOWTO-3.html

3. Crontab files are finicky in a couple of ways; one way is that they require a blank line as the last line in the file. I'd suggest making sure there's at least one empty line at the end of the file (having several won't hurt, so you could throw a couple in there for good measure.)

4. Did you run the backup script manually before attempting to automate it with Cron? If there's a problem with the script itself, adding Cron automation on top of it would only make troubleshooting harder.

Comments

[MadHatter]

You edited the crontab file manually, so cron probably doesn't know you've made any changes. It's always best to use the crontab command, because that sends a signal to cron to re-read the config files. Try using crontab to make a trivial change in the file, then see if your backups run.

[Andris]

can not find file syslog in /var/log/ grep: /var/log/syslog: No such file or directory OK. Will configure cron job according to your advice.

[Zeeshan]

The is a space after mysql.sh 2>&1, I have edited accordingly! If you don't find syslog then it might be /var/log/messages for CentOS/Redhat.

[Andris]

In cron see Oct 21 10:40:01 localhost CROND[16090]: (root) CMD (/usr/bin/php -q /etc/zpanel/panel/bin/daemon.php >> /dev/null 2>&1). Manually did not run backup script. Yes, at first need to try manually. Not sure if it matters, but I use Zpanel (zpanelcp.com).

[Andris]

Yes, see file messages. And see nothing about backup / cron job. Will try to backup manually to see if it works at all.

[YourDaily]

I'd start by breaking down the script and confirming proper function of each component. So for instance, at a command line simply run the mysqldump command (with the appropriate username, password, etc.) and see if you get a successful dump. Be aware that if you specify the password on the command line using the -p flag, the password will show up in a process listing; so any other users who are ssh'd into the system might be able to see the password if they ran a process list. Might be safer to run the command without the -p flag first, supplying it interactively when prompted.