jaferrer

Opening and closing an SSH tunnel in a shell script the smart way

I recently had the following problem:

From an unattended shell script (called by Jenkins), run a command-line tool that accesses the MySQL database on another host.
That tool doesn't know that the database is on another host, plus the MySQL port on that host is firewalled and not accessible from other machines.

We didn't want to open the MySQL port to the network, but it's possible to SSH from the Jenkins machine to the MySQL machine. So, basically you would do something like

ssh -L 3306:localhost:3306 remotehost

Using Git

Global Settings

Related Setup: https://gist.github.com/hofmannsven/6814278

Related Pro Tips: https://ochronus.com/git-tips-from-the-trenches/

	-- show running queries (pre 9.2)
	SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
	FROM pg_stat_activity
	WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

	-- show running queries (9.2)
	SELECT pid, age(clock_timestamp(), query_start), usename, query
	FROM pg_stat_activity
	WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'


	#!/usr/bin/env bash

	PKGS_TO_INSTALL="cups adduser postgresql-client python python-dateutil python-decorator python-docutils python-feedparser python-imaging python-jinja2 python-ldap python-libxslt1 python-lxml python-mako python-mock python-openid python-passlib python-psutil python-psycopg2 python-babel python-pychart python-pydot python-pyparsing python-pypdf2 python-reportlab python-requests python-tz python-vatnumber python-vobject python-werkzeug python-xlwt python-yaml postgresql python-gevent python-serial python-pip python-dev localepurge vim mc mg screen iw hostapd isc-dhcp-server git rsync console-data"
	apt-get -y install ${PKGS_TO_INSTALL}

	adduser pi -s /sbin/nologin -p 'raspberry'
	cd /home/pi
	git clone -b 9.0 --no-checkout --depth 1 https://github.com/odoo/odoo.git
	cd odoo

	-- query with stored plan
	CREATE or replace FUNCTION pybench1(id int) RETURNS text AS '
	if (SD.has_key("plan")):
	plan = SD["plan"]
	else:
	plan = plpy.prepare("SELECT * FROM pagetimer pt, pagebrowser pb WHERE pt.idtimer = $1 and pt.idtimer = pb.idtimer", ["int4"])
	SD["plan"] = plan

	rec = plpy.execute(plan, [id])
	if (rec.nrows() > 0):