LAMP

Description

The purpose of this document is to create a LAMP (Linux/Apache/MySQL/Php) installation on your lab machine. With this in place, we'll also install two common LAMP applications: More passwords:

Install MySQL

Having a MySQL database will be central to this course. We'll start with this installation first. For some reason, the Ubuntu 18.04 MySQL 5.7 packages are not working correctly, so we'll use the MySQL package installer from the MySQL site.
https://www.mysql.com
Start by downloading this file, gotten from the MySQL download site:
mysql-apt-config_0.8.10-1_all.deb
This installer supports installing the MySQL 5.7 or the latest MySQL 8. Install the packages:
$ cd Downloads
$ sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb 
Then proceed as follows: You should see the following message, and, as indicated, use the arrow keys to select the "mysql-5.7" entry:
 ┌─────────────────────┤ Configuring mysql-apt-config ├──────────────────────┐
 │ This configuration program has determined that no MySQL Server is         │
 │ configured on your system, and has highlighted the most appropriate       │
 │ repository package. If you are not sure which version to install, do not  │
 │ change the auto-selected version. Advanced users can always change the    │
 │ version as needed later. Note that MySQL Cluster also contains MySQL      │
 │ Server.                                                                   │
 │                                                                           │
 │ Which server version do you wish to receive?                              │
 │                                                                           │
 │                                mysql-5.7                                  │
 │                                mysql-8.0                                  │
 │                                None                                       │
 │                                                                           │
 │                                <Ok>                                       │
Tab to the OK and hit Enter.

Back in the original dialog, you should now see:
MySQL Server & Cluster (Currently selected: mysql-5.7)
...
...
Ok
Use the arrow keys to get to the (first) OK, and hit Enter.

Back in the shell do this:
$ sudo apt update
This installation makes available the Ubuntu/Debian installer packages.

Begin Installation

Choose and set a MySQL root password, definitely not your machine login password. You will not need to remember it beyond the initial steps. Start the installation:
$ sudo apt install mysql-community-server
Immediately you're presented with the password entry. Key it in, tab to OK and Enter.
┌────────────────┤ Configuring mysql-community-server ├──────────────────────┐   
|                                                                            |
|   Please provide a strong password that will be set for the root account   │
│   of your MySQL database. Leave it blank to enable password less login     │
│   using UNIX socket based authentication.                                  │
│                                                                            │
│   Enter root password:                                                     |
|                                                                            |
│     MYSQL_ROOT_PASS                                                        │
|                                                                            |
|                                  <Ok>                                      |
└────────────────────────────────────────────────────────────────────────────┘
Tab to the "OK" and press enter.

The next step is to confirm the password. Key it in again. Then the installation is done. Begin by confirming that you can get access the MySQL server as root:
$ mysql -u root -p
Enter password: MYSQL_ROOT_PASS
You want to securely store and use this password so that you don't want to have to remember it.

As root, create this file:

/root/.my.cnf
[client]
user=root
password="MYSQL_ROOT_PASS"
select
Test the effectiveness by going in without the password entry:
$ sudo su
# mysql
and, directly from non-root user:
$ sudo -H mysql
Using this /root/.my.cnf in the home directory implies:
if any MySQL client programs is executed, such as mysql, mysqldump, etc., then use the user and password provided.
It's most secure to make this usable only by the machine's root user. To do password-less root access in this way, you must either run a root shell, or use "sudo -H" which correctly sets HOME directory. Prove this to yourself by comparing:
$ sudo printenv | grep HOME
$ sudo -H printenv | grep HOME
You can now "forget" the MySQL root password with respect to this document. It's already recorded in a root-owned file.

Install Apache/Php

Install the following packages. It's a bit of overkill compared to what is absolutely necessary, but they may be useful later.
$ sudo apt install \
  apache2 php libapache2-mod-php php-cli php-mysql php-cgi php-curl \
  php-json php-apcu php-gd php-xml php-mbstring php-gettext
Apache itself can execute in one of two modes: The former is called thread-safe and is required for running Php as an Apache module (libapache-mod-php). The latter potentially has higher throughput.

From an installation point of view, you need to install apache2 and libapache2-mod-php side-by-side to get the correct execution mode. Apache documents can found on the Apache site:
http://httpd.apache.org/docs/2.4

Apache Configuration

First verify the Apache service by activating the root URL
http://localhost
What the root URL is displaying is:
/var/www/html/index.html
The directory /var/www/html is the DocumentRoot as defined in the file:

/etc/apache2/sites-enabled/000-default.conf
<VirtualHost *:80>
  ...
  DocumentRoot /var/www/html
  ...
</VirtualHost>
Apache employs many files from various locations, including:
/etc/apache2/                     configuration root
/etc/apache2/apache2.conf         main config file
/etc/apache2/conf-available/      available config files
/etc/apache2/conf-enabled/        enabled statup files
/etc/apache2/mods-available/      available config files for modules
/etc/apache2/mods-enabled/        enabled config files for modules
/etc/apache2/sites-available/     available site definitions
/etc/apache2/sites-enabled/       enabled site definitions
/etc/init.d/apache2               apache control script
/usr/sbin/apachectl               actual apache binary file
/usr/lib/apache2/                 apache modules
/var/log/apache2/                 apache Log files
The relationship between the directories
"*-enabled/" and "*-available/"
is that each ".conf" and/or ".load" file in an "*-enabled/" directory is a symbolic links to an actual file in the "*-available/" directory. Verify this by:
$ ll /etc/apache2/conf-enabled
$ ll /etc/apache2/mods-enabled
When Apache is restarted or reloaded, it automatically uses everything in an "*-enabled/" directory. This diagram of loading configuration files is in the top comment section of /etc/apache2/apache2.conf:
#   /etc/apache2/
#   |-- apache2.conf
#   |       `--  ports.conf
#   |-- mods-enabled
#   |       |-- *.load
#   |       `-- *.conf
#   |-- conf-enabled
#   |       `-- *.conf
#   `-- sites-enabled
#           `-- *.conf
Ubuntu provides special commands to control the enabling/disabling of the features specified by these executables:
a2enconf         apache2 enable a conf file
a2disconf        apache2 disable a conf file   
a2enmod          apache2 enable a module
a2dismod         apache2 disable a module   
a2ensite         apache2 enable a site
a2dissite        apache2 disable a site
The enablers simply create links from the "*-available/" directory to the corresponding "*-enabled/" directory. The disablers remove these links.

Apache service, error log, config test

The Apache service is one which needs to be "reset" often because of configuration changes. Control the Apache server is done as a SysV service which is based on the systemctl command.
$ sudo systemctl  command  apache2.service
// or, more simply:
$ sudo systemctl  command  apache2
where the commands are:
status | start | stop | restart | reload | etc.
An equivalent method (less favored now) is this:
$ sudo service apache2 [ mostly same commands ]
The reload feature is generally preferred to restart because it does not actually stop the server and therefore can be done without affecting web users. You can get usage information by running "service apache2."

If something goes wrong, the first place to look is usually this log file:
/var/log/apache2/error_log
This error log file is readable by you, a system admin, without invoking sudo. Often a useful thing to do is to "follow the tail" of this file as messages are generated with using the "tail -f" command:
$ tail -f /var/log/apache2/error_log
If you make a configuration change, you can test its effectiveness by running this prior to attempting to reset the service.
$ sudo apachectl configtest
When you run this, you'll see a complaint about unknown ServerName. Fix this by editing the following file, making the change to the second entry:

/etc/hosts
127.0.0.1       localhost
127.0.1.1       MACHINE.cs.wcupa.edu MACHINE

Apache user sites

Apache understands user sites as the automatic association of a special directory owned by you (by default, ~/public_html) to the URL
http://localhost/~
User directories are not enabled by default. To enable the Apache userdir module by:
$ sudo a2enmod userdir
$ sudo systemctl reload apache2
The first command, as indicated above, simply creates a symbolic link. Check it yourself:
$ ll /etc/apache2/mods-enabled/userdir.* 
/etc/apache2/mods-enabled/userdir.conf -> ../mods-available/userdir.conf
/etc/apache2/mods-enabled/userdir.load -> ../mods-available/userdir.load
Here is the content of the files:

/etc/apache2/mods-available/userdir.conf
<IfModule mod_userdir.c>
    UserDir public_html
    UserDir disabled root
 
    <Directory /home/*/public_html>
        AllowOverride FileInfo AuthConfig Limit Indexes
        Options MultiViews Indexes SymLinksIfOwnerMatch IncludesNoExec
        <Limit GET POST OPTIONS>
            Require all granted
        </Limit>
        <LimitExcept GET POST OPTIONS>
            Require all denied
        </LimitExcept>
    </Directory>
</IfModule>

/etc/apache2/mods-available/userdir.load
LoadModule userdir_module /usr/lib/apache2/modules/mod_userdir.so
It is common to add protection of the form:
<IfModule mod_userdir.c>
  ...
</IfModule>
to mean that the commands in the configuration file can only be applied if the module has been loaded.

Test the Apache home directory

Create the web directory and add a test file to it:
$ mkdir -p ~/public_html
$ echo "HELLO WORLD" > ~/public_html/hello.html
Test the effectiveness by activating this URL:
http://localhost/~
You should get a directory listing with the "hello.html" file within.

Apache control features

As seen in the moduserdir.conf file Apache has a complex set of control features controlling what can and cannot happen to requests to a user web directory. You can track down the documentation for these features through the manual. These are considered core features and one should look in modules → core to find this information: Information about the Require statement (not used in the older Apache2.2) can be found in the docs about Access Control.

File Access Permissions

A file is normally thought of as a holding content such as a text file, but a UNIX file system considers a file as an entity which can any one of these:
  1. ordinary file
  2. directory
  3. symbolic link
  4. block device
  5. character device
  6. fifo
  7. socket
Perhaps a better word is resource because it has a more neutral connotation. The access mode of a resource is made up of a 12-bit field:
sSt uuu ggg ooo
The 3-bit fields uuu, ggg and ooo stand for the user, group and other (world) permissions, respectively. Each 3-bit field represents the read/write/execute permissions for this resource. The 3-bit field sSt is quite specialized: From a command shell, the permissions of files can be seen by executing a long listing. Here are some example files with an assortment of permissions:
$ ls -ld ~/.profile ~/public_html /root /tmp /usr/bin/php \
  /usr/bin/passwd /var/run/mysqld/mysqld.sock /dev/sda /dev/null

-rw-r--r-- 1 LOGIN LOGIN  ... /home/LOGIN/.profile
drwxr-xr-x 3 LOGIN LOGIN  ... /home/LOGIN/public_html
drwx------ 6 root root ... /root/
drwxrwxrwt 5 root root ... /tmp
-rwsr-xr-x 1 root root ... /usr/bin/passwd
lrwxrwxrwx 1 root root ... /usr/bin/php -> /etc/alternatives/php
srwxrwxrwx 1 mysql mysql ... /var/run/mysqld/mysqld.sock
brw-rw---- 1 root disk 8, 0 ... /dev/sda
crw-rw-rw- 1 root root 1, 3 ... /dev/null
The initial character gives the file type: regular (-), directory (d), symbolic link (l), block device (b), character device (c), fifo (p), and socket (s). The remaining nine characters of the first field specify the allowable rwx permissions for user, group, and other. A dash (-) replacing the letter means permission is not granted. The specialized "sSt" bits replace "x" bits for user, group, other, resp.

Access Mode for Directories

The read/write/execute permissions are fairly easy to understand for ordinary files, but they have a different interpretation for directories. For a directory, In particular, the x permission on a directory gives the all-important ability to pass through it.

Resource access by a process

Every Linux process has a user id and group id obtained by a system authentication mechanism, most likely from the /etc/passwd file. The resources that a process may access are dictated by A process has read permission to a resource with permissions uuugggooo if any of these holds:
  1. the user id of the process is the user id of the resource and the resource's uuu has the r bit set.
  2. the group id of the process is the group id of the resource and the resource's ggg has the r bit set.
  3. the ooo portion of the mode has the r bit set.
Furthermore, if, say, the resource has this full path:
/dir1/dir2/.../lastdir/resource
then the process must have "pass-through" (x) permission on all directory components
/,  /dir1,  /dir1/dir2,  ...,  /dir1/dir2/.../lastdir

Web Access

The Apache web server runs as the user and group of name www-data, different from you, and (definitely) from root. To understand web accessibility of
~/public_html/hello.html
check:
$ ls -ld  /  /home ~  ~/public_html ~/public_html/hello.html
drwxr-xr-x 24 root   root   ... /
drwxr-xr-x  4 root   root   ... /home
drwxr-xr-x 26 LOGIN LOGIN ... /home/LOGIN 
drwxr-xr-x  2 LOGIN LOGIN ... /home/LOGIN/public_html
-rw-r--r--  1 LOGIN LOGIN ... /home/LOGIN/public_html/hello.html
You'll see that each of the directories in the path have the necessary "x" permission for all users and that the target file has the necessary "r" permission. We might say the permission on home are actually too permissive:
drwxr-xr-x ...     ... /home/
If there are multiple users on a system, any other can to read the readable files within your home directory. Better would be this:
drwx--x--- ...     ... /home/
However, the change of group can only be done as root.

File access control

An alternative is to use user-controllable access control permissions built into modern UNIX-like file systems. Mac OS X makes big usage of these. The main executables are setfacl/getfacl (set/get file access control permissions). Here is an experiment:
$ sudo apt install links                 (a shell based web browser)

$ links -dump http://localhost/~LOGIN   (display user web home)

$ getfacl ~                              (default access controls of your home)
# file: .
# owner: LOGIN 
# group: LOGIN 
user::rwx
group::---
other::---

$ chmod 700 ~                            (close off to other users)
$ ls -ld ~
drwx------+ ... LOGIN LOGIN
$ links -dump http://localhost/~LOGIN   (now permission denied)

$ setfacl -m u:www-data:x ~              (allow "x" access to apache user)
$ getfacl ~                              (default access controls of your home)
# file: .
# owner: LOGIN 
# group: LOGIN 
user::rwx
user:www-data:--x
group::---
mask::--x
other::---

$ ls -ld ~
drwx--x---+ ... LOGIN LOGIN             (note the "+" in permissions)

$ links -dump http://localhost/~LOGIN   (works again)
If you wish, you can return to the original state:
$ setfacl -b ~      (clear ACL permissions)
$ chmod 755 ~       (set original permissions)

Local Apache config file

There are many circumstances in which you need to extend the default configuration settings provided by Apache. Toward this end we create our own local Apache configuration file:
$ sudo touch /etc/apache2/conf-available/local.conf
Put it into play by:
$ sudo a2enconf local
For convenience make a symbolic link to it in the /etc/apache2 directory:
$ sudo ln -s /etc/apache2/conf-available/local.conf /etc/apache2/
As an example of what to do with it, edit it and allow extended privileges to you:

/etc/apache2/local.conf
<Directory /home/LOGIN/public_html>
  Options All
  AllowOverride All
</Directory>
select
Put it into effect by:
$ sudo systemctl reload apache2

Php

Php, like Bash, is a script language, but it is a more complete programming language in itself and is intended to be executed on multiple OS platforms. Php was written for, and is primarily dedicated to being a server-side scripting language for web programming. Unlike Bash, which is purely interpreted, Php is compiled and thereby gives its programs a significant speedup advantage when executed.

Php has three different versions:
  1. as an Apache module (apache)
  2. as an executable for a CGI-style exectuion (typically FASTCGI) used in non-Apache web servers like Lighttpd, Nginx, IIS (cgi)
  3. as a command-line program, /usr/bin/php, for script-style programming (cli)
As of Ubuntu 16.04, the default version used is Php 7. Prior to this point it was Php 5. Key files and directories for Php include:
/etc/php/7.2/ configuration root
/etc/php/7.2/X/ X = apache, cgi, cli
/etc/php/7.2/X/php.ini main config file for X
/etc/php/7.2/mods-available/ configuration (.ini) files for Php modules
/etc/php/7.2/X/conf.d/ added config files for X (symlinks into previous folder)
/usr/bin/php
  ⇾ /etc/alternatives/php
  ⇾ /usr/bin/php7.2
command-line interpreter
/usr/bin/php-cgi CGI executable
/usr/lib/apache2/
    modules/libphp7.2.so
Apache Php module
/usr/lib/php/20170718/ Php plugin modules
/usr/share/php7.2-* modules .ini file storage
The three subdirectories apache2, cgi, cli of /etc/php/7.0 correspond to the three ways Php can be used as listed above, respectively. The init files in mods-available primarily specify the name of the Php plugin extension (a .so file) to be used.

Ubuntu has an executables
phpenmod
phpdismod
which, like the Apache correlates, create symlinks from the .ini files in /etc/php/7.0/mods-available into the corresponding conf.d subdirectories of apache2, cgi and cli. For the most part this linking to all Php versions is done whenever a Php module is installed.

The web-based CGI method creates a separate Php process for every server-side invocation, whereas, the Apache module maintains "live" Php processes, presumably avoiding the start-up latency. Nevertheless, the CGI system can have very good performance if enhanced by so-called "FastCGI" technology which caches the executable code created from Php invocations.

Permitting Php execution in user directories

Ubuntu distributions make it so that an extra step must be taken to enable Php for user directories. You have to edit the file:

/etc/apache2/mods-enabled/php7.2.conf
<FilesMatch ".+\.ph(ar|p|tml)$">
    SetHandler application/x-httpd-php
</FilesMatch>
<FilesMatch ".+\.phps$">
    SetHandler application/x-httpd-php-source
    # Deny access to raw php sources by default
    # To re-enable it's recommended to enable access to the files
    # only in specific virtual host or directory
    Require all denied
</FilesMatch>
# Deny access to files without filename (e.g. '.php')
<FilesMatch "^\.ph(ar|p|ps|tml)$">
    Require all denied
</FilesMatch>
 
# Running PHP scripts in user directories is disabled by default
# 
# To re-enable PHP in user directories comment the following lines
# (from <IfModule ...> to </IfModule>.) Do NOT set it to On as it
# prevents .htaccess files from disabling it.
<IfModule mod_userdir.c>
    <Directory /home/*/public_html>
        php_admin_flag engine Off
    </Directory>
