Setup a Raspberry Pi MYSQL Database

In this Raspberry Pi MYSQL tutorial, we will be showing you how to install and configure the MySQL server on your Pi.

MySQL is one of the world’s most popular relational database systems and is a common inclusion in most LAMP ( Linux, Apache, MYSQL, and PHP) stacks. It is one of the pieces of technology that helps drive the modern web.

A database such as MYSQL is often a key component of dynamic websites and is one of the best ways of storing data for web applications.

If you’re unfamiliar with MySQL, it is a relational database management system and allows you to store and maintain large amounts of data easily.

Set up something like PHPMyAdmin if you want a graphical user interface rather than the command line. It does make managing a database slightly easier.

You will need to go through this tutorial for setting up any web server project that requires a database. For example, we use it in our WordPress tutorial.

Equipment List

Below are the pieces of equipment that I made use of for this Raspberry Pi MySQL tutorial.

Optional

Raspberry Pi Case ( Amazon )
USB Keyboard ( Amazon )
USB Mouse ( Amazon )
HDMI Cable ( Amazon )

Video

Within this video, we will be walking you through the steps to setting up a MySQL server on the Raspberry Pi. We will also show you a couple of examples of how to make use of your new SQL server.

You can also check out our written version of this project by continuing to our steps below.

Adblock removing the video? Subscribe to premium for no-ads.

Setting up MYSQL on a Raspberry Pi

As with all of our tutorials, we will be utilizing the Raspbian operating system. If you’re using something different, then the steps may differ slightly.

1. Before we get started with installing MySQL to our Raspberry Pi, we must first update our package list and all installed packages.

We can do this by running the following two commands.

sudo apt update
sudo apt upgrade

2. The next step is to install the MySQL server software to your Raspberry Pi.

Installing MySQL to the Raspberry Pi is a simple process and can be done with the following command.

sudo apt install mariadb-server

3. With the MySQL server software installed on the Raspberry Pi, we will now need to secure it by setting a password for the “root” user.

By default, MySQL is installed without any password configured, meaning you can access the MySQL server without any authentication.

Run the following command to begin the MySQL securing process.

sudo mysql_secure_installation

Just follow the prompts to set a password for the root user and to secure your MySQL installation.

For a more secure installation, you should answer “Y” to all prompts when asked to answer “Y” or “N“.

These prompts will remove features that allows someone to gain access to the server easier.

Make sure you write down the password you set during this process as we will need to use it to access the MySQL server and create databases and users for software such as WordPress or PHPMyAdmin.

4. Now, if you want to access your Raspberry Pi’s MySQL server and start making changes to your databases, you can enter the following command.

sudo mysql -u root -p

5. You will be prompted to enter the password that we just created in step 3 for MySQL’s root user.

Note: Like most Linux password inputs, the text will not appear as you type.

6. You can now enter MYSQL commands to create, alter, and delete databases. Through this interface, you can also create or delete users and assign them the rights to manage any database.

7. There are two different ways you can quit out of the MYSQL command line. The first of those is to type “quit;” into the MySQL interface.

The other way of quitting out of the MYSQL command line is to press CTRL + D.

8. At this point, you will now have successfully set up MySQL on your Raspberry Pi. Our next few sections will go into making better use of this database.

Creating a MySQL Database and User

1. Before we proceed to create a MySQL user and database on our Raspberry Pi, we must first log back into the MySQL command-line tool.

Run the following command to log in to the MySQL command line. You will be prompted to enter the password for the “root” account that you set up earlier.

sudo mysql -u root -p

2. Let’s start by creating a MySQL database using the following command.

This command is super simple and is just “CREATE DATABASE” followed by the name that you want to give the database.

In our example, we will be calling this database “exampledb“.

CREATE DATABASE exampledb;

3. Next, we will create a MySQL user that we will assign to our new database. We can create this user by running the following command.

For this example, we will be calling the user “exampleuser” and giving it the password “pimylifeup“. When creating your own, make sure you replace both of these.

CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'pimylifeup';

4. With the user created, we can now go ahead and grant all privileges to the user so that it can interact with the database.

This command will grant all permissions to our “exampleuser” for all tables within our “exampledb” database.

GRANT ALL PRIVILEGES ON exampledb.* TO 'exampleuser'@'localhost';

5. The final thing we need to do for both our MySQL database and user to be finalized is to flush the privilege table. Without flushing the privilege table, the new user won’t be able to access the database.

