About

mFabrik Blog is about mobile and web software development, open source and Linux. We tell exciting tales where business, technology, web and mobile convergence.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License.

One-liner to copy remote MySQL database to local computer

Posted on July 23, 2011

The following commands dump a MySQL database from a remote server and create a corresponding database on the local computer.

The instructions have been tested on OSX and Linux (Ubuntu/Debian). On-line SSH compression is applied, so transferring SQL files, which are text content and compress well, should be around 6x faster than normal.

(Well… actually the script is six lines, but because this is my blog I’ll decide it doesn’t count)

The script

  • Remotely runs mysqldump and puts the result to a local file
  • Creates a MySQL database and corresponding user with full access to this database
  • Reads the content of mysqldump to the newly created database
 ssh user@dserver.com -C -o CompressionLevel=9 mysqldump -u YOURDATABASEUSER --password=YOURDATABASEPASSWORD --skip-lock-tables --add-drop-table YOURDATABASENAME > YOURDATABASENAME.sql
mysql -uroot -p
create database YOURDATABASENAME;
connect YOURDATABASENAME;
source YOURDATABASENAME.sql
GRANT ALL ON YOURDATABASENAME.* TO 'YOURDATABASEUSER'@'localhost' identified by 'YOURDATABASEPASSWORD';

Leave out create database and GRANT for the subsequent runs – all data on the local computer will be replaced.

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Encrypted folders on Ubuntu Linux using eCryptfs on an external hard drive

Posted on April 15, 2011

This blog post continues my Ubuntu encryption tools testing. Previously there was an example for losetup. However, with the latest Ubuntus eCryptfs is recommended instead.

eCrypfs makes one directory in a file-system crypted. Since it does not work on a partition level, you do not need to worry about extending or shrinking the encrypted partition inside the uncrypted partition. Instead, file system works normally and only the content of the files are encrypted. This should also add some more fault tolerance in the case of disk failure – it is less unlikely to loose the whole encrypted partition.

Here we create an encrypted directory on an external hard drive

  • First format the drive with ext4 file-system (mkfs.ext4)

Prepare a passphrase in a .TXT file (you won’t be asked to type mistyped passphrase again).

Then go to the mounted disk

cd /media/fbf0a2c3-0631-4a00-ad1b-a34e449c8b2a/
mkdir crypted
chmod 700 crypted/
sudo mount -t ecryptfs crypted/ crypted/

Copy-paste in the passphrase and otherwise use the default settings given by ecryptfs.

Voilá. Now your encrypted folder is ready. It is not accessible if you do not mount it with eCryptfs and enter the passphrase.

We can test it with umount and mounting it again. It will ask passphrase and  format options again:

echo "foobar" > test.txt
umount /media/fbf0a2c3-0631-4a00-ad1b-a34e449c8b2a/crypted
cd crypted
cat test.txt

You will see garbled output instead of the file contents. But after you remount it it works again:

mount -t ecryptfs crypted/ crypted/

Just give the passphrase and hit enter to all options (again).

More info

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Sticky session load balancing with Apache and mod_balancer on Ubuntu Linux

Posted on April 15, 2011

Apache 2.2 can do load balancing with sticky sessions. However, there is a catch. You need to use mod_headers module to set a cookie based on the chosen balancer member for the first request and then route the subsequent requests to this client.

Use cases

The method described here works in every situation and does not rely on client IP address, etc. The only downside is that if one balancer member goes down all subsequent requests for it will die. So this method can be only used for load balancing, not for high availability (I am not sure if BALANCER_ROUTE_CHANGED environment variable is set when a balancer member is lost and would redirect the clients to a new balancer member).

This requests were tested on Ubuntu Linux, but may as well work in other environments.

Setting route configution in virtual host

Create a balancer

<Proxy balancer://yourlb>
 BalancerMember http://127.0.0.1:13001/ route=1
 BalancerMember http://127.0.0.1:13002/ route=2
 BalancerMember http://127.0.0.1:13003/ route=3
 BalancerMember http://127.0.0.1:13004/ route=4
</Proxy>

Set the cookie using mod_headers. Note that the cookie must be in format [session name].[route id] (the dot is required). It seems to be possible to leave session name empty.

Header add Set-Cookie "ROUTEID=.%{BALANCER_WORKER_ROUTE}e; path=/" env=BALANCER_ROUTE_CHANGED

Make ProxyPass  follow the cookie (Zope virtual host monster style with HTTPS)

ProxyPass / balancer://lbsits/http://localhost/VirtualHostBase/https/yoursite.org:443/yourplonesiteid/VirtualHostRoot/ stickysession=ROUTEID

Note: Hard restart is required. apache2ctl graceful is not enough to make new balancer rules effective.

Testing

Use wget

wget -S https://yoursite.org/

See that Set-Cookie: ROUTE_ID is present and it contains a valid value (is not empty)

HTTP/1.1 200 OK
 Date: Wed, 13 Apr 2011 15:21:52 GMT
 Server: Zope/(Zope 2.10.9-final, python 2.4.5, linux2) ZServer/1.1 Plone/3.3.3
 Content-Length: 23197
 Expires: Sat, 01 Jan 2000 00:00:00 GMT
 Content-Type: text/html;charset=utf-8
 Content-Language: en
 Set-Cookie: I18N_LANGUAGE="en"; Path=/
 Set-Cookie: ROUTEID=.1; path=/

More info

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Encrypted partition on an external hard disk in Ubuntu Linux

Posted on April 2, 2011

Here are short instructions how to create and use encrypted hard disk on Ubuntu Linux. These instructions are tested on Ubuntu 10.10 Maverick Meekat.

We are going to use the following strategy

  • Use cryptoloop kernel module. This is 100% open source software and the part of mainstream Linux (as opposite to TrueCrypt)
  • No additional software installations needed – stock Ubuntu is fine
  • These instructions work also on a server – no GUI needed
  • We will create an encrypted file on the target hard-drive partition. The target partition can be even NTFS, so the hard disk acts normally in Windows, though you still need Linux to read encrypted files
  • The encrypted file system will be Ext4 and mounted through a loop device

Prerequisites

  • Ubuntu Linux
  • Basic terminal / command-line know how

Creating an encrypted partition on an external driver

Plug-in the hard disk to Ubuntu Desktop – it will automount it over USB.

Install cryptoloop driver

    sudo modprobe cryptoloop

Check what mount point the new disk has – in your case it is /media/Elements:

ls /media
cdrom  cdrom0  Elements

Check how much space the disk contains

df -h
/dev/sdc1             932G   94M  932G   1% /media/Elements

Create a loopback file – don’t use the whole disk. In our case, allocate just 500 GB for the encrypted file system. Note that this may take time:

dd if=/dev/zero bs=1G count=500 of=/media/Elements/encrypted-fs

Now mount this file as a crypted loopback device. Note that need you to give separate password for sudo and the encryption. We use SHA-1 encryption. Don’t use password, use passphrase. SHA-1 has 160 bit key and to produce enough entropy, you need a latin sentence of at least 27 case-sensitive alphanumeric characters. We do this using losetup command.

sudo losetup -e sha1 /dev/loop0 /media/Elements/encrypted-fs

Then format this encrypted partition as ext4 file system.

mkfs.ext4 /dev/loop0

And now start using it

mkdir /media/encrypted
mount /dev/loop0 /media/encrypted

Usage

Later when you remount the encrypted partition you can simply do

sudo losetup -e sha1 /dev/loop0 /media/Elements/encrypted-fs
mount /dev/loop0 /media/encrypted

After usage always cleanly unmount the encrypted partition before unplugging the external hard-drive

umount /media/encrypted

More info

 

 

 

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter