Managing MariaDB Databases (Ubuntu Server)

In this guide, you'll learn how to manage MariaDB databases in your terminal, from connecting to the database server using the mariadb command, creating a database, removing (drop) database, and managing users and permission.

If you don't already have mariadb installed, visit the below guide

https://devsrealm.com/cloud-computing/ubuntu/installing-mariadb-ubuntu/

By default, mariadb already has the root account created as an administrative user, so, we would create a new account to make it separate from the root account, this way, others won't be able to make use of the root account.

Note: The users created for your mariadb database is separate from the user accounts on your actual server, so, don't get it twisted ;)

Creating and Manging New User For MariaDB

To create a new user, you'll need to enter the mariadb shell using the following command:

sudo mariadb

This would connect as root, the root is assumed if you are attempting to access MariaDB with sudo. Alternatively, you can use the following command as a normal user to switch to root, without using sudo (skip this step if you are logged in):

mariadb -u root -p

The above command might fail even if you enter the correct password, and the reason for the failure is because by default the root user is set up to use a completely different mode of authentication altogether (UNIX auth_socket).

You can fix this by using  sudo mariadb or  sudo mariadb -u root -p which is my preferred method or you can fix this using the native password authentication method.

To do this, login to mariadb using sudo mariadb and then enter the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

Stop and Start mariadb:

systemctl start mariadb
systemctl stop mariadb

Once you are logged in, you'll be presented with the following:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

To create a new administrative user, use the following command (Replace the admin and password with your preferred one.):

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

To create user 'iamsecured' and password 'powerful', I'll do:

CREATE USER 'iamsecured'@'localhost' IDENTIFIED BY 'powerful';
FLUSH PRIVILEGES;

So, I am creating user iamsecured and restricting it to localhost as I don't want it open the the wild(you can't connect remotely). The FLUSH PRIVILEGES; reload MariaDB privilege information, this should be run every time you add a user or modify permission.

Creating a User and Allowing Connection From Anywhere:

The previous command only allows it to connect from localhost, which means you'll first need to log in to the server before logging in to the mariadb with the user account. So, to allow a remote login from anywhere, you can use the following command:

CREATE USER 'admin'@'%' IDENTIFIED BY 'password';

This is creating user admin, and the percent symbol means you can log in from anywhere, which isn't advisable.

Creating a User and Allowing Connection From A Particular Network:

If you want to allow a database administrator access to the server remotely, you can use the following format:

CREATE USER 'admin'@'192.168.1.%' IDENTIF

The % is a wildcard, which means it would match any number that follows the IP address.

Granting User Permissions

We can create a set of permission with the Grant command, so, if I want to give admin user full access to the DB server when
called from localhost, I'll do:

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

Now, you can use this user to manage server's database. Any logged on GNU/Linux user will be able to access the database server and manage it, provided they know the password.

Accessing the Newly Created User

Use the following command to access the newly created user, replace admin with the user you created:

mariadb -u admin -p

After entering the password, you'll be logged it with admin or your chosen user.

If you want to log in without the prompt, use the following command:

mariadb -u admin -p<password>

There is no space between the -p option and the actual password, so, say I want to use password 'Iamstring', I'll do:

mariadb -u admin -pIamstrong

The downside of this method is that any command you type would be saved in history, so anyone can view your command history and see the password in plain text.

You can create and manage database with the new user, but note that the admin account can't manage user, so, you'll still need to log in as root to manage user permissions.

Creating a Read-only For MariaDB User

You probably don't want everyone to make changes to your database server, which is where the read-only is useful, your data would only be read, use the following command to execute that:

GRANT SELECT ON *.* TO 'readonlyuser'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

You want to make sure the user you are putting on read-only is already created.

Creating and Managing a Database in Mariadb

Creating a database is super easy, for example, to create database cars, I'll do:

CREATE DATABASE cars;

You should now have a database on your server named car, to list all database on your server, use the following command:

SHOW DATABASES;

Here is an image for illustration:

The above output would show some databases created for you by mariadb, and your newly created one.

Listing All Users

To list all users, you use:

SELECT HOST, USER, PASSWORD FROM mysql.user;

Output

MariaDB [(none)]> SELECT HOST, USER, PASSWORD FROM mysql.user;
+-----------+--------+-------------------------------------------+
| HOST      | USER   | PASSWORD                                  |
+-----------+--------+-------------------------------------------+
| localhost | root   |                                           |
| localhost | admin  | *8BB874FBC1BE0D620B5BE546BE235A5308E6737C |
+-----------+--------+-------------------------------------------+

When installing an application that needs it own database, e.g nextcloud, you'll create the database, a user for that database, and then a user permission to only that database, this way the user won't have access to other databases.

To create a user, password and a read-only access with one command, you use:

Note: You'll need to log in as root to do this, as other admin users don't have the privilege to manage users

GRANT SELECT ON cars.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';

