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.