Introduction To MariaDB

A database is a structured collection of records or data stored in a computer server and organized in such a way that it can be quickly searched and information can be rapidly retrieved with very minimal to no overheads.

MySQL or MariaDB is an open-source, multithreaded relational database that gives you the ability to create one or more database tables, each of which contains records or rows, on this page, you'll learn plentiful of stuff about MySQL or MariaDB.

MariaDB is a fork of the MySQL database management system by the original developers of MySQL. So, they are practically the same thing, but MariaDB comes bundled with more powerful features than MySQL. Like an improved version.

The good thing about learning any SQL system is that it hasn't changed very much, so, anything you learn in one would gracefully transfer to others.

Note: MySQL and MariaDB name would be used interchangeably. 

Before you get started with MariaDB, you would want to make sure that you have installed on your system, here is a guide for Ubuntu, this is what I would be using throughout this guide and future guides, if you are using windows, there are plentiful of tutorials online

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

Useful Terms To Know

• MariaDB Server - The server is a software that maintains, controls, store files on the computer system, and protects your data.
• The serve listens for requests from others from other software that is running, which is usually known as clients. The client can be a web browser or software, and can even be running alongside the server on the same computer.
• mysqld - The d stands for daemon or also known as a server, note that the name is the same in both MySQL and MariaDB. The daemon must be running in order for uses to be able to access data and make changes, it is not needed by the client but needed for the administrator that wants to configure or make use of MariaDB.
• MySQL client or mysql - This gives you the ability to interact with the mysqld daemon and databases. It is a textual user interface and used on the command line.

Basics of MariaDB

Log-in to mariadb shell using:

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 mariadbor sudo mariadb -u root -pwhich 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:

sudo systemctl start mariadb
sudo 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)]>

As you can see in the output above, the first line says Welcome to the MariaDB monitor. Commands end with ; or \g. so, you end each and every statement you enter in the client with a semicolon (;) or a slash-g (\g), I guess the semicolon is much more better as it is just a stroke on your keyboard.

The second line tells you the identification number for your connection on the server, in my case, it is 38, it would be useful in the future, for now, let's move on.

The third line tells you which MariaDB is installed on your server, in a scenario where you are installing a certain software, and you are told the requirement of mariadb to have on your system, checking the version would be handy.

Now, if you need to quickly read the documentation, and or you want check information on Manipulating Data in the Database, e.g inserting, updating and deleting data, you can enter:

help contents

This would showing several categories on what you need, e.g:

MariaDB [(none)]> help contents
You asked for help about help category: "Contents"
For more information, type 'help ', where  is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

MariaDB [(none)]>

To narrow down on Data Manipulation, you enter "help Data Manipuilation", and that would return the following:

MariaDB [(none)]> help Data Manipulation
You asked for help about help category: "Data Manipulation"
For more information, type 'help ', where  is one of the following
topics:
   CALL
   DELETE
   DO
   DUAL
   HANDLER
   INSERT
   INSERT DELAYED
   INSERT SELECT
   JOIN
   LOAD DATA
   LOAD XML
   REPLACE
   SELECT
   UNION
   UPDATE

MariaDB [(none)]>

To read anything, just do "help item", super useful. You everything at your finger tip, you don't even need to google anything, just run the help content statement, and narrow down on what you need. If you can’t quite remember the syntax of an SQL statement, it’s a quick way to retrieve the information.

Another useful tip when working with the client is the \c, this is useful in the case where you want to cancel an SQL statement once you've started typing it, you simply enter \c and press enter without a closing semicolon, e.g

MariaDB [(none)]> SQL statements \c
MariaDB [(none)]>

It will clear whatever you have been entering, even on previous lines from the buffer of the mysql client, and return you to the MariaDB > prompt.

Creating and Managing New User For MariaDB

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;

The above is like saying, I am user "admin", and I want to be identified by "password"

So, 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 to 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.%' IDENTIFIED BY 'password';

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 the server’s database. Any logged on Linux user will be able to access the database server and manage it, provided they know the password.

Accessing the Newly Created User

Logout from mariadb using Ctrl + D, and then use the following command to access the newly created user, replace admin with the user you created:

mariadb -u admin -p