</IfModule>
As directed by the comments, comment out the last 5 lines:
#<IfModule mod_userdir.c>
#    <Directory /home/*/public_html>
#        php_admin_value engine Off
#    </Directory>
#</IfModule>
and reload Apache:
$ sudo systemctl reload apache2
Test the effectiveness by creating a simple Php file in your home site as follows:

~/public_html/hello.php
<?php
echo "Hello from Php";
select
Then go back to your home page with a browser:
http://localhost/~
Refresh, locate the new file and activate it.

MySQL Discussion

Key files for MySQL include:
/var/lib/mysql/             MySQL database files (mysql or root only)
/var/lib/mysql/mysql/       main administrative database
/etc/mysql/                 configuration files
/var/run/mysql/             socket file, PID file
By default, MySQL listens on port 3306 for client requests, but it also listens on a dedicated UNIX socket file to provide more efficient service from local clients. The MySQL service, unlike Apache, is an Upstart service, meaning that we can use restart and/or reload as commands (the service command works too), e.g.:
$ sudo restart mysql
Unlike Apache, we almost never need to do this, because administrative changes are all done in the mysql administrative database which can be manipulated through a MySQL client executable.

The MySQL client installation provides a number of useful shell commands to manipulate its databases, including:
mysql:        command line interpreter for manipulating database
mysqladmin:   basic administration commands
mysqldump:    dump the contents of table(s) in a database
mysqlshow:    show table/fields in a database
You can see the entire set of choices from the shell using tab completion by doing:
$ mysql[TAB][TAB]
For example, try these commands:
$ sudo -H mysqlshow               (all databases)

