Select data from table WHERE not matched in another table

The following SQL example allow you to select data, excluding data that is in another table.

You can off course include more selection criteria after the WHERE clause.

Code:   

--  Select data from table WHERE not matched in another table
SELECT data_from_table.*
FROM data_from_table
LEFT JOIN exclude_table
ON data_from_table.email = exclude_table.email
WHERE exclude_table.email IS NULL;

So, data will be selected where the email address does NOT match an email address in table exclude_table.

You can exclude multiple tables...
Code:   

--  Select data from table WHERE not matched in another table
SELECT data_from_table.*
FROM data_from_table
LEFT JOIN exclude_table_1
ON data_from_table.email = exclude_table_1.email
LEFT JOIN exclude_table_2
ON data_from_table.email = exclude_table_2.email
WHERE exclude_table_1.email IS NULL
AND exclude_table_2.email IS NULL;

So, data will be selected where the email address does NOT match an email address in table exclude_table_1 and does NOT match an email address in table exclude_table_2.

For speed, make sure that the field/s you’re comparing in both tables are keys.

MYSQL count email country suffix

This count gives an idea of how much of the email data is from what country.
Code:

SELECT SUBSTRING_INDEX(email, '.', -1) as Suffix, count(email) as Total
FROM members
WHERE (email != '' AND email NOT NULL)
GROUP BY Suffix
ORDER BY Total DESC
LIMIT 100;

You can remove the LIMIT so that you get a full count.

Tunneling Remote Desktop over SSH in Windows 7

This was a difficult one because no matter what I tried, I could not get Remote Desktop to work over SSH (PuTTY) from a Windows 7 system. I just kept getting an error message that a Remote Desktop was already open.

After a lot of Red Herring websites http://blog.spencerkellis.net/2010/06/tunneling-remote-desktop-over-ssh-in-windows-7/ gave the answer.

Windows 7 blocks 3389 and 3390 from being used on the loopback interface. Answer was to use 3391 as the localhost port.

Take a look at that guys excellent instructions for setting up PuTTY SSH on your local system to then tunnel MS Remote Desktop connection via an encrytped SSH connection.

Categories: How-To, Microsoft, OS
Tags: Microsoft, Server, copssh, putty, remote desktop, SH, tunnel, Windows 7

MySQL count of Geographical postcode selections, grouped

Here is an example for a query to give you a count of members/customers/recipients within a postcode2 area, listed (group by) the postcode2 area.

What do I mean? I wanted to know how many club members were in different England postcode areas thus:

Quote:   

POSTCODE2 COUNT
CA 34
CB 12
...
HP 22
..
MK 62

use this

Code:   

-- England count grouped by postcode2
SELECT SUBSTRING(postcode,1,2) AS POSTCODE2, COUNT(*) AS COUNT
FROM members
WHERE (SUBSTRING(postcode,1,2) IN
('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD','BN','CM','CT','GU','HP','LU','ME','MK',
'PO','RG','RH','SG','SL','SO','SS','TN','CB','CO','DE','DN','IP','LE','LN','NG','NR','PE','CV','DY','HR','NN','ST','TF','WR','WS',
'WV','DH','DL','HG','HU','LS','NE','SR','TS','WF','YO','BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA',
'WN','BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]')
OR postcode REGEXP ('^S[1-9]')
OR postcode REGEXP ('^B[1-9]')
OR postcode REGEXP ('^L[1-9]')
OR postcode REGEXP ('^M[1-9]'))
GROUP BY SUBSTRING(postcode,1,2);

   

MYSQL dedupe data

If you want to select data from one table, ensuring that it does not (de-duplicate) match another table, I find the following useful.

Code:   

SELECT * FROM data_2
WHERE data_2.email
NOT IN (
SELECT data_1.email
FROM data_1
WHERE data_1.email=data_2.email
);

   
If you have a flat-file, .csv for example and you want to cleanse of duplicate records, you can upload it to a temporary table, setting the field you want to ignore duplicates from, as a UNIQUE data field.

Code:

CREATE TABLE 'example' (
'id' BIGINT(20) NOT NULL AUTO_INCREMENT,
'email' VARCHAR(255) NOT NULL,
'forename' VARCHAR(30) NULL DEFAULT NULL,
'surname' VARCHAR(30) NULL DEFAULT NULL,
PRIMARY KEY ('id'),
INDEX 'Index 2' ('email'),
UNIQUE INDEX 'Index 3' ('email')
)
COLLATE='utf8_general_ci'
ENGINE='InnoDB'
AUTO_INCREMENT=1;

The above example will check and remove any duplicate email addresses it finds during import of the file, thus de-duping the data.

MYSQL select random records

If you need to select <n> number of sample records at random, you could try the following. Though, it can be resource intensive.

Code:   

SELECT *
FROM members
WHERE (email != '' AND email NOT NULL)
ORDER BY RAND() LIMIT 0,100;

   
This example will select 100 records at random.

MYSQL count unique data

Here I had to select unique (none duplicated) email addresses from a table. You would do this if say, you were mailing club members but there are multiple family members with the same email address but, you only want to send an email to them once. Hey, no one likes their Inbox filling up with the same email!

Code:   

SELECT COUNT(DISTINCT email)
FROM members
WHERE (email != "" OR email IS NOT NULL);

   

MYSQL count email domains

In a previous job where clients supplied their own data, I had to be careful how of their email campaign went to a particular ESP/FQDN in one go.

This count gives an idea of how much of the email data is for what domains.

Code:   

SELECT SUBSTRING_INDEX(email, '@', -1) as Domain, count(email) as Total
FROM members
WHERE (email != '' AND email NOT NULL)
GROUP BY Domain
ORDER BY Total DESC
LIMIT 100;

   

MySQL query for geographical UK postcode selections

First, the basics.
Code:   

SELECT * FROM contacts WHERE postcode = 'MK3 6ZZ';

   
will match any records with a single postcode in Milton Keynes.

Code:   

SELECT * FROM contacts WHERE postcode LIKE 'MK3 %';

   
will match records with the postcode area MK3 in Milton Keynes. Care needs to be taken when using LIKE. Notice the space after the '3'.

Code:   

SELECT * FROM contacts WHERE postcode LIKE 'MK3%';

   
is not the same as the above. Notice that there is no space after the '3'. The would return records, not only in the MK3 postcode area but also, MK31 to MK39 (if they existed).

Code:   

SELECT * FROM contacts WHERE postcode REGEXP ('^E[1-9]');

   
will match any postcodes in the E1 to E9 (London) postcode areas.

As I will show, you can mix these examples to search for more than a single postcode area in one query. Useful if you want the query to return a larger geographical area.

If you want to target members/customers/recipients within a geographical location then here are a few snippets I have gleaned over the years.

Code:   

-- London
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]'));

-- Scotland
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('KW','IV','AB','PH','DD','PA','FK','KY','ML','KA','EH','DG','TD')
OR postcode REGEXP ('^G[1-9]'));

-- England
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN
('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD','BN','CM','CT','GU','HP','LU','ME','MK',
'PO','RG','RH','SG','SL','SO','SS','TN','CB','CO','DE','DN','IP','LE','LN','NG','NR','PE','CV','DY','HR','NN','ST','TF','WR','WS',
'WV','DH','DL','HG','HU','LS','NE','SR','TS','WF','YO','BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA',
'WN','BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]')
OR postcode REGEXP ('^S[1-9]')
OR postcode REGEXP ('^B[1-9]')
OR postcode REGEXP ('^L[1-9]')
OR postcode REGEXP ('^M[1-9]'));

-- Wales
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('LL','SY','SA','LD','SA','CF','NP'));

-- Northern Ireland
SELECT COUNT(*)
FROM members
WHERE postcode LIKE 'BT%';

-- South East
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BN','CM','CT','GU','HP','LU','ME','MK','PO','RG','RH','SG','SL','SO','SS','TN'));

-- East Midlands
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('CB','CO','DE','DN','IP','LE','LN','NG','NR','PE')
OR postcode REGEXP ('^S[1-9]'));

-- West Midlands
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('CV','DY','HR','NN','ST','TF','WR','WS','WV')
OR postcode REGEXP ('^B[1-9]'));

-- North East
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('DH','DL','HG','HU','LS','NE','SR','TS','WF','YO'));

-- North West
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA','WN')
OR postcode REGEXP ('^L[1-9]%'
OR postcode REGEXP ('^M[1-9]%'));

-- South West
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX'));

I do use LIKE for small queries but REGEXP (Regular Expression) is better to avoid any ambiguity. LIKE 'MK1%' would be a mistake because it would select not only MK1 but, MK11, MK12, etc.

