Setup MySQL replication
There are several flavors of MySQL, this guide describes the standard/free MySQL version.
Quote from the MySQL manual:
Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster
This means, that with the standard MySQL version, you can have a master which replicates to one or more slaves. Changes made on the slave is NOT replicated back to the master. If you want such features, you must use the MySQL cluster.
Setting up one-way replication
The first thing to do is to take two virtual machines and install MySQL on both of them. MySQL can be downloaded from http://dev.mysql.com/downloads/ - download and install the Community Server. Select a complete installation and include the MySQL bin dir in the PATH environment variable.
When done, you will see something like this
Image 1: Two virtual machines with a fresh install of MySQL community server 5.5. Click to enlarge
NOTE: Don't start the command line client, unless you know what you are doing. It will give you superuser access (if you know the password) to the MySQL system database (named mysql), and you should know what you are doing when typing commands here. One good command is "use test;" which changes the current database to the test database. However, you will still be superuser, so don't.
To set up replication follow the steps in the manual at this location: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html. The following will describe the easiest possible setup - a new master and slave is created in the virtual machines. There are some decisions to take:
- What to replicate
- Which user to use for the replication
Both must exist before replication can be setup. A fresh MySQL installation contains a test database, which can be used to play with.
Setting up the network
MySQL needs a host name if you want to connect to a remote server. An IP address is not enough, so you must make sure, that a hostname can be looked up. There are two ways of doing this, the difficult is to change a name server configuration, the easy is to modify the hosts file on your system, assuming that the master and slave will have fixed IP addresses.
The hosts file on both machines will contain entries like this (your addresses and names may be different). Make sure, that the IP addresses are correct.
10.1.1.101 mysql_master
10.1.1.102 mysql_slave1
Windows hides the hosts files in c:\windows\system32\drivers\etc. You should see something like this when done.
Image 2: The hosts file. Click to enlarge
Note: You can skip the hosts file update with the command-line option --skip-name-resolve each time you start the mysql command line client, but you don't want to do that.
Setting up the master
The green virtual machine is going to be the master, while the blue virtual machine is going to be the slave. Master configuration will be done first.
The very first thing to do is to setup a user. Do this with the "CREATE USER" command in the command line client.