$ sudo -H mysqlshow mysql         (all tables in mysql)

$ sudo -H mysqlshow mysql user    (description of user table)

$ sudo -H mysql
mysql> show databases;
mysql> use mysql;
mysql> show tables;
mysql> describe user;
mysql> quit

$ mysqldump                       (synopsis)

$ mysqladmin                      (synopsis)

Backup and reload

The mysql commands provide an excellent scheme by which a database can be "backed up" and then "reloaded". Do the backup like this:
$ sudo -H mysqldump somedb > somedb.sql
The somedb.sql file contains the data in all the tables plus the commands needed to recreate these tables. After creating the somedb database, the restoration can be done like this:
$ sudo -H mysql somedb < somedb.sql

MySQL access principles (simplified)

The MySQL is a network-oriented DBMS listening on port 3306. Client programs may reside on different hosts than the server. The access rights of MySQL client to a MySQL DBMS database is determined by three key factors: When a client connects, it attempts to gain access privileges by consulting these three tables.
  1. user table. This pair is matched
    (host,user)
    
    If the password field is non-empty the client must provide a matching password. The remaining fields represent boolean "global privileges" which are given to the client in that they can be used to access any database. For the most part, non-root uses gain no privileges in these fields and root gains all privileges.
  2. host table. This pair is matched:
    (host,database)
    
    thereby augmenting the privileges available to the client when accessing from this host.
  3. db table. This triple is matched.
    (host,database,user)
    
    therefore further augmenting the privileges available to the client as this particular database. The db table is the most common place where privileges are assigned for non-root users since it is the most specific to the database.
