How to Manage Databases in MariaDB on Ubuntu 18.04

MariaDB is free, open-source, and one of the most popular database servers in the world. It is a community-developed fork of the very popular MySQL database management system. MariaDB provides support for PHP and uses a standard and popular querying language. MariaDB comes with a wide range of storage engines and several optimizations that can be used to improve the database performance.

In this tutorial, we will show you how to manage databases in MariaDB on Ubuntu 18.04.

Step 1 – Install MariaDB

By default, the latest version of MariaDB is not available in the Ubuntu 18.04 default repository. Therefore, it is recommended to install MariaDB from the MariaDB repository.

First, download and add the MariaDB GPG key to your system with the following command:

apt-get update -y 
apt-get upgrade -y
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

Next, add the MariaDB repository using the following command:

add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://ftp.utexas.edu/mariadb/repo/10.3/ubuntu bionic main'

Next, update the repository and install MariaDB using the following commands:

apt-get update -y
apt-get install mariadb-server -y

After installing MariaDB, check the status of MariaDB with the following command:

systemctl status mariadb

You should see the following output:

● mariadb.service - MariaDB 10.3.19 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Wed 2019-11-06 04:25:01 UTC; 24s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 4538 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 32 (limit: 1114)
CGroup: /system.slice/mariadb.service
└─4538 /usr/sbin/mysqld

Nov 06 04:25:03 ubuntu1804 /etc/mysql/debian-start[4584]: Phase 6/7: Checking and upgrading tables
Nov 06 04:25:03 ubuntu1804 /etc/mysql/debian-start[4584]: Running 'mysqlcheck' with connection arguments: --port='3306' --socket='/var/run/mysq
Nov 06 04:25:03 ubuntu1804 /etc/mysql/debian-start[4584]: # Connecting to localhost...
Nov 06 04:25:03 ubuntu1804 /etc/mysql/debian-start[4584]: # Disconnecting from localhost...
Nov 06 04:25:03 ubuntu1804 /etc/mysql/debian-start[4584]: Processing databases
Nov 06 04:25:03 ubuntu1804 /etc/mysql/debian-start[4584]: information_schema
Nov 06 04:25:03 ubuntu1804 /etc/mysql/debian-start[4584]: performance_schema

Step 2 – Create a New Database

To create a new database, first log in to MariaDB shell with the following command:

mysql -u root -p

Enter your root password when prompted, then create a database with the following command:

CREATE DATABASE db1;

After creating a new database, you can list all databases with the following command:

SHOW DATABASES;

You should see the following output:

+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.002 sec)

Step 3 – Create a New User Account

You can create a new MariaDB user account by running the following command:

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'yourpassword';

In this line, the variables are defined as follows:

dbuser: The name of the MariaDB user.

localhost: This option specifies that the user can connect to the MariaDB server only from the localhost.

yourpassword: The password of the MariaDB user.

If you want to change the MariaDB user account password, then run the following command:

ALTER USER 'dbuser'@'localhost' IDENTIFIED BY 'yournewpassword';

To list all MariaDB user accounts, run the following command:

SELECT user, host FROM mysql.user;

You should get the following output:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
| dbuser           | localhost |
| debian-sys-maint | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.003 sec)

Step 4 – Grant Privileges to User Account

You can grant multiple types of privileges to the MariaDB user account.

To grant all privileges to a user (dbuser) over a specific database (db1), run the following command:

GRANT ALL PRIVILEGES ON db1.* TO 'dbuser'@'localhost';

To grant all privileges to a user (dbuser) over all databases, run the following command:

GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'localhost';

To grant only specific privileges to a user (dbuser) over a specific database (db1), run the following command:

GRANT SELECT, INSERT, DELETE ON db1.* TO dbuser@'localhost';

You can see all the privileges granted to a specific user account with the following command:

SHOW GRANTS FOR 'dbuser'@'localhost';

You should see the following output:

+------------------------------------------------------------------------------------------------------------------------+
| Grants for dbuser@localhost                                                                                            |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'localhost' IDENTIFIED BY PASSWORD '*7F99F4477B835ED95816BC01E9823771AE2A3F1C' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'dbuser'@'localhost'                                                                |
+------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Step 5 – Deleting Databases and Users

You can delete a database with the following command:

DROP DATABASE db1;

To delete a user account, run the following command:

DROP USER 'dbuser'@'localhost';

Step 6 – Reset MariaDB Root Password

If you have forgotten your MariaDB root password, then you can recover it easily by following the below steps.

First, stop the running MariaDB service with the following command:

systemctl stop mariadb

Next, you will need to start MariaDB service with –skip-grant-tables option. This will allow you to connect to the database server without a password:

You can start MariaDB service without loading the grant tables by running the following command:

mysqld_safe --skip-grant-tables &

Now, you can connect to the MariaDB server as a root user without a password as shown below:

mysql -u root

After successful login, run the following command to reset the root password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your-new-password';
FLUSH PRIVILEGES;

If the above command doesn’t work, run the following command:

UPDATE mysql.user SET authentication_string = PASSWORD('your-new-password')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Next, stop the MariaDB service and start it normally with the following commands:

mysqladmin -u root -p shutdown

Provide your root password when prompted to stop the MariaDB service.

Next, start the MariaDB service normally with the following command:

systemctl start mariadb

Conclusion

In the above tutorial, we learned how to create and manage databases and user accounts in MariaDB. We also learned how to reset the MariaDB root password. For more information, visit the MariaDB official documentation at MariaDB Doc.