Say that you want to target UK members/customers/recipients within a radius of a postcode, how do you work it out? Well, there are websites that can help. Do a search for something like, 'Find UK Postcodes Inside a Radius' and you will find a few. Type in a postcode for the center of the search radio and then the mile/kilometers radius and then you can feed the results into an SQL query.

Example:
Code:   

SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,4) IN ('MK1 ','MK17','MK2 ','MK3 ','MK4 ','MK5 ');

   

HOW-TO: Convert .aac .m4a file to .mp3

You first extract the audio from the .mp4 or .m4a to .wav using faad. Then convert the .wav to .mp3

You need faad2 and lame installed.

Code:   

$ faad foobar.m4a       # to convert to wav
$ lame foobar.wav foobar.mp3          # to convert to mp3

   

HOW-TO: Email notification of SFTP file upload

Following on from my post, 'HOW-TO: Chrooted SFTP only access' I wanted to find a way to get an email notification of when a client had uploaded a file to my work's SFTP area. I had Sendmail already installed.

Each client has their own subdirectory under /home/sftp/ and I wanted to monitor them all and trigger an email when a new file was created.

Ok, I don't know how much use this will be to others but I am just going to throw down what I did to achieve what I wanted. These are personal notes and use them at your peril.

Code:   

sudo apt-get install iwatch

   

installs iWatch, written in Perl and based on inotify, a file change notification system, a kernel feature that allows applications to request the monitoring of a set of files against a list of events.

Code:   

sudo touch /etc/init.d/sftp-iwatch

   

creates an empty file. I edited the file and typed

Code:   

iwatch -r -e create -c "(w;ps -ef)|/home/david/file_notify_email.sh %f '$($(which date))'" /home/sftp >> /dev/null 2>&1 &

   

which basically executes a script /home/david/file_notify_email.sh when a new file is created under /home/sftp/.

/home/david/file_notify_email.sh
Code:   

#!/bin/bash
# 20110723 David Humble

FILE="$1"
CREATED="$2"
DATE=$($(which date))
FROM="aadfiler"
SUBJECT="File upload notification"
EMAIL="technical@xxx.co.uk"
EMAILMESSAGE="/var/upload.txt"

echo "Date: $DATE" > /var/upload.txt
echo "To: $EMAIL" >> /var/upload.txt
echo "From: $FROM">>/var/upload.txt
echo "Subject: $SUBJECT">>/var/upload.txt
echo "New file $FILE created at $CREATED on aadfiler SFTP Server.">>/var/upload.txt
echo ".">>/var/upload.txt

cat /var/upload.txt | /usr/sbin/sendmail -t

   


Code:   

sudo chmod +x /etc/init.d/sftp-iwatch
sudo chmod +x /home/david/file_notify_email.sh

   

makes both files executable.

Code:   

cd /etc/init.d
update-rc.d sftp-iwatch defaults

   

Adds sftp-iwatch to the servers startup processes.

now you can manually start sftp-iwatch by typing
Code:   

sudo /etc/init.d/sftp-iwatch

   


Now whenever a client uploads a file to any subdirectory of /home/sftp/ I get a notification thus:
Quote:   

Date: Wed Aug 3 16:27:02 BST 2011
To: technical@xxx.co.uk
From: aadfiler@xxx.local
Subject: File upload notification

New file /home/sftp/clientsname/upload/AAD013 GE eNewsletter Day 3.csv created at Sat Jul 23 13:36:37 BST 2011 on aadfiler SFTP Server.

References
http://iwatch.sourceforge.net/index.html

Categories: How-To, Linux, OS, Open-SSH, SFTP server, Chrooted SFTP only access
Tags: kde, ubuntu server, Linux, open-ssh, chroot, sftp, File upload email notification, iwatch, notify, dnotify

Copy PuTTY sessions to another computer

Copy PuTTY sessions (conn. profiles) to another computer

 
1. From Run or a command prompt, paste and execute the following:

Code:

regedit /e "%userprofile%\desktop\putty-registry.reg" HKEY_CURRENT_USER\Software\Simontatham

Simon Tatham is the author of PuTTY and thus the registry entry above! Bet you were worried that the above script ran something to mess with your Registry!?

2. This produces a file, putty-registry.reg on your desktop. Copy on to the computer you want the saved sessions added to and double click on it to execute. This adds the PuTTY registry to the computer's registry.

Done!

