This topic describes some common scenarios that OBDUMPER supports and provides the corresponding use cases.
The following table provides information about the database used in the examples.
| Database configuration item | Example |
|---|---|
| Cluster name | cluster_a |
| IP address of the OceanBase DataBase Proxy (ODP) host | 10.0.0.0 |
| ODP port number | 2883 |
| Tenant name of the cluster | mysql |
| Password of the root/proxyro user under the SYS tenant | ****** |
| User account (with read/write privileges) under the business tenant | test |
| Password of the user under the business tenant | ****** |
| Schema name | USERA |
Export DDL definition files
Scenario description : Export the DDL statements of all supported objects in the USERA schema to the /output directory.
Sample code :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --ddl --all -f /output
Export CSV data files
File format definition : A CSV data file has the .csv file name extension. CSV is a file format that uses commas (,) as the separator. CSV data files store tabular data in the form of plain text. You can open them by using a text editor or Excel.
Scenario description : Export the data of all supported objects in the USERA schema to the /output directory in the CSV format.
Sample code :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --all -f /output
Export SQL data files
File format definition : An SQL data file has the .sql file name extension. SQL is a file format compiled in the SQL language. SQL data files are mainly used to store and retrieve data and update and manage relational database systems. You can open these files by using a text editor or SQL software.
Scenario description : Export the data of all supported objects in the USERA schema to the /output directory in the SQL format.
Sample code :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --sql --all -f /output
Export CUT data files
File format definition : A CUT data file has the .dat file name extension. CUT is a file format that uses a character string as the separator. Data in a CUT file is separated by the corresponding character string. You can open a CUT file by using a text editor or Excel.
Scenario description : Export the data of the table named "table" in the USERA schema to the /output directory in the CUT format, with |@| being the separator and |@| appended to the end of each row.
Sample code :
./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --table'table' -f /output --cut --column-splitter '|@|' --trail-delimtier
Customize the name of an exported file
Scenario description: Export the data of the table named "table" in the USERA schema to the /output directory in the CUT format, with the exported file being named as "filetest".
Sample code :
./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --cut --column-splitter '|@|' --table 'table' --file-name 'filetest.txt' --public-cloud -f /output
Set session-level variables
# This variable is used to initialize the session.
# The default value is 5 minutes.
ob.query.timeout.for.init.session=5
# This variable is used to initialize the session.
# The default value is 5 minutes.
ob.trx.timeout.for.init.session=5
# This variable is used to query metadata, such as
# to query the database;
# to query the row key, primary key, and macro range;
# to query the primary key;
# to query the unique key;
# to query load status;
# The default value is 5 minutes.
ob.query.timeout.for.query.metadata=5
# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.query.timeout.for.dump.record=24
# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.query.timeout.for.dump.custom=5
# This variable is used to execute DDL statements, such as statements for loading the schema and truncating the table.
# The default value is 1 minute.
ob.query.timeout.for.exec.ddl=1
# This variable is used to execute DML statements, such as the statement for deleting the table.
# The default value is 1 hour.
ob.query.timeout.for.exec.dml=1
# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.trx.timeout.for.dump.record=24
# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.trx.timeout.for.dump.custom=5
# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.net.read.timeout.for.dump.record=24
# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.net.read.timeout.for.dump.custom=5
# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.net.write.timeout.for.dump.record=24
# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.net.write.timeout.for.dump.custom=5
# This variable is used to set the session variable ob_proxy_route_policy.
# The default value is follower_first.
ob.proxy.route.policy=follower_first
Use a control file to export data
Scenario description : Export the data of the table named "table" in the USERA schema to the /output directory in the CUT format, with '|@|' being the separator. The path of the control file is /output.
Sample code :
./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --table'table' -f /output --cut --column-splitter '|@|' --ctl-path /output
The following code shows a sample rule defined in the control file:
lang=java
(
c1 "lpadb(c1,20,'x')",
c2 "rpadb(c2,20,'x')"
);
Export data from specified table columns
Scenario description: Export the data of the table named "table" in the USERA schema to the /output directory in the CUT format, with the data in columns specified in --exclude-column-names being excluded from the export.
Sample code :
./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA -f /output --table'table' --cut --column-splitter '|@|' --exclude-column-names 'deptno' --public-cloud
Export the result set of a custom query
File format definition : An SQL data file has the .sql file name extension. SQL is a file format compiled in the SQL language. SQL data files are mainly used to store and retrieve data and update and manage relational database systems. You can open these files by using a text editor or SQL software.
Scenario description: Export the returned result set of the SQL statement defined in the --query-sql option in the CUT format.
Sample code :
-- Export of single-table queries
./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --cut --column-splitter '|@|' --query-sql 'select deptno,dname from dept where deptno<3000' --public-cloud
-- Export of a multi-table query
./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --cut --column-splitter '|@|' --query-sql 'select * from dept,STUDENT where 1=1;' --public-cloud
Export all definitions and data in limited mode
Scenario description : Export the definitions and data of all tables and views in the USERA schema to the /output directory in limited mode.
Sample code :
[admin@localhost]> ./obdumper -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --ddl --sql --public-cloud --all -f /output