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 xz -$compress_level -T0 \
    | $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

Paste something like the following into e.g. /etc/cron.d/mariadb_backup:

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