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

# MySQL dictionary source

> Configure MySQL as a dictionary source in ClickHouse.

Example of settings:

<Tabs>
  <Tab title="DDL">
    ```sql theme={null}
    SOURCE(MYSQL(
        port 3306
        user 'clickhouse'
        password 'qwerty'
        replica(host 'example01-1' priority 1)
        replica(host 'example01-2' priority 1)
        db 'db_name'
        table 'table_name'
        where 'id=10'
        invalidate_query 'SQL_QUERY'
        fail_on_connection_loss 'true'
        query 'SELECT id, value_1, value_2 FROM db_name.table_name'
        enable_compression 1
    ))
    ```
  </Tab>

  <Tab title="Configuration file">
    ```xml theme={null}
    <source>
      <mysql>
          <port>3306</port>
          <user>clickhouse</user>
          <password>qwerty</password>
          <replica>
              <host>example01-1</host>
              <priority>1</priority>
          </replica>
          <replica>
              <host>example01-2</host>
              <priority>1</priority>
          </replica>
          <db>db_name</db>
          <table>table_name</table>
          <where>id=10</where>
          <invalidate_query>SQL_QUERY</invalidate_query>
          <fail_on_connection_loss>true</fail_on_connection_loss>
          <query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
          <enable_compression>1</enable_compression>
      </mysql>
    </source>
    ```
  </Tab>
</Tabs>

<br />

Setting fields:

| Setting | Description |
| - | - |
| `port` | The port on the MySQL server. You can specify it for all replicas, or for each one individually (inside `<replica>`). |
| `user` | Name of the MySQL user. You can specify it for all replicas, or for each one individually (inside `<replica>`). |
| `password` | Password of the MySQL user. You can specify it for all replicas, or for each one individually (inside `<replica>`). |
| `replica` | Section of replica configurations. There can be multiple sections. |
| `replica/host` | The MySQL host. |
| `replica/priority` | The replica priority. When attempting to connect, ClickHouse traverses the replicas in order of priority. The lower the number, the higher the priority. |
| `db` | Name of the database. |
| `table` | Name of the table. |
| `where` | The selection criteria. The syntax for conditions is the same as for `WHERE` clause in MySQL, for example, `id > 10 AND id < 20`. Optional. |
| `invalidate_query` | Query for checking the dictionary status. Optional. Read more in the section [Refreshing dictionary data using LIFETIME](/reference/statements/create/dictionary/lifetime). |
| `fail_on_connection_loss` | Controls behavior of the server on connection loss. If `true`, an exception is thrown immediately if the connection between client and server was lost. If `false`, the server retries to fetch data at least three times before reporting an error. Note that retrying leads to increased response times. Default value: `false`. |
| `query` | The custom query. Optional. |
| `enable_compression` | Enables zlib compression for the MySQL protocol connection. When set to `1`, ClickHouse requests protocol-level compression from the MySQL server. Can also be set per-replica inside `<replica>`. Default value: `0`. |
| `enable_local_infile` | Allows the MySQL server to ask ClickHouse for the contents of a local file (`LOAD DATA LOCAL INFILE`). Only a dictionary defined in a server configuration file may enable it; a `CREATE DICTIONARY` query may leave it unset or set it to `0`. Can also be set per-replica inside `<replica>`, and a `<replica>` that does not set it inherits the value above. Default value: `0`. |
| `ssl_ca_pem` | Contents of the CA certificate that the MySQL server certificate is verified against. Optional. |
| `ssl_cert_pem` | Contents of the client certificate, for certificate-based authentication. Optional. |
| `ssl_key_pem` | Contents of the private key belonging to `ssl_cert_pem`. Optional. |
| `ssl_ca`, `ssl_cert`, `ssl_key` | The same credentials as paths to files on the server. Only allowed for a dictionary defined in a server configuration file, or through a named collection defined there, see below. Optional. |

<Note>
  The `table` or `where` fields cannot be used together with the `query` field. And either one of the `table` or `query` fields must be declared.
</Note>

<Note>
  `ssl_ca`, `ssl_cert` and `ssl_key` name files that the server opens with its own privileges, so they are only accepted for a dictionary defined in a server configuration file, or through a named collection defined there. A `CREATE DICTIONARY` query that specifies the TLS credentials directly must pass their contents instead, in `ssl_ca_pem`, `ssl_cert_pem` and `ssl_key_pem`. Those values are masked in logs and in `SHOW` queries, the same way passwords are.
</Note>

<Note>
  `enable_local_infile` lets the MySQL server ask ClickHouse for the contents of a file of its choosing, which the server reads with its own privileges, so it is only accepted for a dictionary defined in a server configuration file. A `CREATE DICTIONARY` query that enables it, at the source or at one of its `<replica>` entries, is rejected with `BAD_ARGUMENTS`.
</Note>

<Note>
  There is no explicit parameter `secure`. When establishing an SSL-connection security is mandatory.
</Note>

MySQL can be connected to on a local host via sockets. To do this, set `host` and `socket`.

Example of settings:

<Tabs>
  <Tab title="DDL">
    ```sql theme={null}
    SOURCE(MYSQL(
        host 'localhost'
        socket '/path/to/socket/file.sock'
        user 'clickhouse'
        password 'qwerty'
        db 'db_name'
        table 'table_name'
        where 'id=10'
        invalidate_query 'SQL_QUERY'
        fail_on_connection_loss 'true'
        query 'SELECT id, value_1, value_2 FROM db_name.table_name'
    ))
    ```
  </Tab>

  <Tab title="Configuration file">
    ```xml theme={null}
    <source>
      <mysql>
          <host>localhost</host>
          <socket>/path/to/socket/file.sock</socket>
          <user>clickhouse</user>
          <password>qwerty</password>
          <db>db_name</db>
          <table>table_name</table>
          <where>id=10</where>
          <invalidate_query>SQL_QUERY</invalidate_query>
          <fail_on_connection_loss>true</fail_on_connection_loss>
          <query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
      </mysql>
    </source>
    ```
  </Tab>
</Tabs>