Raspberry Pi is a compact single-board computer that, due to its size, price, and performance, has become a popular tool for various DIY projects, educational purposes, and also as a server for small applications. One of the possibilities of using Raspberry Pi is to run a database server. MySQL is a widely-used open-source relational database system that is ideal for small to medium-sized applications. In this article, we will explore how to install, set up, and manage MySQL database on Raspberry Pi.

Installing MySQL on Raspberry Pi

1. System Preparation

Before installing MySQL, it's important to ensure that your Raspberry Pi system is up-to-date. Run the following commands in the terminal to update and upgrade your system:

sudo apt-get update
sudo apt-get upgrade

2. Installing MySQL Server

To install the MySQL server on your Raspberry Pi, use the following command:

sudo apt-get install mysql-server

During installation, you may be prompted to set a password for the 'root' user of the MySQL database. It's important to choose a strong password.

3. Securing MySQL

After installation, it's recommended to run the MySQL security script, which will help you remove unnecessary default settings and secure your database server. Run:

sudo mysql_secure_installation

Follow the on-screen prompts, which will guide you through settings such as setting a strong password for the 'root' user, removing anonymous users, disabling remote root login, and deleting the test database.

Managing MySQL Database

1. Logging into MySQL

After completing the installation and security setup, log into MySQL using the command:

mysql -u root -p

After entering the 'root' user password, you'll be logged into the MySQL prompt.

2. Creating a New Database

To create a new database, use the SQL command:

CREATE DATABASE database_name;

Replace 'database_name' with the name of your database.

3. Creating a User and Granting Privileges

Security best practices recommend creating a new user for each database to avoid using the 'root' user for all operations. To create a new user and grant privileges to the database, use:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';
FLUSH PRIVILEGES;

Backup and Restore

Backing up your database is critically important. To create a backup, use:

mysqldump -u user -p database_name > database_backup.sql

To restore a backup, use:

mysql -u user -p database_name < database_backup.sql

 

Setting up and managing MySQL database on Raspberry Pi can be straightforward and direct if you follow the steps outlined in this article. This makes Raspberry Pi an efficient and affordable solution for small database projects, personal websites, and educational purposes.