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.
Install and configure MySQL server on Pi
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
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
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.
Just follow the prompts to set the password for the root user and to securely install MySQL. For a more secure installation, you should answer “Y” to all prompts when asked to answer either “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.
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
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.
6. Now, you can enter MYSQL commands to create, modify, and delete databases. Through this interface, you can also create or delete users and assign them permission to manage any database.
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 MYSQL command line is to press CTRL + D .
8. At this point, you have successfully set up MySQL on your Raspberry Pi.
Create MySQL database and user
1. Before proceeding 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
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, I'll call this database “exampledb”.
CREATE DATABASE exampledb;
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, we will call the user "exampleuser" and set the password "pimylifeup".
CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'pimylifeup';
4. With the user created, you can now go ahead and grant all the privileges that the user can interact with the database.
This command will grant all permissions for "exampleuser" to every table in the database "exampledb".
GRANT ALL PRIVILEGES ON exampledb.* TO 'exampleuser'@'localhost';
5. The last thing to do to complete 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.
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.
How to host your own website on a Raspberry Pi
How to install NOOBS on Raspberry Pi
How to share folders on the Pi using Apple Filing Protocol (AFP)
How to change DNS server on Raspberry Pi
Turn your Raspberry Pi into a Mac or PC with Twister OS
How to set up MiniDLNA on a Raspberry Pi
How to install CentOS on a Raspberry Pi