一、前文
如果是使用开源版本IoTDB,那么有如下三种数据同步和备份的方法,可供选择。
如果是使用企业版本IoTDB(TimechoDB),那么一种数据同步的方法就够了,简单方便更好用。
- 数据库备份与迁移是数据库运维中的核心任务,其重要性不言而喻。
- 确保备份过程既简单快捷又稳定可靠,对于保障数据安全与业务连续性至关重要。
- 注意:IoTDB V1.3.2及之后版本使用tools/export-data、tools/import-data
- 注意:IoTDB V1.3.1及之前版本使用tools/export-csv、tools/import-csv
tools/import-data支持两种数据格式
- CSV:纯文本格式,存储格式化数据,需按照下文指定 CSV 格式进行构造
- SQL:包含自定义 SQL 语句的文件
二、导出
2.1 准备导出服务器
- 登录数据库
[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash sbin/start-cli.sh
---------------------
Starting IoTDB Cli
---------------------
_____ _________ ______ ______
|_ _| | _ _ ||_ _ `.|_ _ \
| | .--.|_/ | | \_| | | `. \ | |_) |
| | / .'`\ \ | | | | | | | __'.
_| |_| \__. | _| |_ _| |_.' /_| |__) |
|_____|'.__.' |_____| |______.'|_______/ version 1.3.2 (Build: aa0ff4a)
Successfully login at 127.0.0.1:6667
- 创建数据库
IoTDB> CREATE DATABASE root.test.test
Msg: The statement is executed successfully.
IoTDB> show databases
+--------------+----+-----------------------+---------------------+---------------------+
| Database| TTL|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|
+--------------+----+-----------------------+---------------------+---------------------+
|root.test.test|null| 1| 1| 604800000|
+--------------+----+-----------------------+---------------------+---------------------+
Total line number = 1
It costs 0.006s
- 写入数据库
IoTDB> INSERT INTO root.test.test(status) values(1)
Msg: The statement is executed successfully.
IoTDB> INSERT INTO root.test.test(status) values(2)
Msg: The statement is executed successfully.
IoTDB> INSERT INTO root.test.test(status) values(3)
Msg: The statement is executed successfully.
- 查询数据库
IoTDB> select * from root.test.test order by time desc
+-----------------------------+---------------------+
| Time|root.test.test.status|
+-----------------------------+---------------------+
|2024-08-04T09:29:08.893+08:00| 3.0|
|2024-08-04T09:29:06.757+08:00| 2.0|
|2024-08-04T09:29:04.169+08:00| 1.0|
+-----------------------------+---------------------+
Total line number = 3
It costs 0.141s
2.2 导出命令
bash tools/export-data.sh -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format> -datatype <true/false> -q <query command> -s <sql file>]
usage: ExportData -h <host> -p <port> -u <username> [-pw <password>] -td <targetDirectory>
[-f <targetFile>] [-s <sqlfile>] [-tf <timeformat>] [-tz <timeZone>] [-datatype
<datatype>] [-q <queryCommand>] [-type <exportType>] [-aligned <export aligned insert
sql>] [-linesPerFile <Lines Per File>] [-help] [-t <arg>]
-h,--host <host> Host Name (required)
-p,--port <port> Port (required)
-u,--username <username> Username (required)
-pw,--password <password> Password (required)
-td <targetDirectory> Target File Directory (required)
-f <targetFile> Export file name (optional)
-s <sqlfile> SQL File Path (optional)
-tf <timeformat> Output time Format in csv file. You can choose 1)
timestamp, number, long 2) ISO8601, default 3)
user-defined pattern like yyyy-MM-dd HH:mm:ss,
default ISO8601.
OutPut timestamp in sql file, No matter what time
format is set(optional)
-tz <timeZone> Time Zone eg. +08:00 or -01:00 (optional)
-datatype <datatype> Will the data type of timeseries be printed in the
head line of the CSV file?
You can choose true) or false) . (optional)
-q <queryCommand> The query command that you want to execute.
(optional)
-type <exportType> Export file type ?
You can choose csv) or sql) . (optional)
-aligned <export aligned insert sql> Whether export to sql of aligned (only sql optional)
-linesPerFile <Lines Per File> Lines per dump file.
-help,--help Display help information
-t,--timeout <arg> Timeout for session query
2.3 执行命令
[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash tools/export-data.sh -h '127.0.0.1' -p 6667 -u root -pw root -td ./ -q 'select * from root.test.test order by time desc' -type sql
------------------------------------------
Starting IoTDB Client Export Script
------------------------------------------
Export completely!
2.4 sql文件
dump0_0.sql
就是本次导出的sql文件。
三、导入
3.1 准备导入服务器
- 登录数据库
[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash sbin/start-cli.sh
---------------------
Starting IoTDB Cli
---------------------
_____ _________ ______ ______
|_ _| | _ _ ||_ _ `.|_ _ \
| | .--.|_/ | | \_| | | `. \ | |_) |
| | / .'`\ \ | | | | | | | __'.
_| |_| \__. | _| |_ _| |_.' /_| |__) |
|_____|'.__.' |_____| |______.'|_______/ version 1.3.2 (Build: aa0ff4a)
Successfully login at 127.0.0.1:6667
- 删除数据
IoTDB> DELETE FROM root.test.test.status where time < 40000000000000
Msg: The statement is executed successfully.
- 查询数据库,没有数据
IoTDB> select status from root.test.test
+----+
|Time|
+----+
+----+
Empty set.
It costs 0.184s
3.2 上传sql文件
将在2.4小节获得的dump0_0.sql上传到导入服务器中
3.3 导入命令
bash tools/import-data.sh -h <ip> -p <port> -u <username> -pw <password> -s <xxx.csv/sql> [-fd <./failedDirectory> -aligned <true/false> -batch <int> -tp <ms/ns/us> -typeInfer <boolean=text,float=double...> -lpf <int>]
usage: ImportData -h <host> -p <port> -u <username> [-pw <password>] -f <file or folder>
[-fd <failed file directory>] [-aligned <use the aligned interface>] [-help] [-tz
<timeZone>] [-batch <batch point size>] [-tp <timestamp precision (ms/us/ns)>]
[-typeInfer <type infer>] [-linesPerFailedFile <Lines Per FailedFile>]
-h,--host <host> Host Name (required)
-p,--port <port> Port (required)
-u,--username <username> Username (required)
-pw,--password <password> Password (required)
-f <file or folder> If input a file path, load a csv file,
otherwise load all csv file under this
directory (required)
-fd <failed file directory> Specifying a directory to save failed file,
default YOUR_CSV_FILE_PATH (optional)
-aligned <use the aligned interface> Whether to use the interface of aligned(only
csv optional)
-help,--help Display help information
-tz <timeZone> Time Zone eg. +08:00 or -01:00 (optional)
-batch <batch point size> 100000 (optional)
-tp <timestamp precision (ms/us/ns)> Timestamp precision (ms/us/ns)
-typeInfer <type infer> Define type info by
option:"boolean=text,int=long, ...
-linesPerFailedFile <Lines Per FailedFile> Lines per failed file
3.4 执行命令
- 导入sql文件
[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash tools/import-data.sh -f ./dump0_0.sql -h 127.0.0.1 -p 6667 -u root -pw root
------------------------------------------
Starting IoTDB Client Import Script
------------------------------------------
dump0_0.sql Import completely!
- 登录数据库
[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash sbin/start-cli.sh
---------------------
Starting IoTDB Cli
---------------------
_____ _________ ______ ______
|_ _| | _ _ ||_ _ `.|_ _ \
| | .--.|_/ | | \_| | | `. \ | |_) |
| | / .'`\ \ | | | | | | | __'.
_| |_| \__. | _| |_ _| |_.' /_| |__) |
|_____|'.__.' |_____| |______.'|_______/ version 1.3.2 (Build: aa0ff4a)
Successfully login at 127.0.0.1:6667
- 查询数据是否成功导入
IoTDB> select * from root.test.test order by time desc
+-----------------------------+---------------------+
| Time|root.test.test.status|
+-----------------------------+---------------------+
|2024-08-04T09:29:08.893+08:00| 3.0|
|2024-08-04T09:29:06.757+08:00| 2.0|
|2024-08-04T09:29:04.169+08:00| 1.0|
+-----------------------------+---------------------+
Total line number = 3
It costs 0.073s
五、参考
觉得好,就一键三连呗(点赞+收藏+关注)