MySQL Restore Table

$ mysqldump --host originHOST originDB originTable | mysql targetHOST targetDB

Note: The TABLE does not need to be articulated on the destination server, the TABLE is specified by the mysqldump.

e.g. to replace commodore.media.slugs with db9.media.slugs

$ mysqldump -h db9 media slugs | mysql media

mysqldump Options

--opt

This option, enabled by default, is shorthand for the combination of

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly. Because the –opt option is enabled by default, you only specify its converse, the –skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by –opt.

--add-drop-table

Write a DROP TABLE statement before each CREATE TABLE statement.

--add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. See Section 8.2.5.1, “Optimizing INSERT Statements”.

--create-options

Include all MySQL-specific table options in the CREATE TABLE statements.

--disable-keys, -K

For each table, surround the INSERT statements with /!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of MyISAM tables.

--lock-tables, -l

For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, –single-transaction is a much better option than –lock-tables because it does not need to lock the tables at all. Because –lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states. Some options, such as –opt, automatically enable –lock-tables. If you want to override this, use –skip-lock-tables at the end of the option list.

--quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

--set-charset

Write SET NAMES default_character_set to the output. This option is enabled by default. To suppress the SET NAMES statement, use –skip-set-charset.

To disable the default options of –opt, use – skip-opt.

--skip-opt

Turn off options set by –opt.

Sources:

Souce MySQL 8.0 Manual: mysqldump - A DatabaseBackup Program