SlateoAPI Reference

MySQL Setup

Connect Slateo to your MySQL database using a dedicated read-only user. This guide covers user creation, network access options (PrivateLink, SSH tunnel, or direct connection), and security best practices.

Overview

MySQL supports fine-grained access control through users and grants. Slateo can connect to your MySQL database using multiple network access methods depending on your security requirements:

  • AWS PrivateLink: Secure private connection without exposing your database to the public internet (AWS only)
  • SSH Tunnel: Secure connection through a bastion host
  • Direct Connection: Direct TCP connection (requires network access configuration)

Best practice: Use PrivateLink or SSH tunnel for production databases. Direct connections should only be used for development or when the database is in a secure network.

Prerequisites

Before setting up the connection, ensure you have:

  • An active MySQL database (version 5.7 or higher, or MySQL 8.0+)
  • Root or admin privileges to create users and grant permissions
  • At least one database with tables you want Slateo to access
  • Network access configured (see network access options below)

Setup steps

Step 1: Create a read-only database user

Create a dedicated user for Slateo with read-only permissions. Connect to your MySQL database using the mysql client or a SQL tool, then run:

-- Create the user
CREATE USER 'slateo_readonly'@'%' IDENTIFIED BY '<secure-password>';

-- Grant read-only access to the database
GRANT SELECT ON <your_database>.* TO 'slateo_readonly'@'%';

-- Grant access to information_schema for schema discovery
GRANT SELECT ON information_schema.* TO 'slateo_readonly'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

-- Verify the grants
SHOW GRANTS FOR 'slateo_readonly'@'%';

Replace:

  • <secure-password>: A strong, unique password
  • <your_database>: Your database name

MySQL user management documentation →

Host specification: The '%' wildcard allows connections from any host. For better security, replace '%' with specific IP addresses or hostnames:

-- Allow connections only from Slateo's IP addresses
CREATE USER 'slateo_readonly'@'1.2.3.4' IDENTIFIED BY '<secure-password>';
CREATE USER 'slateo_readonly'@'5.6.7.8' IDENTIFIED BY '<secure-password>';

GRANT SELECT ON <your_database>.* TO 'slateo_readonly'@'1.2.3.4';
GRANT SELECT ON <your_database>.* TO 'slateo_readonly'@'5.6.7.8';

Contact your Slateo account manager for the current list of IP addresses.

Multiple databases:

If you have multiple databases, you can grant access to each one:

-- Grant access to multiple databases
GRANT SELECT ON database1.* TO 'slateo_readonly'@'%';
GRANT SELECT ON database2.* TO 'slateo_readonly'@'%';
GRANT SELECT ON database3.* TO 'slateo_readonly'@'%';

-- Or grant access to all databases (not recommended for production)
GRANT SELECT ON *.* TO 'slateo_readonly'@'%';

Security best practices:

  • Use a strong, unique password (minimum 16 characters)
  • Only grant SELECT permissions—no write access needed
  • Limit access to specific databases containing data you want Slateo to access
  • Use specific host restrictions instead of '%' when possible

Step 2: Configure network access

Choose the network access method that best fits your security requirements.

Option A: AWS PrivateLink (recommended for AWS)

AWS PrivateLink provides secure private connectivity without exposing your database to the public internet.

Prerequisites:

  • MySQL database running in AWS (RDS, EC2, or Aurora)
  • Network Load Balancer (NLB) in front of your database
  • VPC Endpoint Service configured

See the AWS PrivateLink connectivity guide for detailed setup instructions. Select Databases → MySQL and follow Steps 1-4.

Once PrivateLink is configured, Slateo will provide you with the endpoint DNS name to use in Step 3.

Option B: SSH Tunnel (recommended for non-AWS or strict security)

SSH tunneling provides secure access through a bastion host.

Prerequisites:

  • SSH bastion host with public IP or hostname
  • SSH key pair for authentication
  • Bastion host has network access to your MySQL database

Setup:

  1. Create an SSH user on your bastion host for Slateo
  2. Generate an SSH key pair (or use an existing one)
  3. Add the public key to ~/.ssh/authorized_keys on the bastion host
  4. Verify SSH access: 
    ssh -i /path/to/private_key user@bastion-host
  5. Verify the bastion can reach your database: 
    nc -zv <database-host> 3306

