Migration From MySql To ClickHouse

Transfer the data from MySQL into ClickHouse

Recommended solution – MinervaDB ClickHouse MySQL Data Reader (forked from

Alitinity/clickhouse-mysql-data-reader )

GitHub – https://github.com/MinervaDB/MinervaDB-ClickHouse-MySQL-Data-Reader

Installation and configuration

** Prerequisites – we have to install pip3 (python3-pip in Ubuntu) as Python in version at least 3.4 is required. Then

we can use pip3 to install some of the required Python modules

pip3 install mysqlclient

pip3 install mysql-replication

pip3 install clickhouse-driver

We have to clone the repository next. For Centos 7, RPM’s are also available, it is also possible to install it using

pip3 (clickhouse-mysql package), but we found that the version available through pip does not contain the latest

updates and we want to use origin branch from the git repository:

git clone https://github.com/Altinity/clickhouse-mysql-data-reader

Installation using pip

pip3 install -e /path/to/clickhouse-mysql-data-reader/

Next step will be to create MySQL users required by clickhouse-mysql-data-reader to access MySQL data:

mysql> CREATE USER 'clickhousereader'@'%' IDENTIFIED BY 'yourpassword!';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE USER 'clickhousereader'@'127.0.0.1' IDENTIFIED BY 'yourpassword!';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'clickhousereader'@'localhost' IDENTIFIED BY 'yourpassword!';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'clickhousereader'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'clickhousereader'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'clickhousereader'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

My.CNF (We have enabled log_bin)

[mysqld]

# mandatory

server-id = 200

log_bin = /var/lib/mysql/bin.log

binlog-format = row # very important if you want to receive, write, update and delete row

events

# optional

expire_logs_days = 30

max_binlog_size = 900M

# setup listen address

bind-address = 0.0.0.0

When you have successfully completed the steps above, You can proceed with data import to ClickHouse. We

recommend you run the import on a host with tables locked to avoid possible data corruption errors. You can use a

MySQL slave (strongly recommended) as the source of the data. The command you will be running is:

clickhouse-mysql --src-server-id=200 --src-wait --nice-pause=1 --src-host=192.168.56.101 --src-user=clickhousereader --src-password=MDB@2019 --src-tables=wiki.pageviews --dst-host=127.0.0.1 --dst-create-table --migrate-table

The above script connects to MySQL on host 192.168.56.101 using the given credentials, it will copy the table

‘pageviews’ in the schema ‘wiki’ to a ClickHouse running on the local host (127.0.0.1). The table will be created

automatically, and data will be migrated