MySQL & MariaDB

MySQL & MariaDB Integration

Connect Querri to your MySQL or MariaDB database to run queries, analyze data, and create visualizations.

Overview

The MySQL connector supports:

• MySQL 5.7+: All modern MySQL versions
• MySQL 8.0+: Full support including new features
• MariaDB 10.x: All MariaDB versions
• AWS RDS MySQL: Managed MySQL on AWS
• Azure Database for MySQL: Managed MySQL on Azure
• Google Cloud SQL for MySQL: Managed MySQL on GCP

Setup

Prerequisites

• MySQL or MariaDB database server
• Database credentials with appropriate permissions
• Network access to the database

IP Whitelisting

Important: For cloud-hosted databases or databases behind firewalls, whitelist the following IP address:

18.189.33.77

This is Querri’s outbound IP address. Add this to your database firewall rules or security group.

Creating the Connector

1. Navigate to Connectors

   • Go to Settings → Connectors
   • Click “Add Connector”

2. Select MySQL

   • Choose “MySQL” from the database connectors

3. Configure Connection

Host: your-database-host.com
Port: 3306 (default MySQL port)
Database: your_database_name
Username: your_username
Password: your_password

Optional Settings:

• SSL: Enable for encrypted connections
• Charset: Default is utf8mb4
• Connection Timeout: Default is 30 seconds

4. Test Connection

   • Click “Test Connection” to verify
   • If successful, click “Save”

5. Use in Projects

   • Create a new step → “Database Query”
   • Select your MySQL connector
   • Write your SQL query

Network Configuration

AWS RDS Security Groups

Add inbound rule:

Type: MySQL/Aurora
Protocol: TCP
Port: 3306
Source: 18.189.33.77/32
Description: Querri Access

Azure MySQL Firewall

Add firewall rule:

Rule Name: Querri
Start IP: 18.189.33.77
End IP: 18.189.33.77

Google Cloud SQL

Add authorized network:

Name: Querri
Network: 18.189.33.77/32

Database User Setup

Read-Only User (Recommended)

Create a dedicated read-only user for Querri:

-- Create user
CREATE USER 'querri_readonly'@'%'
IDENTIFIED BY 'secure_password_here';

-- Grant read permissions
GRANT SELECT ON your_database.* TO 'querri_readonly'@'%';

-- Apply changes
FLUSH PRIVILEGES;

Read-Write User (If Needed)

For data updates or inserts:

-- Create user
CREATE USER 'querri_readwrite'@'%'
IDENTIFIED BY 'secure_password_here';

-- Grant read-write permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'querri_readwrite'@'%';

-- Apply changes
FLUSH PRIVILEGES;

Verify User Permissions

-- Show user grants
SHOW GRANTS FOR 'querri_readonly'@'%';

-- Test user can connect
mysql -u querri_readonly -p -h your-host your_database

Example Queries

Basic SELECT

SELECT
    customer_id,
    customer_name,
    email,
    created_at
FROM customers
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Aggregate Query

SELECT
    DATE_FORMAT(order_date, '%Y-%m') as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

JOIN Query

SELECT
    c.customer_name,
    c.email,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.customer_name, c.email
HAVING COUNT(o.order_id) > 0
ORDER BY lifetime_value DESC
LIMIT 50;

Subquery Example

SELECT
    product_name,
    category,
    price,
    (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category) as avg_category_price
FROM products p1
WHERE price > (
    SELECT AVG(price)
    FROM products p3
    WHERE p3.category = p1.category
)
ORDER BY category, price DESC;

Performance Optimization

Use Indexes

Ensure frequently queried columns have indexes:

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_customers_email ON customers(email);

Check Index Usage

SHOW INDEX FROM orders;

-- See query execution plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Limit Result Sets

Always use LIMIT for large tables:

SELECT * FROM large_table
LIMIT 1000;

Use Query Parameters

For repeated queries with different values:

SELECT * FROM orders
WHERE customer_id = {{customer_id}}
AND order_date BETWEEN {{start_date}} AND {{end_date}};

Common Use Cases

Daily Sales Report

SELECT
    DATE(order_date) as date,
    COUNT(*) as orders,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY
GROUP BY DATE(order_date)
ORDER BY date DESC;

Customer Segmentation

SELECT
    CASE
        WHEN total_orders >= 10 THEN 'VIP'
        WHEN total_orders >= 5 THEN 'Regular'
        ELSE 'New'
    END as segment,
    COUNT(*) as customer_count,
    AVG(lifetime_value) as avg_ltv
FROM (
    SELECT
        c.customer_id,
        COUNT(o.order_id) as total_orders,
        COALESCE(SUM(o.total_amount), 0) as lifetime_value
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
) as customer_stats
GROUP BY segment;

Inventory Alert

SELECT
    product_name,
    current_stock,
    reorder_level,
    supplier_name,
    CASE
        WHEN current_stock = 0 THEN 'OUT OF STOCK'
        WHEN current_stock <= reorder_level THEN 'REORDER NEEDED'
        WHEN current_stock <= reorder_level * 1.5 THEN 'LOW STOCK'
        ELSE 'OK'
    END as status
FROM inventory
LEFT JOIN suppliers ON inventory.supplier_id = suppliers.supplier_id
WHERE current_stock <= reorder_level * 2
ORDER BY current_stock ASC;

Troubleshooting

Connection Refused

Problem: Can’t connect to MySQL server

Solutions:

1. Verify IP 18.189.33.77 is whitelisted
2. Check MySQL is running: systemctl status mysql
3. Verify bind-address allows remote connections
4. Check firewall rules
5. Verify host and port are correct

Access Denied

Problem: Access denied for user

Solutions:

1. Double-check username and password
2. Verify user exists: SELECT user, host FROM mysql.user;
3. Check user permissions: SHOW GRANTS FOR 'username'@'host';
4. Ensure user is created with correct host (% for any host)
5. Run FLUSH PRIVILEGES; after grant changes

Unknown Database

Problem: Unknown database error

Solutions:

1. Verify database name is correct
2. List databases: SHOW DATABASES;
3. Ensure user has access to database
4. Check for typos in database name

Too Many Connections

Problem: Too many connections error

Solutions:

1. Check current connections: SHOW PROCESSLIST;
2. Increase max_connections: SET GLOBAL max_connections = 200;
3. Close idle connections
4. Optimize connection pooling settings

Query Timeout

Problem: Query takes too long

Solutions:

1. Add indexes to queried columns
2. Use LIMIT to reduce result set
3. Optimize with EXPLAIN
4. Increase wait_timeout and interactive_timeout
5. Consider creating summary tables

Security Best Practices

Use Read-Only Credentials

Create dedicated read-only users for analytics to prevent accidental data modification.

Enable SSL

For production databases, enable SSL:

-- Require SSL for user
ALTER USER 'querri_readonly'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

In connector settings:

SSL: Enabled

Restrict Host Access

Limit user to specific hosts:

-- Instead of '%', use specific IP
CREATE USER 'querri_readonly'@'18.189.33.77'
IDENTIFIED BY 'password';

Use Strong Passwords

-- MySQL 8.0+ password validation
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 16;

Regular Audits

Monitor database access:

-- View current connections
SELECT * FROM information_schema.PROCESSLIST
WHERE USER = 'querri_readonly';

-- Enable general query log (for debugging only)
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- View logged queries
SELECT * FROM mysql.general_log
WHERE user_host LIKE '%querri%'
ORDER BY event_time DESC
LIMIT 100;

MySQL vs MariaDB

The connector works with both, but note these differences:

MySQL 8.0 Features

• Window functions
• CTEs (WITH clause)
• JSON functions
• Better optimizer

MariaDB Features

• Sequence support
• System-versioned tables
• Different JSON implementation

Connection Pooling

Querri uses connection pooling:

• Min connections: 1
• Max connections: 10
• Idle timeout: 5 minutes
• Reuse: Connections reused across queries

MySQL Versions

Tested and supported:

• MySQL 8.0.x ✓
• MySQL 5.7.x ✓
• MySQL 5.6.x ✓ (limited support)
• MariaDB 10.11.x ✓
• MariaDB 10.6.x ✓
• MariaDB 10.5.x ✓
• MariaDB 10.4.x ✓

Support

MySQL Resources

• MySQL Documentation: dev.mysql.com/doc
• MySQL Forums: forums.mysql.com
• MariaDB Documentation: mariadb.com/kb

Querri Support

• Email: support@querri.com
• Documentation: /guides/connectors/

Next Steps

• Data Connectors Guide
• Working with Data
• Creating Projects