MySQL employs a "wild-card" value % to populate a field which is allowed to match any value.

phpMyAdmin

The phpMyAdmin tool is a web-based package for administering a MySQL DBMS. As the name suggests, it requires a Php installation. We need the MySQL root password to set it up. You can refer to the file you've already created:
$ sudo cat /root/.my.cnf
Start the installation:
$ sudo apt install phpmyadmin
Here are the configuration steps although they may not be presented in exactly this order
  1. You are asked to choose the web server on which to run phpMyAdmin. Tab to "apache2", press the SPACE BAR to select it:
      [*] apache2                                                    
      [ ] lighttpd
    
      <Ok>
    

    Tab to "OK", press ENTER.
  2. You are presented with the "Configuring phpmyadmin" screen The installer wants to create and configure a database by dbconfig-common. The Yes choice is already selected, just press ENTER.
  3. A password is requested for the phpmyadmin user. You can just leave it blank. You never need to use it. Tab to OK and press Enter.
  4. The installer needs the MySQL root (administrative) password to set up the database.
You can always go back and reconfigure the installation to pick up changes by:
$ sudo dpkg-reconfigure phpmyadmin
If things get screwed up, you can start over:
$ sudo apt purge phpmyadmin
Say yes to deleting everything possible. Then start again.

Relevant files

It is useful to know just what files are affected by an installation. One way to discover this information is like this:
$ dpkg -L phpmyadmin
This rarely tells the whole story because an installation will often kick in other package installations along with post-installation additions. Here are the key files for phpmyadmin:
  1. The directory /etc/phpmyadmin: usually the starting point for configuration changes.
  2. The direcory /usr/share/phpmyadmin contains the bulk of the code for running phpMyAdmin.
  3. The Apache config file: /etc/apache2/conf-available/phpmyadmin.conf, a symlink to /etc/phpmyadmin/apache.conf. The main access code specified within this file is this:

    /etc/phpmyadmin/apache.conf
    Alias /phpmyadmin /usr/share/phpmyadmin
     
    <Directory /usr/share/phpmyadmin>
      Options FollowSymLinks
      DirectoryIndex index.php
     
      <IfModule mod_php5.c>
        ...
      </IfModule>
      <IfModule mod_php.c>
        ...
      </IfModule>
    </Directory>
    <Directory /usr/share/phpmyadmin/setup>
      ...
    </Directory>
  4. The file
    /etc/dbconfig-common/phpmyadmin.conf
    contains database configuration information for phpMyAdmin. In particular, this file stores the phpmyadmin user password.

    All files in /etc/dbconfig-common are root-only readable since they store passwords and other sensitive information.

Log in as root and observe

phpMyadmin or other MySQL GUI clients serve well to help educate us about the MySQL database structure. All the information could be obtained through the simple mysql client, but you have to know a lot more about how to get it.

Use this link in a browser:
http://localhost/phpmyadmin
In the Log in section, log in. Remember that you can find the MySQL root password in the /root/.my.cnf file.
Username:
  root
Password:
  -the-MySQL-root-password-
On the left you should see these databases:
information_schema
mysql
performance_schema
phpmyadmin
sys
The phpmyadmin database is used for managing phpMyAdmin itself. The others are standard MySQL database; for example, the information_schema database is read-only, holding "meta" information about the actual databases. The key one is the administrative mysql database.

Choose the mysql database on the left to reveal all the tables within it. Locate the user table at the bottom; select that one and try to understand the table content. Compare the root and phpmyadmin users.

The fields Select_priv, ..., Create_tablespace_priv indicate what a user can do to any table. The root user has "Y" in all these and so can do anything to any table. In contrast, the phpmyadmin user has "N" in all these and so can do nothing to any table based on these fields.

