MySQL · MariaDB · GUI

Copy a MySQL table between two servers

pimvdmolen.nl · · ~6 min read

Copying orders from a laptop MySQL onto a VPS is a two-connection job. INSERT ... SELECT only works when both databases sit on the same instance. A desktop client that already has both connections open can move the table without a dump file in between. This how-to uses that wizard on MySQL and MariaDB, then the one-table mysqldump you use when you want a file you can read first.

Open both servers first

Save the source and the target as two connections and connect to both before you start. The target dropdown only lists connections you already stored. If the VPS listens on 127.0.0.1:3306 and the public interface does not expose 3306, put the SSH tunnel on that saved connection. A tunnel you opened only in a terminal does not show up as a target. The steps for keeping 3306 closed are in Connect a MySQL GUI to a VPS without opening 3306.

On one server, skip the wizard. CREATE TABLE staging.orders LIKE shop.orders plus INSERT INTO staging.orders SELECT * FROM shop.orders never leaves the host. The wizard also refuses the run when source and target are the same database.

Run the copy from the tree

Right-click the table and choose Copy Table(s) To... The table you clicked is pre-checked. Tick checkboxes and press Copy... on the bar under the tree to include more than that one object. Right-click the database and choose Copy Tables To... when every table and view should start selected. Procedures, functions, triggers, and events stay in the list, so the trigger that writes to orders can go in the same run.

  1. The header names the source connection and database. The left tree groups Tables, Views, Procedures, Functions, Triggers, and Events, with all and none links.
  2. Choose Target connection, then Target database. information_schema, performance_schema, mysql, and sys are left out. If the dialog says "Could not connect to the target", fix host, port, user, or SSH on that connection and select it again.
  3. Leave Structure + data selected, or switch to Structure only. Structure only runs CREATE and copies no rows. Batch size is 100, 500, 1,000, or 2,500 rows, default 1,000, and it is disabled for structure only.
  4. Pick Drop & recreate existing (the default) or Skip existing objects. Drop runs DROP TABLE IF EXISTS on the target, or the matching DROP for a view, procedure, function, trigger, or event, then creates the object again. Skip leaves an existing name untouched. It does not merge rows into the old table.
  5. Click Copy. The confirm panel shows the object count, the source database, and the target database. Click Confirm.

The log prints lines such as Created table orders and a running row count. The summary reports copied, skipped, failed, and rows transferred. One failed object does not cancel the rest.

What lands on the target

The client reads SHOW CREATE on the source and runs that statement on the target. Table rows follow as batched INSERTs. Views are structure only. For the run, the target session uses FOREIGN_KEY_CHECKS=0, then the client sets it back to 1, so a child table can be created before its parent. Order is tables, then views, then procedures, functions, and events, then triggers, because a trigger needs its table to exist.

DEFINER clauses are removed from views, routines, triggers, and events. The definer account on your laptop often does not exist on the VPS, and a leftover DEFINER makes CREATE fail with access denied. For views and triggers the source database name is stripped so the object is created in the target database.

A foreign key stays inside CREATE TABLE. Copy the parent in the same run if later inserts must succeed. With checks off, MySQL creates the child even when the parent is missing, and inserts that reference the missing parent fail afterward.

If one row is wider than the target max_allowed_packet, that table is marked failed. Oversized batches are split first. The summary then offers Increase max_allowed_packet on target & retry, which raises the variable and retries only those tables. The account needs permission to change it.

This copy is not one consistent snapshot. Rows are read with SELECT in batches. Writes on the source during the run can duplicate a row or skip one. Stop writes, or use the dump below when you need an InnoDB point in time.

GUI copy versus a one-table dump

Path Use it when Leaves a file
Copy wizard between two saved connections The desktop can reach both hosts and you want rows moved now No
mysqldump of one table, then mysql on the other host You want to read the SQL, or one host is not open in the GUI Yes
CREATE TABLE ... LIKE plus INSERT ... SELECT Both schemas are on the same instance No

A file you can inspect:

mysqldump --single-transaction --no-tablespaces \
  -h 127.0.0.1 -P 3306 -u reader -p shop orders > orders.sql
mysql -h 127.0.0.1 -P 3307 -u writer -p shop < orders.sql

--single-transaction needs InnoDB and avoids holding LOCK TABLES for the whole dump. --no-tablespaces avoids the MySQL 8 error that asks for the PROCESS privilege. Ports 3306 and 3307 here are local forwards or two local servers. On MySQL with GTID, add --set-gtid-purged=OFF so the file does not set gtid_purged on the target. MariaDB's mariadb-dump does not use that flag.

GUI export of the same table is in Export a MySQL dump: GUI vs mysqldump. Load the file with How to Import a SQL Dump into MySQL. That path is more clicks and easier to review. TabulaSQL is one free client that also copies between two open MySQL or MariaDB connections, with the batch size and drop-or-skip choice: https://tabulasql.com/download. DBeaver, Workbench, and HeidiSQL can move a table too. Compare whether both connections stay open, whether you can skip an existing name, and whether the tool asks before it drops the target.

Confirm the rows match

On both hosts run SELECT COUNT(*) FROM orders and, when id is the primary key, SELECT MAX(id) FROM orders. Counts still miss a changed column, so compare a checksum:

SELECT BIT_XOR(CRC32(CONCAT_WS('|', id, updated_at))) FROM orders;

A different sql_mode, charset, or a trigger that rewrites rows on insert will diverge even when the log says the copy succeeded. Read the failed line before you run again with Drop & recreate existing, because that option deletes the target table first. If the log says the object was skipped, the target still has the old definition.

Frequently asked questions

Yes. Open both connections in a desktop client, right-click the table, choose Copy Table(s) To..., pick the target connection and database, then confirm. The client runs SHOW CREATE on the source and batched INSERTs on the target.

Yes. That option runs DROP TABLE IF EXISTS on the target, or the matching DROP for a view, routine, trigger, or event, before it creates the object again. Choose Skip existing objects if the current target table must stay.

One row is larger than the target server's max_allowed_packet. The wizard can raise that variable on the target and retry those tables if your account is allowed to change it. Otherwise raise the server limit or move that table with a dump.