Databases are always a great choice to add persistence to your project or application. You can write information in one session and still have the data next time you need to look.
A well-designed database can be efficient at looking up in large data sets. It’s relatively simple to have and set a structured database for CRUD (create, record, update and delete) applications, which is a popular pattern, particularly to the internet of things projects.
MySQL server is one of the world’s most common relational database systems. It’s dynamic and is among the technology interfaces that help to drive the modern web. A database like the MYSQL server is always a significant component of dynamic web platforms. It is one of the best ways of storing big data for intelligent projects and web applications.
It’s considered as the best among the open-source databases in terms of features and performance. Today, many IoT Solutions are designed using raspberry pi. Therefore, there must be an ideal database to store logs and data collected from all the connected devices. This guide’s main interest is to learn how to activate the remote connection to the MySQL Server database.
The remote connection can be handy if you have a server to store your data. Initiating the remote connection allows you to connect to MySQL service from an IP and manage its database without passing other services. This guide explains how to install a MySQL server on Raspberry Pi.
Steps to Follow When Installing MySQL Server on Raspberry Pi
A high-grade USB card with efficient speed or a UHS1 card is required to run a Rasbian OS for a successful MySQL Server installation. With database installation, you will have a full-featured platform that meets every application development requirement and deployment requirement.
This article describes how to configure and install a MySQL server on Raspberry pi. Connecting is comparatively straightforward. Designing the database is slightly applied but rather manageable. Follow the guidelines to join the server with other remote devices carefully.
Install MySQL Server
First, ensure that you have installed all the upgrades and updates before anything else. Run the command line below:
Sudo-apt-get_update-&&-sudo_apt-get-upgrade
Once the system has updated all the changes, you are set for MySQL server installation. Begin by executing the following code:
Sudo-apt_get _nstall-mysql-server&& -sudo-apt-get_install_mysql-client-
The execution prompts you to install both the client and the server. The client provides an easy connection to the server from the execution point. As the process progresses, a screen will pop-up as the installation continues to display the following:
Install MySQL Server on your Raspberry Pi
Continue and allocate a root password. The default administration account serves as the root account to give the locally connected server total control. A prompt to repeat will be shown for you to confirm the stated password. Proceed and type your initial password again. You will have to wait for a short while for the installation to complete.
Connecting locally to the MySQL Server
Now that you have successfully installed the MySQL server, try to connect it to the MySQL client and the server. Use the command below:
Mysql-root-p.
You will be provided with a dashboard to fill the password to connect the MySQL server again. The system is now ready as you are already logged in. Your screen will display the following text. Install MySQL on Raspbian
Type this program code:
Status;
The process will return a summary similar to the statement below:
MySQL-server on Raspbian OS.
It’s a summary to give the status of the server running on your Raspberry pi.
How to allow MySQL Server to have a Remote Connection to the Linked Raspberry Pi
Considering that you have already installed the server to the Raspberry Pi, you need to start a secure connection with your remote devices. The private link is useful when you have a server with no external database. To manage the database, you need to create a new user interface for effective remote application control.
Creating an enthusiastic MySQL Server User
First, you need to connect Raspberry Pi before connecting to the MySQL server to effectively manage remote databases. Nonetheless, take care as the remote assembly you are starting can increase the danger of threats and attacks. Thus, try to use a complex username and enhance security with a complicated password to avoid exposing user rights for your records.
A new operator ‘user’ by the password ‘pswrd’ will be created in this section. To complete the connection, you require to fix to MySQL server as root using the below program:
Mysql –u_root –p-
MySQL server password ‘root’ will be asked. It’s the password formed during the connection of the server to Raspberry Pi. You are now connected. Create the local database ‘test’:
CREATE DATABASE test;
Practice the order ‘GRANT’ to outline the privileges of the connected user.
GRANTALLPRIVILEGESON test. *TO user@’ %’ Identified BY ‘pswrd’;
Give every necessary right for your database and all records to the operator ‘user.’ The user will be able to connect at any point using the password ‘pswrd.’
Once the process is done, it’s a must you command the MySQL server to apply all the changes ready using the FLUSH code below:
Flush-privileges;
Now you can move out from the server with the code ‘exit.’
Configure MySQL Server to Enable Remote Connections using Raspberry Pi
As every right to access the database is granted, you have to inform the server to receive external connections through Raspberry Pi. Apply the command for a complete configuration:
Sudonano_/etc_/_mysql-/my.cnf
Comment on the bind-address line:
#bind-address_= 127-0-0-1
By default, your server is only listening to the local configurations (‘127-0-0-1’). Comment on the line and make it potent to clear this restriction, thus, recovering any external Raspberry Pi remote connections. Restart your MySQL server to apply the updates using the line below:
Etc_/-init.d-/mysqlrestart
Conclusion
Activating remote networks to servers is often faced by threats and attacks. To significantly reduce these attacks, it’s good to modify your server port. Ensure that all TCP ports are open for you to readdress the torrent to the Raspberry Pi. If you involve the mounting of an efficient database server, pick a quality SD card.