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.

Shell

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

Shell

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

Shell

You should see a responses similar too

Shell

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.

Shell

Top Tip

Check out the official mysql documentation

You will be prompted to answer a few questions as follows

Shell

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.

Shell

We now have the file simply search for the lines

Shell

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

Shell

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.

Shell

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.

Shell

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.

Gary Woodfine

Technical Director at Denizon
Gary is Technical Director at Denizon, an independent software vendor specialising in IoT, Field Service and associated managed services,enabling customers to be efficient, productive, secure and scalable in a way which helps them address and reduce their ecological impact.

Denizon's product line successfully integrate IoT, Artificial Intelligence and Blockchain technology to enable efficient, productive, secure and scalable solutions to help organisations address increasing energy demands, ecological impact and Health & Safety concerns of their staff.

Latest posts by Gary Woodfine (see all)

Tags: