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 ?

What do you mean by "take mysqldump with UTF8"? Whats the problem? — Nico Haase, Mar 20 '18 at 08:40
Are you looking for something like https://makandracards.com/makandra/595-dumping-and-importing-from-to-mysql-in-an-utf-8-safe-way? — Nico Haase, Mar 20 '18 at 08:41
@NicoHaase I was facing problem when I was importing sql file. There are some restrictions with other character set's. So There is standard with UTF8 and It fixes lot of mysql import issues. — flik, Mar 21 '18 at 03:09

score 29 · Accepted Answer · edited May 27 '19 at 00:24

29

Hi please try the following.

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

edited May 27 '19 at 00:24

randers

5,031
5
37
64

answered Mar 20 '18 at 08:46

IT-Guy

454
4
4

14

Never use `utf8`; as of 2021 use nothing less than `utf8mb4`. – John May 13 '21 at 00:46
1

@John Why? Can you please explain? – Raul Chiarella Oct 26 '21 at 14:51
2

@RaulChiarella You need to use `utf8mb4`, not `utf` or a lot of UTF characters will get corrupted. Example: **HTTP GET Request**: `SET CHARACTER SET 'utf8mb4'`, **HTTP POST Requests**: `SET NAMES 'utf8mb4'`. – John Oct 27 '21 at 09:29
Thanks. I will use utf8mb4 from now on. – Raul Chiarella Oct 27 '21 at 18:27
1

Here's an explanation of utf8 (utff8mb3) vs utf8mb4: https://stackoverflow.com/a/30074553 – scharette Jan 26 '23 at 18:40

score 20 · Answer 2 · answered Jun 10 '20 at 17:02

I had the problem, that even with applied utf-8 flags when creating the dump I could not avoid broken characters importing a dump that was created from a DB with many text columns using latin1. Some googling and especially this site helped me to finally figure it out.

mysqldump with --skip-set-charset --default-character-set=latin1 flags, to avoid MySQL attempt of reconversion and setting a charset.
fix the dump by replacing the charset strings using sed on terminal

sed -i 's/latin1_swedish_ci/utf8mb4/g' mysqlfile.sql
sed -i 's/latin1/utf8mb4/g' mysqlfile.sql

to make sure you don't miss anything you can do grep -i 'latin1' mysqlfile.sql before step 2 - and then come up with more sed orders. Introduction to sed here

create a clean DB

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
apply fixed dump

Wow, incredible answer, upvoted! It's unbelievable that setting `default-character-set=utf8` would actually cause utf-8 corruption, thank you! — HoldOffHunger, Nov 10 '21 at 18:59

score 14 · Answer 3 · edited Jun 20 '20 at 09:12

--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

Never use `utf8`; as of 2021 use nothing less than `utf8mb4`. — John, May 13 '21 at 00:46

How take mysqldump with UTF8?

3 Answers3

Linked