Scenarios and examples

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.

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

Use key files

OBLOADER & OBDUMPER 3.1.0 and later versions support the use of a key file as a substitution of specifying the password of the SYS tenant on the command line. Perform the following steps:

1. Use OpenSSL to generate public and private keys and encrypt sys-password.

   # 1. Use genrsa to generate the private key.
   openssl genrsa -out xxx.pem 1024
   
   # 2. Use the private key to generate the public key.
   openssl rsa -in xxx.pem -pubout -out xxx.pem
   
   # 3. Convert the private key format and specify it in properties.
   openssl pkcs8 -topk8 -in xxx.pem -out xxx.pem -nocrypt
   
   # 4. Use the public key to encrypt the sys-password file and output the ciphertext to the file.
   openssl rsautl -encrypt -pubin -inkey xxx.pem -in pwd.txt -out xxx.txt
   

2. Modify the decrypt.properties file in the conf directory.

   # Your encrypted password path.
   # Enter the address of the encrypted password, corresponding to xxx.txt in Step 1.
   encrypted.password.path=xxx/xxx/xxx.txt
   
   # Your private key path.
   # Enter the address of the private key file generated in the third substep in Step 1, which is xxx.pem.
   private.key.path=xxx/xxx/xxx.pem
   
   # The full class name of your own decryption jar package.
   # If you want to decrypt sys-password using your own or default decryptor,
   # you must provide your class name or default class name.
   # If you do not provide the decryption jar package, the default decryptor is used.
   decrypt.class.name=com.oceanbase.tools.loaddump.decrypt.OpenSslDecryptor
   

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

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