Nov 022011
 

Article by Jimmy, first published on his Blog and the BGLUG in Italian

Gnucash  is a great tool to keep the budget of the family, and from the version 2.4.0 supports connection to a database, SQLite, MySQL or PostgreSQL for data storage. Using a local database, however, tie to a specific computer for its use. Instead I want to be able to open a GnuCash session from any computer and connect to a single, remote, database.

Enable remote connections to the database from the whole net is very unsafe, but a SSH tunnel gives me a lightweight and reliable alternative .


To implement this i’ve used:

On server side:

On client side:

All these software can run on either Linux, Windows and Mac, which offers great flexibility. I have a Linux server, at home, connected to internet, so for me the whole thing has been very simple, but i suppsoe that even a not too expensive hosting offer sufficient support to implement this.

Server

The configuration of the SSH server does not require anything special, just make sure that your firewall is properly configured to allow access to port 22, or whatever you have set. If you want more security i suggest to use authentication via keys, as described here.

The next step is to create the database and the user who will use it. We will create a SSH tunnel, so you can also use phpMyAdmin remotely as explained for example here, but the procedure is so simple that I decided to do it from the terminal. After obtaining access to the server give the following commands::

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4012
Server version: 5.1.49-3 (Debian)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> create database gnucash;
Query OK, 1 row affected (0.00 sec)
 
mysql> grant all on gnucash.* to gnucash@localhost identified by 'PASSWORD' with grant option;
Query OK, 0 rows affected (0.12 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.13 sec)
 
mysql> exit
Bye
#

This means that, after gaining root access to mysql i create the database gnucash and with the GRANT command i create the user gnucash, i give to him permissions for the use of the database and sets the password for access to the database. In GnuCash documentation I have not found anything about the necessary permissions you have to set for use the program, then check them all, although probably you can limit access and improve safety.

Client

Before starting GnuCash on any client you must create an SSH tunnel, which is quite simply to do on any platform.

OpenSSH Client (Linux e Mac)

From a terminal run:

# ssh -L 3307:localhost:3306 -i /media/USB/private.key user@mysite.com

the -i option specifies the location of the private key for authentication, if necessary, that I keep onto my USB stick, while the -L option is used to create the tunnel to my server which is located in mysite.com, specifying three parameters:

3307 is the local port to which I’ll have to connect to enter the tunnel

localhost is the address where the MySQL server is available. Since my tunnel “exit” on the same machine the address is localhost.

3306 is the port to use to connect to the MySQL server

Putty (Windows)

In the Putty configuration window enter the following information:

Session: Enter the host name, mysite.com, and port of the SSH server, 22 or another.

Connection-> Data: in login details I can put the username for the SSH connection, so that It’s not asked every time.

Connection-> SSH-> Auth: Here i can specify the location of the file containing the private key for authentication, if necessary.

Connection-> SSH-> Tunnels: in source port i insert the local port access, and in the destination i insert the address of the MySQL server and the destination port with the syntax localhost:3306

Before opening the connection, you can return to Session, and save the parameters of the session to call them again in a future session. I can click now on Open, and after entering the password, the prompt of the terminal will indicate that the tunnel is active.

GnuCash

Now I can run GnuCash and open a new file indicating as format of the data mysql, as the host machine the one from which we opened the tunnel and the entrance port, in our case 127.0.0.1:3307, the database name, gnucash, the name of the MySQL user, gnucash, and the PASSWORD. The program creates all necessary tables automatically depending on the structure of the balance indicated by us.

I used the IP address because on my linux box i got errors using the host name, for reasons unknown to me, if I point to localhost: 3307 I get an error and the connection aborts. On Windows machines it works with both syntaxes.

Now i’m ready to enter the data. Whenever I want to edit them, from anywhere i’m at the moment, i can simply open the tunnel and from GnuCash open the existing file entering the same parameters as above.

If you use a private key is not advisable to scatter it on every machine you work on, it is practical and safe enough to keep it on a USB stick where you can also put Putty, which requires no installation and takes up very little space.

I wish a good budget to all :)

Popular Posts:

flattr this!

  11 Responses to “Online Budget with Gnucash + MySQL + SSH”

  1. Best compliments for your translation, and many thanks for sharing :)

  2. Hi,
    good tutorial… for the localhost problem on linux, is likely to be related to IPv6 for localhost which is translated as ::1, if you edit the file resolve.conf reversing the order of localhost IPv6 and IPv4 should works.

  3. Nice article.

    1) Will this setup also work if you replace mysql with postgresql?
    2) How to move ahead if you already got a gnucash traditional file and then want to move to a relational database storage? Just using “save as” will do the trick?

    • 1) yes sure.
      2) The one you suggested could be a good solution, please try it and let us know ;)

  4. Not sure why would I need to keep my budget on-line… :-?

    • Perhaps on cloud would be better as title :P ?
      Basically to access them from different computer, if you don’t have this need, just keep them without DB as flat file.

  5. How to have an accountant perform accounting/auditing duties in this case?

  6. Perfect guide thankyou very much! I thought there must be a way to tunnel into mysql via ssh just needed a nice guide :-)

    I’m going to pop a small guide here as well, I installed MySQL and GNUCash on Debian 6.0.3 and it didn’t allow me to choose MySQL at all for new setups, so here is a guide to install with support for MySQL:

    wget http://downloads.sourceforge.net/sourceforge/gnucash/gnucash-2.4.9.tar.gz
    tar xvfz gnucash-2.4.9.tar.gz
    sudo aptitude build-dep gnucash
    sudo aptitude install texinfo subversion intltool libdbi0-dev libdbd-{sqlite3,pgsql,mysql} guile-1.8 guile-1.8-dev doxygen libdbi0-dev libdbd-{sqlite3,pgsql,mysql}
    cd /usr/share/guile/1.8
    sudo ln -s ../../slib slib
    sudo guile -c “(use-modules (ice-9 slib)) (require ‘new-catalog)”
    cd ~/gnucash-2.4.0/
    ./configure –prefix=$HOME/stable/gnucash –enable-debug –enable-doxygen –enable-error-on-warning –enable-compile-warnings –enable-dbi
    make all install

    and finally, run it

    ~/stable/gnucash/bin/gnucash

  7. How to convert an existing file based storage gnucash account to a (postgresql) based storage gnucash acount?
    Otherwise, very handy information!

 Leave a Reply

(required)

(required)


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>