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.
- 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.
- 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.
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.
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.
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:
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
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)
MariaDB/MySQL on Solus allows you to see the databases you have created by running this command:
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)
Apply the USE statement to change to the database you want. In your case, we will use testdb database.
mysql> USE testdb; Database changed
Let’s create a new user in the current database using this statement:
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)
For changes to take effect immediately, use the FLUSH PRIVILEGES statement.
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
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
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.