Symptoms
New server set up with MySQL 8 and sites transferred give cannot establish connection errors and the PHP error_log shows "PHP Warning: mysqli_connect(): (HY000/2054): Server sent charset unknown to the client" is because the server collation and charset is incorrect.
Description
The following command
mysql -e "show variables like 'char%';"
may give output like the following:
+--------------------------+--------------------------------+
| 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-8.0/charsets/ |
+--------------------------+--------------------------------+
and databases with charset latin1 or utf8 may not work. The MySQL server collation and charset would need to be changed.
Workaround
The collation and charset were changed by modifying /etc/my.cnf and under [mysqld] and adding:
collation-server = utf8_unicode_ci
character-set-server = utf8
and then afterward add:
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
and then restart MySQL.
/scripts/restartsrv_mysql
Then when you check collation and charset again you should have:
mysql -e "show variables like 'char%';"
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+