Migration from MySQL to ClickHouse
UDTS supports migration from MySQL to ClickHouse. Supported MySQL versions include MySQL (including Percona) 5.5/5.6/5.7/8.0, MariaDB 10.1.2 and above, and PolarDB (MySQL compatible version). Supported ClickHouse versions range from 21.3 to 22.8.
Functional Limitations
- Supports single database migration, allowing for entire database migration or specified tables. It does not support migration of stored procedures, triggers, views, etc.
- Does not support the migration of DDL statements and delete statements.
- For incremental/full+incremental migration, binlog must be enabled on the source database, with the format set to ROW and the image set to FULL.
Query method:
show global variables like 'binlog_format';
show global variables like 'binlog_row_image';Set method:
set global binlog_format = "ROW" ;
set global binlog_row_image = "FULL" ;- The target database only supports the cluster version of ClickHouse, and migration database tables only support cluster tables.
- When selecting “Manual Table Creation”, UDTS will not create the database table in the target database. Please pre-create the tables to be migrated in the target database before starting the migration task.
- When selecting “Automatic Table Creation”, if the database tables to be migrated do not exist in the target database, UDTS will automatically create them. If they already exist, UDTS will not recreate the table structure.
- When selecting “Automatic Table Creation”, the tables to be migrated must have a primary key or unique index. If this requirement is not met, please select “Manual Table Creation” and pre-create the tables in the target database.
- Incremental tasks can only choose “Manual Table Creation”.
Migration Content
| Migration Content | Description |
|---|---|
| Migration Structure | 1. Database and table structures and data 2. Before migration starts, it will not clean up existing databases and tables in the target database. If automatic table creation does not meet the requirements, customers can manually create tables in the target database in advance. |
| Migration Scope | Only migrates databases and tables that can be found when creating a task. Newly added tables during task execution will not be automatically migrated temporarily |
| DDL | Not supported |
| DML | insert/update |
Form Filling
Data Source Form
| Parameter Name | Description |
|---|---|
| Address Type | Supports three methods: internal network address, external network address, and direct line address. Internal network address requires filling in VPC and subnet information; external network address supports both IP and domain name; direct line address supports both IP and domain name. If using a domain name, the user’s network should have an external network exit. |
| Port | MySQL connection port |
| Username | MySQL connection username |
| Password | Corresponding user password for the MySQL database |
| Database Name | Name of the MySQL database. For all database transmission, please enter *. For specifying one database transfer, please enter the database name; for specifying multiple database transfers, sequentially input multiple database names, separated by commas. (If the database name contains spaces, incremental migration cannot be performed) |
| Table Name | MySQL transfer table name. It is only effective when “Database Name” is specified as one database. If not filled, all tables in the specified database will be migrated by default. To specify one table transfer, please enter the table name; to specify multiple table transfers, enter multiple table names sequentially, separated by commas. |
| Max Rate | The rate for external network/direct line is between 1-56 MB/s; for internal network it is between 1-1024 MB/s |
| Nolocks | Disabled by default. It needs to be enabled for competitor RDS services that cannot obtain SUPER privileges. For details on UDB obtaining SUPER privileges, please refer to FAQ |
Transmission Target Form
| Parameter Name | Description |
|---|---|
| TCP Address | ClickHouse cluster’s TCP connection address, default port is 9000. Multiple addresses are connected with ”,”, e.g., 192.168.1.100:9000,192.168.1.120:9000 |
| HTTP Address | ClickHouse cluster’s HTTP connection address, default port is 8123. Multiple addresses are connected with ”,”, e.g., 192.168.1.100:8123,192.168.1.120:8123 |
| Username | ClickHouse connection username |
| Password | Corresponding user password for the ClickHouse database |