How to Install and Use MariaDB on Ubuntu 18.04

Install MariaDB Ubuntu

MariaDB database server is an open source server that is a fork of MySQL and a drop-in replacement. It is now the fastest growing open source database server today. Over the many years, MySQL was the king of database servers for all of Linux systems.

Now MariaDB is the king of database servers for all of Linux systems after MySQL current parent company, Oracle made some changes to its licensing terms that angered lots of people in the open source community.

Today, MariaDB is the default database server on the majority of Linux distributions, if not all. For those who want to test MariaDB on Ubuntu 18.04 LTS, the steps below will get you started.

Step 1 — Installing MariaDB Database Server On Ubuntu 18.04

Just single command line to install MariaDB Database Server, to do that run the commands below. First, run the commands below to update your Ubuntu 18.04 machines.

sudo apt-get update

Next, run the commands below to install MariaDB server and client.

sudo apt-get install mariadb-server mariadb-client

After installing, the commands below can be used to stop, restart, start and enable MariaDB service to always start up when the server boots.

sudo systemctl stop mariadb.service
sudo systemctl restart mariadb.service
sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service

After that, run the commands below to secure MariaDB server by creating the root user password and deleting the test database.

sudo mysql_secure_installation

When prompted, answer the questions below by following the guide.

  • Enter current password for root (enter for none): Just press the Enter
  • Set root password? [Y/n]: Y
  • New password: Enter password
  • Re-enter new password: Repeat password
  • Remove anonymous users? [Y/n]: Y
  • Disallow root login remotely? [Y/n]: Y
  • Remove test database and access to it? [Y/n]: Y
  • Reload privilege tables now? [Y/n]: Y

Restart MariaDB server when done.

sudo systemctl restart mariadb.service

To verify MariaDB version, run the command below:

mysql --version
output
mysql  Ver 15.1 Distrib 10.1.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Step 2 — Configuring MariaDB Database Server

Now that MariaDB server is installed, the default configuration file is located at /etc/mysql/mariadb.config.d/50-server.cnf, this is where you enter config directives to optimize and enhance the database server performance.
Most of the settings you’ll be looking for will be in this file.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Make all the changes you want in the file above and save CTRL + x, then restart the server.

sudo systemctl restart mariadb.service

MariaDB Administration Commands

We will now discuss some useful MariaDB administration commands. These are some very basic commands that will get you started with using MariaDB & these can also be used with MySQL, since Mariadb is forked out version of MySQL only.

1) Checking version of your MariaDB installation

To check the current version of your DB installation, type following command in your terminal:

$ mysql --version

you can also run below mentioned command for the detailed view on version.

$ sudo mysqladmin -u root version

2) Logging into MariaDB

To log into the MariaDB server, run

$ sudo mysql -u root -p

& then enter password to login to the session.

3) Showing all databases

To show all the databases that your MariaDB currently has, run command below:

MariaDB [()]> show databases;

4) Creating new databases

To create a new database in mariadb, run command below:

MariaDB [()]> CREATE DATABASE store;

Here, store is the name of the new database.

5) Deleting a database

To delete a database, run command below:

MariaDB [()]> DROP DATABASE store;

6) Creating new user

To create a new user for database, run command below:

MariaDB [()]> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'pass123';

7) Granting access to user for a database

For providing access to testuser for a single database, run command below:

MariaDB [()]> GRANT ALL PRIVILEGES ON store.* to 'testuser'@'localhost';

This will provide user testuser complete access over database named store. We can also grant SELECT, INSERT , DELETE permissions to testuser.
To provide access to all databases, replace store with * i.e.

MariaDB [()]> GRANT ALL PRIVILEGES ON *.* to 'testuser'@'localhost';

8) Creating backup/dump of database

Frist type quit to exit from mariadb.
To create of a single database, run the following command from your terminal window,

$ sudo mysqldump store > db_backup.sql

To create backup of multiple databases in single command,

$ mysqldump --databases database_one database_two > two_databases.sql

In the code above, database_one is the name of the first database to be backed up, and database_two is the name of the second.
To create dump of all databases in single command,

$ sudo mysqldump --all-databases > all_databases.sql

In the code above will back up all of the databases on a server.

9) Restoring database from the dump

To restore database from a dump, run

$ sudo mysql store < db_backup.sql

In the code above, store is the name of the database you want to restore, and db_backup.sql is the name of the backup file to be restored.
If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this:

mysql --one-database store < all_databases.sql

10) Changing password for a user in MariaDB

if you wanted to update the user named testuser with the password 'newpassword', you would run the following SET PASSWORD statement in MariaDB:

$ mysql -u root -p
MariaDB [()]> SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD('newpassword');

and then exit the session by hit Ctrl+d.
In the end run command below:

$ sudo systemctl restart mariadb.service

11) Create Tables In MariaDB Table

We have named our database "store", so now let's create a table with this database that describes products found in a store.
Frist select which database you will use, in our article is store:

MariaDB [()]> USE store;
MariaDB [(store)]> CREATE TABLE IF NOT EXISTS products (
    product_id int(5) NOT NULL AUTO_INCREMENT,
    kind varchar(50) DEFAULT NULL,
    added_date DATE DEFAULT NULL,
    size varchar(20) DEFAULT NULL,
    origin varchar(250) DEFAULT NULL,
    PRIMARY KEY(product_id));
output
  Query OK, 0 rows affected (0.03 sec)

To see what we've accomplished, use the following command to print out the columns of our new table:

show columns in products;

products1

The results give us some insight into the fields necessary to define a column. Each column description in the table creation command is separated by a comma, and follows this convention:

Column_Name Data_Type[(size_of_data)] [NULL or NOT NULL] [DEFAULT default_value] [AUTO_INCREMENT]
product_id int(5) NOT NULL AUTO_INCREMENT

These are the values of each column definition:

  • Column Name: Describes the attribute being assigned. For instance, the first column is called "product_id" because it will hold the unique ID number associated with each product of products.
  • Data Type: Specifies the type of data the column will hold. Can be any of MySQL's data types. For instance, "int" specifies that only integer values will be accepted, while "varchar" is used to hold string values.
  • Null: Defines whether null is a valid value for that field. Can be "null" or "not null".
  • Default Value: Sets the initial value of all newly created records that do no specify a value. The "default" keyword is followed by the value.
  • auto_increment: MySQL will handle the sequential numbering internally of any column marked with this option, in order to provide a unique value for each record.

Finally, before closing the column declarations, you need to specify which columns to use as the primary key by typing "PRIMARY KEY (columns_to_be_primary_keys).

We used our "product_id" column as the primary key because the "auto_increment" option guarantees the value to be unique, which is a requirement of a primary key.

12) Delete Tables From MariaDB Table

To delete a table we can use the following syntax:

MariaDB [()]> DROP TABLE table_name;

Be very careful with this command, because once the table is deleted, the data inside cannot be recovered.

First, let's view our current table so that we can establish what the "show tables" command looks like:

MariaDB [(store)]> SHOW tables;

show-tables1

Let's delete our products table:

MariaDB [(store)]> DROP TABLE products;

And now, check the "store" tables list again:

MariaDB [(store)]> SHOW tables;
output
Empty set (0.00 sec)

We haven't any tables in the "store" database, so the operation was successful.

13) Insert Data Into MariaDB Table

Let's insert a record into our table. To do this, we'll use the following syntax:

MariaDB [()]> INSERT INTO table_name (field1, field2, ...) VALUES (value1, value2, ...);

Every string value must be placed in quotation marks. Every column with "auto_increment" set does not need a value, as the database will provide it with the next sequential number.

We can add a row to our store products table like this:

MariaDB [(store)]> INSERT INTO products (kind, added_date, size, origin)
VALUES
("T-Shirt", Now(), "max", "EGYPT");

We used a special function called "Now()" that fills in the current date for the date column.

To see the information, query the table. The asterisk (*) is a special wildcard character that matches everything. This query selects everything in the products table:

MariaDB [(store)]> SELECT * FROM products;

insert-tables
Read Also:

Thanks for reading this article and please drop your suggestion on the comment section.

Ahmed Abdalhamid 12:05 am

About Ahmed Abdalhamid

Technical Support Engineer experienced in Linux servers administration of production environments. Exploring DevOps culture and tools. Interested in containerization and open source monitoring tools.

Author Archive Page

Have anything to say?

Your email address will not be published. Required fields are marked *

All comments are subject to moderation.