Using CronJob to automatically backup MySQL files

Question

This is my crontab file in the etc folder in order to backup the database on a daily basis(2pm) but it doesnt really back things up:

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name command to be executed

14 0 * * * root mysqldump -u CENSORED -pCENSORED server | gzip > /home/samp/samp03/scriptfiles/sql-backup/database_`date '+%d-%m-%Y'`.sql.gz

I tried checking the log but didn't find anything. This was in a mail to root:

/bin/bash: -c: line 0: unexpected EOF while looking for matching ``'
/bin/bash: -c: line 1: syntax error: unexpected end of file

Any tips on what to do?

Duplicate thread, check this https://stackoverflow.com/questions/19731148/mysqldump-doesnt-work-in-crontab — Mikołaj Osowski, Jun 23 '17 at 13:59
Possible duplicate of [mysqldump doesn't work in crontab](https://stackoverflow.com/questions/19731148/mysqldump-doesnt-work-in-crontab) — jww, Jun 23 '17 at 16:01
Stack Overflow is a site for programming and development questions. This question appears to be off-topic because it is not about programming or development. See [What topics can I ask about here](http://stackoverflow.com/help/on-topic) in the Help Center. Perhaps [Super User](http://superuser.com/) or [Unix & Linux Stack Exchange](http://unix.stackexchange.com/) would be a better place to ask. — jww, Jun 23 '17 at 16:03

score 1 · Answer 1 · answered Jun 23 '17 at 14:52

1

Firstly, you need to make sure that your bash script work well. Create a shell script file, named mysql-backup-script.sh

#! /bin/bash
mysqldump -u CENSORED -pCENSORED server | gzip > /home/samp/samp03/scriptfiles/sql-backup/database_`date '+%d-%m-%Y'`.sql.gz

Secondly, run the script, make it work well.

sh mysql-backup-script.sh

Thirdly, Create Crontab to run the script.

14 0 * * * /home/samp/samp03/scriptfiles/mysql-backup-script.sh

answered Jun 23 '17 at 14:52

Zilong Wang

564
4
14

I got these errors in the mail: /bin/bash: -c: line 0: unexpected EOF while looking for matching ``' /bin/bash: -c: line 1: syntax error: unexpected end of file – Nutz Jun 23 '17 at 15:02

Todd Henderson · Answer 2 · 2018-05-23T18:21:56.270

The attached bash script is what I use that runs in cron daily. The bash script will email you notification when the backup has completed. the backups are kept for 5 day and then removed after that. I also have this script send me notification on the server information such as memory and storage of my backup directory. Please feel free to modify this script to work with your needs. You will want to change some of the variables in the # variables section of the bash script.

The section for the email in the script will also need to be modify to use your email address in the NOTIFY_EMAIL="root@gmail.com".

You can add the mysql root user account information in my.cnf in the root directory or within the bash script under the # [ Define Variables ].

you will need to chmod the bash script with

$sudo chmod +x backupmysql.sh

This will allow you to run the script manually with the following command or use the crontab listed below to automate the MySQL backups.

$sudo ./backupmysql.sh

You can name the script whatever you like. In this example, I named it nightly_mysql_backup.sh.

Here is the nightly_mysql_backup.sh bash script:

#!/bin/bash
#
############################################################################
#
# Script:   nightly_mysql_backup.sh
# Date:     05-23-2018
# Author:   
#
# Description:  
#
# Usage:        
#
# History:  
#
############################################################################
#
# Variables
HOST=`hostname -s`
DELETE_BACKUPS_OLDER_THAN_DAYS=5
syslogtag=MySQL-Backup
DEST=/var/dba/backup/
DBS="$(mysql -u root -Bse 'show databases' | egrep -v '^Database$|hold$' | grep -v 'performance_schema\|information_schema')"
DATE=$(date +'%F')
MIN_BACKUP=1000 #NOTE: 1 Kilobit = 1000 bit
DAYS_KEPT=5

# Backup all existing databases.  Currently disabled, can be enabled to backup certain individual databases.
#DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
#DBS="$(mysql -u root -Bse 'show databases' | egrep -v '^Database$|hold$' | grep -v 'performance_schema\|information_schema')"
# Or specify which databases to backup
#DBS="mysql zarafa"

# DO NOT BACKUP these databases.  Currently disabled, can be enabled to not backup certain individual databases.
#NOBKDB="test"

# Send Result EMail can be toggled on or off
SEND_EMAIL=1
NOTIFY_EMAIL="root@gmail.com"
NOTIFY_SUBJECT="MariaDB Backup Notification on ${HOST}"

# Linux bin paths, change this if it can't be auto detected via which command
MYSQLDUMP="$(which mysqldump)"
GREP="$(which grep)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
MAIL="$(which mail)"
FIND="$(which find)"
DF="$(which df)"
FREE="$(which free)"
DU="$(which du)"

# Function for generating Email
function gen_email {
DO_SEND=$1
TMP_FILE=$2
NEW_LINE=$3
LINE=$4
if [ $DO_SEND -eq 1 ]; then
if [ $NEW_LINE -eq 1 ]; then
echo "$LINE" >> $TMP_FILE
else
echo -n "$LINE" >> $TMP_FILE
fi
fi
}

# Temp Message file used for recording data on backup and cleanup
TMP_MSG_FILE="/tmp/$RANDOM.msg"
if [ $SEND_EMAIL -eq 1 -a -f "$TMP_MSG_FILE" ]; then
  rm -f "$TMP_MSG_FILE"
fi

set -o pipefail

# Start backing up databases and check if today's backup already exists
STARTTIME=$(date +%s)
for db in ${DBS[@]};
do  

skipdb=-1
if  [ "$NOBKDB" != "" ];
then
for i in $NOBKDB
do
    [ "$db" == "$i" ] && skipdb=1 || :
done
fi

    GZ_FILENAME=$HOST-$db-$DATE.sql.gz

    if [[ -f ${DEST}${GZ_FILENAME} ]]; then 
    echo "Backup file ${DEST}$GZ_FILENAME already exists for today. Exiting."
    gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "Error: Backup files ${DEST}$GZ_FILENAME: already exists for today."
    elif  [ "$skipdb" == "-1" ] ; then
    # connect to mysql using mysqldump for select mysql database
    # and pipe it out to gz file in backup dir
    mysqldump -u root --quote-names --opt --single-transaction --quick $db | gzip -cf > $DEST$HOST-$db-$DATE.sql.gz
    ERR=$?
    if [ $ERR != 0 ]; then
    NOTIFY_MESSAGE="Error: $ERR, while backing up database: $db"
    logger -i -t ${syslogtag} "MySQL Database Backup FAILED; Database: $db"
    else
    NOTIFY_MESSAGE="Successfully backed up database: $db "
    logger -i -t ${syslogtag} "MySQL Database Backup Successful; Database: $db"
    fi
    gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE"
    echo $NOTIFY_MESSAGE
    fi
done

ENDTIME=$(date +%s)
DIFFTIME=$(( $ENDTIME - $STARTTIME ))
DUMPTIME="$(($DIFFTIME / 60)) minutes and $(($DIFFTIME % 60)) seconds."

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# Log Time
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "mysqldump took: ${DUMPTIME}"
echo "mysqldump took: ${DUMPTIME}"

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

#Make sure we have a minimum number of files.  If we don't than e-mail.
DATABASE_COUNT=$(mysql -u root -e 'show databases;' | egrep -v '^Database$|hold$' | grep -v 'performance_schema\|information_schema'| wc -l)
BACKUP_COUNT=$(find ${DEST} -maxdepth 1 -iname '*.gz' | wc -l)
if [ $BACKUP_COUNT -eq 0 ] 
then
NOTIFY_MESSAGE="No backup files exist in $DEST. total files: $BACKUP_COUNT  ERROR!!!!"
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE"
echo $NOTIFY_MESSAGE
elif  [ $BACKUP_COUNT -le  $((($DATABASE_COUNT) * ${DAYS_KEPT})) ]
then
NOTIFY_MESSAGE="Only found $BACKUP_COUNT backup files. No cleanup will be done at this time.";
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE"
echo $NOTIFY_MESSAGE
elif [ $BACKUP_COUNT -gt $((($DATABASE_COUNT) * ${DAYS_KEPT})) ]
then
NOTIFY_MESSAGE="$BACKUP_COUNT backup files over the amount. Cleanup to occur on $DEST"
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE"
echo $NOTIFY_MESSAGE
fi

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

#Delete backups older than 5 days
total_backup=$(ls -tr $DEST | wc -l)
TO_DELETE=$(find ${DEST} -maxdepth 1 -type f -iname "*.sql.gz" -daystart -mtime +$(($DELETE_BACKUPS_OLDER_THAN_DAYS-1)) -print | sort | head -n $total_backup)
if [ -n "$TO_DELETE" ]
then
echo "Deleting the following files: $TO_DELETE"
NOTIFY_MESSAGE="Deleting the following files: $TO_DELETE "
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "Deleting: $TO_DELETE: "
echo $TO_DELETE | xargs rm
echo "Files deleted."
NOTIFY_MESSAGE="OK: Files deleted."
else
NOTIFY_MESSAGE="No files to delete."
fi

gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE"
echo "$NOTIFY_MESSAGE"


# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

for i in ${DEST}*.sql.gz
do
#Check to make sure we have a backup from today. If we don't then e-mail
if [[ ! -f ${DEST}/${GZ_FILENAME} ]]; then 
echo "Today's backup file, ${DEST}/${GZ_FILENAME}, not found.";
NOTIFY_MESSAGE="Today's backup file, ${DEST}/${GZ_FILENAME}, not found.";
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE" 
fi

#Make sure the backups has some reasonable size to it. If it doesn't then e-mail
SIZE_BYTES=$(stat -c%s ${i} )
SIZE=$(( SIZE_BYTES + 512 / 1024 ))
if [[ ! $SIZE -gt $MIN_BACKUP ]]; then
echo "Backup file: ${i} is smaller than the expected size.  Expecting > ${MIN_BACKUP} bit and got ${SIZE} bit";
NOTIFY_MESSAGE="Backup file: ${i} is smaller than the expected size.  Expecting > ${MIN_BACKUP} bit and got ${SIZE} bit";
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE" 
fi
done

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# Disk space stats of backup file system
if [ $SEND_EMAIL -eq 1 ]; then
$DF -h "$DEST" >> "$TMP_MSG_FILE"  
fi
$DF -h "$DEST"

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# Memory stats of file system
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "Memory status on $HOST: "
echo "Memory status on $HOST: "
if [ $SEND_EMAIL -eq 1 ]; then
$FREE -m -h >> "$TMP_MSG_FILE"  
fi
$FREE -m -h

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# List of current backup files email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "List of current backup files: "
echo "List of current backup files: "
if [ $SEND_EMAIL -eq 1 ]; then
$DU -hsc --time --apparent-size ${DEST}* >> "$TMP_MSG_FILE"
fi
$DU -hsc --time --apparent-size ${DEST}*

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# Sending notification email and cleanup of temporary file
if [ $SEND_EMAIL -eq 1 ]; then
$MAIL -s "$NOTIFY_SUBJECT" "$NOTIFY_EMAIL" < "$TMP_MSG_FILE"
rm -f "$TMP_MSG_FILE"
fi

exit

Set the crontab job to the following using crontab -e

0 14 * * * /path to backup file/nightly_mysql_backup.sh

You can use gunzip to uncompress the sql.gz file.

score -1 · Answer 3 · edited Jun 23 '17 at 16:01

-1

You should get mail to root for any error so check that or change MAILTO address with your mail account so you can get mail for error. You may have to give full path of executable command. execuable path can get by which command.

which mysqldump
/usr/bin/mysqldump

Or you can either create shell script and write this complete backup code in that script and configure script via cron.

edited Jun 23 '17 at 16:01

jww

97,681
90
411
885

answered Jun 23 '17 at 13:59

sahil kataria

135
6

I got these errors in the mail: /bin/bash: -c: line 0: unexpected EOF while looking for matching ``' /bin/bash: -c: line 1: syntax error: unexpected end of file – Nutz Jun 23 '17 at 15:02

score -1 · Answer 4 · answered Jun 23 '17 at 14:02

-1

The issue here is the command mysqldump -u <user> -p <databasename> gives you an interactive shell to type the password. If you want to be able to login as a user without the interactive prompt, you need to follow the steps in this post

answered Jun 23 '17 at 14:02

arsh chauhan

57
5

I got these errors in the mail: /bin/bash: -c: line 0: unexpected EOF while looking for matching ``' /bin/bash: -c: line 1: syntax error: unexpected end of file – Nutz Jun 23 '17 at 15:02
The first error is interesting. It says you're missing a closing ``` but it looks all balanced. Is what you posted an exact copy of the crontab file? – arsh chauhan Jun 23 '17 at 18:30

Using CronJob to automatically backup MySQL files

4 Answers4