This topic describes some common scenarios that OBDUMBER supports and provides the corresponding use cases.
The following table provides information about the database used in the examples.
| Database information | Example value |
|---|---|
| Cluster name | Cluster A |
| OBProxy IP address | 10.0.0.0 |
| OBProxy port number | 2883 |
| Tenant name | TenantA |
| Password of a user (with at least the real-only privilege) under the sys tenant | **1*** (the password of the **u*** user in this example) |
| User account (with read/write privileges) under a business tenant | **u*** |
| Password of the user under the business tenant | **1*** |
| Schema name | USERA |
Full export of schema objects
Scenario description : Export the schema statements of all the objects in the USERA schema to the /home/admin/DUMP-1/ directory.
Example statement :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u **u*** -p **1*** --sys-password **1*** -c ClusterA -t tenantA -D USERA --ddl --all -f /Users/admin/DUMP-1/
Full export of data files in SQL format
Scenario description : Export data of all the objects in the USERA schema to the /home/admin/DUMP-1/ directory in the SQL format.
Example statement :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u **u*** -p **1*** --sys-password **1*** -c ClusterA -t tenantA -D USERA --sql --all -f /Users/admin/DUMP-1/
Full export of data files in CSV format
Scenario description : Export the structures and data of all the objects in the USERA schema to the /home/admin/DUMP-1/ directory in the CSV format.
Example statement :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u **u*** -p **1*** --sys-password **1*** -c ClusterA -t tenantA -D USERA --csv --all -f /Users/admin/DUMP-1/
Structure and data export in lite mode
Scenario description : Import the structures and data of all the tables and views in the USER Schema to the /home/admin/DUMP-1/ directory in lite mode.
Example statement :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u **u*** -p **1*** -c ClusterA -t tenantA -D USERA --ddl --sql --public-cloud --all -f /Users/admin/DUMP-1/
Data export in CUT format
Scenario : Export the data of test table in the test database in CUT format, specify the column separator for the exported data as '|@|', and truncate the column separator '|@|' at the end of the last column.
Sample statement :
[admin@localhost]>./obdumper -h 10.0.0.0 -P 2881 -u test -t mysql -c xx -p **1*** -D test --table 'test' -f /home/admin --cut --column-splitter '|@|' --trail-delimtier
Deduplication when exporting data
Scenario : Export the data of test table in the test database in CUT format, specify the column separator for the exported data as '|@|', and deduplicate record rows.
Sample statement :
[admin@localhost]>./obdumper -h 10.0.0.0 -P 2881 -u test -t mysql -c xx -p **1*** -D test --table 'test' -f /home/admin --cut --column-splitter '|@|' --distinct
Data export using a control file
Scenario : The data of the test table in the export database is in the CUT format. Specify the column separator of the exported data as '|@|' and the path of the control file as /home/admin.
Sample statement :
[admin@localhost]>./obdumper -h 10.0.0.0 -P 2881 -u test -t mysql -c xx -p **1*** -D test --table 'test' -f /home/admin --cut --column-splitter '|@|' --ctl-path '/home/admin'
The rules defined by the control file are as follows:
lang=java ( c1 "lpadb(c1,20,'x')", c2 "rpadb(c2,20,'x')" );
Export data from specified columns in a table
Scenario description: Data in the dept table in the database test is in the CUT format, and the --exclude-column-names parameter is used to specify the columns in the table that do not need to be exported.
Sample statement :
./obdumper -h 10.0.0.0 -P 2881 -u test -t mysql -c xx -p **1*** -D test --table'dept' --cut --column-splitter '|@|' --exclude-column-names 'deptno' --public-cloud
Export custom SQL query
Scenario Description: The result set returned after the SQL statement defined in the Export Parameter --query-sql is executed is in the CUT format.
Sample statement :
-- Single-table query and export ./obdumper -h 10.0.0.0 -P 2881 -u test -t mysql -c xx -p **1*** -D test --cut --column-splitter '|@|' --query-sql 'select deptno,dname from dept where deptno<3000' --public-cloud -- Export multi-table query. ./obdumper -h 10.0.0.0 -P 2881 -u test -t mysql -c xx -p **1*** -D test --cut --column-splitter '|@|' --query-sql 'select * from dept,STUDENT where 1=1;' --public-cloud
Export custom file name
Scenario description: The data of the dept table in the export database test is in the CUT format, and the name of the custom export file is filtest, that is, the export result is stored in the ./tmp0526/filetest.
Sample statement :
./obdumper -h 10.0.0.0 -P 2881 -u test -t mysql -c xx -p **1*** -D test --cut --column-splitter '|@|' --table 'dept' --file-name 'filetest.txt' --public-cloud -f ./tmp0526