MySQL is an open-source and most popular database management system. MySQL installation on Ubuntu is very easy and it's commonly being a part of the popular LAMP and LEMP stacks.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related MySQL queries.

In this context, we shall look into how to install MySQL on Ubuntu 18.04 Bionic Beaver systems.

How to Install MySQL on Ubuntu?

To begin, ensure that you access the system with a non-root user with sudo privileges. To remote a system use SSH in Linux and in Windows you can use Putty application to make SSH connection.

Then, once logged in, follow the steps given below.

1. Install MySQL on Ubuntu

Before start installation you need to update the package index on your system. 

i. To update the system, Execute the following command :

$ sudo apt update

ii. Then, Run the below commands to start MySQL installation:

$ sudo apt install mysql-server

It will ask you to set MySQL root user password. On completion of installation, the MySQL service will start automatically. 

iii. Also, you can check whether the MySQL server is running, type below command:

$ sudo systemctl status mysql

● mysql.service - MySQL Community Server
    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
    Active: active (running) since Thu 2019-03-21 18:22:17 IST; 2min 1s ago
  Main PID: 6260 (mysqld)
    CGroup: /system.slice/mysql.service
            └─6260 /usr/sbin/mysqld

2. Securing MySQL & Further Configuration

It's recommended to run included security script. By this script it will make changes in default options like remote root logins and sample users and test database. Run the following security script:

$ sudo mysql_secure_installation

Once you execute this command it will prompt you to enter root user password which you set before this step. 

First of all it will ask you to choose whether to use the VALIDATE PASSWORD plugin or not, which can be used to test the strength of your MySQL 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?
Press y|Y for Yes, any other key for No:

Press Y and hit Enter to go ahead.

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: 1

After that, it will ask you for select a level of password validation. Make sure here if you have select 2 for for the strongest level then it will not allow password without numbers, upper and lowercase letters, and special characters. So here you should select 1 for medium level and hit Enter.

Next, It will ask if you want to change that password. If your current password is perfect then, enter N for “no” at the prompt:

Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

Now, for all next questions press Y and hit Enter key for each. This will remove some anonymous users and test database, disable remote root logins and load these new rules so that MySQL immediately respects the changes you have made. Following questions will be prompted and you have to press yes/y for all :

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

You can login to mysql using following command as root user :

$ sudo mysql -uroot -p

Enter you mysql root user password and it seems you logged in.

All done. Finally, you successfully installed MySQL on your system.

How to Allow remote access to MySQL ?

If you have iptables enabled and want to connect to the MySQL database from another machine, you must open a port in your server's firewall (the default port is 3306). You don't need to do this if the application that uses MySQL is running on the same server.

Run the following command to allow remote access to the mysql server:

$ sudo ufw enable
$ sudo ufw allow mysql

How to Start the MySQL service ?

After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:

$ sudo systemctl start mysql

How to Launch MySQL at reboot ?

To ensure that the database server launches after a reboot, run the following command:

$ sudo systemctl enable mysql

How to Configure interfaces for MySQL?

MySQL, by default is no longer bound to ( listening on ) any remotely accessible interfaces. 

i. Edit the "bind-address" directive in /etc/mysql/mysql.conf.d/mysqld.cnf:

bind-address		= 127.0.0.1 ( The default. )
bind-address		= XXX.XXX.XXX.XXX ( The ip address of your Public Net interface. )
bind-address		= ZZZ.ZZZ.ZZZ.ZZZ ( The ip address of your Service Net interface. )
bind-address		= 0.0.0.0 ( All ip addresses. )

ii. Restart the mysql service.

$ sudo systemctl restart mysql

How to Start the mysql shell ?

There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach, the mysql shell.

i. At the command prompt, run the following command to launch the mysql shell and enter it as the root user:

/usr/bin/mysql -u root -p

ii. When you're prompted for a password, enter the one that you set at installation time, or if you haven't set one, press Enter to submit no password.

The following mysql shell prompt should appear:

mysql>

How to Set the root password for MySQL ?

If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.

i. For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:

UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';

ii. For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing password with your new password:

UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root';

iii. To make the change take effect, reload the stored user information with the following command:

FLUSH PRIVILEGES;

Note: We're using all-caps for SQL commands. If you type those commands in lowercase, they'll work. By convention, the commands are written in all-caps to make them stand out from field names and other data that's being manipulated.

How to View users ?

MySQL stores the user information in its own database. The name of the database is mysql. Inside that database the user information is in a table, a dataset, named user. 

If you want to see what users are set up in the MySQL user table, run the following command:

SELECT User, Host, authentication_string FROM mysql.user;

The following list describes the parts of that command:

i. SELECT tells MySQL that you are asking for data.

ii. User, Host, authentication_string tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case, you are looking for the username, the host associated with the username, and the encrypted password entry.

iii. FROM mysql.user " tells MySQL to get the data from the mysql database and the user table.

iv. A semicolon (;) ends the command.

Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.

How to Create a database ?

There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.

Some applications create a database as part of their setup process, but others require you to create a database yourself and tell the application about it.

To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:

CREATE DATABASE demodb;

After the database is created, you can verify its creation by running a query to list all databases. The following example shows the query and example output:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demodb             |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

How to Add a database user ?

When applications connect to the database using the root user, they usually have more privileges than they need. You can add users that applications can use to connect to the new database. In the following example, a user named demouser is created.

To create a new user, run the following command in the mysql shell:

INSERT INTO mysql.user (User,Host,authentication_string,ssl_cipher,x509_issuer,x509_subject)
VALUES('demouser','localhost',PASSWORD('demopassword'),'','','');

When you make changes to the user table in the mysql database, tell MySQL to read the changes by flushing the privileges, as follows:

FLUSH PRIVILEGES;

Verify that the user was created by running a SELECT query again:

SELECT User, Host, authentication_string FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | Password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *756FEC25AC0E1823C9838EE1A9A6730A20ACDA21 |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| debian-sys-maint | localhost | *27E7CA2445405AB10C656AFD0F86AF76CCC57692 |
| demouser         | localhost | *0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6 |
+------------------+-----------+-------------------------------------------+

How to Grant database user permissions ?

Right after you create a new user, it has no privileges. The user can log in, but can’t be used to make any database changes.

Give the user full permissions for your new database by running the following command:

GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;

Flush the privileges to make the change official by running the following command:

FLUSH PRIVILEGES;

To verify that those privileges are set, run the following command:

SHOW GRANTS FOR 'demouser'@'localhost';
2 rows in set (0.00 sec)

MySQL returns the commands needed to reproduce that user’s permissions if you were to rebuild the server. USAGE on \*.\* means the users gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.

+-----------------------------------------------------------------------------------------------------------------+
| Grants for demouser@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |
| GRANT ALL PRIVILEGES ON `demodb`.* TO 'demouser'@'localhost'                                                    |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[Need urgent assistance in setting up MySQL on any Linux Distribution ? We can help you. ]