Posted on
by

mysql-logo3 So, the other week, I migrated all my sites over to a new server. This was accomplished with minimum fuss, using lots of rsync magic and juggling of DNS ttls.

The part of the migration I imagined would be the most complicated, moving several tens of MySQL databases running on the old server to the new, turned out to be pretty straightforward, and essentially completed with one command. I thought others might find it handy to know how…

First, I brought down Apache on both servers, so I could be sure that nobody was going to try and write to the database. This may not really be required, since mysqldump can handle dumping live databases consistently, for MyISAM at least, but in never hurts to be paranoid.

Next, I needed to move all the databases, together with their access permissions and users (so the full MySQL schema) to the other server. One way to do that is to copy the whole /var/lib/mysql directory over. However, I had a lot of cruft in there (old bin-logs etc), plus there were a number of articles suggesting that the straight binary copy had a number of issues, especially for mixed storage engine environments. So, I opted for the mysqldump method.

Traditionally, this takes a lot of SCPing. Here’s how to do it with one command, using the magic of Unix pipes:

mysqldump -u root -pPASSWORD --all-databases | ssh USER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'

Boom. This ran surprisingly quickly for me, and you can of course just as easily use this method to transfer a single database.

Three gotchas:

  • If the link dies, you need to start again, so don’t do this over a flakey connection, and I suggest you run the command in a screen if the first server isn’t localhost.
  • You need to restart the database server on the target machine for the new users and privileges to come into effect.
  • On Debian, you may see an error along the lines of:

    Got error: 1045: Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES) when trying to connect

    Fix this by executing the command:

    GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'THEPASSWORD' WITH GRANT OPTION;

    Where THEPASSWORD is the password found in /etc/mysql/debian.cnf

One final note: this will only work if both servers are the same major version number. I was moving between two Debian 6.0 installs, so YMMV.

Happy Easter!

Posted on
by

This is just a quick note to readers of this website to give you advanced warning of some planned server maintenance.

Over the next few days I will be migrating all sites and services over to a new hosting facility. Unfortunately, the migration will necessitate short periods of downtime while I move things over and repoint DNS etc. We will do the utmost to minimise any disruption caused, but over the next week or so, you may notice that certain services that you use will become unavailable for short periods
of time.

If you have any questions please get in touch!

Posted on
by

Those of you who have done any amount of web application programming should all be familiar with the concept of a Session, but for everyone else, a Session is a way of preserving state between each browser page load in the inherently stateless web environment.

This is required for, among other things, implementing the concept of a logged in user!

In PHP, this is typically implemented by setting a session ID to identify a user’s browser and saving it as a client side cookie. This cookie is then presented to the server alongside every page request, and the server uses this ID to pull the current session information from the server side session store and populate the $_SESSION variable.

This session data must, obviously, be stored somewhere, and typically this is on the server, either in a regular file (default in PHP) or in a database. This is pretty secure, and works well in traditional single web server environments. However, a server side session store presents problems when operating in modern load balanced environments, like Amazon Web Services, where requests for a web page are handled by a pool of servers.

The reason is simple… in a load balanced environment, a request for the first page may be handled by one server, but the second page request may be handled by an entirely different server. Obviously, if the sessions are stored locally on one server, then that information will not be available to other servers in the pool.

There are a number of different solutions to this problem. One typical solution is to store sessions in a location which is available to all machines in the pool, either a shared file store or in a shared database. Another technique commonly used is to make the load balancer “session aware”, and configure it to address all requests from a given client to the same physical machine for the duration of the session, but this may limit how well the load balancer can perform.

What about storing sessions on the client?

Perhaps a better way to handle this problem would be to store session data on the client browser itself? That way it could send the session data along with any request, and it wouldn’t matter which server in the pool handled the request.

Obviously, this presents some issues, first of which being security. If session data is stored on the client, then it could conceivably be edited by the user. If the session contains a user ID, and this isn’t protected, then this could let a user pretend to be another. Any data stored on the client browser must therefore be protected, typically this is done by the use of strong encryption.

So, to begin with we need to replace the default PHP session handler with one of our own, which will handle the encryption and cookie management. The full code can be found on GitHub, via the link below, but the main bits of the code to pay attention to are saving the session:

// Encrypt session
$encrypted_data = base64_encode(mcrypt_encrypt(MCRYPT_BLOWFISH, self::$key, $session_data, MCRYPT_MODE_CBC, self::$iv));

// Save in cookie using cookie defaults
setcookie($session_id, $encrypted_data);

Followed by reading the session back in:

return mcrypt_decrypt(MCRYPT_BLOWFISH, self::$key, base64_decode($_COOKIE[$session_id]), MCRYPT_MODE_CBC, self::$iv );

If things are working correctly, you should see something similar to this:

Cookie Sessions

Limitations

There are some limitations to this technique that you should be aware of before trying to use it.

Firstly, the total size of all cookies varies from browser to browser, but is limited to around 4K. Remember, this is the total size of all cookies, not just the encrypted session, so your application should only store a bare minimum in the session.

Secondly, and this relates somewhat to the previous point, since the session is sent in the HTTP header on every request, you could eat up bandwidth if you start storing lots in the session. Another reason to store the absolute minimum!

Thirdly, session saving is done using a cookie, so you must be done with sessions, and call session_write_close(); explicitly, before you send any non-header output to the browser.

Have fun!

» Visit the project on Github…