How to add a mysql user with REPLICATION SLAVE privileges using Ansible

james 3 Comments

I just wanted to add a user to our database with REPLICATION SLAVE privileges. The Ansible module docs suggest that would do this:

mysql_user: user="replication_user" host="%" password="longpass" priv=*.*:REPLICATION SLAVE

However you get an error messages saying “this module requires key=value arguments”. The error was caused by the space between REPLICATION and SLAVE. The answer is to put the privilege in quotes:

mysql_user: user="replication_user" host="%" password="longpass" priv=*.*:"REPLICATION SLAVE"

Hope this helps someone.

Only running command if file doesn’t exist Ansible

james 2 Comments

I have a simple build script which installs wkhtmltophf on a server. However I only want this script to be run if the software isn’t already installed. Essentially, I want an if statement or check statement in Ansible. I.e. If file doesn’t exist then run this command.

I have read the Ansible documentation multiple times and it isn’t completely clear how to do this. Anyhow, here is the answer:

- name: If file don't exist run command
  command: /root/installer.sh creates=/usr/bin/wkhtmltopdf

This is command say “This command creates this file and so if the file doesn’t exist then run the command”.

I hope this helps someone

How to batch rename files in Ubuntu

james 1 Comment

You can batch rename files in Ubuntu using the “rename” command. It should be installed by default – so no need to apt-get install.

The command is quite simple:

rename s/"SEARCH"/"REPLACE"/g *

For example renaming:

google_marker_green.png
google_marker_blue.png
etc…

To:

map_marker_green.png
map_market_blue.png
etc…

rename s/"google"/"map"/g *

Hope this helps someone.

How to import maxmind ip locations in mysql

james Leave a comment

I’m just building a map of some ip addresses and need to geolocate them. I decided to use the MaxMind database. I found these two brilliant resource without which I could not have imported the databases properly.

There are:

http://www.dbasquare.com/2012/06/01/implementing-efficient-geo-ip-location-system-in-mysql/

http://nickbartlett.com/wordpress/using-maxmind-geo-city-lite-database-on-your-website/

Networking disconnects with libvirt (kvm) and vm still running on Ubuntu

james 1 Comment

We’ve had a problem with some of our KVM Virtual machines where the network connection just disappears. The vm is still running but essentially it’s unreachable over the network (just like if you have pulled out the network cable of real computer). Until, I found the fix, I had to connect to the VM using VNC and then reboot it and everything works for a little while again. This happened on both Ubuntu 12.04 and 13.04 machines which and been completely updated.

I found it really hard to diagnose and searched endless for “kvm libvirt networking problem” or “kvm libvirt networking disappeared” or “kvm libvirt networking failure” etc.

However after trial and error, I found that the virtio virtual network driver was to blame. Changing it to the e1000 virtual network driver solved the problems.

So…

virsh edit vm-name

And change…

<interface type='bridge'>
    <mac address='xx:xx:xx:xx:xx:xx'/>
    <source bridge='br0'/>
    <model type='virtio'/>
    <address type='pci' domain='0x0000' bus='0x00' slot='0x03' function='0x0'/>
</interface>

to

<interface type='bridge'>
    <mac address='xx:xx:xx:xx:xx:xx'/>
    <source bridge='br0'/>
    <model type='e1000'/>
    <address type='pci' domain='0x0000' bus='0x00' slot='0x03' function='0x0'/>
</interface>

Hope this helps someone.

How to block a specific IP Address using UFW

james 3 Comments

The key to blocking a specific IP address with UFW is to make sure that the rule which blocks the ipaddress is applied before any allow rules. Because the firewalls rules are run in order – the block will no come into affect if it appears at the bottom. For example on most webserver you might expect the rules to be:

To                         Action      From
--                         ------      ----
22                         ALLOW       Anywhere
80                         ALLOW       Anywhere
443                        ALLOW       Anywhere
22                         ALLOW       Anywhere (v6)
80                         ALLOW       Anywhere (v6)
443                        ALLOW       Anywhere (v6)

Therefore, to block an IP address the rules would need to setup like this:

To                         Action      From
--                         ------      ----
Anywhere                   DENY        &lt;ip address &gt;
22                         ALLOW       Anywhere
80                         ALLOW       Anywhere
443                        ALLOW       Anywhere
22                         ALLOW       Anywhere (v6)
80                         ALLOW       Anywhere (v6)
443                        ALLOW       Anywhere (v6)

To do this you need to insert the new deny rule at the top using the “insert” option.

sudo ufw insert 1 deny from <ip address>

To remove the block simple:

sudo ufw delete allow from <ip address>

For more information read the community documentation for UFW on Ubuntu.

How to remove / purge / prune old mysql binary logs (safely)

james Leave a comment

I generally setup binary logs on a MySQL server where the data isn’t being replicated and I’m using daily mysql-dumps to backup the data. This allows me to recover the database to a specific point in time. However sometimes the binary logs can become very large if there are lots of changes occuring to the data. Then you can suddenly need to delete / purge / prune the mysql binary log files. Here is how I did it:

1 – Examine the binary logs to decide up to where you want to delete to:

ls -la /var/lib/mysql/

2 – Here the traffic to one server had suddenly increased and the server had been logging 101MB every few minutes for several days.

....
-rw-rw----  1 mysql mysql   104871967 Aug 24 00:01 BINLOG.015687
-rw-rw----  1 mysql mysql   104885618 Aug 24 00:04 BINLOG.015688
-rw-rw----  1 mysql mysql   104866713 Aug 24 00:06 BINLOG.015689
...

3 – You can either prune by date or by file. For simplicity, I pruned by file. You need to do this from the MySQL command line.
I connected to mysql and ran the following command:

PURGE BINARY LOGS TO 'BINLOG.015689';

But you could prune by date:

PURGE BINARY LOGS BEFORE '2013-08-24 00:06:00';

You can read the offical mysql documentation here.

Visual Disk Usage Analyser From the Command Line Ubuntu

james 3 Comments

When you are trying to analyse disk usage there are a limited set of command line tools on Linux. Most people use either of ‘find’ or ‘du’. However using these tools it can be a lot of work to get an overall picture and drill down into directories to find the problems. Enter ncdu (short for ncurses-based disk usage). Simply put ncdu is a fanatastic graphical disk usage analyser.

It could not be simplier to install.

sudo apt-get install ncdu

To use:

ncdu

And you get a lovely screen like the one below. You can then navigate around using the cursor keys.

ncdu-screenshot

Thanks ncdu people 🙂

How completely reconfigure apache2 configuration files

james 1 Comment

I tried upgrading one of our servers from Apache 2.2 -> Apache 2.4. It didn’t work so I purged the PPA but the configurations needed rolling back too (which ppa-purge command didn’t do). So, to completely remove Apache2 configuration files and reinstall them use the following command:

sudo apt-get remove --purge apache2 apache2-utils apache2.2-common

and then had to also reinstall php5:

sudo apt-get install --reinstall apache2-utils apache2 libapache2-mod-php5

Hope this helps someone get out of a pickle.