How to install and configure MySQL server on Pi

MySQL is one of the most popular database systems in the world and is included in most of the LAMP stacks (Linux, Apache, MYSQL, and PHP). It is one of the technologies that help promote the modern web.

Databases like MYSQL are often the primary component of dynamic web pages and one of the best ways to store data for web applications. MySQL is a database management system that allows you to store and maintain large amounts of data with ease.

Setting up MYSQL on Raspberry Pi

The article will use the Raspbian operating system. If you are using a different operating system, the steps may be slightly different.

Step 1. Before starting to install MySQL on the Raspberry Pi, update the list and all installed packages. You can do this by running the following two commands.

sudo apt update sudo apt upgrade

Step 2. The next step is to install the MySQL server software onto the Raspberry Pi. Installing MySQL into the Raspberry Pi is a simple process and can be done with the following command.

sudo apt install mariadb-server

Step 3. With the MySQL server software installed on your Raspberry Pi, you will now need to secure it by setting a password for the "root" user.

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

Run the following command to start the MySQL security process.

sudo mysql_secure_installation

Just follow the prompts to set the password for the root user and to securely install MySQL. For a more secure installation, it's a good idea to answer 'Y' to all prompts when asked to answer 'Y' or 'N'. These prompts will remove features that allow someone to access the server more easily.

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

Step 4. Now if you want to access the MySQL server of your Raspberry Pi and start making changes to your database, you can enter the following command.

sudo mysql -u root -p

Step 5. You will be prompted for the password you created in step 3 for the root user of MySQL.

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

Step 6. Now, you can enter MYSQL commands to create, modify, and delete the database. Through this interface, you can also create or delete users and assign them permission to manage any database.

Step 7. There are two different ways you can exit the MYSQL command line, the first is to type 'exit;' into the MySQL interface. Another way to exit the MYSQL command line is to press CTRL + D.

Step 8. At this point, you have successfully set up MySQL on your Raspberry Pi.

Create MySQL database and user

Step 1. Before you proceed to create the MySQL database and user on the Raspberry Pi, you must log back into the MySQL command line tool.

Run the following command to log into the MySQL command line. You will be prompted to enter the password for the 'root' account you set up earlier.

sudo mysql -u root -p

Step 2. Let's start by creating the MySQL database with the following command.

This command is very simple and contains only 'CREATE DATABASE' followed by the name you want to give the database. In this example, the post will call this database 'exampledb'.

CREATE DATABASE exampledb;

Step 3. Next, you will create a MySQL user to assign to your new database. You can create this user by running the following command.

For this example, the post will call the user 'exampleuser' and set the password 'pimylifeup'.

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

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

This command will grant all permissions to 'exampleuser' for every table in the 'exampledb' database.

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

Step 5. The last thing to do to perfect the MySQL database and user is to delete the privilege table. Without deleting the privilege table, new users will not be able to access the database.

You can do this by running the following command.

FLUSH PRIVILEGES;

If you don't want to use the command line to manage your database then you can always install PHPMyAdmin instead.

Install the MySQL connector for PHP

If you plan to use a MySQL database from PHP, you will need to make sure that you have this module installed. You can install the MySQL connector for PHP into your Raspberry Pi by running the following command.

sudo apt install php-mysql

There are many projects where the database will be of great help. Most modern websites will require a database in order to function correctly.

Isabella Humphrey

Update 08 October 2020