1. Keeping a “hot spare”. You have an additional database already configured as a master. This might not seem like much, since you “almost” gain the same thing in a master-slave setup. However, if your master-master includes slave servers, this topology provides a very high degree of fault tolerance. Especially when each master-server pair is geographically separated.

2. Making changes to the database. Certain changes made to the database may require a long time to complete, particularly if you have a very large database. In the master-master setup, you can take one of the servers “offline” (by telling the other master not to replicate its changes), and make the changes necessary. Then bring it back online, after the changes have been made, make it the active master, and let the other master perform the changes.

This could be seen as an added benefit of keeping a “hot spare”.

The good news is that setting this up is identical to setting up master-slave replication, you simply do it twice. Each master is essentially the master of and a slave to the other database. To keep it active-passive, one of the databases will need to be made read-only. Here are the changes you will need to the my.cnf configuration file:

Active Master my.cnf

log_bin = mysql-bin
server_id = 1001
relay_log = mysql-relay-bin
log_slave_updates = 1

Passive Master my.cnf

log_bin = mysql-bin
server_id = 1002
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1  # Notice this line

Then set up the replication user accounts, as described in this post: Simple MySQL Master-Slave Replication

Finally you issue the slave directives, and start the slave process:

Active Master ‘change master’

# Active master is slave to passive host
CHANGE MASTER TO MASTER_HOST='passive.mysql.host',
MASTER_USER='rep_user',
MASTER_PASSWORD='reppassword',
MASTER_LOG_FILE='mysql-bin-000001',
MASTER_LOG_POS=0;

start slave;

Passive Master ‘change master’

# Passive master is slave to active host
CHANGE MASTER TO MASTER_HOST='active.mysql.host',
MASTER_USER='rep_user',
MASTER_PASSWORD='reppassword',
MASTER_LOG_FILE='mysql-bin-000001',
MASTER_LOG_POS=0;

start slave;

Because of the way replication works in MySQL, you will need to turn on binary logging. Essentially, the slave is going to connect to the master and request the log. After it gets the log (or the parts that it needs) it will replay it, executing the queries to bring it up to date. You also need to give the server an ID. So add these lines to the my.cnf (usually /etc/my.cnf) on the master database:

<mysqld>
log_bin = mysql-bin
server_id = 1001

You can give it any ID you want, you just want it to have a different ID than the slave. In the slave we will add a few additional lines, as well:

<mysqld>
log_bin = mysql-bin
server_id = 1002
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

Note: Replace the angle brackets above with square brackets. I haven’t figured out how to make my code plugin not treat [mysqld] as another code block definition.

According to Schwartz, et al. in High Performance MySQL (review forthcoming), the only parameter required on a slave is the server_id. The other parameters make it easy to switch the server between being a master or a slave. They also mention the read_only parameter is a good safety precaution but might not be applicable in all cases.

Replication privileges need to be granted to the replication user on the master. If you want to be able to easily switch your server between master or slave, you can grant these privileges on both servers.

GRANT REPLICATION SLAVE
ON *.*
TO 'rep_user'@'10.0.0.%'
IDENTIFIED BY 'reppassword';

There are certain features that are accessible by granting the REPLICATION CLIENT privilege. You can also limit the replication privileges to only certain databases or tables if so desired. Though it is not recommended, you could also grant access to all hosts, just as you might in any other GRANT statement.

Now that both the master and slave are configured, and correct permissions are granted to the replication user, the slave needs to be “started”. This is done by declaring the master host, and indicating necessary credentials and log file information, and then issuing the ’start slave’ command.

The host and credential information can be declared in the my.cnf file. However there are some advantages to making the declarations as MySQL commands. Specifically you will be able to make changes to these without having to restart the daemon. Here is the command you should issue to configure the slave:

CHANGE MASTER TO MASTER_HOST='master.mysql.host',
MASTER_USER='rep_user',
MASTER_PASSWORD='reppassword',
MASTER_LOG_FILE='mysql-bin-000001',
MASTER_LOG_POS=0;

