MariaDB Tutorial: An Introductory Guide with Examples

March 9th, 2021

MariaDB is a popular relational database management system (RDBMS) that has a striking resemblance to MySQL. That is why we decided to compare and contrast the two systems while maintaining the focus on MariaDB databases. Read on to discover the essential information on MariaDB and learn how to install and connect to your MariaDB database as well as execute simple queries.

Contents

  1. What is MariaDB?
  2. MariaDB vs MySQL: Common Features and Differences
  3. Advantages of MariaDB
  4. The Most Notable Features of MariaDB
    1. SQL Compatibility
    2. Parallel Execution of Queries
    3. Thread Pooling
    4. Database Views
    5. Virtual Columns
    6. ColumnStore
    7. Flash Storage
  5. MariaDB Installation on Windows
  6. Connect to MariaDB via Command Prompt
  7. Create a MariaDB Database
  8. Create a Table in MariaDB
  9. MariaDB GUI Tools
  10. Work with MariaDB via dbForge Studio for MySQL
  11. Conclusion

What is MariaDB?

MariaDB is a software fork of MySQL, which is the main reason for the similarity between them. Its core is MySQL source code. However, they are not the same. MariaDB has evolved significantly since 2009 when several MySQL creators made a separate product from the same origin.

After Oracle Corporation purchased MySQL, the developers were concerned about the future of their product. They feared that Oracle would cancel MySQL to remove a major competitor. Also, they wanted to ensure that their creation would remain open source and free. This is how MariaDB appeared.

It is fully GPL licensed and available to everyone. Now it is an independent product from the top-10 list of the world’s most widely used relational databases (holding the 9th position). Among the many MariaDB users, you’ll see such companies as Google, Wikipedia, Tumblr, Amazon Web Services, Ubuntu, RedHat, and many more.

Besides the MariaDB Windows version, there are versions for Linux and macOS. This RDBMS works on Intel and IBM Power8 hardware platforms. As a service, it is available on Amazon Web Services (AWS), Rackspace Cloud, and Microsoft Azure cloud platforms. The system is easy to master, safe, and convenient. So, no wonder that its popularity grows.

MariaDB vs MySQL: Common Features and Differences

MariaDB is a frequent substitute for MySQL. Close relationships between these two systems make migration to MariaDB from MySQL smooth and straightforward. You can import and export data from one RDBMS into another without changing it. All habitual CMS tools and applications work on MariaDB out of the box.

Besides,

  • Data and table definition files, client APIs, and protocols are compatible.
  • MySQL connectors like PHP, Perl, Python, Java, etc. work on MariaDB properly.
  • Filenames, binaries, paths, ports, and sockets are the same on both MySQL and MariaDB.

The goal of the MariaDB team is to keep their product compatible with MySQL. Whenever a new MySQL version is released, the corresponding MariaDB version comes out, too. Therefore, MySQL users can switch at any moment – they will always have the matching environment to do it seamlessly.

However, keeping MariaDB a convenient match for MySQL is not the only goal. The functionality of this alternative system improves continually. The new versions bring more advanced options and features, such as JSON, WITH, and KILL statements. Moreover, the new MariaDB version releases many essential bug fixes that are absent in MySQL.

Advantages of MariaDB 

MySQL belongs to Oracle, which is the biggest database vendor in the world. Still, MariaDB competes against it. In several scenarios, it is superior:

  • MariaDB has more storage engines, including PBXT, XtraDB, Maria, and FederatedX.
  • Its connection pool is larger (up to 200 000+ connections).
  • Replication can be almost two times faster in MariaDB.
  • It is faster overall and easier-to-use.
  • MariaDB allows creating versioned tables.
  • It provides Oracle compatibility features making it easy to migrate from Oracle Database.
  • MariaDB Server supports columnar storage format.
  • MariaDB provides a distributed SQL database solution—MariaDB Xpand. 

Data Masking and Dynamic columns aren’t supported in MariaDB. However, in many aspects, it has the upper hand. Add the easiness of migration from one system to another, and you can see why MariaDB has become much more than a MySQL copy.

The Most Notable Features of MariaDB 

MariaDB has the client/server architecture with the main database and numerous clients that request the data and manipulate them. To communicate with the MariaDB server, clients use SQL statements and present the retrieved data and results of manipulations on the client-side. This architecture is standard. Also, MariaDB includes the same security features as MySQL, which is among the safest systems in the world.

Let’s now consider the key features related to the MariaDB database.

SQL Compatibility 

MariaDB supports most SQL statements, functions, procedures, definitions, variables, etc. There are several client programs, such as mysqladmin and mysqldump (these popular solutions for command-line come with MariaDB). Besides, there are many useful plugins, e.g., the audit plugin. All these options make the database-related tasks more straightforward in MariaDB.

Parallel Execution of Queries

Recent MariaDB versions from 10.0 and higher include this helpful option. Several queries can be executed simultaneously. The essence is replicating some queries from the main database in the secondary databases. In practice, it makes the task performance much faster. That provides OLAP capabilities for analytical queries at a much faster pace and in a smoother manner. MySQL does not provide this opportunity.

Thread Pooling

The concept of Thread Pooling accelerates work on MariaDB in regards to multiple connections to the database. Instead of opening a separate thread for each connection, MariaDB offers a pool of already open threads. A new connection takes a thread and sends the query. All queries become faster and produce quicker results. MySQL has a similar feature, but only in the Enterprise edition, which is paid. In MariaDB, it is available to all free of charge.

Database Views

Another performance optimization feature relates to database views. The main difference is in involving virtual tables when querying a view. In MariaDB, the process will involve only that dedicated, particular table. MySQL involves all tables connected to that view, no matter if the query targets them. The optimized query of a database view provides more speed and less load.

Virtual Columns

One of MariaDB’s prominent features is the support of virtual columns. Such columns can do calculations at the database level. When several apps access one column, users won’t need to write calculations in every app separately. Instead, the database will do it. 

ColumnStore

The feature of ColumnStore relates to the overall performance improvement. A distributed data architecture ensured correct scaling of MariaDB linearly. This way, it is possible to store excessive data volumes across various MariaDB servers in a database cluster.

Flash Storage 

MyRocks storage engine adds the RocksDB database to MariaDB. This database provides a higher level of data compression and thus performs better in flash storage.

The rest of MariaDB functionality includes a lot more options. You can get familiar with them in the official documentation. Besides, the variety of professional tools allow users to work with MariaDB efficiently even without in-depth knowledge of SQL.

MariaDB Installation on Windows

To use MariaDB, you first need to install it on your computer. In the paragraph that follows, we will take you through the installation steps and show you the difficulties and subtleties that may occur in the process.
To start, go to the download page, and select the version appropriate for you. For simplicity, select a Windows Installer Package and click Download.  After downloading the package, double click the file to initiate the installation process:

Welcome to the MariaDB Setup Wizard

Select the checkbox to accept the terms in the License Agreement:

Accept the terms in the License Agreement of MariaDB Server

Next, select the features you want to install on your computer. By default, you install all features. Also, you can select to install only Database instance, and the Wizard will create a new database instance.

Define which MariaDB products you want to install

After that, specify the root password for the MariaDB service. Select the necessary checkbox, if you want to permit the ‘root’ user to have access from remote machines. Also, select the lower checkbox if you intend to use UTF8 as your default character set. Click Next to continue:

Configure the MariaDB instance properties

Here, define to run the database as a service. The default name is MariaDB in MariaDB 10.4 and later versions. Specify the port number and set the buffer pool size that meets your requirements. The default buffer pool size is 12.5% of RAM, but you can extend it up to 70-80% RAM if necessary. After you configure the settings, click Next:

Specify the port number and set the buffer pool size

Now, you can begin the installation or go back to modify any of the settings:

Begin the installation

Click Finish to complete the installation:

Complete the installation

Now that you have your MariaDB up and running, let’s move on to the part where we are going to connect to your MariaDB database instance.

Connect to MariaDB via Command Prompt

If you plan to work with the DBMS directly through the command-line interface, you need to start the MariaDB command prompt. Click Start and begin typing the name as follows:

Access MariaDB command prompt from Start

Next, you need to log in as the root user and enter the password you specified during the installation. Type the following command in the command prompt and press Enter:

MariaDB -u root -p
Run the command to log in as the root user

You can now begin working with MariaDB! Let’s start by creating a database using the command prompt.

Create a MariaDB Database

If you want to add and start handling some data, you first need to create a database structure. Note that to create a database, one needs special privileges that are only granted to the root user or admin.

Execute the following statement in the command prompt specifying the name of your new database:

CREATE DATABASE DatabaseName;
Create a database via MariaDB command prompt

