create MySQL-Backup  (Only one of the masters)

The MySQL backup is created on the master server. The server performs a daily backup and deletes all files older than 14 days.

Simply copy the following into the command line as root and execute - replace <password> with the corresponding password. 

Afterwards there should be a backup on /home/jtel/shared/backup/sql/daily

# 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 --add-drop-table --events --routines --triggers --databases JTELWeb JTELStats JTELStats2 JTELLog | gzip > /home/jtel/shared/backup/sql/daily/backup-$(hostname -s)-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


  • No labels