Ubuntu 20.04 - Manage MySQL 8

Result

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}';

look list user grant

SELECT user, host FROM mysql.db WHERE db = 'app_database';

look list user grant for all access

SELECT user, host, db, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv
FROM mysql.db
WHERE db = 'app_database';

grant permission user

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

grant permission Privileges with GRANT OPTION

If you want to grant all privileges to the root@localhost user on a specific database (e.g., testing), you need to ensure that the user has the GRANT OPTION privilege. You can grant privileges to root@localhost as follows:

GRANT ALL PRIVILEGES ON {name_db}.* TO 'root'@'localhost' WITH GRANT OPTION;

revoke permission user

REVOKE ALL PRIVILEGES ON app_database.* from 'app_user';

revoke permission user with grant option

REVOKE GRANT OPTION ON app_database.* from 'app_user';

grant permission 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;

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