Compare two MySQL databases and generate SQL

MySQL has a set of tools that does wonders. This is just one of them by mysqldbcompare

On a Debian / Ubuntu servers, you need to install mysql-python-connector and mysql-utilities and the following command will generate a difference SQL for the first database.

mysqldbcompare --server1=user:pass@localhost \
--server2=user:pass@localhost db_old:db-new \
-a --difftype=sql --changes-for=server1 \
--skip-data-check > diff.sql

Setup SSL on Ubuntu server using GoDaddy certificate

Let’s create a directory under the home dir so that we know where our certs are.

openssl genrsa -out 2048
openssl req -new -sha256 -key -out

Here you need to enter the details of the domain – Please note that the common name is the domain name on which the SSL will work. So, make sure whether you need the ‘www’ in place or not etc.

Now, login to your GoDaddy account and set up a new certificate under

For the CSR, you can take copy the content by


and paste it to the textbox.

You can double check the CSR content at or using the following command

openssl req -in -noout -text

Once you submit, it would have to undergo validation process. An email is sent to the domain owner for verification. Once verified it will be issued and available for download – ensure that Apache is selected when downloading the file. You can upload the zip file to the server and decompress it on the server itself –


The file contains two crt files – and gd_bundle.crt. Copy the certificate, godaddy bundle and the key to /etc/apache2/ssl directory

  sudo cp /etc/apache2/ssl/
  sudo cp /etc/apache2/ssl/
  sudo cp gd_bundle.crt /etc/apache2/ssl/

Now update the virtualhost entry as indicated below

Update the virtual host file

sudo nano /etc/apache2/sites-available/
<VirtualHost nnn.nnn.nnn.nnn:443>
  # Admin email, Server Name (domain name) and any aliases
  # Document Root (where the public files are located)
  DocumentRoot /blah
  <Directory /blah>
    Options -Indexes +FollowSymLinks
    Require all granted
  SSLEngine on
  SSLCertificateFile /etc/apache2/ssl/
  SSLCertificateKeyFile /etc/apache2/ssl/
  SSLCertificateChainFile /etc/apache2/ssl/gd_bundle.crt

For servers that are lower than Apache 2.2, use the following

<VirtualHost nnn.nnn.nnn.nnn:443>
  # Admin email, Server Name (domain name) and any aliases
  # Document Root (where the public files are located)
  DocumentRoot /blah
  <Directory /blah>
    Options -Indexes FollowSymLinks
    Order allow,deny
    allow from all
  SSLEngine on
  SSLCertificateFile /etc/apache2/ssl/
  SSLCertificateKeyFile /etc/apache2/ssl/
  SSLCertificateChainFile /etc/apache2/ssl/gd_bundle.crt

If you’re setting up multiple sites, you can create individual directories so as to keep the gd_bundle.crt exclusive to each other.

Reload apache and you should be all set.

  sudo /etc/init.d/apache2 reload

For the ssl sites to work, the mod_ssl must be enabled, if not get that enabled as well

sudo a2enmod ssl

Ubuntu setlocale warning – solved

On a newly setup Ubuntu 10.04 LTS machines, you may encounter the following error, when running commands or trying to auto complete or whatever;

warning: setlocale: LC_CTYPE: cannot change locale (UTF-8)

Here’s the fix – open up the nano with the default setlocate

sudo nano /etc/default/locale

Update the contents of the file such that it reads as the one below – you can select your own language settings – like en_IN or en_GB


To temporarily get started without a server reboot, use the following command

export LC_ALL="en_US.UTF-8"

We should be good.

Redirecting to a new domain

This can be used on both when a site is transferred to a new domain or from a staging URL

Many occasions you need to push for an https URL (SSL) – here’s how we can do it using .htaccess

RewriteEngine On
RewriteRule (.*)$1 [R=301,L]

You can leave the old copy of files there and this should ensure visitors don’t see things from there anyways.

Crontab – best practices

Crons can get us to impossible situations. They can get very difficult to trace where things can go wrong. Here I’m trying to outline the process of setting up crons and logging the activities so as to help avoid chaos.

To start editing the crontab

$ sudo nano /etc/crontab

Another way of editing the crontab is per-user crontab

$ crontab -e

First when setting up any crons on your server, ensure that they are grouped by the various sites you need them that are hosted on the server. Comment them out clearly and have all the team members to add or modify them in the appropriate sections.

