Python ssh tunnel mysql

How to SSH Tunnel to a Remote MySQL Server with Python

Like all sysadmins, I write scripts to automate routine operations. Lately, though, I have needed to write scripts that automate routine operations on a remote system, and we need the security barriers to be a little higher than in the “old days”.

We’re accessing our database through an SSH tunnel, rather than via a regular encrypted socket. (The SSH connection will eventually require key pairs, and disallow regular passwords.)

If you don’t know what SSH tunnels are, there’s an explanation below.

So, I need to create scripts that will automatically log in to the server, open a tunnel, connect to the database server through this tunnel, and then execute SQL statements. It turns out to be a little difficult… but after some effort, the following script did what I needed:

 #! /usr/bin/python import subprocess as sp import MySQLdb import traceback import sys from nbstreamreader import NonBlockingStreamReader as NBSR import os import signal try: print "Connecting to example.com" ssh_process = sp.Popen(['ssh','-L3308:localhost:3306','example.com'], bufsize=0, stdin=sp.PIPE, stdout=sp.PIPE, stderr=sp.STDOUT ) except ValueError, OSError: ssh_process.terminate() exit() nbsr = NBSR( ssh_process.stdout ) # delay until we're really logged in while ssh_process.poll()==None: output = nbsr.readline(0.5) if output: # print output.strip() # should probably run this before we try to start another one if (output.find('bind: Address already in use') != -1): print "Critical error, cannot bind to the address." print "Killing the errant process. Please run this script again." sp.call(["lsof","-iTCP@localhost:3308","-t"]) (pid,err) = sp.communicate() pid = int(pid) os.kill(pid, signal.SIGQUIT) ssh_process.terminate() exit() break if (output.find('Welcome to Ubuntu') != -1): print "SSH connection established." break try: print "Connecting to database" db = MySQLdb.connect( host='127.0.0.1', port=3308, user='abcdefgh', passwd='********', db='test_schema' ) except MySQLdb.Error as e: traceback.print_exc() ssh_process.terminate() exit() try: print "Sending query." q = """SELECT name FROM test_table""" cur = db.cursor() cur.execute( q ) name = cur.fetchone() print name[0] except MySQLdb.Error as e: traceback.print_exc() cur.close() db.close() ssh_process.terminate() print "Completed." cur.close() db.close() ssh_process.terminate() 

Note that this is test code. It’s not production code. The passwords and other information should be in configuration files, not in the code.

Next step is to turn this into a decorator, so we can create the function to perform the database operations, and wrap it with code that will transform it to execute the operations remotely.

(It’s also possible to do the encryption on the MySQL server’s socket – and require that specific certificates are provided. I’m not certain if one is better than the other.)

SSH tunnels

SSH has a feature where it can forward a local port to a specific port on the remote machine, creating an encrypted tunnel for your traffic. This is done with the -L option. The following forwards port 3308 on the local machine to port 3306 on the remote machine; 3306 is what MySQL runs on:

ssh -L3308:localhost:3306 remotemachine.com

SSH manages this connection, and when you log out of the remote machine, the tunnel is also taken down. What’s nice about this is, you don’t have a socket permanently open. It’s only available when you’re logged on. You can also tunnel anything, so unencrypted services available only on the server can be used remotely. It’s like a temporary VPN.

Here’s a diagram showing SSH and SSH with a tunnel.

The script above uses the subprocess library to execute ssh, and build the tunnel.

 ssh_process = sp.Popen(['ssh','-L3308:localhost:3306','example.com'], bufsize=0, stdin=sp.PIPE, stdout=sp.PIPE, stderr=sp.STDOUT )

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

A simple code snippet that describes how to connect to a remote database over SSH

License

deskool/connect_to_remote_database_via_ssh

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Connecting to a Remote MySQL Database using an SSH tunnel, and Python.

This tutorial will show you how to connect to a remote machine hosting a MySQL database; the connection will be made using an SSH Tunnel. We will then query the database using Python’s pymysql library .

STEP 1: From the command line, create an SSH tunnel to machine with your database:

From the command line, you will need to create a connection to the server that is running the database. Most standard MySQL instances are run on port 3306. If the MySQL instance on your server is running on a Different port, you may need to change that number.

ssh -i key.pem -fN -L 3307:localhost:3306 username@site_or_ip_address_hosting_the_database 

Next, we will install python’s pymysql package

STEP 2: Query the database from Python.

from python, run the following script, and you should be all set.

import pymysql cnx = pymysql.connect(db = 'database_name', user = 'database_username', passwd = 'database_password', host = '127.0.0.1', port = 3307, ) cursor = cnx.cursor() cursor.execute("SELECT NOW()") result = cursor.fetchall() cursor.close() cnx.close() print(result) 

About

A simple code snippet that describes how to connect to a remote database over SSH

Источник

Пример использования ssh туннеля (tunnel) для sql запроса на python

Более развернуто задача звучит так: есть удаленных сервер с доступом по ssh. на сервере запущена БД. База данных настроена по безопасности так, что запросы можно делать только с локальной машины localhost. Требуется выполнить запрос к базе данных программно. Конечно можно вручную подцепиться к серверу а потом к БД, и выполнить sql query. Можно также залить какой-то скрипт на сервер и запросы пропускать через него. Но как же выполнить SQL запрос через ssh удаленно с рабочей машины? Для этого мы воспользуемся построением SSH туннеля.
Для примера возьмем БД mysql, однако это может быть и postgres и другая БД.
Также важной особенностью будет и то, что доступ по ssh осуществляется через публичный ключ. Вариант авторизации только по логину и паролю также рассмотрим, и добавим в код.
Ключом к решению задачи является библиотека paramiko. В стандартные набор она не входит, поэтому для установки выполняем pip install paramiko . Данная библиотека содержит обширный набор функций для работы по ssh как с серверной так и с клиентской стороны.

import mysql.connector import paramiko # параметры соединения по SSH ssh_username = "ssh_username" ssh_password = "ssh_password" ssh_host = "ssh_host" ssh_port = 22 # параметры соединения по SSH при использовании авторизации по ключу ssh_username = "ssh_username" ssh_key_path = "/path/to/ssh/key" ssh_key_password = "ssh_key_password" ssh_host = "ssh_host" ssh_port = 22 # Параметры соединения с БД MySQL mysql_username = "mysql_username" mysql_password = "mysql_password" mysql_host = "127.0.0.1" mysql_port = 3306 mysql_database = "database_name" # Загружаем SSH ключ private_key = paramiko.RSAKey.from_private_key_file(ssh_key_path, password=ssh_key_password) # Открываем SSH Tunnel transport = paramiko.Transport((ssh_host, ssh_port)) transport.connect(username=ssh_username, password=ssh_password) # вместо предыдущей строки, для соединения по ключу, используем параметр pkey transport.connect(username=ssh_username, pkey=private_key) local_bind_address = (mysql_host, mysql_port) transport.start_client() # Соединяемся с MySQL через SSH Tunnel cnx = mysql.connector.connect(user=mysql_username, password=mysql_password, host=mysql_host, port=mysql_port, database=mysql_database, transport=transport) # Делаем селект запрос к базе данных cursor = cnx.cursor() query = "SELECT * FROM table_name" cursor.execute(query) for row in cursor: print(row) # Закрываем соединение с Базой данных cursor.close() cnx.close() # Закрываем ssh тоннель transport.close()

Библиотека Python Paramiko используется для безопасных (SSH) подключений к удаленным машинам. Некоторые распространенные варианты использования Paramiko включают в себя:

* Управление ключами SSH
* Передача файлов по SFTP
* Удаленное выполнение команды
* Port forwarding
* Удаленный запуск скриптов

Источник