MySQL

MySQL is one of the world's most popular open-source relational databases, widely used for web applications and enterprise systems. Scoop connects directly to MySQL, enabling AI-powered analysis on your data.

Connection Details

Prerequisites

Before connecting Scoop to MySQL:

1. MySQL server (version 5.7 or later recommended)
2. Read-only user with SELECT permissions
3. Network access from Scoop's IP addresses

Create a Read-Only User

Connect to your MySQL database and run:

-- Create the read-only user (accessible from any host)
CREATE USER 'scoop_user'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant SELECT on your database
GRANT SELECT ON your_database.* TO 'scoop_user'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

Replace your_database and the password with your actual values.

For enhanced security, you can restrict to specific IP addresses:

CREATE USER 'scoop_user'@'SCOOP_IP' IDENTIFIED BY 'your_secure_password';
GRANT SELECT ON your_database.* TO 'scoop_user'@'SCOOP_IP';
FLUSH PRIVILEGES;

Whitelist Scoop IP Addresses

For Cloud-Hosted MySQL

Amazon RDS:

1. Go to your RDS instance's Security Group
2. Edit inbound rules
3. Add a rule allowing MySQL (port 3306) from Scoop's IP addresses

Google Cloud SQL:

1. Go to your Cloud SQL instance
2. Click Connections > Networking
3. Add Scoop's IP addresses to Authorized Networks

Azure Database for MySQL:

1. Go to your server's Connection Security
2. Add firewall rules for Scoop's IP addresses

For Self-Hosted MySQL

Configure your firewall to allow incoming connections on port 3306 from Scoop's IP addresses. Also ensure MySQL is configured to accept remote connections in my.cnf:

bind-address = 0.0.0.0

Contact Scoop support for the current list of IP addresses to whitelist.

Connect in Scoop

1. Navigate to Datasets and click New Dataset
2. Select Database as your source
3. Choose MySQL from the database type dropdown
4. Enter your connection details:
   • Server Host: Your database hostname or IP
   • Port: 3306 (or your custom port)
   • Database: Your database name
   • Username: scoop_user
   • Password: The user's password
5. Click Test Connection to verify
6. Enter your SQL query and proceed

Connection URL Format

If using a JDBC URL directly:

jdbc:mysql://hostname:3306/database_name

With SSL:

jdbc:mysql://hostname:3306/database_name?useSSL=true&requireSSL=true

Best Practices

• Enable SSL for encrypted connections
• Use InnoDB storage engine for better performance and reliability
• Create indexes on frequently filtered columns
• Use EXPLAIN to analyze and optimize slow queries

Troubleshooting

Connection Refused

• Verify MySQL is configured to accept remote connections
• Check that the user can connect from your IP: SELECT user, host FROM mysql.user;
• Ensure port 3306 is open in your firewall

Access Denied

• Verify the username and password are correct
• Check the user's host permissions allow connection from Scoop's IP
• Verify GRANT statements were executed and FLUSH PRIVILEGES was run

Unknown Database

• Verify the database name is spelled correctly (MySQL is case-sensitive on some systems)
• Check the user has access to the database: SHOW GRANTS FOR 'scoop_user'@'%';