Deploy MariaDB on Headless Raspberry Pi via SSH

I want to note my MariaDB deployment process on Raspberry Pi, step by step.

First of all my Raspberry Pi doesn't have a display. So I had to connect it via SSH connection.
I have followed processes below to connect my headless Raspberry Pi:
https://desertbot.io/blog/headless-raspberry-pi-3-bplus-ssh-wifi-setup

After the configurations, to find IP address of Raspberry Pi via my personal computer, I used "arp-scan" program.
I installed "arp-scan" on Ubuntu:
- sudo apt install arp-scan
I saw the list of connected devices on my LAN with the command below:
- sudo arp-scan --localnet
The name of my Raspberry Pi device is seen as "Raspberry Pi Foundation".

To establish SSH connection;
- ssh pi@192.168.1.19        // Because the IP of my device is 192.168.1.19
 
Now I can connect to Raspberry Pi.

After the connection has been established:

I reviewed the page below:
https://linuxize.com/post/how-to-install-mariadb-on-debian-9/

Steps:
1- We must make "apt" packet update.
$ sudo apt update
I don't prefer to make  "upgrade" also to "apt" after "update", because sometimes while "upgrade" operation, some errors occur.

2- I install MariaDB Server after "apt" packet update is done using the command below:
$ sudo apt install mariadb-server

MariaDB service starts automatically. I control the status of the service using the command below:
$ sudo systemctl status mariadb

In the text that comes as output;
- Active:           // in this line
- Active: active    // I see this. Probably there is no problem.


As for that I've completed the installation, I can run secure installation.
I use the command below for Secure Installation;
$ sudo mysql_secure_installation

When the command runs, it asks me "root" password. After I determined my password, terminal asked me whether I want to change my password or not, because I don't want to change, I said that question 'n', and then I said the questions remaining 'y'.
NOT: After I say 'y' to the questions anonymous users are deleted, remote access to database as "root" is closed, sample databases are deleted and server flushes the privileges.

Because I've completed the process, my MariaDB database server is safer anymore.

Now I can enter MariaDB database server monitor.
$ sudo mysql -u root -p          # we type the username after we say -u

After I type my password correctly, MariaDB terminal comes.

Now first of all I create the database that I will use.
MariaDB> CREATE DATABASE dbname;

To control whether the database is created or not;
MariaDB> show databases;

After I create the database, because I want to manage the database, I need to create a database user and I need to grant privileges on it. I use the page below for this:
https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

I create the user and grant the privileges.
MariaDB> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

MariaDB> GRANT ALL PRIVILEGES ON dbname.* TO 'newuser'@'localhost';

I use the command below to understand whether the user was created or not:
MariaDB> SELECT user, host FROM mysql.user;

NOT: If we forget to flush privileges, then we probably see problems.

MariaDB> FLUSH PRIVILEGES;

I approved privileges immediately by using "FLUSH PRIVILEGES" command.

I can exit from MariaDB terminal now.
MariaDB> exit;


It's that simple!










I can control the database using MySQL Workbench which I installed on my PC, over "Standard TCP/IP over SSH" Connection Method remotely anymore.


For the Turkish version of this page:
https://isakilikya.blogspot.com/2020/02/ekransiz-raspberry-pi-uzerinde-mariadb.html