MySQL

The MySQL query runner allows you to connect to and query MySQL and MariaDB databases. It also includes a specialized runner for Amazon RDS MySQL instances.

Configuration

The MySQL query runner supports a comprehensive configuration to connect to MySQL databases:

{
  "host": "127.0.0.1",
  "port": 3306,
  "user": "username",
  "passwd": "password",
  "db": "database_name",
  "connect_timeout": 60,
  "charset": "utf8",
  "use_unicode": true,
  "autocommit": true,
  "use_ssl": false,
  "ssl_mode": "preferred",
  "ssl_cacert": "/path/to/ca.pem",
  "ssl_cert": "/path/to/client-cert.pem",
  "ssl_key": "/path/to/client-key.pem"
}

Configuration Schema

Parameter	Type	Required	Description
host	String	No	MySQL server hostname or IP address (default: "127.0.0.1")
port	Number	No	MySQL server port (default: 3306)
user	String	No	Username for authentication
passwd	String	No	Password for authentication
db	String	Yes	Database name to connect to
connect_timeout	Number	No	Connection timeout in seconds (default: 60)
charset	String	No	Character set (default: "utf8")
use_unicode	Boolean	No	Whether to use Unicode (default: true)
autocommit	Boolean	No	Whether to autocommit transactions (default: true)
use_ssl	Boolean	No	Whether to use SSL for connection
ssl_mode	String	No	SSL mode (options: "disabled", "preferred", "required", "verify-ca", "verify-identity")
ssl_cacert	String	No	Path to CA certificate file
ssl_cert	String	No	Path to client certificate file
ssl_key	String	No	Path to client key file
ssh_tunnel_enabled	Boolean	No	Whether to use SSH tunneling (default: false)
ssh_host	String	No	SSH bastion host address
ssh_port	Number	No	SSH port (default: 22)
ssh_username	String	No	SSH username

Amazon RDS MySQL Configuration

For Amazon RDS MySQL instances, you can use the rds_mysql type, which has a simplified configuration:

{
  "host": "myinstance.abcdefghijkl.us-west-2.rds.amazonaws.com",
  "port": 3306,
  "user": "username",
  "passwd": "password",
  "db": "database_name",
  "use_ssl": true
}

RDS MySQL Configuration Schema

Parameter	Type	Required	Description
host	String	Yes	RDS instance endpoint
port	Number	No	MySQL server port (default: 3306)
user	String	Yes	Username for authentication
passwd	String	Yes	Password for authentication
db	String	Yes	Database name to connect to
use_ssl	Boolean	No	Whether to use SSL for connection

Example Usage

from legion_query_runner.query_runner import QueryRunner

# Initialize with MySQL configuration
mysql_config = {
    "host": "mysql.example.com",
    "port": 3306,
    "user": "username",
    "passwd": "password",
    "db": "mydatabase"
}

# Create a QueryRunner instance
runner = QueryRunner('mysql', mysql_config)

# Test connection
runner.test_connection()

# Execute a query
results = runner.run_query("SELECT * FROM users LIMIT 10")

Type Mapping

The MySQL query runner maps MySQL data types to standardized types:

MySQL Type Code	Legion Type
0, 4, 5, 246	FLOAT
1, 2, 3, 8, 9, 16	INTEGER
7, 12	DATETIME
10	DATE
15, 253, 254	STRING

Schema Retrieval

The MySQL query runner extracts schema information from the INFORMATION_SCHEMA views:

# Get schema for all tables
schema = runner.get_schema()

# Get columns for a specific table
columns = runner.get_table_columns("users")

# Get column types for a specific table
column_types = runner.get_table_types("users")

Implementation Details

The MySQL query runner uses the MySQLdb (also known as mysqlclient) library to connect to MySQL databases. This library must be installed for the query runner to be enabled.

To install the required dependency:

pip install mysqlclient

The query runner also includes support for:

• Thread management for query execution
• Query cancellation
• Cross-database queries
• SSL connections
• Connection pooling

SSH Tunneling

To connect to MySQL databases behind a firewall through a bastion host:

from legion_query_runner.query_runner import QueryRunner

# Configuration with SSH tunnel
mysql_config = {
    # Database connection
    "host": "internal-mysql.example.com",  # Internal database host
    "port": 3306,
    "user": "username",
    "passwd": "password",
    "db": "mydatabase",
    
    # SSH tunnel configuration
    "ssh_tunnel_enabled": True,
    "ssh_host": "bastion.example.com",  # Public bastion host
    "ssh_port": 22,
    "ssh_username": "ssh_user"
}

# Create runner - tunnel is established automatically when needed
runner = QueryRunner('mysql', mysql_config)

# Run queries as normal
results = runner.run_query("SELECT * FROM users")

The SSH tunnel is automatically established when running queries and cleaned up afterward. SSH key authentication is configured through the application settings.