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 - Debian 11
In Debian 11 Buster, a setting must be made in the configuration file /lib/systemd/system/anacron.timer and the deamon/service must be reloaded. Otherwise, the daily cron jobs will run at the default value, which is <07..23:30>
vi /lib/systemd/system/anacron.timer <OnCalendar=*-*-* 04..23:30> systemctl daemon-reload systemctl restart anacron.timer systemctl status anacron.timer
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.sh #!/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 #!/bin/bash su - jtel -c '/home/jtel/jtelbackup/jtelbackup.sh' EOF # Execution permissions and cron job chmod 750 /home/jtel/jtelbackup/jtelbackup chmod 750 /home/jtel/jtelbackup/jtelbackup.sh ln -s /home/jtel/jtelbackup/jtelbackup /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
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.