Thanks to Anton Perez for this. I copied here in case his page ever disappears and it helps my ageing grey-cells!

Batch file to convert RAW to JPEG using dcraw

This is more a note for myself really, so that I can't quickly batch convert my Olympus E-500 .ORF (raw) images quickly to .jpg (JPEG) using dcraw and exiftool.

Using dcraw you can convert from most RAW formats to lesser formats, like JPEG.

Code:   

for i in *.ORF; do newname=$(echo $(basename "$i" ".ORF").jpg); dcraw -c -w -W -v -h "$i" | cjpeg -quality 95 -optimize -progressive > "$newname"; exiftool -overwrite_original -tagsFromFile "$i" "$newname"; done

   

TIP: Installing XenServer an XenServer Tools on Ubuntu 10.04

Excellent XenServer on Ubuntu Server 10.04 How-to at
http://www.jansipke.nl/installing-xenserver-tools-on-ubuntu-10-04

I followed his clear and excellent how-to, to get Ubuntu Server going in paravirtualization mode. I in fact made the boot partition Ext3.

jansipke has a load of XenServer tips on his blog so check them out!

HOW-TO: Chrooted SFTP only access

Updated: 5th December 2012

Scenario:
My place of work wanted me to set up a FTP server. This was so that clients could download and upload sensitive files. Only that specific client and our staff should have access to that client's files. NO other client should see anothers files.

I wanted to avoid FTP because of its weaknesses and searched for a way to achieve a SFTP (Secure File Transfer Protocol) server easily.

The user (username) set up for the client's SFTP should NOT have access to the system in any other way, including SSH.

I did this on a dedicated Virtual Machine running Ubuntu Server AMD64 10.04.2 (2.6.32-32-server) running OpenSSH 5.8 and no other server services. Chrooting should work successfuly on all OpenSSH since release 4.8.

chroot is an operation to lock down the apparent root (highest) directory that the user can get to.

How-to
The following notes sets up a way to chroot a group of users using open-SSH, restricting them to SFTP only.

Whilst others have made good How-tos on the subject, they were not concise enough and I pulled my hair out, meeting errors like, 'fatal: bad ownership or modes for chroot directory' and 'Couldn't get handle: Permission denied'.

This will be done by setting up a group. Any system user who is not in this group and normally allowed SSH access will continue to have SSH access.

If OpenSSH server is not yet installed
Code:  

sudo apt-get install openssh-server

  


EDIT /etc/ssh/sshd_config
Quote:  

change: PermitRootLogin yes
to: PermitRootLogin no

change: LoginGraceTime 120
to: LoginGraceTime 20

add to # Authentication section:
MaxAuthTries 3
MaxSessions 3

append to end of file:
Match group sftponly
# chroot members into this directory
# %u gets sustituted with the username
ChrootDirectory /home/sftp/%u
X11Forwarding no
AllowTcpForwarding no
ForceCommand internal-sftp
Banner /etc/issue.net

Change SFTP listening port
You may wish to change the listening port for SFTP connections to help thrawt attackers.

In /etc/ssh/sshd_config uncomment the Port line and choose a suitable alternative port. Example:
Code:  

Port 6022

  
Do not forget to open this port on your Firewall.

At this point restart sshd. If however, when you create your first user you meet with problems, restart the server before swearing!
Code:  

sudo /etc/init.d/sshd restart

Create sftponly group
Code:  

sudo groupadd sftponly

  


Below is a script to create a user that is added to the sftponly group. Replace <user> with the desired username.
Code:  

sudo mkdir -p /home/sftp/<user>
sudo mkdir -p /home/sftp/<user>/upload
sudo chown root.root /home/sftp/<user>
sudo chown <user>.sftponly /home/sftp/<user>/upload
sudo chmod -R 755 /home/sftp/<user>
sudo useradd <user> -g sftponly -d /home/sftp/<user>/
sudo passwd <user>

That's it!

Explanation
In /etc/ssh/sshd_config we ensured that no one can remotely log in as root. Those with SSH access can still use sudo. We then shortened the time allowed for a successful login and three attempts before disconnect. I also limited concurrent connections to three.

Then I appended to the bottom of the file a Match group for sftponly group. This means that any user login, who is in the group sftponly, has additional rules.

ChrootDirectory restricts the user to a specific directory. The user cannot gain access to a GUI (if exists), port forwarding or bash, only to the internal sftp server application. See http://manpages.ubuntu.com/manpages/intrepid/man5/sshd_config.5.html for more sshd_config commands.

