Migrating from MySQL with db-archiver
In this tutorial, we'll walk you through the process of migrating from MySQL to Databend Cloud using db-archiver.
Before You Start
Before you start, ensure you have the following prerequisites in place:
- Docker is installed on your local machine, as it will be used to launch MySQL.
- Go is installed on your local machine, as it is required to install db-archiver.
- BendSQL is installed on your local machine. See Installing BendSQL for instructions on how to install BendSQL using various package managers.
Step 1: Launch MySQL in Docker
- Start a MySQL container on your local machine. The command below launches a MySQL container named mysql-server, creates a database named mydb, and sets the root password to
root:
docker run \
--name mysql-server \
-e MYSQL_ROOT_PASSWORD=root \
-e MYSQL_DATABASE=mydb \
-p 3306:3306 \
-d mysql:8
- Verify MySQL is running:
docker ps
Check the output for a container named mysql-server:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1a8f8d7d0e1a mysql:8 "docker-entrypoint.s…" 10 hours ago Up About an hour 0.0.0.0:3306->3306/tcp, 33060/tcp mysql-server
Step 2: Populate MySQL with Sample Data
- Log in to the MySQL container and enter the password
rootwhen prompted:
docker exec -it mysql-server mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.4 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- Switch to the mydb database:
mysql> USE mydb;
Database changed
- Copy and paste the following SQL to create a table named my_table and insert data:
CREATE TABLE my_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
value INT
);
INSERT INTO my_table (name, value) VALUES
('Alice', 10),
('Bob', 20),
('Charlie', 30);
- Verify the data:
mysql> SELECT * FROM my_table;
+----+---------+-------+
| id | name | value |
+----+---------+-------+
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 30 |
+----+---------+-------+
3 rows in set (0.00 sec)
- Quit the MySQL container:
mysql> quit
Bye
Step 3: Set Up Target in Databend Cloud
- Connect to Databend Cloud using BendSQL. If you're unfamiliar with BendSQL, refer to this tutorial: Connecting to Databend Cloud using BendSQL.
- Copy and paste the following SQL to create a target table named my_table:
CREATE TABLE my_table (
id INT NOT NULL,
name VARCHAR(255),
value INT
);
Step 4: Install db-archiver
Install db-archiver using the go install command:
go install github.com/databend/db-archiver@latest
Step 5: Configure & Run db-archiver
- Create a file named conf.json on your local machine with the following content:
{
// Replace the placeholders with your actual values:
"sourceHost": "127.0.0.1",
"sourcePort": 3306,
"sourceUser": "root",
"sourcePass": "root",
"sourceDB": "mydb",
"sourceTable": "my_table",
"sourceQuery": "select * from mydb.my_table",
"sourceSplitKey": "id",
"sourceWhereCondition": "id < 100",
"databendDSN": "https://cloudapp:{password}@{host}:443?warehouse={warehouse_name}",
"databendTable": "{database}.my_table",
"batchSize": 2,
"batchMaxInterval": 30,
"workers": 1,
"copyPurge": false,
"copyForce": false,
"disableVariantCheck": false,
"deleteAfterSync": false,
"maxThread": 10
}
- Run the following command in the directory where your conf.json file is located to start the migration:
db-archiver -f conf.json
Migration will begin as follows:
start time: 2025-01-22 21:45:33
sourcedatabase pattern ^mydb$
not match db: information_schema
sourcedatabase pattern ^mydb$
match db: mydb
sourcedatabase pattern ^mydb$
not match db: mysql
sourcedatabase pattern ^mydb$
not match db: performance_schema
sourcedatabase pattern ^mydb$
not match db: sys
INFO[0000] Start worker mydb.my_table
INFO[0000] Worker mydb.my_table checking before start
INFO[0000] Starting worker mydb.my_table
INFO[0000] db.table is mydb.my_table, minSplitKey: 1, maxSplitKey : 6
2025/01/22 21:45:33 thread-1: extract 2 rows (1.997771 rows/s)
2025/01/22 21:45:33 thread-1: extract 0 rows (1.999639 rows/s)
2025/01/22 21:45:33 thread-1: extract 2 rows (1.999887 rows/s)
2025/01/22 21:45:33 thread-1: extract 2 rows (1.999786 rows/s)
INFO[0000] get presigned url cost: 126 ms
INFO[0000] get presigned url cost: 140 ms
INFO[0000] get presigned url cost: 159 ms
INFO[0000] upload by presigned url cost: 194 ms
INFO[0000] upload by presigned url cost: 218 ms
INFO[0000] upload by presigned url cost: 230 ms
INFO[0000] thread-1: copy into cost: 364 ms ingest_databend=IngestData
2025/01/22 21:45:34 thread-1: ingest 2 rows (2.777579 rows/s), 68 bytes (94.437695 bytes/s)
2025/01/22 21:45:34 Globla speed: total ingested 2 rows (2.777143 rows/s), 29 bytes (40.268568 bytes/s)
INFO[0001] thread-1: copy into cost: 407 ms ingest_databend=IngestData
2025/01/22 21:45:34 thread-1: ingest 2 rows (2.603310 rows/s), 72 bytes (88.512532 bytes/s)
2025/01/22 21:45:34 Globla speed: total ingested 4 rows (2.603103 rows/s), 62 bytes (37.744993 bytes/s)
INFO[0001] thread-1: copy into cost: 475 ms ingest_databend=IngestData
2025/01/22 21:45:34 thread-1: ingest 2 rows (2.401148 rows/s), 70 bytes (81.639015 bytes/s)
2025/01/22 21:45:34 Globla speed: total ingested 6 rows (2.400957 rows/s), 93 bytes (34.813873 bytes/s)
INFO[0001] Worker dbarchiver finished and data correct, source data count is 6, target data count is 6
end time: 2025-01-22 21:45:34
total time: 1.269478875s
- Return to your BendSQL session and verify the migration:
SELECT * FROM my_table;
┌────────────────────────────────────────────┐
│ id │ name │ value │
├───────┼──────────────────┼─────────────────┤
│ 3 │ Charlie │ 30 │
│ 1 │ Alice │ 10 │
│ 2 │ Bob │ 20 │
└────────────────────────────────────────────┘