How to install and configure Memcached

Memcached is commonly used to alleviate backend database contention by temporarily storing recently requested database records in memory. As a result, the database traffic is reduced as Memcached is able to pull the records from cache.

Installing Memcached is quick. However there are a number of steps that need to be taken to secure Memcached. Memcached has the potential to store a lot of sensitive information, so its critical that the service is locked down properly to prevent outside access or data leakage.

Installation

# CentOS 6
[[email protected] ~]# yum install memcached
[[email protected] ~]# chkconfig memcached on
[[email protected] ~]# service memcached start

# CentOS 7
[[email protected] ~]# yum install memcached
[[email protected] ~]# systemctl enable memcached
[[email protected] ~]# systemctl start memcached

# Ubuntu 14.04 and 16.04
[[email protected] ~]# apt-get update
[[email protected] ~]# apt-get install memcached

Configuration

Memcached listens on port 11211 by default and does not have any type of restrictions built in to prevent it from being queried from the public internet. If you do not protect Memcached with a firewall, there is a extremely high risk of leaking sensitive data. In recent years, unprotected memcached servers have also been exploited to launch DDoS amplification attacks.

If you do not have a dedicated firewall, use your OS’s built in firewall to only allow in connections for trusted web servers using their internal IP’s. Some quick examples are below:

# iptables
[[email protected] ~]# vim /etc/sysconfig/iptables
...
-A INPUT -p tcp -m tcp --dport 11211 -s client_server_private_IP -m comment --comment "memcached" -j ACCEPT
service iptables restart

# firewalld
[[email protected] ~]# firewall-cmd --permanent --new-zone=memcached
[[email protected] ~]# firewall-cmd --permanent --zone=memcached --add-port=11211/tcp
[[email protected] ~]# firewall-cmd --permanent --zone=memcached --add-source=client_server_private_IP
[[email protected] ~]# firewall-cmd --reload

# ufw
[[email protected] ~]# ufw allow from client_server_private_IP/32 to any port 11211

When configuring memcached itself, there are a few options to set. In my case, to help limit exposure to the service, I want to set Memcached to only listen on my private network interface and disable UDP. I’ll also set max connections to be 16384 and set the max cachesize to be 1.5G (1536M). Adjust the connections and cachesize as needed for your situation. Below is the example using the options above:

# CentOS 6 and 7
[[email protected] ~]# vim /etc/sysconfig/memcached

# Ubuntu 14.04 and 16.04
[[email protected] ~]# vim /etc/memcached.conf

The configuration options to use to reflect the requirements above:

PORT="11211"
USER="memcached"
MAXCONN="16384"
CACHESIZE="1536"
OPTIONS="-l memcached_servers_private_IP -U 0"

Then restart memcached

[[email protected] ~]# service memcached restart

Client setup

The typical use cases I run into on a day to day basis are clients using memcached for their PHP application. Memcached can be used to store cached content, or it can be used to centrally store sessions. Therefore these examples will be PHP focused.

Client setup – General data caching

For storing data, there is nothing that needs to be configured on the client side. The application code itself is what controls storing content within Memcached. To ensure memcached can be reached to store content, create the following test script:

[[email protected] ~]# vim /var/www/html/test-memcached.php
if (class_exists('Memcache')) {
    $meminstance = new Memcache();
} else {
    $meminstance = new Memcached();
}

$meminstance->addServer("memcached_servers_private_IP", 11211);

$result = $meminstance->get("test");

if ($result) {
    echo $result;
} else {
    echo "No matching key found.  Refresh the browser to add it!";
    $meminstance->set("test", "Successfully retrieved the data!") or die("Couldn't save anything to memcached...");
}

Then run it in your browser. You can further confirm it works by running it on the command line and confirming ‘cmd_set’ increments by one indicating it was able to store the object:

[[email protected] ~]# echo stats | nc localhost 11211 | grep cmd_set; curl localhost/test-memcached.php; echo stats | nc localhost 11211 |grep cmd_set
STAT cmd_set 2
No matching key found.  Refresh the browser to add it!STAT cmd_set 3

[[email protected] ~]# echo stats | nc localhost 11211 | grep cmd_set; curl localhost/test-memcached.php; echo stats | nc localhost 11211 |grep cmd_set
STAT cmd_set 3
Successfully retrieved the data!STAT cmd_set 3

Digital Ocean has a good article that goes into far more detail on various ways to test/use memcached:
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-memcache-on-ubuntu-14-04

Client setup – Storing sessions in memcached

To have memcached act as a central server for sessions, some additional configuration is needed on each client web server. Install php-memcache for your version of PHP. Assuming the default PHP version is installed from the package manager, you can install it by:

# Red Hat / CentOS:
[[email protected] ~]# yum install php-pecl-memcached
[[email protected] ~]# service httpd graceful
[[email protected] ~]# php -m |grep memcached

# Ubuntu
[[email protected] ~]# apt-get update
[[email protected] ~]# apt-get install php-pecl-memcached
[[email protected] ~]# service apache2 graceful
[[email protected] ~]# php -m |grep memcached

Then update php.ini on both server as follows:

session.save_handler = memcached
session.save_path="memcached_servers_private_IP:11211?persistent=1&weight=1&timeout=1&retry_interval=15"

On CentOS and Red Hat servers, depending on what version of PHP was installed and how, you may have to update another file as it will override the php.ini. Only change this if the values exist and are configured for files:

[[email protected] ~]# vim /etc/httpd/conf.d/php.conf
php_value session.save_handler "memcached"
php_value session.save_path    "memcached_servers_private_IP:11211?persistent=1&weight=1&timeout=1&retry_interval=15"

Test to ensure sessions are now being stored in memcached:

[[email protected] ~]# vim /var/www/html/test-sessions.php
<?php
session_start();
?>
Created a session

Perform the test by running the following on the command line and confirming ‘cmd_set’ increases, indicating it was able to store the session:

[[email protected] ~]# echo stats | nc localhost 11211 | grep cmd_set; curl localhost/session.php; echo stats | nc localhost 11211 |grep cmd_set
STAT cmd_set 17
Created a session
STAT cmd_set 19

