防止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.

#!/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.

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> SOURCE utf8.dump