Replication In MySQL

In some forums, i often saw a question asking about how to synchronize a database to some database servers (it could be local servers or even public servers via Internet). Using a dumped SQL is not very efficient if you want a real time access. Using mysqlhotcopy needs some resources, so it would rip off the performance of the system. The solution is by using Replication. Here's some information from MySQL's manual:

Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves). Replication is asynchronous - your replication slaves do not need to be connected permanently to receive updates from the master, which means that updates can occur over long-distance connections and even temporary solutions such as a dial-up service.

Today, at UGM, i got a material about replication (actually it was last week's material, but many of us had failed, so we had to restart it again. I was absent last week since i had to go to ILC 2007, so this is my first class actually). As always, Manual is the best place to start. I managed to set up one computer as a master and another computer as a slave in less than 10 minutes, so i guess it's pretty straightforward. By using this scheme, the slave would only have read-only access, meaning it only retrieve the updates from the server. Changing the slave wouldn't affect the server. In order to get a round robin model, we should use the daisy-chain model, which is like a P2P technology, where a computer became a server and also became a slave. To do this, it only requires some modifications and it won't take more than another ten minutes to configure it.

The steps needed to configure a replication can be described as below:
- Configure the master (set server-id and enable binary logging)
- Configure the slave (only set a different server-id)
- Obtain the master status information (this will be used for setting up the slave)
- Setting the master configuration in slave (you need the information from the master status information)
- Start the slave and you're done

After executing the last steps, any changes to the master will be replicated by the slave and you will have a clone of the master in slave. Of course, it's the most basic configuration and you will need to adjust it for more advanced needs.

Replication is pretty useful when you have several machines acting as a database servers and all of them are connected to the networks. You can use several database to ensure high availability on the data needed by the application.