0

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
  • What does & > mean? – Rick James May 22 at 23:17
  • @RickJames to be honest I am not 100% sure.. I copied it from another website that specified how to output to a file with this process. – Dorian Waite May 23 at 0:46
  • Normally & means do the statement in the background. I don't know about the rest. – Rick James May 23 at 5:22
0

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/

  • Yeah that might be something I need to try, considering that what I am doing isn't working out to be the best. I did add --single-transaction before, and it shows in the results of my grep command, though it didn't create any legitimate results. I will check permissions for the directory and I skimmed through your article real quickly. Will try creating a backup script, and if that doesn't work, use the instructions in the article you sent... The two are a similar process it seems. – Dorian Waite May 23 at 0:48

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.