MySQL - Revision history

Merenber: /* Backups */

2024-03-30T15:03:11Z

Backups

← Older revision		Revision as of 10:03, 30 March 2024
Line 165:		Line 165:
	# desired dir name (in case our process gets killed)		# desired dir name (in case our process gets killed)
	mariabackup --user=mysql --backup $incremental_basedir_args --stream=xbstream --extra-lsndir=$new_checkpoint_dir \		mariabackup --user=mysql --backup $incremental_basedir_args --stream=xbstream --extra-lsndir=$new_checkpoint_dir \
	\| nice xz -$compress_level -T4 \		\| nice zstd -$compress_level -T4 \
	\| $SSH -- "cd $SSH_FOLDER && mkdir $foldername.tmp && cat > $foldername.tmp/data.xb.xz"		\| $SSH -- "cd $SSH_FOLDER && mkdir $foldername.tmp && cat > $foldername.tmp/data.xb.zst"
	scp $SSH_ARGS $new_checkpoint_dir/* $SSH_USER@$SSH_HOST:$SSH_FOLDER/$foldername.tmp/		scp $SSH_ARGS $new_checkpoint_dir/* $SSH_USER@$SSH_HOST:$SSH_FOLDER/$foldername.tmp/
	$SSH -- "mv $SSH_FOLDER/$foldername.tmp $SSH_FOLDER/$foldername"		$SSH -- "mv $SSH_FOLDER/$foldername.tmp $SSH_FOLDER/$foldername"
Line 376:		Line 376:
	backup_dir=$incr_dir		backup_dir=$incr_dir
	fi		fi
	$SSH -- "cat $SSH_FOLDER/$backup/data.xb.xz" \| xz -d \| mbstream -x -C $backup_dir		$SSH -- "cat $SSH_FOLDER/$backup/data.xb.zst" \| zstd -d \| mbstream -x -C $backup_dir
	incremental_dir_args=		incremental_dir_args=
	if [ $backup_dir = $incr_dir ]; then		if [ $backup_dir = $incr_dir ]; then

Merenber: /* Cron */

2024-03-16T08:51:31Z

Cron

← Older revision		Revision as of 03:51, 16 March 2024
Line 218:		Line 218:

	==== Cron ====		==== Cron ====
	We are going to use systemd timers because they are much nicer to use than cron. Install /etc/systemd/system/csc-email-on-failure@.service on the target machine so that we get emails for failed jobs (there should be a copy of this on caffeine).		We are going to use systemd timers because they are much nicer to use than cron. Install /usr/local/bin/csc-systemd-email and /etc/systemd/system/csc-email-on-failure@.service on the target machine so that we get emails for failed jobs (there should be a copy of this on caffeine).

	Paste the following into /etc/systemd/system/mariadb-backup@.service:		Paste the following into /etc/systemd/system/mariadb-backup@.service:

Merenber: /* Cron */

2024-03-16T08:50:58Z

Cron

← Older revision		Revision as of 03:50, 16 March 2024
Line 218:		Line 218:

	==== Cron ====		==== Cron ====
	We are going to use systemd timers because they are much nicer to use than cron. Install /etc/systemd/system/~~mariadb~~-~~backup~~@.service on the target machine so that we get emails for failed jobs (there should be a copy of this on caffeine).		We are going to use systemd timers because they are much nicer to use than cron. Install /etc/systemd/system/csc-email-on-failure@.service on the target machine so that we get emails for failed jobs (there should be a copy of this on caffeine).

	Paste the following into /etc/systemd/system/mariadb-backup@.service:		Paste the following into /etc/systemd/system/mariadb-backup@.service:

Merenber: /* Cron */

2024-03-16T08:48:15Z

Cron

@@ Line 218: / Line 218: @@
 ==== Cron ====
 <pre>
 # Full back up at 00:20 every Sunday and Wednesday
 # Differential backup at 00:35 every day
 # Incremental backup at the 50th minute of every hour
 </pre>

Merenber: /* Installation */

2024-03-11T16:40:09Z

Installation

← Older revision		Revision as of 11:40, 11 March 2024
Line 165:		Line 165:
	# desired dir name (in case our process gets killed)		# desired dir name (in case our process gets killed)
	mariabackup --user=mysql --backup $incremental_basedir_args --stream=xbstream --extra-lsndir=$new_checkpoint_dir \		mariabackup --user=mysql --backup $incremental_basedir_args --stream=xbstream --extra-lsndir=$new_checkpoint_dir \
	\| nice xz -$compress_level -T0 \		\| nice xz -$compress_level -T4 \
	\| $SSH -- "cd $SSH_FOLDER && mkdir $foldername.tmp && cat > $foldername.tmp/data.xb.xz"		\| $SSH -- "cd $SSH_FOLDER && mkdir $foldername.tmp && cat > $foldername.tmp/data.xb.xz"
	scp $SSH_ARGS $new_checkpoint_dir/* $SSH_USER@$SSH_HOST:$SSH_FOLDER/$foldername.tmp/		scp $SSH_ARGS $new_checkpoint_dir/* $SSH_USER@$SSH_HOST:$SSH_FOLDER/$foldername.tmp/

Merenber: /* Backups */

2023-12-05T18:23:13Z

Backups

@@ Line 229: / Line 229: @@
 * * * * mysql chronic /var/mariadb/bin/backup-mariadb.sh incr
 </pre>
 [[Category:Software]]

Merenber at 18:17, 5 December 2023

2023-12-05T18:17:46Z

← Older revision		Revision as of 13:17, 5 December 2023
Line 1:		Line 1:
	== For members ==		== For members ==
	Note: the database on caffeine is actually MariaDB, not MySQL. Although they are mostly compatible, there are some incompatibilities to be aware of. See [[https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ MariaDB versus MySQL: Compatibility]] for details.		Note: the database on caffeine is actually MariaDB, not MySQL. Although they are mostly compatible, there are some incompatibilities to be aware of. See [https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ MariaDB versus MySQL: Compatibility] for details.

	=== Creating databases ===		=== Creating databases ===
Line 56:		Line 56:
	=== Backups ===		=== Backups ===

	We use [[https://mariadb.com/kb/en/mariabackup-overview/ mariabackup]] to take periodic backups. It is currently installed and configured on both caffeine and coffee.		We use [https://mariadb.com/kb/en/mariabackup-overview/ mariabackup] to take periodic backups. It is currently installed and configured on both caffeine and coffee.

	==== Installation ====		==== Installation ====
Line 216:		Line 216:
	</pre>		</pre>
	The script should be invoked with exactly one argument which must be one of "full", "diff" or "incr".		The script should be invoked with exactly one argument which must be one of "full", "diff" or "incr".

			==== Cron ====
			Paste something like the following into e.g. /etc/cron.d/mariadb_backup:
			<pre>
			MAILTO=root@csclub.uwaterloo.ca

			# Full back up at 00:20 every Sunday and Wednesday
			20 0 * * 0,3 mysql chronic /var/mariadb/bin/backup-mariadb.sh full
			# Differential backup at 00:35 every day
			35 0 * * * mysql chronic /var/mariadb/bin/backup-mariadb.sh diff
			# Incremental backup at the 50th minute of every hour
			50 * * * * mysql chronic /var/mariadb/bin/backup-mariadb.sh incr
			</pre>

	[[Category:Software]]		[[Category:Software]]

Merenber at 18:15, 5 December 2023

2023-12-05T18:15:25Z

Show changes

Merenber at 18:04, 5 December 2023

2023-12-05T18:04:28Z

← Older revision		Revision as of 13:04, 5 December 2023
Line 1:		Line 1:
	== For members ==		== For members ==
			Note: the database on caffeine is actually MariaDB, not MySQL. Although they are mostly compatible, there are some incompatibilities to be aware of. See [[https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ MariaDB versus MySQL: Compatibility]] for details.

	=== Creating databases ===		=== Creating databases ===
Line 23:		Line 24:
	== For syscom ==		== For syscom ==

	=== ~~Manually~~ ===		=== Creating a database manually ===
	To create a MySQL database manually, first connect to the database as root:		To create a MySQL database manually on caffeine, first connect to the database as root:

	$ mysql -uroot -p		$ mysql -uroot -p

Merenber at 18:02, 5 December 2023

2023-12-05T18:02:49Z

← Older revision		Revision as of 13:02, 5 December 2023
Line 1:		Line 1:
	== For members ==		== For members ==
	Note: the database on caffeine is actually MariaDB, not MySQL. Although they are mostly compatible, there are some incompatibilities to be aware of. See [[https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ MariaDB versus MySQL: Compatibility]] for details.

	=== Creating databases ===		=== Creating databases ===
Line 14:		Line 13:
	=== Deleting databases ===		=== Deleting databases ===

	Users can delete their own MySQL databases.		Users can delete their own MySQL databases.

	SSH into [[Machine_List#caffeine\|caffeine]].		SSH into [[Machine_List#caffeine\|caffeine]].
Line 24:		Line 23:
	== For syscom ==		== For syscom ==

	=== ~~Creating a database manually~~ ===		=== Manually ===
	To create a MySQL database manually ~~on caffeine~~, first connect to the database as root:		To create a MySQL database manually, first connect to the database as root:

	$ mysql -uroot -p		$ mysql -uroot -p