How to change from utf8 to utf8mb4 in MySQL?

Question

MySQL

There was a need to change the encoding of the database to save the special. characters

(1366, "Incorrect string value: '\\xF0\\x9F\\x92\\xB0<b...' for column 'description' at row 1")

mysql version 5.7
mysql settings

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

init-connect='SET NAMES utf8'


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# For the special characters
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Check if everything has changed

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

Some of the settings do not change, how to solve this issue?
BD is clean.

Answer 1

With this configuration

[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

we have

+--------------------------+--------------------+
| 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                          |
| collation_connection        | utf8mb4_general_ci |
| collation_database          | utf8mb4_unicode_ci |
| collation_server               | utf8mb4_unicode_ci |
+-----------------------------+------------------------+
10 rows in set (0.01 sec)

Answer 2

At a minimum, it is enough to change the charset of one field in the table

ALTER TABLE `table` 
  MODIFY `description` VARCHAR(xxx) CHARACTER SET utf8mb4;

Well, or immediately for all text fields in the table

ALTER TABLE `table` CONVERT TO CHARACTER SET utf8mb4;