Posted over 7 years ago. Visible to the public.

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

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=utf8 database mysql> SET names 'utf8' mysql> SOURCE utf8.dump

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Author of this card:

Henning Koch
Last edit:
8 months ago
by Dominik Schöler
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev