Web Stew

In this guide I will give you a step by step process for setting up Circular replication with MySQL. But first perhaps I should explain what it is.

Normal MySQL replication is simple enough.  You have a master database that sends all the SQL queries it executes to a slave database so that the servers will always maintain the same set of data . In a Circular or (Master to Master) replication,  when a database entry is inserted to or updated on the "Slave" database that record is also updated on the Master server.  So essentially you would have two database servers that would theoretically have exactly the same data in nearly real time. Hopefully that makes sense.

Now why would you want this kind of a database set up?  Well there are a few reasons, but let me give you the reason we are doing this.

In our scenario we have 2 servers running at 2 different data servers in 2 different cities. The first server "Water" is currently the Master server.  All our web traffic is run through it.  The second server "Fire" is a fail-over server.  It maintains a constant up to date copy of all the code, files and database that exists on the main server.

If Water were to fail for some reason we switch to Fire which is already synced and ready to go. As a side note we accomplish this through the use of Global DNS Load Balancing but that is information for another post.  Now at this point Fire has been running for a day while Water is now prepped, fixed and ready to go back online.  The problem is that now all the data on Fire needs to be synced back to Water.  You could do this from a export and import but that would be costly time wise.  If out databases where synced in a circular replication scenario, then as soon as Water is back up, it would simply  be update itself from the point where Fire took over. A few minutes (or hours) later and the databases are in sync again.

In a separate but similar scenario you could use this same method to keep both Fire and Water in a load balanced scenario where each one gets half the traffic as well. A circular replication scheme would keep all the data in sync.

So how do I do it?

In this example I'm going to make a few assumptions.

1. You don't currently have a secondary slave server setup yet.
2. That you are running version 5.5+ of MySQL. (These instructions may work with version 5.0 and up as well)
3. Your MySQL setup is using InnoDB as the default storage engine (It has to be for any replication to work) default-storage-engine = INNODB in the my.ini
4. Your MySQL is setup to be accessible through a network connection and that the servers can talk to each other through port 3306 (We currently do this through a VPN for extra security, however there is a way of using an SSL connection in MySQL as well)

Ok lets get our hands dirty.

To try and keep things clear I will refer to the existing server as "Water" and the new server you will set up as "Fire". Before we get Fire set up we need to update the my.ini on Water.

You will need to add these options to Water's my.ini on Windows or my.cnf on Linux:

server-id = 10
auto-increment-increment = 2
auto-increment-offset=1
log-bin = binarylog
log-error = replErrorLog

The server-id is the id of each replication server and needs to be unique for each.

The auto-increment takes more explaining.  In the case where both servers are writing data at the same time, the potential for the servers to create a different record in a table with the same ID exists.  These two config variables tell the server to auto increment table id's 2 at a time. One server will create odd id's and the other even that way the index values never collide. If they did, it would break replication.

Log-bin is the name of the binary log that the servers read from each other so they get the updates.  It can be called whatever you like as long as you reference it properly later. Log-error is the name of the replication error file you will have to read if something goes wrong.

We need to create a new user for MySQL that Fire will connect with that has replication privileges. To do this you will have to enter the next command into your MySQL console on Water.
CREATE USER 'repluser'@'%' IDENTIFIED BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';

Now that the replication user is ready to go on Water we need get some information ready for Fire. You need to make a backup of your data now so when Fire is up and running you have a copy of your data before replication was ready to start. It's very important that no data is written to the database while the backup is being run or you may not have a complete copy on Fire.  For more information on how to get an export of your data see http://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html .

Now that you have a copy of your data exported you need to enter this command into your MySQL console on Water.

reset master;
show master status;

After you run the show master status you will get a listing that contains two items that you need to write down.  The file name which will look something like "binarylog.000001" and the position which will be a number like 107. Mark these down under the title Water replication reference. Now that you have this information Water can go back to it's regular database duties and Fire will know where to start it's replication when it's ready for it.

Great, now we are finally ready to get Fire up and running. After you have installed the MySQL software on Fire you should import the databases from the backup you made. Now we need to configure Fire in a similar matter to Water. Make these changes to the my.ini on Fire.

server-id = 20 #different than Water's
auto-increment-increment = 2
auto-increment-offset=2 #also different than Water
log-bin = binarylog
log-error = replErrorLog

As always with my.ini changes make sure to restart MySQL to ensure they take affect. Now login to the MySQL console again and add the slave user as we did before.

CREATE USER 'repluser'@'%' IDENTIFIED BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';


Now get the master position information.

show master status;

