Launch the MySQL database on a virtual machine

Goal:

Configure a virtual machine on a personal computer or a remotely-hosted server and set up the MySQL environment.

Requirements:

• a virtual machine (VM) running Ubuntu* on your hardware of choice

If.. * you want to test infrastructure or run a VM on a personal computer, here's a tutorial on setting up a VM locally. * you want to launch a database on a compute cluster, get in touch with a system administrator to set-up a VM on your organization's hardware. * your VM is running on a separate server, assess it via ssh -i /path/to/.ssh/<id_rsa.pub> <os>@<ip>

* For this tutorial, we used Ubuntu Focal (20.04.2 LTS).

Steps:

1. Configure the VM
2. Install Docker and Docker Compose
3. Install the MySQL Client
4. Set up the Docker environment
5. Launch the containers for MySQL and MinIO
6. Create user accounts

Additional details:

We received feedback that users setting up their own VM locally ran into an issue regarding EFI Secure Boot and signing the kernel modules. Their proposed fix (source):

sudo apt install --reinstall virtualbox-dkms

---

1. Configure the VM

First, enable sudo for your user (e.g. ubuntu if you installed an Ubuntu OS). You will need to set a password. Run the commands below from a terminal on your VM:

sudo -i
sudo passwd ubuntu
<password>

Don't allow sudo without password:

sudo -s
cd /etc/sudoers.d
nano 90-cloud-init-users

Remove NOPASSWD from the file and save it (NOPASSWD:ALL becomes just ALL).

Unfortunately, it is highly likely that someone somewhere will try to break into your server using brute force attacks. That's why we'll add security with Fail2Ban, which bans IP addresses conducting too many failed login attempts.

sudo apt-get update
sudo apt-get install fail2ban

You can edit the default settings at sudo nano /etc/fail2ban/jail.local.save (such as "bantime", the time a host is banned, or "maxretry", the number of failures before getting banned).

Now we'll open the ports we'll use for SSH (port 22), MySQL (port 3306), and MinIO (port 9000, optional). We'll activate the Linux firewall, ufw, to enforce these changes.

sudo ufw allow 22 
sudo ufw allow 3306 
sudo ufw allow 9000  # optional, if using MinIO
sudo ufw enable

2. Install Docker and Docker Compose

The next step is to install Docker for Linux.

Verify your Docker installation:

docker --version
docker run hello-world

Next, install Docker Compose.

Verify the Docker Compose installation:

docker-compose --version

3. Install the MySQL client

sudo apt-get install mysql-client

4. Set up the Docker environment

Create the directory where your Docker container will run (the folder names below are examples and can be changed, just be consistent with your new name in later steps).

sudo mkdir ~/mysql/mysql-docker
sudo chown <user>: ~/mysql/mysql-docker

Download the files from here and place them in your new folder ~/mysql/mysql-docker (we use a modified version of the Docker container provided by DataJoint).

Choose a password for both MySQL and MinIO (optional).

sudo nano datajoint.env.example

Insert your password, MYSQL_ROOT_PASSWORD=<password>, and rename the file to datajoint.env.

mv datajoint.env.example datajoint.env

Do the same for the MinIO file (if using):

sudo nano minio.env.example

Set MINIO_ROOT_USER=root and insert your password of choice, MINIO_ROOT_PASSWORD=<password>. Rename this file, too.

mv minio.env.example minio.env

Edit directories as needed in docker-compose.yaml (relevant lines are marked in the file):

sudo nano docker-compose.yaml

5. Launch the containers for MySQL and MinIO

sudo docker-compose up -d

You can now log into your MySQL database! Exciting, but before we log in we still have a few steps to make our new database more secure. We'll start by attaching to our now-running Docker container:

sudo docker-compose exec <container name> bash

Now run the following command and follow the prompts to add essential security measures to your MySQL installation:

mysql_secure_installation

And again install Fail2Ban:

sudo apt-get update
sudo apt-get install fail2ban

6. Create user accounts

Now for the fun part—we can finally log into our new MySQL database!

mysql -h 127.0.0.1 -u root -p

Now you should be operating from a MySQL shell as the root user. Create new user accounts for the people you want to give access to:

CREATE USER '<username>'@'%' IDENTIFIED BY '<password>;
GRANT ALL PRIVILEGES ON `<username>\_%`.* TO '<username>'@'%';