How to use mysqldump in a cron job

[PaulusMagnus]

I'm trying to setup regular MySQL dumps using the cron jobs but I can't get it to work. It seems to be a mixture of permissions and directory problems as I don't know where the cron job is executed from and by whom.

If I login as admin through SSH this command works fine (on one line):

DIR='/home/admin/domains/mydomain.com/public_html/@mysqlbackups'; 
DT=`date +%Y%m%d_%H%M%S_%Z`; 
/usr/bin/mysqldump -u'admin_username' -p'password' --opt 'admin_username' | 
gzip > {DIR}/backup_${DT}.gz

It creates a gzip'd file in the user's @mysqlbackups folder with the current date/time. How can I get this to work as a user created cron job?

I tried using a simpler version;

/usr/local/mysql/bin/mysqldump -u`admin_username` -p`password` --opt `admin_username` > backup.sql

This created an empty file though but I can't find any errors recorded that explain why it didn't work.

I want users to be able to schedule their own backups and download them without me putting it into the server's crontab.

I've used a similar approach with Plesk and CPanel and then tied this into automated FTP transfers and it keeps my database backed up off-site every 4 hours and it's a system that I trust. I just can't work out why it won't work under Direct Admin.

Any pointers?