MySQL: Copy Database without Saving Dump File


For PostgreSQL, see PostgreSQL: Copy Database without Saving Dump File.

When we copy MySQL data, create dump file with mysqldump and execute that SQL with mysql.

But we can copy data without creating intermediate dump file.

Command

Commands before Combined

Here are 2 commands, exporting dump file and importing the dump file. Combining those commands, we can get the preceding command.

Export
Import

In Addition

If you add the option --single-transaction to mysqldump command, you can get the data in one transaction. Thanks to this option, we can get well-ordered data. If you use MyISAM table engine, it’s not true.