toic.org - Entries for the tag server

Reclaiming InnoDB ibdata unused space.

Tue, 04 Dec 2012 23:05:56 +0000

As you well know InnoDB will store its data either in one big system tablespace called ibdata or you can store it in multiple tablespaces (innodb_file_per_table). In either case InnoDB by default will not reclaim unused space gained when deleting data. Using innodb_file_per_table will separate each InnoDB table in its own .ibd file and it can be easily reclaimed by doing optimize table, using system tablespace will get you stuck since there is no real supported method for reducing you ibdata file and reclaiming unused disk space.

One could wonder why would anyone want to have its ibdata in system tablespace? Well there are some "bugs" (in versions prior to 5.5) and performance issues that cause mysql lock-ups when using innodb_file_per_table and doing truncate table or drop table or even drop database queries with large buffer pools. In a nutshell MySQL will do a whole buffer pool lock and scan on each of the above mentioned commands effectively stoping the entire MySQL server until it scans over the entire buffer pool for references to those dropped/truncated tables.

When given choice and when I do know the pattern of data input to my tablespace i will chose system tablespace.

Recently I was a victim of my own system and uncontrolled input of data to my InnoDB table space which leaved me with almost 36Gb of garbage data over one night. Naturally i truncated the data, but it left me with 37Gb ibdata file.

So here are the steps I've taken to reduce this system tablespace.

First of all backup!

I highly recommend Xtrabackup from Percona for this task, yes it will require additional 37Gb of space for backup but this is by far best point in time backup for MySQL.

innobackupex $backupdir

And replace the backupdir with your preferred location

Let's create a working environment, say you have a lots of space on /home

mkdir /home/inno-reduce
cd /home/inno-reduce

Ok, next stop dumping all the databases that have innodb tables inside, for this I've created a small bash script a while ago..

#!/bin/bash
CWD=`pwd`
mysql -e "SELECT distinct(table_schema) FROM information_schema.TABLES where ENGINE = 'InnoDB'" > $CWD/innodb-databases.list
sed "/table_schema/d" $CWD/innodb-databases.list > $CWD/tmp
mv $CWD/tmp $CWD/innodb-databases.list
for database in `cat $CWD/innodb-databases.list`; do
echo "dumping data from $database to $CWD/$database.sql"
/usr/bin/mysqldump --triggers --routines $database > $CWD/$database.sql;
done

Now would be a good time to cut off any application using MySQL (for example turn off Apache, or drop the packets in firewall). Just copy and paste this code in step1-dump.sh file, make it executable and run it. It will produce a .sql dump files in your current directory (hopefully we are still on /home/inno-reduce)

Step2 script will iterate trough that dumped data files and drop all the databases that have innodb tables inside.

#!/bin/bash
CWD=`pwd`
for database in `ls $CWD/*.sql|awk -F'/' '{print $NF}'|cut -d. -f1`; do
echo "droping database $database"
mysqladmin drop $database
done

Again just copy and paste this code in step2-drop.sh, make it executable and run it. At this point There shouldn't be any InnoDB tables in MySQL server.

Now, it is a good time to shutdown your MySQL service and rm ibdata file. On typical CentOS system this would be done with

service mysqld stop
rm -f /var/lib/mysql/ibdata1

If you are not running CentOS and default MySQL install or if you have ibdata file on some other path… please adjust the above. Starting MySQL service at this point would "regenerate" ibdata file with its smallest size increment as defined in my.cnf lets do so:

service mysqld start

And now for the third script

#!/bin/bash
CWD=`pwd`
for database in `ls $CWD/*.sql|awk -F'/' '{print $NF}'|cut -d. -f1`; do
mysqladmin create $database
mysql $database < $CWD/$database.sql
done

As before copy and paste this script into step3-import.sh (remember to place this file at the same directory as other two, and dumps), make it executable and run it.

Voila! your data is back in, ibdata reduced and MySQL up&running.

Remember to restore any access to your MySQL service (starting Apache, clearing firewall drops, etc..)

Wsgi on cPanel improved

Sun, 27 Mar 2011 20:01:02 +0000

There is a long time now from my original post on running Django with python 2.6 on cPanel based servers, and as time passed there was some issues while deploying Django in such a way. So I decided to post another post with some updates regarding deployment of any python based scripts trough mod_wsgi and cPanel.

What's changed since last post?

There is a fix for easy_apache rebuilds, retaining the mod_wsgi and not braking rebuild process
There was some additions in virtualhost include files, enabling python app to run as a user, not nobody
A fix for cpanel's shell fork bomb protection while running python aps as a user and not nobody
Some minor changes regarding media handling, so it get's included in virtualhost includes and not by eating up user's subdomains
And of course python and Django version updates

Disclaimer at the beginning, this setup works for me on latest RELESE cPanel build, running on top of CentOS 5.5. I'm posting this as a way how I managed to get it working, so this shouldn't be considered as a official way of running Django or any other python app on cPanell servers. Although, as per my last post <a href="/blog/2010/django-on-cpanel-with-python2-6-virtualenv-and-mod_wsgi/">Django on cpanel with python2.6, virtualenv and mod_wsgi</a> lot's of people are reporting this is working for them also, and this version of deployment guide will bring some more enhancements.

Installing python and dependencies

mkdir /usr/src/python2.7 && cd /usr/src/python2.7

again, if you need sqlite, now is the time to install it.

wget http://www.sqlite.org/sqlite-autoconf-3070500.tar.gz
tar zxvf sqlite-autoconf-3070500.tar.gz
cd sqlite-autoconf-3070500
./configure
make
make install

Since CentOS 5.5 is still running python 2.4 and if you don't wish to brake your system you should install your python 2.7 in alternate location:

cd /usr/src/python2.7/
wget http://www.python.org/ftp/python/2.7.1/Python-2.7.1.tgz
tar zxvf Python-2.7.1.tgz
cd Python-2.7.1
./configure --prefix=/opt/python2.7 --with-threads --enable-shared
./configure --help
make
make install

This will install python 2.7 in /opt/python2.7 directory. To make any use of this alternate install we must do the following:

ln -s /opt/python2.7/bin/python /usr/bin/python2.7
echo '/opt/python2.7/lib'>> /etc/ld.so.conf.d/opt-python2.7.conf
ldconfig

It will make a symbolic link in your path and instruct the system where to find libs for this alternate python install.

Now is a good time to check if everything is working as it should

root@toy2 [/usr/src/python2.7]# /usr/bin/python2.7
Python 2.7.1 (r271:86832, Mar 26 2011, 22:31:33)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-48)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>>

Type CTRL+D to exit.

Next thing to do is installation of python-setup tools:

/usr/bin/python2.7
cd /usr/src/python2.7/
wget http://pypi.python.org/packages/2.7/s/setuptools/setuptools-0.6c11-py2.7.egg
sh setuptools-0.6c11-py2.7.egg --prefix=/opt/python2.7

In this tutorial, I'm skipping the installation of mysql for python since you will have to install it per user basis in their virtual environments. All we need to do next is to install virtualenv to our python 2.7 so we can distribute them to our users

cd /opt/python2.7/bin/
./easy_install virtualenv

mod_wsgi installation

cd /opt/python2.7/lib/python2.7/config/
ln -s ../../libpython2.7.so .
cd /usr/src/python2.7/
wget http://modwsgi.googlecode.com/files/mod_wsgi-3.3.tar.gz
tar zxvf mod_wsgi-3.3.tar.gz
cd mod_wsgi-3.3
./configure --with-python=/opt/python2.7/bin/python
make
make install

Now this will install mod_wsgi.so file in /usr/local/apache/modules folder, as I experienced during cPanel easy_apache rebuilds it will completely empty this folder, thus any virtualhost includes relying on mod_wsgi will fail and easy_apache will not be able to properly rebuild your httpd.conf file and whole apache or php upgrade you were running will fail and revert back to last good state.

To avoid this we will now copy the mod_wsgi to different folder:

mkdir /usr/local/apache/extramodules
mv /usr/local/apache/modules/mod_wsgi.so /usr/local/apache/extramodules/

Note that this wsgi module is built with python 2.7 and will not work with any other version. If for some strange reason later on you try to run any given python application with system's default python2.4 it will not work.

Anyways, all we need to do next is to include mod_wsgi into the apache configuration:

nano /usr/local/apache/conf/includes/pre_virtualhost_global.conf

and paste:

LoadModule wsgi_module /usr/local/apache/extramodules/mod_wsgi.so
AddHandler wsgi-script .wsgi

Now do configtest:

root@toy2 [~]# service httpd configtest
Syntax OK

If you get Syntax OK at the end, you can safely restart your apache:

/scripts/restartsrv httpd

This is it, our apache can now serve python apps like Django, pylons, etc...

Setting up Django projects

Just like in my previous post I'll use '[username]' for cpanel username reference and '[domain]' for that user's domain. To match your needs you will have to replace those where appropriate.

We will quickly setup user's virtualenv, be aware though some of the python packages requires compile rights, and most of the cPanel setups I've seen so far are forbidding compiler access to their users. You can either make an permanent exception for this user, or you can instruct your user to contact you whenever he needs to install some package that requires compile rights (PIL, mysql...).

To enable compile rights for that specific user you can find that user in WHM WHM->Compiler Access->Allow specific users to use the compilers or if you don't like to exit the shell you can do it like this:

gpasswd -a [username] compiler

to remove it, use the same path in WHM and in shell:

gpasswd -d [username] compiler

For now, lets add this user to the compiler group since we will be installing some python packages for him in the start. Also you will have to enable your user a normal login shell, let's first check what shell this user has.

cat /etc/passwd |grep [username]

it should return something like this:

[username]:x:928:923::/home/[username]:/usr/local/cpanel/bin/jailshell

This user has jailshell enabled to change that into normal shell do this:

usermod -s /bin/bash [username]

If it's already bash, don't change it. You can also do this via WHM-> Manage Shell Access.

So let's finaly create a virtualenv for our user:

cd /home/[username]
/opt/python2.7/bin/virtualenv --no-site-packages --distribute virtualenv
chown -R [username].[username] virtualenv

this will create a new directory /home/[username]/virtualenv with our new virtual python environment for our user. From this point on we will do everything as the user and not root, until told otherwise.

su [username]
source virtualenv/bin/activate

And the promt will change to something like this:

(virtualenv)[username]@[domain] [~]#

that way we know we are loged in as user and we are using his virtualenv. Now let's install Django in this users environment, simply enter:

easy_install django

Now is also a good time to install additional python site-packages like mysql and PIL.

easy_install pil
easy_install mysql-python

So we have the django installed inside the users virtualenv, all we have to do now is start one of the instance. It's probably the best practice to keep django sites outside of public_html folders on cpanel servers. So we will do (still as a user)

cd
mkdir djangosites
cd djangosites
django-admin.py startproject [projectname]

So by now we should have a directory structure like this:

/home/[username]/virtualenv < - python virtual environment
/home/[username]/djangosites <- django sites folder
/home/[username]/djangosites/[projectname] <- django project

Create the wsgi script for that project, usualy [projectname].wsgi in django sites folder

nano /home/[username]/djangosites/[projectname].wsgi

and paste the following code:

import sys
import site
import os
vepath = '/home/[username]/virtualenv/lib/python2.7/site-packages'
prev_sys_path = list(sys.path)
site.addsitedir(vepath)
sys.path.append('/home/[username]/djangosites')
new_sys_path = [p for p in sys.path if p not in prev_sys_path]
for item in new_sys_path:
sys.path.remove(item)
sys.path[:0] = new_sys_path
from django.core.handlers.wsgi import WSGIHandler
os.environ['DJANGO_SETTINGS_MODULE'] = '[projectname].settings'
application = WSGIHandler()

give the scripts execute permissions:

chmod +x /home/[username]/djangosites/[projectname].wsgi

and exit user login with:

exit

now you will have your root propmt at the shell:

root@servername [/home/username]#

If you have decided so, now is the good time to disable the compiler access for this user.

create the apache include folder for that virtualhost:

mkdir -p /usr/local/apache/conf/userdata/std/2/[username]/[domain]/
nano /usr/local/apache/conf/userdata/std/2/[username]/[domain]/django.conf

and add:

<ifmodule mod_wsgi.c>
WSGIScriptAlias / /home/[username]/djangosites/[projectname].wsgi
WSGIDaemonProcess [projectname] user=[username] group=[username] processes=5 threads=15 display-name=%{GROUP}
WSGIProcessGroup [projectname]
WSGIApplicationGroup %{GLOBAL}
</ifmodule>

Ok, so this will create a processgroup for this specific django projects with 5 processes each 15 threads with our user's uid and gid. This is great since we have isolated this user to his own application pool and his own home path, also we are enforcing user quotas since if we omit "user=[username] group=[username]" from WSGIDaemonProcess the deamon process will run ass apache user nobody, and all files made/uploaded via this application will not count into this user's quota. They will also make lot's of problems if the user don't grant global write permissions on some folders. Just like mod_php and php_suexec.

Shell fork bomb exception

On the other hand, running 5 processes each with 15 threads (you can customize this ofcourse), will hit the defaults ulimits enforced by cPanels shell fork bomb protection (if enabled). You could either disable the protection, or you can make a little adjustments to its logic so that you can make exceptions per user allowing them a bit more freedom but still not unlimited.

I have found this method somewhere on cPanel forums (I can't seem to find the exact post right now, but I will update this post when I find it), anyways with little patching of profile, limits and bashrc you can create a list of users that have a slightly higher limits, so our django users won't run into any memory or fork limits normally enforced by fork bomb protection.

I've created a little tarball with installer scripts for patched fork bomb logic, I advise you to check the files prior to installing them. Script will make a copies of your original files, to the same destination with .orig suffixes.

cd /usr/src/
wget https://toic.org/files/django/forkbomb.tar.gz
tar zxvf forkbomb.tar.gz
cd forkbomb
./install.sh

Now all you have to do is put one username per line in /etc/profile.exclude

Each user in that line will have slightly bigger ulimits

Back to the Django

Let's also make admin media paths and media folder paths

System Message: WARNING/2 (<string>, line 323)

Literal block expected; none found.

mkdir /home/[username]/djangosites/[projectname]/media nano /usr/local/apache/conf/userdata/std/2/[username]/[domain]/django-media.conf

and add.

Alias /admin_media/ /home/[username]/virtualenv/lib/python2.7/site-packages/Django-1.2.4-py2.7.egg/django/contrib/admin/media/
<Directory /home/[username]/virtualenv/lib/python2.7/site-packages/Django-1.2.4-py2.7.egg/django/contrib/admin/media>
Order deny,allow
Allow from all
</Directory>
Alias /media/ /home/[username]/djangosites/[projectname]/media/
<Directory /home/[username]/djangosites/[projectname]/media>
Order deny,allow
Allow from all
</Directory>

Just replace your python version, django version and username and projectname in paths. This will create [domain]/media - for site media and [domain]/admin_media/ for admin media

Now verify those apache include files and rebuild apache conf:

/scripts/verify_vhost_includes
/scripts/rebuildhttpdconf

As always you can make subdomains for your media files, you will just need to update your [projectname]/settings.py

And that's it, hope you enjoy your django installation

Django on cpanel with python2.6, virtualenv and mod_wsgi

Sat, 14 Aug 2010 19:59:44 +0000

Here is the simple to follow step by step tutorial on howto run latest Django on cpanel powered servers with python 2.6, virtual env and mod_wsgi.
If you are running cPanel on your server it's most probably a rhel or centos distro, and those will have by default python2.4 installed. You can't just overwrite your python2.4 as many of the system utilities are depended on the python2.4 So what we will do is install an alternate python 2.6 and setup our server to run Django instances with python2.6

First of all let's make a folder where we will download all those source packages

Installing python and dependencies

mkdir -p /usr/src/python26 && cd /usr/src/python26

Next, we have to download and install sqlite3 dependency. It's not needed if your Django instances will not run sqlite databases, but on a safe side it's better to install it than not.

wget http://www.sqlite.org/sqlite-amalgamation-3.6.4.tar.gz
tar zxvf sqlite-amalgamation-3.6.4.tar.gz
cd sqlite-3.6.4
./configure
make
make install

If all went well it's time to install python 2.6 as alternate install.

cd /usr/src/python26
wget http://www.python.org/ftp/python/2.6.5/Python-2.6.5.tgz
tar zxvf Python-2.6.5.tgz
cd Python-2.6.5
./configure --prefix=/opt/python2.6 --with-threads --enable-shared
make
make install

This will install python 2.6 in /opt/python2.6 and will not interfere with system python install. Let's make a symbolic link to our newly installed python:

ln -s /opt/python2.6/bin/python /usr/bin/python2.6<

Also we must instruct our system where it should find the libs for new python.

echo '/opt/python2.6/lib' >> /etc/ld.so.conf.d/opt-python2.6.conf
ldconfig

You shoud check if your new python install is working as it should:

/usr/bin/python2.6
Python 2.6.5 (r265:79063, Jul 15 2010, 16:55:23)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-48)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>>

Type CTRL+D to exit. Next thing to do is installation of python-setup tools:

cd /usr/src/python26
wget http://pypi.python.org/packages/2.6/s/setuptools/setuptools-0.6c11-py2.6.egg
sh setuptools-0.6c11-py2.6.egg --prefix=/opt/python2.6

Make sure to define the prefix on where your new python 2.6 installation is residing. We should make a temporary alias for new python as it will be needed for installation of python mysql package:

alias python="/opt/python2.6/bin/python"

Let's check if everything is working ok:

python
Python 2.6.5 (r265:79063, Jul 15 2010, 16:55:23)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-48)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> CTRL + D

So next in line is mysql for python:

cd /usr/src/python26
wget http://netcologne.dl.sourceforge.net/project/mysql-python/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz
tar zxvf MySQL-python-1.2.3.tar.gz
cd MySQL-python-1.2.3
python setup.py build
python setup.py install

Again let's verify the install:

cd /usr/src/python26
python
Python 2.6.5 (r265:79063, Jul 15 2010, 17:57:29)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-48)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> import MySQLdb
>>> CTRL + D

It shouldn't return any errors while issuing import statements. Ok for the end let's install the virtualenv so we can create a virtual python enviroment for each user and his Django instances

cd /opt/python2.6/bin
./easy_install virtualenv

And here we conclude the python instalation. Next order of business is mod_wsgi

mod_wsgi installation

cd /opt/python2.6/lib/python2.6/config/
ln -s ../../libpython2.6.so .
cd /usr/src/python26
wget http://modwsgi.googlecode.com/files/mod_wsgi-3.2.tar.gz
tar zxvf mod_wsgi-3.2.tar.gz
cd mod_wsgi-3.2
./configure –with-python=/opt/python2.6/bin/python
make
make install

This will install mod_wsgi in /usr/local/apache/modules/mod_wsgi.so Note that this wsgi module is built with python 2.6 and will not work with any other version. All we have to do now is add the mod_wsgi in our apache conf. You can do that on cpanel servers via WHM. Log into WHM , click on Apache Configuration then Include Editor then Pre-Virtualhost Include and select All versions Paste this:

LoadModule wsgi_module /usr/local/apache/modules/mod_wsgi.so
AddHandler wsgi-script .wsgi

** Save and restart apache.** And that's it. Our apache is now ready to serve python scripts via mod_wsgi Now all we have to do is install some Django instances for our user.

Setting up Django projects

I'll use '[username] ' for cpanel username reference and '[domain] ' for that user's domain. To match your needs you will have to replace those where appropriate. First of all you will have to decide if you will do empty virtual environments so that the user can decide what python site-packages will he use or will you preinstall some packages so that user can only update his virtualenv with packages that are missing. If you are choosing the first method user will have to have compile rights under security setting of cpanel WHM otherwise he will not be able to install packages as python mysql, PIL and some others. If you choose the former method you can preinstall those packages systemwide for our alternate python install, but whenever you update those packages all virtual environments will be updated with them. It's a matter of personal preference. I for myself rather like the clean virtualenv, and before delivering it to the client you as root preinstall in his virtualenv those packages requiring gcc rights. So let's create a virtualenv for our user:

cd /home/[username]
/opt/python2.6/bin/virtualenv --no-site-packages --distribute virtualenv
chown -R [username].[username] virtualenv

this will create a new directory __ /home/[username]/virtualenv __ with our new virtual python environment for our user. You will have to enable normal shell for this user, let's first check what shell this user has.

cat /etc/passwd |grep [username]

it should return something like this:

[username]:x:928:923::/home/[username]:/usr/local/cpanel/bin/jailshell

This user has jailshell enabled to change that into normal shell do this:

usermod -s /bin/bash [username]

You can also do this via WHM-> Manage Shell Access. From this point on we will do everything as the user and not root, until told otherwise.

su [username]
source virtualenv/bin/activate

And the promt will change to something like this:

(virtualenv)[username]@[domain] [~]#

that way we know we are loged in as user and we are using his virtualenv. Now let's install Django in this users environment, simply enter:

easy_install django

Now is also a good time to install additional python site-packages like mysql and PIL. First temporarily enable compilers for this user via WHM->Compiler Access->Allow specific users to use the compilers do the modules install with:

easy_install [module_name]

disable the compiler access afterwards

cd
mkdir djangosites
cd djangosites
django-admin.py startproject [projectname]

So by now we should have a directory structure like this:

/home/[username]/virtualenv < - python virtual environment
/home/[username]/djangosites <- django sites folder
/home/[username]/djangosites/[projectname] <- django project

Create the wsgi script for that project, usualy [projectname].wsgi in django sites folder

nano /home/[username]/djangosites/[projectname].wsgi

and paste the following code:

import sys
import site
import os
vepath = '/home/[username]/virtualenv/lib/python2.6/site-packages'
prev_sys_path = list(sys.path)
site.addsitedir(vepath)
sys.path.append('/home/[username]/djangosites')
new_sys_path = [p for p in sys.path if p not in prev_sys_path]
for item in new_sys_path:
sys.path.remove(item)
sys.path[:0] = new_sys_path
from django.core.handlers.wsgi import WSGIHandler
os.environ['DJANGO_SETTINGS_MODULE'] = '[projectname].settings'
application = WSGIHandler()

give the scripts execute permissions:

chmod +x /home/[username]/djangosites/[projectname].wsgi

and exit user login with:

exit

now you will have your root propmt at the shell:

root@servername [/home/username]#

create the apache include folder for that virtualhost:

mkdir -p /usr/local/apache/conf/userdata/std/2/[username]/[domain]/
nano /usr/local/apache/conf/userdata/std/2/[username]/[domain]/django.conf

and add:

<ifmodule mod_wsgi.c>
WSGIScriptAlias / /home/[username]/djangosites/[projectname].wsgi
WSGIDaemonProcess [username] processes=7 threads=1 display-name=%{GROUP}
WSGIProcessGroup [username]
WSGIApplicationGroup %{GLOBAL}
</ifmodule>

edit the virtualhost entry for that domain:

nano /usr/local/apache/conf/httpd.conf

and in that virtualhost add:

Include "/usr/local/apache/conf/userdata/std/2/[username]/[domain]/*.conf"

save & exit

test the apache configuration prior to reload:

service httpd configtest

Should say syntax OK at the end, if so reload the apache:

/scripts/restartsrv_httpd

And voila, you have your django instance up& running You can now add via User's cpanel media subdomain for django media files, and define the path to the subdomain root in setings.py media root can live inside public_html folder. You can also create additional subdomain for admin media and copy the admin media content to that subdoman. For example: admin-media.[domain]:

cp -r /home/[username]/virtualenv/lib/python2.6/site-packages/Django-1.2.1-py2.6.egg/django/contrib/admin/media/* /home/[username]/public_html/admin-media/

SSH port forwarding

Tue, 16 Feb 2010 20:58:36 +0000

In one of my previous post I made a tutorial how to bypass corporate firewalls and gain access into your office computer. It work well if you are at your home and you need ssh access (or any other service) to your office computer. However if the situation is reversed, and you need to access some outside service which your firewall is blocking then you would use this little tutorial with explanations. Although all this is covered in the ssh man pages, one always learn best by real life examples, so here I'll try to cover few of them.

To better explain our first problem look at the picture below:

First problem

We are located at our office computer which is behind very restrictive firewall, and we want to get to the non-standard service running on remote server.

Normally I'll use Mysql Administrator for example, to connect on my MySql database on a remote server. That communication would happen on port 3306, and for this to work Mysql Administrator must have appropriate rules set in our firewall to allow that traffic.

But what if traffic on that port is blocked?

Here is where we come to ssh port forwarding. If we have ssh access on any outside computer we can route our traffic through the tunnel and gain access to the service via standard ports.

Second problem

Ssh tunnel can be also used to establish connection from insecure networks to standard and non-standard services inside secured firewalled network as shown in picture bellow:

As you can see here, server is behind firewall and all the standard ports on that server (like http, pop, imap...) are allowed. Server is also running MySQL service which is listening on port 3306. To connect to it server's firewall should allow this incoming connection.

But what if it doesn't?

What if the network from which we are connecting is insecure and we wish to maintain our data private while communicating with our trusted, firewalled network?

Again we can solve this problem with ssh port forwarding. If we have clear and working ssh access on server or any other machine in the firewalled network, we can route our traffic through that ssh connection.

Solving the first problem

If our firewall is very restrictive (inbound and outbound), and you don't have control over it, you can use ssh port forwarding to your advantage. Essential things you will need are:

allowed ssh traffic on your restrictive firewall, port 22 by default

a remote server to which you can connect via ssh, and preferably control over that remote server's firewall.

If even port 22 is blocked in your restrictive firewall you can setup your outside ssh server to listen on some other port that is allowed through your firewall. You can than use -p switch in your ssh command to connect on your server. If you don't really know anything about ssh, you can always read ssh basics and than come back here.

So in our real example we want to connect to mysql service running on remote server and our firewall won't allow it. We have ssh access on that server so we will use it to tunnel this traffic.

To start up this tunnel this command will be used:

ssh -L 3306:localhost:3306 username@server

This will actually open up a port 3306 on our local computer listening on loopback interface through established ssh connection on to server's port 3306. If we already have mysql server running on local machine then the port 3306 is already in use, so we need to use another port on our loopback interface, so the command would look like:

ssh -L 3307:localhost:3306 username@server

Then we use our service client, in this case Mysql administrator and instruct it to connect to 127.0.0.1 at our specified port. We can use the same command to tunnel any other port and or service this way.

Extending this example

We can also use this connection method to our remote server for routing traffic to some other servers. Of course our remote server must to be able to connect to that remote service. This is usually very popular to forward traffic to some online games running on non standard ports, EvE, Warcraft, and any other game (this might produce additional lag on FPS games).

As shown on picture, for example we have outside gaming server running at port 66732, and that port is blocked in our firewall. We can use our remote server with ssh connection to establish a ssh tunnel and then route that traffic to our local computer.

To do so we would use this command:

ssh -L 66732:remote.gameserver:66732 username@our.server

On our loopback interface (127.0.0.1), this will create a listening port 66732 which will then be forwarded to remote.gameserver's port 66732 through our ssh connection on port 22. All you need to do is instruct your game client to connect on localhost. This can also be used in constructive purposes, like using your remote shell server to route traffic this way to remote mysql server on which you don't have ssh access but is available to your remote server via 3306 port. Bare in mind that your remote ssh server will have to be able to connect to remote.gameserver/mysql server on appropriate port, it your remote server have any outbound firewall rules filtering this traffic, this example will not work until you open that port.

As with reverse ssh port forwarding we can make this connection available to other computers on our LAN by specifying listening interface while establishing the tunnel. I'll go with remote gameserver example and enable our co-workers to connect via same ssh tunnel to remote gameserver without them needing to create their own tunnels.

If you have multiple network interfaces on your computer you will specify the one whit which you are connected to your co workers, but you can also enable it on all interfaces like this:

ssh -L 0.0.0.0:66732:remote.gameserver:66732 username@our.server

when you do netstat -ntl on your machine you will see it's listening on 0.0.0.0:66732. Your co-workers can now connect to your's office pc ip on that port and their connections will be also tunneled via this established ssh connection. Bare in mind that the remote.gameserver will see all the connections comming from our.server so if the remote.gameserver have any per ip connection count limit this will obviously be a problem.

Solving the second problem

How is this problem different form the first one. In essence it's not, only differences is that our firewall is permitting the non standard traffic, or we don't even have a firewall to worry about, but the server's firewalled network is very restrictive and its blocking our non standard ports. Ssh tunnel commands used in this example will be the same. However I can use this example for demonstrating why ssh tunnel can be useful for.

Say we don't have firewall and port limitations, but we are temporary on insecure network and server is on secured/trusted network. If we were to use any of the old plain test services like ftp, pop3, imap, synergy, etc... malicious hosts/users can sniff out that traffic and find out any usernames and passwords sent via plain text. Even the contents of the connection. So here's were ssh tunnel steps in again.

We can start an ssh tunnel from our insecure network to our secured server's network and tunnel any plain text/insecure traffic through it. By default ssh tunnel binds its self to loopback interface on our computer, which malicious network user doesn't have the access to, and as the ssh is encrypted, all the traffic passing through this tunnel will be plain gibberish to any malicious user sniffing our traffic on this insecure network.

Just like in one of the previous posts (secure synergy setup) we will use this tunnel to secure our traffic from eavesdropping. Ftp actually has sftp (ftp over ssh) so if you already have ssh you will want to use that instead of tunneling ftp traffic, but if for any reason you are not able to use it then this should work as well.

I'll take my example on pop3:

We establish ssh connection to secured remote server and tunnel the port 110 on our loopback interface to server's 110 through established ssh session. Then we connect with our mail client to localhost and preform plain text pop3 authentication through secure ssh tunnel.

ssh -L 110:localhost:110 user@secure.server

If you don't have access to the mail server's ssh then you can use another ssh host on secured network to route traffic with this:

ssh -L 110:mailserver:110 user@secure.server

Few useful tips

As mentioned few times before, perhaps the firewall will not allow standard ssh ports through, or your ssh server is running on different port in which case you should use -p switch with ssh. For example your ssh server is running on port 2210 then to forward pop3 traffic you would use:

ssh -p 2210 -L 110:mailserver:110 user@secure.server

You can also speed things up by using ssh keys and ssh-agent

And of course if you wish to use your ssh connection only for port forwarding an wish to put it into background you would use:

ssh -N -f -L 110:mailserver.110 user@secure.server

And from here it's all combinations of above used commands.

Have fun!

Howto create rsync server

Wed, 21 Oct 2009 19:57:21 +0000

There are tons of reasons why would one want to create a rsync server. For example you wish to backup your data to a remote server but you don't want to backup everything every time.

rsync is an open source utility that provides fast incremental file transfer. rsync is freely available under the GNU General Public License and is currently being maintained by Wayne Davison.

As you can see rsync is ideal for this. You can use it within ssh protocol, rsh and rsync itself. Creating a rsync server will allow you to create easily accessible storage server, update server for your scripts, etc.

Anyway let's get started on configuring rsync server which will serve as remote backup server.

First make sure you have tcp and udp port 873 open in your firewall. Next install rsync on your machine (if you don't have it yet), and xinetd as well.

yum install -y rsync xinetd

We will make rsync available trouh xinetd so you must enable it by editing its conf file

nano /etc/xinetd.d/rsync

edit the line saying:

disable = yes

to:

disable = no

so the entire file should look something like this:

service rsync
{
disable = no
socket_type = stream
wait = no
user = root
server = /usr/bin/rsync
server_args = --daemon
log_on_failure += USERID
}

Next we want to create rsync client username and password:

nano /etc/rsyncd.secrets

and enter a username and password in format:

username:password

yes it's plain text.

Let's create a rsync server conf file:

nano /etc/rsyncd.conf

now here enter:

#maximum allowed connections
max connections = 10
#where to log
log file = /var/log/rsync.log
timeout = 300

To create a share using a password and being able to send files to rsync server, we shoulwd add to our /etc/rsyncd.conf:

[backup]
comment = Backup place for my office computers
path = /backup/
read only = false
list = yes
uid = backup
gid = backup
hosts allow = 192.168.0.0/24 # i want to limit the rsnyc server only to this group of hosts
secrets file = /etc/rsyncd.secrets
auth users = username #enter username specified in secrets file

Now we have a rsync server module at path /backup which will allow only hosts within 192.168.0.0/24 network and users authenticated by username specified in secrets file.

To make sure this will be somewhat secure let's change permissions on rsync config files

chown root.root /etc/rsyncd.*
chmod 600 /etc/rsyncd.*

Restart the xinetd:

service xinetd restart

and voila.

Let's go test it out from one of our client hosts:

rsync rsync.server.com::
backup Backup place for my office computers

To actually backup something onto this host we would use:

rsync -avz ./ username@rsync.server.com::backup

the command would ask us for a password specified in secrets file. After successful login rsync will start to transfer files to remote machine. Next time we start it it will only transfer the differences since last time.

If you would like to script this, entering a password could be a problem. Luckily rsync offers a solution as password file.

nano /home/branko/.rsync.pass

enter your password here and chmod this file to 600 so it's only readable by you. start the rsync with following command:

rsync -avz --password-file=/home/branko/.rsync.passw ./ username@rsync.server.com::backup

To setup another share for download only we would create a read-only share without passwords. just append this to your /etc/rsyncd.conf file:

[update]
comment = update downloads
path = /home/branko/update
read only = true
list = yes
uid = branko
gid = branko
hosts allow = 192.168.0.0/24

Now you may see there is no auth user or secrets password. So when we issue the rsync command on our server again:

rsync rsync.server.com::

you will se another module available by the name update.

to rsync content from this module just use:

rsync -avz rsync.server.com::update ./