setup-mysql.sh

#!/bin/bash

# Installs mariadb in Debian 9 GCP VM, creates self-signed certificates,
# sets up the server config, and restarts the mysql server

set -e
set -x

apt-get update
apt-get install -y mysql-server

systemctl start mysql

mysql_secure_installation

CERTS_DIR=/etc/mysql/certs

BITS=2048

if [[ ! -e "$CERTS_DIR" ]]; then
  mkdir -p "$CERTS_DIR"
fi

pushd $CERTS_DIR

openssl genrsa $BITS > ca-key.pem

openssl req -new -x509 -nodes -days 36500 -key ca-key.pem \
  -subj '/C=JP/ST=Tokyo/L=Shibuya/O=builderscon/CN=mysql-admin' \
  -out ca-cert.pem

openssl req \
  -newkey rsa:$BITS \
  -days 36500 \
  -nodes \
  -subj '/C=JP/ST=Tokyo/L=Shibuya/O=builderscon/CN=mysql-server' \
  -keyout server-key.pem \
  -out    server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 \
  -req \
  -in server-req.pem \
  -days 36500 \
  -CA ca-cert.pem \
  -CAkey ca-key.pem \
  -set_serial 01 \
  -out server-cert.pem

openssl req \
  -newkey rsa:$BITS \
  -days 36500 \
    -nodes \
  -subj '/C=JP/ST=Tokyo/L=Shibuya/O=builderscon/CN=mysql-client' \
  -keyout client-key.pem \
  -out client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 \
  -req \
  -in client-req.pem \
  -days 36500 \
  -CA ca-cert.pem \
  -CAkey ca-key.pem \
  -set_serial 01 \
  -out client-cert.pem

openssl verify -CAfile ca-cert.pem server-cert.pem
openssl verify -CAfile ca-cert.pem client-cert.pem

chown mysql *.pem
chmod go-rwx *-key.pem

popd

chown mysql  $CERTS_DIR/*.pem
chmod u-wx   $CERTS_DIR/*.pem
chmod og-rwx $CERTS_DIR/*-key.pem

# Change the server config
CONFIG=/etc/mysql/mariadb.conf.d/50-server.cnf

sed -i -e 's/^#\s*ssl\s*=\s*.*$/ssl = on/' $CONFIG
sed -i -e 's!^#\s*ssl-ca\s*=\s*.*$!ssl-ca      = '"$CERTS_DIR/ca-key.pem!" $CONFIG
sed -i -e 's!^#\s*ssl-cert\s*=\s*.*$!ssl-cert  = '"$CERTS_DIR/server-cert.pem!" $CONFIG
sed -i -e 's!^#\s*ssl-key\s*=\s*.*$!ssl-key    = '"$CERTS_DIR/server-key.pem!" $CONFIG
sed -i -e 's/^port\s*.*$/port             = 13306/' $CONFIG
sed -i -e 's!^bind-address\s*=\s*.*$!bind-address = *!' $CONFIG

# Change the client config (so that the check for `select verrsion()` below uses proper ssl
CONFIG=/etc/mysql/mariadb.conf.d/50-client.cnf

sed -i -e 's!^#\s*ssl-cert\s*=\s*.*$!ssl-cert  = '"$CERTS_DIR/client-cert.pem!" $CONFIG
sed -i -e 's!^#\s*ssl-key\s*=\s*.*$!ssl-key    = '"$CERTS_DIR/client-key.pem!" $CONFIG

# We could just flush privileges here, but why not.
systemctl restart mysql

echo "mysql reconfigured and restarted."

set +x

# Waiiiit
sleep 3

# Sanity check
echo 'select version()' | mysql -uroot myql

echo "Sanity check OK"

echo ""
echo "now grant permissions to octav user by issuing the following command:"
echo ""
echo "   grant all on *.* to octav@'%' identified by 'PASSWORD' require SSL;"
echo ""
echo "you also must copy the following files to be used by the mysq client:"
echo ""
echo "== client-cert.pem =="
cat $CERTS_DIR/client-cert.pem
echo ""
echo "== client-key.pem =="
cat $CERTS_DIR/client-key.pem
echo ""