5.8 The wbcopytables Tool

wbcopytables is a command line utility included in MySQL Workbench that allows you to copy table data from a supported source database server to MySQL. It is used by the 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 is performed by executing a SELECT statement on the source database, and then INSERT inserts the retrieved rows to the target MySQL server. Several options allow you to control the data that is copied.

Table 5.1 File Location (Default)

Operating SystemLocation
Linux/usr/bin/wbcopytables
OS X/Applications/MySQLWorkbench.app/Contents/MacOS/wbcopytables
WindowsC:\Program Files (x86)\MySQL\MySQL Workbench 6.2\wbcopytables.exe

Connection Parameters

Options for the source connection 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/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

You can pass the connection password by using the --source-password option.

For the target connection, the option is: --target=MySQL_connection_string.

You can use the --passwords-from-stdin option to pass 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.

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/catalog the table belongs to. 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 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 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 will be:


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 perform a COUNT(*) of the SELECT which would be generated by the --table option that was used. The target schema and table can be omitted in this case.

  • --truncate-target: Execute 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: Bypass the trigger disabling step.