How take mysqldump with UTF8?

Question

I am trying to take mysql dump with command:

mysqldump -u xxxx -p dbxxx > xxxx270613.sql

what is command to take mysqldump with UTF8 ?

Answer 1

Hi please try the following.

mysqldump -u [username] –p[password] --default-character-set=utf8 -N --routines --skip-triggers --databases [database_name] > [dump_file.sql]

Answer 2

--default-character-set=utf8 is the option you are looking for the one can be used together with these others:

mysqldump --events \
 --routines \
 --triggers \
 --add-drop-database \
 --compress \
 --hex-blob \
 --opt \
 --skip-comments \ 
 --single-transaction \
 --skip-set-charset \
 --default-character-set=utf8 \
 --databases dbname > my.dump

Also, check the --hex-blob it helps to dump binary strings in hexadecimal format, so I can guaranty (be more portable) making the import to work.

The --databases option causes all names on the command line to be treated as database names. Without this option, mysqldump treats the first name as a database name and those following as table names.

With --all-databases or --databases, mysqldump writes CREATE DATABASE and USE statements prior to the dump output for each database. This ensures that when the dump file is reloaded, it creates each database if it does not exist and makes it the default database so database contents are loaded into the same database from which they came. If you want to cause the dump file to force a drop of each database before recreating it, use the --add-drop-database option as well. In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.

This helps to restore using:

# mysql < dump.sql

Instead of:

# mysql dbname < dump.sql