Skip to main content

MySQL

Overview​

MySQL is an open-source relational database management system (RDBMS) known for its reliability, performance, and ease of use. It is widely used for a variety of applications, from small to large enterprise projects, supporting structured data storage, retrieval, and management with SQL queries. MySQL offers a comprehensive ecosystem with support for advanced features such as replication, transactions, and full-text indexing, making it a versatile choice for integrating with BI tools. You can connect to and read from MySQL databases directly.

Authentication Methods​

To connect to MySQL, you need to provide database connection credentials. Rill supports connecting via individual parameters (host, port, user, password, database).

When you add data from MySQL through the Rill UI, the process follows two steps:

  1. Configure Authentication - Set up your MySQL connector with connection credentials (host, port, user, password, database)
  2. Configure Data Model - Define which table or query to execute

This two-step flow ensures your credentials are securely stored in the connector configuration, while your data model references remain clean and portable.

Connection String Format​

When connecting to MySQL, you can also specify an appropriate Data Source Name (DSN) using the following syntax:

<scheme>://<user>:<password>@<host>:<port>/<database>
  • scheme: The transport protocol to use. Use mysql for classic MySQL protocol connections and mysqlx for X Protocol connections.
  • user and password: Should correspond to the user credentials that Rill will use to connect to MySQL.
  • host and port: Should correspond to the IP address/hostname and port (default 3306) of your MySQL database.
  • database: Should correspond to the database in MySQL that you are using.

For more details, see the MySQL documentation on DSN formats.

Method 1: Connection Credentials​

Using the UI​

  1. Click Add Data in your Rill project
  2. Select MySQL as the data source type
  3. In the authentication step:
    • Enter your MySQL host and port
    • Enter your database name
    • Enter your username and password
    • Configure SSL mode if needed
  4. In the data model configuration step, enter your SQL query
  5. Click Create to finalize

After the model YAML is generated, you can add additional model settings directly to the file.

Manual Configuration​

If you prefer to configure manually:

Step 1: Create connector configuration

Create connectors/mysql.yaml:

type: connector
driver: mysql

host: "localhost"
port: 3306
database: "mydatabase"
user: "myusername"
password: "{{ .env.MYSQL_PASSWORD }}"
ssl_mode: "DISABLED"

Step 2: Add credentials to .env

MYSQL_PASSWORD=your-secure-password
Did you know?

If this project has already been deployed to Rill Cloud and credentials have been set for this connector, you can use rill env pull to pull these cloud credentials locally (into your local .env file). Please note that this may override any credentials you have set locally for this source.

Then, create your first model.

Create Your First Model​

Once your connector is configured, create a model to define what data to pull.

Create models/mysql_data.yaml:

type: model
connector: mysql
dev:
  sql: SELECT * FROM my_table limit 10000

sql: SELECT * FROM my_table

After creating the model, you can add additional model settings directly to the file.

Separating Dev and Prod Environments​

When ingesting data locally, consider setting parameters in your connector file to limit how much data is retrieved, since costs can scale with the data source. This also helps other developers clone the project and iterate quickly by reducing ingestion time.

For more details, see our Dev/Prod setup docs.

Deploy to Rill Cloud​

When deploying a project to Rill Cloud, Rill requires you to explicitly provide the MySQL connection credentials with access to MySQL used in your project. Please refer to our connector YAML reference docs for more information.

If you subsequently add sources that require new credentials (or if you simply entered the wrong credentials during the initial deploy), you can update the credentials by pushing the Deploy button to update your project or by running the following command in the CLI:

rill env push
Did you know?

If you've already configured credentials locally (in your <RILL_PROJECT_DIRECTORY>/.env file), you can use rill env push to push these credentials to your Rill Cloud project. This will allow other users to retrieve and reuse the same credentials automatically by running rill env pull.