This would create the user, password and also give it a SELECT permissions on the cars database, which is same as read-only.

To give user a full access to the database, you can use the following:

GRANT ALL ON cars.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';

To show the grants for a particular user, you can use:

SHOW GRANTS FOR 'dbuser'@'localhost';

Output:

MariaDB [(none)]> SHOW GRANTS FOR 'dbuser'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for dbuser@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser'@'localhost' IDENTIFIED BY PASSWORD '*8BB874FBC1BE0D620B5BE546BE235A5308E6737C' |
| GRANT ALL ON `cars`.* TO 'dbuser'@'localhost'                                                              |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

As you can see, dbuser has full access only to the cars database, you should provide full access to a database if it necessary.

You can also provide additional permissions such as:

• DELETE (whether or not the user has access to delete rows from database tables)
• CREATE (whether the user can add rows to the database)
• INSERT (controls whether or not the user can add new rows to a table)
• SELECT (allows the user to read information from the database)
• DROP (allows the user to fully remove a database)
• ALL (grant all permission)

You can read the documentation of mariadb for more permissions, the type of permission you'll grant is dependable on the software, which you can look up in the software doc.

Let's go over some of them.

Remove User Access (DELETE):

To remove user access, you can use the following command to do that(replace dbuser with the user you wish to remove):

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

Adding Tables and Rows In Database

A database is useless with no data, so, let's add some data.

USE cars;

The USE command is self explanatory, it allows us to select a database we want to work with, in my case, I want to work with the cars database.

Once you've executed the command, it will change MariaDB [(none)]>to MariaDB [cars]>

This is a very useful feature as it indicated the database you are currently working with, so, anything you are doing would only affect this database.

Creating a Table:

Now, let's CREATE a table in our cars database, I'll call the table Information:

CREATE TABLE Information (Name char(15), Model char(15), Year int(4));

I have created Table information that has three columns (Name, Model, and Year). The charand intis an example of data type. The data type is simply a type of data you want the table fields to be given, each column in a database table must be assigned a type.

In my case, I gave the name and model field a char data type which accepts both text string and number.

There is a limit to what you can store in a field using char data type, you can't store more than 256 bytes, which is more than enough for our use case, I gave the name and model char (15), which means it cannot hold up more than 15 characters.

I gave the year field an integer data type with a display width of 4, this doesn't define the size of the integer to be stored in fact I can store number larger than 50000 on it, and it would still accept it. You can as well replace the int (4) with just INT

To verify the columns in the database, use the following command:

SHOW COLUMNS IN Information;

Output

MariaDB [cars]> SHOW COLUMNS IN Information;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Name  | char(15) | YES  |     | NULL    |       |
| Model | char(15) | YES  |     | NULL    |       |
| Year  | int(4)   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Adding Data To The Table

To add new data to this table, you can use the INSERT command as follows:

INSERT INTO Information VALUES ('Rolls Royce', 'Phantom', '2005');

The above command adds a new car name, and its model into the Information table. When we use INSERT, we insert all the data of the columns, we have a car named 'Rolls Royce', which model is 'Phantom' of year '2005'

You can add more using the INSERT as done above, e.g I can add Toyota, Range Rover, and the likes.

Use the following command to show all the data for the table:

SELECT * FROM Information;

Output

MariaDB [cars]> SELECT * FROM Information;
+-------------+---------------+------+
| Name        | Model         | Year |
+-------------+---------------+------+
| Rolls Royce | Phantom       | 2005 |
| SUV         | Mahindra Thar | 2020 |
+-------------+---------------+------+
2 rows in set (0.00 sec)

To remove an entry, use the following command:

DELETE FROM Information WHERE Name = 'Rolls Royce';

This is self explantory, we are using the DELETE FROM command, giving the name of the table to delete from (Information, in this case), and then using WHERE to provide what we want to remove exactly.

Deleting (DROP) a Databae

To delete a database, we would use the DROPcommand, this allows us to delete a table or an entire database.

To delete the table informationI just created, I'll do the following:

DROP TABLE Information;

To delete an entire database:

DROP DATABASE cars;

Backing Up and Restoring a Database

To backup a database, you can do the following (make sure you are logged out of the mariadb shell, and instead use the GNU/Linux shell for the below command):

mysqldump -u admin -p --databases cars> cars.sql;

It would prompt you for the user password, and mysqldump would create a copy of the cars database, and store it in a file named cars.sql. The file would be stored in your working directory, so, you might as well create a special folder for that in your GNU/Linux server.

Use the following to make a single database backup:

mysqldump -u username -p --single-transaction --quick --lock-tables=false databasename > databasename.sql;

Use the following command to restore a a backup file:

sudo mariadb < cars.sql

Use the following command to restore a backup file:

mysql -u user -p [databasename] < database-backup.sql

Here you go, there are more things to learn on mariadb, but this would get you started!!!