Crontab commands will have all unescaped occurrences of
% replaced by newlines. This is from the
crontab(5) manual on my system:
The command field (the rest of the line) is the command to be run. The
entire command portion of the line, up to a newline or
% character, will
be executed by
/bin/sh or by the shell specified in the
SHELL variable of
the crontab. Percent signs (
%) in the command, unless escaped with a
\), will be changed into newline characters, and all data
after the first
% will be sent to the command as standard input.
Your crontab command should look like
mysql -N -ubackup -pt -e 'show databases' | while read dbname; do mysqldump -ubackup -p123 --complete-insert -N "$dbname" > /home/test/sqlbackups/"$(date +"\%Y-\%m-\%d")-$dbname".sql;done
Here, I've also corrected the
$(...) that was previously single-quoted (and thus not expanded by the shell).
In general, it's better to put all non-trivial cron jobs in their own scripts and then schedule these instead. That way you have more control over things like this and you are also able to choose the correct interpreter for any particular job (e.g.
bash instead of
sh). It additionally makes any subject lines in emails sent to you from the cron daemon more readable.