All posts by Ruturaj Vartak

Replication: Configuring Slave

Now that master is all set! Its time for the slave to obey the master. To setup the slave, We need to tell the Slave server which log file it is supposed to read, and yet again we need to give a unique server-id to the slave.

So open the /etc/my.cnf and add the following below the [mysqld] section.

server-id=2
replicate-do-db=test
report-host=slave-server-1

  • The directive replicate-do-db: specifies that only test database is to be replicated.
  • The directive report-host: specifies the hostname that will be seen on the master

Restart your MySQL server.

Now we are ready to tell the server about the master server.
Give following command to the slave server.

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.5.100',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000001',
    ->     MASTER_LOG_POS=4;

The master_log_pos is the parameter which tells the server to read the file from a particular position.
You can check the position by the following command on the master server.

mysql> show binlog events in 'mysql-bin.000009' limit 10;
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000009 | 4    | Format_desc | 2         | 98          | Server ver: 5.0.19-standard-log, Binlog ver: 4                     |
| mysql-bin.000009 | 98   | Intvar      | 2         | 126         | INSERT_ID=10077                                                    |
| mysql-bin.000009 | 126  | Query       | 2         | 430         | use `test`; insert into testtable (tname) values('ruturaj vartak') |
| mysql-bin.000009 | 430  | Query       | 2         | 511         | use `test`; truncate table testtable                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+

So you can MASTER_LOG_POS set anything in the Pos column.

This should set up all the required configuration of the slave. You just need to tell slave to start the replication of the slave by the following command

mysql> start slave;

Replication: Configuring Master

To configure the master we need to start binary logging, and some minor tweaking. Before we move ahead, I assume that you have the root or Administrator access to the sytem where you are setting up replication. First let us setup the Binary logging.

To enable binary logging, we need to configure the configuration file /etc/my.cnf on Linux. If you are in Windows, I guess it should be in c:\program files\MySQL\MySQL Server 5.0\my.ini. Open the file and add the following below the [mysqld] section

log-bin=mysql-bin
server-id=1

So the configuration should look something like

[mysqld]
log-bin=mysql-bin
server-id=1
... other parameters ...

[mysql]
... parameters for mysql client ...
  • The directive log-bin defines the file name of the binary log. All the binary logs that will be created in the data directory, are named something like mysql-bin.000004
  • The directive server-id is to give the servers in the replication a unique numeric ID. It has to be an integer. So we’ll call our Master Server as 1.

This would set the master, all we require is to restart the Server. You can restart the server (in linux) with either of the following commands

  • service mysql restart
  • /etc/rc.d/init.d/mysql restart
  • mysqladmin -u root -p shutdown
    and then mysqld_safe &

In windows, you’d be having an easier interface 😀

Replication

If you are here reading Replication for MySQL, if you know what type of replication is available in other databases, then it may be a bit troubling for you to understand. However, One point will make everything clear.

In MySQL replication is that of the Binary Log, which has user’s commands logged in it.

Generally replication is used when there are many servers to balance the load and have the same data. In such cases, Usually A single master and multiple Slaves are used.

So let us consider we have 3 servers,

  • Server A: The Master Server (192.168.5.100)
  • Server B: The Slave Server no. 1 (192.168.5.101)
  • Server C: The Slave Server no. 2 (192.168.5.102)

The Servers B and C are peers to each other.

We’ll set up replication of the database test. When I say database “test”, it means its all tables are replicated as well. I’ll come up to the specific configurations, etc … but later. I assume we are starting with an empty database.

To setup replication, we need to configure the following

  • Master
    • Binary log
    • …and some configuration
  • Slave: Configuration of the master for the slave

URL Rewriting for content pages

Many of the content sites (blogs, news sites) that you see these days have a specific url for each page. Eg. News.com

Many of the sites have something like /news/75-news-title.html. You can’t have a actual distinct page for each content. The most common solution to this is URL Rewrite the content.

The idea is to grab the specific content from the URL and then map that content, id, or whatever from the url as a get parameter’s value to a specific page.

In the above scenario, check the URL: /news/75-news-title.html. What is commonly done is the content_id the key by which the content is mapped in the content table is placed in the URL along with the title.

As in this case
Content ID: 75
Title Text: news-title (The hyphens are to make it readable instead of %20 for space)

So lets assume that we have a page called news.php in which we will give a get parameter as newsid. All we got to do now is write the URL Rewrite rule using Apache’s mod_rewrite engine.

We’ll use the RewriteCond and RewriteRule

<IfModule mod_rewrite.c>
    RewriteEngine On

    RewriteCond %{REQUEST_URI} /news/([0-9]+).*\.html$
    RewriteRule (.*) /news.php?newsid=%1 [L]
</IfModule>

Now Let us look how we built the thing.

  • First we used the Server Variable REQUEST_URI, to match the pattern with the request. The variable is referenced using %{SERVER_VARIABLE} format.
  • The RewriteCond is basically a If condition, which means if the condition is true, only then the condition or rules below that statement will be executed. That means the pattern should match for the rule to work
  • The regular expression pattern we made was accepting a integer value after /news/, After that integer value any text can come. But should end with .html. As emphasized by the $ at the end.
  • Now if the Condition works, we need to write the rule for it, so we use RewriteRule. The first argument is .*, which means accept any URL
  • The second argument is the actual mapping of the news.php with the newsid parameter. Check that we’ve used %1 which means the first back reference of the RewriteCond regex pattern
  • Since our pattern was /news/([0-9]+).*\.html$ and had just one class in it, that class i.e. ([0-9]+) should be referenced by %1 in the RewriteRule directive

The magician, .htaccess file

Long before anything like web.config or web.xml was used/invented, Apache had this wonderful file “.htaccess”

This file as you would expect, is a file to control the Web Application’s behaviour. The possibilities with this file are endless… from Password Protected Directories to Complex URL Rewrites, All can be done using this file.

.htaccess

The file’s extension is “htaccess” and has no initial filename. This comes from the *nix’s legacy system of having all the hidden files starting with a period “.”

This file could be placed in any directory of your web application. Lets say your DocumentRoot is /domains/ruturaj.net. Now if you place the .htaccess file in the main DocumentRoot, Any configurations that are present in the .htaccess file are available in all the subfolders of ruturaj.net

So if I put the fol. code in the .htaccess file,

DirectoryIndex rutu-default.php

All the sub directories or folders in directory ruturaj.net will have rutu-default.php page as the default index page.

But to ensure that the .htaccess file is read and implemented, you need to tell Apache.
To tell Apache which is the standard Configuration file, you need to modify the entry in the httpd.conf file. AccessFileName is the parameter which specifies which file is the “.htaccess” file, by default, the value of the parameter is set to “.htaccess”

AccessFileName .htaccess

There is also another parameter, AllowOverride, which tells Apache whether to read and implement the AccessFileName. You need to make the foll. settings in your VirtualHost or Directory mapping as

AllowOverride All

This will enable the implementation of the .htaccess file.

Creating a Live Score Board Client Logic

The the custom JavaScript object player is defined as below.

function player () {
	
	this.name = '';
	this.serving = false;
	this.gamepoint = 0;
	this.sets = new Array();
	
	this.toString = function() {
		return (this.name + ": " + (this.serving ? 'Serving' : 'Facing') + "\n" + this.sets + "\n" + this.gamepoint);
	}
}

The sets member variable is an array.


Next comes the import updateScoreBoard function

function updateScoreBoard() {
	sc = xmlScore;
	pDOM = sc.getElementsByTagName("player");
	players = new Array();
	for (i=0; i<pDOM.length; i++) {
		players[i] = new player(pDOM[i].getAttribute("name"));
		players[i].name = pDOM[i].getAttribute("name");
		if (pDOM[i].getAttribute("serving") == 1) {
			players[i].serving = true;
		}
		// Now get the match sets for the players
		setDOM = pDOM[i].getElementsByTagName("set");
		for (j=0; j<setDOM.length; j++) {
			players[i].sets[j] = setDOM[j].childNodes[0].nodeValue;

			// Now get the curent Game point for the players
			players[i].gamepoint = pDOM[i].getElementsByTagName("gamepoints")[0].childNodes[0].nodeValue;
		}


//		alert(players[i].toString());
	}

	// Now that all data has been gathered...
	document.getElementById("player1").innerHTML = players[0].name;
	document.getElementById("matchplayer1").innerHTML = players[0].name;
	document.getElementById("player2").innerHTML = players[1].name;
	document.getElementById("matchplayer2").innerHTML = players[1].name;

	//Serving
//	document.getElementById("serving1").innerHTML = (players[0].serving)?'o':'';
//	document.getElementById("serving2").innerHTML = (players[1].serving)?'o':'';

	// Sets
	document.getElementById("setplay11").innerHTML = players[0].sets[0];
	document.getElementById("setplay12").innerHTML = players[0].sets[1];
	document.getElementById("setplay13").innerHTML = players[0].sets[2];
	document.getElementById("setplay21").innerHTML = players[1].sets[0];
	document.getElementById("setplay22").innerHTML = players[1].sets[1];
	document.getElementById("setplay23").innerHTML = players[1].sets[2];

	// Game points
	document.getElementById("gamepoints1").innerHTML = players[0].gamepoint;
	document.getElementById("gamepoints2").innerHTML = players[1].gamepoint;
}
</script>