Scrolling right toward the end we see the fields plugin, ..., account_locked which specify authentication and account access. In particular these 2 are the basis for authentication:
plugin:                  encryption mechanism
authentication_string:   encrypted password
The second most important table is db. Look at the entry:
User: phpmyadmin
Db:   phpmyadmin
Except for Grant_priv all privilege columns have "Y", indicating that this user can do everything to this database except grant access to other users.

Drupal

Drupal is a popular open source Php-based Content Management System (CMS) which allows users to create websites with minimal knowledge of HTML and Php per se. The content is "managed" by maintaining it within a database and presented by means of an extensive library of templates and themes.

We will work directly from the software available from the Drupal site:
https://www.drupal.org/8
Download either the zip, or tarball for Drupal. At the time of writing, the version used is 8.5.4.

Everything we do is through a root shell so start here:
$ cd
$ sudo su
#

Database preparation

We need a database dedicated to Drupal. Like phpMyAdmin, it's not necessary to remember this database password, but you have to remember it until the installation is complete.

Choose a Drupal user database password. Invoke the mysql client and run these mysql client commands to set up the database (substitute your chosen password):
# mysql
mysql> create database drupal;
mysql> create user drupal@localhost identified by 'DRUPAL_DB_PASS';
mysql> grant all on drupal.* to drupal@localhost;
mysql> quit
Extract the downloaded archive and move it into place, renaming the folder. We are assuming you're still in your home directory and the archives are in the ~/Downloads folder, do one of these (double-check the version in use):
# unzip Downloads/drupal-8.5.4.zip
--- or ---
# tar xzf Downloads/drupal-8.5.4.tar.gz 
Then move it into place renaming the folder MACHINE_drupal. Please use exactly this name.
# mv drupal-8.5.4 /var/www/html/MACHINE_drupal
# cd /var/www/html/MACHINE_drupal

Begin installation

PLEASE NOTE Shell actions take place in this directory:
/var/www/html/MACHINE_drupal

The installation is driven by browser interaction. Open a browser to the site:
http://localhost/MACHINE_drupal
Drupal recognizes that the installation is taking place and redirects to:
http://localhost/MACHINE_drupal/core/install.php
It reveals the 6 steps. The first two are easy:
Choose Language.
Click Save and Continue

Select an installation profile Standard.
Save and Continue

Fix requirement problems

The next step, Verify Requirements, takes the most effort. We want to satisfy one requirement problem at a time in order to appreciate what is going on. After each step, refresh the browser to see the change.
  1. Clean URLs.

    First enable Apache rewrite module:
    # a2enmod rewrite
    
    For performance reasons, we want to make the .htaccess file a server-loaded config file. The goal is to create and activate this config file:

    /etc/apache2/conf-available/drupal.conf
    <Directory /var/www/html/MACHINE_drupal>
    THE CONTENTS OF .htaccess
    </Directory>
    To do so, run the following commands. You're still in the root shell and the prompt should confirm that you're in the right working directory.
    # mv .htaccess /etc/apache2/conf-available/drupal.conf
    Edit the file you just created:
    # gedit /etc/apache2/conf-available/drupal.conf
    
    Add this as the first line:
    <Directory /var/www/html/MACHINE_drupal>
    and this as the last line:
    </Directory>
    Then put it all into effect by:
    # a2enconf drupal
    # systemctl reload apache2
    
    Refresh the browser to see the effect. The "Clean URLs" message should disappear.
  2. File System

    Again, confirm you're in the right directory. We have to create a folder and make it Apache-writable:
    # mkdir sites/default/files
    # setfacl -m g:www-data:rwx sites/default/files
    
    Refresh the browser to see the effect. The "File System" message should disappear.
  3. Settings file (1)

    We have to create a settings configuration file.
    # cp sites/default/default.settings.php sites/default/settings.php
    
    Refresh the browser to see the effect. The "Settings file" message should change. Now it says the file is not Apache-writable.
  4. Settings file (2)

    We have to make this newly-created file Apache-writable:
    # setfacl -m g:www-data:rw sites/default/settings.php
    
    Refresh the browser to see the effect. All requirement problems have been dealt with.

Configure Database