And then you issue the command to start the slave:

start slave

If you were previously using binary logs on the master, and then cloned the server to create the slave, you might have trouble getting replication started without first deleting the binary log from the slave.

Sources
The MySQL documentation
High Performance MySQL, by Baron Schwartz et al.

There is a register.php script residing on the load balancer, accessible via HTTP.
There is a deregister.php script residing on the load balancer, accessible via HTTP.
There is a register_with_lb.pl script residing on the web server, in /usr/local/bin/.
There is a deregister_with_lb.pl script residing on the web server, in /usr/local/bin/.
There is a MySQL database that stores the current configuration state, on it are two stored procedures register_lb and deregister_lb.

No changes were made to register.php as described in this post , though I’m considering some alterations to increase its security.

The biggest difference between register.php and deregister.php (aside from their purpose) is where the insert/delete database code is called from and why. When register.php is called by the web server, it will have already inserted information about itself into the database, including its hash. I made the decision that I did not want the load balancer responsible for inserting servers into the database. It would merely check that the requesting server inserted itself, and then regenerate the balancer_members.conf.

In the case of deregister.php I decided I wanted the server making the call to still be in the database so the script could verify the identity before removing it and regeneration the balancer_members. And since the deregistration SQL is contained within a stored procedure, I needed to make some changes to the script (as compared to register.php) regarding the database.

Specifically, the standard mysql library cannot call stored procedures. So I had to convert it to using mysqli, which is a similar, though more OO approach. The portion of the code that regenerates the balancer_members.conf is similar enough that I won’t re-list it here, but I will show how to connect using mysqli, and how to call a stored procedure.

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
}

$query = "SELECT count(*) as count FROM " . $dbtable . " WHERE ip='" . $_SERVER['REMOTE_ADDR'] . "';";
$result = $mysqli->query($query);
$row = $result->fetch_row();
echo $row[0];

if ($row[0] >= 1) {
  $del_query = "call deregister_lb('" . $_SERVER['REMOTE_ADDR'] . "');";
  $del_result = $mysqli->query($del_query);

  //<code for regenerating the conf file removed here>

  echo exec('echo "' . $file . '" > /etc/httpd/conf.d/balancer_members.conf');
  echo exec("sudo /usr/local/bin/reload_httpd");
}

As you can see, I’m using the actual REMOTE_ADDR to determine the validity of the request.

Here is the code for the deregister_lb stored procedure:

DROP PROCEDURE IF EXISTS deregister_lb $$

CREATE PROCEDURE deregister_lb ( ip VARCHAR(100) )
  BEGIN
    DELETE FROM lb2_members
	WHERE ip=_ip;
  END $$

and also for the register_lb stored procedure:

DROP PROCEDURE IF EXISTS register_lb $$
CREATE PROCEDURE register_lb (
  _hostname VARCHAR(100),
  _ip VARCHAR(40),
  _loadfactor INT,
  _hash VARCHAR(100)
  )

  BEGIN
    DECLARE already_exists INT DEFAULT 0;
    SELECT count(*) INTO already_exists FROM lb2_members WHERE hash=_hash;

    IF already_exists=1 THEN
	  UPDATE lb2_members
	  SET hostname=_hostname, ip=_ip, loadfactor=_loadfactor
	  WHERE hash=_hash;
    ELSE
      INSERT INTO lb2_members (ip, hostname, loadfactor, hash)
	  VALUES (_ip, _hostname, _loadfactor, _hash);
    END IF;
  END $$

Note that I’ve omitted the code that changes the delimiter to $$ instead of a semicolon.

This perl script uses perl DBI for accessing the database. I had to get that installed on my web server since it wasn’t already. Normally you can install perl packages using the cpan command. In which case you would issue the following commands to install DBI and a MySQL driver for it:

cpan DBI
cpan DBD::mysql

If it’s the first time you’ve run cpan, you will need to go through some configuration. It’s pretty much self explanatory, and I just accepted all of the defaults. Everything installed correctly except for the MySQL driver, which I ended up having to install from source. If I had executed the command:

