Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How take mysqldump with UTF8?

Tags:

mysql

I am trying to take mysql dump with command:

mysqldump -u xxxx -p dbxxx > xxxx270613.sql

what is command to take mysqldump with UTF8 ?

like image 960
flik Avatar asked Mar 20 '18 08:03

flik


People also ask

How do I change MySQL from UTF8 to latin1?

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

Should I use UTF8 or utf8mb4?

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.

What is the difference between utf8mb4 and UTF8 charsets in MySQL?

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 .


Video Answer


2 Answers

Hi please try the following.

mysqldump -u [username] –p[password] --default-character-set=utf8 -N --routines --skip-triggers --databases [database_name] > [dump_file.sql]
like image 84
IT-Guy Avatar answered Sep 29 '22 08:09

IT-Guy


--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 
like image 26
nbari Avatar answered Sep 29 '22 07:09

nbari