Click Technology

Linux, Windows, Mac it's all good

Output a MySQL result set to a .csv file

October15

This is quite sweet. You need to get some table data straight out to a CSV for manipulating in OpenOffice Calc, or similar products. But how?

Connect to your SQL server using a command line, ususally

mysql -u root -p

enter your password. Show your databases using

SHOW DATABASES;

Switch to your database using

USE my_database_name;

Now show your tables if you like..

SHOW TABLES;

Now get your CSV out from the table results of a query thus..

SELECT Name, Surname, Address, Postcode, Telephone
FROM Customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Et voila, data dumped to /tmp/customers.csv! Nice.

 

Perhaps my favourite XKCD cartoon.

Perhaps my favourite XKCD cartoon from xkcd.com

OK, funny / cool but how do I get .CSV file to my PC?

Understood.  I use Linux Mint, but in any Debian, Ubuntu, Mint system, open your file manager (or Thunar in Mint-speak) and put

sftp://your_username@your.server.name.com/tmp/

Enter any password required for the target server and voilà, you’re on the /tmp directory on the target server!  From there, you can copy / paste the file as you wish,

posted under Linux Tips | No Comments »

Extract data from a photo

October3

All pictures taken with digital cameras add information to images they take. A lot of information. To see it, install imagemagick using this command on the command line..

sudo apt-get install imagemagick

Then, just use the identify command thus..

identify -verbose /home/me/pics/some_picture.jpg

and it should pump out a heap of data.

Personally, if ever uploading photos to the internet (never) or emailing them to friends (rarely), I make sure to strip all the data from them. For this, use mogrify, also from the imagemagick suite…

mogrify -strip /home/me/pics/some_picture.jpg

Now the exif data has been removed. Nice!

 

True dat..

posted under Linux Tips | No Comments »

Start a VirtualBox server in headless mode

October2

Sometimes (in fact, why not every time?), you will want to start a server on your machine but not have it display a console. In other words, start it ‘headless’.

First, list all your machines. From the command prompt…

VBoxManage list vms

You get a list of all the machine images thus..

"Windows XP" {f0e63449-61bc-40e1-a82f-5a9bc6fb2434}
"Debian Basic" {b596b383-04e7-4895-bfaf-94a206f66960}
"Homestead_default_1407795525403_97441" {7abc2b40-5f57-4a6f-a647-8583c8790a62}
"UDA" {68999d0c-6391-4792-b13a-c120649ab240}
"PXE Boot" {fe1480eb-68d1-493a-9a90-6c36f819410e}
"Tiny Linux" {44ed7a1d-4e75-4d21-9bdd-a23699603af4}
"Turnkey Linux" {a2194ccb-c88d-48b9-aa6f-12b0805500c9}

So, to boot the Turnkey Linux box, just enter..

VBoxManage startvm "Turnkey Linux" --type headless

You should get

VBoxManage startvm "Turnkey Linux" --type headless
Waiting for VM "Turnkey Linux" to power on...
VM "Turnkey Linux" has been successfully started.

Now, just ssh straight onto the machine

ssh root@192.168.10.123

Obviously, you need to know the ip address of the machine in advance or see your DHCP log to see what address it picked up. Otherwise, you’re ready to log in.

To shut it down again…

VBoxManage controlvm "Turnkey Linux" poweroff

Sweet!

Check out the well-written and compact documentation for VirtualBox here.

posted under Linux Tips | No Comments »

Mount a CD in Linux

September18

This works for any version of Linux as it’s a universal command and is very simple.

First, su as root

$sudo su

Now create the directory where you want to see the CD/DVD files..

# mkdir -p /media/mycd

Now use the mount command to mount the CD

# mount -o loop /path/to/my/iso/my_favourite.iso /media/mycd

The files are now visible in /media/mycd.

Done.

posted under Linux Tips | No Comments »

Create an iso image from a CD..

September18

Two steps…

isoinfo -d -i /dev/cdrom | grep -i -E 'block size|volume size'

You get this output…

Logical block size is: 2048
Volume size is: 1620057

Using this information, we insert the numbers into the dd command..

dd if=/dev/cdrom of=/home/user/iso_files/test.iso bs=2048 count=1620057

And that’s it. Happiness!

posted under Linux Tips | No Comments »

Backing up and restoring hard disks & partitions

September15

Here’s a slightly longer article than ususal on the subject of hard disks and partitions. This is always a subject that is occasionally tricky. Here’s the nutshell of how to find your hard disk name, the relevant partition and how to back it up and restore it.

Listing the disks and partitions

To list the hard disk devices on your system use..

sudo fdisk -l

You should get an output something similar to this…