The next step is Set up database. This is the last place where you need to know the drupal database password. Substitute your actual password.
Database name:
drupal
Database username:
drupal
Database password:
DRUPAL_DB_PASS

Click: Save and Continue

Now it goes through Installing Drupal. There's nothing to do but wait. While you wait, you can "forget" the drupal database password:
Drupal DB Password:

Configure Site

We'll deal with the opening message about removing write privileges later.

You have to create a site maintenance account with login/password. Unfortunately, you have to remember these credentials. I am suggesting that you use your machine login and password, which will be encrypted in the drupal database.
SITE INFORMATION
Site name:
MACHINE
Site email address: (must be trimmed)
LOGIN@MACHINE.cs.wcupa.edu

SITE MAINTENANCE ACCOUNT
Username:
LOGIN
Password:
LOGIN's PASSWORD on MACHINE
Confirm password:
--- repeat ---
Email address:
already set

REGIONAL SETTINGS
Default country:
United States
Default time zone:
New York

UPDATE NOTIFICATIONS
Check for updates automatically (uncheck it)
Click to finish: Save and Continue

Remove Write Permissions

The message reported previously is reported again:
All necessary changes to sites/default and sites/default/settings.php have been made, so you should remove write permissions to them now in order to avoid security risks....
Refresh the browser to make the warning message disappear. Because of the way we've used the ACL's, there actually is little risk that system other users (if there were any) could do any harm. Nevertheless, this final step secures the file for root-only access:
# setfacl -b sites/default/settings.php

Create a "hello world" post

You're already logged in as administrator; however, log out and back in to test this feature.
  1. Click Home and the Add Content link below "No front page content has been created yet."
  2. Select Basic Page.
  3. Create Basic Page (make the content as you like):
    Title:
    Hello World.
    Body:
    I'm creating my first Drupal page.
  4. On the right (or below), select PROMOTION OPTIONS and check:
    Promoted to front page.
  5. Click Save.
  6. Click Home. Then Log out to see your page.

Access through the taz tunnel

We have already described the setup of the taz tunnel in the Ubuntu Desktop Installation document. Now we can make use of the forward:
LocalForward 2003 MACHINE:80
Activate the tunnel from your client machine, either through PuTTy or from a shell via:
$ ssh MACHINE_tunnel
Once activated, you should be able to access both sites that we have created:
http://localhost:2003/phpmyadmin
http://localhost:2003/MACHINE_drupal/
It's OK to use passwords because this access is secure by virtue of transmission through SSH.

Access through taz by proxy

The administrator of a public server (like taz) can make a site on a private server publicly accessible through proxy. The taz server has already been set up to do so for all the Csc586 class machines.

The assumption is that your Drupal site is
/MACHINE_drupal
You can use this site directly through taz:
http://taz.cs.wcupa.edu/MACHINE_drupal
What is necessary on taz is that the proxy_http module be enabled:
$ sudo a2enmod proxy_http
and that there is an Apache configuration entry like this:
ProxyPreserveHost On
 
ProxyPass /MACHINE_drupal http://MACHINE/MACHINE_drupal
<Location /MACHINE_drupal>
  Require all granted
</Location>

Change MySQL root password

It's of course not necessary to do this, but here are the instructions for posterity. Select a desired new MySQL root password
NEW_ROOT_PASS

The convoluted procedure, taken from online MySQL docs, is specific to MySQL versions 5.7.6 and above.
  1. Stop MySQL:
    $ sudo systemctl stop mysql
    
  2. Start MySQL in a novel way:
    $ sudo mysqld_safe --skip-grant-tables &
    
    Hit Enter to get the shell prompt back.
  3. Starting MySQL in the way we did, authentication is not observed, so you can go in as you.
    $ mysql
    mysql> flush privileges;
    mysql> alter user root@localhost identified by 'NEW_ROOT_PASS';
    mysql> quit
    
  4. Kill the running mysqld process:
    $ sudo killall mysqld
    
    Wait for output dump confirmation and hit Enter to get the shell prompt back.
  5. Restart MySQL and verify password:
    $ sudo systemctl start service mysql
    $ mysql -u root -pNEW_ROOT_PASS
    
  6. Edit /root/.my.cnf, make the password change and verify:
    $ sudo -H mysql 
    mysql> 
    


© Robert M. Kline