python-mysqlHow can I connect to a MySQL database over SSH using Python?
You can connect to a MySQL database over SSH using Python with the help of a library such as Paramiko.
First, you need to install Paramiko:
pip install paramiko
Then, you can create a SSH client and connect to the server:
import paramiko
hostname = 'example.com'
port = 22
username = 'username'
password = 'password'
client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
client.connect(hostname, port, username, password)
After the connection is established, you can use the Paramiko client to execute commands on the server, such as mysql -u username -p
to connect to the MySQL database.
You can also use a library such as MySQL Connector to execute SQL queries directly on the database. To do this, you need to create a SSH tunnel and pass the tunnel information to MySQL Connector:
import paramiko
import mysql.connector
hostname = 'example.com'
port = 22
username = 'username'
password = 'password'
client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
client.connect(hostname, port, username, password)
transport = client.get_transport()
local_port = 3306
dest_addr = '127.0.0.1'
dest_port = 3306
channel = transport.open_channel("direct-tcpip", (dest_addr, dest_port), ('127.0.0.1', local_port))
db = mysql.connector.connect(host='127.0.0.1', port=local_port, user='username', password='password', database='database')
After the tunnel is established, you can use the db
object to execute SQL queries on the database.
List of code parts with detailed explanation
pip install paramiko
- Installs Paramiko library.import paramiko
- Imports the Paramiko library.client = paramiko.SSHClient()
- Creates a SSH client.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
- Sets the missing host key policy to AutoAddPolicy.client.connect(hostname, port, username, password)
- Connects to the server.transport = client.get_transport()
- Gets the transport from the client.channel = transport.open_channel("direct-tcpip", (dest_addr, dest_port), ('127.0.0.1', local_port))
- Opens a SSH channel.db = mysql.connector.connect(host='127.0.0.1', port=local_port, user='username', password='password', database='database')
- Connects to the MySQL database.
Relevant links
- Paramiko documentation: https://www.paramiko.org/
- MySQL Connector documentation: https://dev.mysql.com/doc/connector-python/en/
More of Python Mysql
- How do I download MySQL-Python 1.2.5 zip file?
- How can I connect Python to a MySQL database?
- How can I use Python to interact with a MySQL database using YAML?
- ¿Cómo conectar Python a MySQL usando ejemplos?
- How do I use Python to query MySQL with multiple conditions?
- How can I install the MySQL-Python (Python 2.x) module?
- How can I access MySQL using Python?
- How to compile a MySQL-Python application for x86_64-Linux-GNU-GCC?
- How can I use Python to make a MySQL request?
- How do I execute a query in MySQL using Python?
See more codes...