How to install MySQL server on Ubuntu

MySQL with disk background

MySQL is one of the most popular Open Source Relational DataBase Management System, RDBMS for short. MySQL is widely spread across the world, it is employed in enterprise environments as well as small hobbyist sites. Surprisingly, it is dead-simple to set it up and start using it on Ubuntu, let’s see how.

Requirements (before you start)

Important
I take absolutely NO responsibility of what you do with your machine; use this tutorial as a guide and remember you can possibly cause data loss if you touch things carelessly.

Databases workloads are among the most demanding ones. But that’s not true when you have a handful of small databases, and you do not perform many queries (operations). As a matter of fact you can install and get MySQL working on even the smallest 1GB RAM cloud instances without any issues.

Although mechanical hard drives have been used for databases for a long time, having a Solid State Drive holding your database will significantly improve your database performance.

You will need a running instance of Ubuntu to follow this tutorial, if you’re going to use MySQL server as a stable database (for a long time) I suggest you use a LTS version of Ubuntu such as Xenial Xerus or Bionic Beaver.

If everything is in order, let’s start.

Installing

MySQL has been available by default in Ubuntu repositories for a while so it’s quite easy to install, fire up your terminal and follow the steps:

Ubuntu > 16.04Ubuntu <= 16.04
You won’t be asked to set up a root password, the default one will be blank. That is very insecure! To set one up go to Securing MySQL.

During the installation you will be asked to set up a root password, pick a strong one.

That’s pretty much it, your database management system is already up and running, you can learn how to manage it, secure it as well as common operations in the following paragraphs.

Managing MySQL

Starting MySQL

Ubuntu > 16.04Ubuntu <= 16.04

Stopping MySQL

Ubuntu >= 16.04Ubuntu < 16.04

Starting MySQL at boot

Ubuntu > 16.04Ubuntu <= 16.04

The service is enabled by default.

Securing MySQL

Ubuntu (any version)
Please notice the output in the command is heavily edited, but it will ask you the same questions. In this way you can set up your root password and secure your MySQL instance.

Accessing MySQL shell

Ubuntu (any version)
Change USER according to the user name of the database user. The prompt will ask for a password.

You can get a list of commands by issuing:

You can exit using the exit command:

Common operations

Create a Database

MySQL Shell

Drop a Database (delete)

MySQL Shell

Create a User

MySQL Shell

Change the command according to your needs.

Drop a User (delete)

MySQL Shell

Granting privileges

MySQL Shell
Of course you may not want to grant ALL PRIVILEGES to a particular user, instead you may want to let the user create a table, or insert data. You can replace ALL PRIVILEGES with the desired list of privileges, you can find a complete list here. After any privilege modification you should always reload privileges to make sure they are enforced immediately.

Revoking privileges

MySQL Shell
You can replace ALL with the desired list of privileges you want to revoke, you can find a complete list here. After any privilege modification you should always reload privileges to make sure they are enforced immediately.

Reloading privileges

After any privilege modification you should always reload the privileges to make sure they are enforced immediately.

MySQL Shell
Image courtesy of mark | marksei

The following two tabs change content below.
The IT guy with a little boredom look in his eyes, fond of computers since forever he now works as a freelancer in the IT and shares his experiences through this blog.

You may also like...