Category Archives: Software

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

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…

Posted on
by

If you’re anything like me, you often find yourself needing to add create and modified timestamps to data stored in a MySQL database. Typically, I’d do this programmatically from within the application without giving it a second thought, wrapping up time(); in an SQL statement and firing it over.

Recently, while doing some development work for one of my clients, I was given the requirement to add Created and Modified timestamps to a whole bunch of existing data tables. These tables were referenced by hundreds of different MySQL queries, and to complicate matters further, we were in the middle of migrating the code over to a new database library.

I didn’t much fancy rewriting all of the SQL queries, potentially twice, just to add a couple of timestamps, so modifying code wasn’t much of an option. Thankfully, a couple of MySQL’s internal features came to the rescue.

Adding a ModifiedTime

Adding a modified timestamp to a table is the most straight forward. All your have to do is create the field of type TIMESTAMP, and by default, MySQL will automatically update the field when the row is modified.

There are a couple of things to be aware of:

  • While you can have multiple TIMESTAMP fields in a row, only one of these can be automatically updated with the current time on update.
  • If your UPDATE query contains a value for your ModifiedTime field, this value will be used.

So, to add your modified timestamp field to an existing table, all you need is:

ALTER TABLE my_table ADD ModifiedTime TIMESTAMP;

Adding a CreatedTime

Adding a CreateTime value is a little more involved.

On the latest versions of MySQL it is apparently possible to create a DateTime field with a default value of CURRENT_TIMESTAMP. This wasn’t an option for me as I was having to support a somewhat older version, besides, even on the newer versions of MySQL it is not possible to have more than one field using CURRENT_TIMESTAMP, which of course we are in order to get ModifiedTime working.

So, in order to get a created timestamp, firstly we must add a DATETIME field to the table.

ALTER TABLE my_table ADD CreatedTime datetime NOT NULL;

Note, that this must be created as NOT NULL in order for the next part to work (this is because setting NOT NULL forces an automatic all zeros default).

Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.

DELIMITER //
DROP TRIGGER IF EXISTS `my_table_insert_trigger`//
CREATE TRIGGER `my_table_insert_trigger`
BEFORE INSERT ON `my_table`
FOR EACH ROW
BEGIN
IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN
SET NEW.CreatedTime = NOW();
END IF;
END;//
DELIMITER ;

Now, when you insert a value into the table, this trigger will fire and, if you’ve not provided a CreatedTime field in your insert query, it will be set to the current time stamp.

Conclusion

Since all the queries in the application code specified the columns they were updating on insert, I was able to use this method to add created and modified time stamp fields to all the existing object tables in the database, without needing to modify any of the existing application code.

This simplified my life greatly, but also suggests to me that this method might be somewhat more efficient than the methods I’d previously used.