Setting Up and Securing MySQL on Raspberry Pi

This guide provides a comprehensive walkthrough of setting up MySQL on your Raspberry Pi, creating a database, and securing your setup. MySQL is a robust open-source relational database management system (RDBMS), ideal for managing data in various Raspberry Pi projects.

Table of Contents

1. Installing MySQL Server on Raspberry Pi
2. Securing your MySQL Installation
3. Creating a Database and User
4. Connecting to your MySQL Database

Installing MySQL Server on Raspberry Pi

Before starting, ensure your Raspberry Pi’s operating system is up-to-date:

sudo apt update
sudo apt upgrade

Now, install the MySQL server:

sudo apt install mysql-server

You’ll be prompted to create a strong root password. Remember this password carefully – it’s crucial for managing your MySQL instance. The installation might take a few minutes.

After installation, verify the server status:

sudo systemctl status mysql

You should see “active (running)”. If not, start and enable the service:

sudo systemctl start mysql
sudo systemctl enable mysql

Securing your MySQL Installation

Security is paramount. After installation, it is crucial to take steps to secure your MySQL server. The following steps are highly recommended:

1. Remove the anonymous user: This prevents unauthorized access.
2. Set a strong password for the root user: We did this during installation, but it is a good practice to review and change if needed.
3. Restrict remote root login: Never allow root login from remote machines. This is a major security risk.
4. Remove test databases and users: These are included for testing purposes and should be removed after installation.
5. Grant privileges carefully: Only give users the minimum permissions they need.

To perform these actions, use the mysql_secure_installation script:

sudo mysql_secure_installation

The script will guide you through each step.

Creating a Database and User

Access the MySQL command-line client:

sudo mysql

Enter the root password. Create a database:

CREATE DATABASE mydatabase;

Create a user and grant privileges (replace placeholders with your choices; **never** use “mypassword” in a production environment):

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

FLUSH PRIVILEGES ensures the changes are applied immediately. Exit the client:

EXIT;

Connecting to your MySQL Database

You can now connect using your preferred MySQL client (e.g., the command-line client or a GUI tool like phpMyAdmin) with the username “myuser” and your chosen password. Remember to always use strong, unique passwords.