Client setup – Distributed sessions across multiple Memcached instances

Building solutions that can withstand failure is always recommended. Having a Memcached server go offline that is storing sessions will most likely result in unhappy customers. Memcached does not have a built in mechanism to replicate data between multiple memcached servers. This functionality is instead done on the client side.

To allow another Memcached server to take over connections without replicating the session data, update the php.ini on the web servers with the snippet below and restart Apache:

memcache.hash_strategy = consistent
session.save_handler = memcache
memcache.allow_failover = 1
session.save_path="memcached_servers_private_IP:11211?persistent=1&weight=1&timeout=1&retry_interval=15,memcached_servers_private_IP:11211?persistent=1&weight=1&timeout=1&retry_interval=15"

If you wanted to have automatic failure and also ensure that the sessions are replicated to each Memcached server, update the php.ini on the web servers with the snippet below and restart Apache:

memcache.hash_strategy = consistent
memcache.session_redundancy=3
memcache.allow_failover = 1
session.save_handler = memcache
session.save_path="memcached_servers_private_IP:11211?persistent=1&weight=1&timeout=1&retry_interval=15,memcached_servers_private_IP:11211?persistent=1&weight=1&timeout=1&retry_interval=15"

Important note: To determine what memcache.session_redundacy should be set to, simply total up all the Memcached servers and add 1 to that total. So in the example above, I have 2 Memcached servers. Therefore, the memcache.session_redundacy should be set to 3.

Troubleshooting

Confirm the web server can reach the memcached server:

[[email protected] ~]# telnet memcached_servers_private_IP 11211

Verify traffic is being sent from the web servers to the memcached server:

[[email protected] ~]# tcpdump -i any port 11211

Checking memcached stats:

[[email protected] ~]# echo stats | nc localhost 11211

To see some of the more commonly used stats, use:

[[email protected] ~]# echo stats | nc localhost 11211 |grep -E 'total_connections|curr_connections|limit_maxbytes|bytes'

Check to see if you may need to increase the size of the cache. If bytes is reaching the total memory allocation defined by ‘limit_maxbytes’, you may need to increase the cachesize setting:

[[email protected] ~]# echo stats | nc localhost 11211 |grep -E 'bytes|limit_maxbytes' |grep -v bytes_ |grep -v _bytes

To flush all the data within memcache:

[[email protected] ~]# echo flush_all | nc localhost 11211

To retrieve the version of memcached:

[[email protected] ~]# echo version | nc localhost 11211

Ubuntu 16.04 Apache 2.4 with PHP-FPM

PHP-FPM does have some advantages depending on the solution and the common path is to use Nginx with PHP-FPM. However what happens when you want to utilize the normal features of Apache, such as basics like .htaccess files, but still keep the tuning options open that come with PHP-FPM? Well, there is a module for that!

This guide is going to assume a fresh Ubuntu 16.04 server to illustrate everything from start to finish, and will assume that all sites on this server will use the same php-fpm pool.

First, installed the required packages for your web server:

[[email protected] ~]# apt-get update
[[email protected] ~]# apt-get install php7.0-fpm apache2

Now confirm or update the Apache configuration to use the mpm_event_module instead of the mpm_prefork_module:

[[email protected] ~]# a2enmod actions
[[email protected] ~]# apache2ctl -M | grep mpm
[[email protected] ~]# a2dismod mpm_prefork
[[email protected] ~]# a2dismod mpm_worker
[[email protected] ~]# a2enmod mpm_event

Then tell Apache to send all PHP requests over to PHP-FPM by creating a new configuration file:

[[email protected] ~]# vim /etc/apache2/conf-available/php.conf
<FilesMatch \.php$>
	SetHandler "proxy:unix:/run/php/php7.0-fpm.sock|fcgi://localhost/"
</FilesMatch>

Enable the new Apache PHP configuration:

[[email protected] ~]# a2enconf php.conf

Confirm PHP-FPM is set to use sockets instead of TCP connections for performance purposes, and also confirm the following additional settings:

[[email protected] ~]# vim /etc/php/7.0/fpm/pool.d/www.conf
; listen = 127.0.0.1:9000
listen = /run/php/php7.0-fpm.sock
...
listen.owner = www-data
listen.group = www-data
listen.mode = 0660
user = www-data
group = www-data

Enable FCGI proxy, then restart Apache and PHP-FPM to apply the changes above:

[[email protected] ~]# a2enmod proxy_fcgi
[[email protected] ~]# systemctl restart apache2
[[email protected] ~]# systemctl restart php7.0-fpm

If you are using a software firewall on the server, open ports 80/443 accordingly. This example will open them up to the world. Adjust yours accordingly:

[[email protected] ~]# ufw allow 80
[[email protected] ~]# ufw allow 443

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at x.x.x.x/info.php:

[[email protected] ~]# vim /var/www/html/info.php
<?php phpinfo(); ?>

And your done!

Using multiple PHP-FPM pools

What happens if you want to isolate each site to their own PHP-FPM pool instead of using a shared pool? That is easy enough to do. Assuming that you followed everything in this guide to get to this point, do the following.

First, disable the global Apache configuration for PHP:

[[email protected] ~]# a2disconf php.conf

Create a new PHP-FPM pool for this specific site and update it accordingly:

[[email protected] ~]# cp /etc/php/7.0/fpm/pool.d/www.conf /etc/php/7.0/fpm/pool.d/example.com.conf
[[email protected] ~]# vim /etc/php/7.0/fpm/pool.d/example.com.conf
; Start a new pool named 'www'.
; the variable $pool can be used in any directive and will be replaced by the
; pool name ('www' here)
[example.com]
...
; listen = 127.0.0.1:9000
listen = /run/php/www.example.com-php7.0-fpm.sock
...
listen.owner = www-data
listen.group = www-data
listen.mode = 0660
user = www-data
group = www-data

Then update the site’s Apache vhost to point to a new PHP-FPM pool in both the 80 and 443 stanzas. Be sure to update the socket accordingly for your site in the 2 sections below! (ie: unix:/run/php/www.example.com-php7.0-fpm.sock)

