A simple shell script to check MySQL slave replication status in a cluster

We use MySQL in a master-slave configuration, with one of the slaves designated to take over as the master should anything nasty happen to the master.

Before switching to the new master, we used to flip onto each slave and check the replication status. This got to be quite stressful and prone to error, so I’ve written a small shell script to help with this. Comments welcome! Enjoy!

As root, put this script in /usr/local/bin, call it (say) mysql-get-slave-server-status

echo ""
echo "Checking status of mysql servers"
echo "================================"
echo ""
echo "SSH-ing onto the servers listed in $mysqlServerFile"
echo "Running SHOW SLAVE STATUS on each box"
echo "Storing results in /tmp"
echo ""
for host in $(cat $mysqlServerFile)
 echo $host
 ssh -l root $host "mysql -u root -p$mysqlmaster -Bse 'SHOW SLAVE STATUS\G'" > /tmp/mysql-slave-status-$host.txt

echo ""
echo "Extracting data based on list in $parameterFile"
echo "================================================================================"
echo ""

for parameter in $(cat $parameterFile)
 echo $parameter
 echo "------------------------------"
 for host in $(cat /etc/cluster/mysql-servers)
 value=`cat /tmp/mysql-slave-status-$host.txt | grep $parameter | awk '{print \$2}'`
 echo $host: $value
 echo ""

The box this runs on will need to be able to ssh onto the other servers without needing a password. Don’t forget to put it on your failover box as well!

You will need to tweak mysqlmaster, parameterFile, and mysqlServerFile settings and then create the mysql-parameters and mysql-servers files to cover your cluster

For example, mysql-servers:

And mysql-parameters


Good luck. Please note I’m not able to provide tech support (sorry!). But if you do make an improvement, let me know. You might be able to test if the mysqld process is running on the box first, for example.



Posted in Techy Stuff | Tagged , , , , | Leave a comment

The SaaS market is changing – perspective from a SaaS MD

Until recently, the market for SaaS software has been like the “American Frontier” in the United States – what I mean by this, in the context of my absence management software company TeamSeer, is that we were selling to customers who were buying something completely new, had probably never bought a SaaS product before, and were probably using paper forms and spreadsheets to track their holidays. We might on rare occasions replace “in-house systems” (as an aside, see my post on “why developing an in-house absence management system is crazy” from 2010) but we never, ever replaced one of our competitor’s systems.

Twelve months ago, that started to change. We won our first customer from a competitor that had let them down. Since then, we have won several new customers who have been using another SaaS product. There seem to be two main reasons – limited functionality, and poor customer service. Not price.

As regards functionality, one of the things that’s interesting / difficult about selling software is resisting the urge to show your prospective customer everything the software can do. It presents us with a dilemma, because while we think TeamSeer has the most comprehensive functionality, we don’t want to overwhelm our prospective customers. So if a prospect has a demo from Competitor A, Competitor B, and a demo from us, then they might go away thinking “well, these systems are all the same”.

And customer service, well, you get what you pay for. When companies have been buying SaaS software they have (in my view) generally ignored the question “what’s your customer service like”. And of course, if you were to ask a software “what’s your support like” they would hardly be likely to tell you it’s poor, are they! 

So – you can only find these things out after you’ve lived with someone for a while. Your software doesn’t do something you want it to do, and the support you are getting isn’t great. And if a company now has several SaaS providers, and a range of experience with them, they can get smart looking around.

My knowledge of American history is a bit limited so I will avoid trying to apply what happened in the US after the colonisation to the SaaS market – but it might be fun! Wars, gold rush, railroads, bandits.. thoughts anyone?!

Posted in Uncategorized | Leave a comment

SSL Certificates for Exchange 2010 – Generate a CER file not a REQ file

We use the site Certificates for Exchange to generate a multi-domain certificate. When we did the renewal, the new certificate wouldn’t install because it said PrivateKeyMissing.

So we tried to generate a new certificate request from the Exchange Management Console, but it only generated a .REQ file, not a .CER file. What to do?!

For any other confused occasional Exchange Administrators, here’s what we did.


  • exchangeserver.company.com
  • autodiscover.company.com
  • servername.company.local
  • autodiscover.company.local

But the Exchange Management Console doesn’t allow you to generate CER format certificates.

To do this, after a bit of googling (from http://www.digicert.com/csr-creation-microsoft-exchange-2010.htm):

In the Exchange Shell:

New-ExchangeCertificate -GenerateRequest -KeySize 2048 -SubjectName "c=UK, l=London, s=London, o=COMPANY, cn=COMPANY" -DomainName exchangeserver.company.com, autodiscover.company.com, servername.company.local, autodiscover.company.local -PrivateKeyExportable:$true

[Replace company.com etc with your own requirements of course!]

This dumps a certificate to the screen which you can copy to clipboard (right-click and select Mark, then select the screen area, then hit enter to copy to clipboard).

Now you need to find your certificate in Certificates for Exchange and “Re-Key” it.

Paste in the CER, and re-download the CRT file.

Now, in Exchange Management console, refresh and you will see your new request listed. Right-click and select “complete certificate request” and the rest is easy.

By the way, if you get stuck in a loop of doom and can’t seem to delete a certificate using

Remove-ExchangeCertificate -Thumbprint BLABLABLA

because you get an error, then you can delete it by running mmc, snap-in the certificate console, and find the certificate (the SHA-1 key is the thumbprint).

Good luck!

Posted in Techy Stuff | Leave a comment

The Joys of SaaS

The team has been working flat out on the new version of our software, and last night we did a huge release of source code that has been building up and up and up over the last 3 months. So far, everything is running smoothly (touch wood) and I am really pleased and excited.

The thing is .. logging in this morning, none of our customers will notice any difference.

This is one of the “joys of SaaS” and something that is hard to put across to customers as a benefit. “We are working hard for your money, honest!” I say; but when the only thing they see is an hour of scheduled downtime, then it’s difficult to prove.

If we were offering an on-premise solution, the customer would have to pay for a new version, and put up with (and probably pay for) months of implementation. Because of this, as an on-premise provider, you would need to ram in new features to justify the extra cost, when in fact a big part of staying up to date is making sure the low-level stuff (database, code base, framework) is regularly refreshed. How many corporates do you know that are still running applications that “only work on Windows 2000” or “need IE6”. As a SaaS provider, you’ve got the luxury of doing things in phases: you can do a “boring release”, and then do the “cool release”. Once you’ve got the low-level stuff sorted, you can then offer new super-cool features you could never have offered if you were on your old stack. Just don’t expect anyone to pay you for the boring stuff.

In the last 6 months we have completely changed our infrastructure, introduced a new framework, brought in multi-lingual capability, refactored out about a quarter of our old code. It’s like painting the Forth Road Bridge!

As the MD / CEO, you must make sure your team are continually doing some of the “boring” stuff (mixed in with the cool stuff of course) or your will get bitten later.

Now, onto some cool stuff…!

Posted in Uncategorized | Leave a comment

Is BT selling the fact we’ve moved office to dodgy people?

We just moved office last week, and pretty much the only company who knew about that were the people involved with getting our new phone system – 

  • Approved Index
  • our telephone provider
  • BT

So why are we now getting snowed with mail that is the corporate equivalent of the Nigerian email-me-your-bank-account-number?

  • A phone call from the Utility Registration Board
  • Industry and Commerce Register of Business Information Valencia

We spoke to our telephone provider and they promised they aren’t selling the information .. anyone else got any interesting stories?

BT – are you going to comment and say you don’t do this?

Posted in Uncategorized | Leave a comment

Changing from Sendmail to Postfix on Debian

Our production servers had sendmail on them, and we wanted to upgrade to postfix. Our specific requirement is that each production server sends emails but doesn’t receive them. This was on Debian Squeeze, not sure about Etch or Lenny. You will need to have root access and control the whole box, i.e. you are on a dedicated server.

As always, try at your own risk.

Here’s how we did it:



/etc/init.d/sendmail stop
apt-get install postfix

[Selected ‘no configuration’]

cp /usr/share/postfix/main.cf.debian /etc/postfix/main.cf
vim /etc/postfix/main.cf

Insert at the end of this file..

# by Me on todaysDate
# Mail should all be from (e.g automailer@mydomain.com)
myorigin = mydomain.com
# Can comment the next line out if we can get myhostname from /etc/hostname
#myhostname = subdomain.mydomain.com
#No relaying!
relayhost =
relaydomains =
mynetworks_style = host
inet_interfaces = loopback-only
local_transport = error:local delivery is disabled
# Use TLS, if the recipient's mail server has the capability
smtp_tls_security_level = may

Now disable local

vim /etc/postfix/master.cf
Comment out local
#local unix - n n - - local

Start postfix..

/etc/init.d/postfix start

Check the version

postconf -d | grep mail_version

Try an email – it might go to junk mail!

echo "testing external domain delivery" | mail -s "test email to outside world" myemailAddress@mydomain.com

Check sendmail has been removed (it should say it is already removed)

apt-get remove sendmail

Check to see postfix is fired up, not sendmail!

tail /var/log/mail.log

Also – check the message headers in the email you get, to see if it has used TLS (of course, this depends on whether or not your mail server supports it).

Posted in Uncategorized | Leave a comment

Backing up Windows Server 2008 files nightly to offsite Debian Linux box using cwrsync (rsync)

We’ve had endless problems trying to get offsite backups of our office Windows Server 2008 data. The batch script we designed would run fine in testing, but then fail when run as a scheduled task.

This post is designed in the hope anyone stuck with a similar problem might find our solution useful (it seems like we finally got it working!).

Again, you will need to be quite techy already for this post to make sense.

First, a bit of background:

We do nightly full backups to a USB hard drive that sits in the office data room

So we just want to back up data on our shared drive and our Users drive (we have these on a partitioned D drive – you might have yours on your C drive)

We have an offsite server that runs Debian Linux (so if you are backing up to another Windows box this post won’t help – but I would guess any Linux box would be fine).

Some of the things we did:

  • make sure you do everything as an administrator on the Windows box
  • install cwRsync (we did it into in C:\Program Files (x86)\cwRsync)
  • get rsync running properly on the remote backup server
  • set iptables on the remote server to allow ssh connections from our office
  • set up a public-private key, installed in C:\.ssh
  • get ssh to work
  • get rsync to work when it is run manually (as administrator)
  • set up a Windows scheduled task with ‘highest privileges’
  • make sure that the administrator that will be running the Windows scheduled task has “Full control” Windows security permissions over the files you want to back up (this will usually be fine – if the user is a member of the Administrators group)
  • make sure if you’ve got a shared network drive (we call ours the p drive) that your user’s virus checkers aren’t scanning files on this drive
  • make sure if you’re running a full backup, that you do the rsync-ing after this has finished

We then created a batch script – here it is:


SET CWRSYNCHOME=C:\Program Files (x86)\cwRsync
SET CYGWIN=nontsec
SET DESTINATION_USER=[user name on your backup server]
SET DESTINATION_HOST=[IP address of your backup server]
SET EXCLUDE_COMMANDS=--exclude="**/My Documents/$RECYCLE.BIN/**" --exclude="**/Downloads/**" --exclude="**/$RECYCLE.BIN/**"

CALL :s_rsync pdrive
CALL :s_rsync Finance
CALL :s_rsync Users

GOTO s_end


echo %DATE% > d:\%1\.sync
echo Starting backup of %1 > %LOGFILE%
echo %DATE% >> %LOGFILE%
echo %TIME% >> %LOGFILE%
echo Logs >> %LOGFILE%
rsync -a -v -v -v --delete --timeout=120 %EXCLUDE_COMMANDS% --chmod u+rwx -e "ssh -i c:\.ssh\id_dsa" "/cygdrive/d/%1" %DESTINATION_FULL% >> %LOGFILE%
GOTO :eof

ECHO "Done"

You will definitely need to tweak this script to your own requirements. It backs up D:\pdrive, D:\Users, D:\Finance – you will of course want to do something different.

The key bits in it that we’ve found useful are:

The SETLOCAL and ENDLOCAL commands in DOS mean that the variables we create only persist while we’re running the batch file

EXCLUDE_COMMANDS is a bit of a pain but we couldn’t get cwRsync to use an external file list of excludes. You will probably want to tweak this, but the list here is quite a useful start

We put a file .sync into the folder we’re backing up so that we can run a cron job on the backup box to check the time stamp of this file and make sure the backups have run.

The -v -v -v bit of rsync makes it create a full debug output – great for seeing what’s gone wrong (which it may well do!)

The –delete bit will delete anything on the backup server, if it’s been deleted on the Windows box

The –timeout=120 is a bit of an experiment, to try and stop the Windows scheduled task hanging if for some reason ssh doesn’t work

The –chmod u+rwx sets the correct permissions on the files on the backup server

The -e “ssh -i c:\.ssh\id_dsa” is important – it makes sure that when you are running as a Windows scheduled task, that the ssh bit of cwRsync can find the key

We dump all the output of rsync to a log file (we created a subfolder of the cwRsync directory called logs, but you could keep this anywhere).


Posted in Techy Stuff | 1 Comment