So the Manchester server is working nicely 2 weeks on, the reporting system is beginning to be useful and its time to develop the infrastructure a little more. The ideal scenario with this new off site system would be that if it were to fail for whatever reason we could bring on another to replace it very quickly. In this way if we begin to move the pubs everyday applications over we know that we could always turn on an up-to-date backup within a few minutes thereby increasing the peace as Chuck D might say.
The best approach for this in my opinion is not to go all out and create an automatic fail over system with database clustering etc etc as its just not that much of a nightmare if the systems are unavailable for 5 minutes. Instead I am going to use replication which creates a live backup of the data but sends changes over periodically rather than trying to keep total synchronisation. MySQL has a very nice replication system which operates in a master slave relationship - Manchester being the Master and head office being the slave.
There are lots of benefits to this approach but ironically backup is not one of them. Having a database to backup from on the local network is obviously a benefit but one of the things with replication is that if you dismember your master it will automatically dismember your slave right on time :o)
So you still have to backup in the traditional sense but we can do it here instead of in Manchester and if we need to take one of the databases down for maintenance we have a way we can do this in a controlled manner rather than getting up at 2AM and hoping no one wants to use the systems!
I will not go into a 'How To' about replication on MySQL because I just used someone else's blog to accomplish it so here is the link. Also having set it up I now have to go home and see if my changes filter through by Monday morning, speaking of which did anyone else see the BBC thing with the tiny camera last night? Eugh!
Scheduled for next week - I really must get round to playing with Net Beans, found it last week and it really looks like somebody finally made a nice use able web app development tool for Java which must be worth a couple of hours.