Store PHP sessions in Memcached and MySQL

This howto is tested on:

• Debian 6.0 Squeeze
• Debian 7.0 Wheezy

Prerequisites

This howto needs :

• a working PHP setup, as described by Install PHP-FPM on Debian.
• a Memcached server, as described by Install Memcached on Debian.

• a MySQL server, as described by Install and setup MySQL on Debian.
• mysql-tools, a script easing MySQL administration on local host (and on MySQL server host, if different).

Parameters

Provide the domain name of the local host (used to name the database):

DOMAIN="$(command hostname --fqdn)"

Provide the MySQL server host :

MYSQL_HOST="localhost"

If the MySQL server is not local, the mysql-tools script will try to connect with MySQL client, or in case of connection failure, through SSH.

Provide the Memcached server host :

MEMCACHED_HOST="localhost"

Installation

Préparation de l'environnement

Install the needed softwares.

command apt-get install php5-mysql php5-memcached apg

Compute random table and field prefixes in order to prevent SQL injections based attacks :

TABLE_PREFIX="$(command apg -q -a 0 -n 1 -M NCL)"
FIELD_PREFIX="$(command apg -q -a 0 -n 1 -M NCL)"

Database creation

Create the database with mysql-tools :

MYSQL_PARAMS="$(command mysql-tools --server="${MYSQL_HOST}" --with-ssh \
            --auto-hosts --db-prefix="php-session" --create "${DOMAIN}")"

Parse the database connection parameters :

MYSQL_DB="$(echo "${MYSQL_PARAMS}" | command grep -e "^MYSQL_DB" \
    | cut --delimiter="=" --fields="2-")"
MYSQL_USER="$(echo "${MYSQL_PARAMS}" | command grep -e "^MYSQL_USER" \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PASSWORD="$(echo "${MYSQL_PARAMS}" | command grep -e "^MYSQL_PASSWORD" \
    | cut --delimiter="=" --fields="2-")"
echo "${MYSQL_PARAMS}"

Create the table designed to store sessions :

command wget 'https://raw.github.com/biapy/howto.biapy.com/master/php5/mysql-memcached-sessionhandler/memcached_mysql_sessionhandler.sql' \
    --quiet --no-check-certificate --output-document=- \
  | command sed \
       -e "s/php_/${TABLE_PREFIX}php_/" \
       -e "s/session_/${FIELD_PREFIX}session_/" \
  | command mysql --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" \
    --host="${MYSQL_HOST}" "${MYSQL_DB}"

PHP configuration

Detect PHP extension configuration path:

MODS_CONF_PATH='/etc/php5/conf.d'
test -d '/etc/php5/mods-available' \
  && MODS_CONF_PATH='/etc/php5/mods-available'

Create the code folder:

command mkdir --parents '/etc/php5/session-management'

Create the session management configuration file :

echo "<?php
/**
 * Memcached + MySQL session storage for ${DOMAIN}.
 *
 * MySQL connection configuration file
 */
abstract class Memcached_MySQL_SessionHandler_Config
{
  const MYSQL_HOST = '${MYSQL_HOST}';
  const MYSQL_DB = '${MYSQL_DB}';
  const MYSQL_USER = '${MYSQL_USER}';
  const MYSQL_PASSWORD = '${MYSQL_PASSWORD}';

  const TABLE_PREFIX = '${TABLE_PREFIX}';
  const FIELD_PREFIX = '${FIELD_PREFIX}';

  const MEMCACHED_ID = '${TABLE_PREFIX}';
  // Memcached servers list.
  // To add multiple Memcached servers use:
  // const MEMCACHED_SERVERS='xx.xx.xx.ip:port:weight,xx.xx.xx.ip:port:weight,...'
  const MEMCACHED_SERVERS = '${MEMCACHED_HOST}:11211';
}" \
  > '/etc/php5/session-management/memcached_mysql_sessionhandler_config.class.php'

Download the session management code :

command wget 'https://raw.github.com/biapy/howto.biapy.com/master/php5/mysql-memcached-sessionhandler/memcached_mysql_sessionhandler.class.php' \
    --quiet --no-check-certificate --output-document='/etc/php5/session-management/memcached_mysql_sessionhandler.class.php'

Create the prepend.php file if it does not exists:

if [ ! -e '/etc/php5/prepend.php' ]; then
command echo '<?php
// This file is prepended at each PHP script.' \
  > '/etc/php5/prepend.php'
fi

Add the MySQL and Memcached session management loading to prepend.php (enabling the custom session management for all hosted sites on the server):

echo "require('/etc/php5/session-management/memcached_mysql_sessionhandler.class.php');" >> '/etc/php5/prepend.php'

Setup PHP to load the prepend.php file at the start of each served request:

command echo "; Load PHP code at startup.
auto_prepend_file=/etc/php5/prepend.php" \
 >  "${MODS_CONF_PATH}/prepend-code.ini"
test -n "$(command -v php5enmod)" && command php5enmod 'prepend-code/50'

If PHPMyAdmin is installed, add the code folder to the open_basedir option:

command test -e '/etc/phpmyadmin/apache.conf' \
  && command sed -i \
        -e 's|open_basedir.*|&:/etc/php5/session-management|' \
      '/etc/phpmyadmin/apache.conf'

Reload PHP configuration :

test -x /etc/init.d/php5-fpm && /etc/init.d/php5-fpm restart
test -x /etc/init.d/apache2 && /etc/init.d/apache2 force-reload
test -x /etc/init.d/lighttpd && /etc/init.d/lighttpd force-reload
test -x /etc/init.d/nginx && /etc/init.d/nginx force-reload

Thanks

• Thanks to Keboola (en) for PHP Sessions with Memcached and a Database (Sessions in the Cloud Done Right) (en).

• Thanks to Adventures in PHP / DHTML / CSS and MySQL (en) for Memcache & MySQL PHP Session Handler (en).

• Thanks to GitHub (en) for hosting this howto source code: Handle PHP sessions with Memcached and MySQL (en).