To organize, store, and manipulate data there are different tools available, usually known as database tools. The significance of using such tools is that they provide a consistent and reliable way of accessing data, managing data, and ensuring data security, integrity, and quality. They support various data models, such as relational, non-relational, or hybrid, to suit different types of applications and use cases.
They offer various features and tools, such as query languages, indexing, transactions, backup and recovery, replication, and scalability, to optimize data performance and functionality. Usually, using Linux distributions like Arch Linux is a popular way for managing these databases and MariaDB is one of the most used database tools which is based on the relational database management system.
Contents:
- Install MariaDB on Arch Linux
- Use MariaDB on Arch Linux
- Remove MariaDB on Arch Linux
How To Install MariaDB on Arch Linux
To install MariaDB on Arch Linux there is only one way and that is by using its default package manager though it has a slightly old version. If you are looking for a newer version and using the Debian-based Linux distributions, then you can visit their official website for it. In the case of Arch Linux to install MariaDB execute:
Now initialize the MariaDB data directory and create the system tables in the MySQL database and for that execute:
It works by starting the MariaDB server’s mysqld process in –bootstrap mode and sending commands to create the system tables and their content. The options –user, –basedir, and –datadir specify the username that mysqld will run as, the installation directory, and the data directory, respectively. This command is for Linux/Unix only. Moreover, this command is also useful for setting up MariaDB on a new system or after upgrading from a previous version.
Next, activate the MariaDB service on Arch Linux and for first enable it and then start it using the systemctl command:
sudo systemctl start mariadb
The default installation of MariaDB leaves it insecure which can cause security breaches and for that, it is necessary to prevent unauthorized access to your database server and data. So, to configure security settings and improve MySQL or MariaDB server security on Linux systems execute:
Furthermore, it allows you to set a password for the root user, remove root and anonymous accounts, delete the test database, and reload user privileges tables. Now upon the execution of the command you will receive several prompts:
Switch to unix_socket authentication: If you want to use the unix_socket plugin to authenticate the root user by matching the system username and the MariaDB username. You can avoid passwords and be safer, but only for connections within the same network. The recommended answer is Yes if you only need to access MariaDB as root from the same machine, or No if you need to connect remotely or use a different system username.
Change the root password: If you have not set a password yet, or if you want to use a stronger password, you should answer Yes and enter a new password. Otherwise, you can answer No to keep the existing password.
Remove anonymous users: This prompt asks you if you want to remove the anonymous users that are created by default when you install MariaDB. Anonymous users can pose a security risk, as they can access any database that has not been explicitly protected. The recommended answer is Yes to delete the anonymous users.
Disallow remote login: This prompt asks you if you want to prevent the root user from logging in remotely. Allowing remote login for the root user can expose your MariaDB server to attacks from the network. The recommended answer is Yes to disable remote login for the root user.
Remove test database and access to it: This prompt asks you if you want to remove the test database that is created by default when you install MariaDB. The test database can be accessed by any user, even anonymous users, and can be used to test malicious queries or exploit vulnerabilities. The recommended answer is Yes to delete the test database and the privileges that allow access to it. However, you can enter No if you want to keep the test databases for testing and learning purposes.
Reload table privileges: This prompt asks you if you want to reload the table privileges after making the changes above. This is necessary to apply the new security settings to the MariaDB server. The recommended answer is Yes to reload the table privileges.
Now once you are done with the changes you can upgrade MariaDB if by any chance the version in the default repository is updated:
Now login to the MariaDB database tool using the root account, here if you have created any other user then replace the root with its username:
Now as you can see in the image above there is a warning for using mysql which says to use mariadb instead as it will be deprecated in the newer versions though still you can use it for logging in:
Once you are in the database server now you are all set and can create users, and databases and manage them, to list all the databases executed:
Note: As I have mentioned earlier the newer version file for MariaDB is available on its official site but on Arch Linux, there are some issues with the dependencies due to which it cannot be installed. This is because the dependencies required to install the tar.gz file on Arch Linux have been deprecated.
Here I have tried to install through yaourt and other AUR helpers on Arch, but the process wasn’t successful as the repository required for installation of yaourt on Arch is dead as you can see the error in the image below:
How to Use MariaDB on Arch Linux
MariaDB can power websites that need to handle large amounts of content, such as blogs, news portals, e-commerce sites, and more. It can handle log data from different kinds of sources, such as web servers, applications, and devices. MariaDB is free and open source under the GPLv2 license, which means you can use it without any restrictions or fees. Here I have explained some of the basics of using MariaDB:
1: Creating a Database in MariaDB
The database is primarily a set of data that is specifically about a single entity, for instance, a list of employees and their details of a company or any set of readings regarding any experiment. So, to gather all the data in one place you first have to create a database and execute:
To list all the databases created in MariaDB, just use the following command:
2: Create a User in MariaDB
Managing a big database by a single user can be a challenging task, so a database can be linked with different users, but these users can be given limited privileges. So, to create a user you need to give a username and password through which the user will access MariaDB:
Once you have created a user, you can log in to MariaDB by giving its username and password as in the syntax below:
You can also log in to MariaDB by using the p flag and giving the password at a later stage as this way is more secure because no one can see your database password:
Just like listing the databases in MariaDB, you can also list the users that have access to MariaDB on Arch Linux and for that execute:
3: Grant Privileges in MariaDB
Users in MariaDB require permissions to make changes in the databases so if you want a user to edit or add some data in the database then the respective user should have editing permissions. As in the image below User1 is trying to create a database but it doesn’t have any rights to it:
To grant the privileges to a user first, you have to log in using the administrator account and then list all the privileges that can be given to a user along with their command and for that execute:
Now from the privileges table, shortlist the privileges you want to give and then use the GRANT command along with the username and database name to give access to the respective user:
Now once you have given access to the user for the certain task on the database, log in to MariaDB using that user and execute the below command to see the given privileges:
Now as an administrator, you can list the users along with the databases along with their hostname through which they are allowed to access the MariaDB databases:
4: Granting Similar Privileges to Multiple Users
Usually, there might be cases where different databases are accessed by multiple users so in that case it becomes quite a lengthy task to grant each user the same set of rights. For that, it’s better to create a role in MariaDB that has all the privileges that are to be granted to other users and then use the role permissions that can be granted to the users.
Now I have created a role named MANAGER and granted it the same permissions using the GRANT command along with the database name:
As I have already created a user and to grant it privileges for the test database, I have granted the manager role to User2 by stating its username and password:
Usually, granting all the permissions is not recommended to any of the users except the administrator as it can compromise the security of the database. To give a user or root account full access, run this command:
Now if you want to remove all the privileges from a user in case you have assigned a role to the user then in that case execute:
Alternatively, you can also revoke the privileges of a user if you have assigned it manually and for that execute:
5: Removing Database in MariaDB
If you want to remove the database in MariaDB then login through the administrator account if the other users do not have permission for it and after that use the below syntax:
6: Removing a User in MariaDB
Further, if you want to delete a user from the MariaDB database on Arch Linux, then execute the drop command:
Furthermore, if you need more help with commands used for operating MariaDB then you can consult its help:
Note: Granting and revoking access to users for different databases should be done by logging in to the administrator account.
How to Remove MariaDB from Arch Linux
If you want to completely remove MariaDB from Arch Linux, then first you need to deactivate its service and for that execute:
Now to uninstall it from Arch using the default package manager along with the Rns flag as in the command below:
Next, remove the directory of MariaDB from Arch Linux and afterward reboot the system to apply the changes:
Conclusion
MariaDB is a database management tool that is open-source and free to use, to install it on Arch Linux use its default package installer. Once you have installed it, make it secure by executing the sudo mysql_secure_installation command. Compared to MySQL, MariaDB offers more features and capabilities, which makes it a preferred choice for most users.
I’m Aaliyan javaid, an electrical engineer and a passionate tech enthusiast. I have been working with embedded systems, programming, and Linux operating systems for over 2 years. I love to explore new technologies and share my knowledge and experience with others.