Create daily backups from your MySQL databases (simple script)

mysql_db_backup.sh

#!/bin/sh
# Find out what databases are in mysql and back them up
# Delete old backups
STARTTIME=` date +%Y%m%d-%H%M `

BACKUP_DIR="/srv/users/serverpilot/apps/backups"
LOGFILE="/srv/users/serverpilot/apps/backups/db_backup.log"
USER="root"
PASSWD="ENTER_ROOT_PASSWORD"
KEEP="20"


(
echo
echo " ---MySQL backups start ${STARTTIME} ---"
#delete any backup written more than ${KEEP} days ago
echo "Removing files over ${KEEP} days old from ${BACKUP_DIR}:"
/usr/bin/find  ${BACKUP_DIR} -mindepth 1 -mtime +${KEEP} -print -delete
echo
echo "Performing today's dumps"
#find each database running in this instance of mysl
for DB in ` echo "show databases;"|mysql -u${USER} -p${PASSWD} mysql |awk " NR>1 {print         
$1} " `
do
    #generate a backup file name based on the data base name
    BACKUP_FILE="${BACKUP_DIR}/${DB}-${STARTTIME}.sql"
    echo "Processing database ${DB} into file ${BACKUP_FILE}"
    # dump the database data/schema into the backup file
    mysqldump -u${USER} -p${PASSWD} ${DB} > ${BACKUP_FILE}
    gzip ${BACKUP_FILE}
done

ENDTIME=` date +%Y%m%d-%H%M `
echo
echo " ---MySQL backups complete ${ENDTIME} ---"
echo
) >>  ${LOGFILE} 2>&1

If you don't use Serverpilot to manage your server, change the value for the BACKUP_DIR variable.

Otherwise create an APP with the name "backups" with serverpilot as the Linux user.
You can find the root password for your MySQL server inside the root directory (Serverpilot only, check the file .my.cnf).

Upload the file into the APP directory, make the file executable and create a CRON job with this row:

0 7 * * * /srv/users/serverpilot/apps/lbackups/./mysq_db_lbackup.sh