A database is a collection of interrelated data that is easy to manipulate, update or manage. In other terms, a database is used to store, maintain and access any sort of data or information. Note that, databases need software for managing them. In that case, a database management system (DBMS) is used as a tool that makes it possible to manage databases. DBMS provides an interface for certain operations such as database creation, updating of data, and upgrades. In this guide, we will cover two major DBMS on Solus known as MySQL and MariaDB Database.

What is MySQL?

MySQL is a well-known open-source relational SQL database management system(DBMS).MySQL was launched in 1995 thus it’s among the top most used Relational DBMS, especially in the field of web-based software development. Note that MySQL can be run on different operating systems such as Windows, Linus, or Unix.

MySQL Features

  • Open Source: MySQL is free to download and easily available.
  • High compatibility: It supports a wide range of operating systems, i.e Windows, Linux, and Unix.
  • Secure: Solid data protection by use of encryption.
  • Easy of Use: With basic knowledge of SQL, you will interact with MySQL using simple SQL statements.
  • Memory: MySQL has efficient memory due to the low memory leakage feature.

What is MariaDB?

MariaDB is a relational database management system.MariaDB was forked from MSQL in 2009 and has the intention of staying free and open source following GNU General Public Licence. Additionally, the protocols and APIs found in MySQL are also found in MariaDB.As a relational DBMS MariaDB manages predefined relationships between data items.

MariaDB Features

  • Galera Cluster: Provides high-up time and prevention of data loss.
  • Supports PHP: PHP is among the popularly used programming languages.
  • Free and open source
  • Licensed under LGPL, GPL, or BSD.
  • High-performance storage engines.
  • User friendly: Uses a standard and popular querying language.

Installing MySQL on Solus Linux

In this part, I will take through the installation process for MySQL.Let’s start by upgrading our Solus repositories using the following command.

sudo eopkg up

We are going to install Mysql using Docker. If you don’t have it on your system, install it first.

sudo eopkg install docker

After the installation of Docker is successful, we will pull the MySQL image from the docker hub.

sudo docker pull cytopia/mysql-8.0

Docker will start downloading MyQSL latest image as illustrated below.

Using default tag: latest
latest: Pulling from cytopia/mysql-8.0
74f0853ba93b: Pull complete 
42781f05bd9c: Pull complete 
cb6ae9ffe143: Pull complete 
75dbb89aea84: Pull complete 
05910d1a2f8b: Pull complete 
ff2e6187da80: Pull complete 
37b51b61fd86: Pull complete 
967cd40e74d4: Pull complete 
Digest: sha256:3c3839992eb4df68a68250f6d9bd887f2de3f48918bda0408caa4f3d9366dc35
Status: Downloaded newer image for cytopia/mysql-8.0:latest
docker.io/cytopia/mysql-8.0:latest

Storing MySQL Files

To store the MySQL files, we will create a directory by the name superstore where data items will be stored.

sudo mkdir superstore

We will change the current working directory to superstore using the cd command.

cd superstore

Download a product-new CSV file using curl in the current directory.

curl -O https://weclouddata.s3.amazonaws.com/datasets/retail/superstore/product_new.csv 

Exit from the superstore directory using the below command.

cd ..

In our next step, let’s deploy the docker image using the docker run command. In the same command, we will set the MySQL default password as root.

docker run -p 3307:3306 --name mysql8 -e MYSQL_ROOT_PASSWORD=root -d -v $(pwd)/superstore:/var/lib/mysql-files cytopia/mysql-8.0:latest

To allow MySQL access from the terminal, let’s SSH the container with bash shell access.

sudo docker exec -it mysql8 /bin/bash

Now, it is possible to use MySQL from the terminal, enter this command immediately after the above command.

mysql --local-infile -u root -p

Remember that the password is the word root as we set it.

$ sudo docker exec -it mysql8 /bin/bash
[[email protected] /]# mysql --local-infile -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.2-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Congratulations, you can now use MySQL on Solus to manage your databases.

Installing MariaDB on Solus Linux

An alternative for MySQL is MariaDB. On Solus operating system, install it using this command.

sudo eopkg install mariadb-server

MariaDB will be installed successfully.

mariadb-server-10.6.7-25-1-x86_64.eopkg [cached]
Installing mariadb-server, version 10.6.7, release 25
Extracting the files of mariadb-server
Installed mariadb-server

MariaDB Service Management

Start MariaDB on Solus from the terminal.

sudo systemctl start mariadb.service

Next, enable the MariaDB service.

sudo systemctl enable mariadb.service

The results after enabling MariaDB should look like this:

Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

To ensure that MariaDB is up and running, use this command:

systemctl status mariadb.service

Here is the status of the MariaDB we just installed:

● mariadb.service - MariaDB 10.6.7 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2022-04-26 20:17:25 EAT; 3min 31s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 6225 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 8 (limit: 4647)
     Memory: 61.0M
        CPU: 443ms
     CGroup: /system.slice/mariadb.service
             └─6225 /usr/sbin/mariadbd

Secure MySQL Database Server

For both MariaDB and MySQL, we ensure secure installation by the use of the following command. It allows us to set passwords plus other settings.

sudo mysql_secure_installation

After the above command has been executed, you can set a new password for your MariaDB or MySQL.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..

After changing your password, continue with the process of MySQL secure installation. You will be given choices for the available settings. Choose Yes or No. To maintain default MySQL or MariaDB security settings, click the enter button.

Remove anonymous users? [Y/n] 
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] 
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] 
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] 
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Using MariaDB/MySQL database on Solus

Let’s see how we can use MariaDB or MySQL in managing databases. First, log in to the MySQL server.

For MariaDB use these commands:

sudo mysql

For MySQL which we installed via Docker, use these commands. The password is root as we set it or any other password you set during installation.

sudo docker exec -it mysql8 /bin/bash
mysql --local-infile -u root -p

Creating Databases

After you have logged into MySQL or MariaDB, you can now create databases. For instance, we will create a testdb database.

CREATE DATABASE testdb;

After successfully creating your database, you should see these results.

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.01 sec)

Show Databases

MariaDB/MySQL on Solus allows you to see the databases you have created by running this command:

SHOW DATABASES;

You can see the testdb database we have created and others.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.01 sec)

Select Database

Apply the USE statement to change to the database you want. In your case, we will use testdb database.

mysql> USE testdb;
Database changed

Create User

Let’s create a new user in the current database using this statement:

CREATE USER

For example, we will create a user by the name Mary and assign Mary123 as the password.

mysql> CREATE USER 'Mary'@'localhost' IDENTIFIED BY 'Mary123';
Query OK, 0 rows affected (0.00 sec)

Grant User Permissions

It is possible to give a user permission to access a database using GRANT ALL PRIVILEGES statements. Here is an example.

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'Mary'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Flush Privileges

For changes to take effect immediately, use the FLUSH PRIVILEGES statement.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Drop Database

To delete an entire database, use the DROP. Let’s delete the testdb database we created.

mysql> DROP DATABASE testdb;
Query OK, 0 rows affected (0.01 sec)

By using the SHOW statement, you will see that the database has been removed.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

To exit from either MySQL or MariaDB, type exit and hit the Etter button.

mysql> exit
Bye

Conclusion

A database is an essential tool when it comes to storage and managing data. To manage and communicate with databases efficiently, we will require a database management system. In this guide, we have covered the installation and usage of MySQL and MariaDB database management systems. These are popular DBMS that you will require while interacting with databases. Enjoy installing and using MySQL and MariaDB on Solus.

More Guides:

LEAVE A REPLY

Please enter your comment!
Please enter your name here