5 Steps to MySQL Replication on Windows

In establishing a high-availability web application you need to consider multiple levels of availability and redundancy.

In this first installment discussing MySQL replication I am referring to omni-directional replication. In my follow-up on the subject, next week, we will be covering bi-directional replication or creating what is otherwise known as multi-master replication, and yes I am doing this on Microsoft Windows.

As previously discussed in my post on the hardware upgrades for the Poscribes website, I am introducing multiple levels of redundancy to ensure availability not only with load-balanced web servers but also replicated databases and ultimately fail-over internet connectivity (still working on that one!). Why Windows? Well, doing it with Linux is the norm these days and I just wanted to see how easy it would be.

In this situation I am using MySQL 5.0.51b-community-nt to replicate between my primary or master database running on a single 2.8Ghz Intel Xeon CPU with 1.5Gb RAM (aka DB01) to my secondary or slave database running on a single 800Mhz Intel Pentium III CPU with 896Mb RAM (aka DB02).

So what does replication really mean and what really happens? Well simply put, the queries that are executed against the master database (DB01) from the incoming application requests are recorded in a binary log (bin.log) along with a unique identifier, otherwise known as the log position identification number. Through an established and authentication connection, the slave database server (DB02) reads this log and applies the same queries to its database(s). Obviously there is a little more to it than just that but I don't want to get bogged down with the details when I want to concentrate on the how-to.

I think it's worth noting that the overhead on the servers is minimal. The process is really very simple and I'm going to include, for a default install, all of the code I used to make it happen.

If you are somewhat familiar with MySQL and I kind of hope you are to be performing something like replication in the first place you will be comfortable with using the mysqldump and mysql command syntax.

Now, before we begin with the first step there are a couple of things that I like to do just to make my life easier. In order to use the mysqldump and mysql commands from any directory as apposed to just the c:\program files\mysql\mysql server 5.0\bin directory we can COPY the executables mysql.exe and mysqldump.exe to the c:\windows\system32 directory. Doing this now gives us system wide access to these executables.

Here's a quick list of the steps that will follow:

1) Setup the replication user account and grant privileges
2) Configure the Master server
3) Configure the Slave server
4) Export/Import your current databases
5) Start replication

Okay, so now you have an idea of what we will be covering and you've made life a little easier for yourself, let's start at the beginning with step 1.

Step 1 - Setup the replication user account and grant privileges

On the master database server open a MySQL command line client window or a regular command line window and connect to MySQL using the standard string of:

c:\>mysql -u root -p

Once connected issue the following command to setup a valid user account for replication:

mysql>grant replication slave, replication client on *.* to 'replicator'@'192.168.1.35' identified by 'password';

(Obviously use your own user name, password combination and the IP address or system name that corresponds to your slave or secondary database server.)

[Click To Enlarge]

As you can see above to confirm the establishment of replication rights you can issue the following command:

mysql>show grants for 'replicator'@'192.168.1.35';

You could of course just use the existing 'root' account that has 'all' access, but it's generally not a good idea from a security perspective unless you are just testing.

Now issue the same command on the slave server using the IP address for the master server instead.

You have now established the necessary user account and assigned privileges to make replication possible.

Step 1 complete!

Step 2 - Configure the Master Server

There is very little in fact to do with regards the master server. Having already established a user account for replication in Step 1 we now need to make a couple of small additions to the my.ini file.

Edit the c:\program files\mysql\mysql server 5.0\my.ini (my.cnf is the equivalent on linux) and add the following lines to the bottom of the file.

(On the Master Server)
# On DB01
tmpdir="c:/temp/"
server-id=1
log-bin="c:/Program Files/MySQL/MySQL Server 5.0/data/bin.log"
log-error="c:/Program Files/MySQL/MySQL Server 5.0/data/error.log"
log-bin-index="c:/Program Files/MySQL/MySQL Server 5.0/data/log-bin.index"

The server-id shown above needs to be a unique identifier for this server. Any subsequent slaves that you establish need to have their own unique server-id. Unless you have changed the service account under which MySQL operates you shouldn't have to set any file permissions.

Then save the file.

Step 2 complete!

Step 3 - Configure the Slave Server

Okay, switch systems to the slave database server and make the following additions to its my.ini file just like you did for the master server.

(On the Slave Server)
# On DB02
server-id=2
master-host=db01.yourdomain.net (or IP address)
master-port=3306
master-user=replicator
master-password=password

log-bin="c:/Program Files/MySQL/MySQL Server 5.0/data/bin.log"
log-bin-index="c:/Program Files/MySQL/MySQL Server 5.0/data/log-bin.index"
log-error="c:/Program Files/MySQL/MySQL Server 5.0/data/error.log"
relay-log="c:/Program Files/MySQL/MySQL Server 5.0/data/relay.log"
relay-log-info-file="c:/Program Files/MySQL/MySQL Server 5.0/data/relay-log.info"
relay-log-index="c:/Program Files/MySQL/MySQL Server 5.0/data/relay-log.index"

Then save the file.

Step 3 complete!

Step 4 - Export/Import Your Current Databases

Assuming you have an existing database or multiples thereof on your primary database server, you will need to export the data from this server and import it into your slave system. If this is a completely new setup then move on to Step 5.

Okay, to export the data we will use the mysqldump command. To ensure success we need to tell the database to complete all existing queries by locking and flushing the tables and then perform a dump of the data. Here's how we do this, on the master database server:

mysql>mysqldump --user=root --password=password --extended-insert --master-data --lock-all-tables --all-databases > c:\full_backup.sql

This command provides the necessary information to completely rebuild your data on the slave database server along with the binary log position from the master database server to identify where to start replication from.

On the slave database server, copy over the .sql file containing the dump to a location such as c:\ to make it easy to find.

Before importing the dump file we need to first stop the slave service. To do so, open the MySQL command line client or regular command window. Log in using your root or similar credentials and issue the following command:

mysql>stop slave;

Now you are ready to import the data to your slave server.

mysql>mysql --user=root --password=password < c:\full_backup.sql

Once the import has finished running and providing there are no errors you are ready to move on to Step 5.

NOTE: During my install I had an inconsistent error that occurred. This is the error I received:

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE' filter\_formats: Can't create/write to file 'd:\temp\#sql_c58_0.MYI'

After some head scratching I established that this error was related the McAfee anti-virus blocking the writing to this temp directory, so to get around the issue I established an exception for that folder.

Step 4 complete!

Step 5 - Start Replication

On the master database server restart the MySQL service.
On the slave database server restart the MySQL service.
On the slave database server issue the following command from the MySQL command line client or regular command window:

mysql>start slave;

That's it! You should now be replicating.

To verify this is so issue the following command while still on the slave server:

Mysql>show slave status\G;

And you should see something like the following:

[Click To Enlarge]

You are now replicating! Add a record in whatever way you choose to the master database server and watch as it magically (alright no magic involved, but it is pretty cool!) appears in the slave database.

Next time around for MySQL we will be completing the replication setup by establishing replication back to the master in a multi-master circular or bidirectional configuration.

Enjoy!