I was trying to migrate a MySQL database today, that contained Chinese characters. When you dump the database to standard out, like you normally would with mysqldump, the export doesn’t keep the Chinese characters. I did all sorts of things with the character set, etc., and nothing seemed to work. I always ended up with garbage on import. Obviously, the database is UTF-8, but the text file doesn’t work the way I expected. I tried the mysqldump command, passing the -r command, specifying the location to output the file. This is supposed to solve this problem, because it’s not going through standard out. However, you are left using standard in for the import, or you can cut in paste into MySQL Workbench. In any case, none of these worked. The characters were always corrupted. After doing a bit of research, and thinking about it for a few, I realized I could pipe the process directly from one MySQL box to another. This shouldn’t have seemed like rocket science, but I’d never thought of doing an export/import like this before. Here’s the syntax.
First, create the database on the remote machine, either by logging in our through mysqladmin.
mysqladmin -h host_name -u user_name -p create db_name
Next, pipe the dump from the original server to the new server.
mysqldump db_name | mysql -h host_name -u user_name -p db_name
This seems like a really easy solution for migration, though I found virtually no documentation on doing such a migration in this manner.
