Skip to main content

Connecting MySQL to Omni

In this guide, you'll learn how to connect your MySQL database to Omni.

Requirements​

To follow the steps in this guide, you'll need:

  • Organization Admin permissions in Omni
  • Permissions in MySQL that allow you to create database users

Step 1: Create a database user for Omni​

In your MySQL database, run the following command to create a database user for Omni:

Create Omni user
CREATE USER omni IDENTIFIED WITH mysql_native_password BY '<password>';

Next, run the following command to grant the Omni user access to the database:

Grant SELECT permissions to Omni user
GRANT SELECT ON <database_name>.* TO 'omni'@'%';

Step 2: Allowlist Omni's IP addresses​

If access to the MySQL database is limited by IP address, you'll need to add Omni's IPs to the allowlist before you create the database connection.

Omni's IP addresses can be found on an individual connection's page, accessed by navigating to Settings > Connections and clicking a connection.

Step 3: Create the connection in Omni​

  1. In Omni, click Settings > Connections.

  2. Click Add connection.

  3. Click MySQL.

  1. Fill in the fields as follows:

    • Display name - Required. Enter a name for the connection, which will display in the connections list in Omni.

    • Host - Required. Enter the host address for the database.

    • Post - Required. Enter the port used by the database.

      The default for MySQL is 3306.

    • Default schema - Required. Enter the name of the default schema for the database.

    • Include schemas - To only include specific schemas, enter the names of the schemas as a comma-separated list. Leaving this field blank will allow Omni to access all schemas in the database.

    • Username - Required. Enter the username of the database user you want Omni to use.

      This is the Omni user you created in Step 1.

    • Password - Required. Enter the password for the database user.

      This is the password you assigned to the Omni user in Step 1.

    • Schema for CSV Upload - Enter the name of the Schema where CSV uploads will be stored. If left blank, you'll still be able to upload CSVs, but they won't be pushed to the database.

    • Database Timezone - Required. Select the timezone used by the database.

    • Query timezone - Required. If specified, data will be converted from the Database timezone to this timezone when querying. Refer to the Converting timezone data guide for more information.

    • Allow User-Specific Timezones - Check this option to allow individual users' Query timezone settings to be used as the query timezone for the connection.

    • Query timeout - Enter the number of seconds a query has to execute before the database cancels it. The default is 900 seconds.

  2. When finished, click Create connection.

What's next?​

Now that MySQL is set up, you can: