Replication from MySQL to ClickHouse

Updated: Apr 29, 2019


How to setup clickhouse on ubuntu bionic

MySQLdb package is used for communication with MySQL:

mysql-replication package is used for communication with MySQL also:

clickhouse-driver package is used for communication with ClickHouse:


Starting clickhouse server


How to generate table and ingress data into clickhouse

SQL Commands are generally same in clickhouse and mysql so it will be informal for a MySQL related person to query exclusive clickhouse CREATE TABLE, CREATE DATABASE, SHOW TABLES, SHOW DATABASES, USE DBNAME

Here are the specifics of SQL orientations in clickhouse

Here is the command to create table in clickhouse

MATERIALIZED is a way to generate to column that is repeatedly set when a row is inserted built on the function after the keyword. In this circumstance, the function I selected to use is now(), in command to get the insertion timestamp for each row.

Here you can get the data to insert into this table

Some queries to understand the data and what we can do with it using data aggregation functions.

Data replication from mysql to clickhouse

MySQL setup

Also the following (at least one of) MySQL privileges are required for this operation:


Also the following MySQL config options are required:

Run data reader as following:

Expected results are:

Automatically create target table in ClickHouse (if possible)

migrate existing data from MySQL to ClickHouse

after migration completed, listen for new events to come and pump data from MySQL into ClickHouse

Options description

  • --src-server-id - Master's server id

  • --src-wait - wait for new data to come

  • --nice-pause=1 - when no data available sleep for 1 second

  • --src-host= - MySQL source host

  • --src-user=reader - MySQL source user (remember about PRIVILEGES for this user)

  • --src-password=qwerty - MySQL source password (remember about PRIVILEGES for this user)

  • --src-tables=airline.ontime - list of MySQL source tables to process

  • --dst-host= - ClickHouse host

  • --dst-create-table - create target table automatically

  • --migrate-table - migrate source tables

  • --pump-data - pump data from MySQL into ClickHouse after data migrated

  • --csvpool - make pool of csv files while pumping data (assumes --mempool also)

You can get the full description about Options by Typing --help with clickhouse-mysql.


Recent Posts

See All

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne


T: +44 7825018241

Follow me


© 2020 by GoplarDB 

All Rights Reserved

Powered by: Goplar LTD

  • LinkedIn Social Icon
  • Twitter Clean