Master-Slave Database. How to activate a secondary database whenever the primary database is down?

Question

I need some idea on how to accomplish a master-slave database in MySQL. It's like, there's a primary server which the system connects then when it is down, it will use its local server. Here's the scenario:

I'm doing my thesis as a requirement for my course, I'm currently developing a web-based POS which will be installed for two branches, the system will be connected to the main office as the server is located there. What my client wants is to have a secondary database which will be used whenever the internet connection is down, that secondary database is installed in the computer in the branch. Well ofcourse, the primary database is in the main office, and it needs at internet connection for the POS and the primary database to work together.

Then when the internet connection works again, the primary database will be updated from the latest records in secondary database.

Answer 1

In addition to setting up replication, which will copy the data from your master to your slave, you need a failover technology to make the system use the slave when the master is down.

You can do this manually, but you can also use something like MMM (http://mysql-mmm.org/). It is open-source, so you can look at the code to see the logic of what it is doing. Basically, it is moving aliases (like yourdb-master and yourdb-slave) between two physical servers, so the system always connects to a yourdb-master, wherever that is physically.

Answer 2

See http://www.webdeveloperjuice.com/2010/02/15/3-jump-steps-to-configure-master-slave-replication-in-mysql/

The basic is, setup some line of codes to create a replication server.

See here: http://dev.mysql.com/doc/refman/5.0/en/replication.html

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.