[Yandex Cloud documentation](../../index.md) > [Yandex Query](../index.md) > Data sources and sinks > Working with Managed Service for MySQL® databases

# Working with Managed Service for MySQL® databases

This section covers the basics of working with [Managed Service for MySQL®](https://yandex.cloud/ru/services/managed-mysql).

To start working with a Managed Service for MySQL® database, follow these steps:
1. Create a [connection](../concepts/glossary.md#connection) containing your database access credentials.
1. [Run a query](#query) against the database.

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

```sql
SELECT * FROM mysql_mdb_connection.my_table
```

Where:
* `mysql_mdb_connection`: Your database connection name.
* `my_table`: Database table name.


## Setting up a connection {#create_connection}

To create a connection to Managed Service for MySQL®:
1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a connection.
1. Navigate to **Yandex Query**.
1. In the left-hand panel, switch to the **Connections** tab.
1. Click ![info](../../_assets/console-icons/plus.svg) **Create new**.
1. Specify the connection settings:

   1. Under **General parameters**:

      * **Name**: Managed Service for MySQL® connection name.
      * **Type**: `Managed Service for MySQL`.

   1. 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](../../iam/concepts/users/service-accounts.md) or create a new one. Assign it the [`managed-mysql.viewer`](../../managed-mysql/security/index.md#managed-mysql-viewer) role allowing it to connect to `Managed Service for MySQL®` clusters.

        To use a service account, the `iam.serviceAccounts.user` [role](../../iam/security/index.md#iam-serviceAccounts-user) is required.

      * **Database**: Select the database you will use when working with the MySQL® cluster.
      * **Login**: Username you will use to connect to MySQL® databases.
      * **Password**: Password you will use to connect to MySQL® databases.


1. Click **Create**.

A service account is necessary to detect Managed Service for MySQL® cluster connection endpoints inside Yandex Cloud. To access data, you need a separate username and password.

{% note warning %}

First, grant network access from Yandex Query to Managed Service for MySQL® clusters. To do this, enable **Access from Yandex Query** in your target database settings.

{% endnote %}

## Query syntax {#query}
MySQL® uses the following SQL syntax:

```sql
SELECT * FROM <connection>.<table_name>
```

Where:
* `<connection>`: Your database connection name.
* `<table_name>`: Database table name.

## Limitations {#limits}

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](https://ydb.tech/docs/en//yql/reference/types/primitive). 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](https://www.iso.org/iso-8601-date-and-time-format.html) format.

## Filter pushdown {#predicate_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:

|Description|Example|
|---|---|
|`NULL` check|`WHERE column1 IS NULL` or `WHERE column1 IS NOT NULL`|
|Logical operators `AND`, `OR`, `NOT`, and parentheses to control operator precedence |`WHERE column1 IS NULL OR (column2 IS NOT NULL AND column3 > 10)`.|
|Comparison operators `=`, `==`, `!=`, `<>`, `>`, `<`, `>=`, and `<=` that compare a column with other columns or constants|`WHERE column1 > column2 OR column3 <= 10`, `WHERE column1 + column2 > 10`, `WHERE column1 = (10 + 10)`|

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:

|Yandex Query data type|
|----|
|`Bool`|
|`Int8`|
|`Uint8`|
|`Int16`|
|`Uint16`|
|`Int32`|
|`Uint32`|
|`Int64`|
|`Uint64`|
|`Float`|
|`Double`|

## Supported data types {#supported_types}

In MySQL databases, the column nullability flag, i.e., whether or not the column can contain `NULL` values, is not part of the type system. The `NOT NULL` constraint for any column of any table is stored as an `IS_NULLABLE` value in the [INFORMATION_SCHEMA.COLUMNS](https://dev.mysql.com/doc/refman/8.4/en/information-schema-columns-table.html) system table, i.e., at the table metadata level. Therefore, all MySQL® base types may contain `NULL` values by default, and within the YQ type system they must be mapped to [optional](https://ydb.tech/docs/en//yql/reference/types/optional) types. 

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

| MySQL® data type | Yandex Query data type |
| :---: | :---: |
|`bool`|`Optional<Bool>`|
|`tinyint`|`Optional<Int8>`|
|`tinyint unsigned`|`Optional<Uint8>`|
|`smallint`|`Optional<Int16>`|
|`smallint unsigned`|`Optional<Uint16>`|
|`mediumint`|`Optional<Int32>`|
|`mediumint unsigned`|`Optional<Uint32>`|
|`int`|`Optional<Int32>`|
|`int unsigned`|`Optional<Uint32>`|
|`bigint`|`Optional<Int64>`|
|`bigint unsigned`|`Optional<Uint64>`|
|`float`|`Optional<Float>`|
|`real`|`Optional<Float>`|
|`double`|`Optional<Double>`|
|`date`|`Optional<Utf8>`|
|`datetime`|`Optional<Utf8>`|
|`timestamp`|`Optional<Utf8>`|
|`tinyblob`|`Optional<String>`|
|`blob`|`Optional<String>`|
|`mediumblob`|`Optional<String>`|
|`longblob`|`Optional<String>`|
|`tinytext`|`Optional<String>`|
|`text`|`Optional<String>`|
|`mediumtext`|`Optional<String>`|
|`longtext`|`Optional<String>`|
|`char`|`Optional<Utf8>`|
|`varchar`|`Optional<Utf8>`|
|`binary`|`Optional<String>`|
|`varbinary`|`Optional<String>`|
|`json`|`Optional<Json>`|