Creating a connection to MySQL® in DataLens On-premises

To create a MySQL® connection:

1. Go to the workbook page or create a new one.

2. In the top-right corner, click Create → Connection.

3. Select the MySQL® connection.

4. Specify the connection parameters for the external MySQL® database:

   • Host name: Specify the path to a master host or a MySQL® master host IP address. You can specify multiple hosts in a comma-separated list. If you fail to connect to the first host, DataLens will select the next one from the list.
   • Port: Specify the MySQL® connection port. The default port is 3306.
   • Path to database: Specify the database name.
   • Username: Specify a username for the MySQL® connection.
   • Password: Specify a password for the user.
   • Cache TTL in seconds: Specify cache TTL or leave the default value. The recommended value is 300 seconds (5 minutes).

   • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset. This option is disabled by default. When enabling it, you will need to select the raw SQL level:

     • Subqueries only: Describe dataset sources using SQL queries.
     • Subqueries and parameters: Describe dataset sources using SQL queries and use source parameterization.
     • SQL to read: Describe dataset sources using SQL queries, use source parameterization, create QL charts, and execute read-only SQL queries.

5. Optionally, test the connection by clicking Check connection.

6. Click Create connection.

7. Enter a name for the connection and click Create.

Additional settingsAdditional settings

You can specify additional connection settings under Advanced connection settings:

• Setting collate in a query: To explicitly define a collation for database queries, select a mode:

  • Auto: Applies the default setting. DataLens decides whether to enable the utf8mb4_general_ci locale.
  • On: Applies the DataLens setting. The utf8mb4_general_ci locale is specified for individual expressions within a query. As a result, the server uses the appropriate sorting logic, regardless of the server settings and specific tables. Use the DataLens setting if your database locale is incompatible with DataLens.
  • Off: Applies the default setting. DataLens only uses database-level locale settings.

• TLS: Indicates whether TLS is required. When this option is enabled, the connection requires using SSL.

• CA Certificate: To upload a certificate, click Attach file and select the certificate file. When the certificate is uploaded, the field shows the file name.

• Disable data export: When this option is on, the data export item will not be available in the menu for the charts based on this connection. However, you will still be able to copy chart data and take screenshots.