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.
#!/bin/bash db_user="root" db_pass="password" 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.