MySQL
Guide to connect MySQL to Velvet
Connect your MySQL database to your Velvet workspace. You can skip some steps if you've previously connected to other services.
1. Create a read-only user
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:
- Create the
velvet_readonly
user with the passworda 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.
- 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:
- Create a
velvet_readonly
user with the passworda very good password
and assign them thevelvet_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 *
.
2. Set static IPs
Configure network restrictions to allow Velvet's static IP addresses. Follow our static IP configuration guide.
3. Build your connection string
Read our general guide on URI construction here. The MySQL-specific overview is below.
If you already have a connection string - copy it and skip to step #4.
Build a connection string
Follow the MySQL-specific instructions below if you need to construct a URI.
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:
-
Only Username on Localhost
mysql://username@localhost:5432/your_database
-
Default port with Username and Password
mysql://username:password@host/your_database
-
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. Connect your database
Connect your database to Velvet using your connection string.
- Tap into a workspace in your Velvet dashboard
- Tap "Add a data source"
- Select type "Remote database"
- Name your database
- Paste in your database URI
- Press create
If you run into any errors, schedule a call or email [email protected].
Once your database is connected, you're ready to use the AI SQL editor. Close out of the modal to start using the editor on top of your database.
Schema mapping is optional, and only required if you want to query multiple sources at the same time.
5. [optional] Schema mapping for unified sources
This configuration is only required to query multiple data sources at the same time. For example - two databases at the same time, or stripe webhooks alongside your database tables.
You can set up this unified mapping any time.
Add schema mapping context
After you connect your database, you'll be prompted to configure the schema for each table you set up.
Name your table to create this source.
Repeat these schema mapping steps for each table you want included as a unified data source.
Get help
Email [email protected] if you need additional support.
Updated 18 days ago