Disk /dev/sda: 128.0 GB, 128035676160 bytes
255 Köpfe, 63 Sektoren/Spur, 15566 Zylinder, zusammen 250069680 Sektoren
Einheiten = Sektoren von 1 × 512 = 512 Bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Festplattenidentifikation: 0x00055cbf

   Gerät  boot.     Anfang        Ende     Blöcke   Id  System
/dev/sda1   *        2048      495615      246784   83  Linux
/dev/sda2          497662   250068991   124785665    5  Erweiterte
/dev/sda5          497664    23932927    11717632   83  Linux
/dev/sda6        23934976    27838463     1951744   82  Linux Swap / Solaris
/dev/sda7        27840512   250068991   111114240   83  Linux

Disk /dev/mapper/sda5_crypt: 12.0 GB, 11996758016 bytes
255 Köpfe, 63 Sektoren/Spur, 1458 Zylinder, zusammen 23431168 Sektoren
Einheiten = Sektoren von 1 × 512 = 512 Bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Festplattenidentifikation: 0x00000000

Festplatte /dev/mapper/sda5_crypt enthält keine gültige Partitionstabelle

Platte /dev/mapper/cryptoswap: 1998 MByte, 1998585856 Byte
255 Köpfe, 63 Sektoren/Spur, 242 Zylinder, zusammen 3903488 Sektoren
Einheiten = Sektoren von 1 × 512 = 512 Bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Festplattenidentifikation: 0x67218b8c

Festplatte /dev/mapper/cryptoswap enthält keine gültige Partitionstabelle

Disk /dev/sdb: 32.0 GB, 32019316736 bytes
255 Köpfe, 63 Sektoren/Spur, 3892 Zylinder, zusammen 62537728 Sektoren
Einheiten = Sektoren von 1 × 512 = 512 Bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Festplattenidentifikation: 0xc3072e18

   Gerät  boot.     Anfang        Ende     Blöcke   Id  System
/dev/sdb1              32    62537727    31268848    c  W95 FAT32 (LBA)

As you can see, the first disk name is /dev/sda and this disk is 128GB in size. Listed further down are the details of how the disk, /dev/sda, is sliced up, namely the five partitions sda1, sda2, sda5, sda6 and sda7.

There’s also another disk, /dev/sdb which is 32GB. It has one partition, /dev/sdb1.

From this we can see that the disk is alsways called something like /dev/sda, /dev/sdb, /dev/sdc and so on.  The partitions are always numbered sub items, so /dev/sdb1, /dev/sdb2, /dev/sdb3, /dev/sdb4 and so on as needed.

Back up the paritions or disks..

We back up the partition or disk using the dd command. Let’s backup the boot partition, /dev/sda1, on the disk /dev/sda to the partition /dev/sdb1 on the disk /dev/sdb.  We know /dev/sda1 is the boot partition because it’s marked with an asterisk (*).

So, device /dev/sdb is a USB memory stick. To backup the boot parition, /dev/sda1, to the USB drive, /dev/sdb, let’s mount the USB drive first using the following commands.

First, make a target folder for the mount..

sudo mkdir -p /media/any/path/you/like

Now, we mount the partition /dev/sdb1 to the target location /media/any/path/you/like so that when we change directory to /media/any/path/you/like, the contents of the USB disk are visible.

For this, we use the mount command. The mount command syntax is effectively mount which_device where. We’ll use the following mount command.

sudo mount -t vfat -o rw,users /dev/sdb1 /media/any/path/you/like

The command above states that the filesystem of the USB disk is vfat and is read/write-able and can be dismounted by users.

Ready to backup

Now the disk is mounted, we can dd our data straight to it.

sudo dd if=/dev/sda1 of=/media/any/path/you/like/the_sda1_partition_backup

Once running, nothing appears to happen. Remain calm and wait. Losen any tight clothing if necessary and wait. Backup is happening. When it ends, you’ll get something like this…

493568+0 Datensätze ein
493568+0 Datensätze aus
252706816 Bytes (253 MB) kopiert, 6,09949 s, 41,4 MB/s

For style points, you can also backup the whole disk to another hard disk of the same size and dimensions..

sudo dd if=/dev/sda of=/dev/sdb

Or even back up a partition or disk to a compressed file to save space.

sudo dd if=/dev/sda1 | gzip > /media/any/path/you/like/the_sda1_partition_backup.gz

You will probably need to sudo su before running the command above, so first,

sudo su

and then use the dd command above.

That’s it. You’re a pro. Drinks all round.

posted under Linux Tips | No Comments »
« Older Entries

I used to run Click Technology from 1995 to 2010.  Now no longer, I maintain the site with things I find interesting or are noteworthy or have some handy-tip value.