Notice the players variable is an array of player object. The traversing of XML content is easy. If you are unable to understand JavaScript DOM methods check following links

You can find a full working example here, Live Score Board

Creating a Live Score Board Client Logic

Let us start with a script tag that will enclose all the fundooo script. You can save in a .js file as well if you want…

The httpObj is the XMLHttpRequest object, and xmlScore is the response XMLDocument. I’ve created a player object class which is used by the tplayers variable.

<script type="text/javascript">
var httpObj, xmlScore, players;
function getScore(round, category, sex, player) {
	url = "http://localhost/score_server.php";
	url += "?round="+round+"&category="+escape(category)+
		"&sex="+escape(sex)+"&player="+escape(player)+"&rand="+new Date() ;
	httpObj = false;
    // branch for native XMLHttpRequest object
    if(window.XMLHttpRequest) {
    	try {
			httpObj = new XMLHttpRequest();
        } catch(e) {
			httpObj = false;
        }
    // branch for IE/Windows ActiveX version
    } else if(window.ActiveXObject) {
       	try {
        	httpObj = new ActiveXObject("Msxml2.XMLHTTP");
      	} catch(e) {
        	try {
          		httpObj = new ActiveXObject("Microsoft.XMLHTTP");
        	} catch(e) {
          		httpObj = false;
        	}
		}
    }
	if(httpObj) {
		httpObj.onreadystatechange = processReqChange;
		httpObj.open("GET", url, true);
		httpObj.send(null);

		timerID = setTimeout("getScore('"+round+"', '"+category+"', '"+sex+"', '"+player+"')",30000);
	}
}

The getScore function requires params, that are need for the server. If you notice at the end of the function, the function calls itself again after 30 sec (30,000 msec). Assuming one serve takes half a min or so.

We define the function that will handle the process’ change of status, i.e. when the status turns 4, it assigns xmlScore the XMLDocument object.

function processReqChange() {
    // only if req shows "loaded"
    if (httpObj.readyState == 4) {
        // only if "OK"
        if (httpObj.status == 200) {
            xmlScore = httpObj.responseXML;
			updateScoreBoard();
        } else {
            alert("There was a problem retrieving the XML data:\n" +
                httpObj.statusText);
        }
    }
}

Making a Live Score Board – Client

Here comes the cool part now. The client! using the famous XMLHttpRequest

The basic logic of creating the client is this

  • Make asynchronous calls to the server to fetch the match data
  • Parse the XML data
  • Using DOM update the content of the Score board
  • And obviously add a timer to refresh the asynchronous HTTP calls


It should look something like this.
Score card screenshot

The first time you load the page (most probably it should be a small pop-up window) you can write the basic HTML…
and the main HTML would be something like this.

<div id="scoreboard">

<p style='font-weight:bold;'>
<span id="matchsex">Womens</span> <span id="matchround">Semifinal</span>: 
<span id="player1">Hingis</span> vs <span id="player1">Sharapova</span> 
(<span id="elapsedtime">1.5h</span>)</span> 
</p>

<table style="border-collapse:collapse;" border="1" bordercolor="#888888" 
cellspacing="0" cellpadding="5">
	<tr>
		<td align="left" > </td>
		<td colspan="3" align="left" >Sets</td>
		<td align="left" >Game</td>
	</tr>
	<tr>
		<td align="left" id="matchplayer1">Hingis </td>
		<td align="center" id="setplay1">6</td>
		<td align="center" id="setplay2">3</td>
		<td align="center" id="setplay3">4</td>
		<td align="center" id="gamepoints1">30</td>
	</tr>
	<tr>
		<td align="left" id="matchplayer2">Sharapova</td>
		<td align="center" id="setplay1">4</td>
		<td align="center" id="setplay2">6</td>
		<td align="center" id="setplay3">2</td>
		<td align="center" id="gamepoints2">15</td>
	</tr>
</table>

</div>

Let us consider some important IDs (the id attributes) that we’ve used

  • matchsex: To set the type of match women’s, men’s, etc.
  • matchround: Quarter final, semi final, etc
  • player1, player2: Player’s names id
  • elapsedtime: Elapsed Time
  • matchplayer1, matchplayer2: Score Board’s Player’s name
  • serving: If the player is serving
  • setplay1, setplayx: Playerx’s set
  • gamepoints1, gamepoints2: Player1 and 2’s gamepoints