You would be prompted to enter the new user password, in this case, I am assuming the new user is "admin", and 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

There is no space between the -p option and the actual password, so, say I want to use password ‘Iamstrong’, 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 a database with the new user, but note that the admin account can’t manage users, so, you’ll still need to log in as root to manage user permissions.

Quick Overview on Creating Database, Tables, and Records

This section would work you through getting familiar with mariadb database creation, tables creation, and also manipulation. Don't worry if you don't understand a certain concept, we would cover in detail in future guides.

Let's start by asking ourselves what is data? In its simplest term, it is a piece of information, it can be anything, user info such as name, email address, etc, it can be a collection of music names and anything that can be stored for further analysis.

This is no different in SQL, it gives us the ability to manipulate the data in a structured and efficient manner. Below is an example of a table in a database:

+----------+--------------+-----------------+
| music_id | title        | artiste         |
+----------+--------------+-----------------+
|       50 | Silhouette   | Avicii          |
|       51 | Higher Steps | Beautiful Nubia |
|       52 | Jailr        | Asa             |
+----------+--------------+-----------------+

The above is a table about music, as you can see, we have a horizontal row about each music, with the title as on column, and other columns to indicate more information on each music, e.g Artiste.

Create a Database

Let's see a quick example of how you can create the above table, from the MariaDB prompt, but first, enter the following and press the Enter key:

SHOW DATABASES;

You would get the following output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

You don't have to write "SHOW DATABASE" in full caps, mariadb is case insensitive, so, you can as well write "show database" and it would work just fine.

Note: Linux is case sensitive, so, whenever you are referencing names of databases, tables, and columns, you might want to reference them in the proper case.

I would use uppercase to reference all mariadb statements and use lowercase to reference the database name, table, and columns, as this can tell you what is what. You can do whatever you think is best.

Back to the information returned by "show database":

It displays three databases, the information_schema database contains information about the server, the mysql database stores usernames, passwords, and user privileges. The performance_schema is a feature for monitoring server performance, don't manipulate these files, they are there for a reason, just wanted to point it out.

Create a Test Database:

The database is where we would store our tables pertaining to our music info, so, create one using the following command:

CREATE DATABASE test;

You would get the following output:

Query OK, 1 row affected (0.00 sec)

The result says one row is in set, meaning the query is okay, and you have 1 database created.

Create Table

Our test database is initially empty, and contains no table whatsoever, so, to create one, enter the following:

CREATE TABLE test.music (music_id INT, title TEXT, artiste TEXT);

The above SQL statement creates a table in the test database and we named it music. Note that, we specified the name of the database and table with table.music (the syntax is database.music).

Inside the parenthesis, we defined three columns for the table, the first one is the music_id, the title of the music, and the artiste column, we would talk about the INT, and TEXT later, keep reading.

Now, to see a list of tables within the test database, you enter:

SHOW TABLES FROM test;

It gave me the following result:

+----------------+
| Tables_in_test |
+----------------+
| music          |
+----------------+

For each SQL statement, you have to specify the database name, e.g test.music, since you would be working with mostly one database at a time, you can set the default database so that you don’t have to specify the database each time.

To do this, enter the USE command like so:

USE test;

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 test database.

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

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

You can re-enter the earlier SQL statement to list the tables in the database without specifying that you want test, I mean, you can now change:

SHOW TABLES FROM test;

to

SHOW TABLES;

You see how easy it is, we create a music table earlier, if we had used the USE test; command, we would have entered the following command:

CREATE TABLE music (music_id INT, title TEXT, artiste TEXT);

Instead of:

CREATE TABLE test.music (music_id INT, title TEXT, artiste TEXT);

You don't need to specify the database anymore.

We have only shown our tables so far, to take a look at what is inside the table, you enter the following command:

DESCRIBE music;

+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| music_id | int(11) | YES  |     | NULL    |       |
| title    | text    | YES  |     | NULL    |       |
| artiste  | text    | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+

As you can see, we created three fields for entering data, which are music_id, title, and artiste.

The first field is an integer type, meaning it can only contain whole numbers but not fraction, the second and third are test type, meaning they can contain text (text is anything you can type at the keyboard).

Inserting and Altering Data

