Mariadb Create Database – How to Guide (Step by Step)
MariaDB is the most versatile open-source relational database management system (DBMS) that can be used in place of the widely used MySQL database technology. It was developed as a clone of MySQL by developers who were instrumental in the original database’s development; they formed MariaDB in 2009 in response to Oracle Corp.’s acquisition of MySQL, when they saw a threat to open source development to MySQL. In this guide we will show the steps to MariaDB create database.
Much of the development work on the open source database has focused on achieving feature parity between MariaDB vs MySQL. MariaDB Corp., the driving force behind MariaDB, says that “for all practical purposes”. As a result, most users can switch between the two technologies simply by uninstalling MySQL and installing MariaDB in its place. In this article, we will create a MariaDB database without much ado; let us get started.
Features of MariaDB Database
- Supports Major SQL Statements
- Several Queries can be executed in Parallel.
- Supports Thread Pooling Concept – Thread Pooling accelerates work on MariaDB when many connections to the database are used. Rather than creating a new thread for each connection, MariaDB makes use of a pool of previously opened threads. A new connection creates a thread for the query and sends it. All searches become more efficient and offer more accurate results. MySQL provides a comparable feature; however, it is only available in the Enterprise edition, which is a costly upgrade. It is completely free in MariaDB.
- Support for Database Views
- Virtual Columns’ Support – The support for virtual columns is one of MariaDB’s most notable features. At the database level, such columns can do calculations. Users won’t have to write computations in each app independently when multiple apps use the same column. Instead, it will be handled by the database.
- Feature of ColumnStore supported – The feature of ColumnStore enables better storage of massive volumes of data across the cluster. This also improves the overall performance of the cluster through horizontal scaling improvement.
- Supports Data Compression using Flash Storage – The RocksDB database is added to MariaDB via the MyRocks storage engine. This database compresses data more efficiently and so performs better in flash storage.
Major Organizations using MariaDB Database
ServiceNow, DBS Bank, Google, Mozilla, the Wikimedia Foundation are some of the major organizations using MariaDB.
MariaDB is included in a number of Linux distributions and BSD operating systems. Several distributions, including Arch Linux, Manjaro, Debian, Fedora, Red Hat Enterprise Linux, CentOS, Mageia, OpenSUSE, SUSE Linux Enterprise Server, OpenBSD, and FreeBSD, make MariaDB the default database.
Mariadb Database Use Cases
MariaDB finds its usage in ad-hoc analytics, Data Marts for Hadoop, Self service analytics and smart transactions. Also, read the article on setting up Azure MariaDB in the cloud.
MariaDB Enterprise can be used as a data warehouse or analytics database, utilising columnar storage and massively parallel processing (MPP) to execute interactively, without the need to create indexes ad hoc queries can be executed on hundreds of billions of rows – and all using standard SQL (including joins). Additionally, MariaDB Enterprise utilises a cloud-native storage architecture and offers the option of using Amazon S3 compatible object storage to reduce costs and take advantage of unlimited capacity.
Install MariaDB Database on Windows
Download MariaDB
MariaDB can be installed from the official repository. At the time of writing this article, the stable version was 10.6.4. However, the Windows installer is not working, and we will be using an older version for the installation of our windows.
Installing MariaDB on Windows
For windows, we will be using version 10.5.12. After you download the msi installer, click on it and click next. It is quite easy to install on windows.
Click on next till you reach the window as shown below.
The above window gives you an advanced option to select various features. Leave it as default in case you don’t understand much. Click on next, and you will be directed to a window where you have to provide a password for the root.
You will be required to provide a root password in case you opted for a DB instance in the Custom setup window. Here you can also enable access from the remote machine for the root user. Click on next, and you will be routed to default instance properties.
You can configure the service name along with the port. You can also configure Innodb settings here. If you are a beginner, then leave it as default values and click next and install it.
Installing MariaDB on MacOS
For macOS, you will need brew for installing MariaDB. In case you don’t have homebrew installed in your macOS, then follow the instructions here. Now follow the instructions after homebrew has been installed. First, of all install MariaDB using brew.
brew install mariadb
After installing MariaDB, start MariaDB.
mysql.server start
To automatically start the MariaDB Server every time on restart (also known as a background service):
brew services start mariadb
After MariaDB Server is started, you can log in as your user:
mysql
To login as root (no password is initially set):
sudo mysql -u root
You will get a MariaDB command console as shown below.
Install MariaDB Database on Ubuntu Server
To install on Ubuntu follow the instructions. First update the package list.
sudo apt update
Next we will install the mariadb.
sudo apt install mariadb-server
Once the mariadb is installed, it will be automatically started. You can check the status by running the following command.
sudo systemctl status mariadb
MariaDB Create Database
For the creation and deletion of the database in MariaDB, you will require local admin privileges if running on Windows and root admin if running on Linux. You can also read the article about installing MariaDB Windows in the cloud (Azure, AWS, GCP)
Once MariaDB has been installed, run the MariaDB console and use the following command:
MySQL -u root –p
Provide the password which you gave during the installation process.
Syntax:
CREATE DATABASE databasename;
Sample Code:
CREATE DATABASE Logistics;
Incase you are not sure whether the database exists or not you can add Replace with Create
CREATE OR REPLACE DATABASE Logistics;
You can also use DROP and CREATE database incase you are not sure.
DROP DATABASE logistics;
CREATE DATABASE logistics;
You can provide a check before creating a database to ensure whether the database has been previously created or not.
CREATE DATABASE IF NOT EXISTS logistics;
You can check all the created databases by using the following command:-
show databases;
MariaDB Create Database Steps Finished
The biggest advantage of using MariaDB is that it was created as a clone of MySQL. It is easy to migrate the MySQL database to MariaDB. MySQL fans won’t be disappointed. Most of the MySQL syntaxes and commands are retained in MariaDB. You can read more about Maria DB installation in the article MariaDB cloud.
MariaDB is a versatile Database and provides support for various applications from modern-day NoSQL databases to traditional relational databases. The learning curve is easy. MariaDB can be used to build and deploy a variety of applications.
Despite a lot of support for MySQL, there are still many drawbacks, and it requires some consideration before selecting MariaDB. Issues such as compatibility with the latest version of MySQL is a challenging issue. So, people who are migrating from the latest version of MySQL would still have to re-code some of their databases to migrate them to MariaDB. Its caching mechanism still requires some improvement. Its central Indexing file becomes heavy after some time, causing performance issues for the entire database. Despite all these issues, it looks promising database alternative when opting for complete open-source database solutions.
