Atlantic.Net Blog

How to Manage Databases in MariaDB on Ubuntu 18.04

Hitesh Jethva
by Atlantic.Net (76posts) under VPS Hosting
0 Comments

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.

Prerequisites

  • A fresh Ubuntu 18.04 VPS on the Atlantic.Net Cloud Platform.
  • A root password configured on both servers.

Step 1 – Create an Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server.  Create a new server, choosing Ubuntu 18.04 as the operating system with at least 2GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged into your Ubuntu 18.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y
apt-get upgrade -y

Step 2 – 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-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 3 – 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 4 – 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 5 – 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 [email protected]'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 [email protected]                                                                                            |
+------------------------------------------------------------------------------------------------------------------------+
| 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 6 – 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 7 – 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.

Get A Free To Use Cloud VPS

Free Tier Includes:
G2.1GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G2.1GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources