Server Backups - Revision history

Clive at 12:53, 13 September 2024

2024-09-13T12:53:18Z

← Older revision		Revision as of 12:53, 13 September 2024
Line 19:		Line 19:
	=== Nightly backup ===		=== Nightly backup ===

	The nightly back copies everything to <code>merple</code>. It:		The nightly back copies everything to <code>merple</code>. <code>merple</code> exposes <code/var/backups> via samba as <code>/backups</code>

			It:
	# mounts <code>/mnt/backup</code> to <code>//merple/backups</code>.		# 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/sqlserver/$DAY</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/mysql/$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/apache2-config/</code>		# copies the apache config to <code>/mnt/backup/gonzo/apache2-config/</code>
	# copies the ssl config to <code>/mnt/backup/ssl-config/</code>		# copies the ssl config to <code>/mnt/backup/gonzo/ssl-config/</code>
	# ~~finally,~~ 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/websites/</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>

	=== Other backups ===		=== 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 <code>/var/opt/mssql/data/backups/$BACKUP_FOLDER</code> and Matt can restore from them using Sql Server Manager.		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.

Clive at 13:56, 26 July 2024

2024-07-26T13:56:02Z

← Older revision		Revision as of 13:56, 26 July 2024
Line 22:		Line 22:
	# mounts <code>/mnt/backup</code> to <code>//merple/backups</code>.		# 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/sqlserver/$DAY</code>		# iterates over the <code>ms sqlserver</code> databases, backing them up to <code>/mnt/backup/sqlserver/$DAY</code>
	# iterates over the <code>mariadb> databases, backing them up to <code>/mnt/backup/mysql/$DAY</code>		# iterates over the <code>mariadb</code> databases, backing them up to <code>/mnt/backup/mysql/$DAY</code>
	# copies the apache config to <code>/mnt/backup/apache2-config/</code>		# copies the apache config to <code>/mnt/backup/apache2-config/</code>
	# copies the ssl config to <code>/mnt/backup/ssl-config/</code>		# copies the ssl config to <code>/mnt/backup/ssl-config/</code>

Clive at 13:54, 26 July 2024

2024-07-26T13:54:50Z

← Older revision		Revision as of 13:54, 26 July 2024
Line 1:		Line 1:
	[[Category:IT]]		[[Category:IT]]
	~~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.~~		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>. 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/sqlserver/$DAY</code>
			# iterates over the <code>mariadb> databases, backing them up to <code>/mnt/backup/mysql/$DAY</code>
			# copies the apache config to <code>/mnt/backup/apache2-config/</code>
			# copies the ssl config to <code>/mnt/backup/ssl-config/</code>
			# finally, 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/websites/</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.~~		=== Other backups ===

	~~== Flaws ==~~		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.

	* Errors go to ~~cron-mail~~, ~~which I'm fairly sure is a black hole~~
	* 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~~.

Clive: Clive moved page Favog Wiki Backups to Server Backups

2024-07-26T13:29:47Z

Clive moved page Favog Wiki Backups to Server Backups

← Older revision	Revision as of 13:29, 26 July 2024
(No difference)

Clive at 11:23, 23 July 2022

2022-07-23T11:23:51Z

← Older revision		Revision as of 11:23, 23 July 2022
Line 1:		Line 1:
			[[Category:IT]]
	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.		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.

Aquarion: 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..."

2018-03-05T16:26:40Z

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..."

New page

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 Script ==

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)

== Databases ==

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

== Wiki Uploads ==

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.

== Flaws ==

* Errors go to cron-mail, which I'm fairly sure is a black hole
* 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.