3

When I use mysqldump to export mysql database, it always produce a dump.sql containing

...some other things...
/*!40101 SET character_set_client = utf8*/;
...some other things...

This is the mysqldump command I use:

mysqldump -u root -p databaseName -R -E --single-transaction --default-character-set=utf8mb4 > dump.sql

The charset of the mysql database is utf8mb4 rather than utf8, the characters related variables is:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_unicode_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+

Why does mysqldump always add /*!40101 SET character_set_client = utf8*/ rather than /*!40101 SET character_set_client = utf8mb4*/?

What happens if /*!40101 SET character_set_client = utf8*/ is used?

Can we have mysqldump use /*!40101 SET character_set_client = utf8mb4*/?

3

That special type of comment says "leave this as a comment if running a version older than 4.1.1; else execute it.

utf8mb4 came in in 5.5.3, so the 'correct' version would be

/*!50503 SET character_set_client = utf8mb4*/

If your version of mysqldump and mysql are newer than 5.5.3, no harm is done by leaving it 40101. If you do try to load it on a mysql older than 5.5.3, the SET will probably complain.

As for why mysqldump does not say 50503 -- I suspect that is a bug. File it at http://bugs.mysql.com.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.