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:

1. SSH into any CSC machine.
2. Run ceo.
3. Select "Create MySQL database" and follow the instructions.
4. Login info will be stored in ceo-mysql-info in your home directory.
5. 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 zstd -$compress_level -T4 \
    | $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/
$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.zst" | zstd -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.