Tuesday, May 15, 2007

Auto-Generated-Consistent-ServerIds

I manage /etc/my.cnf from a template id. Since I use servers in pairs, all master-master setups, I need to ensure that the server id is unique and consistent for the server mysql is running on. Managing the id's is a pain in the ass, especially when servers change roles. For instance rotating a server in from another cluster, or recovering a server from backup.

So, I create a my.cnf.tmpl file and that is deployed to all database instances.

The /etc/init.d/mysql script executes a perl script to generate my.cnf from my.cnf.tmpl right before a start executes.


!/usr/bin/perl -w
#
#

use strict;


my $HOSTNAME = `hostname`;
chomp($HOSTNAME);

my ($name,$alias,$addrtype,$length,@addrs) = gethostbyname($HOSTNAME);

my ($a,$b,$c,$d) = unpack('C4',$addrs[0]);

my $cnfContents ='';
while(<>) {
$cnfContents .= $_;
}
$cnfContents =~ s/\%SERVERID\%/$b$c$d/g;
print $cnfContents;



This script gets the IP address of the host, reads stdin and prints out the my.cnf contents, after replacing %SERVERID% with an the last 3 octets of the IP address to ensure that the number fits in the variable size.


in /etc/init.d/mysql this is added


start')
if test -s $confdir/my.cnf.tmpl
then
if test -x $bindir/generateServerID.pl
then
cat $confdir/my.cnf.tmpl | $bindir/generateServerID.pl > $confdir/my.cnf
fi
fi



So, I'm ensured that my, my.cnf is generated and set to a server of which is the concat of the servers IP address.

2 comments:

linuxrunner said...

I usually use the last octect of the IP address, this works good as long as all your servers are on the same subnet

Anonymous said...

Managing your config files with cfengine or bcfg2 can also do the trick. In all cases, it's a good idea to have unique generated server ids :)