Preventing Encoding Issues with mysqldump

Creating a MySQL dump file of your database can be fairly straightforward, but, if you aren't careful, you could corrupt the character set in your backup file.

I used to created a database dump using a command like the following:

mysqldump -u nathan -p database1 > database1.backup.sql

The above command is very straightforward in that it generates a dump file using default options and redirects the output to a file instead of to standard output.

However, I want all my data to maintain a UTF-8 character set. To do this I need to use 2 options:

  • --default-character-set=utf8: This insures UTF8 is used for each field
  • --result-file=file.sql: This option prevents the dump data from passing the through the Operating System which likely does not use UTF8. Instead it passes the dump data directly to the file specified.

Using these new options your dump command would look something like this:

mysqldump -u nathan -p --default-character-set=utf8 --result-file=database1.backup.sql database1

If you are like me and are constantly creating MySQL dumps, you might want to consider creating a simple shell script like the one I use below.

mysqldump -u $db_user -p $db_pass --single-transaction --default-character-set=utf8 --result-file=$1.$(date -I).sql $1

The script just takes a single argument, the database name, and generates a datestamped dump file of it in the current directory.



saved my day