MySQL Provider

MySQL is one of the world’s most popular open-source relational databases, powering millions of web applications. Queryly provides full support for connecting to and managing MySQL databases.

Connection String Format

The standard MySQL connection string format:

Server=localhost;Database=mydb;User=root;Password=secret

Connection Examples

Local Database
Remote Database
With SSL
Advanced

Standard Local Connection

# Basic local connection
Server=localhost;Database=myapp;User=root;Password=mypass

# With explicit port (default is 3306)
Server=localhost;Port=3306;Database=myapp;User=root;Password=mypass

# Using IP address
Server=127.0.0.1;Database=myapp;User=root;Password=mypass

Default MySQL port is 3306. You can omit the Port parameter if using the default.

Remote Server Connection

# Remote host with domain
Server=db.example.com;Database=production;User=admin;Password=secure123

# Remote host with IP
Server=192.168.1.100;Database=production;User=admin;Password=secure123

# Custom port
Server=db.example.com;Port=3307;Database=production;User=admin;Password=secure123

Ensure your MySQL server is configured to accept remote connections. Check the bind-address setting in my.cnf and verify firewall rules.

Secure Connections

# Require SSL
Server=localhost;Database=myapp;User=root;Password=mypass;SslMode=Required

# Prefer SSL (falls back to non-SSL)
Server=localhost;Database=myapp;User=root;Password=mypass;SslMode=Preferred

# Disable SSL
Server=localhost;Database=myapp;User=root;Password=mypass;SslMode=None

# With certificate verification
Server=db.example.com;Database=myapp;User=root;Password=mypass;SslMode=VerifyFull;SslCa=/path/to/ca.pem

SSL modes: None, Preferred, Required, VerifyCA, VerifyFull

Advanced Configuration

# Connection timeout
Server=localhost;Database=myapp;User=root;Password=mypass;Connection Timeout=30

# Command timeout
Server=localhost;Database=myapp;User=root;Password=mypass;Default Command Timeout=60

# Connection pooling
Server=localhost;Database=myapp;User=root;Password=mypass;Pooling=true;MaximumPoolSize=100

# Character set
Server=localhost;Database=myapp;User=root;Password=mypass;CharSet=utf8mb4

# Allow user variables
Server=localhost;Database=myapp;User=root;Password=mypass;Allow User Variables=true

# Allow batch statements
Server=localhost;Database=myapp;User=root;Password=mypass;Allow Batch=true

Adding a MySQL Connection

Interactive Mode

queryly connect add

Example session:

$ queryly connect add
Enter connection name: ProductionMySQL
Select database type: MySQL
Enter connection string: Server=localhost;Database=myapp;User=root;Password=mypass
✓ Connection test successful!
✓ Connection 'ProductionMySQL' saved

Supported Features

Connection Management - Add, test, list, and remove connections

Schema Exploration - List tables and view structures

Table Browsing - View table data with proper type handling

Query Execution - Run MySQL-specific SQL queries

Data Export - Export tables to CSV or JSON formats

Port Configuration

Default Port

MySQL uses port 3306 by default. You can omit the port in your connection string:

Server=localhost;Database=myapp;User=root;Password=mypass

Custom Port

If your MySQL server runs on a different port:

Server=localhost;Port=3307;Database=myapp;User=root;Password=mypass

Checking MySQL Port

# On the MySQL server
sudo netstat -tlnp | grep mysql

# Or check MySQL configuration
mysql -u root -p -e "SHOW VARIABLES LIKE 'port';"

Connection Options

Character Set
Timeouts
Connection Pooling

UTF-8 Support

# Use utf8mb4 for full Unicode support (including emojis)
Server=localhost;Database=myapp;User=root;Password=mypass;CharSet=utf8mb4

# Legacy utf8 (3-byte only)
Server=localhost;Database=myapp;User=root;Password=mypass;CharSet=utf8

Always use utf8mb4 for new applications. It supports the full Unicode character set, including emojis and special characters.

Connection and Command Timeouts

# Connection timeout (seconds to wait for connection)
Server=localhost;Database=myapp;User=root;Password=mypass;Connection Timeout=30

# Command timeout (seconds to wait for query execution)
Server=localhost;Database=myapp;User=root;Password=mypass;Default Command Timeout=300

# Both timeouts
Server=localhost;Database=myapp;User=root;Password=mypass;Connection Timeout=30;Default Command Timeout=300

