# MySQL

Connect to MySQL databases for analytics

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

| Setting | Value |
|---------|-------|
| Default Port | 3306 |
| Connection Type | TCP/IP |
| SSL | Recommended |

## 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:

```sql
-- 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:

```sql
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`:

```ini
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'@'%';`