I am trying to take mysql dump with command:
mysqldump -u xxxx -p dbxxx > xxxx270613.sql
what is command to take mysqldump with UTF8 ?
Similarly, here's the command to change character set of MySQL table from latin1 to UTF8. Replace table_name with your database table name. mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; Hopefully, the above tutorial will help you change database character set to utf8mb4 (UTF-8).
The difference between utf8 and utf8mb4 is that the former can only store 3 byte characters, while the latter can store 4 byte characters. In Unicode terms, utf8 can only store characters in the Basic Multilingual Plane, while utf8mb4 can store any Unicode character.
utf-8 can store only 1, 2 or 3 bytes characters, while utf8mb4 can store 4 bytes characters as well. utf-8 is a subset of characters given by utf8mb4 .
Hi please try the following.
mysqldump -u [username] –p[password] --default-character-set=utf8 -N --routines --skip-triggers --databases [database_name] > [dump_file.sql]
--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
writesCREATE DATABASE
andUSE
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 aDROP DATABASE
statement preceding eachCREATE DATABASE
statement.
This helps to restore using:
# mysql < dump.sql
Instead of:
# mysql dbname < dump.sql
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With