Install MySQL on Ubuntu 16.04

Ubuntu 16.04 changes the MySQL install process.

Ubuntu 16.04 Bind Address Change

Changing the Bind Address to allow external (public) connections to the MySQL server is the biggest change.

The configuration file is now named mysqld.cnf. It has moved from /etc/mysql/ to /etc/mysql/mysql.conf.d/mysqld.cnf

How to Install a MySQL Database

You need root permissions to install MySQL on the Ubuntu Linux platform.

Become the Root User

For this MySQL install to function properly you MUST run it as root! Just using [sudo] is not going to cut it. “su” to root and then change to the root user’s home directory.

sudo su root
cd $pwd

Run the apt-get Command

You will be asked to enter the database root password. Keep it sensible.

apt-get --assume-yes install mysql-server

Create Database & User | Grant Permissions

After installing MySQL you need to create databases, create users and grant thosse users permissions.

login with the command

mysql -u root -p

You can also look at what you’ve got with

show databases;

How to Create a Database

Database names cannot include hyphens – sorry!

create database domain_model

How to Create a Local MySQL User

Be careful to use single quotes around

  1. the @ symbol – '@'
  2. the username and hostname (including @ symbol) – 'yourname'@'localhost'

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

View the User Table

Are you the root user? Ok – you need to use the database (schema) called mysql and run the select host,user from user; command. The command interaction logs are below.

MySQL> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
| apollo           | localhost |
| debian-sys-maint | localhost |
| root             | localhost |
| root             | warehouse |
+------------------+-----------+
6 rows in set (0.00 sec)

Grant User Access to a Database

The dot star appended to the database name is vital. The query will appear to work but the user will not be able to access the database you specified. Don’t forget the dot star.

GRANT ALL ON domain_model.* TO 'apollo'@'localhost';

Enable External MySQL Connections

If you use Ubuntu 16.04 then the file is here /etc/mysql/mysql.conf.d/mysqld.cnf
The previous location was /etc/mysql/my.conf.

You must comment out two lines to achieve external connections from IP addresses that are not either localhost or 127.0.0.1.

  • the skip external locking directive and
  • the bind address directive

The portion of my.conf should look like this.

MySQL Configuration File | /etc/mysql/my.cnf


# ############################################ ##
#
# /etc/mysql/my.conf - MySQL Configuration File
#
# ## ######################################### ##
# ## For details visit build business websites ##
# ## ######################################### ##
#
# https://www.build-business-websites.co.uk/how-to-install-mysql-on-ubuntu/
#

# skip-external-locking
# bind-address		= 127.0.0.1

After the configuration change restart MySQL with a sudo service mysql restart

Create Externally Connecting MySQL User

Your external connection attempt still won’t work. But this time it should fail with a different error. And the error is important.

"Host <<hostname>> is not allowed to connect to this MySQL server"

Use the <<hostname>> part to create a user as above and the external mysql connection succeeds. Well done you!

Leave a Reply

Your email address will not be published. Required fields are marked *