Vultr bash script to attach Block Storage to mysql on ubuntu

README.md

Vultr Block Storage for MySQL

datadir + tmpdir on Ubuntu 22.04 LTS

I had a somewhat ephemeral use-case for mysql server but due to some heavy indexes (and tmp dir usage) the disk kept running out of room on smallish instances.

How to run

• Create your Block Storage and Vultr instance in the same region
• Under Block Storage settings, "attach" to your instance.
• shell into instance, save init-block.sh at ~/init-block.sh
• Run it:

sudo chmod 755 init-block.sh && ./init-block.sh

TODO

• Fix bypass needrestart nag
• probably convert /etc/apparmor.d/usr.sbin.mysqld to simple sed commands See "Allow tmp dir access"

init-block.sh

#!/bin/bash

## Was made for vultr but probably close to other EBS type attached blocks

# Tip: stay in the same data center
# Cloud Compute > Regular Performance > Los Angeles > Ubuntu 22.04 LTS
# Storage > Add New Block > Block Storage (NVMe) > Los Angeles > 140 Gigs, etc
# Attach block to instance above...then run this...

# sudo chmod 755 init-block.sh && ./init-block.sh

sed -i "s/#\$nrconf{kernelhints} = -1;/\$nrconf{kernelhints} = -1;/g" /etc/needrestart/needrestart.conf

sudo apt update && sudo apt install -y mysql-server

parted -s /dev/vdb mklabel gpt && \
parted -s /dev/vdb unit mib mkpart primary 0% 100% && \
mkfs.ext4 /dev/vdb1 && \
mkdir /mnt/data && \
echo >> /etc/fstab && \
echo /dev/vdb1 /mnt/data ext4 defaults,noatime,nofail 0 0 >> /etc/fstab && \
mount /mnt/data

sudo systemctl stop mysql && \
sudo rsync -av /var/lib/mysql /mnt/data && \
mkdir /mnt/data/tmp && chmod 777 /mnt/data/tmp && \
echo >> /etc/mysql/mysql.conf.d/mysqld.cnf && \
echo datadir=/mnt/data/mysql >> /etc/mysql/mysql.conf.d/mysqld.cnf && \
echo tmpdir = /mnt/data/tmp >> /etc/mysql/mysql.conf.d/mysqld.cnf && \
echo >> /etc/apparmor.d/tunables/alias && \
echo "alias /var/lib/mysql/ -> /mnt/data/mysql/," >> /etc/apparmor.d/tunables/alias

# update apparmor conf
cat > /etc/apparmor.d/usr.sbin.mysqld << EOF
# vim:syntax=apparmor
# Last Modified: Tue Feb 09 15:28:30 2016
#include <tunables/global>

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

# Allow system resource access
  /proc/*/status r,
  /sys/devices/system/cpu/ r,
  /sys/devices/system/node/ r,
  /sys/devices/system/node/** r,
  capability sys_resource,
  capability dac_override,
  capability dac_read_search,
  capability setuid,
  capability setgid,

# Allow network access
  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

# Allow config access
  /etc/mysql/** r,

# Allow pid, socket, socket lock file access
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock rw,
  /var/run/mysqld/mysqld.sock.lock rw,
  /var/run/mysqld/mysqlx.sock rw,
  /var/run/mysqld/mysqlx.sock.lock rw,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock rw,
  /run/mysqld/mysqld.sock.lock rw,
  /run/mysqld/mysqlx.sock rw,
  /run/mysqld/mysqlx.sock.lock rw,

# Allow systemd notify messages
  /{,var/}run/systemd/notify w,

# Allow execution of server binary
  /usr/sbin/mysqld mr,
  /usr/sbin/mysqld-debug mr,

# Allow plugin access
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,

# Allow error msg and charset access
  /usr/share/mysql/ r,
  /usr/share/mysql/** r,

# Allow data dir access
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,

# Allow tmp dir access
  /mnt/data/ r,
  /mnt/data/** rwk,

# Allow data files dir access
  /var/lib/mysql-files/ r,
  /var/lib/mysql-files/** rwk,

# Allow keyring dir access
  /var/lib/mysql-keyring/ r,
  /var/lib/mysql-keyring/** rwk,

# Allow log file access
  /var/log/mysql.err rw,
  /var/log/mysql.log rw,
  /var/log/mysql/ r,
  /var/log/mysql/** rw,

# Allow read access to mecab files
  /var/lib/mecab/dic/ipadic-utf8/** r,

# Allow read access to OpenSSL config
  /etc/ssl/openssl.cnf r,
  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}
EOF

# SHOW VARIABLES LIKE 'tmpdir';

sudo systemctl restart apparmor && \
sudo systemctl restart mysql