Posted almost 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

By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.

Author of this card:

Henning Koch
Last edit:
22 days 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 makandropedia