6.9 wbcopytables Utility

wbcopytables is a command-line utility included in MySQL Workbench that enables you to copy table data from a supported source database server to MySQL. It is used by the MySQL Workbench Migration Wizard to copy data after the schema is migrated and created in the target MySQL server.

wbcopytables can connect to the source database using either ODBC, the Python DBAPI, or the native MySQL client library.

The copy executes a SELECT statement on the source database and then INSERT to insert the retrieved rows into the target MySQL server.

Table 6.2 File Location (Default)

Operating System	Location
Linux	/usr/bin/wbcopytables
macOS	/Applications/MySQLWorkbench.app/Contents/MacOS/wbcopytables
Windows	C:\Program Files\MySQL\MySQL Workbench 8.0\wbcopytables.exe

Connection Parameters

Option parameters for source and target connections are:

• --odbc-source=ODBC_connection_string: The syntax of the ODBC connection string uses standard ODBC syntax. You can also use a ODBC data source name (DSN).

• --mysql-source=MySQL_connection_string: Use for MySQL sources (when doing a MySQL to MySQL migration or copy). It uses the same syntax as the MySQL Utilities:

  • For TCP/IP connections: username[:password]@host:port

  • For local socket connections: username[:password]@::socket_path

• --source-password: Passes the connection password of the data source.

• --target=MySQL_connection_string: Specifies the target connection.

• --passwords-from-stdin: Passes a passwords through STDIN. Source and target passwords must be separated by a tab character.

You can use ODBC specific data source options from the source RDBMS to specify the number of rows to fetch at a time for the source SELECT statement.

Option parameters for source and target connections that support SSH tunneling to copy data are:

• --source-ssh-port=ssh port

• --source-ssh-host=ssh host

• --source-ssh-user=ssh user

• --source-ssh-password=ssh password

• --target-ssh-port=ssh port

• --target-ssh-host=ssh host

• --target-ssh-user=ssh user

• --target-ssh-password=ssh password

Table Specification

One or more tables can be specified in the command line for the copy operation. There are two copy types:

• Full table copy: --table

• Range copy: --table-range

Both table copy types require a set of common arguments:

• Source schema: The schema or catalog to which the table belongs. If quoting is required, it must be done using the syntax from the source RDBMS. For example, SQL Server uses [square_brackets].

• Source table: The table to copy. If the source RDBMS uses a schema name in addition to a catalog, both schema and table must be specified here and separated by a dot. For example, [dbo].[mytable].

• Target schema: The name of the MySQL schema. If quoting is needed, it must use the MySQL backtick syntax. For example, `sakila`.

• Target table: The name of the MySQL table.

• Select expression: The list of fields to SELECT. This will be inserted verbatim into the source SELECT statement.

  Caution

  Use caution as this expression is copied directly into the source SELECT statement.

For the select expression, if both the source and target tables have the same fields in the same order, and use compatible types, you can simply pass * here, which will build a query like "SELECT * FROM [dbo].[mytable]". If not, you can specify the fields as you would in the SELECT statement, which are comma (,) separated and with proper escaping/quoting specific to the source RDBMS. You can also specify typecasts and/or data conversions that the source RDBMS supports. For example:

[client_id], [name], [address], AsText([location])

Because each option must be interpreted as a single option by the wbcopytables command, you must perform OS shell specific quoting whenever necessary. Usually, quoting your parameter values with 'single' or "double" quotes is enough. This is in addition to any database specific quoting you use.

Full Table Copy

This argument performs a full SELECT on the source table, fetches records, and then inserts them into the target table.

There are no additional arguments required.

The --table syntax is as follows:

--table Source_Schema Source_Table Target_Schema Target_Table Select_Expression

Range Copy

This argument performs a SELECT copy on the source table for the specified range. The table must have a numeric UNIQUE NOT NULL or PRIMARY KEY that is used to create a WHERE expression for the range.

The --table-range syntax is as follows:

--table-range Source_Schema Source_Table Target_Schema Target_Table Select_Expression Source_Key Range_Start Range_End

The generated expression is:

key_column >= range_start AND key_column <= range_end

If you specify -1 for Range_End, then the expression is:

key_column >= range_start

Other Options

• --thread-count=Number: If you are copying more than one table, you can use this option to divide the tables across several threads. There is no support for dividing a single table across many threads.

• --count-only: Only performs a COUNT(*) of the SELECT generated by the --table option that was used. The target schema and table can be omitted in this case.

• --truncate-target: Executes a TRUNCATE TABLE command on each target table that is copied.

Trigger Handling

Because there is no way to temporarily disable triggers in MySQL and they can affect the copy process, MySQL Workbench will backup and drop all triggers from the target MySQL database before the copy process starts, and then these triggers are restored after the copy finishes. The triggers are backed up in the target schema under a table named wb_tmp_triggers.

• --disable-triggers-on=Schema_Name: Performs the backup and DROP process for all triggers in the specified schema.

• --reenable-triggers-on=Schema_Name: Restores triggers previously backed up to the wb_tmp_triggers table.

• --dont-disable-triggers: Bypasses the trigger disabling step.