List of available users

SELECT User, Host FROM mysql.user;

Show privileges of an account

show grants for exampleuser@'localhost';

Contoh:

mysql> show grants for epintaruser@localhost;

+------------------------------------------------------------------+

| Grants for epintaruser@localhost                                 |

+------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `epintaruser`@`localhost`                  |

| GRANT ALL PRIVILEGES ON `epintar`.* TO `epintaruser`@`localhost` |

+------------------------------------------------------------------+

2 rows in set (0.00 sec)

Alter user password

ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

Contoh:

mysql> alter user 'moodle_user'@'%' identified by 'p4sswordku';

Query OK, 0 rows affected (0.00 sec)



mysql>

Grant privileges of a database to a user

Syntax:

GRANT <privileges> ON <database>.<object> TO '<user>'@'<host>';

Sebelumnya harus sudah ada terlebih dahulu proses pembuatan user dengan host yang sama

Contoh:

mysql> GRANT ALL PRIVILEGES ON digitoskelasku.* TO 'moodle_user'@'%';
Query OK, 0 rows affected (0.00 sec)

Create alternate Super Admin Users

Using root account for daily activities is not recommended, so we need to have other user account that has superadmin privileges

mysql> create user admin@localhost identified by '@dminpassw0rd';
mysql> grant all privileges on *.*  to admin@localhost;

List of databases size

Show size in MB and GB

SELECT 
    table_schema AS `Database`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size_MB`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 3) AS `Size_GB`
FROM 
    information_schema.tables
GROUP BY 
    table_schema
ORDER BY 
    `Size_GB` DESC;

Show a database in size MB

SELECT 
    table_schema AS `Database`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size_MB`
FROM 
    information_schema.tables
WHERE 
    table_schema = 'your_database_name'
GROUP BY 
    table_schema;