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
Dorian Waite
  • 1
  • 1

1 Answers1

1

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/

gerryamurphy
  • 51
  • 5
  • 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 '19 at 00:48