Scenarios and examples|V4.0.0|OceanBase Loader and Dumper|docs|OceanBase Documentations

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 3 minutes.
ob.query.timeout.for.init.session=3

# This variable is used to initialize the session.
# The default value is 3 minutes.
ob.trx.timeout.for.init.session=3

# 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

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