We can do this by running the following command.

FLUSH PRIVILEGES;

If you rather not use the command line to administrate your databases then you can always install PHPMyAdmin instead.

Installing the PHP MySQL Connector

1. If you intend to use a MySQL database from PHP, you will need to ensure that you have the module installed.

You can install the MySQL connector for PHP to your Raspberry Pi by running the following command.

sudo apt install php-mysql

As I mentioned earlier, there are many projects where a database will come in handy. Most modern websites will require a database to be able to function correctly.

At this point of the tutorial, you should now have a MySQL server up and running on your Raspberry Pi. If you have run into any issues, feel free to drop a comment in the comments section below.

18 Comments

Andrew on October 2, 2022 at 9:30 am

Does the database automatically start when the system does?
I found issues with influx not starting correctly when my Pi booted.

1. Emmet on October 2, 2022 at 4:23 pm
  
  Editor
  Hi Andrew,
  
  The database server should automatically start at boot on your Raspberry Pi.
  
  If it doesn’t you can ensure this by enabling the service.
  
  sudo systemctl enable mariadb
  
  Cheers,
  Emmet
  
mike elder on October 2, 2021 at 7:52 pm

Can I install mysql on the same Pi as my webserver(Apache)?

1. Emmet on October 2, 2021 at 11:19 pm
  
  Editor
  
  Hi Mike,
  
  Yes you definitely can.
  
  Cheers,
  Emmet
  
RLBian on March 13, 2020 at 1:11 am

Using the given command, I get an error
import mysql.connector as mariadb

I get an error:

import mysql.connector as mariadb
ModuleNotFoundError: No module named ‘mysql’

1. Emmet on March 13, 2020 at 9:25 am
  
  Editor
  Hi RLBian,
  
  You need to first install the mysql connector for Python before trying to use it.
  
  You can do this by running one of the following commands.
  
  Python 2
  
  sudo pip install mysql-connector-python
  
  Python 3
  
  sudo pip3 install mysql-connector-python
  
  Cheers,
  Emmet
  
Sunil on January 9, 2020 at 7:06 pm

Thanks for wonderful explanation. We are using Python on R.Pi, and need to connect Python code with Maria DB. How to install connector for same, as you have explained here for PHP-sql connector.
thanks, Sunil

1. Gus on February 12, 2020 at 4:17 pm
  
  Editor
  
  In Python, you can use the MYSQL Python module. To import it, place the following line at the top of your file under the shebang (#!).
  
  import mysql.connector as mariadb
  
  Check out the MariaDB website to learn more about the Python connector.
  
RPI on January 8, 2020 at 4:44 am

Where do I get access to PHPMyAdmin?

1. Gus on January 8, 2020 at 10:35 pm
  
  Editor
  
  You will need to follow our PHPMyAdmin tutorial.
  
Morke on October 30, 2019 at 11:01 am

Hi, good tutorial
Why do you say mysql but install MariaDB Though?

1. Emmet on October 30, 2019 at 12:30 pm
  
  Editor
  
  Hi Morke,
  
  People tend to be more familiar with the MySQL name then MariaDB. Also this tutorial originally used to install MySQL however Raspbian only has MariaDB now by default.
  
  Cheers,
  Emmet
  
Robert on September 29, 2019 at 5:10 pm

Where are the commands typed into?

E.g.

sudo apt update

1. Gus on September 29, 2019 at 9:14 pm
  
  Editor
  
  The terminal application on Raspbian.
  
Ismael Ramirez on September 27, 2019 at 5:42 am

Would I be able to access this database remotely via a custom C# application I’ve coded?

1. Gus on October 7, 2019 at 10:37 pm
  
  Editor
  
  Yes, it should work just fine. Just make sure you allow external traffic to the database.
  
Bruce Lunde on July 7, 2019 at 11:57 am
I am new to mysql, but I cannot seem to get the syntax correct for the setup of privileges command as written:
```
GRANT ALL PRIVILEGES ON 'exampledb'.* TO 'exampleuser'@'localhost';
```
I was able to modify it:
```
GRANT ALL PRIVILEGES ON exampledb.* TO 'exampleuser'@'localhost';
```
1. Gus on July 10, 2019 at 11:05 pm
  
  Editor
  
  Thanks for picking up on this typo, we have updated the tutorial with the correct command.