Contact your Slateo account manager to provide:

  • Bastion host IP or hostname
  • SSH port (typically 22)
  • SSH username
  • SSH private key

Option C: Direct Connection (development only)

For development or when your database is in a secure network, you can use a direct TCP connection.

Prerequisites:

  • MySQL database with a publicly accessible IP or hostname
  • Firewall rules allowing inbound traffic on port 3306 from Slateo's IP addresses

Contact your Slateo account manager for the current list of IP addresses to allowlist.

Configure MySQL to accept remote connections:

  1. Edit my.cnf or my.ini (location varies by installation):

    [mysqld]
bind-address = 0.0.0.0  # or specific IP addresses

  2. Restart MySQL:

    sudo systemctl restart mysql
# or
sudo systemctl restart mysqld

  3. Verify MySQL is listening on all interfaces:

    netstat -tlnp | grep 3306

MySQL server configuration documentation →

Security warning: Direct connections expose your database to the public internet. Only use this method for development databases or when the database is behind a VPN or other network security controls.

Step 3: Configure the connection in Slateo

Add your MySQL connection in the Slateo admin panel.

  1. Log in to your Slateo workspace
  2. Navigate to Admin → Data Sources → Add Data Source
  3. Select MySQL
  4. Enter the connection details:
    • Name: A friendly name for this connection (e.g., Production Analytics)
    • Host: Your database hostname or IP address
      • For PrivateLink: Use the endpoint DNS name provided by Slateo
      • For SSH tunnel: Use localhost or the internal database hostname
      • For direct connection: Use the public hostname or IP
    • Port: 3306 (default MySQL port, or your custom port)
    • Database: Your database name
    • User: slateo_readonly
    • Password: The password you created in Step 1
    • SSL Mode: REQUIRED (recommended) or PREFERRED
    • SSH Tunnel (if applicable):
      • Enable SSH tunnel
      • Enter bastion host, port, username, and private key
  5. Click Test Connection to verify connectivity
  6. Click Save to complete the setup

Next steps

After you complete the setup:

  1. Schema discovery: Slateo automatically scans your database schema to discover available tables and views (typically within 5 minutes)
  2. Verify access: Check that all expected databases and tables appear in your Slateo workspace
  3. Start querying: Navigate to your workspace to start analyzing your MySQL data

Security considerations

Credential management

  • Use strong passwords: Generate passwords with at least 16 characters including uppercase, lowercase, numbers, and special characters
  • Rotate credentials periodically: Update the password every 90 days
  • Store credentials securely: Use a password manager or secure vault
  • Enable SSL/TLS: Always use encrypted connections

To rotate the password:

ALTER USER 'slateo_readonly'@'%' IDENTIFIED BY '<new-secure-password>';
FLUSH PRIVILEGES;

Then update the password in the Slateo admin panel.

Access control

  • Principle of least privilege: Only grant access to databases that Slateo needs
  • Use read-only permissions: Never grant INSERT, UPDATE, DELETE, or DDL permissions
  • Review grants regularly: Audit user permissions quarterly
  • Enable audit logging: Monitor query activity for unusual patterns

To review current grants:

-- View all grants for the user
SHOW GRANTS FOR 'slateo_readonly'@'%';

-- View user details
SELECT user, host FROM mysql.user WHERE user = 'slateo_readonly';

SSL/TLS encryption

Always use SSL/TLS to encrypt connections between Slateo and your database.

Configure MySQL to require SSL:

  1. Edit my.cnf or my.ini:

    [mysqld]
require_secure_transport = ON
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

  2. Restart MySQL:

    sudo systemctl restart mysql

  3. Verify SSL is enabled:

    SHOW VARIABLES LIKE '%ssl%';

  4. Require SSL for the Slateo user:

    ALTER USER 'slateo_readonly'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

MySQL SSL/TLS documentation →

Monitoring and auditing

Enable query logging to monitor Slateo activity:

-- Enable general query log (caution: can generate large log files)
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- View recent queries by the user
SELECT
  event_time,
  user_host,
  thread_id,
  command_type,
  argument
FROM mysql.general_log
WHERE user_host LIKE '%slateo_readonly%'
ORDER BY event_time DESC
LIMIT 100;

