Lubos Rendek

The only solution is determination.

Import Data From CSV File to MySQL With Bash Script

Here is what you need to this simple demonstration on how to import data from CSV file to MySQL database. Fist we need a comma separated value file like:

1
2
3
4
5
6
7
8
9
10
11
$ cat input.csv 
54,68,74
54,67,77
54,67,78
54,67,76
54,67,79
54,67,74
54,67,75
54,67,68
54,67,63
54,65,63

Next, you need MySQL database with at least one table. Our database is called “mydata” and contains a single table named “cost”:

1
2
3
4
5
6
7
8
9
10
mysql> describe cost;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| column1 | int(11) | NO   |     | NULL    |                |
| column2 | int(11) | NO   |     | NULL    |                |
| column3 | int(11) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Lastly, we need a bash script to import our comma separated CSV file into our table. The file can look like the one below:

1
2
3
4
5
6
7
8
9
$ cat import-SQL.sh 
#!/bin/bash

IFS=,
while read column1 column2 column3
      do
        echo "INSERT INTO cost (column1,column2,column3) VALUES ('$column1', '$column2', '$column3');"

done < input.csv | mysql -u myusername -p mypassword mydata;

Most of the stuff above is self explanatory except IFS variable. IFS or Internal field separator is a bash buildin bash variable which is by default to set to contain space like “ ”. It is used to read input and since now our input is comma separated we set IFS to “,”. Ok, enough talk, let’s run the script:

1
$ ./import-SQL.sh

No ouptut, good output. Let’s check our database:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from cost;
+----+---------+---------+---------+
| id | column1 | column2 | column3 |
+----+---------+---------+---------+
|  1 |      54 |      68 |      74 |
|  2 |      54 |      67 |      77 |
|  3 |      54 |      67 |      78 |
|  4 |      54 |      67 |      76 |
|  5 |      54 |      67 |      79 |
|  6 |      54 |      67 |      74 |
|  7 |      54 |      67 |      75 |
|  8 |      54 |      67 |      68 |
|  9 |      54 |      67 |      63 |
| 10 |      54 |      65 |      63 |
+----+---------+---------+---------+
10 rows in set (0.00 sec)

Get Secure and Anonymous Browsing With Tor, Privoxy and Debian

Here is a quick tutorial on how to setup secure and anonymous browsing with tor, privoxy and Debian linux. First install tor and privoxy:

1
# apt-get install tor privoxy

Check a tor log file and see whether it started correctly. You should see something like:

1
2
3
4
5
6
7
cat /var/log/tor/log
Apr 01 21:46:32.000 [notice] We now have enough directory information to build circuits.
Apr 01 21:46:32.000 [notice] Bootstrapped 80%: Connecting to the Tor network.
Apr 01 21:46:32.000 [notice] Bootstrapped 85%: Finishing handshake with first hop.
Apr 01 21:46:34.000 [notice] Bootstrapped 90%: Establishing a Tor circuit.
Apr 01 21:46:36.000 [notice] Tor has successfully opened a circuit. Looks like client functionality is working.
Apr 01 21:46:36.000 [notice] Bootstrapped 100%: Done.

You can also check privoxy logs for any suspicious errors /var/log/privoxy/logfile. Furthermore, confirm that privoxy is listening on port 8118 and tor on port 9050.

Why do we need both privoxy and tor? Both are standalone services. Privoxy is a proxy server with additional security features such as content filtering and advertisement blocker. Tor on the other hand will provide you with anonymity using onion routing techniques.

At this stage what we need to do is to make any requests from privoxy forwarded to tor on socks5 9050. To do that we need to uncomment a:

1
forward-socks5   /               127.0.0.1:9050 .

in /etc/privoxy/config the privoxi config file and restart privoxy:

1
# /etc/init.d/privoxy restart

Now, we are ready to configure our prowser to use a privoxy as a proxy server: Note: Although now you are anonymous to all destinations hosts you can improve you privacy by using personal DNS. What I mean is that if you for example use google’s 8.8.8.8 as a nameserver than all your DNS data are owned by google which means that google know exactly what websites you are visiting as they have all your DNS resolutions! This is just a thought to be considered !

Extract DATE From a Photo Image Using Exiftool

Very useful tool when one needs to get in EXIF data from an image/photo is to use exiftool. By default exiftool extract all information from an image. If only date is required we can grep through the out information and extract only date and or file name. One approach would be to simply use:

1
$ exiftool -common directory

