MySQL

Guide to set up your MySQL database with Velvet

In this guide, we outline the steps required to securely connect your MySQL database to your Velvet workspace. You may be able to skip some of these steps if you've previously connected your database to other services.

Depending on whether you're starting a new project from scratch, or connecting a database that's already configured for a third-party tool - this will take between 10 minutes and 2 hours.

  1. Create a read-only user
  2. Set up a static IP address (guide here)
  3. Identify and copy your connection string
  4. Configure which database tables you want to connect with Velvet

1. Create a read-only user

It's best practice to create a user with read-only permissions for products like Velvet. Create a user with read-only permissions in MySQL.

Connect to your MySQL server as a user with administrative privileges and run the following SQL commands.

MySQL v8 includes a pg_read_all_data role. Run mysql -V in MySQL to determine your version.

For MySQL v7 and below:

  1. Create the velvet_readonly user with the password a very good password.
CREATE USER 'velvet_readonly'@'%' IDENTIFIED BY 'mypassword';

The % in the command above means that user velvet_readonly can be used to connect from any host or IP. You can limit the access by defining the host or IP from where the user can connect.

  1. Grant the user the SELECT privilege from all tables in the specified database.
GRANT SELECT ON your_database_name.* TO 'report'@'%';

The % in the command above gives the user velvet_readonly read-only access to the database from any host or IP. You can limit the access by defining the host or IP from where the user can connect. To grant access to a single table, specify the table name instead of *.

For MySQL v8 and above:

  1. Create a velvet_readonly user with the password a very good password and assign them the velvet_read_all_data role.
CREATE ROLE 'velvet_read_all_data';
GRANT SELECT ON your_database_name.* TO 'velvet_read_all_data'@'%';
GRANT 'velvet_read_all_data' TO 'velvet_readonly'@'%';

The first % in the command above means that role velvet_read_all_data can be used to connect from any host or IP. You can limit the access by defining the host or IP from where the user can connect.

The second % in the command above gives the user velvet_readonly read-only access to the database from any host or IP. You can limit the access by defining the host or IP from where the user can connect. To grant access to a single table, specify the table name instead of *.

Once you've created a read-only user, move on to the next step.

2. Set up a static IP address

Set up a static IP using our static IP configuration guide.

3. Identify and copy your connection string

Find your database connection string to paste it into your Velvet workspace config. If you already have a connection string, jump ahead to section #4.

Or, follow the guide below for building your connection string for the first time.

Build a connection string

Read our general guide on URI construction here. The MySQL-specific overview is below.

Ensure all characters that require encoding are converted to their percent encoded counterparts.

Characters to percent-encode:

  • %%25
  • &%26
  • /%2F
  • :%3A
  • =%3D
  • ?%3F
  • @%40
  • [%5B
  • ]%5D

The MySQL connection string requires a username, followed by an optional password in the following format:

mysql://username:password@

If you prefer to specify only the username, you can use:

mysql://username@

The host (a name resolved by an external name system like DNS, an IP address or other direct address) and the port (port number on which the database server is running):

mysql://username:password@host:port

If the MySQL server is running on the default port (3306) you can omit the port component:

mysql://username:password@host

The schema name is required and is provided after the host and port. The database name begins with a forward slash.

mysql://username:password@host:port/database

You can also include additional parameters to customize the connection. These parameters are appended to the connection string and start with a question mark (?).

mysql://username:password@host:port/database?sslmode=required

Common parameters::

  • charset
  • sslmode
  • timeout

Examples to illustrate these formats:

  1. Only Username on Localhost

    mysql://username@localhost:5432/your_database

  2. Default port with Username and Password

    mysql://username:password@host/your_database

  3. Multiple Additional Parameters

    mysql://username:password@localhost:5432/your_database?sslmode=require&timezone=UTC&application_name=my_app&connect_timeout=10

Once done, copy your connection string to paste it into Velvet.

4. Configure your database and tables on Velvet

Configure MySQL as a remote database connection and select which tables you want connected.

  1. Tap into a workspace in your Velvet dashboard
  2. Tap "Add a data source"
  3. Select type "Remote database"
  4. Name your database
  5. Paste in your database URI. Note: After providing your database credentials for the first time, we manage the connection using ClickHouse. If required, you can add ClickHouse's Static IPs to an allowlist - Read their API documentation here.
  1. Select and configure the table you want to connect as a source.
  1. Name your table and create the source.
  1. Repeat steps 6 and 7 to add each table you want included as a data source.
  2. You can now view, search, and filter data inside your database source.

Great job, you’ve connected your internal database as a data source. We’ll capture data from your connected tables so you can query them in your Velvet workspace.

Email [email protected] for support or feedback.