How to stop Mysql ASCII tables / column separators from being lost when redirecting bash output

Today I needed to write a quick bash script to send a monthly report  to a colleague.  The report required running a few MySQL queries and then concatenating the output into a file,  and e-mailing it to them.  Normally when you run a MySQL query from the command line, the output is shown within a handy ASCII table.

# mysql --table -e "SELECT 1+1";
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

Unfortunately,  it seems that when you redirect the output to a file,  the ASCII formatting is lost…

# mysql -e "SELECT 1+1" > /tmp/test
# cat /tmp/test
1+1
2

It took me a while to find it, but the solution is really simple. Simply add the –table parameter to the MySQL command:

# mysql --table -e "SELECT 1+1" > /tmp/test
# cat /tmp/test
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

And that’s it! Hopefully this post will save someone else some time in the future.

Setting up bridged networking for libvirt on CentOS / RedHat

By default, libvirt is setup to use NAT based networking for any guests created, which keeps them isolated from the rest of the physical network in the sense that they can only connect outbound, and inbound connections from other machines on the physical network would fail (other guests on the same hypervisor in the same virtual network can connect). The hypervisor server acts as a router, and each guest is given it’s own IP addresses in the 192.168.122.* range from libvirt’s built-in DHCP server.

If you would like your guests to be part of your main network, so they get an IP address from your main DHCP server, then you need to set up bridged networking. With bridged networking enabled, all the guests behave as if they are connected directly into the main network without any firewall or router in between.

First make a backup of your existing eth0 config – just in case!

cp /etc/sysconfig/network-scripts/ifcfg-eth0 /root/backup-ifcfg-eth0

Then run this small script to update the eth0 config to make it part of the bridge. This script looks for the MAC address in your existing eth0 config, and then writes out a new one using this MAC. NB: This script assumes you were using DHCP on eth0, and there were no VLANS involved. If you have a more complex network, then you will need to write your own custom config – the key point is to add BRIDGE=br0.

eth0_mac=`grep HWADDR /etc/sysconfig/network-scripts/ifcfg-eth0 | grep -i -o '[0-9A-F]\{2\}\(:[0-9A-F]\{2\}\)\{5\}'`
cat > /etc/sysconfig/network-scripts/ifcfg-eth0 <

Now create the bridge config...

cat > /etc/sysconfig/network-scripts/ifcfg-br0 <

Then restart the network...

service network restart

If all goes well you should still have network access, and the new bridge should show up in the output of brctl show:

[root@centos-latest-gpt-basic ~]# brctl show
bridge name	bridge id		STP enabled	interfaces
br0		8000.009c02a46912	no		eth0
virbr0		8000.5254007ac74f	yes		virbr0-nic

To get your virtual machines to use this bridge, you would need something like the following in your guest xml file:

 
    
    
    
 

Setting up a PXE boot server on Synology DSM 4.2 beta

I was excited to see that Synology have recently integrated a PXE solution in their latest version of Diskstation Manager – DSM 4.2 beta. This makes their NAS devices even more ideal in a home virtualisation lab as they are both cheap to buy and to run (the DS212 unit that I own consumes less than 20W in use), but also easy to configure and they offer a wide range of storage and network services such as CIFS / AFP / NFS / iSCSI, LDAP, PXE, TFTP, VPN, DNS.

They also offer more powerful Enterprise versions of their NAS devices, which run the same operating system but with much faster hardware. I’ve yet to test them in a production environment, but given my experience in the lab, I am sure they would be a competitive solution.

In this post I will show you how to set up a PXE boot server that will let you perform a network installation of Centos 6.3 using your Synology NAS.

What is PXE?

PXE (pronounced pixie) stands for Preboot eXecution Environment. It’s a technology that can be used to boot a computer into an operating system from it’s network card without needing anything to be installed on the computer’s local storage devices in advance. Most modern servers come with PXE support as standard.

It’s incredibly useful if you wish to automate the deployment of many servers without having to attend each one with an installation CD / DVD / USB stick. With a little work, you can also configure custom kickstart files to be served to each server, to save having to enter all the installation options manually.

How to set up your Synology NAS as a PXE boot server

Step 1 – Install DSM 4.2

Upgrade your Synology device to DSM 4.2 beta if you haven’t already. Follow the download links for your region, download the appropriate firmware that for your model of device, then upload it in the DSM admin panel – control panel – DSM update screen.

Step 2 – Set up the DHCP Service on your NAS

I would recommend you set up the DHCP server on your Synology first and test it works. If you are running this on your main LAN, you will need to disable the DHCP server on your router so they don’t conflict. You can download the DHCP server package in Package Center.

You will need to configure the relevant primary and secondary DNS, start and end IP addresses, netmask and gateway settings.

Synology DSM DHCP Settings

Once you are happy this is working, you can move on to configure the TFTP and PXE servers.

Step 3 – Set up the TFTP and PXE Services.

Tick the Enable TFTP service box. You also need to specify a folder somewhere on your NAS that can be used as the TFTP root folder.

Tick the Enable PXE service box. In the boot loader box type ‘pxelinux.0′. Fill out the remaining fields using the same settings you used for DHCP in step 2. This will override the DHCP service settings.

