1

I'm trying to set up a cron job in order to backup my databases daily. Here's what I wrote in my crontab file :

25 18 * * * root mysqldump -u root -p myPassWord --all-databases | gzip > /var/backup/database_`date '+%m-%d-%Y'`.sql.gz 

As nothing happened at 18:25, I had a look in my /var/log/syslog file (the server is under Debian) here's what I found:

Jan 24 18:25:01 ns311475 /USR/SBIN/CRON[16252]: (root) CMD (/usr/local/ispconfig/server/server.sh 2>&1 > /dev/null | while read line; do echo `/bin/date` "$line" >> /var/log/ispconfig/cron.log; done)
Jan 24 18:25:01 ns311475 /USR/SBIN/CRON[16253]: (getmail) CMD (/usr/local/bin/run-getmail.sh > /dev/null 2>> /dev/null)
Jan 24 18:25:01 ns311475 dovecot: imap-login: Disconnected (no auth attempts in 0 secs): user=<>, rip=127.0.0.1, lip=127.0.0.1, secured, session=<3xFlL2kNAAB/AAAB>
Jan 24 18:25:01 ns311475 dovecot: pop3-login: Disconnected (no auth attempts in 0 secs): user=<>, rip=127.0.0.1, lip=127.0.0.1, secured, session=<rhRlL2kNvwB/AAAB>
Jan 24 18:25:01 ns311475 postfix/smtpd[16279]: connect from localhost.localdomain[127.0.0.1]
Jan 24 18:25:01 ns311475 postfix/smtpd[16279]: lost connection after CONNECT from localhost.localdomain[127.0.0.1]
Jan 24 18:25:01 ns311475 postfix/smtpd[16279]: disconnect from localhost.localdomain[127.0.0.1]

I don't understand the first line, I assume the rest is trying to email the issue but I got nothing.

2

Read the fine manual: man 5 crontab : There you will see that "%" has a special meaning for cron.

That is clobbering up your intended command.

apart from this this is a very bad way to start a mySQL-backup:

  • Your root-DB-password will appear in the process list viewable for every local user or every user with snmp read access
  • You should not start a subshell in a shell for no apparent reason. Use "$(command)" instead.
  • gzip will only use one CPU. So that part will slow down your whole backup

You should:

  • Place your root-DB-password into a file (.mysql - something I do not remember the exact syntax and name of that file) in the root-folder, only accessible by root. This will enable automatic root-login with the password to mySQL
  • Place what you are doing into an own script on /usr/local/sbin (propably also only readable for root and a trusted group)
  • Call that script via cron.
  • Use the full paths for all commands in that script. Cron might not execute in an environment with all "usual" paths set
  • use pgzip or if you have the space available, compress after the backup is done
2
  • In addition to that PATH may not be set properly. In general it is more desirable to create a script. Otherwise sound answer! Jan 24 '15 at 21:28
  • Thanks for the tips Nils, as you might have seen I'm very new to server administration and Linux, but your suggestions make sense to me.
    – Dave
    Jan 25 '15 at 9:06
1

Debian has a package for you to take care of dumping, compressing and rotating MySQL data. You can install it with the following command:

$ sudo apt-get install automysqlbackup

After this daily, weekly and monthly dumps will be placed in /var/lib/automysqlbackup

3
  • ok, thank you hspaans, I think I'll go with your solution which fits well with my needs and will prevent me from making any big mistake.
    – Dave
    Jan 25 '15 at 9:08
  • Install went ok, but I just can't find where to set up the daily backup hour and minutes. In /etc/default/automysqlbackup only allows to edit day of week...
    – Dave
    Jan 25 '15 at 9:32
  • The package automysqlbackup installs a script in /etc/cron.daily and all the scripts in that directory are being executed daily by the line with "run-parts --report /etc/cron.daily" in /etc/crontab.
    – hspaans
    Jan 25 '15 at 14:15

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.