Dumping and importing from/to MySQL in an UTF-8 safe way

Updated . Posted . Visible to the public.

In a nutshell: to avoid your shell character set from messing with imports, use -r to export and SOURCE when importing.

Dumping safely

# Do not do this, since it might screw up encoding
mysqldump -uroot -p database > utf8.dump # this is bad

Better do:

mysqldump -uroot -p database -r utf8.dump

Note that when your MySQL server is not set to UTF-8 you need to do mysqldump --default-character-set=latin1 (!) to get a correctly encoded dump. In that case you will also need to remove the SET NAMES='latin1' comment at the top of the dump, so the target machine won't change its UTF-8 charset when sourcing.

If you only want to dump the structure without data, use

mysqldump -uroot -p --no-data database -r utf8.dump

Importing a dump safely

# Do not do this, since it might screw up encoding
mysql -u username -p database < dump_file # this is bad

Better do:

mysql -uroot -p --default-character-set=utf8mb4 database
mysql> SET names 'utf8'
mysql> SOURCE utf8.dump
Profile picture of Henning Koch
Henning Koch
Last edit
Daniel Straßner
License
Source code in this card is licensed under the MIT License.
Posted by Henning Koch to makandra dev (2010-08-26 20:37)