Tutorial: Launch the MySQL database on a virtual machine¶
Requirements:¶
- a VM running Ubuntu on your hardware of choice.
In this tutorial we'll cover how to launch the MySQL database, which will store, retrieve, and manage all (or most) of your data based on the commands and queries issued through DataJoint (covered in later tutorials).
We'll launch the database on a virtual machine (VM). There are many ways to set up a VM, depending on the hardware you'll be using. We launched Epiphyte on a university cluster which has its own specific methodology for launching VMs. We won't go into the details here, as they won't be relevant to most people.
We recommend getting in touch with your system administrator to first set up a VM on your organization's hardware, or if you're doing it yourself from a local computer, to follow tutorials such as this one by freeCodeCamp. We used Ubuntu Focal (20.04.2 LTS) for the operating system (OS), and the instructions below assume you are working from a Linux terminal. If your VM is running on another server, you should be able to access it with a command such as ssh -i /path/to/.ssh/<id_rsa.pub> <os>@<ip>.
Whether you're working with a VM set up locally or on a remote server, be sure to allow admin access to anyone who will be managing the database by adding their public SSH keys to the VM (the folder will be something like ~/.ssh/authorized_keys, and you might need to first create the folder if it's not already present).
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 your 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>'@'%';