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.
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;
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
To provide access to all databases, replace
MariaDB [()]> GRANT ALL PRIVILEGES ON *.* to 'testuser'@'localhost';
8) Creating backup/dump of database
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
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
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;
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;
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;
- How to Configure Mariadb Maxscale Master-Slave with Galera Cluster
- How to Setup MariaDB Galera Cluster with HAproxy on CentOS 7
Thanks for reading this article and please drop your suggestion on the comment section.