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:
- OpenSSH Client (Putty on Windows)
- Gnucash
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:
- None Found
Best compliments for your translation, and many thanks for sharing 🙂
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.
Good idea, thanks for the feedback Sebastian
The error is because gnucash attempts to use a unix domain socket to connect to mysqld when the string “localhost” is used for hostname. Change the string “hostname” to “127.0.0.1” and it will use tcp/ip.
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 😉
Not sure why would I need to keep my budget on-line… 😕
Perhaps on cloud would be better as title 😛 ?
Basically to access them from different computer, if you don’t have this need, just keep them without DB as flat file.
“mysql” does not imply “on-line.” It is easily possible to run mysqld on localhost. Why would one do this? Because mysql provides excellent ACID guarantees.
How to have an accountant perform accounting/auditing duties in this case?
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
Thanks for the feedback
How to convert an existing file based storage gnucash account to a (postgresql) based storage gnucash acount?
Otherwise, very handy information!
Thank for this guide.
Im using ubuntu on server side and MAC OSx on client side.
I’ve followed the steps but my client (mac) side gnucash only has xml and sqlite3 database options.
Could there be some other dependancy missing on the OSX side?
thank you
daz
I believe you’re missing the database connection dependencies that have to be installed on the client side.
I believe you’re missing the SQL backend. You’ll have to build the drivers yourself.
Refer to
http://wiki.gnucash.org/wiki/MacOSX/Quartz#Building_Libdbi_Drivers
I seem to be having some issues getting the GNUcash program to access the data on the remote server. The error mysql://’Username’@127.0.0.1:3307/database name experienced an error or encountered bad or corrupt data. The Ssh connection works and I can access the database from the server. Any ideas?
script to open gnucash automatically from a ssh tunnel
copy the original /usr/bin/gnucash to /usr/bin/gnucashbin
and then create a new /usr/bin/gnucash with the source bellow and give it permission to execute.
voa lá!
—-
#!/bin/bash
CTRL_SOCKET=$HOME/.gnucash_ssh_tunnel
SERVER=”postgresserveronsshserver:5432″
LISTEN_PORT=5432
SSH_SERVER=”[email protected]”
SSH_PORT=22
ssh -M -S $CTRL_SOCKET -fnNT -L $LISTEN_PORT:$SERVER $SSH_SERVER -p $SSH_PORT
/usr/bin/gnucashbin
ssh -S $CTRL_SOCKET -O exit $SSH_SERVER -p $SSH_PORT
ow, I forgot..
to work properly, you need create a ssh key with “ssh-keygen” without a passphrase and copy the id file to the server with “ssh-copy-id [email protected]“