Installing MySQL 8 from Scratch on Ubuntu 18.04 For Use By Many Students

Comp Sci User
Nov 2 · 4 min read

I help provide technology solutions for a Data Science Program as part of my masters student duties. One of the problems I faced recently was providing MySQL 8 access to a group of students. Server access was a scarcity and took too long to get access to on campus and so I ended up creating my own system to provide the service.

Starting with an install of Ubuntu 18.04, in this article I go through the steps I took in order to set up a MySQL 8 server and create user accounts for a group of students given an excel file with their names and email addresses. I wanted to be able to give the students the ability to create and drop their own databases so as to get real world experience and understanding in database operations. The following explains the strategy I took.

Getting started:

After the initial login, we want to update our server:

$ sudo apt update
$ sudo apt upgrade -y

The next few steps are only important if you are running as a root user:

Since root users carry significant power, the use of these accounts is discouraged. Instead, it is recommended that you create a non-root user and give it superuser (sudo) privileges. Choose a username and do the following:

$ adduser newUserName

Follow the instructions and enter Y to create the user. Then:

$ usermod -aG sudo newUserName

The user newUserName now has sudo privileges.

Now, we want to set up the firewall. It is important here to follow these steps exactly, as if you fail to open the port for SSH access, you will be cut off from communicating with your server. Type the following:

$ ufw allow OpenSSH

This should be fine for the vast majority of users. However, if it is known that you connect by a port other than the standard port 22, you will have to enable that now by running ‘$ ufw allow portNum’.

Now, it is time to enable the firewall:

$ ufw enable

At this point, if you were logged in as a root user, you should log out and ssh back in as the new user you created.

It’s time to install MySQL 8.

MySQL 8 is not the default package for Ubuntu 18.04, and we will therefore have to install the apt-config locations for the latest version by running:

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb

which is the latest version as of this writing (or go to https://dev.mysql.com/downloads/repo/apt/ and find the link to a newer version if it exists). Now run:

$ sudo dpkg -i mysql-apt-config_0.8.14–1_all.deb

You will be presented with the following picture, which you can select OK on:

Apt Config Screen for MySQL 8

Now, do the following:

$ sudo apt-get update
$ sudo apt-get install mysql-server

You will now be asked to select a root password. This will be the root password to access your MySQL server instance, so choose wisely and make sure you take note of it. Then, it is suggested to choose User Strong Password Encryption, unless it is known that applications you are using will not work with it.

Now, run the following and follow the instructions to set up the MySQL server (I selected n-n-y-y-y-y, although you may want to select y on the first if you are worried about creating secure passwords).

MySQL is now set up and ready to go!

One last step: Allow port access. Run the following:

$ sudo ufw allow 3306/tcp
$ sudo ufw allow 33060/tcp

Your MySQL server is now ready for accessing.

Setting it up for Students / Creating many accounts from a list at once

There was one last step: creating user accounts that had access to the databases. In my case, I had access to an excel file that contained the students names and email addresses in columns 0 and 1. This made it easy to create accounts for each of them using the following python3 script to remotely add the accounts for each student (I needed to install pymysql and xlrd using pip3 and pwgen for the password generation):

import subprocess
import xlrd
import pymysql
userList = open(“mysqlList.txt”, “a”)
emailGen = open(“genEmails.txt”, “a”)
pymysql.install_as_MySQLdb()
loc = (“classlist.xlsx”)
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
#Use a mysql root access user here
mydb = pymysql.connect(host=’x.x.x.x’,
user = ‘userName’,
passwd = ‘password’)
cursor = mydb.cursor()
for x in range(0,sheet.nrows):
name = sheet.cell_value(x,1)
emailGen.write(“{0}\n”.format(name))
name = name.split(“@”)[0]
name = name.replace(‘.’,’’)
cmd = [“pwgen -cnyB1 -r \`\\\”\\’\\\ 6 1"]
output = subprocess.Popen( cmd, stdout=subprocess.PIPE, shell=True ).communicate()[0].decode(‘utf-8’).strip()
statement = “””CREATE USER ‘{0}’@’%’ IDENTIFIED BY ‘{1}’;”””.format(name,output)
statement2 = “””GRANT ALL PRIVILEGES ON `{0}\_%` . * TO ‘{1}’@’%’ WITH GRANT OPTION;”””.format(name, name)
cursor.execute(statement)
cursor.execute(statement2)
userpass = “username:{0} password:{1}”.format(name,output)
userList.write(“{0}\n”.format(userpass))
emailGen.write(“””Hello,\n\nAccess has been generated for you at the host:x.x.x.x with the following login information:\nUsername:{0}\nPassword:{1}\n\nYou can freely create and drop databases of the form {0}_databasename .\n\nYou of course are free to keep using your local devices if you have successfully set them up.\n\nThanks,\nmyName\n\n”””.format(name,output))

Of course, a script could be created to send the emails, but I preferred to send them by hand as a last ditch effort to find any potential bugs.

Comp Sci User

Written by

A Masters Student trying to find their way through the chaos…

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade