How to Manage MariaDB databases on Ubuntu 20.04

Creating a New User and Granting Permissions

Step 1 : We’ll need to enter the MariaDB shell, which again is simply a matter of executing the mariadb command with sudo.

sudo mariadb

Step 2 : Now, we can create our new administrative user. Here’s the command to create a new user in MariaDB (replace the username and password in the command with your desired credentials):

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES;

Step 3 : We can create a set of permissions (also known as Grants) with the Grant command.

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'; 
FLUSH PRIVILEGES;

Step 4 : Now, we have an administrative user we can use to manage our server’s databases. We can use this account for managing our server instead of the root account.

To test out your new user, log out by typing:

quit

and log back in with this command in terminal:

mariadb -u admin -p

Step 5 : After entering the password, you’ll be logged in to MariaDB as admin.

Creating a database

Step 6 : Let’s create a database. At the MariaDB prompt, execute:

CREATE DATABASE new_database;

Step 7 : To list all databases on our server (and confirm our database was created properly), we can execute the following command:

SHOW DATABASES;

Step 8 : We can also list users just as easily:

SELECT HOST, USER, PLUGIN, PASSWORD FROM mysql.user;

Step 9 : The USE command allows us to select a database we want to work with.

USE new_database;

Remove user access

Step 10 : If you’d like to remove user access, you can use the following command to do so (as root)

DELETE FROM mysql.user WHERE user='myuser' AND host='localhost';

Add Comment