For the longest time, MySQL database management system (DBMS) has been in use in database driven applications. However, when Oracle acquired MySQL, there were serious concerns from the community regarding its open source nature. This resulted in the development of MariaDB by the founders of MySQL. This tutorial focuses on introductory concepts in MariaDB which will hopefully push you to get a more in-depth understanding and master this database management system.
For a better understanding of this tutorial, prior experience with relational database management systems, MySQL, querying languages and general programming concepts are advantageous.
Introduction
MariaDB is the next step in database management. It has the adaptability needed to serve both enterprise needs and the smaller data processing jobs. Since there exist some similarities with MySQL, you can just simply uninstall MySQL (if you have it), and install MariaDB in its place. MariaDB is a Relational database management systems (RDBMS) and as such stores data in multiple tables. The relationships between these tables are maintained using the established primary and foreign keys. Before we go any further, let’s look at the most essential features of
MariaDB:
- There is a vast selection of storage engines, some of which are high-performance engines to facilitate working with other RDBMS sources.
- The querying language in MariaDB is standard and quite popular SQL – Structured Query Language.
- MariaDB is flexible and versatile being supported by multiple operating systems and programming languages.
- MariaDB uses Galera cluster technology to achieve high performance and scalability through replication.
- MariaDB supports PHP and offers a lot more commands than there is in MySQL which impact performance.
Installation
All the download resources you need at this point can be found on the official website of the MariaDB foundation. There you will be given multiple options for various operating systems and architectures. Chose an appropriate one and download.
On UNIX/LINUX
If you have a mastery of Linux, you can simply download the source and do the build yourself. The safest bet here would be using packages for various distributions. Distributions are available for-
- Ubuntu/Debian
- CentOS/Fedora/RedHat
Also, these distros have a MariaDB package inside their repositories-
- Slackware
- Magela
- Arch Linux
- Mint
- openSUSE
Installation steps on Ubuntu
- Log in as the root user since you need to have unfettered access while doing the installation.
- Go to the directory that has the MariaDB package – this is the directory you downloaded the package into. At this point, we shall perform an importation of GnuPG signing key by using the following code.
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
- The next step is to add MariaDB to the file called sources.list. Use the following code after opening the file.
sudo add-apt-repository 'deb http://ftp.osuosl.org/pub/MariaDB/repo/5.5/ubuntuprecise main.'
- Use the following command to refresh the system
sudo apt-get update
- Install with the following command
sudo apt-get install MariaDB-server
Creating a database
Creating and deleting databases needs administrative permissions, i.e., root user privileges. There are two ways to do this:
mysqladmin binary
This is a straightforward way of creating a database. The following is the code to create a new database called LINUXHINTS.
[root@host]# mysqladmin -u root -p create LINUXHINTS Enter password: ******
PHP Create Database Script
Here, PHP will use the mysql_query function to create a MariaDB database. This function only uses two parameters where the value “true” is returned when successful and when unsuccessful, it returns “false”. Here are a sample code and syntax:
<html> <head> <title>Create MariaDB Database</title> </head> <body> <?php $dbhost = 'localhost:3036'; $dbuser = ‘root’; $dbpass = 'root password'; $conn = mysql_connect($dbhost, $dbuser, $dbpass) if(! $conn ) { die('Failed to connect: ' . mysql_error()); } echo 'Connected successfully'; $sql = 'CREATE DATABASE LINUXHINTS'; $result = mysql_query( $sql, $conn ); if(! $result ) { die('Failed to create the database: ' . mysql_error()); } echo "Database LINUXHINTS creation successfuln"; mysql_close($conn); ?> </body> </html>
Drop database
This function also needs administrative privileges to execute. A query that takes two parameters and should return either true or false is executed: bool mysql_query( SQL, connection );
Here is a sample PHP code snippet for deleting a database:
<html> <head> <title>Delete MariaDB Database</title> </head> <body> <?php $dbhost = 'localhost:3036'; $dbuser = 'root'; $dbpass = 'root password'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } echo 'Connected successfully'; $sql = 'DROP DATABASE LINUXHINTS'; $retval = mysql_query( $sql, $conn ); if(! $retval ){ die('Could not delete database: ' . mysql_error()); } echo "Database LINUXHINTS deleted successfullyn"; mysql_close($conn); ?> </body> </html>
Selecting database
Assuming you did not go through with the previous section of deleting a database, and it is still available on your localhost/server, you must now select it to start using it. Else, you will have to create it again before proceeding with the next steps.
To select the database, we employ the “use” SQL command. Below is the syntax:
USE database_name;
Creating tables and dropping them
Tables are the glue to RDBMS. Before creating a table, you should already know its name, the names of the fields and their corresponding definitions. Here is a general syntax for this.
CREATE TABLE your_table_name (column_name column_type); CREATE TABLE comments_tbl( -> comment_id INT NOT NULL AUTO_INCREMENT, -> comment_content VARCHAR(1000) NOT NULL, -> commenter_name VARCHAR(50) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( comment_id ) -> );
To confirm whether the table was created, use “SHOW TABLES” command.
To drop the tables, use the DROP TABLE command.
mysql> use LINUXHINTS; Database changed mysql> DROP TABLE comments_tbl
Insert query
Information must first be existing in a table before manipulation. Hence, we must first add the information using the INSERT command. Below is the syntax for the insertion.
INSERT INTO table_name (field,field2,...) VALUES (value, value2,...);
For example
INSERT INTO users<tbl (user_id, user_name, user_address, signup_date) VALUES (1,'John','Texas','2017-11-07 00:00:00'), (2,’Jane','Vegas','2017-12-07 00:00:00');
Select query
Since we have inserted data into our table, we can now query it. The SELECT statements are used to query data from a particular table or tables. The SELECT statements can include UNION statements, a LIMIT clause, an ORDER clause, among others. This is the general syntax –
SELECT field, field2,... FROM table_name, table_name2,... WHERE...
Where clause
This clause is essentially made to filter out statements such as UPDATE, SELECT, INSERT and DELETE. These clauses show the criteria to be used for a specified action. This is the general syntax-
[COMMAND] field,field2,... FROM table_name,table_name2,... WHERE [CONDITION]
Example
mysql> use LINUXHINTS; Database changed mysql> SELECT * from users_tbl WHERE user_address = 'Vegas';
These are only but basic concepts surrounding MariaDB. However, with the mastery of these commands, you can now advance your knowledge further and build a big MariaDB driven system.
Sources
https://www.tutorialspoint.com/mariadb/
https://mariadb.org/learn/
https://www.tecmint.com/learn-mysql-mariadb-for-beginners/
https://www.techonthenet.com/mariadb/index.php
https://www.javatpoint.com/mariadb-tutorial
https://mariadb.com/kb/en/library/training-tutorials/
