Database Setup

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. Now, to access your Raspberry Pi’s MySQL server and start making changes to databases, you can enter the following command.

sudo mysql -u root -p

4. As we have not setup any password for root, it will log you in. Note: Like most Linux password inputs, the text will not show up as you type.

5. 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.

6. 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 setup 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 project, we will be calling this database “dreamHacker“.

CREATE DATABASE dreamHacker; 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 project, Please check DB File for username and password.

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 “user” for all tables within our “dreamHacker” database.

GRANT ALL PRIVILEGES ON dreamHacker.* 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;