In the example above, we created a database named testdb. To make sure the creation was successful, you can check the list of the available databases. Run the following command:

SHOW DATABASES;
View the list of the available databases

As you see, the testdb database is on the list. Now, let’s proceed by creating a table in the database.

Create a Table in MariaDB

Before you start creating tables and filling a newly created database with data, you first need to select the database from the list of the available ones. After that, you can start working with this database. Thus, execute the following command:

USE database_name;
Run the USE statement to select the database

Having selected the database that you want to work with, you can finally create a table within the database. Use the following syntax to create a table:

CREATE TABLE tableName (columnName columnType);

For instance, a users table that we have created below has three columns: id, name, and email, and its primary key is the id column:

Create a table and specify column properties

To check whether the table has been created successfully, run the SHOW TABLES command as follows:

SHOW TABLES;
The SHOW TABLES command outputs a newly created users table

As you see, the testdb database contains a single table called users. However, if you intend to view the whole structure of the table, use the DESCRIBE command or DESC for short:

DESC TableName;
View the table structure

On the whole, you can create as many tables as necessary in the way described above. Later you will probably want to fill your tables with data using the INSERT INTO statement, and then you will probably want to run UPDATE commands to modify the data. However, the command-line interface may turn out to be clumsy and pretty time-consuming when it comes to querying data. It demands much manual effort and doesn’t provide any formatting or auto-completing functionality. This is where a database management tool will come in handy.

MariaDB GUI Tools 

Like it is with all DBMS management tools, MariaDB developers, administrators, and analytics apply different software solutions to work with the databases more efficiently. Among them, there are free and paid solutions, as well as multi-functional tools and specific utilities targeted at particular jobs.

Database tools for MySQL relate to MariaDB as well. You’ll notice that in many tools’ specifications. One of the most popular and functional product lines for MariaDB management is Devart’s dbForge for MySQL. These tools are excellent for creating databases and database projects, writing queries, generating database scripts, comparing and synchronizing MariaDB databases, backing up and restoring tasks, reporting, etc. 
All of these and more tasks are covered by the powerful multi-featured dbForge Studio for MySQL. Tools included in the Studio bunch are also available as stand-alone products. Full-functional free trials are present for every solution, and you can test their functionality and evaluate it appropriately.

Work with MariaDB via dbForge Studio for MySQL

The biggest advantage of working with a GUI tool like dbForge Studio for MySQL is the possibility to perform the majority of database-related tasks much faster: tools like Query Builder, Data Editor, MySQL Completion, Table Designer, and many others allow to build, edit, and run queries in a few clicks. So, let me show how to perform a couple of simple tasks in a MariaDB database through dbForge Studio for MySQL.

First, connect to the testdb database that was created earlier in the article:

Connect to your MariaDB database via dbForge Studio for MySQL

Note that dbForge Studio for MySQL provides additional security options enabling the use of an SSH or SSL security protocol.

After connecting to our testdb database, we can view its contents in the Object Explorer. By clicking on the testdb database node, expand the tree view and access the users table created previously:

Access the table from the Object Explorer

As you see here, the software allows you to add new columns, specify properties, set primary keys, and edit the available information via its intuitive interface.

After that, we want to fill the users table with data. To do that, right-click on the table, select Generate Script As Insert To New SQL Window, just as shown below:

Generate the INSERT script automatically via the interface of MySQL GUI tool

You will be prompted to a new SQL window with all the necessary syntax at hand. Insert the necessary data into the table, as shown below, and click Execute:

Insert the necessary data into the syntax provided by the MySQL software

The green checkmark below informs you of the successful query execution.

To make sure the table has the new data that you have just inserted, switch to the Data view mode and as follows:

View the newly added data in the Data view mode of dbForge Studio for MySQL

These simple tasks are performed much easier through a handy GUI tool like dbForge Studio for MySQL. However, the tool’s functionality is not limited to such straightforward tasks. The Studio provides rich features empowering you to import and export data, design a database visually, create data reports, document your database, and the list goes on and on.

Conclusion

So, let’s summarize. In the article, we have outlined the main similarities and differences between MariaDB and MySQL databases; described the principal advantages that MariaDB offers; showed you the MariaDB installation process; talked about how to get started with your MariaDB database via command prompt and via a versatile GUI tool, dbForge Studio for MySQL.


Leave a Comment