yum install mysql-devel.i386

first, then my cpan install of DBD::mysql might have worked, but I didn’t realize that until installing from source. In case you ever need to install a perl module from source, particularly the DBD::mysql driver, enter these commands (which I think is basically what cpan does):

yum install mysql-devel.i386 #(only requred in this particular instance)
wget http://www.cpan.org/modules/by-module/DBD/DBD-mysql-4.011.tar.gz
gzip -cd DBD-mysql-4.011.tar.gz | tar xf -
cd DBD-mysql-4.011 #(or whatever version you downloaded)
perl Makefile.PL
make

Here is how you connect to the database and call a stored procedure:

my $dsn = "DBI:mysql:host=mysql.host;database=lb_register";
my $dbh = DBI->connect ($dsn, "lbuser", "lbpasswd")
  or die "Cannot connect to MySQL server\n";

my $sql = "call register_lb('" . $localhost . "', '" . $localip . "', " . $loadfactor . ", '" .  $hash . "')";
$dbh->do($sql);

$dbh->disconnect();

After that, register_with_lb.pl opens a socket to the load balancer and makes an HTTP request over the socket. There are probably easier ways to do this, I just happened to have the socket code lying around and was glad to be able to reuse it. Here’s the gist of it, in case you’re interested:

# Parse the URI.
my $url = URI->new("http://load.balancer.com/register/register.php?hash=" . $hash);

# Parse these in from the command line
$host = $url->host;
$port = $url->port;
$resource = $url->path;
$query = $url->query;

# Initialize the socket
$socket = IO::Socket::INET->new ( Proto => "tcp", PeerAddr => $host, PeerPort => $port,);
unless ($socket) { die "Error connecting to $host" }
$socket->autoflush(1);

# Format the request
my $request = "GET " . $resource . (($query)?"?" . $query : "") . " HTTP/1.1" . $EOL . "Host: " . $host . $EOL . "User-agent: register_script" . $EOR;

# Use send() to make the request, and output the response.
# Not necessary in this example, but informational.
if ( $socket->send($request) ) {
  while ( <$socket> ) { print }
}

# Close the socket
close $socket;

The above code pretty much sums up deregister_from_lb.pl, since no database calls are made, a call is simply made to the deregister script. The line you would change is as follows:

my $url = URI->new("http://my.balancer.com/register/deregister.php");

Then make the files executable, and copy them to be used by the startup script described in the previous post:

chmod a+x register_with_lb.pl
chmod a+x deregister_with_lb.pl
cp register_with_lb.pl /usr/local/bin/
cp deregister_with_lb.pl /usr/local/bin

I don’t show it here, but right now my IP addresses are hard coded. There are a number of ways you can find out your actual IP address from within perl, I’m just not doing that right now.

As an additional security measure, I’ve restricted access to the /register/ location on the load balancer to the IP address range I expect my web servers to be from, like this:

<Location /register>
  Order Deny,Allow
  Deny from all
  Allow from 10.0.0.
</Location>

And now you have a web server that can register automatically (if you’ve gone through the previous two posts as well) with a mod_proxy_balancer load balancer.

I did some searching around to find a way to determine your IP address from inside the perl script. This is a simple way if your server has a public IP address and reverse DNS set up correctly for that IP address:

use Socket;
use Sys::Hostname;
my $host = hostname();
my $addr = inet_ntoa(scalar(gethostbyname($host)) || 'localhost');

If your slave web servers are on a private network, the above command will return the loopback IP address (127.0.0.1) which isn’t useful for the load balancer (I wonder if it would start an infinite loop and crash the load balancer?). I found a function that prints out the IP address by parsing it out from the results of the ifconfig command.

It seemed a little long to just rip off and copy verbatim. So here’s a link to that code (which is what I’m using now) in case you’d like to use it. Perl script to get IP address.

