How To Install MySQL On Ubuntu

In this blog post , We have explained how to install MySQL on Ubuntu operating systems.

We will also learn to configure MySQL and connect to MySQL.

What Is MySQL?

MySQL is an open source database management system.

It is a relational database management system based on Structured query Language.

It is used for wide range of purposes such as Application logging , data warehousing , e-commerce.

Its most commonly used as a part of LAMP (Linux , Apache , MySQL , PHP ) Stack.

Installing MySQL On Ubuntu

By default , Ubuntu package manager (APT repository) has the latest version of MySQL included in it.

We can make use of this repository to install MySQL on the Ubuntu system.

First We need to update the APT package repository ,

sudo apt-get update

We can install the mysql packages using the below command,

sudo apt-get install mysql-server

Once the MySQL packages are installed , We need to configure the MySQL as the above installation is not secure.

Starting MySQL Service

Once the packages are installed , we need to start the MySQL service using the systemd unit file which was created by default post installation.

With the help of systemd service file we can easily manage the MySQL service.

The systemd service file is created under /lib/systemd/system/ directory and configurations are stored in mysql.service file.

sudo cat /lib/systemd/system/mysql.service

To start the MySQL Service,

sudo systemctl start mysql

To check the status of the MySQL,

sudo systemctl status mysql

To enable the service to start on system boot up,

sudo systemctl enable mysql

To restart the MySQL service , run the below command.

sudo systemctl restart mysql

To stop the MySQL service , Execute the below command.

sudo systemctl stop mysql

To disable MySQL to start on the system boot up,

sudo systemctl disable mysql

Configuring MySQL

After the MySQL installation , We will be provided with a script using which we can configure MySQL settings such as root password , removing remote access and sample users.

Run the below script to start the process,

sudo mysql_secure_installation

It will connect to MySQL using a blank password , To setup a password It asks us to setup a VALID PASSWORD PLUGIN , Type yes and hit Enter

From the levels of password validation policy , Type 2 (STRONG) and hit Enter

Now we need to enter the password for the root user which should contain number , Uppercase and Lower case letters and Special characters.

Do you wish to proceed with the password provided ? , Type yes and hit Enter

By default , MySQL installation has an anonymous user , which allows anyone to login to mySQL without having username and password.

We should remove them for the security purposes.

Remove anonymous users? Type yes and hit Enter

Root user should be allowed only to connect from localhost , hence we need to disable the remote login for the root user.

Disallow root login remotely? , Type yes and hit Enter

By default , MySQL comes with database named test which anyone can access it.We should remove this database as well and also the privileges on the test database.

Remove test database and access to it? Type yes and hit Enter

We need to reload the privileges table for all the above changes to take effect.

Reload privilege tables now? Type yes and hit Enter

We have successfully installed and configured MySQL on Ubuntu system.

To check the version of the MySQL installed, Run the below command.

sudo mysql --version

Accessing MySQL Database

Now , We have completed all the required steps , Now Its time to access the MySQL database with the root username and password.

Type the below command to login as root user,

sudo mysql -uroot -p

It asks for the password , Provide the password and hit Enter.

We have successfully logged in to MySQL database as root user.

To list all the databases, Execute this command,

show databases;

To list all the users in the MySQL database , Use the below command.

select user from mysql.user;

Using the below command , Create a database.

Lets create a database named testdb.

create database testdb;

Run the below command , To connect to testdb database,

use testdb;

To list all the tables in the database , Run the below command.

For testing , I have connected to mysql database and i am listing all the tables there.

use mysql;

show tables;

To create a user , Run the below command.We should also follow the same password policy that we have configured while setting up MySQL.

create user 'testuser'@'localhost' identified by 'Testuser&93';

To grant read only access to a database for a user,

The below command , Will grant read access to all the tables in that particular database.

grant select on testdb.* to 'testuser'@'localhost';

To grant read access only for particular table in a database,

grant select on testdb.tablename to 'testuser'@'localhost';

To check the permissions granted for a user,

show grants for 'testuser'@'localhost';

To grant full access to a database for a user,

grant all on testdb.* to 'testuser'@'localhost';

To remove access from a database for a user,

revoke select on testdb.* from 'testuser'@'localhost';

revoke all on testdb.* from 'testuser'@'localhost';

Run the below command to delete a database,

drop database testdb;

Run the below command to delete a user,

drop user 'testuser'@'localhost';

To exit from the MySQL datbabase , Type exit and hit Enter.

Conclusion

We have learnt to install and configure MySQL on Ubuntu.

Also Now we should have a good understanding on managing databases , users and granting and revoking permissions for users.

Hope you find it helpful.Please do check out my other articles.