Connect MySQL to AnalytAI

Connect MySQL Database to AnalytAI

Get your MySQL database connected to AnalytAI in minutes! This guide covers two secure connection methods - choose the one that fits your setup.

⏱️ Time to complete: 5-15 minutes
🔧 Difficulty: Beginner to Intermediate
🔒 Security: Read-only access with dedicated users

Prerequisites

Before connecting MySQL to AnalytAI, ensure you have:

✅ MySQL server running (version 5.7 or higher)
✅ Admin access to create users and modify settings
✅ Network access to your MySQL server
✅ Database name and credentials ready

Connection Types

AnalytAI supports two main ways to connect to MySQL:

Cloud/Remote Server - Direct connection to hosted MySQL
Local Computer with VPN - Secure access via Tailscale VPN

Setup 1: Cloud or Remote MySQL Server

Perfect for databases hosted on cloud providers (AWS, Google Cloud, Azure, etc.) or any remote server.

Step 1: Create a Secure Read-Only User

Connect to your MySQL server as an admin user and run these commands:

-- Create a dedicated user for AnalytAI (read-only access only)
CREATE USER 'analytai_user'@'%' IDENTIFIED BY 'YourSecurePassword123!';

-- Grant read-only permissions to your database
GRANT SELECT ON your_database_name.* TO 'analytai_user'@'%';

-- Apply the permission changes
FLUSH PRIVILEGES;

-- Verify the user was created correctly
SHOW GRANTS FOR 'analytai_user'@'%';

Security Note: Never use your root/admin password. Create a dedicated user with minimal required permissions.

Step 2: Add to AnalytAI

In the AnalytAI application, enter these connection details:

Host: Your server address (e.g., mysql.example.com or 192.168.1.100)
Port: 3306 (default MySQL port)
Username: analytai_user
Password: YourSecurePassword123!
Database: your_database_name

Step 3: Test Connection

AnalytAI will test the connection automatically. If successful, you’ll see your database tables and can start querying!

Setup 2: Local Computer with Tailscale VPN

Best for accessing MySQL running on your local computer from anywhere securely. Uses VPN technology to create a secure connection.

Step 1: Install Tailscale

Download and install Tailscale on your computer:

Download: Visit tailscale.com/download and download for your operating system
Install: Run the installer and follow the setup wizard
Don’t login yet: When Tailscale opens, close it without logging in

Step 2: Get Your VPN Key

Contact AnalytAI support team to receive your personal authentication key. We’ll provide this securely.

Step 3: Connect to AnalytAI Network

Open Command Prompt or PowerShell as Administrator and connect:

# Join AnalytAI's secure network
tailscale up --login-server=https://headscale.byvent.com --authkey=YOUR_KEY_HERE --accept-routes

# Check if you're connected
tailscale status

# Get your VPN IP address
tailscale ip -4

Expected output: Something like 100.64.0.5 - this is your secure VPN address.

Step 4: Set Up MySQL User for VPN

Create a MySQL user that can connect through the VPN:

# Connect to MySQL as admin
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
.\mysql.exe -u root -p

Run these SQL commands:

-- Create dedicated user for VPN connections (read-only)
CREATE USER 'analytai_user'@'100.64.%' IDENTIFIED BY 'YourSecurePassword123!';

-- Grant read-only access to your database
GRANT SELECT ON your_database_name.* TO 'analytai_user'@'100.64.%';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify user creation
SHOW GRANTS FOR 'analytai_user'@'100.64.%';

Step 5: Configure MySQL for Network Access

Edit MySQL configuration to accept connections from outside:

# Open MySQL config file as Administrator
notepad "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"

Find and change this line:

# Change FROM:
bind-address = 127.0.0.1

# Change TO:
bind-address = 0.0.0.0

Save the file and restart MySQL:

Restart-Service MySQL80

Step 6: Configure Firewall

Allow VPN connections to reach MySQL:

# Create firewall rule for VPN access
New-NetFirewallRule -DisplayName "MySQL VPN Access" -Direction Inbound -LocalPort 3306 -Protocol TCP -RemoteAddress 100.64.0.0/10 -Action Allow

Step 7: Connect in AnalytAI

Use your VPN IP address in the connection settings:

Host: Your Tailscale IP (from Step 3, e.g., 100.64.0.5)
Port: 3306
Username: analytai_user
Password: YourSecurePassword123!
Database: your_database_name

Connection Checklist ✅

Before testing your connection, verify:

MySQL service is running
Tailscale is connected (tailscale status shows “Tailscale is up”)
You have your VPN IP (tailscale ip -4)
Firewall rule is active
MySQL user exists and has correct permissions

Troubleshooting Guide

Having trouble connecting? Follow these steps in order to identify and fix common issues.

🔍 Quick Connection Tests

Test 1: Is MySQL Running?

# Check if MySQL service is running
Get-Service MySQL*

# Or check if port 3306 is listening
netstat -an | findstr 3306

Test 2: Can You Connect Locally?

# Try connecting to MySQL locally first
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
.\mysql.exe -u root -p -e "SELECT 1 as test;"

Test 3: Verify Your User Exists

-- Connect to MySQL and check users
SELECT User, Host FROM mysql.user WHERE User LIKE '%analytai%';
SHOW GRANTS FOR 'analytai_user'@'%';

🚨 Common Issues & Solutions

❌ “Connection Refused” or “Can’t Connect”

For Cloud/Remote Servers:

Firewall blocking port 3306: Ask your server admin to allow port 3306 from AnalytAI’s IP ranges
MySQL not accepting remote connections: Check bind-address in my.cnf/my.ini is not set to 127.0.0.1
Wrong host permission: User might need 'user'@'%' instead of 'user'@'specific_ip'

For Local VPN Setup:

Tailscale not connected: Run tailscale status - should show “Tailscale is up”
Wrong IP address: Use tailscale ip -4 to get your current VPN IP
Firewall blocking: Ensure the firewall rule was created successfully

❌ “Access Denied” Errors

Wrong username/password: Double-check credentials (case-sensitive!)
User doesn’t exist: Run the user creation commands again
Wrong host restriction: For cloud servers use '%', for VPN use '100.64.%'
Password expired: MySQL passwords can expire - reset if needed

❌ “Unknown Database” Error

Typo in database name: Check the exact database name in MySQL
Wrong database selected: User might not have access to that specific database
Database doesn’t exist: Verify the database was created and spelled correctly

❌ VPN-Specific Issues

Tailscale won’t connect: Try tailscale down then tailscale up again
Auth key expired: Contact AnalytAI support for a new key
Network conflicts: Restart Tailscale service if having issues

🔧 Advanced Diagnostics

Check MySQL Error Logs:

# View recent MySQL errors
Get-Content "C:\ProgramData\MySQL\MySQL Server 8.0\Data\*.err" -Tail 20

Test Connection with MySQL Client:

# Test with mysql command line client
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
.\mysql.exe -h YOUR_HOST -P 3306 -u analytai_user -p YOUR_DATABASE -e "SELECT 1;"

Check Network Connectivity:

# Test if port 3306 is reachable
Test-NetConnection -ComputerName YOUR_HOST -Port 3306

📞 Still Having Issues?

Before contacting support, please provide:

Error message: Exact error you’re seeing
Setup type: Cloud server or local VPN?
Test results: Output from the diagnostic commands above
MySQL version: SELECT VERSION();
Operating system: Windows version and edition

Contact AnalytAI Support:

Email: support@analytai.com
Include your diagnostic information for faster resolution!

Security Best Practices

🔒 Important Security Notes:

Never use root user for application connections
Use strong, unique passwords for database users
Limit user permissions to SELECT only when possible
Regularly rotate passwords and monitor access logs
Keep MySQL updated with latest security patches
Use VPN for local databases instead of exposing them directly

Success Indicators ✅

Your MySQL connection is working when you can:

✅ See your database tables in AnalytAI
✅ Run queries without connection errors
✅ View data in your tables and columns
✅ Create visualizations from your MySQL data

🎉 Setup Complete! Your MySQL database is now connected to AnalytAI. Start exploring your data! 🚀