Monitoring your DNS records

Sometimes DNS servers just drop hostnames for a variety of reasons. Perhaps a customer forgets about a bill and their DNS records are wiped or there is an edit to a record, it’s not quite right and needs to be reverted and so on.

In an ideal world this would never happen but it does seem to every now and again. In most cases, this can be recovered but it can be a pain and also time consuming.

This is where DNS Historian comes in – an applicaton which monitors and records DNS changes. It also provides an http api to new hostnames can be added and existing ones can be queried.  To find out more, check out: http://dns-historian.mage.me.uk/

Automated wordpress installation in under 30 seconds

Overview

Recently a client expressed his interest in teaching people how to use wordpress. The goal was to give each student in the class their own site so they could work on it throughout the day to make the lesson practical and give the students some hands on experience. It’s also great because the student has something they can take home with them and can continue to work on. I got through setting up 3 before I realized it had to be automated.

wordpress-logo-stacked-rgb

Why not just have each student sign up for a wordpress.com account?

WordPress.com is great, someone just has to sign up and poof, they have a site. The issue is flexibility and one of the major ones is lack of plugins. Currently wordpress.com has a list of plugins and as far as I’m aware you are not able to install any plugin you wish – only the ones they have on their set list. Considering the abundance of great plugins and that most users can benefit from them, it’s a major drawback.

Technical Overview

It’s a bash script that starts at the beginning by creating a user on the server, installs the database, creates relevant directories, downloads wordpress and assigns permissions and all the way to using the cloudflare DNS api to create the records and creating a welcome message. The idea was to just type one command and a site is ready for use like so:

./install.sh test123

which would result in a brand new wordpress site at: test123.dev.vixre.co.uk

It does just that and has saved me quite a lot of time. If you’re inclined to have a look at the code, please feel free to do so.

https://github.com/vixre/automatically_install_wordpress

If you have any questions or would like something changing or enhancing, we are available.

Server details: Debian stable, Apache2, MySQL/MariaDB, PHP, cURL, wget

Overall message

I think the key message is try to take time out and be aware of how you are working and whether it can be improved. If you are familiar with the process and find it is taking up more time than you would like, exploring how to automate the process is definitely something to look in to and something we can help with.

MySQL UK post code table with corresponding longitude and latitude

(Edit March 2016) Please note the freemaptools site now maintains a MySQL version so it would probably be best to use theirs instead.

One of the projects I have been working on recently involved the calculation of the distance between two postcodes. Google provides a decent API and retrieving the distance between two post codes with map routing is very simple. For example to calculate the distance between Fountains Abbey and Northallerton high street using their postcodes would only require:

https://maps.googleapis.com/maps/api/distancematrix/json?origins=HG43DY&destinations=DL78LW&sensor=false&units=imperial

The downside to this is that there are request limits imposed on this API and you can only make a certain amount before you need to hand over some cash to Google. Due to the nature of the project, I realized these limits would quickly be met and as a result found the Google API to be unsuitable.
An alternative would be to find a database with all the UK post codes and their longitude and latitude coordinates. You could then easily lookup the coordinates of a post code and pass them to a routing engine (e.g. Open Source Routing Machine) to calculate distances by road or potentially just a simple ‘as the crow flies’ distance calculation using trigonometry.Open Source Routing Machine Logo

It didn’t take long to find the fantastic freemaptools.com which has a csv containing all UK postcodes and their coordinates. Unfortunately they only had an MSSQL version so I whipped up a MySQL version which worked nicely and is just below for your perusal and use. The same goes for the output the script generates:

Output of the script: mysql_postcodes.zip (35M) Last updated 01/07/2014

The PHP script on github

Readme

PHP code for taking the csv found at http://www.freemaptools.com/download-uk-postcode-lat-lng.htm and importing it into a mysql database. You can use the script in two ways:

  1. Fill in the database details at the start of the file and run the queries as they are built.

    php import.php postcodes.csv

  2. Specify ‘echo’ as the 2nd argument and output the sql to a file.

    php import.php postcodes.csv echo > mysql_postcodes.sql