Migrate & Replicate MySQL to Clickhouse

Image source from https://altinity.com/

Installation and Configuration

Before going for database migration, we need to install some library that helps to migrate data smoothly.
Install some python libraries: mysqlclient, mysql-replication, clickhouse-mysql.
Be sure to install all the libraries.

$ sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
$ sudo apt-get install python3-pymysql
$ pip3 install mysqlclient
$ pip3 install mysql-replication
$ pip3 install clickhouse-mysql

# If getting error on pymysql, Try to reinstall pymysql
$ pip3 install --user 'pymysql<0.9'

Create MySQL users required by Clickhouse

After successful installation of required libraries. Create the mysql users and give all the permission that is required by the clickhouse to access mysql data. For running the below command, need to be login in mysql terminal.

mysql>​ CREATE USER ​ 'new_user'​@'%'​ IDENTIFIED BY ​ 'My@123'​ ;
mysql>​ CREATE USER ​ 'new_user'​@'127.0.0.1'​ IDENTIFIED BY ​'My@123'​ ;
mysql>​ CREATE USER ​ 'new_user'​@'localhost'​ IDENTIFIED BY ​ 'My@123'​ ;
mysql>​ GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'new_user'​@'%'​ ;
mysql>​ GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'new_user'​@'127.0.0.1'​ ;
mysql>​ GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'new_user'​@'localhost'​ ;

Make sure to have MySQL settings

After creating the mysql users, do the below changes in the mysql configuration file: my.cnf

server_id = <your id>
binlog_format = ROW
binlog_row_image = FULL
# To Check the details, type following commands in MySQL console
mysql> Select @@server_id;
mysql> Select @@binlog_format
mysql> Select @@binlog_row_image

I - Migrate data from MySQL to Clickhouse

Create schema of MySQL table for Clickhouse

For migration of mysql table, we need the table structure from mysql which is used to create the same table structure in clickhouse as per the clickhouse create table syntax.
Below commands will generate the table schema for clickhouse.

clickhouse-mysql \
--src-host=<mysql-host> \
--src-user=<mysql-user> \
--src-password=<mysql-passowrd> \
--create-table-sql-template \
--with-create-database \
--src-tables=<dbname>.<table-name> > <table-name>.sql
clickhouse-mysql \
--src-host=127.0.0.1 \
--src-user=new_user \
--src-password=My@123 \
--create-table-sql-template \
--with-create-database \
--src-tables=mysql_DB.my_tbl > my_tbl.sql
  • Before proceeding next steps, change some schema values like Engine, DataType, and remove params from MergeTree(<param1>,<param2>) as convenient.

Create Table in Clickhouse

After generating the my_tbl.sql file, create a table in clickhouse using the below commands.
Why do we need the same schema structure??
Because during migration or replication of data from mysql to clickhouse, the values should be in proper order, so it will insert the same data in the clickhouse table.

clickhouse-client \
--host=<clichouse_host> \
--password=<clickhouse_password> \
-mn < /file/path/to/<table_name>.sql
clickhouse-client \
--host=127.0.0.1 \
--password=My@123 \
-mn < /file/path/to/my_tbl.sql
  • If facing the error of merge engine, edit the .sql file and add “ORDER BY (<col name>)” after Engine.

Migrate Table data from MySQL to Clickhouse

After successful creation of mysql table in the clickhouse. Migrate the mysql table data into the Clickhouse table.

clickhouse-mysql \
--src-host=<mysql-host> \
--src-user=<mysql-user> \
--src-password=<mysql-password> \
--migrate-table \
--src-tables=<mysql-dbname>.<mysql-table_name> \
--dst-host=<clickhouse-host> \
--dst-password=<clickhouse-password> \
--dst-schema <clickhouse-dbname> \
--dst-table <clickhouse-table_name> \
--log-file=/file/path/to/<table_name_migt>.log
clickhouse-mysql \
--src-host=127.0.0.1 \
--src-user=new_user \
--src-password=My@123 \
--migrate-table \
--src-tables=mysql_DB.my_tbl \
--dst-host=127.0.0.1 \
--dst-password=My@123 \
--dst-schema clickhouse_DB \
--dst-table my_tbl \
--log-file=/file/path/to/my_tbl_migt.log

II - Replicate MySQL Table to Clickhouse

As the data is migrated to clickhouse, but what about the new data in mysql. For that replication of the mysql table, data is needed to be replicated in the clickhouse database.
It's an additional step where any new entry is inserted in mysql, it will automatically reflect in clickhouse if the table is syncing with clickhouse.
The below command is used for syncing the table.

