Create MySQL-Backup Script (only on one master)The backup script is created on one of the master databasesmaster database. In a redundant system, it must be done on the active master databse. 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. Warning |
---|
title | Caution - without Replication |
---|
| If the setup is without replication, then remove -- source -data from the backup script. |
Warning |
---|
title | Caution - Redundancy - Backup only on active DBM |
---|
| Configure the backup on the active master database on redundant systems, as corruption of the index 'sp_AcdStatisticsLogin_geom3_AcdGroupsID' in the table JTELStats.AcdStatisticsLogin has happened in the past when the backup is made by the secondary database master. |
Status |
---|
colour | Red |
---|
title | Caution Password |
---|
|
Translations Ignore |
---|
Code Block |
---|
| # 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 --source-data --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. |