Multi-database export

OBDUMPER V4.3.5 and later versions support exporting database objects to multiple databases or schemas in a single export task, while maintaining the original single-database export behavior.

Implementation

Single-database mode

If the object parameters such as --table and --view do not contain specific databases or schemas (i.e., no periods are present), it is considered a single-database export. The default database specified by -D is used.

Multi-database mode

If any object parameter value contains specific databases or schemas (i.e., periods are present), it enters multi-database export mode. For example, test.table1, test1.*, *.*, test[0-9].tbl, test*.table, or test?.tbl.

In multi-database mode, a single multi-database export command is split into multiple single-database export subtasks. The details are as follows:

• The object parameters such as --table and --view are parsed, and <schema.object> is distributed to corresponding database/schema subtasks, specifying the export scope.

• If an object does not contain specific databases or schemas (e.g., --table 'db1.tbl1, tbl2' -D 'db2'), the default database specified by -D is used, while other options remain unchanged.

In multi-database mode, exporting objects to different databases/schemas can be understood as executing multiple single-database export tasks. Here is an example:

obdumper -D test --table 'test1.tbl1,test2.tbl2' ...
-> obdumper -D test1 --table tbl1 ...
-> obdumper -D test2 --table tbl2 ...

obdumper -D test --table 'tbl1,test2.tbl2' ...
-> obdumper -D test --table tbl1 ...
-> obdumper -D test2 --table tbl2 ...

The default export directory structure will add a database/schema level directory on the original directory level. Here is an example:

./outputs/
└── data
    ├── test
    │   ├── TABLE
    │   │   ├── sample_tbl1.csv
    │   │   ├── sample_tbl1-schema.sql
    │   │   ├── sample_tbl2.csv
    │   │   └── sample_tbl2-schema.sql
    │   └── VIEW
    │       ├── sample_tbl1_view-schema.sql
    │       └── sample_tbl2_view-schema.sql
    └── test1
        ├── TABLE
        │   ├── sample_tbl1.csv
        │   ├── sample_tbl1-schema.sql
        │   ├── sample_tbl2.csv
        │   └── sample_tbl2-schema.sql
        └── VIEW
            ├── sample_tbl1_view-schema.sql
            └── sample_tbl2_view-schema.sql

7 directories, 12 files

Examples

Scenario 1: Single-database export (unchanged)

Export the test database to /outputs.

./obdumper -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** -D test --csv --table '*' -f /outputs

Scenario 2: Multi-database export (export multiple databases in one task)

• Method 1

  ./obdumper -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test[1-3].*' -f /outputs
  

• Method 2

  ./obdumper -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test1.*, test2.*, test3.*' -f /outputs
  

• Method 3

  ./obdumper -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test1.*' --table 'test2.*' --table 'test3.*' -f /outputs
  

Scenario 3: Tenant-level export (indirectly achieving full-tenant export)

Export all objects from all databases, including definitions and data.

./obdumper -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --table '*.*' --ddl --csv --all -f /outputs