Create MySQL-Backup Script (only on one master)
The backup script is created on one of the master databases. The server creates a rolling backup and deletes backups which are older than 14 days.
Note - this script will fail, if the jtel Databases JTELWeb, JTELStats, JTELStats2 and JTELLog are not present.
Caution - without Replication
If the setup is without replication, then remove --master-data=2 from the backup script.
CAUTION PASSWORD
# CONFIGURATION *** CHANGE PASSWORD ***
CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='<password>'
CONFIG_backup_dir='/home/jtel/shared/backup/sql'
CONFIG_mysql_dump_host='localhost'
# Create directories
cd /home/jtel
mkdir -p /home/jtel/jtelbackup
mkdir -p ${CONFIG_backup_dir}
# Create .my.cnf
cat << EOF > /home/jtel/.my.cnf
[client]
user = ${CONFIG_mysql_dump_username}
password = ${CONFIG_mysql_dump_password}
host = ${CONFIG_mysql_dump_host}
EOF
# Create backup script
cat << 'EOF' > /home/jtel/jtelbackup/jtelbackup
#!/bin/bash
mkdir -p /home/jtel/shared/backup/sql/daily
mysqldump --add-drop-database --single-transaction --master-data=2 --add-drop-table --events --routines --triggers --databases JTELWeb JTELStats JTELStats2 JTELLog | gzip > /home/jtel/shared/backup/sql/daily/backup-$(hostname)-database-$(date +%F-%H%M%S).sql.gz
find /home/jtel/shared/backup/sql/daily -type f -mtime +14 -delete
EOF
# Create cron script
cat << EOF > /home/jtel/jtelbackup/jtelbackup.cron
#!/bin/bash
su - jtel -c '/home/jtel/jtelbackup/jtelbackup'
EOF
# Execution permissions and cron job
chmod 750 /home/jtel/jtelbackup/jtelbackup
chmod 750 /home/jtel/jtelbackup/jtelbackup.cron
ln -s /home/jtel/jtelbackup/jtelbackup.cron /etc/cron.daily
# Ownership to jtel user
chown -R jtel:jtel /home/jtel/.my.cnf
chown -R jtel:jtel /home/jtel/jtelbackup
chown -R jtel:jtel /home/jtel/shared/backup
# Run it
/etc/cron.daily/jtelbackup.cron
Once the script is run, a backup should be present in /home/jtel/shared/backup/sql/daily.
The script runs automatically when the daily cron jobs are run.