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


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.

If you are looking to quickly progress on Raspberry Pi, you can check out my e-book here. It’s a 30-day challenge, where you learn one new thing every day until you become a Raspberry Pi expert. The first third of the book teaches you the basics, but the following chapters include projects you can try on your own.

I answer your questions - Raspberry...
I answer your questions - Raspberry Pi FAQ

What is MariaDB?

Get My Cheat Sheet!
Grab your free PDF file with all the commands you need to know on Raspberry Pi!

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.

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

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

CrowPi2
Learn Python, Scratch, & more in an easier way
This all-in-one kit comes with a Raspberry Pi 4-based laptop with tons of sensors and motors under the keyboard.

Buy now Check my review

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,

Are you a bit lost in the Linux command line? Check this article first, for the most important commands to remember, and a free downloadable cheat sheet so you can have the commands at your fingertips.

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

Get My Cheat Sheet!
Grab your free PDF file with all the commands you need to know on Raspberry Pi!

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.

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.

Want to chat with other Raspberry Pi enthusiasts? Join the community, share your current projects and ask for help directly in the forums.

Conclusion

Get My Cheat Sheet!
Grab your free PDF file with all the commands you need to know on Raspberry Pi!

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:

If you need more information on this topic, feel free to send them in the community, 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!

Raspberry Pi Resources

Not sure where to start?
Understand everything about the Raspberry Pi, stop searching for help all the time, and finally enjoy completing your projects.
Watch the Raspberry Pi Bootcamp course now.

Master your Raspberry Pi in 30 days
Don’t want the basic stuff only? If you are looking for the best tips to become an expert on Raspberry Pi, this book is for you. Learn useful Linux skills and practice multiple projects with step-by-step guides.
Download the e-book.

VIP Community
If you just want to hang out with me and other Raspberry Pi fans, you can also join the community. I share exclusive tutorials and behind-the-scenes content there. Premium members can also visit the website without ads.
More details here.

Need help building something with Python?
Create, understand and improve any Python script for your Raspberry Pi.
Learn the essentials, step-by-step, without losing time understanding useless concepts.
Get the e-book now.

You can also find all my recommendations for tools and hardware on this page.

Patrick Fromaget

I'm the lead author and owner of RaspberryTips.com. My goal is to help you with your Raspberry Pi problems using detailed guides and tutorials. In real life, I'm a Linux system administrator with a web developer experience.

Recent Posts