Convert MySQL db character encoding set
Before you begin be sure to take a database dump in case something breaks. Then proceed with changing the character set as follows:
Inspect the current character encoding set
MariaDB [(none)]> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "webdbprod";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8 |
+----------------------------+
1 row in set (0.001 sec)
In this session we need to change the type from utf8 to utf8mb4
MariaDB [(none)]> ALTER DATABASE webdbprod CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.001 sec)
You can then verify the changes all the way into the table
MariaDB [(none)]> SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "webdbprod" AND T.table_name = "wp_posts";
+--------------------+
| character_set_name |
+--------------------+
| utf8mb4 |
+--------------------+
1 row in set (0.002 sec)
If you run into errors check the WARNINGS, remember to match the COLLATION type with the correct CHARACTER SET
MariaDB [(none)]> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------+
| Error | 1253 | COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' |
+-------+------+----------------------------------------------------------------------+
1 row in set (0.000 sec)