How to Setup a LAMP Server on a Bare-Metal CentOS Box

This article provides step-by-step instructions for setting up a basic LAMP stack – the Apache HTTP Server, PHP and MySQL – on a bare-metal Linux box with only the CentOS operating system pre-installed. The instructions are correct for CentOS 7.2 and will likely be correct for RHEL and Fedora too.

Manual conditioning of a web server is recommended when you have only a small number of dedicated servers to manage. If you have a large number of boxes to configure, or if you frequently create and destroy virtual boxes, then you should use a configuration management system to automate this process.

The instructions below assume that your Linux user account has root privileges. It is good practice to administer servers as a non-root user and then temporarily elevate your privileges to root level for individual commands that require it. Throughout this article, commands that require root privileges are prefixed with the sudo command. Follow these instructions to create and manage Linux user accounts.

The examples will assume that your web application will be served from "https://www.example.com". Obviously, replace "www.example.com" with whatever fully-qualified domain name (FQDN) you will be using. A or CNAME records to point the FQDN to the server's IP addresses should already be in place. If you are not ready to repoint your domain, use a temporary one, such as "test.example.com".

Getting started

Before you start configuring your server, I recommend that you harden SSH access to your box for better protection against brute-force exploits. Also make sure you've got the latest security patches installed for your operating system. (System updates should be done regularly.)

sudo yum update

System time

I recommend setting the system timezone to UTC.

sudo timedatectl set-timezone 'UTC'

Check that the date and time are correct. Remember, if it is British Summer Time, the UTC time will be one hour behind.

date

Use the following command to change the time. The time that you provide must be the current clock time in the system's timezone.

sudo timedatectl set-time "2017-03-13 11:59:00"

If that fails because "Automatic time synchronization is enabled", it means timedatectl is configured to keep your system's clock synchronized with Internet time servers. You will need to disable that if you want to set your server's time manually.

sudo timedatectl set-ntp false

Package management

Off-the-shelf, a CentOS box will be configured to install packages from CentOS's native software repositories. I recommend enabling EPEL (Extra Packages for Enterprise Linux) as an additional source of packages. The EPEL repository is dedicated to distributing a high quality set of optional packages for Linux Enterprise distributions, including CentOS and Red Hat Enterprise Linux (RHEL). The following command adds the EPEL repository to the Red Hat Package Manager (RPM).

sudo yum install epel-release

Now you will be able to install much more stuff using the yum RPM client. Use the following command to list all packages that are available for install. The list is long, so best to print it to a file and read the contents in a text editor.

yum list available > /tmp/yum-list-available.txt
vi /tmp/yum-list-available.txt

Alternatively, if you are looking to install a specific package that you know the name of, you can apply a filter to the list of available packages and print the result straight to the console. The following command will print a list of available packages containing "php" in their title.

yum list available | grep php

To list packages that are already installed:

yum list installed

Apache HTTP Server

Installation

The following command downloads and installs the Apache HTTP server.

sudo yum install httpd

Enable Apache to start at bootup time.

sudo systemctl enable httpd.service

Controls

apachectl is a handy front end for the Apache HTTP Server daemon (httpd). To start the Apache HTTP server, use this command:

sudo apachectl start

To stop the server:

sudo apachectl stop

To do a full restart of the server:

sudo apachectl restart

A full restart is required whenever you make any changes to the server's configuration or enable or disable Apache modules. If Apache fails to start, debugging information will be appended to the server's ErrorLog file.

Start the server now. Then navigate to http://www.example.com (or whatever domain you've got pointing to the server). You should see Apache's test page, which is served from /var/www/html/, the default root folder for the web server. This is what you will see whenever you point a domain to the server without configuring appropriate name-based virtual host rules. HTTP requests that can't be resolved to a virtual host will be directed to the server's default document root.

Apache HTTP Server test page

Configuration

Make a backup copy of Apache's main config file, /etc/httpd/conf/http.conf.

cd /etc/httpd/conf
sudo cp httpd.conf httpd.conf.backup

Edit /etc/httpd/conf/http.conf in your favourite text editor.

cd /etc/httpd/conf
sudo vi httpd.conf

Set the ServerAdmin email address.

ServerAdmin [email protected]

Find the <Directory /var/www/> block and change its contents as follows.

<Directory /var/www/>
    Require all denied
    AllowOverride None
    Options None
</Directory>

These are better defaults. Require all denied blocks access to the /var/www directory unconditionally. Later, when we configure the hosting for our web applications, we'll reinstate access to specific sub-directories under /var/www. The AllowOverride and Options settings provide stricter defaults for directories where access is reinstated.

Save and close the httpd.conf file. Anytime that you change Apache's configuration, or add or update a virtual host configuration, use the following commands to first test the configuration (the response you want is "Syntax OK") and then restart the server. Do this now to apply the above changes.

