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.
You should read it
- How to install MySQL on Ubuntu 20.04
- How to Send Sql Queries to Mysql from the Command Line
- How to Connect to MySQL Using PHP
- Instructions for installing MySQL on Windows and remote access
- Backup and restore MySQL with mysql-zrm on Debian Sarge
- 11 best MySQL monitoring tools for adjusting and managing SQL Server performance
- Instructions on how to connect to MySQL Server in IntelliJ
- MySQL vulnerabilities allow malicious servers to steal data from customers
- How to Install the MySQL Database Server on Your Windows PC
- How to Learn PHP and MySQL
- Instructions on how to connect to MySQL Database in Eclipse
- Guide to creating Virtual Hosting with PureFTPd and MySQL
Maybe you are interested
Instructions on how to install MySQL on Windows 11 computers
Best tools for designing and administering Mysql databases
Steps to Import data from different tables in MySQL Workbench
Instructions on how to connect to MySQL Server in IntelliJ
Instructions on how to connect to MySQL Database in Eclipse
How to install MySQL Workbench Community Edition on Windows 10