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.

How to rename a mysql database

james Leave a comment

There isn’t a simple command to rename a mysql database. You just need to create a new database and then rename all the tables like so:

RENAME TABLE old_db.table_name TO new_db.table_name

There is also an alternative method by dumping the old database and importing it again like so:

mysqldump -v oldDatabase > oldDump.sql
mysqladmin create newDatabase
mysql newDatabase < oldDump.sql

I have found the second option to be alot faster both in time and on the disk.

How to install wildcard certificates on multiple servers

james 2 Comments

If you have a wildcard certificate (for example *.randomhacks.co.uk) you might need to install it on multiple servers which host the different subdomains.

Basically, don’t rekey the certificate for each server just copy the same keys and certificate files across all the servers. If you rekey the certificate each time then you will invalidate any certificates which used the old key.

Typically you would need to copy these files:

/etc/ssl/private/wildcard.randomhacks.key
/etc/ssl/private/wildcard.randomhacks.sf_bundle.crt
/etc/certs/wildcard.randomhacks.crt

Note: GoDaddy let you use their wildcard certificates across an unlimited number of servers. However if you are using another certificate provider you might need to check that they don’t have a limited.

How to change /etc/apt/sources.list to use local apt-cache server in a single sed command

james Leave a comment

I’ve just installed an Ubuntu apt-cache server on our local network to speed up downloading packages each time we install or upgrade an Ubuntu computer. However, changing all the /etc/apt/sources.list file for every computer in the office is a real pain. Here is a simple command using sed which does this automatically for you:

The apt-cache server in our office is 192.168.2.2 but obviously change this to suit your needs. Note: the command can be run multiple times and won’t change the result:

 sed -i '/192.168\|^#/! s/http:\/\//http:\/\/192.168.2.2:3142\//g' /etc/apt/sources.list

Hope this helps someone.

How to resize a qcow2 harddrive images

james 13 Comments

I usually make the hard drives on my virtual machines as small as possible to save disk space on the host. I do this because it is reasonably easy to resize them at a later date (especially if you using LVM). Anyhow here is a quick guide to resizing a qcow2 disk image.

1 – Shutdown the guest.. It’s important to shutdown the vm properly rather than pausing it or forcing it to a stop because you need the file system to be a perfect condition before resizing it.

virsh shutdown hostname

2 – Increase the qcow2 disk. It a simple command to increase the size of the disk. Here I am adding 5GB to an Ubuntu Server disk.

qemu-img resize ubuntu-server.qcow2 +5GB

3 – Resize the partition Now that the virtual disk has been resized you need to resize the guest’s partitions within the image. I recommend using a small live Linux distrobution such as GParted Live. First download an iso image of GParted and attach it to the virtual machine by editing the vm settings.

virsh edit hostname

Add a boot order at the top. By default there will be one node that should read:

<boot dev='hd'/>

Simply change this to:

<boot dev='cdrom'/>

Add a cdrom after the hard disk. Make sure to change the /path/to/image.iso to the ISO image you want to boot from. If you don’t set this correctly the VM will not boot.

<disk type='file' device='cdrom'/> 
<source file='/mnt/iso/gparted-live-0.16.1-1-i486.iso'/>
<target dev='hdc' bus='ide'/>
<readonly/>
</disk>

Finally, start the VM and VNC into it. I tend to use Virtual Machine Manager to connect to a VM. It works brilliantly on most Linux machines. You should then be able to use GParted to increase the size of the partition.

resizing_qcow_image_using_gparted

how_to_resize_a_qcow_file_using_gparted

4 – Start the VM backup Note: Unless you really need it – I generally edit the VM and remove the cdrom and boot order by reversing the step described above.

virsh start hostname

How to add a new user to Ubuntu

james Leave a comment

I find adding a new user in Ubuntu really confusing. Surely there should be a wizard program for it? Anyhow, for my records here is how I do it.

Create the user

useradd -d /home/username/ -m username

Set the users password

passwd username

Then I log in as the user

su user

Change the shell to /bin/bash

chsh

You can make the user a sudoer / administrator by using the massively confusing script name called adduser

Ubuntu 10:04

adduser username root

Ubuntu 13.04

adduser username admin

Ubuntu 14.04

adduser username sudo

Ubuntu 16.04

usermod -aG sudo username