mysql: setting default default_collation_for_utf8mb4?

Question

How to set the value for "default_collation_for_utf8mb4" to "utf8mb4_general_ci", globally , or when the mysql 8.0 server started ?

I tried

[mysqld]
init_connect='SET default_collation_for_utf8mb4=utf8mb4_general_ci'
collation_server = utf8mb4_general_ci
character_set_server = utf8mb4

but still not working. The value still showing "utf8mb4_0900_ai_ci".

 >show variables like "%collation%";
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |

• Akina: This is hardcoded (?) charset property value - see SHOW CHARSET;. Add according additional SQL statement executed during the connection establishing to the connection property.
• Rick James: init_connect is not executed as "root" (or other SUPER users) connect.

Answer 1

mysql> show variables like "default_collation_for_utf8mb4";
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
1 row in set (0.00 sec)
mysql> SET PERSIST default_collation_for_utf8mb4='utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. |
+---------+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@0273bbf363a9:/etc/mysql/mysql.conf.d# /etc/init.d/mysql status
 * Percona Server 8.0.29-21 is running
root@0273bbf363a9:/etc/mysql/mysql.conf.d# /etc/init.d/mysql restart
 * Stopping Percona Server 8.0.29-21
..
 * Percona Server 8.0.29-21 is stopped
 * Re-starting Percona Server 8.0.29-21
..
 * Percona Server 8.0.29-21 is started
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like "default_collation_for_utf8mb4";
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+
1 row in set (0.01 sec)