Here is my flowchart for how self registration will work:
1. Server comes online.
2. A startup script will register itself with the MySQL database (including hostname, ip, loadfactor, and a hash that it will generate in some way).
3. The server will then call a PHP script on the load balancer: “register/register.php”.
4. The PHP script will verify that a server sent the request.
5. The PHP script will query the database to get the current list of balancer members, and regenerate the balancer_members.conf file.
6. The PHP script will then issue a command to reload Apache’s configuration files.

Deregistration, which my PHP script as presented doesn’t display, will work as follows:
1. Server sends its hash to the PHP script, and shuts down.
2. The PHP script will check the hash against the database.
3. The PHP script will remove the server from the database.
4. The PHP script will repeat steps 5 and 6 above.

First, set up the database and created a user with sufficient privileges.

CREATE DATABASE lb_register;
GRANT ALL ON lb_register.* TO 'lbuser'@'%' IDENTIFIED BY 'password';

CREATE TABLE lb2_members(
ip VARCHAR(20) NOT NULL PRIMARY KEY,
hostname VARCHAR(100) NOT NULL,
loadfactor INT NOT NULL DEFAULT 0,
hash VARCHAR(40) );

Second, create the PHP script.

$dbhost = "mysql.host.com";
$dbuser = "lbuser";
$dbpass = "password";
$dbname = "lb_register";
$dbtable = "lb2_members";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());
mysql_select_db($dbname);

$query = "SELECT count(*) as count FROM " . $dbtable . " WHERE hash='" . $_GET['hash'] . "';";
$result = mysql_query($query);

$row = mysql_fetch_assoc(mysql_query($query));
if ($row['count'] >= 1) {

  $file = "<Proxy balancer://mycluster>" . "\n";
  $member_query = "SELECT hostname, loadfactor FROM " . $dbtable . ";";
  $member_result = mysql_query($member_query);

  while ($row = mysql_fetch_array($member_result, MYSQL_BOTH)) {
    $file .= "   BalancerMember http://" . $row['hostname'] . " ";
    $file .= ($row['loadfactor'] > 1) ? ("loadfactor=" . $row['loadfactor'] . "\n") : "\n";
  }
  $file .= "</Proxy>";

  exec('echo "' . $file . '" > /etc/httpd/conf.d/balancer_members.conf');
  exec("sudo /usr/local/bin/reload_httpd");
}

mysql_close($conn);

You can tell a few things about the server configuration by looking at the script:
1. User apache will need to be able to write to the “/etc/httpd/conf.d/balancer_members.conf” file.
2. User apache will need to be able to execute the script “/usr/local/bin/reload_httpd”.
3. User apache will need sudoer rights.
4. This script was used for debugging, and not by a server that is actually registering… tyou can see that deregistration is not handled yet.

To grant write privileges to apache, I changed the owner of the balancer_members.conf to apache.

 chown apache /etc/httpd/conf.d/balancer_members.conf

This is probably the least secure aspect of my solution, as if the apache user were compromised, then any directives could be written to this file. I’m not sure how big a threat this is, but it’s something that concerns me at least enough to think about this some more (and invite suggestions).

Next is to grant apache privileges to execute “/usr/local/bin/reload_httpd”. We could accomplish this the same as we did above, but then it wouldn’t allow apache to execute what’s inside of the script, which is this:

#!/bin/bash
service httpd reload

unless we give execution rights to apache on service, which we don’t want. What we also don’t want is for apache to be able to write to the file reload_httpd. So what I ended up doing was, as you see in the script, to make root the owner of reload_httpd and remove write privileges for all (so apache couldn’t change it) and then add apache to the sudoers file, granting rights to execute this script without a password.

visudo

is the generally accepted way to edit the sudoers file. And I added this line:

apache ALL=(ALL) NOPASSWD: /usr/local/bin/reload_httpd

I’m open to more secure ways of implementing this aspect as well, as I don’t consider myself a sudo configuration expert. I think this gives apache rights to execute everything from anywhere if he knows the password; but he can also execute the /usr/local/bin/reload_httpd script without a password.

I also had to comment out the line:

#Defaults   requiretty

