Source compile Apache2.4 (httpd 2.4.43) + PHP7.4 on Linux to build a Web server --3 MySQL 8.0 introduction

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 -Source compilation of Apache2.4 + PHP7.4 on Linux-- 2. PHP introduction --Source compilation of Apache2.4 + PHP7.4 on Linux --3 MySQL introduction [this article]

Last time built a web application environment by source compiling PHP7.4 on Apache2.4, but this time, we will continue to build a database server. (⑅ • ᴗ • ⑅)

The database server can be separated from the web server itself, but this time it is easy, so we will build the database MySQL together with the web server

environment

--Web server program: Apache 2.4.43 + PHP 7.4.6 + MySQL 8.0 --Client: Windows10 Pro --Server architecture: x64 (operation confirmed with Hyper-V 2nd generation) Linux distribution: CentOS 8.1 / openSUSE 15.1 Leap / Ubuntu 20.04 (all 64bit)

Premise

--User installed as root (in my verification, it is an administrator account called admin, and it is processed by sudo from there) --For all distributions, the firewall uses firewalld (does not use distribution-specific firewall commands) -Last time article Apache + PHP installation completed

Server conditions

IP address

--Client: 192.168.1.11 --Web server: 192.168.1.18 (verified with the same IP address for all distributions) --Database server: (integrated with the web server) --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
• mysql80-community-release-el8-1.noarch.rpm (CentOS 8.1)
• mysql80-community-release-sl15-3.noarch.rpm (openSUSE 15.1)
• mysql-apt-config_0.8.15-1_all.deb (Ubuntu 20.04)
• oniguruma-devel-6.8.2-1.el8.x86_64.rpm (CentOS 8.1)
• httpd-2.4.43.tar.gz
• php-7.4.6.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 MySQL 8.0

MySQL installation

MySQL in the distribution's standard package commands is often an older or compatible MariaDB, so if you want to introduce an explicit version of MySQL, you'll need to download the repository from the MySQL official.

Apply the downloaded repository and install MySQL.

CentOS8.1

# dnf -y install mysql80-community-release-el8-1.noarch.rpm
# dnf -y install mysql-server

In case of MySQL 8.0 ** The authentication infrastructure may support hash values, and the hash value-based authentication infrastructure may be enabled by default **. However, it seems that this is not supported even in PHP7 (I have not heard that it is still supported in 7.4), and if you access it by writing it with a plaintext password in PHP, authentication will not work. Therefore, it is necessary to modify /etc/my.cnf (for CentOS 8.1, it is included in /etc/my.cnf /etc/my.cnf.d/mysql-default-authentication-plugin.cnf To edit)

openSUSE15.1. Ubuntu20.04

# vi /etc/my.cnf

CentOS8.1

# vi /etc/my.cnf.d/mysql-default-authentication-plugin.cnf

my.cnf or mysql-default-authentication-plugin.cnf

default-authentication-plugin = mysql_native_password ← "#Remove

Start MySQL

# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld

Check the MySQL log after starting MySQL

# less -r /var/log/mysqld.log

At that time, if ** "[Note] A temporary password is generated for root @ localhost: ~" exists, the initial password is set **. If not, it is not set (MySQL 8.0 on CentOS 8.1 is not set). Run the MySQL command line with this password

python

[No MySQL initial password]
# mysql -u root
[MySQL initial password available]
# mysql -u root -p ← Enter the initial password to log in

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<MySQL root password(Not a Linux root password)>';
Query OK, 0 rows affected (0.00 sec)

mysql> exit

Here, set the password for the actual operation of MySQL. So, note that root is ** root in MySQL, not Linux root **.

If "Query OK, 0 rows affected (0.00 sec)" is output, the MySQL root password setting is complete, so exit with exit.

Now, try logging in with the MySQL root password after setting

# mysql -u root -p
Enter password:← Enter the MySQL root password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If this happens, the MySQL root login will be successful. Or you could check it below

# mysqladmin ping -u root -p
Enter Password:← Enter the MySQL root password
mysqld is alive

OK if it says "mysqld is alive"

Try MySQL

Create a database with users

Since MySQL has worked, let's actually create a database with users.

--Database name: manutest --Test user name: test --Test user password: test0 --Character code: UTF-8

First, log in as MySQL root.

# mysql -u root -p
Enter password:← Enter the MySQL root password

mysql> CREATE DATABASE manutest CHARACTER SET utf8;
↑ Character code UTF for database "manutest"-Create in 8

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'@'localhost' IDENTIFIED BY 'test0';
↑ Create user name "test" with password "test0"

mysql> GRANT ALL ON manutest.* TO 'test'@'localhost';
↑ Allow user "test" to use all functions for database "manutest"

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

** If a password policy error occurs **

If you get an error if you violate the password policy, it seems that you can check the policy status and change it.

mysql> SHOW VARIABLES LIKE 'validate_password%';
↑ If it is an Empty set, there is no policy, but if it does, it will initially be
　validate_password_length is 8, validate_password.policy is "MEDIUM"

mysql> SET GLOBAL validate_password.length=4;
mysql> SET GLOBAL validate_password.policy=LOW;
↑ Now you can set a password of up to 4 characters or a simple password for testing purposes.

mysql> SET GLOBAL validate_password.length=8;
mysql> SET GLOBAL validate_password.policy=MEDIUM;
↑ When returning

Now, let's test whether MySQL can be used with the created test user and database.

mysql> exit
Bye

# mysql -u test -p
Enter password:← Enter the password "test0"
(Omission)

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

Create a table and put data in it

Now, let's create a table in the created database and put data in it!

This time, we will create this table for testing. Table name: ** testtb **

mysql> USE manutest;
mysql> CREATE TABLE testtb (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) NOT NULL, memo VARCHAR(256) DEFAULT NULL);
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW TABLES;
+--------------------+
| Tables_in_manutest |
+--------------------+
| testtb             |
+--------------------+
1 row in set (0.00 sec)

After successfully creating the table, let's actually put in the data. In the test table testtb, id is automatically numbered and name is required, so insert the following values as an example.

--id: (Omitted due to automatic numbering) --name: test

• memo：Only for test.

mysql> INSERT INTO testtb (name, memo) VALUES ('test', 'Only for test.');
mysql> SELECT * FROM testtb;
+----+-----------+----------------+
| id | name      | memo           |
+----+-----------+----------------+
|  1 |test| Only for test. |
+----+-----------+----------------+
1 row in set (0.00 sec)

The data registered in this way was registered in the database without being garbled ♪ It's a success!

Try working with MySQL on PHP

Now that I have confirmed the installation and operation of MySQL without any problems, I would like to finally handle MySQL from PHP.

MySQL will quit and create a PHP page.

Check MySQL connection from PHP

First, create a receiving PHP that registers the data received by POST with PHP in the MySQL database! Before that, 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=127.0.0.1';
    $usr = 'test';
    $pass = 'test0';
    
    try {
        $db = new PDO($dsn, $usr, $pass);
        print 'Connection successful.';
        $db = NULL;
    }
    catch(PDOException $e){
        die('Connect error : '.$e->getMessage());
    }
?>

Let's 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 the IP address of the Linux server is 192.168.1.18 this time, access 192.168.1.18 / connect.php by entering the URL after https: ~ in the browser.

If connect.php succeeds in connecting, the above message will be output, so the image above shows that the connection was successful! !!

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=127.0.0.1';
        $usr = 'test';
        $pass = 'test0';
        
        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/

Now, try accessing https: // [IP address] /test_form.html with your browser.

Put the strings in the left and right forms as appropriate ... (preferably in Japanese) and press Submit

Succeeded! The entered data was POSTed to PHP and registered in the MySQL database. Let's check it from the command line as well.

In PHP, the user test connects to the database called manutest, so you can also log in to MySQL with test from the command.

# mysql -u test -p
Enter password:← Enter password "test0"

mysql> USE manutest;
mysql> SELECT * FROM testtb;
+----+-----------------+----------------+
| id | name            | memo           |
+----+-----------------+----------------+
|  1 |test| Only for test. |
|  2 |pancake|want to eat|
+----+-----------------+----------------+
2 rows in set (0.00 sec)

It was added properly! !! (\ * ˘ᗜ˘ \ *) ...: \ * ♡

Afterword

Apache and PHP + MySQL have been frequently used in this pattern, both from my past work experience and from large companies. On the contrary, I feel that Java was scarce.

PHP is cheaper and more open than Java's commercial license, so I think it's easier for large companies and, of course, small and medium-sized individuals to introduce it.

As for the cost of introducing Apache + PHP + MySQL to Linux, I don't need a license, so I feel that the cost of labor for salary and the cost of equipment regardless of used PC or Raspberry Pi.

If the installation cost is 2500 yen per hour, if it takes 2 hours to build a new web server and file server, the total is 5000 yen. Besides, if the combination of a used PC or Raspeye and a new HDD or SSD costs 30,000 to 40,000 yen, the new installation cost is less than 50,000 yen, so it takes 6 minutes rather than buying an expensive Windows Server machine for 300,000 yen. I feel that IT solutions will be enriched even for small and medium-sized enterprises and individual management with less than 1 of.

In particular, in the case of IoT such as Raspberry Pi, it is expected that it can be combined with physical property fields such as sensors in a hybrid manner and converted into data, so I would like to broaden my horizons and put it on Qiita in the future (˙꒳). ˙ᐢ)

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).