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:


    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!

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!

Helping out a friend and colleague, as well as stretching my programming muscles with a language I don’t often get to play with these days, I’d like to introduce LoveNote Server.

LoveNote is a simple abstract message queue server which lets you pass an message payload to one of a pool of endpoints and receive a webhook callback with the result. You can specify that this message be delivered ASAP, but crucially you can also specify a date and time for the delivery.

How it works

It works by POSTing a bit of JSON to a webhook provided by the server that contains a delivery time, an array of servers to try, the payload and an optional callback URL.

When received by the server, the message is queued. When the delivery time is reached the list of servers is randomised and the payload POSTed in sequence until all servers fail, or delivery is achieved. If a callback is specified, a report is then POSTed back to the callback as a JSON blob.

Why this is useful

Simply, this provides a common message passing framework with a unified event driven API, simplifying your architecture somewhat. It is especially handy if you wanted a message to be delivered some time in the future, for example for a credit card renewal or email reminder, where beforehand you’d probably have to write a dedicated server process.

All you now have to do is listen to webhook pings.

What still needs to be done

This is an early version and was written to help out a friend with a specific need, and although I’ve gone on to use it in a couple of client projects, there are still a fair amount of enhancements that could be made.

Some obvious ones are:

  • Message IDs: Currently messages in the queue are anonymous. It’d be handy to have message IDs since this would allow more sophisticated process control of scheduled messages.
  • Cancel Control: Basic message control to cancel future queued messages.
  • Make message queues persistent: Currently the queue is held in memory, which is simple and fast, but far from ideal. We should periodically flush the queue to a persistent storage so that no messages are lost if a server goes down.

Get involved and send your pull requests to the usual place!

» Visit the project on Github…