I have this cron job,
/usr/bin/mysqldump -u username -p'password' MyDatabase > /backup/CronBackup/MyDatabaseBackup.sql &>/backup/CronBackup/cronBackupLog.log
I have been through this page:
Why is my crontab not working, and how can I troubleshoot it?
The cronBackupLog.log never appears after the cron running…
running grep mysqldump cron
in the /var/log
directory shows:
[root@server log]# grep mysqldump cron
May 20 00:00:01 server CROND[3104]: (username) CMD (/usr/bin/mysqldump -u username -p'password' MyDatabase > /backup/CronBackup/MyDatabaseBackup.sql)
May 21 00:00:01 server CROND[15318]: (username) CMD (/usr/bin/mysqldump -u username -p'password' MyDatabase > /backup/CronBackup/MyDatabaseBackup.sql)
May 22 00:00:01 server CROND[23517]: (username) CMD (/usr/bin/mysqldump -u username -p'password' MyDatabase > /backup/CronBackup/MyDatabaseBackup.sql)
May 22 10:30:01 server CROND[17670]: (username) CMD (/usr/bin/mysqldump -u username -p'password' --single-transaction MyDatabase > /backup/CronBackup/MyDatabaseBackup.sql)
May 22 11:00:01 server CROND[18543]: (username) CMD (/usr/bin/mysqldump -u username -p'password' MyDatabase > /backup/CronBackup/MyDatabaseBackup.sql &>/backup/CronBackup/cronBackupLog.log)
so I know it must be starting at least.. The problem is, if I run the command I am trying to in the terminal, it executes, and a couple minutes later it is complete and i have a new database backup. If I go into phpmyadmin, cron jobs, and insert this to run every 12 hours or 30 minutes, or whatever it might be (typically 12 hours as you can see above), it just doesn't complete any critical tasks.
I am interested in creating a time based file name but, probably after I get the basic one working.
Any more advice on how I can go about trouble shooting this would be helpful, I feel like I am completely missing something. Thank you.
Would running as root user versus, a user with all mysql permissions have any difference? As I said, running it in terminal works, and it does so using a high permission user. I am just not sure if running as root is required for the cron.
Running
CentOS 6.10
MySQL Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper
Best Answer
I personally always create a backup script in bash, and then use this in the crontab rather than using the mysqldump command itself, this provides more control and easier debugging, although using the mysqldump command should also work. I am wondering if adding
--single-transaction
to the cron would help./usr/bin/mysqldump -u username -p'password' --single-transaction MyDatabase > /backup/CronBackup/MyDatabaseBackup.sql
Also ensure the cron user has permission to write to the
/backup/CronBackup
directory.Also here is a good link https://www.linode.com/docs/databases/mysql/use-mysqldump-to-back-up-mysql-or-mariadb/