MariaDB & PMA

MariaDB is the open source version of MySQL that is recommended for use on a Raspberry Pi. MySQL isn’t compiled to be working on ARM processors.
MariaDB is a direct replacement for MySQL.

PMA, also known as PHP My Admin is a way to manage databases without the need to get into the terminal and doesn’t require any knowledge about SQL.

MariaDB is one of the most common service used on Raspberry Pi.
Since you came to this page I’ll assume you have problems installing and configuring it properly.
Installing MariaDB is easy, just use the raspbian repository.

sudo apt update && sudo apt upgrade
sudo apt install mariadb-server

Afterwards you’ll want to create a new user and a database.
That’s it for the main steps, but lets take it step by step.
But before we go into the how, lets talk about the what.
What is MariaDB?

What is a database?

You can look at a database as a location or file where you store all your data you need to keep.
For example, most websites (like this one) use a database to store there data, like this post, comments and website configuration.

A database can also explained as a folder on you PC, in it many excel files.
The folder would be the database, and each excel file can be seen as a table.
The table contains one column for each data to store.

What is MariaDB?

MariaDB is the service that hosts and controls the database. As Apache is used to host a web server, MariaDB is used to store data in databases.

It’s a free service that is available on any Linux distribution.

Are MariaDB and MySQL different?

MariaDB is a fork from MySQL, so for now there is not much difference between the too.
MariaDB has been created following the MySQL acquisition by Oracle in 2009, thus highly compatible with MySQL. Most of the of the MySQL project will be compatible with MariaDB without the need of change.

MariaDB installation on RPi

Lets finely start with the installation of our database service.

Server instalation

As said in the introduction, MariaDB is available in the Raspbian repository, so the installation is straightforward, but always start with an upgraded system so lets start there:

sudo apt update && sudo apt upgrade

After the upgrade we are ready to install the database:

sudo apt install mariadb-server -y

After a few seconds, the installation process is complete and MariaDB is almost ready to use.

If you have noticed it, the installation of MariaDB has also installed the MariaDB client
This will allow you to connect from the command line with: mysql

But by default, there is no account available to connect
That’s why we need to create root access first

Root access

Lets define the password for the root account in order to acces our database sevice.

sydo mysql_secure_installation

After you entered this command you’ll be asked for a password. As there is none just press enter.

Press “Y” to set a new password, and enter the password you’d like.
And Press “Y” three times more to: Remove anonymous users, Dissalow root login from remote computers and remove the test database.

Finely press “Y” to reload the User database.

Connect with root

sudo mysql -uroot -p

This simple commands allows you to login as user root after entering the root password as requested by the terminal. Now that we are connected we can create a database and a new user and other commands:

• SHOW DATABASES;
• USE <database>;
• SHOW TABLES;
• SELECT * FROM <table>;
• UPDATE, INSERT, DELETE, …

Create a new user

As on any system it is not recommended to use the root user for any application. Keep that user for yourself to administrate and debuging. But for all other applications you’ll want to create other users, with access to only one specific database.

Creating a user on MariaDB is like creating a user on MySQL so lets login, create a database, and a user.

sudo mysql -uroot -p

Now that we are once again logged in to the database we can create a database, create a user for that database and grant it admin permissions and finely reload the User-database and its permissions

CREATE DATABASE <dbname>;
CREATE USER '<username>'@'localhost' IDENTFIED BY '<password>';
GRANT ALL PRIVILEGES ON <dbname>.* TO '<username>'@'localhost';
FLUSH PRIVILEGES;

First we created the new database, change <dbname> with the database name. Afterwards we can create a user, change <username> and <password> accordingly, and give it only access from the local machine.
Now that we have both the database and the user we can give the user the required access by granting all privileges on the database.
Last but not least we must reload the privileges table for the changes to take effect.

Test the connection

You can now check whether the user has connection to the database:

sudo mysql -u<username> -p

Hope you remember the password you entered above?

Installing PHPMyAdmin

PHPMyAdmin (PMA) is used to avoid using the MySQL console for each operation.
It allows for easy access to the databases, user tables, and creating/editing/removing them.
It’s a free web interface that can be installed on the Raspberry Pi to do all the basic operations on you database.

PMA doesn’t need to be installed on the same machine as the database to work. It can be used to access remote database servers so you only need one instance of PMA.

Unfortunately there is no one line command to install PMA as it requires a web hosting service and an active PHP installation so leats break it down:

Installing Apache

The first step to install PMA is to install the webhosting service Apache.

sudo apt install apache2

After installation we can check if the service is working by opening it in our browser:

• Find the IP addres of you RPi
• Open your web browser and go to http://<RPi IP> (eg http://192.168.1.15)
• check that a page like this appears

If you can see this page, all is well and we can continue to install PHP. If not you might want to double check the installation.

Installing PHP

We need 2 php packages from the repository, php and libapache2-mod-php:

sudo apt install php libapache2-mod-php -y

The first one adds the core package of PHP on to your RPi, the second allows us to use it with out webservice.

We’ll check if everything is fine with our installation before going further.
Follow these steps to create a PHP file and test it in your browser:

• Go to the Apache web folder:
  cd /var/www/html
• Create a PHP file:
  sudo nano test.php
• Copy this code into it:
  <?php phpinfo(); ?>
  This is a basic PHP function to display PHP configuration in the browser.
• Save and Exit (CTRL+O, CTRL+X)
• Go to this URL with your web browser: http://<RASPBERRY IP>/test.php
• This should display something like this:
  

If you see this page, everything is OK, your Apache and PHP web server is ready.
Move to the next chapter if needed.

Installing PMA

Finely we come at the part we spoke about.
Now that we have a webservice running that is capable of running a PHP website we can install PHPMyAdmin wit a single command line:

sudo apt install phpmyadmin

• During the installation process, select these options:
  • Select Apache2 (press space and enter)
  • Configure the database for PHPMyAdmin with db-common: No
• After the installation, go to http://<RASPBERRY IP>/phpmyadmin
• Log in with the user created before
• You’ll find our database in the menu, with the table inside and the data on the right. Something like this:
• You’ll also find intuitive menu to do everything with

PHPMyAdmin

Previously in this post, we made all the changes in the MySQL database manually, doing requests to create users, grant access or insert data.
But there is a popular tool to do this faster and intuitively: PHPMyAdmin.
It’s a free tool, providing a web interface to manage your MySQL server.

To install PHPMyAdmin, follow these steps:

• Install the package with apt:
  sudo apt install phpmyadmin
• During the installation process, select these options:
  • Select Apache2 (press space and enter)
  • Configure the database for PHPMyAdmin with db-common: No
• After the installation, go to http://<RASPBERRY IP>/phpmyadmin
• Log in with the user created before
• You’ll find our database in the menu, with the table inside and the data on the right
  Something like this:
  
• You’ll also find intuitive menu to do everything within your database

Conclusion

Now that’s it, you now know how to install a MariaDB on your Raspberry Pi (or any linux system for that mater), and how to manage it on a daily basis using PHPMyAdmin.