# MariaDB

Connect to MariaDB databases for analytics

MariaDB is a community-developed, commercially supported fork of MySQL, known for its enhanced features and performance. Scoop connects directly to MariaDB, 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 MariaDB:

1. **MariaDB server** (version 10.3 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 MariaDB 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, 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 MariaDB

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

**SkySQL (MariaDB Cloud):**
1. Go to your service in SkySQL Portal
2. Navigate to **Security** > **Allowlist**
3. Add Scoop's IP addresses

### For Self-Hosted MariaDB

Configure your firewall to allow incoming connections on port 3306 from Scoop's IP addresses. Also ensure MariaDB accepts remote connections in your configuration:

```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 **MariaDB** 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:mariadb://hostname:3306/database_name
```

With SSL:
```
jdbc:mariadb://hostname:3306/database_name?useSSL=true
```

## Best Practices

- **Enable SSL** for encrypted connections
- **Use InnoDB or Aria storage engines** for better performance
- **Create indexes** on frequently filtered columns
- **Use EXPLAIN** to analyze and optimize slow queries

## MariaDB-Specific Features

MariaDB offers several features beyond MySQL:

- **Aria storage engine** for crash-safe tables
- **Galera Cluster** for synchronous multi-master replication
- **ColumnStore** for analytical workloads
- **Temporal tables** for time-based data versioning

## Troubleshooting

### Connection Refused
- Verify MariaDB is configured to accept remote connections
- Check that the user can connect from your IP
- Ensure port 3306 is open in your firewall

### Access Denied
- Verify the username and password are correct
- Check the user's host permissions: `SELECT user, host FROM mysql.user;`
- Verify GRANT statements were executed and FLUSH PRIVILEGES was run

### Unknown Database
- Verify the database name is spelled correctly
- Check the user has access to the database: `SHOW GRANTS FOR 'scoop_user'@'%';`