Empire

Server Backups

(Created page with "Every day, Cron runs a bash script to backup the MySQL DB & Mediawiki image files to Beaker, from where we rely on Beaker's cloud backup for longer term storage. == The Scrip...")
 
No edit summary
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
Every day, Cron runs a bash script to backup the MySQL DB & Mediawiki image files to Beaker, from where we rely on Beaker's cloud backup for longer term storage.
[[Category:IT]]
Crontab for <code>gonzo</code>


== The Script ==
# m h  dom mon dow  command
# every night (at 5am) back up all databases to merple
  0  5    *  *  *    /root/backup_scripts/nightly_backup.sh > /var/log/nightly_backup.log 2>&1
# local backup jobs
# give Matt a back up of the sql server databases every 4 hours
  0  */4  *  *  *    /root/backup_scripts/sqlserver_backup.sh HOUR > /var/log/hour_backup.log 2>&1
# every Monday
  0  4    1  *  *    /root/backup_scripts/sqlserver_backup.sh WEEK > /var/log/weekly_backup.log 2>&1
# and on the 1st of the month
  0  4    1  *  *    /root/backup_scripts/sqlserver_backup.sh MONTH > /var/log/monthly_backup.log 2>&1


The canonical location of the script is <code>/root/backup.sh</code>, there is a symlink from <code>/etc/cron.daily/backup.sh</code> (which is how it runs daily)
== The Scripts ==


== Databases ==
Scripts are located in <code>/root/backup</code>.


The script loops through the databases (except the system DBs 'information_schema', 'performance_schema'  & 'archive'), dumps them in SQL format to a tmpfile, and then compresses that file to <code>/mnt/beaker/IT/MySQL_Backups/[DAY OF THE WEEK]/[DATABASE].sql.bz</code>, providing us with an automated one week backlog
=== Nightly backup ===


== Wiki Uploads ==
The nightly back copies everything to <code>merple</code>. <code>merple</code> exposes <code/var/backups> via samba as <code>/backups</code>


The three live wikis (Crew, Plot & Public) also have their image directories backed up to <code>/mnt/beaker/IT/mediawiki-image-backups/</code> by rsync as part of the same script.
It:
# mounts <code>/mnt/backup</code> to <code>//merple/backups</code>.
# iterates over the <code>ms sqlserver</code> databases, backing them up to <code>/mnt/backup/gonzo/sqlserver/$DAY</code>
# iterates over the <code>mariadb</code> databases, backing them up to <code>/mnt/backup/gonzo/mysql/$DAY</code>
# copies the apache config to <code>/mnt/backup/gonzo/apache2-config/</code>
# copies the ssl config to <code>/mnt/backup/gonzo/ssl-config/</code>
# using rsync directly because smb has no compression and it's too slow otherwise, it copies <code>/var/www/html</code> directly to <code>merple:/srv/backups/gonzo/websites/</code>
# lastly, again using rsync directly, it copies <code>/home/pdcrew/*</code> to <code>merple:/home/pdcrew</code>


== Flaws ==  
=== Other backups ===


* Errors go to cron-mail, which I'm fairly sure is a black hole
In order to give Matt the ability to roll back any damaging database changes (or recover historical data), we back up the MS Sql Server every 4 hours, every Monday and on the first day of the month. These go to <code>/var/opt/mssql/data/backups/$BACKUP_FOLDER</code> and Matt can restore from them using Sql Server Manager.
* Working out which is the most recent backup isn't trivial
* Daily cron tasks happen at 17:45, which isn't a great time to slow the DB down.

Latest revision as of 12:53, 13 September 2024

Crontab for gonzo 

# m h  dom mon dow   command
# every night (at 5am) back up all databases to merple
  0  5    *   *   *    /root/backup_scripts/nightly_backup.sh > /var/log/nightly_backup.log 2>&1
# local backup jobs
# give Matt a back up of the sql server databases every 4 hours
  0  */4  *   *   *    /root/backup_scripts/sqlserver_backup.sh HOUR > /var/log/hour_backup.log 2>&1
# every Monday
  0  4    1   *   *    /root/backup_scripts/sqlserver_backup.sh WEEK > /var/log/weekly_backup.log 2>&1
# and on the 1st of the month
  0  4    1   *   *    /root/backup_scripts/sqlserver_backup.sh MONTH > /var/log/monthly_backup.log 2>&1

The Scripts

Scripts are located in /root/backup.

Nightly backup

The nightly back copies everything to merple. merple exposes via samba as /backups

It:

  1. mounts /mnt/backup to //merple/backups.
  2. iterates over the ms sqlserver databases, backing them up to /mnt/backup/gonzo/sqlserver/$DAY
  3. iterates over the mariadb databases, backing them up to /mnt/backup/gonzo/mysql/$DAY
  4. copies the apache config to /mnt/backup/gonzo/apache2-config/
  5. copies the ssl config to /mnt/backup/gonzo/ssl-config/
  6. using rsync directly because smb has no compression and it's too slow otherwise, it copies /var/www/html directly to merple:/srv/backups/gonzo/websites/
  7. lastly, again using rsync directly, it copies /home/pdcrew/* to merple:/home/pdcrew

Other backups

In order to give Matt the ability to roll back any damaging database changes (or recover historical data), we back up the MS Sql Server every 4 hours, every Monday and on the first day of the month. These go to /var/opt/mssql/data/backups/$BACKUP_FOLDER and Matt can restore from them using Sql Server Manager.

Retrieved from ‘https://www.profounddecisions.co.uk/mediawiki-crew-pd/index.php?title=Server_Backups&oldid=5554’