to allow sudo to function properly from a script not executed in a terminal.

Finally I had to disable proxying for the register script in my balancer.conf file:

ProxyPass /register/ !

And then your server is configured to dynamically update its list of balance members, you can check by going to the balancer-manager if you’ve got that enabled. Next I will discuss how to handle the web server side of things.

load_balancer -> (ws1, ws2, ws3) -> mysql_server

As you can see, it’s nothing fancy. To test the balancer, each web server has a PHP script that connects to the MySQL database and inserts its hostname and IP address. Then I could run a simple query to determine whether the balancer was distributing the load according to my rules.

The PHP script was basic too:

$dbhost = "mysql.host.com";
$dbuser = "testuser";
$dbpass = "testpass";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());
$dbname = "testdb";
mysql_select_db($dbname);

$query = "INSERT INTO testtable(ip, host) VALUES('" . $_SERVER["SERVER_ADDR"] . "', '" . $_SERVER["SERVER_NAME"] . "');";

print $query;
mysql_query($query);
mysql_close($conn);

I’ve done this a thousand times, so you can imagine my frustration at getting this error message:

[Mon Feb 01 16:22:21 2010] [error] [client 192.168.1.1] PHP Warning:  mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to MySQL server on 'mysql.host.com' (13) in /var/www/html/index.php on line 7

I spent almost 2 hours looking for various solutions. I’ll list the most common ones in case you’re searching for a solution and mine doesn’t work for you:
–Ensure that MySQL user permissions are configured correctly.
–Ensure that MySQL is running on the server and on the correct port
–Ensure that selinux is not blocking the MySQL port or the mysqld process

These three items can be tested by simply logging into MySQL from a remote host using the following command:

mysql -u testuser -p -h mysql.host.com testdb

If that gives you a MySQL prompt, you can rule out the above three causes.

Some of the less obvious suggestions, which didn’t solve my problem either were:
–Ensure MySQL is using the correct path to the mysql.sock in my.ini
–Ensure that the server wasn’t started with –skip-networking

Though I tried a number of configuration changes for both MySQL, the server MySQL was running on (including disabling selinux completely), and PHP, I failed to overlook that selinux was blocking Apache’s outgoing connections to the MySQL database.

So what finally solved my problem was disabling selinux completely on the server Apache was running on. I should have done this in the first place, as I normally do with my Linux desktop installations.

This can be accomplished by changing a line in /etc/selinux/config. Change the line that says:

SELINUX=enforcing

to

SELINUX=disabled

If you do some searching you can find out how to add an exception for Apache, after 2 hours I didn’t feel like fussing with those.

That’s a good thing in my opinion, because it means the material is so interesting that I can’t read for more than a chapter without getting on the computer and trying it out.  The first chapter was a tutorial, I thought I knew everything after I had gone through it and it took quite a bit of discipline (as well as a few error messages) for me to get back to the book and go through the topics.

There are three things I especially like about the book.

First was the additional coverage on triggers and transactions.  After reading this I feel like I haven’t really used MySQL at all– having never used stored procedures, OR transactions, OR triggers.  They were all topics that have been immediately applicable to my projects, because they were needed somewhere I just didn’t realize I could do them.

Second was the discussion of the material in the context of sound software engineering principles.  I always enjoy a refresher in those, and when I’m learning a new technology that’s usually when I need it most because I’m ready to hack everything together in my excitement.  For example, there’s an entire chapter on “Creating and Maintaining Stored Programs” as well as som optimization material and a discussion of best practices.

Third was their treatment on using stored procedures with specific programming languages.  These may some day go out of date, but they had chapters devoted to showing how to use stored procedures from PHP, Java, Perl, Python, and .NET.  All of which are relevant 4 years after publishing.

This book was an excellent choice for someone who has database experience, and some stored procedure experience (Oracle).  Even if you’re only familiar with the basics of MySQL, you will benefit from this book.  And it isn’t at all over the head of anybody with some database programming experience.  My only regret is that I didn’t find this book 4 years ago when it came out.