To put data into the music table, you can enter the following statements:

INSERT INTO music (music_id, title, artiste)
VALUES
(50, 'Silhouette', 'Avicii'),
(51, 'Higher Steps', 'Beautiful Nubia'),
(52, 'Jailr', 'Asa');

As you can see, I only added the semi-colon at the end of the third SQL statement, nothing would be processed until you type a semicolon and then press the Enter key, this can make things easier to read, and can help in the case you made a mistake.

Also, when inserting multiple rows,  include multiple lists of column values, each enclosed within parentheses and separated by commas as I have done above.

It printed the following result:

MariaDB [test]> INSERT INTO music (music_id, title, artiste)
    -> VALUES
    -> (50, 'Silhouette', 'Avicii'),
    -> (51, 'Higher Steps', 'Beautiful Nubia'),
    -> (52, 'Jailr', 'Asa');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

As you can see, 3 rows were added, with no duplicates nor error.

So, now, we have used the SQL statement INSERT to insert, or add data, to the music table, the values are given in the same order and format you create the fields, the first value, e.g 50 would be added to music-id, the second value, e.g "Silhouette" would be added to the title and the last one, e.g "Avicii" would be added to the artiste column, this is also same for the remaining data.

To display the data you have entered in the music table, enter the following:

SELECT * FROM music;

+----------+--------------+-----------------+
| music_id | title        | artiste         |
+----------+--------------+-----------------+
|       50 | Silhouette   | Avicii          |
|       51 | Higher Steps | Beautiful Nubia |
|       52 | Jailr        | Asa             |
+----------+--------------+-----------------+

You can see how it structurally returned the record rows and field columns. We used the SELECT statement to select all columns — the asterisk (*) means “everything” — from the table music.

music_id function as a record identification number of each music while tile and artiste contain more info about the music.

To return the music that has an ID of 50, we use the WHERE clause to the SQL statement:

SELECT * FROM music WHERE music_id = 50;

+----------+------------+---------+
| music_id | title      | artiste |
+----------+------------+---------+
|       50 | Silhouette | Avicii  |
+----------+------------+---------+

If you look at the SQL statement, you can see we use them just as we speak, it says, Select everything (*) from the table music, WHERE the music_id is 50. When you read an SQL statement like that, you begin to grasp the concept real quick, let's try one more example...

To return the music that has an ID of 51 we do:

SELECT * FROM music WHERE music_id = 52;

+----------+-------+---------+
| music_id | title | artiste |
+----------+-------+---------+
|       52 | Jailr | Asa     |
+----------+-------+---------+

You can see how easy it is, we have a bit of an issue, the title of the song is actually "Jailer" and not "Jailr", but I did this on purpose to simulate a real word experience, you'll make lots of mistakes, and you should be able to correct them real quick, but before we do the correction, here is another way you can print a result:

MariaDB [test]> SELECT * FROM music WHERE music_id > 50 \G
*************************** 1. row ***************************
music_id: 51
   title: Higher Steps
 artiste: Beautiful Nubia
*************************** 2. row ***************************
music_id: 52
   title: Jailr
 artiste: Asa

You'll notice I changed the ending of the SQL statement from semicolon to \G, this option is better if you have a long field to read. You'll also notice I change the Where clause to:

WHERE music_id > 50

This would return any music_id that is greater than 50, and that is 51 and 52.

Now to correct the title of the song, you do:

UPDATE music SET title = 'Jailer' WHERE music_id = 52;

We are saying, update music by setting the title to "Jailer" where the music_id is 52, since we have a unique ID for every song, it is only going to change one.

To see the results, run the SELECT statement as we've seen earlier:

SELECT * FROM music WHERE music_id = 52;

+----------+--------+---------+
| music_id | title  | artiste |
+----------+--------+---------+
|       52 | Jailer | Asa     |
+----------+--------+---------+

As you can see, we got the title updated, before we move on, you can update multiple rows of data using:

UPDATE music 
SET title = 'Jailer', music_id = 53 
WHERE music_id = 52;

Don't run this, I am just showing you an example of how you can update multiple values, this is better than entering the UPDATE statement twice.

In future guides, we would explore further on adding multiple tables, manipulating data, and so on.

See ya.