Again write down the log file name and the position as Fire replication reference (It may very well be different than the Water one). On Fire we now need to set the slave replication information so again in the console type the following command changing master_log_file and master_log_pos to the information you had written down as "Water Replication Information".


change master to
master_host='waterIPaddress',
master_user='repluser',
master_password='replpass',
master_port=3306,
master_log_file='binarylog.000001',
master_log_pos=107;


Now Fire is configured to receive replication information from Water. Now we need to start the slave and hope it works.


slave start;
show slave status;


Show slave status should printout a fair bit of information, near then end of it you should have a status line saying something like "Waiting for Master to send event" this means it's done replicating for now and waiting for an update to be pushed. Congratulations, but were not done yet.

Now back on Water we need to start the replication from the Fire server so again start up your MySQL console and type this in changing master_log_file and master_log_pos to the information you had written down as "Fire Replication Information".


change master to
master_host='fireIPpaddress',
master_user='repluser',
master_password='replpass',
master_port=3306,
master_log_file='binarylog.000001',
master_log_pos=107;


Great and start the slave on Water now.

slave start show slave status;

Again the slave status show show "Waiting for Master to send Event" if it does great you did it. If it throws an error though, your going to have to look into that. Find the replErrorLog.err (or whatever you called it) file in the MySQL data directory and hopefully that will help you track down the problem. For a more complete reference on MySQL replication, see http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

Hope this post helped you out. Happy Queries.

My wife and I have wanted to work together on a project for a long time. It took as a while to figure out just what that project might be.  Well after many evenings of conversations over some great Cabernet's we came up with the idea of publishing some of her short stories she was working on as a hobby.

We thought the idea of publishing it in serial fashion in weekly installments was a rather "novel" idea. (Pun intended) A lot of the web based fiction or weblit seemed to be very scifi/fantasy/horror based.  My wife writes in closer to a chicklit or literature based style so we are trying to build a readership of people who may not be aware they can read fiction online. This will likely be the biggest problem for the website, attracting the kind of audience that would enjoy her fiction.  Therein lies my challenge, and so far it's one I've been enjoying.

So please if you enjoy fiction or simply want to peek at what where up to.  Please check out The Secret Loft.

I will be hopefully updating later on some of the marekting techniques, programming tricks another bits of information I glean from this project.

I'm not going to tell you what to buy, and I'm not going to tell you how to buy it.  But I am going to tell you where most people make some very bad decisions about computer purchases.

Recently I was in the Market for a laptop.  My son is now 5 years old and he tends to be on the computer a fair bit.  My wife who had little experience with computers before I met her really enjoys her time on the computer now.  It was starting to become a bit of a conflict between them.  So I decided a laptop would probably be the best solution to the problem. Read the rest of this entry »

Now when I say cool, I don't mean it in that Hip-Hop Rapper of the day way.  No.  I mean in the only way I know.  The iPhone toting Geek Chic way.

In the last year cbc.ca has launched a number of programs aimed squarely at the tech crowd.  This set in general tends to be a younger audience than the Vinyl Cafe listening set that the CBC is normally attributed to.

I'm talking about some very timely and poignant programs such as Spark and Search Engine, and the online radio station that is CBC Radio 3.  But beyond the new tech related programming they have also introduced Digg style article voting and a iPhone version of the site as well as podcasts on pretty much every program they have.

This all leads me to wonder, where did this come from.  Now I'm sure our conservative government didn't put them up to it. And I can't seem to find any information on just what spurred this on but I have to say I'm impressed.

I think the only way for Old Media to survive is to embrace the new media spectrum with open arms.  Radio 3 is a great example of putting new media to the test.  They have full on demand song streaming for most independent Canadian musicians and maintain a full schedule of original programing every week.   This is something that the more conservative laws of the US would most certainly want to shut down (and have).

I congratulate you CBC keep up the good work, I'll be sure to keep listening (online).

I admit it, sometimes I'm just plain lazy when I write my code.  I know better but sometimes that time you just tested something in code manages to stick around a lot longer than you expected.

It happens to you too, even if you don't want to admit it.

This happened to me the other day actually, I mistakenly named a variable in my Javascript code "status".  This as it turns out was a big mistake.  The code I wrote ran fine in Firefox but when I flipped over to Internet Explorer to test nothing was working.  After an embarrassingly long time trying to find the error there it was, and to fix it all I had to do was change the variable name.

So in my function I had something like:

status=objectRef.innerHTML;

The problem is for some unknown and seemingly unfixed reason Internet Explorer references the variable status over to the window.status object.  Window.status is the text that displays in the status area at the bottom of your browser.  So if you set the variable status to a value in side your function the status area in your browser changes as well.
Read the rest of this entry »