sudo apachectl configtest
sudo apachectl restart

Depending on the requirements of the web applications that you will be hosting, you might need to enable additional Apache modules. To get a full list of Apache modules that are available, listed alphabetically:

apachectl -M | sort

This command lists all modules that are available to the server, followed by a list of modules that have already been loaded. Modules that are flagged as static are compiled inside the Apache HTTP Server, while shared modules are those that can be dynamically loaded as and when required.

To enable extra Apache modules that are not listed, open the Apache base modules configuration file /etc/httpd/conf.modules.d/00-base.conf and uncomment the relevant LoadModule lines. The most useful Apache modules are loaded by default, so rarely should you need to make any changes here. Remember to test the configuration and restart the ``httpd` daemon to implement the changes.

LoadModule deflate_module modules/mod_deflate.so
LoadModule expires_module modules/mod_expires.so
LoadModule headers_module modules/mod_headers.so
LoadModule rewrite_module modules/mod_rewrite.so

Virtual Hosts

Follow these steps to configure virtual hosts, one for each web application that will be served from the box.

As root, create two folders inside the /etc/httpd/ directory, called sites-available and sites-enabled.

sudo mkdir /etc/httpd/sites-available
sudo mkdir /etc/httpd/sites-enabled

We will keep configurations for each virtual host in the sites-available folder. To enable a virtual host, we simply create a system link to the configuration file from sites-enabled. Thus, to disable a virtual host, all you will need to do is delete the syslink in sites-enabled, leaving the configuration file intact in sites-available.

Apache needs to be configured to read any .conf files that we put in /etc/httpd/sites-enabled. To do that, add the following line to the bottom of /etc/httpd/conf/httpd.conf and restart Apache.

IncludeOptional sites-enabled/*.conf

Create a configuration file in /etc/httpd/sites-available for each web application that you will be serving. Example:

sudo vi /etc/httpd/sites-available/www.example.com.conf

Here is a basic template.

<VirtualHost *:80>

    ServerName  www.example.com
    ServerAlias     example.com

    ServerAdmin [email protected]

    DocumentRoot /var/www/www.example.com/public/

    SetEnv APPLICATION_HOST production

    LogLevel warn
    ErrorLog /var/www/www.example.com/private/logs/apache/error.log
    CustomLog /var/www/www.example.com/private/logs/apache/access.log common

    <Directory "/var/www/www.example.com/public/">
        Require all granted
        AllowOverride All
        Options All -Indexes
    </Directory>

</VirtualHost>

This is just a basic template for a <VirtualHost> block. There is much more that we can do, and later in this tutoraial we will extend the configuration with SSL certificates and more. For now, the main setting to pay attention to is LogLevel. This specifies the minimum severity level of errors that should get logged. The default is "warn" and the higher-level "error" is also a good option for production servers. The "notice", "info" and "debug" levels tend to be more appropriate for development, testing and staging environments. This is the full list of LogLevel options, from most severe to least:

  • "emerg": Emergencies, system is unusable.
  • "alert": Immediate action required.
  • "crit": Critical conditions.
  • "error": Error conditions.
  • "warn": Warning conditions.
  • "notice": Normal but significant condition.
  • "info": Informational.
  • "debug": Debug-level messages.
  • "trace[1-8]": Trace messages.

Notice also that the <Directory> block loosens the restrictions that we applied earlier on the parent /var/www directory via Apache's global configuration file, httpd.conf. Require all granted enables public access to the web application's public directory, from where publicly-accessible web pages and other documents will be served. AllowOverride All effectively enables the use of .htaccess within the virtual host's document root and sub-directories. The Options directive controls which server features are available throughout the application's public directories. All options are enabled except MultiViews (a system of content negotiation) and automatic generation of directory indexes where index pages are not explicitly included in directories.

Another thing that I like to do is rotate Apache's log files every day, so that no one file becomes very large. The Apache HTTP Server ships with a handy program called rotatelogs, which makes the configuration a breeze.

ErrorLog "|/usr/sbin/rotatelogs -l /var/www/www.example.com/private/logs/apache/error-%Y-%m-%d.log 86400"
CustomLog "|/usr/sbin/rotatelogs -l /var/www/www.example.com/private/logs/apache/access-%Y-%m-%d.log 86400" common

Be sure to create all of the directories that are referenced throughout all of your virtual host configurations. This is important to avoid errors when Apache is started.

sudo mkdir -p /var/www/www.example.com/public
sudo mkdir -p /var/www/www.example.com/private/logs/apache

To enable a virtual host, create a symlink to the config file from the sites-enabled directory.

sudo ln -s /etc/httpd/sites-available/www.example.com.conf /etc/httpd/sites-enabled/www.example.com.conf

As always, test the configuration changes before restarting.

sudo apachectl configtest
sudo apachectl restart

Let's check that the virtual host routing works. Create a file called index.html in the host's document root.

sudo vi /var/www/www.example.com/public/index.html

Save the file with the following contents.

<h1>Hello, World.</h1>

Open a browser and go to http://www.example.com (or whatever domain you are using). If the DNS settings and Virtual Host configuration are all correct, you will see "Hello, World" in place of Apache's test page that you saw earlier.

PHP-FPM

For production environments, PHP-FPM is preferred over the standard Apache module (mod_php), for the simple reason that it is faster.

The PHP-FPM implementation of PHP will start its own built-in FastCGI server and listen for requests through the FastCGI protocol. The Apache HTTP Server will receive HTTP requests and handle them as normal, except in the case of calls to PHP scripts when Apache will delegate the requests to the FastCGI protocol, and from there PHP-FPM will take over.

The following instructions explain how to install PHP versions 5.6, 7.0 and 7.1, running under PHP-FPM. One of the disadvantages of using RHEL distributions over, say, Ubuntu, is that many packages that are available from official RHEL package repositories, including EPEL, are stale. As of April 2017, CentOS's package sources offer only PHP version 5.4, which is no longer supported and in fact reached end-of-life nearly two years earlier. See for yourself:

yum list available | grep php

So, we will need to get current versions of PHP from some place else. Thankfully, there is no need to compile PHP ourselves, as several alternative community-run RPM-compatible repositories have done the hard work for us. One option is the Webtatic Yum Repository, run by Andy Thompson. But in this tutorial I will show you how to install PHP from IUS ("Inline with Upstream Stable"), a community project that aims to promptly release RPM packages for new versions of popular software packages before they turn up in RHEL or EPEL.

To subscribe to the IUS repository, you need to install the ius-release RPM. Note that ius-release depends on epel-release, because several IUS packages have dependencies on EPEL. So be sure to install the epel-release RPM if you have not done so already.

sudo yum install epel-release

The commands to download and install the ius-release RPM for Centos 7 are as follows.

cd /tmp
sudo wget https://centos7.iuscommunity.org/ius-release.rpm
sudo rpm -Uvh ius-release.rpm
sudo rm ius-release.rpm

If the wget command is not installed, install it.

sudo yum install wget

Check that the IUS repositories are now listed alongside the EPEL and native CentOS repositories in /etc/yum.repos.d:

cd /etc/yum.repos.d
ls -la

I recommend updating all packages at this point:

sudo yum update

To check what PHP packages are now available for install:

yum list available | grep php

PHP 5.6 packages are prefixed php56u, PHP 7.0 packages are prefixed php70u and PHP 7.1 packages are prefixed php71u. The following instructions are for PHP 5.6. Just replace the prefix if you are installing a different PHP version.

To install PHP 5.6 running as PHP-FPM:

sudo yum install php56u-fpm

Follow the instructions to complete the installation.

Install any extra PHP modules that your applications depend on. Example:

sudo yum install php56u-soap php56u-gd php56u-mbstring php56u-mcrypt

I recommend installing PHPopcache. It improves PHP performance by storing precompiled script bytecode in shared memory, thereby removing the need for PHP to load and parse scripts on each request.

sudo yum install php56u-opcache

Enable PHP-FPM as a service, so it runs automatically at bootup.

sudo systemctl enable php-fpm.service

PHP configuration

Open php.ini in your favourite text editor.

sudo vi /etc/php.ini

I recommend setting the timezone. This one step will remove a whole class of warnings turning up in log files.

date.timezone = 'UTC'

Here are some other settings that you might adjust. Change the location of PHP error log and the default level of error reporting as appropriate for your need. (Note that the PHP-FPM process has a different error log, /var/log/php-fpm/error.log.)

expose_php = Off
error_log = "/var/log/php/error.log"
error_reporting = E_ERROR

Save your changes and restart both Apache and PHP-FPM.

sudo systemctl restart httpd.service
sudo systemctl restart php-fpm.service

PHP-FPM pools

By default there will be one PHP-FPM pool that runs all PHP scripts for all sites that are enabled on the server. (A PHP-FPM pool is just an ordinary Linux process than runs under a certain Linux user.) But it is good practice to have a separate PHP-FPM process, running under a separate Linux user, for each site. This is more secure and provides greater scope to optimise each site for speed and security independently.

For each site enabled on your server, repeat the following steps.

First, create a Unix user to run the site's PHP-FPM process. For consistency and clarity, I tend to use the site's FQDN for both the Linux username and the name of the corresponding PHP-FPM pool.

sudo useradd www.example.com

I like to have a group called "www", all members of which can read, write and execute any files contained under /var/www, the root directory for web applications. So, if a user requires such extensive privileges to files served by the HTTP server, I simply add the user to the www group. For example, people who need to upload stuff to the server will have their user accounts added to this group. Likewise, the owners of the PHP-FPM processes will be added to the www group so that they can read and execute the PHP scripts.

sudo groupadd www
sudo usermod -aG www www.example.com

Next, create a dedicated PHP-FPM pool for the web application. Each PHP-FPM pool should be configured in a file in the /etc/php-fpm.d directory.

sudo vi /etc/php-fpm.d/www.example.com.conf

Use the following as a template.

[www.example.com]

user   = www.example.com
group  = www

listen                 = /var/run/php-fpm/www.example.com.sock
listen.acl_groups      = apache
listen.allowed_clients = 127.0.0.1

pm                   = dynamic
pm.max_children      = 50
pm.start_servers     = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 35

catch_workers_output = yes

php_admin_value[error_log] = /var/www/www.example.com/private/logs/php/error.log
php_admin_value[memory_limit] = 128M
php_admin_value[session.save_handler] = files
php_admin_value[session.save_path]    = /var/www/www.example.com/private/sessions

php_admin_flag[allow_url_fopen] = off
php_admin_flag[log_errors] = on

This creates a FastCGI pool called "www.example.com". The pool is just a normal Linux process that runs under the Linux user "www.example.com" and the group "www". The name for each pool must be unique.

The process manager (pm) settings control the number of child processes, and are equivalent to tuning Apache's prefork settings when PHP is run as an Apache module.

The catch_workers_output directive is important. It captures error messages and allows them to be passed on to a custom error log that is written by PHP. This is configured in the PHP admin values section.

php_admin_value allows you to set custom PHP configuration values, overriding PHP's defaults. In the example above, php_admin_value is used to define a custom PHP error log for the web application, and to adjust the web application's memory limit and session handling. php_admin_flag is the same as php_admin_value except it is used to set boolean values as "on" and "off".

Apache VirtualHost changes

In the example above, the PHP-FPM process is configured to listen on a socket located at /var/run/php-fpm/www.example.com.sock. As with the pool name, the socket must be unique for each pool. At the moment, HTTP requests to "www.example.com" are being intercepted and handled by Apache. For this FQDN, we want Apache to delegate the processing of PHP scripts to the new PHP-FPM pool. To do that, we modify the Virtual Host configuration that contains the name-based rules for "www.example.com".

sudo vi /etc/httpd/sites-available/www.example.com.conf

Add the following inside the <VirtualHost> block, changing references to "www.example.com" as appropriate.

<FilesMatch \.php$>
    SetHandler "proxy:unix:/var/run/php-fpm/www.example.com.sock|fcgi://www.example.com/"
</FilesMatch>

<Proxy fcgi://www.example.com>
    ProxySet connectiontimeout=5 timeout=240
</Proxy>

While you are editing the virtual host configuration, enable the use of index.php files for handling requests for directory indexes. Add the DirectoryIndex directive to the <Directory> block.

<Directory "/var/www/www.example.com/public/">
    <IfModule dir_module>
        DirectoryIndex index.html index.php
    </IfModule>
</Directory>

The whole Apache <VirtualHost> block should now look like this:

<VirtualHost *:80>

    ServerName  www.example.com
    ServerAlias     example.com

    ServerAdmin [email protected]

    DocumentRoot /var/www/www.example.com/public/

    SetEnv APPLICATION_HOST production

    LogLevel error
    ErrorLog "|/usr/sbin/rotatelogs -l /var/www/www.example.com/private/logs/apache/error-%Y-%m-%d.log 86400"
    CustomLog "|/usr/sbin/rotatelogs -l /var/www/www.example.com/private/logs/apache/access-%Y-%m-%d.log 86400" common

    <FilesMatch \.php$>
        SetHandler "proxy:unix:/var/run/php-fpm/www.example.com.sock|fcgi://www.example.com/"
    </FilesMatch>

    <Proxy fcgi://www.example.com>
        ProxySet connectiontimeout=5 timeout=240
    </Proxy>

    <Directory "/var/www/www.example.com/public/">
        Require all granted
        AllowOverride All
        Options All -Indexes
        <IfModule dir_module>
            DirectoryIndex index.html index.php
        </IfModule>
    </Directory>

</VirtualHost>

Filesystem changes

Any directories that are referenced in PHP-FPM's configuration must actually exist.

sudo mkdir -p /var/www/www.example.com/private/logs/php
sudo mkdir -p /var/www/www.example.com/private/sessions

This is a good time to review ownership and permissions on the files and directories in the application's document root. I recommend that the document root and all of its subdirectories be owned by the same Linux user that owns the PHP-FPM process, and for them to be owned by the www group. We want group ownership to be sticky, so that any new files and directories that get created under the document route are automatically owned by the same user group. This makes it much easier to apply a blanket permissions policy to all files that are installed on the HTTP server.

cd /var/www
sudo chown -R www.example.com:www www.example.com
sudo chmod -R g+s www.example.com

Folder permissions should be set to 775 and file permissions to 664.

cd /var/www
sudo chmod -R 775 www.example.com
sudo find . -type f -exec chmod 664 {} +

Testing

You are now ready to restart Apache and PHP-FPM.

sudo systemctl restart httpd.service
sudo systemctl restart php-fpm.service

Double-check that both services are running.

systemctl status httpd.service
systemctl status php-fpm.service

To check that PHP-FPM is doing its job, throw a PHP file on the server...

sudo vi /var/www/www.example.com/public/index.php

... with the following contents:

<?php
    phpinfo();
    exit;

Delete the index.html test file, which lives in the same directory, that you created earlier.

sudo rm /var/www/www.example.com/public/index.html

Refresh http://www.example.com/ in your browser and you should now see the output of phpinfo().

Screenshot showing phpinfo() output

MySQL

MySQL is replaced with MariaDB in CentOS 7. At the time of writing this article, the MariaDB 5.5 Series is available from the base CentOS repository, while the MariaDB 10.0 Series is available from the IUS repository. See for yourself:

yum list available | grep mariadb

If you want to stick with MySQL, the easiest way to install and update MySQL on Linux Enterprise distributions such as CentOS is via the MySQL Yum Repository, which is maintained by Oracle, owners of MySQL.

Go to https://dev.mysql.com/downloads/repo/yum/ and look for the RPM package that is relevant for your operating system. For CentOS 7, you want the Red Hat Enterprise Linux 7 / Oracle Linux 7 RPM Package.

MySQL Yum Repository RPM Package for CentOS 7

Click the Download button. On the next screen, copy the URL for the download link at the bottom ("No thanks, just start my download"). At the time of writing this article, the URL of the link is https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm, but this may have changed if the RPM package has been updated since.

MySQL Yum Repository RPM Package for CentOS 7 - Download

Use wget to download and install the MySQL Yum Repository RPM Package on your Linux machine. You can then delete the .rpm file.

cd /tmp
wget http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
sudo rpm -Uvh mysql57-community-release-el7-9.noarch.rpm
sudo rm mysql57-community-release-el7-9.noarch.rpm

Two new repositories will have been added to the yum RPM client. To check:

ls -1 /etc/yum.repos.d/mysql-community*

There should be two items listed:

/etc/yum.repos.d/mysql-community.repo
/etc/yum.repos.d/mysql-community-source.repo

You now have the option of installing MySQL in place of MariaDB. The following instructions are for MySQL but will be similar for MariaDB.

The MySQL Community Server is installed from the MySQL Yum Repository with the command yum install mysql-community-server. This will install the latest GA release of MySQL. If you want to install a legacy release, you need to first change the configuration of the MyQL repository. Run the following command:

yum repolist all | grep mysql

This is the output of that command at the time of writing:

mysql-connectors-community/x86_64 MySQL Connectors Community     enabled:     30
mysql-connectors-community-source MySQL Connectors Community - S disabled
mysql-tools-community/x86_64      MySQL Tools Community          enabled:     43
mysql-tools-community-source      MySQL Tools Community - Source disabled
mysql-tools-preview/x86_64        MySQL Tools Preview            disabled
mysql-tools-preview-source        MySQL Tools Preview - Source   disabled
mysql55-community/x86_64          MySQL 5.5 Community Server     disabled
mysql55-community-source          MySQL 5.5 Community Server - S disabled
mysql56-community/x86_64          MySQL 5.6 Community Server     disabled
mysql56-community-source          MySQL 5.6 Community Server - S disabled
mysql57-community/x86_64          MySQL 5.7 Community Server     enabled:    166
mysql57-community-source          MySQL 5.7 Community Server - S disabled
mysql80-community/x86_64          MySQL 8.0 Community Server     disabled
mysql80-community-source          MySQL 8.0 Community Server - S disabled

Various MySQL tools are hosted in different sub-repositories, some of which are disabled by default. At the time of writing, the sub-repository for MySQL 5.7 Community Server is enabled and the sub-repositories for legacy releases and the future 8.0 release are disabled. So, running the command to install MySQL Community Server will install MySQL 5.7. If you want to install a different version, you need to change which of the MySQL sub-repositores are enabled and disabled. To do that, edit the file /etc/yum.repos.d/mysql-community.repo and toggle the enable parameters as required. For example, this is the configuration to disable the sub-repository for MySQL 5.7 Community Server.

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

If you enable more than one sub-repository for the MySQL Community Server, the latest release will be installed by yum. Run the following command again to check that the correct sub-repository is enabled:

yum repolist all | grep mysql

To install MySQL Community Server plus other required packages:

sudo yum install mysql-community-server

Start the server, and enable it to start automatically at boot-up time.

sudo systemctl start mysqld.service
sudo systemctl enable mysqld.service

To check which version of MySQL is running:

mysql --version

For MySQL 5.7 only, at the initial start up of the server, a temporary random password for MySQL's root superuser is set and stored in a log file. You will need to know that password. To reveal it, use the following command:

sudo grep 'temporary password' /var/log/mysqld.log

The program mysql_secure_installation allows you to quickly perform important configuration tasks that harden the security of the MySQL server.

mysql_secure_installation

At the prompt, enter the root user's existing password (for MySQL 5.7 only) and then set a new password for the root user. Next, you will the be presented with a series of questions. It is strongly recommended that you answer yes to all of them.

  • Remove anonymous users?
  • Disallow root login remotely?
  • Remove test database and access to it?
  • Reload privileges table now?

Controls

To start the MySQL server:

sudo systemctl start mysqld.service

To stop it:

sudo systemctl stop mysqld.service

To restart it:

sudo systemctl restart mysqld.service

To check the status of the MySQL server:

sudo service mysqld status

To connect to the server (when it is running):

mysql -u root -p

To exit the mysql> prompt and return to the shell commands:

quit;

To update the MySQL server software:

sudo yum update mysql-server

Configuration

Let's fine-tune the configuration of the MySQL server. Open the server's main configuration file in your favourite text editor.

sudo vi /etc/my.cnf

The following settings define a place to log any SQL statements that take more than a second to execute.

[mysqld]

slow_query_log_file=/var/log/mysql/slow_query.log
long_query_time=1
slow_query_log=1

By default, MySQL is configured to index words with four characters or more. This affects the behaviour of FULLTEXT searches. It means that words of two or three characters will not be searched. Adjust this behaviour via the ft_min_word_len setting.

[mysqld]

ft_min_word_len=3

I still come across legacy database systems that require strict mode to be disables. This allows values such as "" to be submitted to columns of other types, such as integers, without error. To disable strict mode, remove all of the default options from the sql_mode setting.

[mysqld]

sql_mode=""

Before restarting MySQL, syntax-check the my.cnf file. Check for [ERROR] messages near the top of the output.

mysqld --help

Also, be sure that the /var/log/mysql directory and the log files exist and have appropriate permissions.

sudo mkdir /var/log/mysql
sudo touch /var/log/mysql/slow_query.log
sudo chown mysql:mysql /var/log/mysql/slow_query.log

Restart MySQL for the configuration changes to take effect.

sudo systemctl restart mysqld.service

Login to MySQL.

mysql -u root -p

Type root's password at the prompt. Check that the slow query log works:

mysql> SELECT sleep(2);
mysql> quit;

Back at the shell prompt, type the following. You should see the log of the previous SELECT query, which took more than a second to run.

cat /var/log/mysql/slow_query.log

If you have already installed tables with FULLTEXT indexes, you must drop those indexes and recreate them for the changes to the ft_min_word_len setting to come into effect. It is sufficient to do a quick repair operation on the relevant tables. Log back in to MySQL and run:

mysql> REPAIR TABLE tbl_name QUICK;

To test the sql_mode setting:

mysql> SELECT @@GLOBAL.sql_mode;

If strict mode is disabled, the output will be:

+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+

Create users and databases

Instead of doing everything as the root user, with full access to all of the databases and every MySQL command, from now on it will be better to create limited accounts with specific permissions.

Log in to MySQL as root.

mysql -u root -p

Enter MySQL's root password at the prompt.

To create a database:

mysql> CREATE DATABASE <schemename>;

To create a user:

mysql> CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

Replace <username> and <password> as required. Passwords must be strong: a mix of letters, numbers and special characters is required.

At this point, the new user has no permissions to do anything with any databases. The user can't even login.

Here is a list of common permission types:

  • ALL PRIVILEGES: Full access to everything except GRANT OPTION.
  • CREATE: Create new tables or databases.
  • DROP: Delete tables or databases.
  • DELETE: Delete rows from tables.
  • INSERT: Insert rows into tables.
  • SELECT: Use the SELECT command.
  • UPDATE: Use the UPDATE command.
  • GRANT OPTION: Grant or remove other users' privileges.

This is the syntax to grant permissions to a user:

GRANT <permission> ON <scheme>.<table> TO '<username>'@'localhost';

To grant full access to all databases and all tables:

mysql> GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

The only thing this command does not do is permit the user to grant the same level of access to other users. To allow that, too:

mysql> GRANT GRANT OPTION ON *.* TO '<username>'@'localhost';

To limit access to a particular database:

mysql> GRANT ALL PRIVILEGES ON <scheme>.* TO '<username>'@'localhost';

To limit access to a particular table within a particular database:

mysql> GRANT ALL PRIVILEGES ON <scheme>.<table> TO '<username>'@'localhost';

To revoke a permission:

mysql> REVOKE <permission> ON <scheme>.<table> FROM '<username>'@'localhost';

To remove a user entirely:

mysql> DROP USER '<username>'@'localhost';

Run the following command for changes to user permissions to take effect. It's more convenient than restarting MySQL itself!

mysql> FLUSH PRIVILEGES;

To check the limited user accounts work as expected, quit MySQL and login again as one of your new users.

mysql> quit;
shell> mysql -u <username> -p

Enable SSL

To serve applications over HTTPS, the first step is to install Apache's SSL module.

sudo yum install mod_ssl

The mod_ssl package configures a self-signed certificate and installs it on your server. So you should already be able to access your web application over HTTPS. Test with curl, using the -k flag to accept certificates that are not signed by a trusted Certificate Authority. The output should be the HTML for your application's homepage.

curl -k https://www.example.com

Of course, using a self-signed certificate is not practical for production applications. You must install SSL certificates that are signed by a trusted Certificate Authority such as Verisign. You have two options. You can generate free SSL certificates using the Let's Encrypt service. Or you can buy SSL certificates from a vendor. The rest of this article will assume that you have done the latter.

Intsall your SSL certificates in the following location, replacing <hostname> with the primary fully-qualified domain name that the SSL certificate is configured to protect.

/etc/ssl/localcerts/<hostname>/<expiry_date>/

Modify the application's virtual host configuration. Here is a template. This redirects HTTP to HTTPS, and provides some additional security such as ensuring that all cookies apply the Secure flag.

<VirtualHost *:80>

    ServerName  www.example.com
    ServerAlias     example.com

    RewriteEngine on
    RewriteCond %{SERVER_NAME} =www.example.com [OR]
    RewriteCond %{SERVER_NAME} =example.com
    RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,QSA,R=permanent]

</VirtualHost>

<IfModule mod_ssl.c>
    <VirtualHost *:443>

        ServerName  www.example.com
        ServerAlias     example.com

        ServerAdmin [email protected]

        DocumentRoot /var/www/www.example.com/public/

        SetEnv APPLICATION_HOST production

        LogLevel error
        ErrorLog "|/usr/sbin/rotatelogs -l /var/www/www.example.com/private/logs/apache/error-%Y-%m-%d.log 86400"
        CustomLog "|/usr/sbin/rotatelogs -l /var/www/www.example.com/private/logs/apache/access-%Y-%m-%d.log 86400" common

        <FilesMatch \.php$>
            SetHandler "proxy:unix:/var/run/php-fpm/www.example.com.sock|fcgi://www.e$
        </FilesMatch>

        <Proxy fcgi://www.example.com>
            ProxySet connectiontimeout=5 timeout=240
        </Proxy>

        <Directory "/var/www/www.example.com/public/">
            Require all granted
            AllowOverride All
            Options All -Indexes
            <IfModule dir_module>
                DirectoryIndex index.html index.php
            </IfModule>
        </Directory>

        SSLEngine on

        SSLCertificateFile     /etc/ssl/localcerts/wildcard.example.com/2017-09-01/certificate.cer
        SSLCertificateKeyFile  /etc/ssl/localcerts/wildcard.example.com/2017-09-01/private.key
        SSLCACertificateFile   /etc/ssl/localcerts/wildcard.example.com/2017-09-01/geotrust.ca.cer

        # HSTS:
        Header always set Strict-Transport-Security "max-age=63072000; includeSubdomains"
        Header always set X-Frame-Options DENY
        Header always set X-Content-Type-Options nosniff

        # Apply the Secure flag to all cookies
        Header edit Set-Cookie (?i)^(.*)(;\s*secure)??((\s*;)?(.*)) "$1; Secure$3$4"

    </VirtualHost>
</IfModule>

Restart the server for the changes to take full effect.

sudo apachectl restart

Reload your web application using the https:// scheme. Information on the new SSL certificate will be available via your web browser's address bar. There are also a couple of useful online tools where you can verify the status of your SSL certificate and review the level of security that your SSL configuration provides:

To get a perfect A+ score, you should harden your SSL configuration. Open Apache's main configuration SSL configuration file.

sudo nano /etc/httpd/conf.d/ssl.conf

Find the SSLProtocol and SSLCipherSuite lines and comment them out.

# SSLProtocol all -SSLv2
# SSLCipherSuite HIGH:MEDIUM:!aNULL:!MD5:!SEED:!IDEA

At the very bottom of the file, after the closing of the <VirtualHost> block, enter the following:

SSLCipherSuite EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH
SSLProtocol All -SSLv2 -SSLv3
SSLHonorCipherOrder On

SSLCompression off 
SSLUseStapling on 
SSLStaplingCache "shmcb:logs/stapling-cache(150000)" 

# Requires Apache >= 2.4.11
#SSLSessionTickets Off

The last directive, SSLSessionTickets, is understood only by Apache >= 2.4.11. Earlier releases will fail if this directive is included in the server's configuration. Check which version of Apache you are running with the command httpd -v.

Check your configuration for syntax errors and then, if the syntax is OK, restart the Apache service.

sudo apachectl configtest
sudo apachectl restart

Re-test your web application against the Qualys SSL Server Test tool. This configuration should give you a perfect A+ score. Be aware that this configuration is compatible with modern A grade browsers but not legacy clients such as Internet Explorer 6.

Composer

You now have all of the core components of a LAMP stack in place. The rest of this article will explain how to add some optional extras, starting with Composer, the de facto package manager for PHP application development.

cd /tmp
curl -sS https://getcomposer.org/installer | php
sudo mv composer.phar /usr/local/bin/composer

You can now use the composer command globally. From your application's root directory, install the project's dependencies. The following command will ignore the project's require-dev dependencies and install only the dependencies needed in production.

composer install --no-dev

To update the installed dependencies to the latest versions:

composer update --no-dev

It is recommended that you do NOT run these commands as the root superuser. Instead, SSH into the box as a limited user who is a member of the www user group. This is good practice for security reasons and it will ensure that all of the contents of the /vendor directory are also owned by the www group.

Memcached

To install the Memcached daemon, run the following tasks as the root user.

sudo yum install libevent libevent-devel
sudo yum install memcached

For PHP applications to be able to interact with the Memcached daemon, you will need to install a PHP extension. There are two options: pecl-memcache (no "d" on the end) and pecl-memcached (ending with a "d"). pecl-memcached is the newer of the two and is better maintained, and is generally preferred for these reasons. On the other hand, pecl-memcache enjoys better support on Windows, which may be an important consideration if you have development repositories on that OS. The two PHP Memcached clients expose incompatible APIs, so you should choose one of the clients and install it throughout all of your application's various host environments.

To find out which extensions are available for you to install, run the following command:

yum search memcache | grep php

Don't forget, you need an extension that is compatible with your version of PHP. For example, you might choose the php56u-pecl-memcached.x86_64 package if you are running PHP 5.6, or php70u-pecl-memcached.x86_64 if you are running PHP 7.0.

Install your chosen package in the normal way.

sudo yum install php56u-pecl-memcached.x86_64

Now to configure the Memcached daemon itself. Using sudo, open /etc/sysconfig/memcached in a text editor and change the CACHESIZE and OPTIONS values as follows:

PORT="11211"
USER="memcached"
MAXCONN="1024"
CACHESIZE="1GB"
OPTIONS="localhost"

Enable the Memcached daemon to start at bootup, and start the daemon:

sudo systemctl enable memcached.service
sudo systemctl restart memcached.service

Both Apache and PHP-FPM need to be restarted.

sudo systemctl restart httpd.service
sudo systemctl restart php-fpm.service

To check that everything works, create a phpinfo.php file in a web directory.

<?php
    phpinfo();
    exit;

Go that page in a web browser. Look for a "memcache" or "memcached" section, depending on which client you installed.

The memcached extension showing in the output of phpinfo()

To test the API of the pecl-memcache (no "d") extension, run the following test script. The script writes to the store an object with two values, and sets the item's expiry to 10 seconds. The cached object is then immediately retrieved from the store and printed.

<?php
    $memcache = new \Memcache();
    $memcache->connect('localhost', 11211);

    $tmp = new \stdClass();
    $tmp->str_attr = 'test';
    $tmp->int_attr = 123;
    $memcache->set('key', $tmp, false, 10);

    var_dump($memcache->get('key'));

Here is an equivalent test script for the pecl-memcached (with a "d") extension:

<?php
    $memcached = new \Memcached();
    $memcached->addServer('localhost', 11211);

    $tmp = new \stdClass();
    $tmp->str_attr = 'test';
    $tmp->int_attr = 123;
    $memcached->set('key', $tmp, 10);

    var_dump($memcached->get('key'));

The Memcached daemon can be controlled from the command line but requires you to connect to it via telnet (which you may need to install via the yum RPM client).

telnet localhost 11211

Once connected, an extensive API allows you to inspect and manage the Memcached instance. For example, to print a load of statistics about the state of the store, including memory consumption, type:

stats

To wipe everything from the store:

flush_all

To terminate the telnet session and return to the normal shell environment:

quit

For more commands see http://lzone.de/cheat-sheet/memcached.

Deployment

Deploy your web application by uploading the source files to the server via the SCP or SFTP protocols. Popular clients include FileZilla and (my personal favourite) WinSCP. Alternatively you might enable continuous deployment with Git.