Store PHP sessions in Memcached and MySQL
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 :
- 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).