CODEWITHSUNDEEP
linuxbashcronmysql-backup
Chatis
Chatis

Reputation: 57

mysqldump problem in Crontab and bash file

I have created a cron tab to backup my DB each 30 minutes...

*/30 * * * * bash /opt/mysqlbackup.sh > /dev/null 2>&1

The cron tab works well.. Each 30 minutes I have my backup with the script bellow.

#!/bin/sh
find /opt/mysqlbackup -type f -mtime +2 -exec rm {} +
mysqldump --single-transaction --skip-lock-tables --user=myuser -- 
password=mypass mydb | gzip -9 > /opt/mysqlbackup/$(date +%Y-%m-%d-%H.%M)_mydb.sql.gz

But my problem is that the rm function to delete old data isn't working.. this is never deleted.. Do you know why ?

and also... the name of my backup is 2020-02-02-12.12_mydb.sql.gz?

I always have a ? at the end of my file name.. Do you know why ?

Thank you for your help

Upvotes: 0

Views: 349

Answers (2)

k11a
k11a

Reputation: 1

Using find with mtime is not the best choice. If for some reason mysqldump stops creating backups, then in two days all backups will be deleted.

You can use my Python script "rotate-archives" for smart delete backups. (https://gitlab.com/k11a/rotate-archives). The script adds the current date at the beginning of the file or directory name. Like 2020-12-31_filename.ext. Subsequently uses this date to decide on deletion.

Running a script on your question:

rotate-archives.py test_mode=off age_from-period-amount_for_last_timeslot=0-0-48 archives_dir=/mnt/archives

In this case, 48 new archives will always be saved. Old archives in excess of this number will be deleted.

An example of more flexible archives deletion:

rotate-archives.py test_mode=off age_from-period-amount_for_last_timeslot=7-5,31-14,365-180-5 archives_dir=/mnt/archives

As a result, there will remain archives from 7 to 30 days old with a time interval between archives of 5 days, from 31 to 364 days old with time interval between archives 14 days, from 365 days old with time interval between archives 180 days and the number of 5.

Upvotes: 0

parttimeturtle
parttimeturtle

Reputation: 1215

The question mark typically indicates a character that can't be displayed; the fact that it's at the end of a line makes me think that your script has Windows line endings rather than Unix. You can fix that with the dos2unix command: dos2unix /path/to/script.sh

It's also good practice not to throw around MySQL passwords on the CLI or store them in executable scripts. You can accomplish this by using MySQL Option files, specifically the file that defines user-level options (~/.my.cnf).

This would require us to figure out which user is executing that cronjob, however. My assumption is that you did not make that definition inside the system-level crontab; if you had, you'd actually be trying to execute /opt/mysqlbackup.sh > /dev/null 2>&1 as the user bash. This user most likely doesn't (and shouldn't) exist, so cron would fail to execute the script entirely.

As this is not the case (you say it's executing the mysqldump just fine), this makes me believe you have the definition in a user-level crontab instead. Once we figure out which user that actually is as I asked for in my comment, we can identify the file permissions issue as well as create the aforementioned MySQL options file.

Upvotes: 0

Related Questions