You are here: Home / Debian GNU/Linux / Servers / PHP / Store PHP sessions in Memcached and MySQL

Store PHP sessions in Memcached and MySQL

by Pierre-Yves Landuré last modified Nov 11, 2017 09:37

Using Memcached and MySQL to manage PHP sessions is the key to create high-availability hosting. This setup allow to share session data between several PHP servers hosted behind a load-balancer. In a simpler environment, it allow to prevent storing sessions data on file system. This howto describe the installation of a custom PHP sessions manager using a hybrid Memcached / MySQL storage.

This howto is tested on:

  • Debian 6.0 Squeeze
  • Debian 7.0 Wheezy

Prerequisites

This howto needs :

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