clickhouse-mysql \
--src-server-id=<mysql-server-id> \
--src-resume \
--src-wait \
--nice-pause=1 \
--src-host=<mysql-host> \
--src-user=<mysql-user> \
--src-password=<mysql-password> \
--src-tables=<mysql_DB>.<my_tbl> \
--dst-host=<clickhouse-host> \
--dst-password=<clickhouse-password> \
--dst-schema <clickhouse_DB> \
--dst-table <clickhouse_tbl> \
--log-level=info \
--csvpool \
--mempool-max-flush-interval=60 \
--mempool-max-events-num=1000 \
--pump-data \
--log-file=/file/path/to/<table_name_repl>.log
clickhouse-mysql \
--src-server-id=1 \
--src-resume \
--src-wait \
--nice-pause=1 \
--src-host=127.0.0.1 \
--src-user=new_user \
--src-password=My@123 \
--src-tables=mysql_DB.my_tbl \
--dst-host=127.0.0.1 \
--dst-password=My@123 \
--dst-schema clickhouse_DB \
--dst-table my_tbl \
--log-level=info \
--csvpool \
--mempool-max-flush-interval=60 \
--mempool-max-events-num=1000 \
--pump-data \
--log-file=/file/path/to/my_tbl_repl.log

III - Create Schema & Migrate table in Clickhouse

The scenario of creating a mysql table schema and then migrating the table data in clickhouse, it can be done using only one command.

clickhouse-mysql \
--src-server-id=<mysql-server-id> \
--src-wait \
--nice-pause=1 \
--src-host=<mysql-host> \
--src-user=<mysql-user> \
--src-password=<mysql-password> \
--src-tables=<mysql_DB>.<my_tbl> \
--dst-host=<clickhouse-host> \
--dst-password=<clickhouse-password> \
--dst-create-table \
--migrate-table
clickhouse-mysql \
--src-server-id=1 \
--src-wait \
--nice-pause=1 \
--src-host=127.0.0.1 \
--src-user=root \
--src-password=root \
--src-tables=myDB.tbl_dnd \
--dst-host=127.0.0.1 \
--dst-create-table \
--migrate-table

IV - Migration & Replication in One command

If it needs to migrate and replicate the mysql table data to clickhouse using one command, we can follow the below command.
So with the migration of the table data, it will start replicating the new insertion value of the mysql table into the clickhouse table.

clickhouse-mysql \
--src-server-id=<mysql-server-id> \
--src-resume \
--src-wait \
--nice-pause=1 \
--src-host=<mysql-host> \
--src-user=<mysql-user> \
--src-password=<mysql-password> \
--src-tables=<mysql_DB>.<my_tbl> \
--dst-host=<clickhouse-host> \
--dst-password=<clickhouse-password> \
--dst-schema <clickhouse_DB> \
--dst-table <clickhouse_tbl> \
--log-level=info \
--csvpool \
--mempool-max-flush-interval=60 \
--mempool-max-events-num=1000 \
--pump-data \
--migrate-table \
--log-file=/file/path/to/<table_name_migt_repl>.log
clickhouse-mysql \
--src-server-id=1 \
--src-resume \
--src-wait \
--nice-pause=1 \
--src-host=127.0.0.1 \
--src-user=new_user \
--src-password=My@123 \
--src-tables=mysql_DB.my_tbl \
--dst-host=127.0.0.1 \
--dst-password=My@123 \
--dst-schema clickhouse_DB \
--dst-table my_tbl \
--log-level=info \
--csvpool \
--mempool-max-flush-interval=60 \
--mempool-max-events-num=1000 \
--pump-data \
--migrate-table \
--log-file=/file/path/to/my_tbl_migt_repl.log

Validate the successful data import by counting the records of a table

Running the normal select query in both database mysql & clickhouse,
As mysql select query is giving the result in minutes while in clickhouse it returns within a seconds.

mysql>​ SELECT COUNT(*) FROM mysql_DB.my_tbl\G *************************** 1. row *************************** COUNT(*): 935623858
1 row in set (5 min 17.12 sec)
ClickHouse-LAB :) SELECT COUNT(*) FROM clickhouse_DB.my_tbl\GSELECT COUNT(*)
FROM clickhouse_DB.my_tbl
Row 1:
────────────
COUNT(): 935623858
1 rows in set. Elapsed: 0.233 sec. Processed 935.62 million rows, 491.10 MB (4.91 billion
rows/s., 4.91 GB/s.)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store