Next, I created a group called sftponly so that any users added to it would pick up the Match group section added to sshd_config file.

That completed the configuration of sshd for sftp only access and finally you create your users, add them to the group sftponly, create directories with the correct permissions.

Directory permissions
The important thing is that chroot insists that ChrootDirectory and its parent directories MUST have root:root 0700 (rwx) user permissions AND NONE for group or other! If you don't do this then you will meet with the error messages I mentioned above.

However, Chroot does not allowed a user to write into their root directory. This is apparently to keep security tight. If you try and succeed to make the user's root directory writeable, you may have opened your system up due to any current security bugs.

It is better to create a sub-directory say, 'upload' and then give the user writeable permissions there. This is what the Chroot team advise.

I have spent a couple of hours trying different SSH and SFTP clients to see if all works as I have described and is secure. It works!

SFTP clients?
I have sucessfully tested SFTP access with the SFTP client, FileZilla (Linux and Win) and WinSCP (Win).

Firewall
Ensure that the SSH ports are opened on your/your providers firewall(s). The standard TCP port is 22.

Security
DenyHosts is a script to help thwart SSH/SFTP server attacks.
http://taurusii.blogspot.co.uk/2012/11/thwart-attacks-to-your-sftp-server-with.html

Email notification
If you want to receive an email notification of when a file is uploaded,
http://taurusii.blogspot.co.uk/2012/11/how-to-email-notification-of-sftp-file.html

PasswordAuthentication?
Common sense says that, if you are going to open a SSH/SFTP port to the World (Internet) then, use the strongest login authentication available.  This means, turn off Password Authentication and use SSH keys instead of passwords and this is the way I set up servers.  But, what if you have to give password SFTP access to a user (Client maybe)?  Well, you can add an exception to your sshd_config file.

Quote:

Match Address 22.231.113.64/32,216.27.61.137/32
# chroot members into this directory
# %u gets sustituted with the username
ChrootDirectory /home/sftp/%u
X11Forwarding no
AllowTcpForwarding no
ForceCommand internal-sftp
Banner /etc/issue.net
PasswordAuthentication yes

The above example, added to the bottom of your sshd_config file will allow the two example IP addresses SFTP access using passwords. For users such as these I choose a random 24 character strong string, a mixture of characters, numbers and symbols.

I find strongpasswordgenerator.com useful.

Logging
By default, SSH/SFTP logging goes to /var/log/auth.log and the following command is useful to check everything is working OK when you set up a new user.

Code:

tail -f -n 300 /var/log/auth.log

Categories: How-To, Linux, OS, Open-SSH, SFTP server, Chrooted SFTP only access
Tags: kde, ubuntu server, Linux, open-ssh, chroot, sftp

Useful radio stream list URLs

Here are some useful sites listing radio streams.

A mixture of feeds to successfully play radio through Linux or M$ Windows.

I typically use the .m3u or .ogg streams on Amarok 1.4 on Kubuntu. There is .asx streams for Windows Media Player and .m3u streams for Winamp.

UK:
http://www.listenlive.eu/uk.html
http://media-ice.musicradio.com/

EU:
http://www.listenlive.eu/

 Here is a list of radio streams that I currently have on my Amarok 1.4

Absolute 80s
http://network.absoluteradio.co.uk/core/audio/mp3/live.pls?service=a8bb

Absolute 90s
http://network.absoluteradio.co.uk/core/audio/mp3/live.pls?service=a9bb

Absolute Classic Rock
MP3 - http://network.absoluteradio.co.uk/core/audio/mp3/live.pls?service=vcbb
OGG - http://network.absoluteradio.co.uk/core/audio/ogg/live.pls?service=vcbb

Absolute Radio
MP3 - http://network.absoluteradio.co.uk/core/audio/mp3/live.pls?service=vrbb
OGG - http://network.absoluteradio.co.uk/core/audio/ogg/live.pls?service=vrbb

BBC Radio 1
http://www.bbc.co.uk/radio/listen/live/r1.asx

BBC Radio 2
http://www.bbc.co.uk/radio/listen/live/r2.asx

BBC Radio 3
http://www.bbc.co.uk/radio/listen/live/r3.asx

BBC Radio 4
http://www.bbc.co.uk/radio/listen/live/r4.asx

BBC Radio 6
http://www.bbc.co.uk/radio/listen/live/r6.asx

Capital 95.8
http://media-ice.musicradio.com/Capital.m3u

Heart Milton Keynes
http://media-ice.musicradio.com/HeartMiltonKeynesV1.m3u

Stop Silent Calls

Excerpt from http://www.moneysavingexpert.com/phones/no-more-junk:

Stopping silent calls

As of Feb 2011, new rules introduced by Ofcom mean companies using automatic diallers can only call once a day if there's no agent to complete the call. This includes all call centres, not just sales teams.

If you're getting silent calls, which can be generated by automatic dialling equipment in call centres, you can register with the Silent Call-gard
( http://www.silentguard.co.uk/ )
service, but you need to renew it every 12 months.

This adds you to a database used by the major telemarketing companies that use predictive diallers therefore reducing the number of silent calls.

If the calls continue, you can complain to Ofcom, here's what to do:

Step 1: After the call dial 1471 and see if you can get the number.
Step 2: If the number is withheld, note the time & date of the call and contact your provider's nuisance call department, eg, BT's Nuisance Call Advice Line ( http://bt.custhelp.com/app/answers/detail/a_id/9084/c/345,351,363 ), Sky Nuisance Calls Advice ( http://www.sky.com/portal/site/skycom/securitycentre/article?contentid=3236210&catlist=3132510 )
Step 3: Complain to Ofcom online ( https://stakeholders.ofcom.org.uk/tell-us/silent-calls ).

Ofcom can fine the company up to £2m (was previously £50K) if it sees fit - the money goes to the Treasury, Ofcom doesn't give compensation.

Convert .eps vector graphic to .pdf for editing in Inkscape

I bought an .eps vector graphic file from iStock and wanted to edit the elements for a company logo. I was going to do this on a Windows 7 system using Inkscape.

1) Install Ghostscript.
2) Install GSView.
3) Run GSView and open the .eps file.
4) Under File -> Convert..., select 'pdfwrite' Device and save with a .pdf extension.
5) Run Inkscape and open the .pdf file.
6) Save as a .svg file, ungroup and edit away!

Sources:
Ghostscript & GSView - http://pages.cs.wisc.edu/~ghost/
Inkscape - http://inkscape.org/

Categories: How-To, Microsoft, Windows, OS, Inkscape
Tags: edit, convert, .eps, EPS, vector, graphic, inkscape

Backing up MySQL database

Below are just notes for my own purpose. Feel free to make use of them.

I am backing up to compressed files, onto a removable encrypted drive. So not worried about backup file password protection. This is a development system so not only do I want to keep daily backups but also the last two hours of hourly backups of a particular database.

p7zip is a port of 7za.exe for POSIX systems like Unix (Linux, Solaris, OpenBSD, FreeBSD, Cygwin, AIX, ...), MacOS X and also for BeOS and Amiga. I find it has good compression rates and I use it on my Ubuntu Servers.
http://www.7-zip.org/ or http://p7zip.sourceforge.net/

Although quite simular, I'll post both the hourly and daily backup scripts here for completeness. I save them to /root/bin/ and make them only read, write and executable (chown 600) by Root.

Code:   

sudo -s
mkdir /root/bin
cd /root/bin

/root/bin/cron_hourly_database_backup.sh:
Code:   

#!/bin/sh
# /root/bin/cron_hourly_database_backup.sh

if [ -f /tmp/database_backup_lockfile ]
then
    exit 0
else
    touch /tmp/database_backup_lockfile
fi

date=$(date +"%Y%m%d%H%M")
filename="database_hourly_backup-"

#    logger "$date: Cron ran /usr/local/bin/cron_hourly_database_backup.sh"
    mysqldump -uroot -pmypassword --opt database1 | p7zip > /var/backups/$filename$date.sql.7z
# find hourly backup files over 2 hours old and delete
    find /var/backups/$filename* -type f -amin +121 -exec rm {} \;

rm -f /tmp/database_backup_lockfile

The above script creates a lockfile. Whilst this exists I don't want any other backups ran.

mysqldump is then run with p7zip compressing the resultant file, on the fly. Note that my script may not be suitable for all situations : mysqldump needs to acquire locks to create a consistent backup, and this may raise issues on highly loaded servers.

Once backed up, the find command locates specific backup files that are over two hours old and deletes them (rm). Finally, the lockfile is deleted.

