MySQL
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 MariaDB versus MySQL: Compatibility for details.
Creating databases
Users can create their own MySQL databases through ceo. Users emailing syscom asking for a MySQL database should be directed to do so. The process is as follows:
- SSH into any CSC machine.
- Run ceo.
- Select "Create MySQL database" and follow the instructions.
- Login info will be stored in ceo-mysql-info in your home directory.
- You can now connect to the MySQL database (from caffeine only).
Deleting databases
Users can delete their own MySQL databases.
SSH into caffeine.
mysql -u yourusernamehere -p Enter password: ****** DROP DATABASE database name goes here
Login info and database name was created on database creation in ceo-mysql-info in your home directory.
For syscom
Creating a database manually
To create a MySQL database manually on caffeine, first connect to the database as root:
$ mysql -uroot -p Enter password: ******
Then run the following SQL statements:
CREATE USER 'someuser'@'localhost' IDENTIFIED VIA unix_socket; CREATE USER 'someuser'@'%' IDENTIFIED BY 'longrandompassword'; CREATE DATABASE someuser; GRANT ALL PRIVILEGES ON someusername.* to 'someuser'@'localhost' IDENTIFIED VIA unix_socket; GRANT ALL PRIVILEGES ON someusername.* to 'someuser'@'%';
This will allow users to connect locally without a password, and connect remotely with a password.
For random passwords run pwgen -s 20 1. For the administrative passwords see /users/sysadmin/passwords/mysql.
Write a file (usually ~club/mysql) to the club's homedir readable only by them containing the following:
Username: clubuserid Password: longrandompassword Hostname: localhost
Try not to send passwords via plaintext email.
Replication
See the history of this page for information on the previous replication setup.
Backups
We use mariabackup to take periodic backups. It is currently installed and configured on both caffeine and coffee.
Installation
In the example below, we will be installing mariabackup on coffee, and sending the backups to corn-syrup.
First, install the mariadb-backup package:
apt install mariadb-backup
Next, create an SSH key pair for the mysql user:
mkdir /var/mariadb chown mysql:mysql /var/mariadb su -s /bin/bash mysql cd /var/mariadb mkdir .ssh chmod 700 .ssh # Choose /var/mariadb/.ssh/id_ed25519 for the path ssh-keygen -t ed25519
Paste the public key (/var/mariadb/.ssh/id_ed25519.pub) into /users/syscom/.ssh/authorized_keys on corn-syrup:
restrict ssh-ed25519 AAAAC3Nza... mysql@coffee
Also create the folder /users/syscom/backups/coffee/mariabackup. We will store the backups here.
We will use a hacky bash script to try to emulate the same behaviour as pgBackRest. We will compress and stream each backup to a folder on corn-syrup in the format 1701678356-F, where the number is a Unix epoch timestamp and the letter at the end is one of F, D or I (for full, differential or incremental backups). Full backups do not depend on any other backups. Differential backups depend on the latest full backup before them. Incremental backups depend on the latest backup before them (of any type).
On coffee, paste the following into e.g. /var/mariadb/bin/backup-mariadb.sh:
#!/bin/bash
RETENTION_FULL=2
RETENTION_DIFF=4
SSH_KEY=/var/mariadb/.ssh/id_ed25519
SSH_USER=syscom
SSH_HOST=corn-syrup
SSH_FOLDER=/users/$SSH_USER/backups/$(hostname)/mariabackup
SSH_ARGS="-i $SSH_KEY -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null"
SSH="ssh $SSH_ARGS $SSH_USER@$SSH_HOST"
set -euxo pipefail
# $USER doesn't seem to be defined when we run this from cron
if [ "$(id -un)" != mysql ]; then
echo "This script should run as the mysql user" >&2
exit 1
fi
if [ $# -ne 1 ]; then
echo "Usage: $0 <full|diff|incr>" >&2
exit 1
fi
backup_type=$1
if [ "$backup_type" = full ]; then
backup_type_letter=F
elif [ "$backup_type" = diff ]; then
backup_type_letter=D
elif [ "$backup_type" = incr ]; then
backup_type_letter=I
else
echo "Backup type must be one of 'full', 'diff' or 'incr'" >&2
exit 1
fi
if ! pgrep mariadbd >/dev/null; then
echo "MariaDB is not running" >&2
exit 1
fi
if pgrep mariabackup >/dev/null; then
echo "mariabackup is already running" >&2
exit 1
fi
# Delete temporary files left behind by previous run, if there are any
$SSH -- "rm -rf $SSH_FOLDER/*.tmp"
# Get a list of all backups in chronological order
mapfile -t backups < <($SSH -- "/bin/ls -1 $SSH_FOLDER | grep -P '^\\d+-[FDI]$' | sort")
incremental_basedir_args=
old_checkpoint_dir=$(mktemp -d)
new_checkpoint_dir=$(mktemp -d)
trap "rm -rf $old_checkpoint_dir $new_checkpoint_dir" EXIT
if [ "$backup_type" = diff -o "$backup_type" = incr ]; then
# Find a backup which we can use as a base.
# For incr, this can be any type; for diff, this must be a full backup.
base_backup=
for ((i=${#backups[@]}-1; i>=0; i--)); do
backup=${backups[i]}
if [ $backup_type = incr ] || [[ $backup =~ -F$ ]]; then
base_backup=$backup
break
fi
done
if [ -z "$base_backup" ]; then
echo "Could not find base backup for $backup_type type" >&2
exit 1
fi
# Copy the xtrabackup_checkpoints file from the base backup into a
# temporary directory, and use it in the mariabackup command.
scp $SSH_ARGS "$SSH_USER@$SSH_HOST:$SSH_FOLDER/$base_backup/xtrabackup_*" $old_checkpoint_dir/
incremental_basedir_args="--incremental-basedir=$old_checkpoint_dir"
fi
compress_level=6
if [ $backup_type = full ]; then
# Use a lower compression level to go faster
compress_level=5
fi
foldername="$(date +%s)-$backup_type_letter"
# First copy to a temporary dir, then rename the temporary dir to the
# desired dir name (in case our process gets killed)
mariabackup --user=mysql --backup $incremental_basedir_args --stream=xbstream --extra-lsndir=$new_checkpoint_dir \
| nice xz -$compress_level -T4 \
| $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/
$SSH -- "mv $SSH_FOLDER/$foldername.tmp $SSH_FOLDER/$foldername"
# Delete old backups
if [ $backup_type = incr ]; then
# We don't delete backups when making an incr backup, since we only
# have retention limits for full and diff
exit
fi
if [ $backup_type = full ]; then
retention=$RETENTION_FULL
else
retention=$RETENTION_DIFF
fi
num_backups_of_same_type=1
backups_to_delete=()
for ((i=${#backups[@]}-1; i>=0; i--)); do
backup=${backups[i]}
if ! [[ $backup =~ -${backup_type_letter}$ ]]; then
continue
fi
((num_backups_of_same_type++))
if [ $num_backups_of_same_type -lt $retention ]; then
continue
fi
if [ $backup_type = full ]; then
# Delete everything before the last full backup which we want to
# keep
pat='^'
else
# Delete all the diff and incr backups before the last diff backup
# which we want to keep
pat='-[DI]$'
fi
for ((j=$i-1; j>=0; j--)); do
backup=${backups[j]}
if [[ $backup =~ $pat ]]; then
backups_to_delete+=($backup)
fi
done
break
done
if [ ${#backups_to_delete[@]} -eq 0 ]; then
echo "No backups to delete" >&2
exit
fi
$SSH -- "cd $SSH_FOLDER && rm -r ${backups_to_delete[@]}"
The script should be invoked with exactly one argument which must be one of "full", "diff" or "incr".
Cron
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:
[Unit] Description=MariaDB backup (%i) Documentation=https://wiki.csclub.uwaterloo.ca/MySQL#Backups [Service] Type=oneshot User=mysql ExecStart=/var/mariadb/bin/backup-mariadb.sh %i [Unit] OnFailure=csc-email-on-failure@%n.service
Paste the following into /etc/systemd/system/mariadb-backup-full.timer:
[Unit] Description=MariaDB backup (full) [Timer] # Full back up at 00:20 every Sunday and Wednesday OnCalendar=Sun,Wed *-*-* 00:20:00 Unit=mariadb-backup@full.service Persistent=true [Install] WantedBy=timers.target
Paste the following into /etc/systemd/system/mariadb-backup-diff.timer:
[Unit] Description=MariaDB backup (diff) [Timer] # Differential backup at 00:35 every day OnCalendar=*-*-* 00:35:00 Unit=mariadb-backup@diff.service Persistent=true [Install] WantedBy=timers.target
Paste the following into /etc/systemd/system/mariadb-backup-incr.timer:
[Unit] Description=MariaDB backup (incr) [Timer] # Incremental backup at the 50th minute of every hour OnCalendar=*-*-* *:50:00 Unit=mariadb-backup@incr.service Persistent=true [Install] WantedBy=timers.target
Finally, enable and start the timers:
systemctl daemon-reload systemctl enable --now mariadb-backup-full.timer systemctl enable --now mariadb-backup-diff.timer systemctl enable --now mariadb-backup-incr.timer
Restore
Paste the following into e.g. /var/mariadb/bin/restore-mariadb.sh:
#!/bin/bash
SSH_KEY=/var/mariadb/.ssh/id_ed25519
SSH_USER=syscom
SSH_HOST=corn-syrup
SSH_FOLDER=/users/$SSH_USER/backups/$(hostname)/mariabackup
SSH_ARGS="-i $SSH_KEY -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null"
SSH="ssh $SSH_ARGS $SSH_USER@$SSH_HOST"
set -euxo pipefail
shopt -s dotglob
if [ "$(id -un)" != mysql ]; then
echo "This script should run as the mysql user" >&2
exit 1
fi
if [ $# -gt 1 ]; then
echo "Usage: $0 [0123456789-I]" >&2
exit 1
fi
if pgrep mariadbd >/dev/null; then
echo "Please stop MariaDB first" >&2
exit 1
fi
# Get a list of all backups in chronological order
mapfile -t backups < <($SSH -- "/bin/ls -1 $SSH_FOLDER | grep -P '^\\d+-[FDI]$' | sort")
if [ ${#backups[@]} -eq 0 ]; then
echo "No backups found" >&2
exit 1
fi
if [ $# -eq 1 ]; then
last_backup_idx=
for ((i=${#backups[@]}-1; i>=0; i--)); do
if [ ${backups[i]} = "$1" ]; then
last_backup_idx=$i
break
fi
done
if [ -z "$last_backup_idx" ]; then
echo "Could not find $1 on remote" >&2
exit 1
fi
else
last_backup_idx=$(( ${#backups[@]} - 1 ))
fi
last_full_backup_idx=
for ((i=$last_backup_idx; i>=0; i--)); do
if [[ ${backups[i]} =~ -F$ ]]; then
last_full_backup_idx=$i
break
fi
done
if [ -z "$last_full_backup_idx" ]; then
echo "Could not find full backup for ${backups[last_backup_idx]}" >&2
exit 1
fi
backups_to_use=()
if [[ ${backups[last_backup_idx]} =~ -F$ ]]; then
# If we're restoring a full backup, we only need that one backup
backups_to_use=(${backups[last_backup_idx]})
elif [[ ${backups[last_backup_idx]} =~ -D$ ]]; then
# If we're restoring a diff backup, we only need that one backup and the
# first full backup before it
backups_to_use=(${backups[last_full_backup_idx]} ${backups[last_backup_idx]})
else
# If we're restoring an incr backup, we need all the backups from it to
# the first diff backup before it, and the first full backup before that.
# If there is no diff backup between it and the last full backup, then
# we need everything between it and the last full backup.
for ((i=$last_backup_idx; i>=$last_full_backup_idx; i--)); do
backups_to_use=(${backups[i]} ${backups_to_use[@]})
if [[ ${backups[i]} =~ -D$ ]]; then
backups_to_use=(${backups[last_full_backup_idx]} ${backups_to_use[@]})
break
fi
done
fi
base_dir=$(mktemp -d)
incr_dir=$(mktemp -d)
trap "rm -rf $base_dir $incr_dir" EXIT
for backup in ${backups_to_use[@]}; do
if [[ $backup =~ -F$ ]]; then
backup_dir=$base_dir
else
backup_dir=$incr_dir
fi
$SSH -- "cat $SSH_FOLDER/$backup/data.xb.xz" | xz -d | mbstream -x -C $backup_dir
incremental_dir_args=
if [ $backup_dir = $incr_dir ]; then
incremental_dir_args="--incremental-dir=$incr_dir"
fi
mariabackup --prepare --target-dir=$base_dir $incremental_dir_args
if [ $backup_dir = $incr_dir ]; then
rm -rf $incr_dir/*
fi
done
if [ "$(/bin/ls -1 /var/lib/mysql | wc -l)" -gt 0 ]; then
read -p "Everything under /var/lib/mysql will be deleted. Continue (y/n)? " yn
yn=${yn,,} # convert to lower case
if [ "$yn" = y -o "$yn" = yes ]; then
rm -rf /var/lib/mysql/*
else
echo "Aborting." >&2
exit 1
fi
fi
mariabackup --move-back --target-dir=$base_dir
echo "Restoration succeeded, please restart MariaDB"
Make sure to stop MariaDB before restoring a backup. If this script is invoked without any arguments, the latest backup found on corn-syrup will be used; a single argument may also be specified, which must be the name of one of the backup folders stored on corn-syrup.