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 | xx.x.x.x |
| OBProxy port number | 2883 |
| Tenant name | TenantA |
| Password of a user (with at least the real-only privilege) in the sys tenant | **1*** (the password of the user **u***) |
| User account (with read and write privileges) in the business tenant | **u*** |
| Password of the user in 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 xx.x.x.x -P 2883 -u **u*** -p **1*** --sys-password **1*** -c ClusterA -t tenantA -D USERA --ddl --all -f /Users/admin/DUMP-1/
Full data export 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 xx.x.x.x -P 2883 -u **u*** -p **1*** --sys-password **1*** -c ClusterA -t tenantA -D USERA --sql --all -f /Users/admin/DUMP-1/
Full data export 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 xx.x.x.x -P 2883 -u **u*** -p **1*** --sys-password **1*** -c ClusterA -t tenantA -D USERA --csv --all -f /Users/admin/DUMP-1/
Export of structures and data in limited mode
Scenario description : Export the structures and data of all the tables and views in the USERA schema to the /home/admin/DUMP-1/ directory in limited mode.
Example statement :
[admin@localhost]> ./obdumper -h xx.x.x.x -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 description : 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.
Example statement :
[admin@localhost]>./obdumper -h xx.x.x.x -P 2881 -u **u*** -t mysql -c xx -p **1*** -D test --table 'test' -f /home/admin --cut --column-splitter '|@|' --trail-delimtier
Deduplication during data export
Scenario description : 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.
Example statement :
[admin@localhost]>./obdumper -h xx.x.x.x -P 2881 -u **u*** -t mysql -c xx -p **1*** -D test --table 'test' -f /home/admin --cut --column-splitter '|@|' --distinct
Data export using a control file
Scenario description : Use the control file in the /home/admin directory to export data from the test table of the test database in the CUT format, and specify the column separator for the exported data as '|@|'.
Example statement :
[admin@localhost]>./obdumper -h xx.x.x.x -P 2881 -u **u*** -t mysql -c xx -p **1*** -D test --table 'test' -f /home/admin --cut --column-splitter '|@|' --ctl-path '/home/admin'
The following example shows a rule defined in the control file:
lang=java
(
c1 "lpadb(c1,20,'x')",
c2 "rpadb(c2,20,'x')"
);
Data export from specified table columns
Scenario description: Export data of the dept table in the test database in the CUT format and use the --exclude-column-names parameter to specify the table columns whose data does not need to be exported.
Example statement :
./obdumper -h xx.x.x.x -P 2881 -u **u*** -t mysql -c xx -p **1*** -D test --table'dept' --cut --column-splitter '|@|' --exclude-column-names 'deptno' --public-cloud
Export of custom SQL queries
Scenario description: Export the returned result sets of SQL statements defined in the --query-sql parameter in CUT format.
Example statement :
-- Export of single-table queries
./obdumper -h xx.x.x.x -P 2881 -u **u*** -t mysql -c xx -p **1*** -D test --cut --column-splitter '|@|' --query-sql 'select deptno,dname from dept where deptno<3000' --public-cloud
-- Export of multi-table queries
./obdumper -h xx.x.x.x -P 2881 -u **u*** -t mysql -c xx -p **1*** -D test --cut --column-splitter '|@|' --query-sql 'select * from dept,STUDENT where 1=1;' --public-cloud
Customization of the name of the exported file
Scenario description: Export data of the dept table in the test database in the CUT format, name the exported file as filetest, and save the file in the ./tmp0526/filetest directory.
Example statement :
./obdumper -h xx.x.x.x -P 2881 -u **u*** -t mysql -c xx -p **1*** -D test --cut --column-splitter '|@|' --table 'dept' --file-name 'filetest.txt' --public-cloud -f ./tmp0526