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. | 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/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
# DailyjobsExecution scriptpermissions
catchmod << 'EOF' >+x /home/jtel/jtel_dailyjobs/jtel_backup.sh
chmod +x /home/jtel/jtel_dailyjobs/jtel_dailyjobscleaner.sh
#!/bin/bash
bash Does the dailyjobs script exist? If not, create.
if [ ! -f /home/jtel/jtel_dailyjobs/jtel_backupdailyjobs.sh
bash ]; then
# No - so create it
cat << EOFF > /home/jtel/jtel_dailyjobs/jtel_cleanerdailyjobs.sh
EOF
#!/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
# Execution permissions and cron job
chmod 750Link 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 750+x /home/jtel/jtel_dailyjobs/jtel_dailyjobs.sh
chmod 750
fi
# Add jobs to dailyjobs script
cat << 'EOF' >> /home/jtel/jtel_dailyjobs/jtel_backupdailyjobs.sh
chmodbash 750 /home/jtel/jtel_dailyjobs/jtel_cleanerbackup.sh
lnbash -s /home/jtel/jtel_dailyjobs/jtel_dailyjobs /etc/cron.dailycleaner.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. | 