Finally, the last chapter of building the Raspberry Pi development environment. We set up a DBMS on the Raspberry Pi.

A DBMS is a system that manages a DB (Database), a storage space for holding various data. It lets you store necessary data in the DB and retrieve it as needed to work with it. By linking a web server with a DBMS, you can store and use user account information, settings, and more.

With HTTP socket communication, by default, all data disappears the moment the request ends. In other words, it means every interaction that happens between my browser and the web vanishes the moment the page is closed. Because of this, when this kind of data needs to be preserved, a separate, permanent storage location is needed.

The simplest way to address this is that browsers provide storage like cookies and local/session storage. You can store and retrieve data through simple APIs in JavaScript, but there are several constraints that make it unsuitable as a primary storage solution.

1. There is a capacity limit, and that limit is quite small.
2. Since all data is stored as strings, there are constraints on the shape of the data.
3. Since the data is stored in the browser, the data is lost if the browser or computer changes.
4. If a different person on the same PC and same browser saves data, the previous data gets overwritten.
5. All users using the same browser on the same PC can access, modify, and delete the data without restriction.
6. There's no dedicated security mechanism for the storage.

There are several problems like these. The data's security isn't handled properly, and you can't store data per user either. In other words, it's not suitable for storing important data.

In other words, data needs to be stored in an external space unaffected by the browser. The simplest way is to think of building storage in the form of files like txt or csv within the server's file system. But even this approach has something missing in one way or another.

1. Write operations become very vulnerable to threading issues.
2. Read and write operations require search logic for the data, and depending on the complexity of the conditions, that search logic can become exponentially complicated.
3. When searching for data, you have to load the entire dataset, and the more data there is, the more resources are required for the search.

Most of the problems with cookies or browser storage are resolved, but data management issues still remain, and their scale is not something you can ignore. In other words, a lot of resources are required just to isolate the necessary data, making it unsuitable for handling large volumes of data.

A DBMS resolves most of the problems of the above storage solutions very neatly.

1. You can create multiple storage spaces within a single DBMS.
2. Only authorized requests can access the DBMS.
3. Reading and writing data is not affected by threading.
4. For relational databases (RDBMS), you can effectively read and write data through SQL scripts.
5. Through PL/SQL, the DBMS itself can execute functions and scheduling.

There are many other advantages, but if we list only the features related to the storage discussion above, they can be summarized as above.

In this way, having a DBMS is essentially essential for a web service to provide a richer variety of features.

In this chapter, we install MariaDB. I've used several DBMSs, but ORACLE, a representative RDBMS, felt a bit heavy to me. It also isn't particularly friendly to Linux environments. Requiring a GUI environment for installation, or requiring modification of kernel variables, is definitely a burden. As for MySQL, most of its developers have moved over to MariaDB anyway, and MariaDB is updated far more frequently, so it strongly felt like a backward-compatible subset of MariaDB.

NoSQL is a different beast from RDBMS entirely, and since most of the services I currently have in mind don't deal with unstructured data, it's out of scope here.

Also, since the basic functionality of an RDBMS is nearly the same whether it's ORACLE or MariaDB, there's no real difference when it comes to simple data CRUD or basic PL/SQL usage.

CRUD?
An initialism for Create, Read, Update, Delete — collectively referring to the operations you can perform on data: creation, reading, updating, and deletion.

BASH
sudo apt-get -y mariadb-server

Enter the above command to install MariaDB. It's simple. That's really all there is to it.

If you're curious, look up the process of installing ORACLE on Linux. You'll appreciate just how simple MariaDB is by comparison.

BASH
sudo mariadb

 # or
sudo mysql

Let's try connecting to MariaDB using the above command. For relatively recent versions (10 and above), MariaDB maps the system account to the MariaDB root account. Entering the above command lets you connect to MariaDB without entering a password.

You can also use sudo mysql to connect — this is a good illustration of how similar MariaDB and MySQL are.

If you connect as shown above, MariaDB is working correctly.

A DBMS operates command-based, just like Linux. But as with anything command-based, it's not the friendliest environment for users, so typing commands one by one gets tedious. Moreover, this CLI environment's downsides become especially clear when browsing data — it struggles hard to display output in a table format. Yet despite that effort, the console forcibly wraps text once it exceeds the screen width. Since there's no concept of horizontal scrolling at all, readability becomes terrible once the data gets long enough.

Because of this characteristic, even if the DBMS runs on a server, it's common to manage it using a management tool in a GUI environment like Windows. Especially if you don't want to see something like the image above.

There are two representative dedicated MariaDB management tools: MySQL Workbench and SQLyog. Either tool works fine, but this chapter explains things based on MySQL Workbench. Even though the name says MySQL, as mentioned earlier, since MariaDB is based on MySQL, the connection interface looks the same, so it can be used without issue.

SQLyog is split into a community version and an Ultimate version, with some advanced features restricted in the community version. These are mostly advanced features like simple database backups or copying to a different database. This restricts convenience features provided at the management tool level, not the DBMS's functionality itself, so it has no impact on regular DBMS use — no need to worry about that.

Visit the site and download MySQL Workbench.

Once the installation is complete... but at this point, entering the Raspberry Pi's IP still won't let you connect if the port isn't open.

If the port is blocked, you can't connect to MariaDB from Windows. Let's open the service port to allow external communication.

First, check which port it's served on. MariaDB's default service port is 3306, and you can check the service port by entering the following SQL in MariaDB.

BASH
mariadb -u root -p

Enter the above command in the console to access MariaDB.

SQL
SHOW GLOBAL VARIABLES LIKE 'PORT';

While connected to MariaDB, enter the above SQL to check the port. If you haven't changed the port, it will show 3306.

BASH
sudo ufw allow 3306

Enter the above command to allow port 3306.

Then enter the connection information in MySQL Workbench and try connecting. If you see something like the above, it's a success.

• Set up an Ubuntu server on the Raspberry Pi.
• Run Tomcat to host pages.
• Apply a domain.
• Provide HTTPS communication by issuing an SSL certificate.
• Set up remote communication environments such as SSH and RDP.
• Install MariaDB to handle DB communication.

With this, the Raspberry Pi's web development environment is fully set up. Now I'll be able to develop and deploy services however I want.