[[email protected] ~]# vim /etc/httpd/vhost.d/example.com.conf
<VirtualHost *:80>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

	# Send PHP requests to php-fpm
        <FilesMatch \.php$>
                SetHandler "proxy:unix:/run/php/www.example.com-php7.0-fpm.sock|fcgi://localhost/"
        </FilesMatch>

...
<VirtualHost *:443>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

	# Send PHP requests to php-fpm
        <FilesMatch \.php$>
                SetHandler "proxy:unix:/run/php/www.example.com-php7.0-fpm.sock|fcgi://localhost/"
        </FilesMatch>
...

Enable FCGI proxy, then restart Apache and PHP-FPM to apply the changes above:

[[email protected] ~]# a2enmod proxy_fcgi
[[email protected] ~]# systemctl restart php7.0-fpm
[[email protected] ~]# systemctl restart apache2

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at example.com/info.php:

[[email protected] ~]# vim /var/www/vhosts/example.com/info.php
<?php phpinfo(); ?>

And your done!

Ubuntu 14.04 Apache 2.4 with PHP-FPM

PHP-FPM does have some advantages depending on the solution and the common path is to use Nginx with PHP-FPM. However what happens when you want to utilize the normal features of Apache, such as basics like .htaccess files, but still keep the tuning options open that come with PHP-FPM? Well, there is a module for that!

This guide is going to assume a fresh Ubuntu 14.04 server to illustrate everything from start to finish, and will assume that all sites on this server will use the same php-fpm pool.

First, installed the required packages for your web server:

[[email protected] ~]# apt-get update
[[email protected] ~]# apt-get install php5-fpm apache2 libapache2-mod-fastcgi

Now update the Apache configuration to use the mpm_event_module instead of the mpm_prefork_module:

[[email protected] ~]# a2enmod actions
[[email protected] ~]# apache2ctl -M | grep mpm
[[email protected] ~]# a2dismod mpm_prefork
[[email protected] ~]# a2dismod mpm_worker
[[email protected] ~]# a2enmod mpm_event

Then tell Apache to send all PHP requests over to PHP-FPM by creating a new configuration file:

[[email protected] ~]# vim /etc/apache2/conf-available/php.conf

<IfModule mod_fastcgi.c>
        AddHandler php5.fcgi .php
        Action php5.fcgi /php5.fcgi
        Alias /php5.fcgi /usr/lib/cgi-bin/php5.fcgi
        FastCgiExternalServer /usr/lib/cgi-bin/php5.fcgi -socket /var/run/php-fpm.sock -pass-header Authorization -idle-timeout 3600
        <Directory /usr/lib/cgi-bin>
                Require all granted
        </Directory>
</IfModule>

Enable the new Apache PHP configuration:

[[email protected] ~]# a2enconf php.conf

Confirm PHP-FPM is set to use sockets instead of TCP connections for performance purposes, and also confirm the following additional settings:

[[email protected] ~]# vim /etc/php5/fpm/pool.d/www.conf
; listen = 127.0.0.1:9000
listen = /var/run/php-fpm.sock
...
listen.owner = www-data
listen.group = www-data
listen.mode = 0660
user = www-data
group = www-data

Restart Apache and PHP-FPM to apply the changes:

[[email protected] ~]# service apache2 restart
[[email protected] ~]# service php5-fpm restart

If you are using a software firewall on the server, open ports 80/443 accordingly. This example will open them up to the world. Adjust yours accordingly:

[[email protected] ~]# ufw allow 80
[[email protected] ~]# ufw allow 443

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at x.x.x.x/info.php:

[[email protected] ~]# vim /var/www/html/info.php
<?php phpinfo(); ?>

And your done!

Using multiple PHP-FPM pools

What happens if you want to isolate each site to their own PHP-FPM pool instead of using a shared pool? That is easy enough to do. Assuming that you followed everything in this guide to get to this point, do the following.

First, disable the global Apache configuration for PHP:

[[email protected] ~]# a2disconf php.conf

Create a new PHP-FPM pool for this specific site and update it accordingly:

[[email protected] ~]# cp /etc/php5/fpm/pool.d/www.conf /etc/php5/fpm/pool.d/example.com.conf
[[email protected] ~]# vim /etc/php5/fpm/pool.d/example.com.conf
; listen = 127.0.0.1:9000
listen = /var/run/www.example.com-php5-fpm.sock
...
listen.owner = www-data
listen.group = www-data
listen.mode = 0660
user = www-data
group = www-data

Then update the site’s Apache vhost to point to a new PHP-FPM pool in both the 80 and 443 stanzas. Be sure to update the socket accordingly for your site in the 2 sections below! (ie: -socket /var/run/www.example.com-php5-fpm.sock)

[[email protected] ~]# vim /etc/apache2/sites-enabled/example.com.conf
<VirtualHost *:80>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

	# Send PHP requests to php-fpm
	<IfModule mod_fastcgi.c>
		AddHandler php5.fcgi .php
		Action php5.fcgi /php5.fcgi
		Alias /php5.fcgi /usr/lib/cgi-bin/php5.fcgi
		FastCgiExternalServer /usr/lib/cgi-bin/php5.fcgi -socket /var/run/www.example.com-php5-fpm.sock -pass-header Authorization -idle-timeout 3600
		<Directory /usr/lib/cgi-bin>
			Require all granted
		</Directory>
	</IfModule>
...

<VirtualHost *:443>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

	# Send PHP requests to php-fpm
	<IfModule mod_fastcgi.c>
		AddHandler php5.fcgi .php
		Action php5.fcgi /php5.fcgi
		Alias /php5.fcgi /usr/lib/cgi-bin/php5.fcgi
		FastCgiExternalServer /usr/lib/cgi-bin/php5.fcgi -socket /var/run/www.example.com-php5-fpm.sock -pass-header Authorization -idle-timeout 3600
		<Directory /usr/lib/cgi-bin>
			Require all granted
		</Directory>
	</IfModule>
...

Then restart the services:

[[email protected] ~]# systemctl restart php5-fpm
[[email protected] ~]# systemctl restart apache2

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at example.com/info.php:

[[email protected] ~]# vim /var/www/vhosts/example.com/info.php
<?php phpinfo(); ?>

And your done!

CentOS 7 Apache 2.4 with PHP-FPM

PHP-FPM does have some advantages depending on the solution and the common path is to use Nginx with PHP-FPM. However what happens when you want to utilize the normal features of Apache, such as basics like .htaccess files, but still keep the tuning options open that come with PHP-FPM? Well, there is a module for that!

This guide is going to assume a fresh CentOS 7 server to illustrate everything from start to finish, and will assume that all sites on this server will use the same php-fpm pool.

First, installed the required packages for your web server:

[[email protected] ~]# yum install httpd httpd-tools mod_ssl php-fpm

Now update the Apache configuration to use the mpm_event_module instead of the mpm_prefork_module:

[[email protected] ~]# vim /etc/httpd/conf.modules.d/00-mpm.conf 
# LoadModule mpm_prefork_module modules/mod_mpm_prefork.so
LoadModule mpm_worker_module modules/mod_mpm_worker.so

Then tell Apache to send all PHP requests over to PHP-FPM by creating a new configuration file:

[[email protected] ~]# vim /etc/httpd/conf.d/php.conf

# Tell the PHP interpreter to handle files with a .php extension.

# Proxy declaration
<Proxy "unix:/var/run/php-fpm/default.sock|fcgi://php-fpm">
	# we must declare a parameter in here (doesn't matter which) or it'll not register the proxy ahead of time
    	ProxySet disablereuse=off
</Proxy>

# Redirect to the proxy
<FilesMatch \.php$>
	SetHandler proxy:fcgi://php-fpm
</FilesMatch>

#
# Allow php to handle Multiviews
#
AddType text/html .php

#
# Add index.php to the list of files that will be served as directory
# indexes.
#
DirectoryIndex index.php

#
# Uncomment the following lines to allow PHP to pretty-print .phps
# files as PHP source code:
#
#<FilesMatch \.phps$>
#	SetHandler application/x-httpd-php-source
#</FilesMatch>

Tweak PHP-FPM to use sockets instead of TCP connections for performance purposes as follows:

[[email protected] ~]# vim /etc/php-fpm.d/www.conf
; listen = 127.0.0.1:9000
listen = /var/run/php-fpm/default.sock
...
listen.allowed_clients = 127.0.0.1
listen.owner = apache
listen.group = apache
listen.mode = 0660
user = apache
group = apache

And lastly, enable the services to start on boot and start them up:

[[email protected] ~]# systemctl enable php-fpm
[[email protected] ~]# systemctl enable httpd
[[email protected] ~]# systemctl start php-fpm
[[email protected] ~]# systemctl start httpd

If you are using a software firewall on the server, open ports 80/443 accordingly. This example will open them up to the world. Adjust yours accordingly:

[[email protected] ~]# firewall-cmd --zone=public --permanent --add-service=http
[[email protected] ~]# firewall-cmd --zone=public --permanent --add-service=https
[[email protected] ~]# firewall-cmd --reload

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at x.x.x.x/info.php:

[[email protected] ~]# vim /var/www/html/info.php
<?php phpinfo(); ?>

And your done!

Using multiple PHP-FPM pools

What happens if you want to isolate each site to their own PHP-FPM pool instead of using a shared pool? That is easy enough to do. Assuming that you followed everything in this guide to get to this point, do the following.

First, disable the global Apache configuration for PHP:

[[email protected] ~]# mv /etc/httpd/conf.d/php.conf /etc/httpd/conf.d/php.conf.bak

Create a new PHP-FPM pool for this specific site and update it accordingly:

[[email protected] ~]# cp /etc/php-fpm.d/www.conf /etc/php-fpm.d/example.com.conf
[[email protected] ~]# vim /etc/php-fpm.d/example.com.conf
; listen = 127.0.0.1:9000
listen = /var/run/php-fpm/example.com.sock
...
listen.allowed_clients = 127.0.0.1
listen.owner = apache
listen.group = apache
listen.mode = 0660
user = apache
group = apache

Then update the site’s Apache vhost to point to a new PHP-FPM pool in both the 80 and 443 stanzas. Be sure to update the socket accordingly for your site in the 2 sections below! (ie: unix:/var/run/php-fpm/example.com.sock)

[[email protected] ~]# vim /etc/httpd/vhost.d/example.com.conf
<VirtualHost *:80>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

        # Proxy declaration
        <Proxy "unix:/var/run/php-fpm/example.com.sock|fcgi://php-fpm">
                # we must declare a parameter in here (doesn't matter which) or it'll not register the proxy ahead of time
                ProxySet disablereuse=off
                # Note: If you configure php-fpm to use the "ondemand" process manager, then use "ProxySet disablereuse=on"
        </Proxy>

        # Redirect to the proxy
        <FilesMatch \.php$>
                SetHandler proxy:fcgi://php-fpm
        </FilesMatch>
...
<VirtualHost *:443>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

        # Proxy declaration
        <Proxy "unix:/var/run/php-fpm/example.com.sock|fcgi://php-fpm">
                # we must declare a parameter in here (doesn't matter which) or it'll not register the proxy ahead of time
                ProxySet disablereuse=off
                # Note: If you configure php-fpm to use the "ondemand" process manager, then use "ProxySet disablereuse=on"
        </Proxy>

        # Redirect to the proxy
        <FilesMatch \.php$>
                SetHandler proxy:fcgi://php-fpm
        </FilesMatch>
...

Then restart the services:

[[email protected] ~]# systemctl restart php-fpm
[[email protected] ~]# systemctl restart httpd

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at example.com/info.php:

[[email protected] ~]# vim /var/www/vhosts/example.com/info.php
<?php phpinfo(); ?>

And your done!

CentOS 6 Apache 2.4 with PHP-FPM

PHP-FPM does have some advantages depending on the solution and the common path is to use Nginx with PHP-FPM. However what happens when you want to utilize the normal features of Apache, such as basics like .htaccess files, but still keep the tuning options open that come with PHP-FPM? Well, there is a module for that!

