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 Answers 2

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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 at 14:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .