Getting Started: Connect Your First Datasource - MySQL Practical Tutorial

This article will guide you step-by-step to connect your first MySQL datasource to AskTable, enabling you to complete the entire process from zero to usable within 30 minutes.

1. Preparation

1. What Do You Need?

Required:

•✅ AskTable account (Register here)
•✅ MySQL database (version 5.7+)
•✅ Database administration rights (ability to create users)

Optional:

•📊 Test data (we provide examples if you don't have any)
•🔧 MySQL client tools (such as MySQL Workbench, DBeaver)

Time: 30 minutes

2. Environment Check

Check MySQL version:

mysql --version
# Example output: mysql  Ver 8.0.32 for Linux on x86_64

Check if MySQL is running:

# Linux/Mac
sudo systemctl status mysql

# Or
ps aux | grep mysql

Check network connectivity:

# Test local connection
mysql -h localhost -u root -p

# Test remote connection
mysql -h your_host -P 3306 -u root -p

2. Create Test Database (Optional)

If you don't have a database yet, you can create a test database.

1. Create Database

-- Connect to MySQL
mysql -u root -p

-- Create database
CREATE DATABASE demo_shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Use database
USE demo_shop;

2. Create Example Tables

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'User ID',
    name VARCHAR(100) NOT NULL COMMENT 'User Name',
    email VARCHAR(100) COMMENT 'Email',
    phone VARCHAR(20) COMMENT 'Phone Number',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time'
) COMMENT='Users Table';

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Product ID',
    name VARCHAR(200) NOT NULL COMMENT 'Product Name',
    category VARCHAR(50) COMMENT 'Product Category',
    price DECIMAL(10,2) NOT NULL COMMENT 'Price',
    stock INT DEFAULT 0 COMMENT 'Stock',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time'
) COMMENT='Products Table';

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Order ID',
    user_id INT NOT NULL COMMENT 'User ID',
    product_id INT NOT NULL COMMENT 'Product ID',
    quantity INT NOT NULL COMMENT 'Quantity',
    total_amount DECIMAL(10,2) NOT NULL COMMENT 'Total Amount',
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'Order Status',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) COMMENT='Orders Table';

3. Insert Test Data

-- Insert users
INSERT INTO users (name, email, phone) VALUES
('Zhang San', 'zhangsan@example.com', '13800138000'),
('Li Si', 'lisi@example.com', '13800138001'),
('Wang Wu', 'wangwu@example.com', '13800138002');

-- Insert products
INSERT INTO products (name, category, price, stock) VALUES
('iPhone 15', 'Phone', 5999.00, 100),
('MacBook Pro', 'Computer', 12999.00, 50),
('AirPods Pro', 'Earphones', 1999.00, 200);

-- Insert orders
INSERT INTO orders (user_id, product_id, quantity, total_amount, status) VALUES
(1, 1, 1, 5999.00, 'paid'),
(1, 3, 2, 3998.00, 'paid'),
(2, 2, 1, 12999.00, 'pending'),
(3, 1, 1, 5999.00, 'shipped');

3. Create Read-Only User

For security, we create a read-only user for AskTable.

1. Create User

-- Create read-only user
CREATE USER 'asktable_readonly'@'%' IDENTIFIED BY 'your_strong_password_here';

-- If allowing only specific IP access
CREATE USER 'asktable_readonly'@'123.456.789.0' IDENTIFIED BY 'your_strong_password_here';

Password Requirements:

•At least 12 characters
•Contains uppercase and lowercase letters
•Contains numbers
•Contains special characters

2. Grant Permissions

-- Grant read-only permissions for demo_shop database
GRANT SELECT ON demo_shop.* TO 'asktable_readonly'@'%';

-- Grant permission to view table structures
GRANT SHOW VIEW ON demo_shop.* TO 'asktable_readonly'@'%';

-- Refresh permissions
FLUSH PRIVILEGES;

3. Verify Permissions

-- View user permissions
SHOW GRANTS FOR 'asktable_readonly'@'%';

-- Should see similar output:
-- GRANT SELECT, SHOW VIEW ON `demo_shop`.* TO `asktable_readonly`@`%`

4. Test Connection

# Test if new user can connect
mysql -h localhost -u asktable_readonly -p demo_shop

# Test query
SELECT * FROM users LIMIT 1;

# Test write (should fail)
INSERT INTO users (name) VALUES ('test');
# Should report error: ERROR 1142 (42000): INSERT command denied

4. Configure Network Access

1. Check MySQL Listen Address

# View MySQL configuration
cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind-address

# If it's 127.0.0.1, need to change to 0.0.0.0

Modify Configuration:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
bind-address = 0.0.0.0

Restart MySQL:

sudo systemctl restart mysql

2. Configure Firewall

Linux (iptables):

# Allow port 3306
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

# Save rules
sudo iptables-save

Linux (firewalld):

# Allow MySQL service
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

Cloud Servers:

•AWS: Configure security group, open port 3306
•Alibaba Cloud: Configure security group rules
•Tencent Cloud: Configure security group policies

3. Test Remote Connection

# Test from another machine
mysql -h your_server_ip -P 3306 -u asktable_readonly -p demo_shop

5. Add Datasource in AskTable

1. Log in to AskTable

Visit asktable.com and log in.

2. Add Datasource

Step 1: Enter Datasource Page

•Click "Datasources" in the left menu
•Click "Add Datasource" button

Step 2: Select Database Type

•Select "MySQL"

Step 3: Fill in Connection Information

Name: Demo Shop
Host: your_server_ip
Port: 3306
Database: demo_shop
Username: asktable_readonly
Password: your_strong_password_here

Step 4: Advanced Configuration (Optional)

{
  "ssl": false,
  "charset": "utf8mb4",
  "timezone": "+08:00"
}

Step 5: Test Connection

•Click "Test Connection" button
•Wait for test result
•If successful, display "Connection Successful"

Step 6: Save Datasource

•Click "Save" button
•Wait for save to complete

3. Sync Metadata

Step 1: Select Tables

•
Check the tables to sync
- •☑ users
- •☑ products
- •☑ orders

Step 2: Start Sync

•Click "Sync Metadata" button
•Wait for sync to complete

Step 3: View Metadata

•View table list
•View field list
•View field types and comments

6. Test Queries

1. First Query

In AskTable, input:

Query all users

Expected Result:

•AI generates SQL: SELECT * FROM users
•Returns user list

2. Aggregation Query

Count the number of products in each product category

Expected Result:

•AI generates SQL: SELECT category, COUNT(*) as count FROM products GROUP BY category
•Returns category statistics

3. Join Query

Query the total order amount for each user

Expected Result:

•AI generates SQL:

SELECT u.name, SUM(o.total_amount) as total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name

•Returns user order statistics

7. Common Problem Solutions

Problem 1: Connection Timeout

Error Message:

Connection timeout

Solution:

•Check if server IP is correct
•Check if port is correct (default 3306)
•Check if firewall is open
•Check if MySQL is listening on external network

Test Command:

telnet your_server_ip 3306

Problem 2: Access Denied

Error Message:

Access denied for user 'asktable_readonly'@'xxx.xxx.xxx.xxx'

Solution:

•Check username and password
•Check user's host restrictions
•Re-grant permissions

Fix Command:

-- Delete old user
DROP USER 'asktable_readonly'@'%';

-- Recreate
CREATE USER 'asktable_readonly'@'%' IDENTIFIED BY 'new_password';
GRANT SELECT ON demo_shop.* TO 'asktable_readonly'@'%';
FLUSH PRIVILEGES;

Problem 3: Database Not Found

Error Message:

Unknown database 'demo_shop'

Solution:

•Check if database name is correct
•Check if database exists

Check Command:

SHOW DATABASES;

Problem 4: SSL Connection Failed

Error Message:

SSL connection error

Solution:

•Disable SSL (if not needed)
•Configure correct SSL certificate

Configuration:

{
  "ssl": false
}

Problem 5: Character Encoding Issues

Symptom: Chinese characters display as garbled text

Solution:

{
  "charset": "utf8mb4"
}

8. Performance Optimization Suggestions

1. Add Indexes

-- Add indexes for commonly queried fields
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_product_category ON products(category);
CREATE INDEX idx_order_user_id ON orders(user_id);
CREATE INDEX idx_order_created_at ON orders(created_at);

2. Optimize Table Structure

-- Add more detailed comments
ALTER TABLE users MODIFY COLUMN name VARCHAR(100) COMMENT 'User Name (Required)';
ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2) COMMENT 'Product Price (Unit: Yuan)';

3. Configure Connection Pool

Configure in AskTable:

{
  "pool": {
    "min_size": 2,
    "max_size": 10,
    "connect_timeout": 10
  }
}

9. Security Hardening

1. Use SSL Connection

Generate SSL Certificate:

# On MySQL server
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

Configure MySQL:

[mysqld]
require_secure_transport=ON

Enable SSL in AskTable:

{
  "ssl": true,
  "ssl_ca": "/path/to/ca.pem"
}

2. Restrict Access IP

-- Allow only specific IP access
CREATE USER 'asktable_readonly'@'123.456.789.0' IDENTIFIED BY 'password';
GRANT SELECT ON demo_shop.* TO 'asktable_readonly'@'123.456.789.0';

3. Regularly Change Password

-- Change password
ALTER USER 'asktable_readonly'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

10. Next Steps

Congratulations! You have successfully connected your first datasource.

Continue Learning:

•AskTable Canvas User Guide - Learn to use Canvas
•AskTable Permission Control Complete Guide - Configure permissions
•How to Optimize AI Query Effect - Improve query accuracy

Practice Suggestions:

•Try various types of queries
•Add more tables and data
•Configure permission controls
•Invite team members to use

Get Help:

•View AskTable Documentation
•Join community discussion
•Contact technical support

Related Reading:

Technical Exchange:

•Website: asktable.com

cta.readyToSimplify

sidebar.noProgrammingNeeded
sidebar.startFreeTrial

cta.startFree cta.viewPricing

cta.noCreditCard

cta.quickStart

cta.dbSupport

sidebar.joinAskTableCommunity

sidebar.contactUs

Getting Started: Connect Your First Datasource - MySQL Practical Tutorial

1. Preparation

1. What Do You Need?

2. Environment Check

2. Create Test Database (Optional)

1. Create Database

2. Create Example Tables

3. Insert Test Data

3. Create Read-Only User

1. Create User

2. Grant Permissions

3. Verify Permissions

4. Test Connection

4. Configure Network Access

1. Check MySQL Listen Address

2. Configure Firewall

3. Test Remote Connection

5. Add Datasource in AskTable

1. Log in to AskTable

2. Add Datasource

3. Sync Metadata

6. Test Queries

1. First Query

2. Aggregation Query

3. Join Query

7. Common Problem Solutions

Problem 1: Connection Timeout

Problem 2: Access Denied

Problem 3: Database Not Found

Problem 4: SSL Connection Failed

Problem 5: Character Encoding Issues

8. Performance Optimization Suggestions

1. Add Indexes

2. Optimize Table Structure

3. Configure Connection Pool

9. Security Hardening

1. Use SSL Connection

2. Restrict Access IP

3. Regularly Change Password

10. Next Steps

cta.readyToSimplify