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


Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <strike> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <css>, <drupal5>, <drupal6>, <javascript>, <mysql>, <php>, <python>, <ruby>. The supported tag styles are: <foo>, [foo].
  • You can use Markdown syntax to format and style the text. Also see Markdown Extra for tables, footnotes, and more.
  • Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.