Working with Managed Service for MySQL® databases

This section covers the basics of working with Managed Service for MySQL®.

To start using a Managed Service for MySQL® database, follow these steps:

1. Create a connection containing your database access credentials.
2. Run a query to the database.

Query example for reading data from Managed Service for MySQL®:

SELECT * FROM mysql_mdb_connection.my_table

Where:

• mysql_mdb_connection: Your database connection name.
• my_table: Database table name.

Setting up a connectionSetting up a connection

To create a connection to Managed Service for MySQL®:

1. In the management console, select the folder where you want to create a connection.

2. Navigate to Yandex Query.

3. In the left-hand panel, select Connections.

4. Click Create new.

5. Specify the connection settings:

   1. Under General parameters:

      • Name: Managed Service for MySQL® connection name.
      • Type: Managed Service for MySQL.

   2. Under Connection type parameters:

      • Cluster: Select an existing Managed Service for MySQL® cluster or create a new one.

      • Service account: Select an existing Managed Service for MySQL® service account or create a new one. Assign it the managed-mysql.viewer role allowing it to connect to Managed Service for MySQL® clusters.

        To use the service account on your behalf, you need the iam.serviceAccounts.user role.

      • Database: Select the database you will use to work with the MySQL® cluster.

      • Login: Username for connection to the MySQL® database.

      • Password: User password for connection to the MySQL® database.

6. Click Create.

You need a service account to detect Managed Service for MySQL® cluster connection endpoints inside Yandex Cloud. To work with data, set a username and password separately.

Query syntaxQuery syntax

MySQL® uses the following SQL syntax:

SELECT * FROM <connection>.<table_name>

Where:

• <connection>: Your database connection name.
• <table_name>: Database table name.

LimitationsLimitations

Working with MySQL® clusters comes with certain limitations.

• External sources are available for read-only access via SELECT queries. Yandex Query does not currently support data-modifying queries against external sources.

• YQ uses the Yandex Managed Service for YDB type system. However, the valid value ranges for YDB date and time types, i.e., Date, Datetime, and Timestamp, are often too narrow to accommodate the values of the corresponding MySQL® types, i.e., date, datetime, and timestamp. Therefore, YQ returns date and time values read from MySQL® as plain strings (Optional<Utf8>) in ISO-8601 format.

Filter pushdownFilter pushdown

Yandex Query can push parts of query processing down to the source data system by sending filter expressions, e.g., WHERE conditions, directly to the database. This approach is known as filter pushdown.

Filter pushdown is possible when using:

Other filter types do not support source pushdown: the external table rows are filtered on the federated Yandex Query side, i.e., Yandex Query will perform a full scan of the external table when processing the query.

Supported data types for filter pushdown:

Supported data typesSupported data types

In a MySQL® database, the column nullability flag, i.e., whether or not the column can contain NULL values, is not part of the data type system. The NOT NULL constraint for any column is stored in the IS_NULLABLE column of the INFORMATION_SCHEMA.COLUMNS system table, i.e., at the table metadata level. Therefore, all MySQL® base types can contain NULL values by default, and they must be mapped to optional types within the YQ type system.

The table below shows the mapping of MySQL® types to Yandex Query types. Only the listed types are supported.