This guide is going to assume a fresh CentOS 6 server to illustrate everything from start to finish, and will assume that all sites on this server will use the same php-fpm pool.

Apache 2.2 has no native modules for working with fastcgi. So the options would be to install mod_fastcgi from source or use a older SRPM from repos that may not be too well known or maintained. As both those options are less than ideal, we will be installing Apache 2.4 from the IUS repository to avoid the patch management issues associated with source installations.

First, install the repos needed for the updated packages:

[[email protected] ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
[[email protected] ~]# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/6/x86_64/ius-release-1.0-15.ius.centos6.noarch.rpm

Then install the required packages for your web server:

[[email protected] ~]# yum install httpd24u php56u-fpm

Now update the Apache configuration to use the mpm_event_module instead of the mpm_prefork_module:

[[email protected] ~]# vim /etc/httpd/conf.modules.d/00-mpm.conf 
# LoadModule mpm_prefork_module modules/mod_mpm_prefork.so
LoadModule mpm_worker_module modules/mod_mpm_worker.so

Then tell Apache to send all PHP requests over to PHP-FPM by creating a new configuration file:

[[email protected] ~]# vim /etc/httpd/conf.d/php.conf

# Tell the PHP interpreter to handle files with a .php extension.

<Proxy "unix:/var/run/php-fpm/default.sock|fcgi://php-fpm">
	# we must declare a parameter in here (doesn't matter which) or it'll not register the proxy ahead of time
	# Note: If you configure php-fpm to use the "ondemand" process manager, then use "ProxySet disablereuse=on"
	ProxySet disablereuse=off
</Proxy>

# Redirect to the proxy
<FilesMatch \.php$>
	SetHandler proxy:fcgi://php-fpm
</FilesMatch>

Tweak PHP-FPM to use sockets instead of TCP connections for performance purposes as follows:

[[email protected] ~]# vim /etc/php-fpm.d/www.conf
; listen = 127.0.0.1:9000
listen = /var/run/php-fpm/default.sock
...
listen.owner = apache
listen.group = apache
listen.mode = 0660
user = apache
group = apache

Enable the services to start on boot and start them up:

[[email protected] ~]# chkconfig php-fpm on
[[email protected] ~]# chkconfig httpd on
[[email protected] ~]# service php-fpm start
[[email protected] ~]# service httpd start

If you are using a software firewall on the server, open ports 80/443 accordingly. This example will open them up to the world. Adjust yours accordingly:

[[email protected] ~]# vim /etc/sysconfig/iptables
-A INPUT -p tcp -m tcp --dport 443 -j ACCEPT 
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT 
[[email protected] ~]# service iptables restart

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at x.x.x.x/info.php:

[[email protected] ~]# vim /var/www/html/info.php
<?php phpinfo(); ?>

And your done!

Using multiple PHP-FPM pools

What happens if you want to isolate each site to their own PHP-FPM pool instead of using a shared pool? That is easy enough to do. Assuming that you followed everything in this guide to get to this point, do the following.

First, disable the global Apache configuration for PHP:

[[email protected] ~]# mv /etc/httpd/conf.d/php.conf /etc/httpd/conf.d/php.conf.bak

Create a new PHP-FPM pool for this specific site and update it accordingly:

[[email protected] ~]# cp /etc/php-fpm.d/www.conf /etc/php-fpm.d/example.com.conf
[[email protected] ~]# vim /etc/php-fpm.d/example.com.conf
; Start a new pool named 'www'.
; the variable $pool can we used in any directive and will be replaced by the
; pool name ('www' here)
[example.com]
...
; listen = 127.0.0.1:9000
listen = /var/run/php-fpm/example.com.sock
...
listen.owner = apache
listen.group = apache
listen.mode = 0660
user = apache
group = apache

Then update the site’s Apache vhost to point to a new PHP-FPM pool in both the 80 and 443 stanzas. Be sure to update the socket accordingly for your site in the 2 sections below! (ie: unix:/var/run/php-fpm/example.com.sock)

[[email protected] ~]# vim /etc/httpd/vhost.d/example.com.conf
<VirtualHost *:80>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

	# Send PHP requests to php-fpm
	<Proxy "unix:/var/run/php-fpm/example.com.sock|fcgi://php-fpm">
		# we must declare a parameter in here (doesn't matter which) or it'll not register the proxy ahead of time
		# Note: If you configure php-fpm to use the "ondemand" process manager, then use "ProxySet disablereuse=on"
		ProxySet disablereuse=off
	</Proxy>

	# Redirect to the proxy
	<FilesMatch \.php$>
		SetHandler proxy:fcgi://php-fpm
	</FilesMatch>
...
<VirtualHost *:443>
        ServerName example.com
        ServerAlias www.example.com
        DocumentRoot /var/www/vhosts/example.com

	# Send PHP requests to php-fpm
	<Proxy "unix:/var/run/php-fpm/example.com.sock|fcgi://php-fpm">
		# we must declare a parameter in here (doesn't matter which) or it'll not register the proxy ahead of time
		# Note: If you configure php-fpm to use the "ondemand" process manager, then use "ProxySet disablereuse=on"
		ProxySet disablereuse=off
	</Proxy>

	# Redirect to the proxy
	<FilesMatch \.php$>
		SetHandler proxy:fcgi://php-fpm
	</FilesMatch>
...

Then restart the services:

[[email protected] ~]# service php-fpm restart
[[email protected] ~]# service httpd restart

Finally, test a site to ensure PHP is working and is using PHP-FPM by creating the file below, then visiting the page at example.com/info.php:

[[email protected] ~]# vim /var/www/vhosts/example.com/info.php
<?php phpinfo(); ?>

And your done!

IO Scheduler tuning

What is an I/O scheduler? The I/O scheduler is a kernel level tunable whose purpose is to optimize disk access requests. Traditionally this is critical for spinning disks as I/O requests can be grouped together to avoid “seeking”.

Different I/O schedulers have their pro’s and con’s, so choosing which one to use depends on the type of environment and workload. There is no one right I/O scheduler to use, it all simply ‘depends’. Benchmarking your application before and after the I/O scheduler change is usually your best indicator. The good news is, the I/O scheduler can be changed at run time and can be configured to persist after reboots.

The three common I/O schedulers are:
– noop
– deadline
– cfq

noop

The noop I/O scheduler is optimized for systems that don’t need an I/O scheduler such as VMware, AWS EC2, Google Cloud, Rackspace public cloud, etc. Since the hypervisor already controls the I/O scheduling, it doesn’t make sense for the VM to waste CPU cycles on it. The noop I/O scheduler simply works as a FIFO (First In First Out) queue.

You can update the I/O scheduler to noop by:

## CentOS 6

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'noop' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] anticipatory deadline cfq

# Change at boot time by appending 'elevator=noop' to end of kernel line:
[[email protected] ~]# vim /boot/grub/grub.conf
kernel /vmlinuz-2.6.9-67.EL ro root=/dev/vg0/lv0 elevator=noop


## CentOS 7

# Change at run time
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'noop' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] anticipatory deadline cfq

# Change at boot time by appending 'elevator=noop' end of the following line, then rebuild the grub config:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=rhel00/root rd.lvm.lv=rhel00/swap elevator=noop"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg


## Ubuntu 14.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo noop > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] deadline cfq

# Change at boot time by appending 'elevator=noop' end of the following line, then rebuild the grub config:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=noop"
...
[[email protected] ~]# grub-mkconfig -o /boot/grub/grub.cfg


## Ubuntu 16.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo noop > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] deadline cfq

# Change at boot time by appending 'elevator=noop' end of the following line, then rebuild the grub config:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=noop"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg

deadline

The deadline I/O scheduler is optimized by default for read heavy workloads like MySQL. It attempts to optimize I/O request by putting it in a read queue or write queue and assigning a timestamp to the request. For requests in the read queue, they have 500ms (by default) to execute before they are given the highest priority to run. Requests entering the write queue have 5000ms to execute before they are given the highest priority to run.

This deadline assigned to each I/O request is what makes deadline I/O scheduler optimal for read heavy workloads like MySQL.

You can update the I/O scheduler to deadline by:

## CentOS 6

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'deadline' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq

# Change at boot time by appending 'elevator=deadline' to end of kernel line apply the changes to grub:
[[email protected] ~]# vim /boot/grub/grub.conf
kernel /vmlinuz-2.6.9-67.EL ro root=/dev/vg0/lv0 elevator=deadline


## CentOS 7

# Change at run time
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'deadline' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq

# Change at boot time by appending 'elevator=deadline' end of the following line and apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=rhel00/root rd.lvm.lv=rhel00/swap elevator=deadline"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg


# Ubuntu 14.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
[[email protected] ~]# echo deadline > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq

# Change at boot time by appending 'elevator=deadline' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=deadline"
...
[[email protected] ~]# grub-mkconfig -o /boot/grub/grub.cfg


# Ubuntu 16.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
[[email protected] ~]# echo deadline > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq

# Change at boot time by appending 'elevator=deadline' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=deadline"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg

cfg

The cfg I/O scheduler is probably best geared towards things running GUIs (like a desktop) where each process needs a fast response. The goal of the cfq I/O scheduler (Complete Fairness Queueing) is to give a fair allocation of disk I/O bandwidth for all the processes which requests an I/O operation.

You can update the I/O scheduler to cfq by:

## CentOS 6

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq 
[[email protected] ~]# echo 'cfq' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]

# Change at boot time by appending 'elevator=cfq' to end of kernel line apply the changes to grub:
[[email protected] ~]# vim /boot/grub/grub.conf
kernel /vmlinuz-2.6.9-67.EL ro root=/dev/vg0/lv0 elevator=cfq


## CentOS 7

# Change at run time
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq 
[[email protected] ~]# echo 'cfg' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]

# Change at boot time by appending 'elevator=cfq' end of the following line and apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=rhel00/root rd.lvm.lv=rhel00/swap elevator=cfq"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg


# Ubuntu 14.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo cfq > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

# Change at boot time by appending 'elevator=cfq' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=cfq"
...
[[email protected] ~]# grub-mkconfig -o /boot/grub/grub.cfg


# Ubuntu 16.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo cfq > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

# Change at boot time by appending 'elevator=cfq' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=cfq"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg

As with any performance tuning recommendations, there is never a one size fits all solution! Always benchmark your application to establish a baseline before you make the change. After the performance changes have been made, run the same benchmark and compare the results to ensure that they had the desired outcomes.

Disabling Transparent Huge Pages in Linux

Transparent Huge Pages (THP) is a Linux memory management system that reduces the overhead of Translation Lookaside Buffer (TLB) lookups on machines with large amounts of memory by using larger memory pages.

However, database workloads often perform poorly with THP, because they tend to have sparse rather than contiguous memory access patterns. The overall recommendation for MySQL, MongoDB, Oracle, etc is to disable THP on Linux machines to ensure best performance.

You can check to see if THP is enabled or not by running:

[[email protected] ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
[[email protected] ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never

If the result shows [never], then THP is disabled. However if the result shows [always], then THP is enabled.

You can disable THP at runtime on CentOS 6/7 and Ubuntu 14.04/16.04 by running:

[[email protected] ~]# echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
[[email protected] ~]# echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag

However once the system reboots, it will go back to its default value again. To make the setting persistent on CentOS 7 and Ubuntu 16.04, you can disable THP on system startup by making a systemd unit file:

# CentOS 7 / Ubuntu 16.04:
[[email protected] ~]# vim /etc/systemd/system/disable-thp.service
[Unit]
Description=Disable Transparent Huge Pages (THP)

[Service]
Type=simple
ExecStart=/bin/sh -c "echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled && echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag"

[Install]
WantedBy=multi-user.target

[[email protected] ~]# systemctl daemon-reload
[[email protected] ~]# systemctl start disable-thp
[[email protected] ~]# systemctl enable disable-thp

On CentOS 6 and Ubuntu 14.04, you can disable THP on system startup by adding the following to /etc/rc.local. If this is on Ubuntu 14.04, make sure its added before the ‘exit 0’:

# CentOS 6 / Ubuntu 14.04
[[email protected] ~]# vim /etc/rc.local
...
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
...

MySQL 5.1 to MySQL 5.7 upgrade on CentOS

Upgrading MySQL is pretty straight forward, however there are some catches. Per MySQL’s official documentation, you must upgrade from 5.1 to 5.5, then upgrade from 5.5 to 5.6, and finally again from 5.6 to 5.7. You cannot upgrade from 5.1 directly to 5.7.

This guide will outline how to upgrade MySQL 5.1 to MySQL 5.7 in sequence and will assume you are using the IUS repositories for MySQL. If the IUS repositories are not already setup, you can install them by:

# CentOS 6
[[email protected] ~]# yum install epel-release
[[email protected] ~]# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/6/x86_64/ius-release-1.0-15.ius.centos6.noarch.rpm
# CentOS 7
[[email protected] ~]# yum install epel-release
[[email protected] ~]# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/7/x86_64/ius-release-1.0-15.ius.centos7.noarch.rpm

Upgrade from MySQL 5.1 to MySQL 5.5

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:

[[email protected] ~]# mysql -S mysqldump
[[email protected] ~]# mkdir -p /root/mysqlupgrade/mysql51
[[email protected] ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql51/mysql-5.1.databases
[[email protected] ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql51/mysql-5.1.grants
[[email protected] ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig
[[email protected] ~]# yum install xz
[[email protected] ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz

Now stop MySQL and upgrade it to MySQL 5.5:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql mysql-server mysql-libs
install mysql55 mysql55-server mysql55-libs mysqlclient16
ts solve
ts run
exit

Generate a version of the my.cnf that is valid for MySQL 5.5. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

[[email protected] ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
[[email protected] ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig --target 5.5 > /root/mysqlupgrade/mysql-5.5.cnf
[[email protected] ~]# mv /etc/my.cnf /etc/mysql-5.5.cnf.orig
[[email protected] ~]# mv -f /root/mysqlupgrade/mysql-5.5.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.5 and all the databases loaded:

[[email protected] ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql -sse "select @@version"
[[email protected] ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql51/mysql-5.5.databases
[[email protected] ~]# diff -U0 /root/mysqlupgrade/mysql51/mysql-5.1.databases /root/mysqlupgrade/mysql51/mysql-5.5.databases

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

[[email protected] ~]# mysql_upgrade
[[email protected] ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
[[email protected] ~]# service mysqld restart

Finally, confirm MySQL is running version 5.5:

[[email protected] ~]# mysqladmin version

Upgrade from MySQL 5.5 to MySQL 5.6

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:

[[email protected] ~]# mysql -S mysqldump
[[email protected] ~]# mkdir -p /root/mysqlupgrade/mysql55
[[email protected] ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql55/mysql-5.5.databases
[[email protected] ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql55/mysql-5.5.grants
[[email protected] ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig
[[email protected] ~]# yum install xz
[[email protected] ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql55/mysql-5.5.dump.sql.xz

Now stop MySQL and upgrade it to MySQL 5.6:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql55 mysql55-server mysql55-libs
install mysql56u mysql56u-server mysql56u-libs mysqlclient16
ts solve
ts run
exit

Generate a version of the my.cnf that is valid for MySQL 5.6. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

[[email protected] ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
[[email protected] ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig --target 5.6 > /root/mysqlupgrade/mysql-5.6.cnf
[[email protected] ~]# mv -f /root/mysqlupgrade/mysql-5.6.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:

[[email protected] ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql -sse "select @@version"
[[email protected] ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql55/mysql-5.6.databases
[[email protected] ~]# diff -U0 /root/mysqlupgrade/mysql55/mysql-5.5.databases /root/mysqlupgrade/mysql55/mysql-5.6.databases

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

[[email protected] ~]# mysql_upgrade
[[email protected] ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
[[email protected] ~]# service mysqld restart

Finally, confirm MySQL is running version 5.6:

[[email protected] ~]# mysqladmin version

Upgrade from MySQL 5.6 to MySQL 5.7

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:

[[email protected] ~]# mysql -S mysqldump
[[email protected] ~]# mkdir -p /root/mysqlupgrade/mysql56
[[email protected] ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql56/mysql-5.6.databases
[[email protected] ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql56/mysql-5.6.grants
[[email protected] ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig
[[email protected] ~]# yum install xz
[[email protected] ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql56/mysql-5.6.dump.sql.xz

Now stop MySQL and upgrade to MySQL 5.7:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql56u mysql56u-server mysql56u-libs mysql56u-common
install mysql57u mysql57u-server mysql57u-libs mysqlclient16
ts solve
ts run
exit

Generate a version of the my.cnf that is valid for MySQL 5.7. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

[[email protected] ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
[[email protected] ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig --target 5.7 > /root/mysqlupgrade/mysql-5.7.cnf
[[email protected] ~]# mv -f /root/mysqlupgrade/mysql-5.7.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:

[[email protected] ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql -sse "select @@version"
[[email protected] ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql56/mysql-5.7.databases
[[email protected] ~]# diff -U0 /root/mysqlupgrade/mysql56/mysql-5.6.databases /root/mysqlupgrade/mysql56/mysql-5.7.databases

If MySQL fails to start, check the logs as it may be due to MySQL looking for a /var/lib/mysqltmp directory. You can verify and correct it by:

[[email protected] ~]# cat /var/log/mysqld.log |grep ERROR
[ERROR] InnoDB: Unable to create temporary file; errno: 2

[[email protected] ~]# cat /etc/my.cnf |grep tmpdir
tmpdir                          = /var/lib/mysqltmp

[[email protected] ~]# mkdir /var/lib/mysqltmp
[[email protected] ~]# chown mysql:mysql /var/lib/mysqltmp
[[email protected] ~]# service mysqld start

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

[[email protected] ~]# mysql_upgrade
[[email protected] ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
[[email protected] ~]# service mysqld restart

Finally, confirm MySQL is running version 5.7:

[[email protected] ~]# mysqladmin version

Rollback plan

What happens if you need to roll back? If you followed the instructions in this article to create the backups, restoration is simple. Just keep in mind that you also need to restore the databases themselves from the original backups. So anything that changed in the database since the upgrade will be lost. If this is not acceptable, do not use these rollback instructions!

It is going to be assumed that you are going to roll all the way back from MySQL 5.7 to MySQL 5.1. Simply adjust the instructions below accordingly if you are going to roll back to a different version.

Stop MySQL and rollback to MySQL 5.1 by:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql*
install mysql mysql-server mysql-libs mysqlclient16
ts solve
ts run
exit

Restore the original /etc/my.cnf by:

[[email protected] ~]# cp /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig /etc/my.cnf

Startup MySQL and restore the 5.1-formatted databases:

[[email protected] ~]# mv /var/lib/mysql /var/lib/mysql.bak
[[email protected] ~]# mkdir /var/lib/mysql
[[email protected] ~]# chown mysql:mysql /var/lib/mysql
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql_secure_installation
[[email protected] ~]# unxz -c /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz | mysql
[[email protected] ~]# service mysqld restart
[[email protected] ~]# mysqladmin version

Allowing multiple developers to read/write to website via SFTP or FTP

This article simply exists to serve as a visual reference when I’m explaining permissions to others. If you are looking to apply the concepts in this article on a live site, make sure you create a backup of the permissions and ownerships before proceeding as this article could break a pre-existing site!

This is one of those things where there is more than one way to go about it. The goal here is to allow multiple users the ability to work with the site via FTP/SFTP using basic permissions and groups.

First, create the shared group. In this case, as my domain is going to be example.com, I will call it exampleadmins:

[[email protected] ~]# groupadd exampleadmins

Now add the preexisting users to the group

[[email protected] ~]# usermod -aG exampleadmins user01
[[email protected] ~]# usermod -aG exampleadmins user02

Now change the group ownership recursively on the website directory:

[[email protected] ~]# chgrp -R exampleadmins /var/www/vhosts/example.com

Since we want users in the exampleadmins group to have write access, set the group write permissions on the website directory by:

[[email protected] ~]# chmod -R g+w /var/www/vhosts/example.com

To ensure that any new files or directory inherit the group ownership, use the SetGID bit on the directory recursively:

[[email protected] ~]# find /var/www/vhosts/example.com -type d -exec chmod g+s "{}" \;

To ensure that files or directories the user creates or uploads are group writable by default, you need to adjust the default umask for the FTP and SFTP server. For vsftpd which is generally the default FTP server, change the default umask from 022 to 002 by:

[[email protected] ~]# vim /etc/vsftpd.conf
...
local_umask = 002
...
[[email protected] ~]# service vsftpd restart

When using SFTP, update the sftp subsystem within /etc/ssh/sshd_config to set a umask of 0002 by:

[[email protected] ~]# vim /etc/ssh/sshd_config
...
Subsystem       sftp    /usr/libexec/openssh/sftp-server -u 0002
...
# Append to bottom of file:
Match Group exampleadmins
   ForceCommand internal-sftp -u 0002
[[email protected] ~]# service sshd restart

Now whenever you need to add additional users, simply create the user with a membership to exampleadmins

[[email protected] ~]# useradd -s /sbin/nologin -d /var/www/vhosts/example.com -G exampleadmins user03

And if the user already exists, simply run:

[[email protected] ~]# usermod -aG exampleadmins user03

Convert MyISAM tables to InnoDB in MySQL

Converting MyISAM tables over to InnoDB comes with several benefits such as row level locking, being able to commit and roll back transactions and using tractional logs for autorecovery.

While converting MyISAM over to InnoDB is usually straight forward, you must use caution:

- Always create a backup of the database before making any changes!
- Confirm you are running MySQL 5.6.4 or above as that was when full-text indexing was introduced to InnoDB.

You can check to see what tables are using MyISAM in your database by running the following:

[[email protected] ~]# mysql
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE  ENGINE = 'MyISAM' and TABLE_ROWS
+--------------+-----------------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME            | ENGINE | TABLE_ROWS |
+--------------+-----------------------+--------+------------+
| mysql        | db                    | MyISAM |          3 |
| mysql        | help_category         | MyISAM |         39 |
| mysql        | help_keyword          | MyISAM |        464 |
| mysql        | help_relation         | MyISAM |       1028 |
| mysql        | help_topic            | MyISAM |        508 |
| mysql        | proxies_priv          | MyISAM |          2 |
| mysql        | user                  | MyISAM |          5 |
| example01    | wp_commentmeta        | MyISAM |         15 |
| example01    | wp_comments           | MyISAM |         72 |
| example01    | wp_links              | MyISAM |         11 |
| example01    | wp_options            | MyISAM |        224 |
| example01    | wp_postmeta           | MyISAM |        260 |
| example01    | wp_posts              | MyISAM |        277 |
| example01    | wp_term_relationships | MyISAM |        178 |
| example01    | wp_term_taxonomy      | MyISAM |         62 |
| example01    | wp_terms              | MyISAM |         62 |
| example01    | wp_usermeta           | MyISAM |         22 |
| example01    | wp_users              | MyISAM |          1 |
+--------------+-----------------------+--------+------------+

Alternatively, if you wanted to see MyISAM tables that have more than 5,000 rows, run:

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE  ENGINE = 'myISAM' and TABLE_ROWS > 5000;
+--------------+---------------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME          | ENGINE | TABLE_ROWS |
+--------------+---------------------+--------+------------+
| example02    | employees           | MyISAM |      80257 |
| example02    | templates           | MyISAM |     155341 |
| example02    | zipcodes            | MyISAM |      33178 |
| example02    | providers           | MyISAM |      34833 |
| example02    | portal              | MyISAM |      25184 |
+--------------+---------------------+--------+------------+

Converting the tables is pretty simple, but keep in mind that this will lock the table while it is running. Make sure you backup your database BEFORE converting any tables! Things can go wrong at the worst times, so be sure to have a solid roll back plan before proceeding!

Convert the desired table from MyISAM to InnoDB by running:

[[email protected] ~]# mysql
mysql> use example02;
mysql> ALTER TABLE providers ENGINE=InnoDB;