Free and Open Source database server for Arch Linux

 MariaDB install and configuration on Arch Linux.

 

Few days back i switched to Arch Linux and was looking for an alternative for Microsoft SQL Server developer edition. My initial setup for sql server on linux was SQL server + Azure data studio to graphically manage databases. reason for looking for alternative was i had them installed via AUR and after every update the apps fail to work frequently

and had to downgrade the dependent package and tell pacman to not check for updated version for this package. though the issue were resolved as soon as possible by the maintainers. So i decided if i can get a reliable setup using opensource apps.popular sql servers were Postgres, MySql,MariaDB and i choose mariaDB simply because Arch recommends it and its lightweight and simple.

MariaDB as defined on Arch site,

 Installation :

Installation is simple using pacman command,

sudo pacman -S mariadb

once install is finished its time to configure it, but before that i need to disable copy on write for the location where database will be stored. My root partition type is btrfs file system and this step is applicable for my system. default database directory is /var/lib/mysql to disable CoW below is the command.

sudo chattr +C /var/lib/mysql

next we need to run the following command before starting the mariadb service. you can change the default directory if required and do not forget to disable CoW if you are on btrfs file system.

sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

next enable and start the service using systemctl command.

sudo systemctl enable mariadb

sudo systemctl start mariadb

you can check the status using, sudo systemctl status mariadb

now mariadb is installed and we are left with an account without a password which is not recommended, now we need to secure the installation.

sudo mysql_secure_installation

we have to set root password, disable remote login for security, and drop test databases.it will look similar to below.

You already have your root account protected, so you can safely answer 'n'.Switch to unix_socket authentication [Y/n] n
 ... skipping.
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] n
 ... skipping.
Remove anonymous users? [Y/n] Y
 ... Success!
Disallow root login remotely? [Y/n] Y
 ... 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] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reload privilege tables now? [Y/n] Y
 ... Success!

 Configuration: 

we will now create and configure user to connect to database with following commands.

login as root with mariadb -u root -p

next create a user, change the hilighted field as per you user name/password.

MariaDB> CREATE USER 'mj'@'localhost' IDENTIFIED BY 'Password';
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'mj'@'localhost';

MariaDB> FLUSH PRIVILEGES;
MariaDB> quit

in the above command we are creating a user name mj on localhost and access to all databases under mydb schema.

Graphical Tool to manage database and load sample DB:

there are various GUI available to manage mariadb like phpmyadmin a web tool for mysql which may also work for mariadb, we will install another popular free and opensource software called DBeaver. which can be installed with pamcan using command sudo pacman -S dbeaver

once its installed we need to provide creadentials of user we created earlier to login.

click on create new connection and select the database driver as mariadb.

 

Next enter user details and Dbeaver will sugest to install jdbc drivers if not present, you can click ok to install.

important info is you cannot use root account to login to GUI, you must always use user account to login which we created earlier, login with root will not work and no databses will be accessible from GUI.

  once we are done enter user details we can check if connection is successfull using test conection. if all ok we can click on finish.

now we have connected graphically to mariadb server using DBeaver app with user mj.

Loading Sample Database:

https://www.mariadbtutorial.com is a good site to brush up mariadb basics.

you can download the sample nation database from mariadb tutorial site. now extract the archive file and login to mariadb as root with sudo mysql -u root -p

next copy the path to extracted .sql file, as i have extracted  in my downloads folder so will load this file with command.

source /home/mj/Downloads/nation.sql 

once the database is loaded we can check with show databses command as below.

 

Note that i have used limit statement of mariadb to limit rows to 3, which is equivalent for TOP statement in MS SQL Server.

now that we have successfully loaded the nation database using root login, next we have to provide access to user mj. as you can recall we used user mj creadentials to login to GUI. we can provide access to user as follows. as you can see there are no databses showing for user mj in DBeaver as of now.

Grant Privilege to user:

First lets get the list of users - select user from mysql.user;

next to grant all previleges to user mj - GRANT ALL PRIVILEGES ON *.* TO 'mj'@localhost IDENTIFIED BY 'password';

this will give user mj access to all databses and tables. to provide access to specific database you can use-GRANT ALL PRIVILEGES ON 'nation'.* TO 'mj'@localhost;  

running flush privileges is crucial after making any changes.

and finally you can check by show grant statement.

below snap show list user, grant/show/revoke previliges for user mj.

 

once access to nation table is granted successfully you need to re connect to server for changes to reflect and now the database and tableshould be visible for quering.

we can provide read only access to user with grant select instead.

 

 

below is the error you get when you try to update/alter if you have only select access.

 

Now we have reached the end of post, its worth mentioning that mariaDB is a good alternative for SQL Server on Linux and as far as the GUI DBeaver it offers very basic funtionalities when compared to Azure data studio/SQL Server management studio

but its a clean and simple alternative tool to manage databases graphically if you are not comfortable with sql shell.