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

> Set up any MariaDB instance as a source for ClickPipes

# Generic MariaDB source setup guide

<Info>
  If you use one of the supported providers (in the sidebar), please refer to the specific guide for that provider.
</Info>

<h2 id="enable-binlog-retention">
  Enable binary log retention
</h2>

Binary logs contain information about data modifications made to a MariaDB server instance and are required for replication.

To enable binary logging on your MariaDB instance, ensure that the following settings are configured:

```sql theme={null}
server_id = 1               -- or greater; anything but 0
log_bin = ON
binlog_format = ROW
binlog_row_image = FULL
binlog_row_metadata = FULL  -- introduced in 10.5.0
expire_logs_days = 1        -- or higher; 0 would mean logs are preserved forever
```

To check these settings, run the following SQL commands:

```sql theme={null}
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';
SHOW VARIABLES LIKE 'binlog_row_metadata';
SHOW VARIABLES LIKE 'expire_logs_days';
```

If the values don't match, you can set them in the config file (typically at `/etc/my.cnf` or `/etc/my.cnf.d/mariadb-server.cnf`):

```ini theme={null}
[mysqld]
server_id = 1
log_bin = ON
binlog_format = ROW
binlog_row_image = FULL
binlog_row_metadata = FULL  ; only in 10.5.0 and newer
expire_logs_days = 1
```

If the source database is a replica, make sure to also turn on `log_slave_updates`.

You NEED to RESTART the MariaDB instance for the changes to take effect.

<Note>
  Column exclusion isn't supported for MariaDB \<= 10.4 because the `binlog_row_metadata` setting wasn't yet introduced.
</Note>

<h2 id="configure-database-user">
  Configure a database user
</h2>

Connect to your MariaDB instance as the root user and execute the following commands:

1. Create a dedicated user for ClickPipes:

   ```sql theme={null}
   CREATE USER 'clickpipes_user'@'%' IDENTIFIED BY 'some_secure_password';
   ```

2. Grant schema permissions. The following example shows permissions for the `clickpipes` database. Repeat these commands for each database and host you want to replicate:

   ```sql theme={null}
   GRANT SELECT ON `clickpipes`.* TO 'clickpipes_user'@'%';
   ```

3. Grant replication permissions to the user:

   ```sql theme={null}
   GRANT REPLICATION CLIENT ON *.* TO 'clickpipes_user'@'%';
   GRANT REPLICATION SLAVE ON *.* TO 'clickpipes_user'@'%';
   ```

<Note>
  Make sure to replace `clickpipes_user` and `some_secure_password` with your desired username and password.
</Note>

<h2 id="ssl-tls-configuration">
  SSL/TLS configuration (recommended)
</h2>

SSL certificates ensure secure connections to your MariaDB database. Configuration depends on your certificate type:

**Trusted Certificate Authority (DigiCert, Let's Encrypt, etc.)** - no additional configuration needed.

**Internal Certificate Authority** - Obtain the root CA certificate file from your IT team. In the ClickPipes UI, upload it when creating a new MariaDB ClickPipe.

**Self-hosted MariaDB** - Copy the CA certificate from your MariaDB server (look up the path via the `ssl_ca` setting in your `my.cnf`). In the ClickPipes UI, upload it when creating a new MariaDB ClickPipe. Use the IP address of the server as the host.

**Self-hosted MariaDB starting with 11.4** - If your server has `ssl_ca` set up, follow the option above. Otherwise, consult with your IT team to provision a proper certificate. As a last resort, use the "Skip Certificate Verification" toggle in ClickPipes UI (not recommended for security reasons).

For more information on SSL/TLS options, check out our [FAQ](/integrations/clickpipes/mysql/faq#tls-certificate-validation-error).

<h2 id="whats-next">
  What's next?
</h2>

You can now [create your ClickPipe](/integrations/clickpipes/mysql) and start ingesting data from your MariaDB instance into ClickHouse Cloud.
Make sure to note down the connection details you used while setting up your MariaDB instance as you will need them during the ClickPipe creation process.