cron_daily_database_backup.sh:
Code:   

#!/bin/sh
# /root/bin/cron_daily_database_backup.sh
if [ -f /tmp/database_backup_lockfile ]
then
    exit 0
else
    touch /tmp/database_backup_lockfile
fi

date=$(date +"%Y%m%d%H%M")
filename1="database1_daily_backup-"
filename2="mysql_daily_backup-"

#    logger "$date: Cron ran /usr/local/bin/cron_daily_database_backup.sh"
    mysqldump -uroot -pmypassword --opt database1 | p7zip > /var/backups/$filename1$date.sql.7z
    mysqldump -uroot -pmypassword --opt mysql | p7zip > /var/backups/$filename2$date.sql.7z
# find daily backup files over 7 days old and delete
    find /var/backups/$filename1* -type f -atime +7 -exec rm {} \
    find /var/backups/$filename2* -type f -atime +7 -exec rm {} \

rm -f /tmp/database_backup_lockfile

Code:   

chmod 600 /root/bin/cron_hourly_database_backup.sh
chmod 600 /root/bin/cron_daily_database_backup.sh

Code:   

sudo -s
crontab -e

Crontab:
Code:   

## root crontab  
## m h  dom mon dow   command
0 01-23 * * * /root/bin/cron_hourly_database_backup.sh >> /var/backups/cron_hourly_database_backup.log 2>&1
0 0 * * * /root/bin/cron_daily_database_backup.sh >> /var/backups/cron_daily_database_backup.log 2>&1

The crontab shows that the hourly backup is ran between 01:00 to 23:00 hours. The daily backup is the only one to run at midnight (00:00 hour).

Notes:
You can dump more than one database to a file by altering one line in the script.

Example:
Code:   

mysqldump -uroot -pmypassword --opt database1,database2,database3 | p7zip > /var/backups/$filename$date.sql.7z

or

Code:   

mysqldump -uroot -pmypassword --opt --all-databases | p7zip > /var/backups/$filename$date.sql.7z

* The above has not documented how I then copy all the backups to the encrypted, swappable drives.


Restoring database

Regularly test your backup by restoring to a dummy database. If you do need to restore the actual database then drop the old one first.

Code:   

mysql -uroot -pmypassword -e "DROP DATABASE [databasename];"

Next, create the database.

Code:   

mysql -uroot -pmypassword -e "CREATE DATABASE [databasename];"

or combine the commands...

Code:   

mysql -uroot -pmypassword -e "DROP DATABASE [databasename]; CREATE DATABASE [databasename];"

Then restore backup...
Code:   

p7zip -d < /var/backups/[filename].sql.7z | mysql -u mysqlusername -pmypassword [databasename]

   
... replacing [filename] with the correct filename and replacing [databasename] with the correct database name.


MySQL-Maint

If you don't want to 'roll your own' custom scripts for MYSQL database backups and maintenance then check out MySQL-Maint.

A bash script that performs backups and maintenance on your MySQL servers.
https://github.com/mmerian/MySQL-Maint


Categories: How-To, MySQL
Tags: MySQL, database, administration, backup, backups, maintenance, optimization, optimisation

FIX: warning: setlocale: LC_CTYPE: cannot change local

After installing a Ubuntu 10.04 LTS Server I was getting an error message constantly come up whilst trying to do things in the bash,

"warning: setlocale: LC_CTYPE: cannot change locale".

Solution was:
Code:   

sudo locale-gen en_GB
sudo locale-gen en_GB.utf8

   


and reboot server,
Code:   

shutdown -r now

   

New toner cartridge for my Laser Printer or refill?

I was keen and slightly excited to see a flyer come through my letterbox, for a cartridge refilling franchise just opened in my town. Not only for inkjets but also for laser printer toner cartridges.

Now I know that predominantly, their business is inkjet cartridges but I was shocked by a price of 51ukp + VAT for refilling my colour cartridge with compatible toner. When, a replacement high capacity compatible colour toner cartridge can be bought from a reputable supplier for 42ukp (inc. VAT)!

Moral - Shop around for prices and if you want to help the environment then check your manufacturers website. Some now have FREEPOST addresses to send back old cartridges for recycling.

Dell include FREEPOST postage return labels with their toner cartridges.

Konica Minolta Clean Planet Recycling (Europe)
http://cleanplanet.konicaminolta.eu/