MariaDB is one of the most common services used on Raspberry Pi, it has now replaced MySQL on most distributions. But I know that some of you have problems installing and configuring it properly. That’s why I’m sharing a specific tutorial on how to install it on your Raspberry Pi.
MariaDB is available in the default Raspberry Pi OS repository, and can be installed with: “apt install mariadb-server” or via the package manager with the Desktop edition. The configuration can then be found in the /etc/mysql folder.
That’s it for the short answer. In this tutorial, I will guide you through each step, so you’ll know exactly what to do.
What is MariaDB?
Let’s start with a short explanation of what MariaDB is for those who may need this.
What is a database?
The name is almost explicit.
But if you are just starting on MySQL, imagine a database as file where you will store any data you need to keep.
For example, this website uses a database to store the content, comments, or website configuration of each post.
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.
Similar to how 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. It started in 2009 and is now supported by major companies like Google and Alibaba.
MariaDB is not the only database management system available on Raspberry Pi. You can find my top 5 recommendations here.
Difference between MariaDB and MySQL?
MariaDB is a fork of MySQL (= they created it from a copy of the MySQL source code).
So, for now, there’s barely any 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 Raspberry Pi OS
Now that you have more information about what MariaDB is, you can try installing it by reading the following instructions. Here are the required steps to install MariaDB on your Raspberry Pi:
- Install the mariadb-server package.
- Use root to access MariaDB for the first time.
- Complete the installation.
- Create a new user and password.
Let’s see how to do all of this in details.
Install MariaDB server
As said in the introduction, MariaDB is available in the Raspberry Pi OS repository, so the installation is straightforward. Open a terminal (or connect via SSH) and follow the instructions:
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’ve 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).
- Press “Y” to switch to unix_socket authentication.
- Then type “Y” to set a new password, and enter the password of your choice.
- Now, press “Y” three more 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 root login.
The “root” user is not enabled by default on Raspberry Pi OS, you can use “sudo” instead, and specify the username in the MySQL command. But if you really want to enable root, it’s possible. I explain how to do this in this article.
Connect with root
You can now use the mysql command for your first connection:
sudo 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 can 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 (it’s one of the main security tips I give you in this article).
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 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 won’t change it everyday.
But one thing that you’ll do more often is to create users, databases and tables.
Or just reading the data in the tables.
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.
By the way, MariaDB/MySQL isn’t the only option to host a database on your Raspberry Pi. Check the link to find the best alternatives you can try.
But installing MariaDB is often a prerequisite for most projects on Raspberry Pi, including:
- Set Up a LAMP Web Server On A Raspberry Pi In 2022
- How to Install an Email Server on your Raspberry Pi?
- How to install NextCloud on your Raspberry Pi? (2 ways)
And as usual, thanks to share this post in your favorite social network if it was useful for you!
