> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Allows connecting to databases on a remote MySQL server and perform `INSERT` and `SELECT` queries to exchange data between ClickHouse and MySQL.

# MySQL

export const CloudNotSupportedBadge = () => {
  return <div className="cloudNotSupportedBadge">
            <div className="cloudNotSupportedIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.5" d="M6.33366 12.6666L12.3739 12.6667C13.6593 12.6667 14.7073 11.6187 14.7073 10.3334C14.7073 9.04804 13.6593 8.00003 12.3739 8.00003C12.3739 8.00003 12.3337 7.66659 12.0003 7.33325M10.667 5.33322C8.00033 2.33325 4.45395 4.78537 4.14195 6.68203C2.55728 6.7627 1.29395 8.06203 1.29395 9.6667C1.29395 11.3234 2.66699 12.6666 4.00033 12.6666" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.5" d="M2.66699 14L12.0003 4.66663" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>

        </div>
            Not supported in ClickHouse Cloud
        </div>;
};

# MySQL database engine

Allows to connect to databases on a remote MySQL server and perform `INSERT` and `SELECT` queries to exchange data between ClickHouse and MySQL.

The `MySQL` database engine translate queries to the MySQL server so you can perform operations such as `SHOW TABLES` or `SHOW CREATE TABLE`.

You cannot perform the following queries:

* `RENAME`
* `CREATE TABLE`
* `ALTER`

<h2 id="creating-a-database">
  Creating a database
</h2>

```sql theme={null}
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
[SETTINGS enable_compression=0]
```

**Engine Parameters**

* `host:port` — MySQL server address.
* `database` — Remote database name.
* `user` — MySQL user.
* `password` — User password.

**Settings**

<h3 id="enable-compression">
  `enable_compression`
</h3>

Enables zlib compression for the MySQL protocol connection. When set to `1`, ClickHouse requests protocol-level compression from the MySQL server.

Default value: `0`.

Example:

```sql theme={null}
CREATE DATABASE mysql_db
ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')
SETTINGS enable_compression = 1;
```

<h2 id="data_types-support">
  Data types support
</h2>

| MySQL                            | ClickHouse                                       |
| -------------------------------- | ------------------------------------------------ |
| UNSIGNED TINYINT                 | [UInt8](/reference/data-types/int-uint)          |
| TINYINT                          | [Int8](/reference/data-types/int-uint)           |
| UNSIGNED SMALLINT                | [UInt16](/reference/data-types/int-uint)         |
| SMALLINT                         | [Int16](/reference/data-types/int-uint)          |
| UNSIGNED INT, UNSIGNED MEDIUMINT | [UInt32](/reference/data-types/int-uint)         |
| INT, MEDIUMINT                   | [Int32](/reference/data-types/int-uint)          |
| UNSIGNED BIGINT                  | [UInt64](/reference/data-types/int-uint)         |
| BIGINT                           | [Int64](/reference/data-types/int-uint)          |
| FLOAT                            | [Float32](/reference/data-types/float)           |
| DOUBLE                           | [Float64](/reference/data-types/float)           |
| DATE                             | [Date](/reference/data-types/date)               |
| DATETIME, TIMESTAMP              | [DateTime](/reference/data-types/datetime)       |
| BINARY                           | [FixedString](/reference/data-types/fixedstring) |

All other MySQL data types are converted into [String](/reference/data-types/string).

[Nullable](/reference/data-types/nullable) is supported.

<h2 id="global-variables-support">
  Global variables support
</h2>

For better compatibility you may address global variables in MySQL style, as `@@identifier`.

These variables are supported:

* `version`
* `max_allowed_packet`

<Note>
  By now these variables are stubs and don't correspond to anything.
</Note>

Example:

```sql theme={null}
SELECT @@version;
```

<h2 id="examples-of-use">
  Examples of use
</h2>

Table in MySQL:

```text theme={null}
mysql> USE test;
Database changed

mysql> CREATE TABLE `mysql_table` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `float` FLOAT NOT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)

mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)

mysql> select * from mysql_table;
+------+-----+
| int_id | value |
+------+-----+
|      1 |     2 |
+------+-----+
1 row in set (0,00 sec)
```

Database in ClickHouse, exchanging data with the MySQL server:

```sql theme={null}
CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password') SETTINGS read_write_timeout=10000, connect_timeout=100;
```

```sql theme={null}
SHOW DATABASES
```

```text theme={null}
┌─name─────┐
│ default  │
│ mysql_db │
│ system   │
└──────────┘
```

```sql theme={null}
SHOW TABLES FROM mysql_db
```

```text theme={null}
┌─name─────────┐
│  mysql_table │
└──────────────┘
```

```sql theme={null}
SELECT * FROM mysql_db.mysql_table
```

```text theme={null}
┌─int_id─┬─value─┐
│      1 │     2 │
└────────┴───────┘
```

```sql theme={null}
INSERT INTO mysql_db.mysql_table VALUES (3,4)
```

```sql theme={null}
SELECT * FROM mysql_db.mysql_table
```

```text theme={null}
┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘
```