poscribes

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!

Over the past several weeks/months I have been collecting together the components necessary to update and enhance the hardware supporting this and several other websites that I maintain from my home.

The original configuration in a self-hosted model was that of a single PC. This thankfully was replaced by better hardware a couple of years ago that could be best described as powerful desktops or low-grade, non-redundant, server equipment.

Well today I am pleased to announce that through a little begging, a little borrowing and a little scavenging I have established not only a redundant configuration in terms of multiple web servers but also a load-balanced and highly available configuration.

Here's a quick overview of the setup as it stands now.

[Click Image To Enlarge] Home Network Diagram

As you can see there are now two web servers and two database servers. A little overkill for the current setup maybe; but enough room to grow for the foreseeable future too. Like I said earlier this is not the only website that I am hosting on this system but it is the one that currently draws the most attention and the most traffic.

Here are few pictures of the equipment in operation. Some of it is not the newest server technology but the systems run strong, offer RAID capability and in the case of the two newer servers redundant power also.

[Click Image To Enlarge] The used rack is a little more aesthetically pleasing than the previous open wire rack	[Click Image To Enlarge] Wiring mostly in place, just a little tidy up to do.
[Click Image To Enlarge] Intake fan at bottom aids cooling as does spacing somewhat	[Click Image To Enlarge] Separate switches for separate internet connections
[Click Image To Enlarge] Extractor fan to remove excess heat. First fan had worn bearing hence the clipped wires	[Click Image To Enlarge] Foam aids sound suppression but also heat build up

My next step will be to establish some kind of round-robin DNS setup that permits fail over, albeit slightly delayed between the two types of internet connectivity that I have.

Over the next week or so I hope to provide examples of how I have established the load-balancing and replication using the Microsoft Windows Operating System.

Yes, that's right I am using Windoze!

Why not Linux I hear you cry? It's the perfect LAMP configuration! This is true, but I wanted to try something a little, well unconventional by todays standards.

That said, in all likelihood upgrades or additional equipment will involve Linux, although I'm hearing good things about Windows 2008, so I'm gonna have to try it somewhere, maybe here!

Over the past few evenings I have successfully upgraded the site to the latest release of Drupal, now at 6.2!

Having hit a few speed bumps with incompatible modules it's taken a little while to get back to the level of functionality there before the upgrade. That said, it was a necessary upgrade for many reasons most obviously to me being that of the hundreds of fake user accounts that have been springing up on a daily basis. At points during any given day there were tens of new users accounts being registered every hour. To combat this problem I have introduced the reCaptcha module that makes use of the reCaptcha web service for 'human' recognition versus the annoying scripts that have been invading the site. At the same time, and I apologize to all this affects but I have removed all user accounts that weren't personally known to me or accessed within the last 12-months. Please re-join whenever you get the chance and again sorry for the inconvenience.

As part of this upgrade you will also notice that I have included a rating system to allow visitors to rate the posts and comments that they read. This will hopefully add to the user experience and encourage participation. Along those same lines I have decided to open up the comments to allow 'anonymous' input while utilizing the reCaptcha service to 'fingers-crossed' dispel any and all scripted abuse that has been witnessed in the past. I'll let this ride for a time and if I see any abuse occurring regroup and decided on what to do then. All comments, just like posts will continue to be moderated by myself and require approval prior to public display.

Well that's it for now! It's been a while and a lot has happened but without trying to dissect my life, my work and everything in the middle I am going to push forward with a new revitalized blogging schedule. On the list of topics that I have running around in my head at the moment are hardware upgrades to the site infrastructure (remember this - Old Hardware Post) and the reconfiguration and introduction of load-balancing and replication. These and more will be coming very soon.

Dave