Export data using modump

MatrixOne Intelligence supports exporting data using mo-dump.

What is `mo-dump`

mo-dump is a client utility for MatrixOne, which, like mysqldump, can be used to back up a MatrixOne database by exporting a file of type .sql, which contains SQL statements that can be executed to recreate the original database.

Using the mo-dump tool, you must be able to access the server running the MatrixOne instance. You must also have user permissions for the exported database.

`mo-dump` syntax structure

./mo-dump -u ${user} -p${password} -h ${host} -P ${port} -db ${database} [-local-infile=true] [-csv] [-tbl ${table}...] -net-buffer-length ${net-buffer-length} > {dumpfilename.sql}

Parameter definition

--u [user]: Username to connect to the MatrixOne service. Only users with database and table read permissions can use the mo-dump utility, default value: dump.

--p [password]: Valid password for MatrixOne user.

--h [host]: The host IP/HOST address of the MatrixOne service. Default value: 127.0.0.1.

--P [port]: Port of the MatrixOne service. Default value: 6001.

--db [Database Name]: Required Parameters. The name of the database to be backed up. Multiple databases can be specified, separated by ,.

--net-buffer-length [Package size]: Packet size, that is, the total size of SQL statement characters. Data packets are the basic unit of SQL export data. If no parameters are set, the default is 1048576 Byte (1M), and the maximum setting is 16777216 Byte (16M). If the parameter here is set to 16777216 Byte (16M), then when data larger than 16M is to be exported, the data will be split into multiple 16M data packets. Except for the last data packet, the other packet sizes are 16M.

--no-data: The default value is false. When set to true, it means that data is not exported, only table structures are exported.

--csv: The default value is false. When set to true, it means that the exported data is in csv format. The generated database and table structure and imported SQL statements will be saved in the generated sql file, and the data will be exported to the generated ${databaseName}_${tableName}.csv file generated in the current directory.

---local-infile: The default value is true, and only takes effect when the parameter -csv is set to true. When the parameter is true, the LOAD DATA LOCAL INFILE is in the sql file script output by mo-dump. When the parameter is false, LOAD DATA INFILE is in the sql file script output by mo-dump.

--tbl [tableName]: Optional parameter. If the parameter is empty, the entire database is exported. If you want to back up the specified table, you can add the parameters -tbl and tableName to the command. If multiple tables are specified, the table names are separated by ,.

-> {importStatement.sql}: Store the output SQL statements into the file importStatement.sql, otherwise output on the screen.

How to use `mo-dump`

Install the mo-dump tool

Download method 1 and download method 2. You need to install the download tool wget or curl first. If you do not install it, please install the download tool yourself first.

Install under macOS

Download method one: wget tool download binary packageDownload method two: curl tool download binary package

x86 architecture system installation package:

wget https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-darwin-x86_64.zip
unzip mo-dump-1.0.0-darwin-x86_64.zip

ARM architecture system installation package:

wget https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-darwin-arm64.zip
unzip mo-dump-1.0.0-darwin-arm64.zip

If the original github address is downloaded too slowly, you can try to download the image package from the following address:

wget https://githubfast.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-darwin-xxx.zip

x86 architecture system installation package:

curl -OL https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-darwin-x86_64.zip
unzip mo-dump-1.0.0-darwin-x86_64.zip

ARM architecture system installation package:

curl -OL https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-darwin-arm64.zip
unzip mo-dump-1.0.0-darwin-arm64.zip

If the original github address is downloaded too slowly, you can try to download the image package from the following address:

curl -OL https://githubfast.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-darwin-xxx.zip

Install under Linux

Download method one: wget tool download binary packageDownload method two: curl tool download binary package

x86 architecture system installation package:

wget https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-linux-x86_64.zip
unzip mo-dump-1.0.0-linux-x86_64.zip

ARM architecture system installation package:

wget https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-linux-arm64.zip
unzip mo-dump-1.0.0-linux-arm64.zip

If the original github address is downloaded too slowly, you can try to download the image package from the following address:

wget https://githubfast.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-linux-xxx.zip

x86 architecture system installation package:

curl -OL https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-linux-x86_64.zip
unzip mo-dump-1.0.0-linux-x86_64.zip

ARM architecture system installation package:

curl -OL https://github.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-linux-arm64.zip
unzip mo-dump-1.0.0-linux-arm64.zip

If the original github address is downloaded too slowly, you can try to download the image package from the following address:

curl -OL https://githubfast.com/matrixorigin/mo_dump/releases/download/1.0.0/mo-dump-1.0.0-linux-xxx.zip

Note

Due to the limitations of the linux kernel, mo-dump may not function properly on OSes with lower versions of kernels (less than 5.0), and you need to upgrade your kernel version.

Export MatrixOne Intelligence's database using `mo-dump`

mo-dump is very easy to use on the command line. See the following steps for example to export the sql file format complete database:

Select the target instance, clickConnect > Connect through third-party tools. You can check the host address, port number, user name and password of your instance on MatrixOne Intelligence in the sliding window on the right.

Note

mo-dump does not support the username format of the connection string (that is, the original format <accountname>:<username>:<rolename>) is not supported, and needs to be changed to <accountname>#<username>#<rolename>.
Open a terminal window on your local computer, enter the following command, connect to MatrixOne Intelligence, and export the database:
```
./mo-dump -u <accountname>#<username>#<rolename> -p password -h moc_host_address -P 6001 -db database > exported_db.sql
```

Example

Example 1

If you want to generate a backup of a single or multiple databases and all the tables in it, run the following command. This command will generate a backup of themydb1 andmydb2 databases and table structure and data in the importMydb.sql file. The importMydb.sql file will be saved in the current directory:

./mo-dump -u <accountname>#<username>#<rolename> -p password -h moc_host_address -P 6001 -db mydb1,mydb2 > importMydb.sql

**Example 2**

If you want to export the data of the tables in the database *mydb* to *CSV* format, the data of all tables in the database *mydb* will be exported in the current directory in the format of `${databaseName}_${tableName}.csv`. The generated database and table structure and the imported SQL statement will be saved in the *importMydbWithCsv.sql* file:


./mo-dump -u <accountname>#<username>#<rolename> -p password -h moc_host_address -P 6001 -db mydb -csv > importMydbWithCsv.sql
**Example 3**

If you want to specify a backup of a certain table or several tables in the database, you can run the following command. This command will generate a structure and data backup of the *t1* table and *t2* table in the database *db1* and save it in the *tab2.sql* file.

./mo-dump -u <accountname>#<username>#<rolename> -p password -h moc_host_address -db db1 -tbl t1,t2 > tab2.sql
**Example 4**

If you want to backup a certain table or several table structure in the database, you can run the following command. This command will generate the structure of the *t1* table and the *t2* table in the database *db1*, which is saved in the *tab_nodata.sql* file.

./mo-dump -u <accountname>#<username>#<rolename> -p password -h moc_host_address -db db1 -no-data -tbl t1,t2 > tab_nodata.sql

limit

mo-dump does not support exporting only data. If you want to generate backups of your data without a database and table structure, then you need to manually split the .sql file.
mo-dump only supports exporting databases that belong to the user, so in publishing subscriptions, the subscription side only has subscription statements for the sql that exports the subscription library through mo-dump.

Export data using modump

What is mo-dump

mo-dump syntax structure

How to use mo-dump

Install the mo-dump tool

Export MatrixOne Intelligence's database using mo-dump

Example

limit

What is `mo-dump`

`mo-dump` syntax structure

How to use `mo-dump`

Export MatrixOne Intelligence's database using `mo-dump`