(Draft) Ubuntu 20.04 - Manage MySQL 8

Hope this article help to install and manage MySQL in Ubuntu.

Result

Thumbnail

This is result if mysql successfuly installed.

Step by Step


1. update package list

sudo apt update

2. install required package

sudo apt install mysql-server

3. secure the installation

sudo mysql_secure_installation

During the process, you will be prompted to:

  • Set a root password.
  • Remove anonymous users.
  • Disallow root login remotely.
  • Remove test database and access to it.
  • Reload privilege tables.

Service Check


check status service

sudo systemctl status mysql

restart service

sudo systemctl restart mysql

start service

sudo systemctl start mysql

stop service

sudo systemctl stop mysql

Creating New Role Step by Step


0. login mysql

sudo mysql -u root -p

1. create role (MYSQL 8+)

CREATE ROLE 'app_role';

u can fill app_role with valid role name.

Creating New Database Step by Step


create database

CREATE DATABASE app_database;

u can fill app_database with valid database name.

Opening MySQL customize role from prompt Step by Step :


create user

CREATE USER '{user_name}'@'%' IDENTIFIED WITH mysql_native_password BY '{password}';

grant permssion user

GRANT ALL PRIVILEGES ON {name_db}.* TO '{user_name}'@'%';

grant permssion role

GRANT ALL PRIVILEGES ON app_database.* TO 'app_role';

Then assign the role to the user:

GRANT 'app_role' TO '{user_name}'@'%';

Make sure to reload the privileges:

FLUSH PRIVILEGES;

revoke permssion role

REVOKE ALL PRIVILEGES ON app_database.* TO 'app_role';

Database management MySQL Step by Step :


show database

show databases;

use database

use {db_name};

show table

show tables;

running example query

select * from {table_name};

Allow Remote Database management MySQL Step by Step :


edit configuration

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

find this

bind-address = 127.0.0.1

then replace to

bind-address = 0.0.0.0

Allow the user to connect from a specific IP or any IP (% means any host):

allowing port mysql in firewall

sudo ufw allow 3306

restart mysql service

sudo systemctl restart mysql

Reference