Data transfer

Data transfer copies tables, views, procedures and functions from one database to another — including between different engines. MySQL to PostgreSQL, SQLite to SQL Server, production to a local copy.

Open it from Tools → Data Transfer, or right-click a database and choose Data Transfer.

The wizard

1. Source and target

Pick a connection and database on each side. NabuSQL shows the connection type, name, host, port and server version for both, so you can confirm you are pointed where you think you are.

2. Select objects

The object tree groups everything into Tables, Views and Functions / Procedures. Tick what you want to move.

Highlight a single table to configure it individually: its target object name, and its mode — Auto (structure and records) or DDL only (structure, no data).

3. Options

Table options

Option	Effect
Create tables	Create the target tables. Turn off when they already exist
Include indexes	Recreate indexes
Include default values	Carry column defaults over
Include foreign key constraints	Recreate FK constraints
Include engine/table type	MySQL engine, e.g. InnoDB
Include character set	Carry the charset over
Include auto increment	Keep auto-increment columns
Include triggers	Copy the triggers attached to the selected tables — same engine only, see below
Drop target objects before create	Drop what is there first
Adjust DEFINER to target user	Rewrite the DEFINER of copied views, routines and triggers to the account the transfer runs as. On by default

Record options

Option	Effect
Create records	Copy the rows, not just the structure
Use transaction	Wrap the transfer so a failure rolls back
Use extended insert statements	Multi-row INSERTs — considerably faster
Continue on error	Keep going past failing rows instead of stopping
Batch size	Rows per batch

Other

• Create target database if not exists — creates the database when it is missing.

4. Run

Progress is shown per phase — creating schema, then copying — and per object. The result is Transfer complete, Completed with errors with the list, or an error.

Triggers

Include triggers carries over the triggers that hang off the tables you selected — there is no separate trigger group in step 2. Two things are worth knowing:

• They are created after the rows are copied, as the last phase of the table. Created before the insert loop they would fire once per copied row, which would quietly corrupt the data rather than fail.
• Same engine only. A trigger body cannot be translated between dialects, so on a cross-engine transfer triggers are skipped — silently, without failing the transfer.

DEFINER

MySQL and MariaDB stamp every view, procedure, function and trigger with DEFINER=`user`@`host`, and that account has to exist on the server the object lands on. Copy a view defined by root@localhost onto a server where you connect as web@% and it either refuses to be created or breaks the first time someone uses it.

Adjust DEFINER to target user — on by default — rewrites the DEFINER of every copied view, routine and trigger to the account the transfer is connected as on the target side. When that account cannot be determined, the clause is removed instead and the server fills it in itself.

Turn it off to copy the definers verbatim, which is what you want when you are replicating a server whose accounts match on both sides.

Profiles

Save Profile stores the whole configuration — source, target, object selection and options. Load Profile brings it back. Worth doing for any transfer you run more than once, such as refreshing a development database from production.

Changing engine: use this, not a dump

A dump — from Backup and restore, or from mysqldump — is written in the source engine's own SQL and is meant to be restored onto the same kind of server. Pointing one at a different engine fails on the first statement, and the reasons are not superficial:

• Identifiers are quoted the engine's way. MySQL backticks are a syntax error everywhere else, and they appear on every table and column in the file.
• Types do not line up — int(11), datetime, longtext, inline ENUM(...).
• Whole constructs have no counterpart — AUTO_INCREMENT, ENGINE=InnoDB, DEFAULT CHARSET, inline KEY definitions inside CREATE TABLE, LOCK TABLES.
• String escaping differs. MySQL escapes a quote as \'; PostgreSQL reads the backslash literally, so the string ends early. That one corrupts data rather than failing loudly, which makes it the worst of the four.

Data transfer sidesteps all of this because it never moves SQL text. It reads the source schema, regenerates the DDL for the target dialect, and copies rows through the driver with values bound as parameters rather than pasted into statements.

The rule of thumb:

	Use
Same engine, or a file in the middle	Backup and restore
Different engine	Data transfer

Cross-engine notes

Moving between engines means types are mapped, not copied. Check the result when the source uses engine-specific types — MySQL ENUM, PostgreSQL arrays, SQL Server UNIQUEIDENTIFIER.

Procedures and functions are copied as source text. SQL dialects differ between engines, so a routine that works on MySQL will often need editing after landing on PostgreSQL. Tables and views are the parts that travel cleanly.

Start with a dry run

For a first transfer between unfamiliar servers, select one table, set DDL only, and run it. You will see how the types map before committing to a full copy.

Between MySQL and MariaDB

The two share a dialect, so column types normally travel exactly as declared. The exception is a type the target version does not have — typically MariaDB's newer types going to MySQL or to an older MariaDB. Those columns are created as the plain type that holds the same values, and the preview warns about it before anything runs:

Source type	On a target that lacks it
UUID	CHAR(36)
INET4	VARCHAR(15)
INET6	VARCHAR(45)
VECTOR(n)	VARBINARY(4·n) — the same bytes
XMLTYPE	LONGTEXT
JSON (MySQL before 5.7.8, MariaDB before 10.2.7)	LONGTEXT

What the target has is decided from its real version — the same rules as the type list — so it also works when a MariaDB server was saved as a MySQL connection.

Related

• Backup and restore — same server, file in the middle.
• Comparing structure and data — check the two sides match afterwards.