For production environments, consider using MySQL Enterprise Audit or third-party monitoring tools.

MySQL audit logging documentation →

Troubleshooting

Authentication errors

If Slateo reports authentication failures:

  1. Verify the user exists: 
    SELECT user, host FROM mysql.user WHERE user = 'slateo_readonly';
  2. Test the credentials locally: 
    mysql -h <host> -P 3306 -u slateo_readonly -p<password> <database>
  3. Check if the user is locked or expired: 
    SELECT user, host, account_locked, password_expired
FROM mysql.user
WHERE user = 'slateo_readonly';
  4. Verify the host restriction allows connections from Slateo's IP

Connection timeouts

If Slateo reports connection timeouts:

  1. Verify MySQL is running: 
    sudo systemctl status mysql
  2. Check firewall rules allow inbound traffic on port 3306: 
    sudo iptables -L -n | grep 3306
# or
sudo firewall-cmd --list-ports
  3. For PrivateLink: Verify the VPC endpoint is in "Available" status
  4. For SSH tunnel: Verify the bastion host is reachable and can connect to the database
  5. Test connectivity: 
    nc -zv <host> 3306
# or
telnet <host> 3306

Permission errors

If Slateo can connect but cannot query certain tables or views:

  1. Verify the user has the correct grants: 
    SHOW GRANTS FOR 'slateo_readonly'@'%';
  2. Check table-level permissions: 
    SELECT * FROM information_schema.table_privileges
WHERE grantee LIKE '%slateo_readonly%';
  3. Test access by switching to the Slateo user: 
    mysql -h <host> -u slateo_readonly -p<password> <database> -e "SELECT * FROM <table> LIMIT 10;"
  4. Verify the database exists and contains tables: 
    SHOW DATABASES;
USE <database>;
SHOW TABLES;

SSL/TLS errors

If connection fails with SSL errors:

  1. Verify SSL is enabled: 
    SHOW VARIABLES LIKE '%ssl%';
  2. Check SSL certificate validity: 
    openssl x509 -in /path/to/server-cert.pem -text -noout
  3. Try connecting with sslmode=PREFERRED instead of REQUIRED to diagnose the issue
  4. Check MySQL error log for SSL-related errors: 
    sudo tail -f /var/log/mysql/error.log

Database not appearing in Slateo

If a database doesn't appear in Slateo after granting access:

  1. Verify the database exists and contains tables: 
    SHOW DATABASES;
USE <database>;
SHOW TABLES;
  2. Check that the user has SELECT privileges on the database: 
    SHOW GRANTS FOR 'slateo_readonly'@'%';
  3. Trigger a manual schema refresh in Slateo:
    • Go to Admin → Data Sources
    • Find your MySQL connection
    • Click Refresh Schema

Advanced configuration

Using read replicas

For production databases, consider connecting Slateo to a read replica to avoid impacting primary database performance:

  1. Create a read replica of your primary database
  2. Configure the read-only user on the replica
  3. Use the replica hostname in the Slateo connection settings

MySQL replication documentation →

Connection pooling

For high-concurrency workloads, consider using a connection pooler like ProxySQL:

  1. Install and configure ProxySQL in front of your MySQL database
  2. Configure ProxySQL with appropriate pool settings
  3. Use the ProxySQL hostname and port in the Slateo connection settings

ProxySQL documentation →

Using IAM authentication (AWS RDS)

For AWS RDS MySQL, you can use IAM database authentication instead of passwords:

  1. Enable IAM database authentication on your RDS instance
  2. Create an IAM policy granting rds-db:connect permission
  3. Create the database user with AWSAuthenticationPlugin
  4. Contact your Slateo account manager for IAM authentication setup

RDS IAM authentication documentation →

MySQL 8.0 authentication plugin

MySQL 8.0 uses caching_sha2_password by default, which may require additional configuration:

-- Create user with mysql_native_password (for compatibility)
CREATE USER 'slateo_readonly'@'%' IDENTIFIED WITH mysql_native_password BY '<secure-password>';

-- Or alter existing user
ALTER USER 'slateo_readonly'@'%' IDENTIFIED WITH mysql_native_password BY '<secure-password>';

MySQL authentication plugins documentation →

Additional resources

For additional support or questions, contact your Slateo account manager.

Was this page helpful?

Was this page helpful?