Pool Configuration

# Enable pooling with defaults
Server=localhost;Database=myapp;User=root;Password=mypass;Pooling=true

# Custom pool size
Server=localhost;Database=myapp;User=root;Password=mypass;Pooling=true;MinimumPoolSize=0;MaximumPoolSize=100

# Disable pooling
Server=localhost;Database=myapp;User=root;Password=mypass;Pooling=false

Connection pooling is enabled by default. It improves performance by reusing connections.

Authentication

Standard Auth
No Password

Username/Password

# Root user
Server=localhost;Database=myapp;User=root;Password=mypass

# Regular user
Server=localhost;Database=myapp;User=appuser;Password=apppass

# User with special characters
Server=localhost;Database=myapp;User=app@host;Password=p@ssw0rd!

Avoid using the root account for applications. Create dedicated users with limited permissions.

Authentication Without Password

# For local development only
Server=localhost;Database=myapp;User=root

Passwordless access should only be used in local development environments. Never in production.

MySQL-Specific Features

Data Type Support

Queryly handles MySQL’s data types:

Numeric: int, bigint, decimal, float, double
Text: varchar, text, char, mediumtext, longtext
Date/Time: datetime, timestamp, date, time, year
Binary: blob, mediumblob, longblob, binary, varbinary
JSON: json (MySQL 5.7+)
Spatial: geometry, point, linestring, polygon
Enum/Set: enum, set

Query Examples

Basic Queries
MySQL-Specific
Information Queries

queryly data query ProductionMySQL

# Select queries
SQL> SELECT * FROM users LIMIT 10;
SQL> SELECT COUNT(*) FROM orders;
SQL> SELECT name, email FROM customers WHERE created_at > '2024-01-01';

# JSON operations (MySQL 5.7+)
SQL> SELECT JSON_EXTRACT(data, '$.name') as name FROM users;
SQL> SELECT * FROM orders WHERE JSON_EXTRACT(metadata, '$.status') = 'completed';

# Full-text search
SQL> SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database');

# Date functions
SQL> SELECT DATE_FORMAT(created_at, '%Y-%m') as month, COUNT(*) FROM orders GROUP BY month;

# String functions
SQL> SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

# Database size
SQL> SELECT 
       table_schema as 'Database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as 'Size (MB)'
     FROM information_schema.tables
     WHERE table_schema = DATABASE();

# Table sizes
SQL> SELECT 
       table_name,
       ROUND(((data_length + index_length) / 1024 / 1024), 2) as 'Size (MB)'
     FROM information_schema.tables
     WHERE table_schema = DATABASE()
     ORDER BY (data_length + index_length) DESC;

# Current user and database
SQL> SELECT USER(), DATABASE();

# MySQL version
SQL> SELECT VERSION();

Common Workflows

Initial Setup
Data Exploration
Analysis

# Add connection
queryly connect add
# name: MyMySQL
# type: MySQL
# connection string: Server=localhost;Database=myapp;User=root;Password=mypass

# Test connection
queryly connect test MyMySQL

# List all tables
queryly schema list MyMySQL

# View schema as tree
queryly schema tree MyMySQL

# Check table structure
queryly schema info MyMySQL users

# Browse table data
queryly data browse MyMySQL users

# Export to JSON
queryly data export MyMySQL users json

# Enter query mode
queryly data query MyMySQL

# Analyze data
SQL> SELECT DATE(created_at) as date, COUNT(*) as count
     FROM orders 
     GROUP BY DATE(created_at) 
     ORDER BY date DESC
     LIMIT 30;

SQL> SELECT status, AVG(total) as avg_total, COUNT(*) as count
     FROM orders 
     GROUP BY status;

SQL> exit

Troubleshooting

Connection refused

Error: Unable to connect to any of the specified MySQL hostsSolutions:

Verify MySQL is running: sudo systemctl status mysql
Check the port: sudo netstat -tlnp | grep 3306
Verify bind-address in /etc/mysql/my.cnf (use 0.0.0.0 for remote access)
Check firewall rules

# Start MySQL
sudo systemctl start mysql

# Check if it's listening
sudo ss -tlnp | grep 3306

Access denied

Error: Access denied for user 'root'@'localhost'Solutions:

Verify username and password
Check user privileges
Ensure user has access to the database

# Reset root password
sudo mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
mysql> FLUSH PRIVILEGES;

# Grant privileges
mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
mysql> FLUSH PRIVILEGES;

Unknown database

Error: Unknown database 'myapp'Solutions:

Verify database name (case-sensitive on Linux)
Create the database if needed

# List databases
mysql -u root -p -e "SHOW DATABASES;"

# Create database
mysql -u root -p -e "CREATE DATABASE myapp;"

# Or in MySQL prompt
mysql -u root -p
mysql> CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Too many connections

Error: Too many connectionsSolutions:

Close unused connections
Increase max_connections in my.cnf
Check for connection leaks

# Check current connections
mysql -u root -p -e "SHOW PROCESSLIST;"

# Check max connections
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

# Set max connections (requires restart)
# Edit /etc/mysql/my.cnf:
# [mysqld]
# max_connections = 200

SSL connection errors

Error: SSL connection errorSolutions:

Use SslMode=Preferred or SslMode=None for local development
Verify SSL is enabled on the server
Check certificate paths

# For local development
Server=localhost;Database=myapp;User=root;Password=mypass;SslMode=None

# Check SSL status
mysql -u root -p -e "SHOW VARIABLES LIKE 'have_ssl';"

Best Practices

Use utf8mb4 for character set (full Unicode support)

Create dedicated users - don’t use root for applications

Enable SSL for remote connections

Connection pooling - enabled by default for better performance

Regular backups - use mysqldump for database backups

Avoid:

Using the root account for application connections
Passwordless connections in production
Storing credentials in plain text
Leaving default ports open to the internet

Working with Commands

MySQL works with all Queryly commands:

# Connection management
queryly connect add
queryly connect list
queryly connect test MyMySQL
queryly connect remove MyMySQL

# Schema exploration
queryly schema list MyMySQL
queryly schema info MyMySQL users
queryly schema tree MyMySQL

# Data operations
queryly data browse MyMySQL users
queryly data query MyMySQL
queryly data export MyMySQL users csv
queryly data export MyMySQL users json

Command Reference

See the complete command reference for all available options

Next Steps

Schema Exploration

Learn how to explore MySQL schemas

Data Operations

Browse and query table data

Providers Overview

Compare all database providers

Connection Management

Manage your database connections

Get Started

Commands

Database Providers

Guides

Development

Connection String Format

Connection Examples

Standard Local Connection

Remote Server Connection

Secure Connections

Advanced Configuration

Adding a MySQL Connection

Interactive Mode

Supported Features

Port Configuration

Default Port

Custom Port

Checking MySQL Port

Connection Options

UTF-8 Support

Connection and Command Timeouts

Pool Configuration

Authentication

Username/Password

Authentication Without Password

MySQL-Specific Features

Data Type Support

Query Examples

Common Workflows

Troubleshooting

Best Practices

Working with Commands

Command Reference

Next Steps

Schema Exploration

Data Operations

Providers Overview

Connection Management

Build docs developers (and LLMs) love

Get Started

Commands

Database Providers

Guides

Development

​Connection String Format

​Connection Examples

​Standard Local Connection

​Remote Server Connection

​Secure Connections

​Advanced Configuration

​Adding a MySQL Connection

​Interactive Mode

​Supported Features

​Port Configuration

​Default Port

​Custom Port

​Checking MySQL Port

​Connection Options

​UTF-8 Support

​Connection and Command Timeouts

​Pool Configuration

​Authentication

​Username/Password

​Authentication Without Password

​MySQL-Specific Features

​Data Type Support

​Query Examples

​Common Workflows

​Troubleshooting

​Best Practices

​Working with Commands

Command Reference

​Next Steps

Schema Exploration

Data Operations

Providers Overview

Connection Management

Build docs developers (and LLMs) love

Connection String Format

Connection Examples

Standard Local Connection

Remote Server Connection

Secure Connections

Advanced Configuration

Adding a MySQL Connection

Interactive Mode

Supported Features

Port Configuration

Default Port

Custom Port

Checking MySQL Port

Connection Options

UTF-8 Support

Connection and Command Timeouts

Pool Configuration

Authentication

Username/Password

Authentication Without Password

MySQL-Specific Features

Data Type Support

Query Examples

Common Workflows

Troubleshooting

Best Practices

Working with Commands

Next Steps