to get the output. However, there are two problems. First the data output is not chronologically sorted and second it outputs more information than required. The command bellow will strip the unnecessary data from the output and sort from oldest to newest. Make sure that you are located in the directory from which you are running the below command:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$ for i in $(ls | sort); do exiftool -common $i | grep -E "File Name|Date"; done
File Name                       : DSC00639.JPG
Date/Time Original              : 2010:03:04 11:09:17
File Name                       : DSC01408.JPG
Date/Time Original              : 2010:04:13 12:01:03
File Name                       : DSC02366.JPG
Date/Time Original              : 2010:09:06 12:41:31
File Name                       : DSC03990.JPG
Date/Time Original              : 2011:02:07 14:56:43
File Name                       : DSC05060.JPG
Date/Time Original              : 2011:04:21 12:02:53
File Name                       : DSC05370.JPG
Date/Time Original              : 2011:06:24 12:30:53
File Name                       : DSC06019.JPG
Date/Time Original              : 2012:06:09 17:03:05
File Name                       : DSC07919.JPG
Date/Time Original              : 2012:09:16 12:39:26
File Name                       : DSC08459.JPG
Date/Time Original              : 2013:02:10 11:40:31
File Name                       : DSC09922.JPG
Date/Time Original              : 2014:03:30 10:27:21

Print File Without First Line - Bash Shell

Simple way to print a file to STDOUT while omitting the first line. This can be quite helpful when dealing with CSV files with header row.

1
2
3
4
5
$ cat file1 
0 line
1 line
2 line
3 line

Remove/omit first line using sed:

1
2
3
4
$ sed 1d file1 
1 line
2 line
3 line

HL-2135W Brother Linux Driver Installation

Here we assume that your HL-2135W Brother printer is already connected to your network and you know the IP address of your printer or it is already connected to your computer using USB cable. If that is the case first stem is to got to Brother official website and download updated “Linux-brprinter-installer” installer. There is no point to insert the exact URL here as it will definitely change overtime. Depends on the package management your Linux distro uses choose either DEB or RPM. Once you are ready then:

Extract shell executable:

1
# gunzip linux-brprinter-installer-2.0.0-1.gz

Start installation script. In the process you will need to agree to license and install additional packages. Not all output is displayed below, only the important parts.:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
bash linux-brprinter-installer-2.0.0-1
Input model name ->HL-2135W

You are going to install following packages.
   hl2130lpr-2.1.0-1.i386.deb
   cupswrapperHL2130-2.0.4-2.i386.deb
OK? [y/N] ->y
Brother License Agreement
Do you agree? [Y/n] ->Y
GPL License Agreement
Do you agree? [Y/n] ->Y
Need to get 40.4 MB of archives.
After this operation, 137 MB of additional disk space will be used.
Do you want to continue [Y/n]?Y
Will you specify the Device URI? [Y/n] ->Y ( ANSWER NO FOR USB !!! )
16 (I): Specify IP address.
17 (A): Auto. (dnssd://Brother%20HL-2130%20series._ipp._tcp.local/)

select the number of destination Device URI. ->16

 enter IP address ->10.1.1.27
lpadmin -p HL2130 -v socket://10.1.1.27 -E
Test Print? [y/N] ->y

wait 5s.
lpr -P HL2130 /usr/share/cups/data/testprint
Hit Enter/Return key.

BASH Bitwise XOR Example

Normally, I get around with basic logical operators like AND or OR. However, today I needed XOR to make my script more compact and shorter. Here are some how to make use of XOR ( bitwise exclusive OR ) in SASH examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#!/bin/bash

# bitwise exclusive OR ( XOR ) example
echo XOR Truth table:

echo 0XOR0=$(( 0 ^ 0 ))
echo 0XOR1=$(( 0 ^ 1 ))
echo 1XOR0=$(( 1 ^ 0 ))
echo 1XOR1=$(( 1 ^ 1 ))

echo Conditional XOR function example

function xor {
        if (( $1 ^ $2 )) ;then
                echo 1
        else
                echo 0
        fi
}

echo -n 0XOR0= 
xor 0 0
echo -n 0XOR1=
xor 0 1
echo -n 1XOR0=
xor 1 0
echo -n 1XOR1=
xor 1 1

Bash Script execution and output:

1
2
3
4
5
6
7
8
9
10
11
$ bash xor.sh 
XOR Truth table:
0XOR0=0
0XOR1=1
1XOR0=1
1XOR1=0
Conditional XOR function example
0XOR0=0
0XOR1=1
1XOR0=1
1XOR1=0

Rsync Hardisk Backup Clone to External Drive

Here is a simple and cheap idea on how to make a backup of your desktop data to a external disk. It is always good idea to keep your data on a some other storage not directly associated with your primary desktop hardware and software. This kind of redundancy makes you recover from disaster easier. First, get new external USB hard-drive dedicated for this backup. The best would be to have drive with the same size as your primary data disk and preferably for a faster speed the drive should be USB 3.0. The aim is to keep a exact copy of our home directory on a external USB drive.

Plug-in your USB drive, format it with ext4 filesystem and execute:

1
2
3
4
5
6
7
8
9
10
11
# blkid 
/dev/sda3: UUID="c95b5c0f-262f-4322-91cd-2b6e2af469c4" TYPE="swap" 
/dev/sda1: LABEL="System Reserved" UUID="3C600AA3600A63CC" TYPE="ntfs" 
/dev/sda2: UUID="DE921498921476ED" TYPE="ntfs" 
/dev/sdb1: LABEL="STEAM" UUID="2A2E093B2E090195" TYPE="ntfs" 
/dev/sda5: UUID="8d5c2d82-b5c2-48a0-a1f8-dd406a5db3f2" TYPE="ext4" 
/dev/sdc: TYPE="isw_raid_member" 
/dev/sde: TYPE="isw_raid_member" 
/dev/sdd1: UUID="4356e78b-6f85-4c2d-91a6-f561f35d9e20" TYPE="ext4" 
/dev/md0: UUID="11161d35-ce93-4abd-beef-4748577f7001" TYPE="ext4" 
/dev/md0p1: UUID="38ad8d8c-f0b1-4f1a-bb07-1193eb4eefb0" TYPE="ext4"

Our drive has UUID=“4356e78b-6f85-4c2d-91a6-f561f35d9e20” and this is what we are going to insert into /etc/fstab config file so it will mount after each reboot. To do that insert the following into /etc/fstab:

1
UUID=4356e78b-6f85-4c2d-91a6-f561f35d9e20       /mnt/backup ext4 rw,relatime,user_xattr,barrier=1,data=ordered    0      0

Make sure that /mnt/backup mount point is created:

1
# mkdir /mnt/backup

Next open your crontab and set rsync to clone all data in your home directory to /mnt/backup/ every hour:

1
0 * * * * /usr/bin/rsync -a --delete-after /home/lubos/ /mnt/backup/

All is done. From now on your home directly will be cloned/backup to external device. If you are paranoid you could also encrypt your destination drive.

Save and Recover Disk Partition Table and Geometry With Sfdisk

Before you start play with your brand new hard-disk it is a good idea to same some specs regarding its original partitioning and geometry alignment. The tool which comes handy in this case is sfdisk. Let’s take a partitioning and geometry alignment of /dev/sdb block device:

1
2
# sfdisk -g /dev/sdb
/dev/sdb: 21269 cylinders, 35 heads, 21 sectors/track

Now, save partition table:

1
2
3
4
5
6
7
8
9
# sfdisk -d /dev/sdb > partition.txt
# cat partition.txt
# partition table of /dev/sdb
unit: sectors

/dev/sdb1 : start=       21, size=  6836214, Id= 7
/dev/sdb2 : start=        0, size=        0, Id= 0
/dev/sdb3 : start=        0, size=        0, Id= 0
/dev/sdb4 : start=        0, size=        0, Id= 0

To restore your disk geometry and partition run a following command:

1
sfdisk -C 21269 -H 35 -S 21 --force /dev/sdb < partition.txt

All done. Make sure to create a new filesystem!

Convert Time Command Output to Seconds

Today, I wanted to test my USB flash drive speed with dd command. I have run dd command multiple times with time command and wanted take an average from all results. For example, here is an output of dd and time with 5 times iteration loop:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
$ cat dd-time-out.txt
TEST 1
1800+0 records in
1800+0 records out
1887436800 bytes (1.9 GB) copied, 157.159 s, 12.0 MB/s

real    5m31.014s
user    0m0.004s
sys     0m1.476s
TEST 2
1800+0 records in
1800+0 records out
1887436800 bytes (1.9 GB) copied, 180.861 s, 10.4 MB/s

real    5m41.892s
user    0m0.000s
sys     0m1.908s
TEST 3
1800+0 records in
1800+0 records out
1887436800 bytes (1.9 GB) copied, 174.678 s, 10.8 MB/s

real    5m40.847s
user    0m0.000s
sys     0m1.896s
TEST 4
1800+0 records in
1800+0 records out
1887436800 bytes (1.9 GB) copied, 140.008 s, 13.5 MB/s

real    5m32.853s
user    0m0.012s
sys     0m2.764s
TEST 5
1800+0 records in
1800+0 records out
1887436800 bytes (1.9 GB) copied, 143.161 s, 13.2 MB/s

real    5m37.813s
user    0m0.004s
sys     0m2.484s

The only cosmetic issue is that it would be great to have all times in seconds if one needs to sum up all times and calculate average. Here is one way of solving this problem:

1
2
3
4
5
6
$ cat dd-time-out.txt | grep real | cut -d " " -f5 | sed 's/m/:/' | cut -d \. -f1 | sed s/:/*60+/g | bc
331
341
340
332
337

Now we have times in seconds for each relevant dd output.

How to Calculate Column Sum With Bash

Simple bash to sum a dollar price column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ cat cost 
$14.99
$13.95
$11.85
$10.90
$7.95
$7.95
$7.88
$7.35
$17.30
$15.99
$10.55
$10.17
$9.69
$11.95
$12.95

First remove “$” dollar sign, format and calculate with bc:

1
2
$ cat cost | sed 's/\$//' | paste -sd+ | bc
171.42