Synology DSM TFTP & PXE Server

 

This will set up a DHCP service which sets DHCP 67 (boot filename) in it’s DHCP offers to be PXELINUX.0. If the server making the DHCP request is performing a PXE boot, it will attempt to retrieve and load this file via TFTP from the DHCP server IP address. It is possible to tell the server to use a different server for TFTP using DHCP option 66 – but this is not necessary in our case because the Synology NAS is performing both functions.

Step 4 – Upload the PXELINUX scripts and PXE menu to your tftp folder.

In order to get PXE boot working, we now need to upload the PXELINUX.0 and a few associated files from the SYSLINUX project to the TFTP share. I’m sure you could use other boot loaders, but I have never tried any, so I’m going to stick to what I know!

According to the Centos wiki, the minimum required files to perform a PXE network installation of Centos 6.3 are:

  • pxelinux.0
  • menu.c32
  • memdisk
  • mboot.c32
  • chain.c32
  • pxelinux.cfg/default
  • path/to/your_kernel_of_choice
  • path/to/your_init_ramdisk_of_choice

You could download these yourself and edit pxelinux.cfg/default as necessary, but this is out of the scope of this blog, so to speed things up I have created a Github repository with all the files necessary for a Centos 6.3 install.

Simply download this repository as a ZIP file and copy the files inside your tftp folder.

This perfoms a network install using a kickstart I’ve created which will set up Centos 6.3 with a few KVM packages for use as a hypervisor. NB: The default password is changeme1122

Step 5 – Attempt to PXE boot a server.

All you need now is a server. Ensure the server is connected to the LAN with your Synology NAS on it, then power on the server and instruct it to perform a network boot. It should make a DHCP request to the NAS, and then perform a PXE boot using the files that we copied to the TFTP server.

If you want to load a different operating system, you need to copy across the relevant kernels / initial ramdisks for the distribution of your choice and then edit the PXE menu in pxelinux.cfg/default. You may also wish to either remove the kickstart parameter, or refer to a different kickstart of your own creation.

 

 

Setting up SSH authorized_keys with SELinux enabled

If you have ever added your SSH key to an authorised_keys file on server running SELinux, but for some reason you still can’t connect with your key, then it may be because the SELinux contexts have not been correctly set on the .ssh folder and authorized keys file. This normally causes the following error on your ssh client:

Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).

And you may see an error in the audit log (/var/log/audit/audit.log) on the server..

type=AVC msg=audit(1358012203.073:43414): avc: denied { read } for pid=5945 comm=”sshd” name=”authorized_keys” dev=dm-1 ino=25583 scontext=system_u:system_r:sshd_t:s0-s0:c0.c1023 tcontext=unconfined_u:object_r:admin_home_t:s0 tclass=file

The way to fix this is to run…

restorecon -R -v /root/.ssh

… substituting /root/ if necessary for the relevant home dir.

How to setup SSH public key authentication with SELinux enabled

The full steps to setup an authorized keys file from scratch would therefore be:

1) Create the .ssh folder

mkdir -p /root/.ssh
chmod 755 /root/.ssh/

2) Set up the authorized_keys file (remember to paste in the relevant key in vim)

vim /root/.ssh/authorized_keys
chmod 600 /root/.ssh/authorized_keys

3) Fix the SELinux file contexts

 restorecon -R -v /root/.ssh

 

Creating a bootable USB stick from OSX

I was having some trouble getting my ML110 Proliant lab server to boot from a USB drive that I had created with UnetBootin on my Mac. Initially, I thought it was a problem with the ML110 server, but it turns out that Unetbootin does not currently make a fully bootable USB stick in OSX. The trick is that you have to set up the master boot record correctly yourself using DiskUtil, fdisk and an MBR file from the SysLinux project. In this tutorial I will show you how.

If you would like to create a bootable USB drive from OSX, you will need:

Step 1 – Format the disk in Disk Utility, with the correct MBR

Assuming you have already inserted your USB drive into your Mac…

a) Open Disk Utilty

b) Select the USB device

c) Click Partition

d) Select 1 partition in the partition layout

e) Select “Master Boot Record” in the options

f) Select MS-DOS (FAT) in the format type.

g) Click Apply, then Partition

h) Close Disk Utility

This will wipe the USB disk and set it up with the correct boot record.

Step 2  – Install the MBR binary from the SysLinux project

Open up a terminal and then

a) Use the command line diskutil to find the device name for your USB drive.

diskutil list

a) Umount the USB drive with the command line. NB: Be sure to swap the device reference (in my case it is /dev/SOMEdisk2) with the correct one for your usb key that you identified in the previous step – this will change for each machine.

diskutil unmountDisk /dev/SOMEdisk2

b) Mark the partition active, then unmount it again

sudo fdisk -e /dev/SOMEdisk2
print
f 1
write
print
exit
diskutil unmountDisk /dev/SOMEdisk2

c) Download Syslinux

mkdir -p ~/Documents/BootableUSB
cd ~/Documents/BootableUSB
curl -L -O http://www.kernel.org/pub/linux/utils/boot/syslinux/syslinux-5.00.zip
unzip syslinux-5.00.zip -d syslinux-5.00
cd syslinux-5.00/mbr

d) Install the MBR – NB: Update the device name (/dev/SOMEdisk2) to the one you identified in the first step!!!

sudo dd conv=notrunc bs=440 count=1 if=mbr.bin of=/dev/SOMEdisk2

Step 3 – Use UnetBootin to install your OS install files

a) Download and install UnetBootin if you haven’t already from http://unetbootin.sourceforge.net

b) Load the application, choose your preferred distribution, and then click OK.

c) When it’s finished, eject the usb key and use it!

References

Thanks to a tip I found on http://perpetual-notion.blogspot.co.uk/2011/08/unetbootin-on-mac-os-x.html

Showing total disk use on Linux – a.k.a How to sum the output of df

If you want to find the total amount of disk space used on Linux, and other Unix based systems (such as OSX), you can do so quite easily with the following one liner…

df -lP | awk '{total+=$3} END {printf "%d G\n", total/2^20 + 0.5}'

What this does is…

  • df -lP … shows a disk report of all local disks, in posix format (e.g. one line per volume)
  • | awk ‘{total+=$3} END {printf “%d G\n”, total/2^20 + 0.5}’ … this takes the output of the df command, pipes it to awk which then sums the 3rd columns into a variable called total, and when it’s finished it prints out this number converted to Gigabytes.  To get to Gigabytes, we divided by 2^20 (1024*1024), and we also add 0.5 so that it is effectively rounded to the nearest whole number.

This is particularly helpful if you have a lot of volumes on a system.

Fixing keyboard problems with OSX and virt-manager

If, like me, you have been running virt-manager over an SSH tunnel, and are getting problems with the keyboard / key mappings, then you may be able to fix them by changing the keyboard settings in the virtual machines configuration.

1) Click the details button in the virtual machines virt-manager window.

2) Navigate to the Display VNC option in the left hand menu.

3) You should now see a Keymap drop down box. Select the correct Keymap that matches your keyboard, in my case it was en-gb.

4) Click Apply.

Good luck, it worked for me!

 

Installing Ansible on OSX Lion

We’re currently evaluating configuration management (devops) tools at CATN to help us deploy and manage our new vCluster hosting platform in a production environment. Many of the tools we have looked at are fairly complicated, with a steep learning curve.

Then we came across, Ansible. It’s developed primarily by the guy who wrote Cobbler (our previous favourite deployment tool), and it’s been built from scratch to be much simpler to use.

Although it only took me just a few minutes to get it up and running, there were a couple of dependencies required to install it on OSX, so I thought I would document the steps here in case it helps someone else in the future:

Step 1 – Install Dependencies

Check that you have Xcode installed with , try typing gcc -v from the command line. If it says command not found, then ensure you have downloaded Xcode from App Store, then once it’s installed go to Xcode > Preferences > Downloads  and install the Command Line Tools.

Install pre-requisite Python modules on your main OSX machine that will be running ansible.

sudo easy_install jinja2
sudo easy_install PyYAML
sudo easy_install paramiko

If you get an error saying configure: error: no acceptable C compiler found in $PATH then you should double check you have Xcode and it’s command line tools as per the instructions above.

And on the managed nodes, assuming it’s a Centos/SL/RedHat node:

yum install python-simplejson

Step 2 – Checkout latest version of Ansible

Download the latest version from Github. Assuming you are installing into ~/github

cd ~/github
git clone git://github.com/ansible/ansible.git
cd ./ansible
source ./hacking/env-setup

Set up your bash profile to load the environment variables when you load a terminal

# Setup Ansible
cd ~/github/ansible
source ./hacking/env-setup
cd ~
export ANSIBLE_HOSTS=~/ansible_hosts
# End Ansible Setup

If you wanted to install it properly, rather than run the latest code from checkout you could do so with sudo make install. In this case you wouldn’t need to run the hacking/env-setup script to modify the environment variables, since ansible would be located within the ordinary search paths.

Step 3 – Test it works

Add some hosts to your ansible_hosts file, and then ping them.

echo "192.168.0.62" >> ~/ansible_hosts
ansible all -m ping

How to bulk add an ssh key to multiple servers

If you need to add a colleague’s ssh key to multiple servers that share a similar name, you can do it with a simple one liner. In this post I will show you how.

Assuming the servers you need to add they key to are called:

server1.example.com
server2.example.com
server3.example.com
server4.example.com
etc…

And your colleagues key is in a file called key.pub

To add to a list of consecutive servers, use:

for i in {1..10}; do ssh-copy-id -i key.pub root@server$i.example.com; done

If you wanted to list specific servers, you could use:

for i in {1,4,7,10}; do ssh-copy-id -i key.pub root@server$i.example.com; done

You can then check it worked with the following: (replace colleagues-key-comment with the comment that identifies their public key):

for i in {1..14}; do echo server$i; ssh root@server$i.example.com cat /root/.ssh/authorized_keys | grep colleagues-key-comment; done

Economics, Business, Telecoms, Tech and Gadgets