Setting Up MySQL/MariaDB

MariaDB is basically an open source fork of MySQL.

Installation

MariaDB can simply be installed via pacman with the following command.

$ pacman -S mariadb

resolving dependencies...
looking for conflicting packages...

Packages (5) jemalloc-1:5.3.0-3 liburing-2.4-1 mariadb-clients-11.1.2-1 mariadb-libs-11.1.2-1
mariadb-11.1.2-1

Total Download Size: 41.21 MiB
Total Installed Size: 300.52 MiB

:: Proceed with installation? [Y/n]

Setup

First, run the following setup command.

$ mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
OK

To start mariadbd at boot time you have to copy
support-files/mariadb.service to the right place for your system

Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mariadb-safe --datadir='/var/lib/mysql'

You can test the MariaDB daemon with mariadb-test-run.pl
cd '/usr/mariadb-test' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

Then, MariaDB must be started with systemctl. One can start the service every time it is needed (it will not restart upon boot), or one can enable it so that it always starts upon boot. Both are shown below.

$ systemctl start mariadb
$ systemctl enable mariadb

Then you can enter the MariaDB interface. The default password should be blank.

$ sudo mysql -u root -p

Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 11.1.2-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

A new user can be added.

MariaDB [(none)]> CREATE USER 'USER_NAME'@'localhost' IDENTIFIED BY 'PASSWORD';
Query OK, 0 rows affected (0.014 sec)
MariaDB [(none)]> MariaDB> GRANT ALL PRIVILEGES ON *.* TO 'USER_NAME'@'localhost';
Query OK, 0 rows affected (0.005 sec)
MariaDB [(none)]> MariaDB> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> MariaDB> quit
Bye

That user can then be used to login. If a blank password was set, then an empty password can be used to login.

$ mariadb -u USER_NAME -p

Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 11.1.2-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

Use

Once logged in, a new database can be created as follows.

MariaDB [(none)]> CREATE DATABASE myDataBase;

Query OK, 1 row affected (0.001 sec)

That database can be entered as follows.

MariaDB [(none)]> USE myDataBase;

Database changed

A table can be created as follows.

MariaDB [myDataBase]> CREATE TABLE exampleTable (
CREATE TABLE exampleTable (
-> ID char(5) not null,
-> name varchar(15) not null,
-> number int not noll,
-> primary key (ID));

Query OK, 0 rows affected (0.204 sec)

Data can be loaded from an external SQL file as follows.

MariaDB [myDataBase]> SOURCE database.sql

Query OK, 0 rows affected (0.116 sec)

Query OK, 0 rows affected (0.106 sec)

Query OK, 0 rows affected (0.092 sec)

Query OK, 0 rows affected (0.108 sec)

Query OK, 0 rows affected (0.100 sec)

Query OK, 0 rows affected (0.125 sec)

Query OK, 0 rows affected (0.108 sec)

Query OK, 0 rows affected (0.090 sec)

Query OK, 0 rows affected (0.117 sec)

Query OK, 0 rows affected (0.108 sec)

Query OK, 0 rows affected (0.092 sec)

All available tables can be shown as follows.

MariaDB [myDataBase]> SHOW TABLES;

+----------------------+
| Tables_in_university |
+----------------------+
| balloons |
| designs |
| clowns |
| circus |
| clubs |
| jugglers |
| elephants |
| rings |
| dancers |
| cannons |
| venues |
+----------------------+
11 rows in set (0.001 sec)

Previous	Published October 11, 2023