Install and configure MySQL on Ubuntu 18.04

MySQL is an open-source database engine, suitable for small to large sites.

This article walks you through a basic installation of MySQL on an Ubuntu 18.04 LTS server.

Prerequisites

You need access to a Linux server, with sudoers or root privileges. Adding sudo to your account is outside the scope of this tutorial.

Installation

Update package information

Update your package information before you get started. For many of these Linux commands, you will receive multiple screens of output that this guide will shorten to ‘‘, but here are the relevant messages to look out for:

sudo apt-get update

...
Fetched 9981 kB in 2s (4052 kB/s)
Reading package lists... Done

Install MySQL

sudo apt install mysql-server -y

...
Setting up mysql-server (5.7.28-0ubuntu0.18.04.4) ...
...

In our example, Ubuntu installed MySQL 5.7 by default.

Verify installation

To verify that MySQL is installed and started on your system, run:

sudo systemctl status mysql.service

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Fri 2019-12-13 19:06:10 UTC; 2min 26s ago
 Main PID: 2742 (mysqld)
    Tasks: 28 (limit: 2354)
   CGroup: /system.slice/mysql.service
           └─2742 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Dec 13 19:06:09 derek-mysql-ubuntu systemd[1]: Starting MySQL Community Server...
Dec 13 19:06:10 derek-mysql-ubuntu systemd[1]: Started MySQL Community Server.

You should see Active: active (running) as in the example above. If you do not, run:

sudo systemctl start mysql.service

Enter the MySQL shell to ensure that you have access. Then type exit to exit the shell.

mysql

mysql> exit
Bye

Now you can reach your MySQL from another remote host by using:

mysql -h host_name -u user -p

Run the Secure Installation Tool (Optional)

Finally, we recommend that you run MySQL Server’s built-in secure installation tool. This will walk you through several steps intended to close some known vulnerabilities with the default MySQL installation, and will prompt you on which changes it should make at every step:

root@real-lynx-42:~# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

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?

...

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

All done!

Conclusion

In this article, you learned how to install MySQL on Ubuntu 18.04 LTS. The next steps should include configuring your firewall to properly allow only the needed traffic to your MySQL server instance and configuring users with the correct permissions for your needs.

MySQL Basics

What is a database? 

A database is a program that allows you to store data. Each flavor of database has specific methods for creating, updating, deleting, and accessing the data. In addition, searches can be performed.

When discussing MySQL, we’re talking about a Relational Database Management System, or RDBMS. We use these systems to store and manage enormous volumes of data. The term relational refers to the way the data is stored and indexed. Data is stored in different tables, and then, using either Primary or Foreign Keys, related to other tables.

Database elements

A table is simply a collection of specific data, made up of rows and columns. If you can visualize how a spreadsheet looks, you can visualize a table.

Columns are for storing similar types of data, for example, one row of data might have one zip (or postal) code, but this would be stored in a specific column, so that each row could have the same type of data. 

A row (or record) makes up a single group of related data. Think about storing an address in a database. You’d have columns in your row for street address, apartment number, city, state and zip. You might have other fields (or columns) depending on whether or not your application is expected to support different countries. 

Together these columns, rows and tables form the database. You might have a separate table for people and one for addresses… you could list people in one table, and link (relate) them to an address in another table.

A well built MySQL database will be bound together by indexes. Indexing the data allows searches to happen much more quickly than without indexes.

Adding a user account

While users can be added through the control panel for some database services, this is a good example of utilizing your new database. 

Step 1. Log into MySQL using your mysql client, see Connecting above. 

Step 2. Use the mysql database, where users are stored.

mysql> use mysql;
Database changed

Step 3. Insert a user.

mysql> INSERT INTO user (
  host,
  user,
  password,
  select_priv,
  insert_priv,
  update_priv) VALUES (
  'your-hostname',
  'new-test-user',
  PASSWORD('battery-correct-horse-staple'),
  'Y',
  'Y,
  'Y'
);
Query OK, 1 row affected (0.04 sec)

Step 4. Flush privileges to activate the user. 

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.02 sec)

Step 5. Select the user’s record.

mysql> SELECT host, user, password
    FROM user
    WHERE user = 'new-test-user';
+---------------------|-------------------|------------------------+
|         host        |       user        |        password        |
+---------------------|-------------------|------------------------+
| your-hostname       | new-test-user     | (password hash)        |
+---------------------|-------------------|------------------------+
1 row in set (0.01 sec)

Using the PASSWORD function forces MySQL to encrypt the stored password. What you will see in the SELECT display is the user’s hashed password, the encrypted value, not your original password. 

There are also other privileges that can be granted to each user, see the official MySQL documentation for a list of those. 

MySQL Commands

This section will walk you through a list of important MySQL commands you will use periodically. 

List the databases currently available:

SHOW DATABASES;

Select the database to act upon with any follow up commands:

USE databasename;

Lists the tables inside the currently selected database:

SHOW TABLES;

Show the details of the named table:

SHOW COLUMNS FROM tablename;

Shows the details of the indexes on the named table, including the Primary Key details:

SHOW INDEX FROM tablename;

Creating a table

In order to create a table, you will have to specify at a minimum the table’s name, the name of each field (column) and the field’s type.

Here’s the basic syntax:

CREATE TABLE tablename (columnname columntype);

And here’s a simple example:

mysql> USE databasename;
Database changed
mysql> CREATE TABLE addresses (
    address_id INT NOT NULL AUTO_INCREMENT,
    address_street VARCHAR(100),
    address_apt VARCHAR(10),
    address_city VARCHAR(25),
    address_state VARCHAR(2),
    address_zip INT,
    address_entrydate DATE,
    PRIMARY_KEY (address_id)
);
Query OK, 0 rows affected (0.12 sec)

Setting a primary key enables indexing to work efficiently.

Setting AUTO_INCREMENT tells MySQL to pick the next available number for the address_id field, and using NOT NULL forces that field to have something in it, in this case the id number.

Deleting a table

Deleting tables is even easier. Just remember that data removed is lost permanently and cannot be recovered. 

mysql> USE databasename;
Database changed
mysql> DROP TABLE addresses;
Query OK, 0 rows affected (0.63 sec)

There’s more to learn about using MySQL, but these commands should get you started on your journey!