Source compile Apache2.4 + PHP7.4 with Raspberry Pi and build a web server --3. Use MySQL

Assumptions and preparations

Linux server building article

-Building a file server with Samba (CentOS 8.1 / openSUSE 15.1 / Ubuntu 20.04) -Source compilation of Apache2.4 + PHP7.4 on Linux-- 1. Apache introduction / [Raspberry Pi] / items / 67686eccaaec73251458) -Source compilation of Apache2.4 + PHP7.4 on Linux-- 2. PHP introduction / [[Raspberry Pi]](https://qiita.com/kazumi75kitty / items / 50f1a447f6ebc2ee2b61) -Source compilation of Apache2.4 + PHP7.4 on Linux --3 MySQL introduction / [Raspberry Pi: This article] -Apache2.4 + PHP7.4 on Linux --4 Security (chown and firewalld) -Build an IPsec gateway on Linux VPN-- 1. Introduce StrongSwan / [[Ubuntu 20.04 + Raspberry Pi]](https://qiita.com/kazumi75kitty/ items / 08259681247a6c2ebd0d) -Build an IPsec gateway on Linux for VPN-- 2. Check connection to VPN / [[Ubuntu 20.04 + Raspberry Pi]](https://qiita.com / kazumi75kitty / items / c83f920f052d83d62457)

Last time built a web application environment by source compiling PHP7.4 on Apache2.4, but ** Unfortunately, Raspberry Pi by default Linux for Linux does not support MySQL and does not exist in RPM or APT repositories. ** But it is possible to connect an external MySQL server.

Even if you want to build MySQL database with a web server for simplicity, that is not the case with Raspberry Pi, and even if you can install it, it is very unsuitable for writing microSD, so Raspberry Pi will be available in the future. If you develop a lineup that supports storage equivalent to M.2 SSD, it may be possible to support MySQL ... (✿ ・ ω ・) Until then, forcibly introduce MySQL to Raspberry Pi and store it. Even if an error occurs in, it will be overturned again ...

environment

--Web server program: Apache 2.4.46 + PHP 7.4.10 --Client: Windows10 Pro --Server architecture: Raspberry Pi 3B + (with armv8) Linux distribution: openSUSE 15.1 Leap (64bit) / Raspberry Pi OS 2020.08 version (32bit) --Terminal to be prepared separately: MySQL server (In my case, I built MySQL 8.0 on CentOS 8.1)

Premise

--User installed as root (in my verification, it is an administrator account called admin, and it is processed by sudo from there) --In openSUSE, the firewall uses firewalld (does not use distribution-specific firewall commands). In Raspberry Pi OS of Raspberry Pi, for firewalld, since the interlocking around IPv6 was dung, I decided to use the Debian standard ufw. -Last time article Apache + PHP installation completed

Server conditions

IP address

--Client: 192.168.1.11 --Web server: 192.168.1.22 (It was 192.168.1.18 until the last time, but since it was used in the DB, I changed the IP address of Raspberry Pi) --Database server: 192.168.1.18 (Since it was duplicated with the one used for Raspberry Pi, I changed Raspberry Pi to 192.168.1.22) --Affiliation network segment: 192.168.1.0/24

Ability and version to download and install individual packages (as of June 2020)

• zlib-1.2.11.tar.gz
• apr-1.7.0.tar.gz
• apr-util-1.6.1.tar.gz
• httpd-2.4.46.tar.gz
• php-7.4.10.tar.gz

Other required packages are installed with the distribution's standard package commands (dnf, apt, etc.) and do not need to be downloaded individually.

For download, you can access the official website, download from there and transfer it by FTP, or you can get it with wget if you know the URL of the download file, but the acquisition method is omitted.

Work procedure

Introducing the MySQL client

MariaDB Client Tools Installation

I installed the MariaDB client because the client that manages the MySQL commands does not include a package that clearly says "MySQL" in Linux for Raspberry Pi.

openSUSE15.1(RaspberryPi)

# zypper -n install mariadb-client

RaspberryPiOS(2020.08)

# apt-get -y install mariadb-client

You can now use MySQL commands from Raspberry Pi.

Preparation on MySQL server

Users and databases on the server

This time, I will use a virtual machine with a MySQL server built on CentOS 8.1, so [I will use the database built in this article](https://qiita.com/kazumi75kitty/items/76807c637c096618a0e1#mysql%E3%82 % 92% E8% A9% A6% E3% 81% 99).

--Database name: manutest --Test user name: test --Test user password: test1 --Character code: UTF-8 --MySQL server IP address: 192.168.1.18

The password was changed from "test0" to "test1" for personal reasons.

Preparation on the MySQL server

First, prepare various things on the MySQL server so that MySQL can be accessed from other terminals.

Log in as MySQL root.

MySQL server(CentOS8.1)

[Only this command is executed on the MySQL server]
# mysql -u root -p
Enter password:← Enter the MySQL root password

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| manutest           |← The created database is displayed
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE USER 'test'@'192.168.1.22' IDENTIFIED BY 'test1';
↑ IP address of Raspberry Pi "192.168.1.Create a username "test" for "22"

mysql> GRANT ALL ON manutest.* TO 'test'@'192.168.1.22';
↑ "test" is "192" for database "manutest".168.1.All functions can be used from "22"

If there are no errors and "Query OK, 0 rows affected" is displayed, the database and user have been created.

In other words, if MySQL and Apache are on the same server machine, you can use "localhost", but this time you will access another server from Raspberry Pi → MySQL server, so user name + @ mark + host as shown below , And so on

CREATE USER'' @'' IDENTIFIED BY' '; GRANT ALL ON . * TO''@' ';

It means that it is necessary to specify something like (˶ ･ ᴗ ･) ੭

On the MySQL server, it was also necessary to open firewalld on CentOS (˶ ･ ᴗ ･) ੭ \ * \ *

MySQL server(CentOS8.1)

[Only this command is executed on the MySQL server]
# firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="3306" protocol="tcp" accept'
# firewall-cmd --reload

** MySQL port is 3306 / TCP **, so by opening that port, PHP can access the MySQL server ...

Raspberry Pi → Check if you can connect to MySQL server

That's it for working on the MySQL server. Now, let's go back to the Raspberry Pi and see if we can use MySQL. You can specify the host with the "-h" option with the mysql command

RaspberryPi(Raspberry OS openSUSE15.1)

# mysql -h 192.168.1.18 -u test -p
Enter password:← Enter the password "test1"
(Omission)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| manutest           |← Success if the created database is displayed
+--------------------+
2 rows in set (0.00 sec)

You can now see the contents of the table properly! !! (˶˙ᵕ˙˶)

Use MySQL server from PHP

Check MySQL connection from PHP

For the MySQL server, try using a test table as you did last time on the virtual machine CentOS.

Table name: ** testtb **

First of all, before using the table, you have to make sure that you can connect to MySQL with PHP (\ * ˘ᗜ˘ \ *;)

Create a connection confirmation page

# cd ~
# vi connect.php

connect.php

<?php
    $dsn = 'mysql:dbname=manutest; host=192.168.1.18';
    $usr = 'test';
    $pass = 'test1';
    
    try {
        $db = new PDO($dsn, $usr, $pass);
        print 'Connection successful.';
        $db = NULL;
    }
    catch(PDOException $e){
        die('Connect error : '.$e->getMessage());
    }
?>

Move this to the storage location / usr / local / apache2 / htdocs / of the destination web page installed in Apache installation.

# mv connect.php /usr/local/apache2/htdocs/

Since the PHP page is placed, there is no need to restart Apache, so enter https: // [Linux server IP address] /connect.php to check. Since 192.168.1.18 is used by the MySQL server and the IP address of the Raspai Linux server is 192.168.1.22, access it by entering the URL of 192.168.1.22 / connect.php after https: ~ in the browser.

Finally succeeded! !! ⑅︎◡̈︎ *

Check if data can be registered from PHP to MySQL

Next, check if the data can be registered. Create a web page again.

First of all, from the registration form.

# vi test_form.html

test_form.html

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Test page- Test page</title>
</head>
<body>
<form method="POST" action="test_form.php">
    <input type="text" name="name" />
    <input type="text" name="memo" />
    <input type="submit" value="Submit" />
</form>
</body>
</html>

Next, create PHP to add the data received from the form to the MySQL database

# vi test_form.php

test_form.php

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Test input- Test insert</title>
</head>
<body>
<?php
    function getDb(){
        $dsn = 'mysql:dbname=manutest; host=192.168.1.18';
        $usr = 'test';
        $pass = 'test1';
        
        try {
            $db = new PDO($dsn, $usr, $pass);
            $db->exec('SET NAMES utf8');
        }
        catch(PDOException $e){
            die('Connect error : '.$e->getMessage());
        }
        
        return $db;
    }
    
    try {
        $db = getDb();
        
        $stt = $db->prepare('INSERT INTO testtb (name, memo) VALUES (:one, :two)');
        
        $stt->bindValue(':one', $_POST['name']);
        $stt->bindValue(':two', $_POST['memo']);
        $stt->execute();
        
        print $_POST['name'].' - '.$_POST['memo'].' : Insert OK.';
        
        $stt = $db->query("SELECT * FROM testtb");
?>
    <table border="1">
<?php
        while($row = $stt->fetch(PDO::FETCH_ASSOC)){
?>
        <tr>
            <td><?php print $row['name']; ?></td>
            <td><?php print $row['memo']; ?></td>
        </tr>
<?php
        }
?>
   </table>
<?php
         $db = NULL;
   }
    catch(PDOException $e){
        die('Process error : '.$e->getMesssage());
    }
?>
</body>
</html>

Move the above 2 files to the location of Apache web page data.

# mv test_form.* /usr/local/apache2/htdocs/

Then, when you access https: // [IP address] /test_form.html with a browser and register the data ...

This was also successfully registered! (˶ ･ ᴗ ･) ੭⚐⚑ Because it is less powerful than a PC and its storage is microSD, which is unsuitable for reading and writing DBs, it is the moment when I realized that Raspberry Pi can also handle DBs by accessing it externally with an external DB server.

Afterword

Raspberry Pi is less powerful than PC and storage is microSD, so if you build a server exactly like PC, the road is relatively steep due to lack of memory and database incompatibility, but there are also merits unique to the benefit of power saving. I was impressed by the fact that there is another one ... (♥ ´꒳` \ *)

Future and next time

In addition to building a web application server using PHP, I would like to touch on building a web application server using Java (OpenJDK).