MySQL servers for an attendance system

Question

I'm building an attendance system. There are about 20 places where people will check in and check out using a MIFARE 1K card. It will use MySQL as the database. The system will display something like "#ID IN: 800AM" the first time the user checks in and "#ID OUT: 400PM" when the user checks out. For this to work, all the databases need to be synchronized with each other all the times.

For an example, if user A went to location #1 to check in but by the time the user wants to return home, the server at location #1 went down, the user needs to go to location #2 or the nearest server to check out. The server at location #2 should display '#ID OUT: 400PM" and not "#ID IN: 400PM" since the user is already checked in.

So, what should I use to ensure this idea will work? My main concern is with the network (other department manages it) which is very unpredictable. It just love to go down anytime it wants to.

Update

LOL, I didn't realize my question was not clear. I just noticed it when it was pointed out, sorry about that.

My real question is, how can I configure my MySQL to be synchronized with each other (20 servers)? MySQL cluster? (I tried reading about it, but I'm not sure if it's the right thing to do.)

My current setup (first phase):

• Local database for each server
• OS: Slackware
• A main server that keeps track of which staff is at which server
• A web based front end for the user to see their history (which connects to the server based on their records)

Main Pros

• No worries about network problems since it is a local database

Main Cons

• A user can only check in and out at the same server. Databases and servers are not connected with each other.
• Have to add the user to each server if the users want to check in at different locations. Which means, if the user wants to go to location A, the user must be checked out from location A first and then check in at location B. The server at location B didn't know that the user has checked in before at A.

By the way, I've already centralized my NTP to a local server. About the network, let's just say, I don't have the authority to make changes so that the network will be better. The network won't effect all 20 servers at once, usually, just a few of them for several times a week.

If there are anything else you would like me to answer, please just ask.

Answer 1

I think you are asking: how can I have multi-master databases with automatic failover. If this is not your question, then please clarify. If this is your question, then read on.

I have in fact written a web application for the very same thing, except it doesn't use Mifare 1K cards, it uses simple barcode cards (it's called LATRIX and is available from sourceforge, I hope this isn't too much plugging here).

The main issue here is that if your network connection to the actual computer running the frontend goes down, you are a bit stuffed. You could of course run a local database, and implement some sync mechanism back to a master database, but as long as the sync isn't working (due to absence of network), you have no chance of getting this right.

Therefore the very first thing to do is to make sure you have reliable network connectivity. Without that you are always going to struggle. The LATRIX does not use local databases or multi-master databases, it simply checks that the network connection to the database server is working. If not, the frontend terminal goes passive (i.e. users cannot check in our out), because I could not figure out a suitable solution for unavailable network connections.

Answer 2

It just love to go down anytime it wants to

When some smart people designed the internet, it was to provide a robust comunications medium which would continue to operate even in the event of all-out nuclear war. While this may pose some issues for snipe-bidding on ebay, a lot of the historic application protocols are still very effective.

You've provided lots of information about what your system is intended to do, but you've not asked any specific questions. Assuming that you're asking how you keep the clocks in sync, then the best way to do this is using network time protocol (NTP). Note that almost every application reads the OS clock to get the time (but this is not necessarily the same thing as the HARDWARE clock). While we may live in different time zones time is passing at the same rate for all of use. The link above gives you an introduction to how it works. There are lists of free servers available on the internet - or you can run your own (most GPS devices provide very accurate clock data).

You've not mentioned what OS you are running this all on.

BTW if you're still using Microsoft based operating systems then you can set the time from another SMB machine using 'net time \server /set' - but this is not nearly as sophisticated / accurate as NTP.