MySQL Interface

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/interfaces/mysql.md)

ClickHouse supports the MySQL wire protocol. This allows certain clients that don't have native ClickHouse connectors leverage the MySQL protocol instead, and it has been validated with the following BI tools:

- [Looker Studio](/docs/integrations/lookerstudio)
- [Tableau Online](/docs/integrations/tableau-online)
- [QuickSight](/docs/integrations/quicksight)

If you're trying other untested clients or integrations, keep in mind that there could be the following limitations:

- SSL implementation might not be fully compatible; there could be potential [TLS SNI](https://www.cloudflare.com/learning/ssl/what-is-sni/) issues.
- A particular tool might require dialect features (e.g., MySQL-specific functions or settings) that aren't implemented yet.

If there is a native driver available (e.g., [DBeaver](/docs/integrations/dbeaver)), it is always preferred to use it instead of the MySQL interface. Additionally, while most of the MySQL language clients should work fine, MySQL interface isn't guaranteed to be a drop-in replacement for a codebase with existing MySQL queries.

If your use case involves a particular tool that doesn't have a native ClickHouse driver, and you would like to use it via the MySQL interface and you found certain incompatibilities - please [create an issue](https://github.com/ClickHouse/ClickHouse/issues) in the ClickHouse repository.

Note

To support the SQL dialect of above BI tools better, ClickHouse's MySQL interface implicitly runs SELECT queries with setting [prefer\_column\_name\_to\_alias = 1](/docs/operations/settings/settings#prefer_column_name_to_alias).
This can't be turned off and it can lead in rare edge cases to different behavior between queries sent to ClickHouse's normal and MySQL query interfaces.

## Enabling the MySQL Interface On ClickHouse Cloud[​](#enabling-the-mysql-interface-on-clickhouse-cloud "Direct link to Enabling the MySQL Interface On ClickHouse Cloud")

1. After creating your ClickHouse Cloud Service, click the `Connect` button.

  

2. Change the `Connect with` drop-down to `MySQL`.

  

3. Toggle the switch to enable the MySQL interface for this specific service. This will expose port `3306` for this service and prompt you with your MySQL connection screen that include your unique MySQL username. The password will be the same as the service's default user password.

  

Copy the MySQL connection string shown.

## Creating multiple MySQL users in ClickHouse Cloud[​](#creating-multiple-mysql-users-in-clickhouse-cloud "Direct link to Creating multiple MySQL users in ClickHouse Cloud")

By default, there is a built-in `mysql4<subdomain>` user, which uses the same password as the `default` one. The `<subdomain>` part is the first segment of your ClickHouse Cloud hostname. This format is necessary to work with the tools that implement secure connection, but don't provide [SNI information in their TLS handshake](https://www.cloudflare.com/learning/ssl/what-is-sni), which makes it impossible to do the internal routing without an extra hint in the username (MySQL console client is one of such tools).

Because of this, we *highly recommend* following the `mysql4<subdomain>_<username>` format when creating a new user intended to be used with the MySQL interface, where `<subdomain>` is a hint to identify your Cloud service, and `<username>` is an arbitrary suffix of your choice.

Tip

For ClickHouse Cloud hostname like `foobar.us-east1.aws.clickhouse.cloud`, the `<subdomain>` part equals to `foobar`, and a custom MySQL username could look like `mysql4foobar_team1`.

You can create extra users to use with the MySQL interface if, for example, you need to apply extra settings.

1. Optional - create a [settings profile](/docs/sql-reference/statements/create/settings-profile) to apply for your custom user. For example, `my_custom_profile` with an extra setting which will be applied by default when we connect with the user we create later:

   ```
   CREATE SETTINGS PROFILE my_custom_profile SETTINGS prefer_column_name_to_alias=1;
   ```

   `prefer_column_name_to_alias` is used just as an example, you can use other settings there.
2. [Create a user](/docs/sql-reference/statements/create/user) using the following format: `mysql4<subdomain>_<username>` ([see above](#creating-multiple-mysql-users-in-clickhouse-cloud)). The password must be in double SHA1 format. For example:

   ```
   CREATE USER mysql4foobar_team1 IDENTIFIED WITH double_sha1_password BY 'YourPassword42$';
   ```

   or if you want to use a custom profile for this user:

   ```
   CREATE USER mysql4foobar_team1 IDENTIFIED WITH double_sha1_password BY 'YourPassword42$' SETTINGS PROFILE 'my_custom_profile';
   ```

   where `my_custom_profile` is the name of the profile you created earlier.
3. [Grant](/docs/sql-reference/statements/grant) the new user the necessary permissions to interact with the desired tables or databases. For example, if you want to grant access to `system.query_log` only:

   ```
   GRANT SELECT ON system.query_log TO mysql4foobar_team1;
   ```
4. Use the created user to connect to your ClickHouse Cloud service with the MySQL interface.

### Troubleshooting multiple MySQL users in ClickHouse Cloud[​](#troubleshooting-multiple-mysql-users-in-clickhouse-cloud "Direct link to Troubleshooting multiple MySQL users in ClickHouse Cloud")

If you created a new MySQL user, and you see the following error while connecting via MySQL CLI client:

```
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 54
```

In this case, ensure that the username follows the `mysql4<subdomain>_<username>` format, as described ([above](#creating-multiple-mysql-users-in-clickhouse-cloud)).

## Enabling the MySQL interface on self-managed ClickHouse[​](#enabling-the-mysql-interface-on-self-managed-clickhouse "Direct link to Enabling the MySQL interface on self-managed ClickHouse")

Add the [mysql\_port](/docs/operations/server-configuration-parameters/settings#mysql_port) setting to your server's configuration file. For example, you could define the port in a new XML file in your `config.d/` [folder](/docs/operations/configuration-files):

```
<clickhouse>
    <mysql_port>9004</mysql_port>
</clickhouse>
```

Startup your ClickHouse server and look for a log message similar to the following that mentions Listening for MySQL compatibility protocol:

```
{} <Information> Application: Listening for MySQL compatibility protocol: 127.0.0.1:9004
```

## Connect MySQL to ClickHouse[​](#connect-mysql-to-clickhouse "Direct link to Connect MySQL to ClickHouse")

The following command demonstrates how to connect the MySQL client `mysql` to ClickHouse:

```
mysql --protocol tcp -h [hostname] -u [username] -P [port_number] [database_name]
```

For example:

```
$ mysql --protocol tcp -h 127.0.0.1 -u default -P 9004 default
```

Output if a connection succeeded:

```
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 20.2.1.1-ClickHouse

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
```

For compatibility with all MySQL clients, it is recommended to specify user password with [double SHA1](/docs/operations/settings/settings-users#user-namepassword) in configuration file.
If user password is specified using [SHA256](/docs/sql-reference/functions/hash-functions#SHA256), some clients won't be able to authenticate (mysqljs and old versions of command-line tool MySQL and MariaDB).

Restrictions:

- prepared queries aren't supported
- some data types are sent as strings

To cancel a long query use `KILL QUERY connection_id` statement (it is replaced with `KILL QUERY WHERE query_id = connection_id` while proceeding). For example: