How to Install MariaDB on Raspberry Pi? (MySQL Server)

how to install mariadb on raspberry pi

MariaDB is one of the most common service used on Raspberry Pi
But I know that some of you have problems installing and configuring it properly
So, I’m writing a specific tutorial on how to install it

How to install MariaDB on a Raspberry Pi?
MariaDB is available in the Raspbian repository, so you can install it with: “apt install mariadb-server”
Then you’ll need to create a new user and a database
And finally you can configure the server in the /etc/mysql folder

That’s it for the main steps
In the following of this tutorial, I will guide you through each step so you’ll know exactly what to do

What is MariaDB?

Let’s start by a short explanation of what is MariaDB for those who may need this

What is a database?

The name is almost explicit
But if you are starting on MySQL, imagine a database as file where you will store any data you need to keep
For example, this website use a database to store the posts content, the comments or the website configuration

I often explain a database as a folder on your computer, with many excel files in it
The folder is the database, each Excel file is a table, and they contain one column for each data to store

What is MariaDB?

MariaDB is the service that will host the database
As Apache is used to host a web server, MariaDB is used to store data in databases

It’s a free service, available on any Linux distribution
MariaDB is a young project, started in 2009 and now supported by major companies like Google and Alibaba

It’s not yet one of the most popular database engine (0.58% market share according to Datanyze), but it’s growing fast
Big companies like Google, Mozilla and Wikipedia are using it

Difference between MariaDB and MySQL?

MariaDB is a fork of MySQL (= they create it from a copy of the MySQL source code)
So, for now there is almost no difference

MariaDB has been created following the MySQL acquisition by Oracle in 2009
So, it’s highly compatible with MySQL
Most of the projects will work on MariaDB without requiring any changes

How to install MariaDB on Raspbian?

Now that you have more information about what is MariaDB, you can try to install it by reading the following instructions

Server instalation

As said in the introduction, MariaDB is available in the Raspbian repository, so the installation is straightforward:

  • As always, start by updating your system:
    sudo apt update
    sudo apt upgrade
  • Then you can install MariaDB with this command:
    sudo apt install mariadb-server
  • Type “Y” and Enter to continue
    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

Here is how to define the password for the root user and start to use MariaDB:

  • Enter this command:
    sudo mysql_secure_installation
  • A message like this appears:
  • Press enter to continue (no password by default)
  • Then type “Y” to set a new password, and enter the password of your choice
  • Now, press “Y” three times to:
    • Remove anonymous users
    • Disallow root login remotely
    • Remove the test database
  • And finally, press “Y” again to reload the privileges

That’s it, this time MariaDB is ready to use with to root login

Connect with root

You can now use the mysql command for your first connection:

mysql -uroot -p
Then enter the password you have defined previously
And that’s it, you are connected to the MySQL server

Once you are connected, you can use all the MySQL queries like these:

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

You’ll check the syntax and all the information on the official documentation

Create a new MariaDB user

As on your system, it’s not recommended using the root user for any application
Keep it for you, for administration and debugging on a local usage
But for any other applications, you will probably create new users, with access to only one specific database

Create a new user on MariaDB

Here is how to do this

  • Connect to the MySQL command line:
    sudo mysql -uroot -p
  • Create a new database:
    CREATE DATABASE <dbname>;
    In this step and the following, replace all variables between <…> by the name you want to use
  • Now, create the new user:
    CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
  • Then, allow this new user to do anything on the database we just created:
    GRANT ALL PRIVILEGES ON <dbname>.* TO '<username>'@'localhost';
  • Finally, reload the permissions with:
    FLUSH PRIVILEGES;

Test the connection

You can now check that everything works fine with:
sudo mysql -u<username> -p
And use the password you just created

That’s it, you can now do everything you want on the new database
You can for example use this to host a website on your Raspberry Pi, or even to host apps like NextCloud or WordPress at home

Further configuration

You can do most of the MySQL configuration in the command line, but it’s not always easy if you don’t know the MySQL language
So, I’ll show you here two ways to configure your server

/etc/mysql

As with many services, you’ll find a configuration file in the /etc folder
It’s the first way to change the configuration

Here are the files you’ll find under /etc/mysql:

├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── debian.cnf
├── debian-start
├── mariadb.cnf
├── mariadb.conf.d
│   ├── 50-client.cnf
│   ├── 50-mysql-clients.cnf
│   ├── 50-mysqld_safe.cnf
│   └── 50-server.cnf
├── my.cnf -> /etc/alternatives/my.cnf
└── my.cnf.fallback

As you use MariaDB, you need to edit the files under mariadb.conf.d

  • For the server configuration, open the 50-server.cnf file like this:
    sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
  • In this file you can find many configuration options
    Each line uses the same format:
    option = value
    Lines starting with a # are commented, you need to remove this character to change the value
  • For example, you can change the bind-address to allow remote connections, or the datadir to move the databases to another directory (on a USB drive for example)

You can find all the information about this file on the MariaDB website

PHPMyAdmin

Even if the configuration file is mandatory to change the configuration, you’ll not change it everyday
But one thing that you’ll do more often is to create users, databases and table
Or just reading the data in the tables

phpmyadmin interface

You can install PhpMyAdmin to avoid using the MySQL console for each operation
PhpMyAdmin is a free web interface that you can install on your Raspberry Pi to do all the basic operations on your database

You can follow the step by step tutorial on this post to install it
This tutorial installs Apache and PHP before, but the PhpMyAdmin installation will add and configure them automatically

Conclusion

That’s it, you now know how to install MariaDB on Raspberry Pi, and also how to configure and manage it on a daily basis

If you need more information on this topic, let me know in the comments, so I can answer your questions and also write other posts on this subject
And as usual, thanks to share this post in your favorite social network if it was useful for you!

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 3

No votes so far! Be the first to rate this post.

As you found this post useful...

Spread the word!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Leave a Reply

Your email address will not be published. Required fields are marked *