top of page

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.

1,894 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

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

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


bottom of page