How to install MySQL server ubuntu 18.04

MySQL isÂ an open source SQL relational database management system that's developed and supported by Oracle. For many open source developers it is the Go to relational database management system.

In my particular case, I am Installing Team City On Ubuntu Server and want to use MySQL to store build history, users, build results and some run time data for my Internal CI/CD server. I therefore needed to install MySQL server on one of VM Servers.

I make use of ProxMox Open-Source Virtualization Platform and have server in my home office to host a number of Virtual Servers. These servers are not directly accessible from the internet and are primarily used for testing, POC and staging environments before I deploy to Cloud Providers like AWS, Azure or GCP which are fairly popular with my clients.

Installing and configuring MySQL is a fairly straightforwrd process but I did ht one or two snags that I thought I document for future reference.

Install MySQL using default Ubuntu Repository

The default Ubuntu repository currently only contains version 5,7.x of MySQL but for my particular use case this will suffice. So this guide will only guide you through installing MySQL 5.7.

To install MySQL i simply connected to the server I created using ssh and ran the following command.

sudo apt-get install mysql-server

This will install MySQL server, and by default you will be able to access it using the root account of the server you're installing it on. The installation process will no longer prompt you to configure a root password.

Once the installation is complete I test that I can access the mySQL server by attempting to login. So for instance, you have created a root account on your server, using your name. You can simply login ensuring you use the sudo command

sudo mysql -u gary -p

If you are able to successfully login the simply test that your user can access the databases by simply using the SHOW DATABASES command

SHOW DATABASES;

You should see a responses similar too

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Then this confirms the installation worked as expected. Exit out of the mysql using the exit command.

We can now configure the security of the mysql server.

Configure the MySQL security

MySQL ships with a handy security utility to help implement security recommendations.

The mysql_secure_installation program enables you to improve the security of your MySQL installation in the following ways:

You can set a password for root accounts.
You can remove root accounts that are accessible from outside the local host.
You can remove anonymous-user accounts.
You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

To execute the application is as simple as running the command below and answering a few questions.

sudo mysql_secure_installation

Top Tip

Check out the official mysql documentation

You will be prompted to answer a few questions as follows

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

We have now completed all the rudimentary security precautions for our MySQL server.

Bind MySQL server to IP Adress

We will now need to Bind our MySQL to the IP address you it is running on. To do so we need to edit a configuration file and to do so we will use nano a light weight text editor that is shipped with Ubuntu.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

We now have the file simply search for the lines

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

Change the IP address to whatever the IP address of your server is. i.e 192.168.0.2 the simply exit and save the file using CTRL + X

We now have to restart the MySQL daemon for the change to take effect

sudo systemctl restart mysql

Create User Account

We don't want o access our MySQL server using our root account remotely and in fact we have just disabled doing so in the steps above. So we will need to create some user accounts that we will use.

# username and password are just placeholders and you should use your actual username you want to create and the password
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

#  Give user all administration 
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

# To enable remote login for the user use % which signifies access from any machine.
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

Information

Specify the userâ€™s host asÂ localhostÂ and not the serverâ€™s IP address.

localhostÂ is a hostname which means â€œthis computer,â€ and MySQL treats this particular hostname specially: when a user with that host logs into MySQL it will attempt to connect to the local server by using a Unix socket file.

localhostÂ is typically used when you plan to connect by SSH or when youâ€™re running the localÂ mysqlÂ client to connect to the local MySQL server.

To ensure all the commands take effect you will need to Flush the privileges.

FLUSH PRIVILEGES;

Connect to your MySQL server remotely

You should be able to connect to your MySQL server with the new Username your created remotely. So using the Client application of your choice connect.

In my case I make use of Jetbrains DataGrip so I simply configure the connection as follows

Summary

It is a fairly simple task to install MySQL on Ubuntu, but there are some additional steps to ensure you are able to securely connect to the mySQL remotely.

About
Latest Posts

Gary Woodfine

Technical Director at threenine.co.uk

Gary is Technical Director at threenine.co.uk, an independent software vendor specialising in IoT, Field Service and associated managed services,enabling customers to be efficient, productive, secure and scale-able.