# Crons for the site

Secondly, make sure you keep a copy of the template that’s found on top of the /etc/crontab under each section for quick reference.

# m h dom mon dow user  command

Ensure that the script is run via cron by setting the time close to the current time on the server

$ date

You can verify this by listing the running programs

$ ps aux | grep <the_script_file_name>

or by listing it from the cron log as in

$ sudo tail -100 /var/log/cron.log | grep <the_script_file_name>

Syntax Errors
To identify whether the crontab is read correctly, run the following commands – it could be bad username or bad minute or some such thing, check the recently added one, or comment and save to see others are running.

$ sudo cat /var/log/cron.log | grep bad
Jan 1 00:07:01 xxxxxxxxxx cron[17836]: Error: bad username; while reading /etc/crontab
# or
$ sudo cat /var/log/cron.log | grep Error:
Jan 1 00:08:01 xxxxxxxxxx cron[17836]: Error: bad username; while reading /etc/crontab

Pitfalls – the most common errors

Number of stars: It is very easy to get carried away in leaving an additional star on the cron string, more specifically when it is copy pasted from another one.

Missing username: When moving from cPanel based servers to VPS, we tend to paste the string from there which doesn’t have the username parameter.

Midnight: Very often, users tend to setup crons at midnight, there could be others running on shared resources cranking up the server load. Better to be safe and set the “once-in-a-day” cron to a jagged time like 2:32am or some such stuff.

Cron Log – setting up if it doesn’t exist

By default, logging is done by the syslog daemon and configured in /etc/rsyslog.conf. It gets difficult to monitor this since lot of things are written to syslog. To have the cron logs exclusive to /var/log/cron.log, do the following

Login a root user

$ sudo -s

Check and enable cron logging

$ nano /etc/rsyslog.d/50-default.conf

Add the following line: cron.* look for one that is commented out and uncomment it and restart the syslog daemon. You probably need to create the cron logfile too before restarting the syslog daemon.

# Create a cron log file, this step wasn't required for 14.04
$ touch /var/log/cron
# Make changes to the log file
$ nano /etc/rsyslog.d/50-default.conf
# and then
$ service rsyslog restart


Edit hosts file in a Mac

To edit the hosts file in Mac, open the Terminal. It is in Applications -> Utilities -> Terminal.

$ sudo nano /private/etc/hosts

Add the IP addresses and hostname(s) – use a space to separate aliases or multiple hostnames myapp.local www.myapp.local

Press Ctrl-O and press Enter to save. Press Ctrl-X to exit nano.

To flush DNS cache, on terminal issue the following command…

$ dscacheutil -flushcache

Way to go…

Remove SVN directories after checking out

Many get terribly annoyed by the .svn directories that are created by the SVN clients. Here’s a quick way to list them

$ find -type d -name .svn

To delete them now combine it with rm

$ rm -rf `find -type d -name .svn`

Please note the reverse quote that’s on the tilde key – without the shift though 🙂

Installing Webmin on Ubuntu Server

Webmin is a cool web based interface to manage servers. You can install this on a server using the following commands

You need the following packages first –

$ sudo apt-get install \
perl libnet-ssleay-perl openssl libauthen-pam-perl \
libpam-runtime libio-pty-perl libmd5-perl \
apt-show-versions libapt-pkg-perl

Now download webmin – look for the latest version and replace the URL

$ sudo wget

Install webmin now

$ sudo dpkg -i webmin_1.510-2_all.deb

Update Ubuntu server

Ubuntu server doesn’t have GUI, unless you installed it. So here’s how you update the server using the command line

$ sudo apt-get update

This refreshes the update list and the following actually pulls to cache and installs it. It certainly is a long process – so choose a weekend time to be doing this.

$ sudo apt-get dist-upgrade

Just to be on the safer side, do a restart once everything gets alright.

Using .htaccess to force https

Many occasions you need to push for an https URL (SSL) – here’s how we can do it using .htaccess

RewriteEngine On
RewriteCond %{HTTP_HOST} !^www\.
RewriteRule (.*) https://www.%{HTTP_HOST}/$1 [R=301,L]
RewriteCond %{HTTPS} !on
RewriteRule (.*) https://%{HTTP_HOST}/$1

Please note this also ensures www on the domain at the same time!