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
|
1 |
mysqldump -h db_host_a -u user_a -ppassword_a db_name_a | mysql -h db_host_b -u user_b -ppassword_b db_name_b |
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
-
1mysqldump -h db_host_a -u user_a -ppassword_a db_name_a > dump.dmp
- Import
-
1mysql -h db_host_b -u user_b -ppassword_b db_name_b < dump.dmp
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.
