Create MySQL-Backup and cleaner Script (only on one master)
The backup script is created on master 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. The cleaner script is also configured right after, to ensure that the two jobs do not run in parallel
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 --
source
-data
from the backup script.
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.
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/jtel_dailyjobs 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/jtel_dailyjobs/jtel_backup.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 cleaner script cat << 'EOF' > /home/jtel/jtel_dailyjobs/jtel_cleaner.sh #!/bin/bash bash /home/jtel/shared/JTELCarrierPortal/DB/mySQL/cleaners/db_cleaner.sh EOF # Execution permissions chmod +x /home/jtel/jtel_dailyjobs/jtel_backup.sh chmod +x /home/jtel/jtel_dailyjobs/jtel_cleaner.sh # Does the dailyjobs script exist? If not, create. if [ ! -f /home/jtel/jtel_dailyjobs/jtel_dailyjobs.sh ]; then # No - so create it cat << EOFF > /home/jtel/jtel_dailyjobs/jtel_dailyjobs.sh #!/bin/bash EOFF # Create cron script cat << EOF > /home/jtel/jtel_dailyjobs/jtel_dailyjobs #!/bin/bash su - jtel -c '/home/jtel/jtel_dailyjobs/jtel_dailyjobs.sh' EOF # Link to cron.daily ln -s /home/jtel/jtel_dailyjobs/jtel_dailyjobs /etc/cron.daily # Make executable chmod +x /home/jtel/jtel_dailyjobs/jtel_dailyjobs chmod +x /home/jtel/jtel_dailyjobs/jtel_dailyjobs.sh fi # Add jobs to dailyjobs script cat << 'EOF' >> /home/jtel/jtel_dailyjobs/jtel_dailyjobs.sh bash /home/jtel/jtel_dailyjobs/jtel_backup.sh bash /home/jtel/jtel_dailyjobs/jtel_cleaner.sh EOF # Ownership to jtel user chown -R jtel:jtel /home/jtel/.my.cnf chown -R jtel:jtel /home/jtel/jtel_dailyjobs chown -R jtel:jtel /home/jtel/shared/backup # Run it as a test /etc/cron.daily/jtel_dailyjobs
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.